CODEWITHSUNDEEP
sqlamazon-athenacreate-tablepartition
Runeaway3
Runeaway3

Reputation: 1449

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.

Upvotes: 1

Views: 4334

Answers (1)

Theo
Theo

Reputation: 132972

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

Upvotes: 5

Related Questions