CODEWITHSUNDEEP
sqlamazon-web-servicescsvamazon-s3amazon-athena
Captain Caveman
Captain Caveman

Reputation: 1526

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.

Upvotes: 3

Views: 1383

Answers (1)

Rahul Diggi
Rahul Diggi

Reputation: 400

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'

Upvotes: 7

Related Questions