Amazon Athena カラムの中のカンマ区切りデータを複数レコードに分解する

将来的なデータ分析に備えウェブブラウザからPOSTされたデータをそのままファイルやレコードに登録しているケースでは、分析対象のデータのカラムにカンマ区切りデータがそのまま格納されていることがあります。このままではデータ分析用途のデータとして不向きなので、複数レコードに分解する必要があります。 Amazon Athenaでは、split関数と「CROSS JOIN UNNEST」構文を利用して簡単に複数レコードに分解することができます。

サンプルTSVファイル

ファイルはタブ区切りで、３つ目のフィールドがカンマ区切りのフィールドです。今回は６種類のレコードを用意しました。１〜３は正常なカンマ区切りデータです。これだけでは面白くないので、４〜６はあえて異常なカンマ区切りデータを用意しました。

1   ok1 1
2   ok2 2,3
3   ok3 4,5,6
4   ng1 7,
5   ng2 ,8
6   ng3 ,9,

テーブル定義

CREATE EXTERNAL TABLE IF NOT EXISTS comma_data (
id int, 
name string,
categories string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '\t',
  'field.delim' = '\t'
)LOCATION 's3://<bucketname>/comma_data/';

SELECTの例

categoriesカラムを参照するとカンマ区切り文字がそのまま表示されます。

SELECT id, name, categories
FROM comma_data t1;

split関数と「CROSS JOIN UNNEST」構文を利用して、categoriesカラムのデータをカンマ「,」区切りして、categoryカラムの複数レコードとして分割します。

SELECT id, name, category
FROM comma_data t1
  CROSS JOIN UNNEST(
    split(t1.categories, ',')
  ) AS t (category)
;

カンマ区切り文字が複数レコードに分解されました。カンマのみの場合は値がないカラムとして出力されています。

値がないカラムのレコードを取り除くには、「category IS NOT NULL」ではなく、「category <> ''」と指定します。categoryはNULLではないようです。

SELECT id, name, category
FROM comma_data t1
  CROSS JOIN UNNEST(
    split(t1.categories, ',')
  ) AS t (category)
WHERE category <> ''
;

最後に

正常なカラムデータではない場合でもエラーにならずレコードとして分解されることが確認できました。データ分析の要件に合わせ必要に応じて、不要なレコードを除去する条件を指定することができるので、簡易なETLにも活用できそうです。