sqlamazon-athena

Athena Query "mismatched input 'partition'" when trying to add partition

Here is my Athena Query. I am trying to add a partition to a table:

ALTER TABLE public_data_scraping_yahoo_finance_pricing_table
ADD PARTITION ("S3_DATE" = '2021-07') 
LOCATION 's3://my-bucket/enriched_data/pricing/2021-07/';

I'm following the documentation as seen here. But I'm getting some sort of syntax error.

When I run it I get:

line 2:5: mismatched input 'partition'. expecting: '.', 'add' (service: amazonathena; status code: 400; error code: invalidrequestexception; request id: 8dd2f1ac-d197-4c4c-b2fc-024383fd30fb; proxy: null)

I have run other queries to add partitions before, and have never had this issue. I would expect this query to create the partition without any issues.

Is someone able to help me identify the issue?

Solution

  • Okay so it turns out the issue was because the partition field I was specifying was wrapped in double quotes "S3_DATE" when it shouldn't be.

    So the correct query would be:

    ALTER TABLE public_data_scraping_yahoo_finance_pricing_table
ADD PARTITION (S3_DATE='2021-07') 
LOCATION 's3://my-bucket/enriched_data/pricing/2021-07/';