I am looking to create a table from an existing table in Amazon Athena. The existing table is partitioned on partition_0, partition_1, and partition_2 (all strings) and I would like this partition to carry over. Here is my code:
CREATE TABLE IF NOT EXISTS newTable AS Select x, partition_0, partition_1, partition_2 FROM existingTable T PARTITIONED BY (partition_0 string, partition_1 string, partition_2 string)
Trying to run this gives me an error at the FROM line, saying "mismatched input 'by'. expecting: '(', ',',"
…. Status code: 400; error code:invalidrequestexception
Not sure what syntax I am missing here.
Advertisement
Answer
This is the syntax for creating new tables:
CREATE TABLE new_table WITH ( format = 'parquet', external_location = 's3://example-bucket/output/', partitioned_by = ARRAY['partition_0', 'partition_1', 'partition_2']) AS SELECT * FROM existing_table
See the documentation for more examples: https://docs.aws.amazon.com/athena/latest/ug/ctas-examples.html#ctas-example-partitioned