Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得する

Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得する場合に苦労したため、解決方法を紹介しておきます。

結論から言うと、partition分割を使い、フォルダ毎にpartitionとして読み込むことで、AthenaのSQLからフォルダ名を参照することできます。

S3上のCSVファイルをAthenaから読む際に問題が発生するケース

以下のようなケースでは、S3上のCSVファイルにはuser_idが存在しないため、そのままCSVのカラムをAthenaのテーブルとしてマッピングすることができません。

• S3上にCSV形式でユーザーの行動ログが入っている
• ユーザーごとにS3上でフォルダが分けられている
• CSVファイルにはユーザーIDが含まれておらず、ユーザーIDはS3のpathからしか分からない

行動ログCSV

date	action
2018-04-24	view_page
2018-04-25	payment

ログ保存場所（user_idが1の場合）

s3://user-action-logs/1/action.log

解決方法

このような場合は、Athenaのテーブルをuser_idごとにpartition分割し、それぞれのフォルダとパーティションとして読み込むことで、SQL上から参照できるようになります。

docs.aws.amazon.com

テーブルの作成

以下のように PARTITIONED BY に user_id を指定します。

CREATE EXTERNAL TABLE user_actions (
    date timestamp,
    action string
)
PARTITIONED BY (user_id int)
LOCATION 's3://user-action-logs/'
-- 以下メタデータ省略

partitionの追加

以下のクエリを実行し、特定ディレクトリ以下をpartitionとして追加します。 読み込みたいpath全てに対して実行する必要があります。

ALTER TABLE user_actions ADD PARTITION (user=1) location 's3://user-action-logs/1/'

SQLでの参照

partitonは通常のカラムと同様に参照することができます。

SELECT
  user_id,
  date,
  action
FROM user_actions
WHERE user_id = 1

まとめ

Amazon AthenaでCSVに含まれていない情報をS3のフォルダ名から取得したい場合、partition分割を使い、フォルダ毎にpartitionとして読み込むことで、AthenaのSQLからフォルダ名を参照することできます。