hive

HiveでSELECT結果をARRAYに変換する

概要

TreasureDataでデータ加工をしている際に、「GROUP BY」したSELECT結果に、
GROUP BY対象外のカラムを配列で追加したかったので調べた結果を作業メモとして残す。

GROUP BY対象外のカラムをARRAYにする

COLLECT_SET関数で配列にする

SELECT 
  td_global_id,
  COLLECT_SET(user_id) AS user_ids 
FROM
  dev.hoge_access  
WHERE 
  td_global_id IS NOT NULL AND td_global_id <> ''
  AND  user_id IS NOT NULL AND user_id <> ''
GROUP BY
  td_global_id
;

GROUP BY対象外のカラムをカンマ区切りで結合する

CONCAT_WS関数を使って結合する

SELECT 
  td_global_id,
  -- カンマ区切りの文字列に結合して文字列に...
  CONCAT_WS(',', COLLECT_SET(user_id)) AS user_ids 
FROM
  dev.hoge_access  
WHERE 
  td_global_id IS NOT NULL AND td_global_id <> ''
  AND  user_id IS NOT NULL AND user_id <> ''
GROUP BY
  td_global_id
;

結合したカンマ区切りの文字列を分割する

SPLIT関数を使って分割して配列型にする。

SELECT 
  td_global_id,
  -- カンマ区切りの文字列を分割して配列化
  SPLIT(CONCAT_WS(',',COLLECT_SET(user_id)), ',') AS user_ids 
FROM
  dev.hoge_access  
WHERE 
  td_global_id IS NOT NULL AND td_global_id <> ''
  AND user_id IS NOT NULL AND user_id <> ''
GROUP BY
  td_global_id
;

参考サイト