CODEWITHSUNDEEP
amazon-web-servicesamazon-athena
Roelant
Roelant

Reputation: 5119

See the files behind AWS athena with a query

Can you ask AWS Athena for the S3 files that are behind a table?

For example

msck show partitions

would give

s3://some-bucket/some-key/some-table/file1.csv,
s3://some-bucket/some-key/some-table/file2.csv

and similarly for tables which are partitioned or bucketed.

Upvotes: 0

Views: 312

Answers (1)

Theo
Theo

Reputation: 132862

Athena/Presto has a hidden column called $path which can be used to figure out which file a row comes from. You can use it to also figure out all files in a partition or table:

SELECT DISTINCT "$path"
FROM my_table

Please note that in Athena engine v1 this was a very expensive operation, but in engine v2 it's optimized and at least the query above won't scan any data.

See the docs for some more information.

Upvotes: 3

Related Questions