Unable to run "MSCK REPAIR TABLE `xxxx_xxxx_xxxx_xxxx`; on Athena

0

One of my team members created a new data lake in Account B to use in Athena in account A. We get this error below:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask This query ran against the "xxxxxxx" database unless qualified by the query. ...

1 Answer
1

It seems like the data added in the last iteration is not consistent with the schema defined earlier. MSCK REPAIR only adds new partition values. example if you add a file with /month=November/ that would get added as a partition in the catalog. If your new file either has another partition column eg. /country=US/ or if it has newer columns that are inconsistent with existing table definition in the catalog, Athena throws the error:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask This query ran against the "xxxxxxx" database unless qualified by the query.

Please refer to the documentation for MSCK REPAIR.

When you add physical partitions, the metadata in the catalog becomes inconsistent with the layout of the data in the file system, and information about the new partitions needs to be added to the catalog. To update the metadata, run MSCK REPAIR TABLE so that you can query the data in the new partitions from Athena.

profile pictureAWS
answered a year ago
AWS
EXPERT
reviewed a year ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions