目的

下記ブログに書いてあるように、1つのレコードに複数のArrayカラムがあり、それらが対応関係があるので展開したい。
http://linuxandryan.wordpress.com/2014/01/17/hive-explode-multiple-arrays/

例1：元のテーブル

cookie	ids (array)	values (array)
1234123	[a,b,c]	[1,2,3]
1234123	[a,d]	[4,5]

上記のテーブルを下記のようにしたい

例2：展開後のテーブル

cookie	id (string)	value (int)
1234123	a	1
1234123	b	2
1234123	c	3
1234123	a	4
1234123	d	5

解決策 (2017-10-08)

HivemallのUDTFを使って下記みたいな形で実現できるようになった。

WITH t as (
select 'id1' as id, ARRAY(11,12,13) as a, ARRAY(21,22,23,24) as b
union all
select 'id2' as id, ARRAY(31,32,33) as a, ARRAY(41,42,43,44) as b
)
select
   id,
   n as array_index,
   subarray(a, n-1, n)[0] as  val_1,
   subarray(b, n-1, n)[0] as  val_2
from
   ( select id, a, b from t ) t1
lateral view
   generate_series(1,4) n1 as n

解決策（現状では無理

下記ブログでは、下記のUDFを使ったクエリによって記載されている。
http://brickhouseconfessions.wordpress.com/2013/03/07/exploding-multiple-arrays-at-the-same-time-with-numeric_range/
https://github.com/klout/brickhouse

select
 cookie,
 array_index( ids, n ) as id,
 array_index( values, n ) as value
from
( select cookie, ids, values from my_table ) t
lateral view numeric_range( size( values ) - 1 ) n1 as n

UDFが大変便利そうではあるが、TreasureDataでは現在利用できないので、サブクエリを多様して頑張るとどうなるだろうか。
ポイントとしては、numeric_rangeとarray_indexをどうHiveで表現するのかなので、それぞれについて考えてみる。
numeric_rangeを作るには、文字列をsplitして配列を作る。
array_indexはliteral Viewで頑張る。

代替案

事前準備として、データとして1レコードしか持たない one テーブルを作成しておくこと。

また、クエリのポイントとして、
numeric_rangeで生成される一定範囲の数値(ex. 0,1,2,3,...)をsplit_lineで擬似的に作成している。
つまり、idsの最大要素数を文字列'0,1,2,3,'にマッチさせて、各レコード毎の要素数分の数値を残し、
SPLITしている。これにより、要素数が1なら'0,'だけのこし、Lateral Viewをした際に対象のレコードの0の値を持つカラムが追加される。
この値をその配列から値をとる際の要素の目安にする。

SELECT
  cookie,
  CASE cast(num as int)
    WHEN 0 THEN ids[0]
    WHEN 1 THEN ids[1]
    WHEN 2 THEN ids[2]
    WHEN 3 THEN ids[3]
    ELSE NULL
  END as id,
  CASE cast(num as int)
    WHEN 0 THEN values[0]
    WHEN 1 THEN values[1]
    WHEN 2 THEN values[2]
    WHEN 3 THEN values[3]
    ELSE NULL
  END as value
FROM
(
  SELECT
    /*+mapjoin(t2)*/
    cookie, ids, values,
    split(substr(line, 0, instr(line, concat(',', cast(ids_size as string), ','))), ',') as split_line
  FROM (
    SELECT cookie, ids, values, size(ids) as ids_size from my_table
  ) t1 Join (
    -- one table have only 1 record
    select '0,1,2,3,' as line from one
  ) t2 ON 1=1
) t3 LATERAL VIEW explode(split_line) numtable AS num

つまり、上記の例1と例2のテーブルの間に一時的に下記のようなテーブルが内部的には生成されている。

例3：一時的なテーブル

cookie	ids (array)	values (array)	split_line (array)
1234123	[a,b,c]	[1,2,3]	[0,1,2]
1234123	[a,d]	[4,5]	[0,1]

そして、split_lineの値をCASE文で分岐させることで各要素の値のみを取得できるようにしている。
そのため、'0,1,2,3,'とCASE部分についてはids内の最大要素数に依存して増やす必要が有る。

まとめ

もっと簡単な書き方はないでしょうかね・・・

Hive on TreasureData Explode Multiple Arrays

目的

解決策 (2017-10-08)

解決策（現状では無理

代替案

まとめ