【Athena】行のパス・ファイルを取得する

どのファイルにデータが入っているか知りたい

Athenaで一つのテーブルの中で、ファイルごとにデータを見たい場合などがあり、これまではテーブルを分けたり、パーティションで区切っていたのですが、クエリでファイルパスが取得できる事がわかったので記事にします。
こちらの公式のドキュメントを参照しました。

パスの取得方法

パスは "$path" で取得可能です。

select
    id,
    "$path"
from
    users
limit 10

とすると結果が

"id","_col1"
"1","s3://example-backet/path/to/hoge.csv"
"2","s3://example-backet/path/to/foo.csv"
"3","s3://example-backet/path/to/bar.csv"

となっています。

ファイル名の取得

ファイル名は element_at(split("$path", '/'), cardinality(split("$path", '/'))) で取得可能です。
やっていることは複雑に見えるかもですがファイル名を / でsplitして配列を取得。ファイル名なのでその配列を最後の要素を取得しているだけです。
例えば

select
    id,
    element_at(split("$path", '/'), cardinality(split("$path", '/')))
from
    users
limit 10

とすると結果が

"id","_col1"
"1","hoge.csv"
"2","foo.csv"
"3","bar.csv"

となります。

ファイルごとにカウントを取る

select
    element_at(split("$path", '/'), cardinality(split("$path", '/'))) as "ファイル名",
    count(*) as "カウント"
from
    users
group by
    element_at(split("$path", '/'), cardinality(split("$path", '/')))

とすると結果が

"ファイル名","カウント"
"foo.csv ","12742"
"bar.csv ","134308"
"hoge.csv ","547824"

のようなものが取れます