table with different partitions in athena aws

Question

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

Not what you're asking but something similar -> Athena uses Apache Hive–style data partitioning. You can partition your data across multiple dimensions―e.g., month, week, day, hour, or customer ID―or all of them together. aws.amazon.com/blogs/big-data/… — Kyle Bridenstine

Piotr Findeisen Piotr Findeisen · Accepted Answer · 2018-11-12T20:16:02

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.

table with different partitions in athena aws

1 Answers