How to use multiple file with different structure in Athena

Question

I have multiple file in s3 bucket directory (All files are in same directory). All files have different structure like if one file has 4 columns then second file has 6 different columns. How can I use these files to create Athena table?

Answer 1

I believe this is currently not possible with AWS Athena / Presto (please tell me otherwise!).

The external location for an Athena table (always EXTERNAL TABLE) must be a directory.

s3://mybucket/folder/table/file_a and s3://mybucket/folder/table/file_b with different stuctures will lead to SCHEMA_MISMATCH.

Athena expects tables in the format s3://mybucket/folder/table1/ and s3://mybucket/folder/table2/.

Update:

From a conversation with AWS support:

For the first path requirements you have to move the files per table wise in separate folders else Athena will always consider all the files are for a single table.

Answer 2

You can create tables in Athena that point to specific files.

What you do is you create a table with a special input format:

STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'

and then instead of pointing the LOCATION of the table to the actual files, you point it to a prefix with a single symlink.txt file (or point each partition to a prefix with a single symlink.txt). In the symlink.txt file you add the S3 URIs of the files to include in the table, one per line.

The only documentation that I know of for this feature is the S3 Inventory documentation for integrating with Athena.

You can also find a full example in this Stackoverflow response: https://stackoverflow.com/a/55069330/1109

Answer 3

If your files contain different data, you'll need to define them as different external tables (each with their own CREATE EXTERNAL TABLE statement).

You could then run queries across multiple tables via JOINs.

Answer 4

With the following scenario.

data1.json:

{"a":"data1","b":"data2"}

data2.json

{"c":"data3","d":"data4"}

you can create the following tables:

create external table data1 (
  a string,
  b string
)
ROW FORMAT  serde 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://bucket/x';
create external table data2 (
  c string,
  d string
)
ROW FORMAT  serde 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://bucket/x';

Now you can query the separate files, defined through separate tables. The only thing you have to take care of is, that the different file types will generate null entries in your result.

select * from data1 where a is not null;
select * from data2 where c is not null;