Athenaでパーティション射影を使ってみた

Athenaを使う機会があったので、パーティション機能の使い方を簡単ですが備忘録としてまとめてみました。
S3上のファイルとの関連も書いています。

Athenaとは

Amazon S3上のファイルをデータとして、SQLでアクセスできるAWSサービスです。テーブルを作成するときにS3上のパスを指定すると、そのパス以下のファイルがデータとして扱われます。

パーティション機能を使うとSELECTクエリ等でパーティション・プルーニングが働き、
パーティションキーに対するWHERE条件に基づいて不要なデータ探索をスキップできます。

データのイメージ

今回使用するデータは3つの列を持つテーブルです。

日付(tm)	識別子(id)	値(value)
2020-12-01	sensor1	100.0
2020-12-02	sensor1	101.0
2020-12-02	sensor2	102.0
2020-12-03	sensor1	103.0
2020-12-03	sensor2	104.0

テーブル定義

以下のSQLでテーブルを作成しました。

CREATE EXTERNAL TABLE tbl1  (id string, value double)
PARTITIONED BY (tm date)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('serialization.format' = ',', 'field.delim' = ',')
LOCATION 's3://test-bucket/tbl1/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'projection.enabled' = 'true',
  'projection.tm.type' = 'date',
  'projection.tm.range' = 'NOW-3YEARS,NOW+3YEARS',
  'projection.tm.format' = 'yyyy-MM-dd',
  'storage.location.template' = 's3://test-bucket/tbl1/${tm}'
);

日付列(tm)はパーティション列にするので、残りの識別子列(id)と値列(value)をEXTERNAL TABLEの列として定義します。
今回データの場所は、s3://test-bucket/tbl1/ としました。

パーティション列の指定はPARTITIONED BYで行い、設定はTBLPROPERTIESで指定します。
データの範囲'projection.tm.range'を指定しないと、INSERT時に以下のエラーが出たので、
INVALID_TABLE_PROPERTY: Must provide a range to build a projected temporal partition column!
今回は本日から前後3年間を指定しました。

テーブル作成後、以下のSQLを実行し、パーティションを認識させておきます。
（あらかじめS3上にファイルがなければ実行不要）

MSCK REPAIR TABLE tbl1;

データの登録

通常のINSERT文でデータを登録できます。今回は以下のSQLを実行しました。

INSERT INTO tbl1(id, value, tm) VALUES('sensor1', 100.0, DATE '2020-12-01');
INSERT INTO tbl1(id, value, tm) VALUES('sensor1', 101.0, DATE '2020-12-02');
INSERT INTO tbl1(id, value, tm) VALUES('sensor2', 102.0, DATE '2020-12-02');
INSERT INTO tbl1(id, value, tm) VALUES('sensor1', 103.0, DATE '2020-12-03'),('sensor2', 104.0, DATE '2020-12-03');
INSERT INTO tbl1(id, value, tm) VALUES('sensor1', 105.0, DATE '2020-12-03'),('sensor2', 106.0, DATE '2020-12-04');

登録されたデータの確認

SELECT文でデータを取得できますが、S3上のファイルの中身を確認してみました。

テーブル作成時に指定した場所（s3://test-bucket/tbl1/）にファイルが作成されています。
パーティションごとにディレクトリができていて、その下に.gzファイルがあります。

ファイル構成は以下のようになっていました。
.gzファイルを展開するとカンマ区切りのテキストファイルになっていることがわかります。

2020-12-01/xxx.gz:
sensor1,100.0

2020-12-02/yyy.gz:
sensor1,101.0

2020-12-02/zzz.gz:
sensor2,102.0

2020-12-03/www.gz:
sensor1,103.0
sensor2,104.0

2020-12-03/www.gz:
sensor1,105.0

2020-12-04/www.gz:
sensor2,106.0

INSERT文ごとにファイルがファイルが作成されていますね。異なるパーティションの場合はさらに別ファイルに分割されています。

intervalを設定してみた

intervalはパーティションキーの値の間隔を指定できる機能です。さっそく使ってみます。

date列に対して、"日にち"でインターバル

先程のCREATE文に対して、projection.datehour.intervalとprojection.datehour.interval.unitを指定し、インターバルを3日に設定しました。

CREATE EXTERNAL TABLE tbl2 (id string, value double)
PARTITIONED BY (tm date)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('serialization.format' = ',', 'field.delim' = ',')
LOCATION 's3://test-bucket/tbl2/'
TBLPROPERTIES (
  'has_encrypted_data'='false',
  'projection.enabled' = 'true',
  'projection.tm.type' = 'date',
  'projection.tm.range' = 'NOW-3YEARS,NOW+3YEARS',
  'projection.tm.format' = 'yyyy-MM-dd',
  'projection.datehour.interval' = '3',
  'projection.datehour.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://test-bucket/tbl2/${tm}'
);

それでは、本日(12/2)、明日、明後日のデータを登録してみます。インターバルは3日だけど、明日・明後日のデータは登録できるのか？登録できた場合、S3上のディレクトリ構成はどうなっているか気になり、確認しました。

INSERT INTO tbl2(id, value, tm) VALUES('sensor1', 100.0, DATE '2020-12-02');
INSERT INTO tbl2(id, value, tm) VALUES('sensor2', 100.0, DATE '2020-12-03');
INSERT INTO tbl2(id, value, tm) VALUES('sensor3', 100.0, DATE '2020-12-04');

INSERTできました。
S3上はどうなっているかというと、s3://test-bucket/tbl2に3つのディレクトリが作成され、その下にそれぞれ.gzファイルがありました。

2020-12-02/xxx.gz:
sensor1,100.0

2020-12-03/yyy.gz:
sensor2,100.0

2020-12-04/zzz.gz:
sensor3,100.0

date列に対して、"月"でインターバル

今度はインターバルを1月に設定しました。パーティションキーの列には日にちのデータが格納されています。

4つのデータを登録してみます。本日、明日、1ヶ月前、1ヶ月と1日前のデータです。
インターバルより細かいデータはどう扱われるでしょうか？

INSERT INTO tbl3(id, value, tm) VALUES('sensor1', 100.0, DATE '2020-12-02');
INSERT INTO tbl3(id, value, tm) VALUES('sensor2', 100.0, DATE '2020-12-03');
INSERT INTO tbl3(id, value, tm) VALUES('sensor3', 100.0, DATE '2020-11-02');
INSERT INTO tbl3(id, value, tm) VALUES('sensor4', 100.0, DATE '2020-11-01');

結論としては、"日"でのインターバルと同じでした。
以下のように、日付ごとにディレクトリが作成されていました。

2020-11-01/xxx.gz:
sensor4,100.0

2020-11-02/yyy.gz:
sensor3,100.0

2020-12-02/zzz.gz:
sensor1,100.0

2020-12-03/www.gz:
sensor2,100.0

おわりに

パーティション射影機能を使ってみました。
CREATE EXTERNAL TABLEのTBLPROPERTIESに設定を記述するだけで簡単にパーティションが作れました。
S3上には、パーティションのインターバルに関係なくstorage.location.templateで指定した場所にファイル分割されてデータが格納されるようです。

参考文献

https://docs.aws.amazon.com/athena/latest/ug/partition-projection.html
https://dev.classmethod.jp/articles/20200627-amazon-athena-partition-projection/