HIVE_INVALID_METADATA: Hive metadata for table region_us_east_2 is invalid: Table descriptor contains duplicate column

Question

I have SSM/S3 "Resource Data Sync" enabled . 
We are using this data in Athena Query however soe some query we get error "HIVE_INVALID_METADATA: Hive metadata for table region_us_east_2 is invalid: Table descriptor contains duplicate column" . Some query works.

Example - If I query "billiginfo" from table it works however if I query "Instanceinfo' or "region" it does not work.

Answer

When you create an Inventory Resource Data Sync, it create a Glue database and the Amazon Athena queries are run against this database tables.

(-) https://docs.aws.amazon.com/systems-manager/latest/userguide/systems-manager-inventory-query.html

The "**Table descriptor contains duplicate column**" error indicates that there is a duplicate column name in Glue table schema.

(-) https://repost.aws/knowledge-center/athena-hive-invalid-metadata-duplicate

In order to fix the issue, 
1. Navigate to AWS Glue console --> Databases where you will find database with a name similar to the Resource Data Sync name
2. Inside the database, find the table which is giving the error while querying through Athena (For Eg : AWS:InstanceInformation - Look at the "Location" column in the database)
3. Click "Edit Schema" for the particular table and review the column names. You will find any one column being repeated twice.
4. Delete one duplicate column
5. Re-run the Athena query

HIVE_INVALID_METADATA: Hive metadata for table region_us_east_2 is invalid: Table descriptor contains duplicate column

Contenuto pertinente