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...
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?
1 Answer
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'
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
