[jira] [Assigned] (HIVE-22271) Create index on the TBL_COL_PRIVS table for the columns COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID

["wenjun ma (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-22271?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

wenjun ma reassigned HIVE-22271:
--------------------------------

    Assignee: wenjun ma

> Create index on the TBL_COL_PRIVS table for the columns COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID
> --------------------------------------------------------------------------------------------------------------
>
>                 Key: HIVE-22271
>                 URL: https://issues.apache.org/jira/browse/HIVE-22271
>             Project: Hive
>          Issue Type: Bug
>          Components: Metastore
>            Reporter: Marta Kuczora
>            Assignee: wenjun ma
>            Priority: Major
>
> In one of the escalations for HDP-3.1.0 we found that the table privilege checks could be very slow and these checks could be speed up by defining an INDEX on the TBL_COL_PRIVS table for the following columns: COLUMN_NAME,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_ID
> In the MYSQL slow query log, we found that the following query is executed slowly:
> {noformat}
> SELECT DISTINCT 'org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege' AS `NUCLEUS_TYPE`,`A0`.`AUTHORIZER`,`A0`.`COLUMN_NAME`,`A0`.`CREATE_TIME`,`A0`.`GRANT_OPTION`,`A0`.`GRANTOR`,`A0`.`GRANTOR_TYPE`,`A0`.`PRINCIPAL_NAME`,`A0`.`PRINCIPAL_TYPE`,`A0`.`TBL_COL_PRIV`,`A0`.`TBL_COLUMN_GRANT_ID` FROM `TBL_COL_PRIVS` `A0` LEFT OUTER JOIN `TBLS` `B0` ON `A0`.`TBL_ID` = `B0`.`TBL_ID` LEFT OUTER JOIN `DBS` `C0` ON `B0`.`DB_ID` = `C0`.`DB_ID` WHERE `A0`.`PRINCIPAL_NAME` = 'xxx' AND `A0`.`PRINCIPAL_TYPE` = 'GROUP' AND `B0`.`TBL_NAME` = 'xxxx' AND `C0`.`NAME` = 'xxx' AND `C0`.`CTLG_NAME` = 'xxx' AND `A0`.`COLUMN_NAME` = 'xxx'
> {noformat}
> When checked the explain plan of the this query, it could be seen that the index defined on the TBL_COL_PRIVS table is not used. In the slow query, the COLUMN_NAME, PRINCIPAL_NAME, PRINCIPAL_TYPE and TBL_ID columns were used, and after creating an index on these columns only, we saw significant performance improvement.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)