Why does my Athena query fail with the error "HIVE_PARTITION_SCHEMA_MISMATCH"?

Short description

When you run a query on an Athena partitioned table, Athena validates the table schema and the schema of its partitions in the AWS Glue Data Catalog. The validation compares the column data types in order and makes sure that the column data types match for the columns that overlap. A new partition usually inherits the schema of the associated table. However, certain scenarios can cause the schemas to differ over time. When the data type for a column in the partition schema does not match the corresponding column data type in the table schema, your query fails with the error "HIVE_PARTITION_SCHEMA_MISMATCH."

You can resolve this error with either of the following approaches:

• If you used an AWS Glue crawler to create the table, then configure the AWS Glue crawler to update the partition schema.
• If you created the table manually, then use an Athena data definition language (DDL) statement to drop the affected partition and recreate the partition.

Resolution

Configure the AWS Glue crawler to update the partition schema

1. Open the AWS Glue console.
2. On the navigation pane, choose Crawlers.
3. Select the crawler that you want to configure.
4. Choose Action, and then choose Edit crawler.
5. Choose Next until you navigate to the Choose the crawler's output page.
6. Expand Configuration options.
7. Select Update all new and existing partitions with metadata from the table.
8. Choose Next, and then choose Finish to save the crawler configuration.
9. On the Crawlers page, select the crawler that you edited.
10. Choose Run crawler. When you run the crawler, the partitions inherit the table schema.

Use an Athena DDL statement to drop the affected partition and recreate the dropped partition

1. Open the Amazon Athena console.
2. On the Query editor tab, run the ALTER TABLE DROP PARTITION command to drop the affected partition.
3. Run the ALTER TABLE ADD PARTITION command to recreate the dropped partition.
   Note: If your table uses Hive-compatible partitions, you can run the MSCK REPAIR TABLE command to recreate the dropped partition.

Related information

Syncing partition schema to avoid "HIVE_PARTITION_SCHEMA_MISMATCH"

Setting crawler configuration options on the AWS Glue console