Repository: hive Updated Branches: refs/heads/branch-3 bdac6edfd -> 566a48db4
HIVE-19605 : TAB_COL_STATS table has no index on db/table name (Vihang Karajgaonkar reviewed by Yongzhi Chen) Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/566a48db Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/566a48db Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/566a48db Branch: refs/heads/branch-3 Commit: 566a48db4958c5dfe2378d5f2a3f439248bcf652 Parents: bdac6ed Author: Vihang Karajgaonkar <[email protected]> Authored: Wed May 30 10:20:20 2018 -0700 Committer: Vihang Karajgaonkar <[email protected]> Committed: Thu Jun 7 10:41:18 2018 -0700 ---------------------------------------------------------------------- .../src/main/sql/derby/hive-schema-3.1.0.derby.sql | 2 ++ .../main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql | 3 +++ .../src/main/sql/mssql/hive-schema-3.1.0.mssql.sql | 1 + .../main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql | 3 +++ .../src/main/sql/mysql/hive-schema-3.1.0.mysql.sql | 1 + .../main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql | 3 +++ .../src/main/sql/oracle/hive-schema-3.1.0.oracle.sql | 14 ++++++++------ .../main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql | 3 +++ .../main/sql/postgres/hive-schema-3.1.0.postgres.sql | 5 +++++ .../sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql | 3 +++ 10 files changed, 32 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/derby/hive-schema-3.1.0.derby.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/derby/hive-schema-3.1.0.derby.sql b/standalone-metastore/src/main/sql/derby/hive-schema-3.1.0.derby.sql index d679658..f52be79 100644 --- a/standalone-metastore/src/main/sql/derby/hive-schema-3.1.0.derby.sql +++ b/standalone-metastore/src/main/sql/derby/hive-schema-3.1.0.derby.sql @@ -231,6 +231,8 @@ CREATE UNIQUE INDEX "APP"."DBPRIVILEGEINDEX" ON "APP"."DB_PRIVS" ("AUTHORIZER", CREATE INDEX "APP"."PCS_STATS_IDX" ON "APP"."PART_COL_STATS" ("CAT_NAME", "DB_NAME","TABLE_NAME","COLUMN_NAME","PARTITION_NAME"); +CREATE INDEX "APP"."TAB_COL_STATS_IDX" ON "APP"."TAB_COL_STATS" ("CAT_NAME", "DB_NAME", "TABLE_NAME", "COLUMN_NAME"); + CREATE INDEX "APP"."PARTPRIVILEGEINDEX" ON "APP"."PART_PRIVS" ("AUTHORIZER", "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); CREATE UNIQUE INDEX "APP"."ROLEENTITYINDEX" ON "APP"."ROLES" ("ROLE_NAME"); http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql b/standalone-metastore/src/main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql index 047abdb..efb970a 100644 --- a/standalone-metastore/src/main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql +++ b/standalone-metastore/src/main/sql/derby/upgrade-3.0.0-to-3.1.0.derby.sql @@ -24,5 +24,8 @@ ALTER TABLE "APP"."PART_COL_PRIVS" ADD "AUTHORIZER" VARCHAR(128); DROP INDEX "APP"."PARTITIONCOLUMNPRIVILEGEINDEX"; CREATE INDEX "APP"."PARTITIONCOLUMNPRIVILEGEINDEX" ON "APP"."PART_COL_PRIVS" ("AUTHORIZER", "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE INDEX "APP"."TAB_COL_STATS_IDX" ON "APP"."TAB_COL_STATS" ("CAT_NAME", "DB_NAME", "TABLE_NAME", "COLUMN_NAME"); + -- This needs to be the last thing done. Insert any changes above this line. UPDATE "APP".VERSION SET SCHEMA_VERSION='3.1.0', VERSION_COMMENT='Hive release version 3.1.0' where VER_ID=1; http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/mssql/hive-schema-3.1.0.mssql.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/mssql/hive-schema-3.1.0.mssql.sql b/standalone-metastore/src/main/sql/mssql/hive-schema-3.1.0.mssql.sql index 1bb3c1a..7daaf6e 100644 --- a/standalone-metastore/src/main/sql/mssql/hive-schema-3.1.0.mssql.sql +++ b/standalone-metastore/src/main/sql/mssql/hive-schema-3.1.0.mssql.sql @@ -246,6 +246,7 @@ CREATE TABLE TAB_COL_STATS ); ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_PK PRIMARY KEY (CS_ID); +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME); -- Table TYPES for classes [org.apache.hadoop.hive.metastore.model.MType] CREATE TABLE TYPES http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql b/standalone-metastore/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql index d3f2794..c0dd467 100644 --- a/standalone-metastore/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql +++ b/standalone-metastore/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql @@ -25,6 +25,9 @@ ALTER TABLE PART_COL_PRIVS ADD AUTHORIZER nvarchar(128) NULL; DROP INDEX PART_COL_PRIVS.PARTITIONCOLUMNPRIVILEGEINDEX; CREATE INDEX PARTITIONCOLUMNPRIVILEGEINDEX ON PART_COL_PRIVS (AUTHORIZER,PART_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_COL_PRIV,GRANTOR,GRANTOR_TYPE); + +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME); + -- These lines need to be last. Insert any changes above. UPDATE VERSION SET SCHEMA_VERSION='3.1.0', VERSION_COMMENT='Hive release version 3.1.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 3.0.0 to 3.1.0' AS MESSAGE; http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/mysql/hive-schema-3.1.0.mysql.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/mysql/hive-schema-3.1.0.mysql.sql b/standalone-metastore/src/main/sql/mysql/hive-schema-3.1.0.mysql.sql index 1f04503..21dcd2c 100644 --- a/standalone-metastore/src/main/sql/mysql/hive-schema-3.1.0.mysql.sql +++ b/standalone-metastore/src/main/sql/mysql/hive-schema-3.1.0.mysql.sql @@ -730,6 +730,7 @@ CREATE TABLE IF NOT EXISTS `TAB_COL_STATS` ( CONSTRAINT `TAB_COL_STATS_FK` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME) USING BTREE; -- -- Table structure for table `PART_COL_STATS` -- http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql b/standalone-metastore/src/main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql index df5485e..e53c9d4 100644 --- a/standalone-metastore/src/main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql +++ b/standalone-metastore/src/main/sql/mysql/upgrade-3.0.0-to-3.1.0.mysql.sql @@ -25,6 +25,9 @@ ALTER TABLE `PART_COL_PRIVS` ADD `AUTHORIZER` varchar(128) CHARACTER SET latin1 ALTER TABLE `PART_COL_PRIVS` DROP INDEX `PARTITIONCOLUMNPRIVILEGEINDEX`; ALTER TABLE `PART_COL_PRIVS` ADD INDEX `PARTITIONCOLUMNPRIVILEGEINDEX` (`AUTHORIZER`,`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`); + +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME) USING BTREE; + -- These lines need to be last. Insert any changes above. UPDATE VERSION SET SCHEMA_VERSION='3.1.0', VERSION_COMMENT='Hive release version 3.1.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 3.0.0 to 3.1.0' AS ' '; http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/oracle/hive-schema-3.1.0.oracle.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/oracle/hive-schema-3.1.0.oracle.sql b/standalone-metastore/src/main/sql/oracle/hive-schema-3.1.0.oracle.sql index 2d9b2b7..c4c138a 100644 --- a/standalone-metastore/src/main/sql/oracle/hive-schema-3.1.0.oracle.sql +++ b/standalone-metastore/src/main/sql/oracle/hive-schema-3.1.0.oracle.sql @@ -534,6 +534,14 @@ CREATE TABLE TAB_COL_STATS ( LAST_ANALYZED NUMBER NOT NULL ); +ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_PKEY PRIMARY KEY (CS_ID); + +ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_FK FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) INITIALLY DEFERRED ; + +CREATE INDEX TAB_COL_STATS_N49 ON TAB_COL_STATS(TBL_ID); + +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME); + CREATE TABLE VERSION ( VER_ID NUMBER NOT NULL, SCHEMA_VERSION VARCHAR(127) NOT NULL, @@ -541,12 +549,6 @@ CREATE TABLE VERSION ( ); ALTER TABLE VERSION ADD CONSTRAINT VERSION_PK PRIMARY KEY (VER_ID); -ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_PKEY PRIMARY KEY (CS_ID); - -ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_FK FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) INITIALLY DEFERRED ; - -CREATE INDEX TAB_COL_STATS_N49 ON TAB_COL_STATS(TBL_ID); - CREATE TABLE PART_COL_STATS ( CS_ID NUMBER NOT NULL, CAT_NAME VARCHAR2(256) NOT NULL, http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql b/standalone-metastore/src/main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql index 6c4c5be..47a80f4 100644 --- a/standalone-metastore/src/main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql +++ b/standalone-metastore/src/main/sql/oracle/upgrade-3.0.0-to-3.1.0.oracle.sql @@ -25,6 +25,9 @@ ALTER TABLE PART_COL_PRIVS ADD AUTHORIZER VARCHAR2(128) NULL; DROP INDEX PARTITIONCOLUMNPRIVILEGEINDEX; CREATE INDEX PARTITIONCOLUMNPRIVILEGEINDEX ON PART_COL_PRIVS (AUTHORIZER,PART_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_COL_PRIV,GRANTOR,GRANTOR_TYPE); + +CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME, COLUMN_NAME); + -- These lines need to be last. Insert any changes above. UPDATE VERSION SET SCHEMA_VERSION='3.1.0', VERSION_COMMENT='Hive release version 3.1.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 3.0.0 to 3.1.0' AS Status from dual; http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/postgres/hive-schema-3.1.0.postgres.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/postgres/hive-schema-3.1.0.postgres.sql b/standalone-metastore/src/main/sql/postgres/hive-schema-3.1.0.postgres.sql index f8a073a..64c97fc 100644 --- a/standalone-metastore/src/main/sql/postgres/hive-schema-3.1.0.postgres.sql +++ b/standalone-metastore/src/main/sql/postgres/hive-schema-3.1.0.postgres.sql @@ -1282,6 +1282,11 @@ CREATE INDEX "TBLS_N50" ON "TBLS" USING btree ("SD_ID"); CREATE INDEX "TBL_COL_PRIVS_N49" ON "TBL_COL_PRIVS" USING btree ("TBL_ID"); +-- +-- Name: TAB_COL_STATS_IDX; Type: INDEX; Schema: public; Owner: hiveuser; Tablespace: +-- + +CREATE INDEX "TAB_COL_STATS_IDX" ON "TAB_COL_STATS" USING btree ("CAT_NAME", "DB_NAME","TABLE_NAME","COLUMN_NAME"); -- -- Name: TBL_PRIVS_N49; Type: INDEX; Schema: public; Owner: hiveuser; Tablespace: http://git-wip-us.apache.org/repos/asf/hive/blob/566a48db/standalone-metastore/src/main/sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql ---------------------------------------------------------------------- diff --git a/standalone-metastore/src/main/sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql b/standalone-metastore/src/main/sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql index 81f695c..9d7ce09 100644 --- a/standalone-metastore/src/main/sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql +++ b/standalone-metastore/src/main/sql/postgres/upgrade-3.0.0-to-3.1.0.postgres.sql @@ -27,6 +27,9 @@ ALTER TABLE "PART_COL_PRIVS" ADD COLUMN "AUTHORIZER" character varying(128) DEFA DROP INDEX "PARTITIONCOLUMNPRIVILEGEINDEX"; CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" USING btree ("AUTHORIZER", "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +CREATE INDEX "TAB_COL_STATS_IDX" ON "TAB_COL_STATS" USING btree ("CAT_NAME", "DB_NAME","TABLE_NAME","COLUMN_NAME"); + -- These lines need to be last. Insert any changes above. UPDATE "VERSION" SET "SCHEMA_VERSION"='3.1.0', "VERSION_COMMENT"='Hive release version 3.1.0' where "VER_ID"=1; SELECT 'Finished upgrading MetaStore schema from 3.0.0 to 3.1.0';
