How can I troubleshoot the error "FAILED: SemanticException table is not partitioned but partition spec exists" in Athena?

2 minute read
0

When I run ALTER TABLE ADD PARTITION in Amazon Athena, I get this error: "FAILED: SemanticException table is not partitioned but partition spec exists".

Resolution

This error happens if you didn't define any partitions in the CREATE TABLE statement. To resolve this error, take one of the following actions:

  • Re-create the table and use PARTIONED BY to define the partition key.
  • Edit the table schema.

Re-create the table

Create the table again and use PARTITIONED BY to define the partition key. For an example, see Create the table. After you define the partition, you can use ALTER TABLE ADD PARTITION to add more partitions.

For example, if you're using the following data definition language (DDL) to create the table with three partitions for year, month, and day:

CREATE EXTERNAL TABLE test (
requestBeginTime string,
adId string,
...)
PARTITIONED BY (
year string,
month string,
day string
)
ROW FORMAT serde 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 's3://.../' ;

Then, add the partitions similar to the following ones:

ALTER TABLE impressions ADD
PARTITION (year = '2016', month = '05', day='04') LOCATION 's3://mystorage/path/to/data_14_May_2016/';

Edit the table schema

To edit the table schema in AWS Glue, complete the following steps:

  1. Open the AWS Glue console.
  2. Choose the table name in the list, and then choose Edit schema.
  3. Choose Add column.
  4. Enter the column name, type, and number. Then, check the Partition key box.
  5. Choose Add.

For more information, see Viewing and editing table details.

AWS OFFICIAL
AWS OFFICIALUpdated 2 years ago