CODEWITHSUNDEEP
amazon-s3hivepartitionamazon-athenapresto
Bar
Bar

Reputation: 129

table with different partitions in athena aws

Is it possible to create a table with different types of partitions in athena?

for example having a partition per year month day and another partition only by id

    CREATE EXTERNAL TABLE IF NOT EXISTS table_example(
        name string,  
        adress 
        PARTITIONED BY (year string, month string, day string) ----> partition 1
        PARTITIONED BY (id int) -----------> partition 2

ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
LOCATION 's3://example/folder/';

is something similar to this possible?

thanks

Upvotes: 1

Views: 950

Answers (1)

Piotr Findeisen
Piotr Findeisen

Reputation: 20710

No, it is not possible. Partitioning is not "indexing". It's layout of the data on the file system (or file-system-like storage, here: s3). What you're after would be two independent copies of the data. For this, you can simply create two tables, one with partitioning by year/month/day and second with partitioning by id.

However, assuming that id is an identifier in your table, you really do not want to partition by id. You might be interested in exploring bucketing by id, though.

Upvotes: 4

Related Questions