Skip to content
Advertisement

How to create partitioned table from other tables in Amazon Athena?

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement