Amazon Athenaの$pathを使って検索対象のファイルを絞り込む

こんにちは。サービスグループの武田です。

Amazon Athena利用してますか？最近同僚にAthenaの機能を教えてもらったのでブログに認めておきます。

その機能とは$pathというカラムについてです。隠しカラムとして用意されており、データを格納しているファイル名が返されます。これを利用すると、Athenaのテーブルで指定したLOCATIONに異なる拡張子のファイルが含まれていても問題なくクエリが実行できます。簡単に確認してみます。

次のようなファイルを用意し、S3バケットにアップロードします。今回はs3://testdata-xxxxxx/path_testにアップロードしたとします。

data1.csv

id,name,age
1,hoge,10
2,fuga,20
3,piyo,30

data2.csv

id,name,age
100,foo,11
200,bar,21
300,buz,31

アップロードできたらテーブルを作成します。

CREATE EXTERNAL TABLE IF NOT EXISTS `path_test`(
  `id` int,
  `name` string, 
  `age` int
 )
 ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
 LOCATION 's3://testdata-xxxxxx/path_test'
 TBLPROPERTIES ("skip.header.line.count"="1")

テーブルが作成できたら次のクエリを実行してみましょう。

SELECT *, "$path" FROM "path_test" limit 10;

問題なく結果が取得できました。ファイルのパスが出力されていますね。

さて何らかの理由でこのフォルダーにCSV以外のファイルも出力されているケースがあるとします。今回は次のようなデータが含まれたJSONファイルを追加でアップロードします。

metadata.json

{
  "date": "2021-09-22",
  "author": "takeda",
  "organization": "classmethod"
}

先ほどと同じSELECTクエリを実行してみます。結果は残念ながらエラーになります。フォーマットが異なるのでしかたないですね。

この問題を解決しましょう。$pathは通常のカラムと同じように扱えるためWHERE句での指定も可能です。つまり$pathのファイル名がcsvのものだけに絞り込めればよいということになります。次の改良版のクエリを実行すれば先ほどと同様の結果が得られます。後述の参考サイトではregexp_likeを使用していますが今回はLIKE句を使用してみました。

SELECT *, "$path" FROM "path_test" WHERE "$path" LIKE '%.csv' limit 10;

まとめ

何らかの理由でフォーマットの異なるファイルが混在する環境でAthenaクエリを実行したい場合、WHERE句での絞り込みを検討してみてください。

参考URL

• Amazon Athena パースができず空白行になった原因のデータファイルを特定する | DevelopersIO
• Athena テーブルの行のソースファイルを見つける