Hive on TreasureData Explode Multiple Arrays
目的
下記ブログに書いてあるように、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内の最大要素数に依存して増やす必要が有る。
まとめ
もっと簡単な書き方はないでしょうかね・・・
Author And Source
この問題について(Hive on TreasureData Explode Multiple Arrays), 我々は、より多くの情報をここで見つけました https://qiita.com/toru-takahashi/items/26cc4edcee80cfd8526b著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .