大量のファイルからのスキャン量を減らしたい

Athenaの料金はスキャン量に比例するので例えば

1.csv.gz
2.csv.gz
3.csv.gz

99999.csv.gz
100000.csv.gz

といったファイルがあり、その合計が数テラバイト（とにかく大きいデータ）あるとします。
これらは例えば時系列データで2015年〜2022年のデータが入っているとします。

スキャン料を減らす方法として、parquet化やpartitionで分けるというやり方があるのですが、ここで問題が発生します。
時系列データならpartitionで分けるのが一番いいのですが、partitionに振り分けるには一度のスキャンでは100個のパーティションでしか分けられません。
なので最大でも日付単位でパーティションを分けると１年のデータを作るのに3ヶ月ごとにSQLを分けなければならないため、1年で4回のフルスキャンが発生します（今回はCSVなのでフルスキャンとなる）。例えば10TBのデータであれば10TB*4で40TBのスキャンが必要になります。

1 TB あたり 5.00USD

という料金設定だと200USDつまり3万円程度かかります。これが大きいかどうかは個人差がありますが、減らせるに越したことはないでしょう。

どのファイルにいつのデータが入っているかマッピングする

上記記事でも書いたのですが、Athenaのクエリで行のpathが取得可能です。
なのでどのファイルにいつのデータが入っているのか一度マッピングしておけばフルスキャンを避けることができます。
今回は huge_data_csv_table というテーブルにtimeというタイムスタンプが入っているとします。
これを以下のSQLでどのファイルにどのデータが入っているかをマッピングします

CREATE TABLE huge_data_date_mapping
    WITH (
    format='PARQUET',
    external_location='s3://example-backet/date_map'
) AS

SELECT DISTINCT
    "$path" as path,
    date_format(time, '%Y%m%d') as date,
    date_format(time, '%Y%') as year,
    date_format(time, '%m') as month,
    date_format(time, '%d') as day
FROM
    huge_data_csv_table

日付の持ち方はおまかせします…

で、このhuge_data_date_mapping というのにどのファイルにいつのデータが入っているかが

"path","date","year","month","day"
"s3://example-backet/date_map/1.csv.gz","20200506","2020","5","6"
"s3://example-backet/date_map/2.csv.gz","20200505","2020","5","5"
"s3://example-backet/date_map/3.csv.gz","20200508","2020","5","8"
...

このように管理できるようになります。

データを取り出す

データを取り出すときはこのmappingテーブルからファイルを絞ることでスキャン量が減らせます。

SELECT
    *
FROM 
    huge_data_csv_table
WHERE
    "$path" in (SELECT path FROM huge_data_date_mapping WHERE year = 2020 and month = 5)

こうすることでパーティションを分けるにしてもスキャンするファイルを絞りながらパーティションに分けていく作業が可能となりました。