Athena query fail with the error HIVE_PARTITION_SCHEMA_MISMATCH can be resolved with help from our experts. 

At Bobcares, we offer solutions for every query, big and small, as a part of our AWS Support Services.

Let’s take a look at how our Support Team is ready to help customers when Athena query fails with the error HIVE_PARTITION_SCHEMA_MISMATCH.

Fix it: Athena query fail with the error HIVE_PARTITION_SCHEMA_MISMATCH

If your Athena query has been failing when you run it on a partitioned table, our Support Engineers can help you out. The HIVE_PARTITION_SCHEMA_MISMATCH error usually occurs when the data type for a column in the partition scheme mismatches with the corresponding column data type in the table schema.

Our Support Techs have come up with two different ways to resolve this error:

• Configure the AWS Glue crawler to update the partition schema if the AWS Glue crawler was used to create the table.
• Use an Athena data definition language statement to remove the affected partition and recreate the partition if the table was created manually

How to configure the AWS Glue crawler to update the partition schema

1. First, open the AWS Glue console and select Crawlers from the navigation pane.
2. Then, choose the crawler that we plan on configuring.
3. Next, we have to select Edit crawler and choose Action.
4. After that, move on to the Choose the crawler’s output page and select
   Next.
5. Now, it is time to expand Configuration options. And select Update all new and existing partitions with metadata from the table .
6. Next, we have to click Next and select Finish to save the new crawler configuration.
7. Then, select the crawler from the previous step on the Crawlerspage.
8. Finally, select Run crawler. When we run the crawler, the partitions will ultimately inherit the table schema.

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

1. First, we will open the Amazon Athena console.
2. Next, we have to run the ALTER TABLE DROP PARTITION command via the Query editor in order to drop the affected partition.
3. Finally, run the ALTER TABLE ADD PARTITION command in order to recreate the removed partition.

[Looking for a solution to another query? We are just a click away.]

Conclusion

In essence, our skilled Support Engineers at Bobcares demonstrated what to do when Athena query fails with the error HIVE_PARTITION_SCHEMA_MISMATCH.