Athenaを使ってS3上のJSONを変換・パーティショニングしてParquet出力してみる

S3上のJSONデータをAthenaを利用してParquetに変換してみます。

使うのはこの話です。

aws.amazon.com

テストデータ生成

こんな感じのテストデータ使います。

$ for i in $(seq 1 10000);do echo "{\"date\":\"2019/2/$(($RANDOM%28+1))\",\"value\":$RANDOM}";done | head -n 3 | jq
{
  "date": "2019/2/27",
  "value": 24391
}
{
  "date": "2019/2/22",
  "value": 32486
}
{
  "date": "2019/2/16",
  "value": 27867
}

AWS CLIで直接S3バケットにアップロードします。

$ for i in $(seq 1 10000);do echo "{\"date\":\"2019/2/$(($RANDOM%28+1))\",\"value\":$RANDOM}";done | aws s3 cp - s3://otomo-athena-test/in/data.json

アップロードしたファイルを確認してみます。このJSONデータをParquetに変換します。

$ aws s3 cp s3://otomo-athena-test/in/data.json - | head -n3 | jq
{
  "date": "2019/2/18",
  "value": 18512
}
{
  "date": "2019/2/26",
  "value": 12236
}
{
  "date": "2019/2/18",
  "value": 31031
}

まずはJSONをAthenaのテーブルとして読み込みます。

CREATE EXTERNAL TABLE default.otomo_in (
    date string,
    value int
)   
ROW FORMAT  serde 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://otomo-athena-test/in'

SELECTしてみると、こんな感じです。

日付列をパーティションに利用

日付の date の項目をパーティションに利用します。

SELECT文に関しては以下のような感じ。

SELECT   value,
         element_at(split(date,'/'),1) AS year ,
         element_at(split(date,'/'),2) AS month,
         element_at(split(date,'/'),3) AS day
FROM default.otomo_in;

Parquet＋パーティション分割して出力

先程のSELECT文使って新しいテーブルを作成します。

この際に format のオプションや partitioned_by のオプションを指定しているのがポイントです。

詳しくはこの辺りです。

CTAS クエリの例 - Amazon Athena

CREATE TABLE default.otomo_out
WITH (
  partitioned_by  = ARRAY['year','month','day'],
  format = 'PARQUET',
  external_location = 's3://otomo-athena-test/out'
) AS
SELECT   value,
         element_at(split(date,'/'),1) AS year ,
         element_at(split(date,'/'),2) AS month,
         element_at(split(date,'/'),3) AS day
FROM default.otomo_in;

正常にクエリが終了すると以下のようにパーティション付き状態でS3にファイルが生成されます。

ちゃんとParquet形式のファイルで出力されています。

念の為以下で確認してもデータ同じです。

SELECT SUM(value) AS sum ,COUNT(*) AS cnt FROM otomo_in;
SELECT SUM(value) AS sum ,COUNT(*) AS cnt FROM otomo_out;

SQL打ってみるとパーティショニングされているようです。

SELECT * FROM otomo_out;
 (実行時間: 5.01 秒, スキャンしたデータ: 29.12 KB)

SELECT * FROM otomo_out where day = '1';
 (実行時間: 3.92 秒, スキャンしたデータ: 1.01 KB)

カタログへパーティション追加

この時点ではパーティションが、カタログ上で認識されていません。

このコマンドをAthenaで打って認識させます。

MSCK REPAIR TABLE otomo_out;

認識されました。

[https://docs.aws.amazon.com/ja_jp/athena/latest/ug/msck-repair-tabl e.html:title]

所感

こういった処理はGlueでやっていましたが、色々やればデータの追加にも対応できそうですが、基本はワンショットになりそうです。ならAthenaもさっくり使えて良いかも。

参考URL

docs.aws.amazon.com