[HIVE-24815] Remove "IDXS" Table from Metastore Schema

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.0, 3.0.0, 3.1.1, 3.1.2, 3.2.0, 4.0.0
• Fix Version/s: 4.0.0
• Component/s: Metastore, Standalone Metastore
• Labels: pull-request-available
• Target Version/s: 3.2.0, 4.0.0
• Assignee: Riju Trivedi
• Reporter: Hunter Logan
• Created: 23/Feb/21 15:51
• Updated: 29/Mar/24 17:50
• Resolved: 02/Nov/23 23:52

Description

In Hive 3 the rarely used "INDEXES" was removed from the DDL

https://issues.apache.org/jira/browse/HIVE-18448

 

There are a few issues here:

1. The Standalone-Metastore schema for Hive 3+ all include the "IDXS" table, which has no function.
   • https://github.com/apache/hive/tree/master/standalone-metastore/metastore-server/src/main/sql/mysql
2. The upgrade schemas from 2.x -> 3.x do not do any cleanup of the IDXS table
   • If a user used the "INDEXES" feature in 2.x and then upgrades their metastore to 3.x+ they cannot drop any table that has an index on it due to "IDXS_FK1" constraint since the TBLS entry is referenced in the IDXS table
   • Since INDEX is no longer in the DDL they cannot run any command from Hive to drop the index.
   • Users can manually connect to the metastore and either drop the IDXS table or the foreign key constraint

 

Since indexes provide no benefits in Hive 3+ it should be fine to drop them completely in the schema upgrade scripts. At the very least the 2.x -> 3.x+ scripts should drop the fk constraint.

Issue Links

• relates to: HIVE-18448 Drop Support For Indexes From Apache Hive
• links to: GitHub Pull Request #2010
• links to: GitHub Pull Request #4370