CODEWITHSUNDEEP
amazon-web-servicesamazon-s3amazon-athena
Liz Hi
Liz Hi

Reputation: 3

create tables from S3 bucket file

In my S3 bucket I have several files with different schemas.

s3://folder/file1.csv
s3://folder/file2.csv
s3://folder/file3.csv
s3://folder/file4.csv

All files contain fields I need, but number of columns differs.

I try to do this for one of the file, but the created table remains empty

CREATE  EXTERNAL TABLE test1 (
app_id string,
app_version string
)
row format delimited fields terminated by  ','
 LOCATION 's3://folder/file4.csv';


MSCK REPAIR TABLE test1;

Can I create 3 tables from these files? Or I can put fields I need from all files in one table?

Upvotes: 0

Views: 4313

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270144

You cannot define a file as a LOCATION for Amazon Athena. It will result in this error message:

Can't make directory for path 's3://my-bucket/foo.csv' since it is a file

You should put each file in a separate folder and then set the LOCATION to the folder. All files in that folder (even if it is just one file) will be scanned for each query.

Also, there is no need to call MSCK REPAIR TABLE unless it contains partitions.

By the way, this line:

LOCATION 's3://folder/file4.csv'

should also specify the bucket name:

LOCATION 's3://my-bucket/folder/file4.csv'

Upvotes: 1

Related Questions