Amazon Athena set location to single csv file

I would like to set the location value in my Athena SQL create table statement to a single CSV file as I do not want to query every file in the path. I can set and successfully query an s3 directory (object) path and all files in that path, but not a single file. Is setting a single file as the location supported?

Successfully queries CSV files in path:

LOCATION 's3://my_bucket/path/'

Returns zero results:

LOCATION 's3://my_bucket/path/filename.csv.gz'

Create table statement:

CREATE EXTERNAL TABLE IF NOT EXISTS `default`.`my_db` (
    `name` string,
    `occupation` string,    
    )
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
    WITH SERDEPROPERTIES (
    'field.delim' = ','
   ) LOCATION 's3://bucket-name/path/filename.csv.gz'
   TBLPROPERTIES ('has_encrypted_data'='false');

I have read this Q&A and this, but it doesn’t seem to address the same issue.

Thank you.

Answer

You could try adding path of that particular object in WHERE condition while querying:

SELECT * FROM default.my_db
WHERE "$path" = 's3://bucket-name/path/filename.csv.gz'

Advertisement

Answer