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

2 minute read
0

When I run my Amazon Athena query on a partitioned table, the query fails with the error "HIVE_PARTITION_SCHEMA_MISMATCH."

Short description

You might receive the "HIVE_PARTITION_SCHEMA_MISMATCH" error in the following scenarios:

  • The table schema of the partitions isn't compatible with the table schema.
  • The table's data format doesn't allow the type of update that you tried.

For more information see, Updates in tables with partitions.

Resolution

Configure the AWS Glue crawler to update the partition schema

If you used an AWS Glue crawler to create the table, then configure the AWS Glue crawler to update the partition schema.

Complete the following steps:

  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, and then continue to 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.
  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

If you manually created the table, then use an Athena data definition language (DDL) statement to drop the affected partition, and then recreate the partition.

Complete the following steps:

  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, then 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

AWS OFFICIAL
AWS OFFICIALUpdated 2 months ago