How to get input file name as column in AWS Athena external tables

sql amazon-web-services amazon-s3 presto amazon-athena
Rajeev picture Rajeev · May 16, 2017 · Viewed 15k times · Source

I have external tables created in AWS Athena to query S3 data, however, the location path has 1000+ files. So I need the corresponding filename of the record to be displayed as a column in the table.

select file_name , col1 from table where file_name = "test20170516"

In short, I need to know INPUT__FILE__NAME(hive) equivalent in AWS Athena Presto or any other ways to achieve the same.

Answer

jens walter picture jens walter · May 16, 2017

You can do this with the $path pseudo column.

select "$path" from table

Related questions

Hadoop/Hive : Loading data from .csv on a local machine

As this is coming from a newbie... I had Hadoop and Hive set up for me, so I can run Hive queries on my computer accessing data on AWS cluster. Can I run Hive queries with .csv data stored on …

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More
How to create an Index in Amazon Redshift

I'm trying to create indexes in Amazon Redshift but I received an error create index on session_log(UserId); UserId is an integer field.

Read More