AWS-Athenaでファイル名をカラムに追加する方法

#この記事の内容
AWS-Athenaで、ファイル名をカラムに追加するやり方をご紹介します。

もう少し具体的に述べると、
以下のファイル名のデータがS3の同じ場所に格納されていて、
YYYYMMの部分をクエリで利用したいケースを想定しました。
・「test_file_202001.csv」
・「test_file_202002.csv」
・「test_file_202003.csv」

#クエリ例
Athenaでファイル名をカラムとして取得するには、「"$path"」を利用します。

SELECT "$path" FROM テーブル名

#やってみた（環境構築）
バケット名「test-bucket」の配下に、以下3つのデータが格納されています。

・「test_file_202001.csv」

name
satoshi
kasumi

・「test_file_202002.csv」

name
takeshi

・「test_file_202003.csv」

name
musashi
kojiro
sakaki

#やってみた（クエリ実行）
クエリを早速実行してみます。

SELECT
  *
  , "$path" 
FROM 
  バケット名

S3のバケット名とファイル名の両方が取得できました。

name	$path
satoshi	s3://test-bucket/test_file_202001.csv
kasumi	s3://test-bucket/test_file_202001.csv
takeshi	s3://test-bucket/test_file_202002.csv
musashi	s3://test-bucket/test_file_202003.csv
kojiro	s3://test-bucket/test_file_202003.csv
sakaki	s3://test-bucket/test_file_202003.csv

#応用例
今回のように「YYYYMM」部分のみを取得したい場合は、正規表現を用いて置換して取得しました。

SELECT
  *
  , REGEXP_REPLACE(REGEXP_REPLACE("$path", '^s3://test-bucket/test_file_', ''), '.csv$', '') AS yyyymm
FROM 
  バケット名

意図した通りの結果が取得できました！

name	yyyymm
satoshi	202001
kasumi	202001
takeshi	202002
musashi	202003
kojiro	202003
sakaki	202003

#最後に
簡単にカラムを追加できるので非常に便利ですね！

#参考文献
Amazon Athena ユーザーガイド