[HIVE-23721] MetaStoreDirectSql.ensureDbInit() need to optimize QuerySQL

• Type: Bug
• Status: Patch Available
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.2, 4.0.0
• Component/s: Standalone Metastore
• Labels: pull-request-available

Environment

Hadoop 3.1（1700+ nodes）
YARN 3.1 （with timelineserver enabled，https enabled)
Hive 3.1 (15 HS2 instance)
60000+ YARN Applications every day

• Target Version/s: 4.1.0
• Assignee: Butao Zhang
• Reporter: YulongZ
• Created: 18/Jun/20 04:03
• Updated: 06/Apr/24 10:07

Description

From Hive3.0，catalog added to hivemeta，many schema of metastore added column “catName”，and index for table added column “catName”。

In MetaStoreDirectSql.ensureDbInit() ，two queries below
“
initQueries.add(pm.newQuery(MTableColumnStatistics.class, "dbName == ''"));
initQueries.add(pm.newQuery(MPartitionColumnStatistics.class, "dbName == ''"));
”
should use "catName == ''" instead of "dbName == ''"，because “catName” is the first index column。

When data of metastore become large，for example， table of MPartitionColumnStatistics have millions of lines。The “newQuery(MPartitionColumnStatistics.class, "dbName == ''")” for metastore executed very slowly，and the query “show tables“ for hiveserver2 executed very slowly too。

Issue Links

• is cloned by: HIVE-23719 MetaStoreDirectSql need to optime QuerySQL
• links to: GitHub Pull Request #1202