Athenaのパーティション項目の型とパーティションプロジェクションの奇妙な関係

Athenaのパーティションプロジェクションを試していたらちょっとハマったのでメモしておく

パターン1

S3には /app_log/year=2020/month=06/day=05/hour=04/ のような場所にログをおいている

year, month, day, hourを Glueテーブル上の型で stringにする パーティションプロジェクション上の型はintegerにする(stringがないので) たとえば projection.month.type = "integer"

SELECT * FROM my_db.app_log
WHERE year = '2020' AND month = '06' AND day = '05' AND hour = '04'
LIMIT 10

のように検索するとヒットする。 しかし

SELECT * FROM my_db.app_log
WHERE year = '2020' AND month = '06'
LIMIT 10

はヒットしない!

パターン2

S3には /app_log/year=2020/month=06/day=05/hour=04/ のような場所にログをおいている

year, month, day, hourを Glueテーブル上の型で intにする
パーティションプロジェクション上の型はintegerにする(stringがないので)
たとえば projection.month.type = "integer"

SELECT * FROM my_db.app_log
WHERE year = 2020 AND month = 6 AND day = 5 AND hour = 4
LIMIT 10

のように検索するとヒットしない!

SELECT * FROM my_db.app_log
WHERE year = '2020' AND month = '06' AND day = '05' AND hour = '04'
LIMIT 10

のように検索してもヒットしない!

パターン3

S3には /app_log/year=2020/month=6/day=5/hour=4/ のような場所にログをおく。つまり 0 を抜いた

year, month, day, hourを Glueテーブル上の型で intにする
パーティションプロジェクション上の型はintegerにする
たとえば projection.month.type = "integer"

SELECT * FROM my_db.app_log
WHERE year = 2020 AND month = 6 AND day = 5 AND hour = 4
LIMIT 10

のように検索するとヒットする

SELECT * FROM my_db.app_log
WHERE year = 2020 AND month = 6
LIMIT 10

もヒットする。

つまり、S3のパーティションにあたる値が0がついていない数値でかつ、 Glueテーブルとパーティションプロジェクションの型が数値型でないといけない!!

じゃあ S3の ディレクトリを /app_log/year=2020/month=06/day=05/hour=04/ から /app_log/year=2020/month=6/day=5/hour=4/ に変更しようとしても、0抜き指定はfluentdのオプション上できない。https://docs.fluentd.org/v/0.12/output/file#time_slice_format
※追記
よくよく考えると、もしfluentdがTime#strftimeを使っているなら year=%Y/month=%-m/day=%-d/hour=%-H で0抜きができそう。

パターン4

S3には /app_log/dt=2020-06-05-04/ のような場所にログをおく 。
dt=YYYY-mm-dd-HHのような形式は https://docs.aws.amazon.com/athena/latest/ug/partitions.html でも記されているので公式に近い。

dtを Glueテーブル上の型で stringにする パーティションプロジェクション上の型はdateにする。 projection.dt.type = "date"

SELECT * FROM my_db.app_log
WHERE dt > '2020-06-05-04'
LIMIT 10

のように検索するとヒットする

SELECT * FROM my_db.app_log
WHERE dt > '2020-06'
LIMIT 10

でもヒットする

まとめ

パターン4が妥当かな