HIVE-16711: Remove property_id column from metastore_db_properties table (Vihang Karajgaonkar, reviewed by Sergio Pena, Naveen Gangam)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/952fe6e1 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/952fe6e1 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/952fe6e1 Branch: refs/heads/hive-14535 Commit: 952fe6e17e8418515caf33de96e33ff16711265f Parents: 1e086f2 Author: Vihang Karajgaonkar <[email protected]> Authored: Mon May 22 09:56:37 2017 -0500 Committer: Sergio Pena <[email protected]> Committed: Mon May 22 09:56:37 2017 -0500 ---------------------------------------------------------------------- .../test/java/org/apache/hive/beeline/TestSchemaTool.java | 4 ++-- metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql | 6 ++---- .../scripts/upgrade/derby/hive-schema-3.0.0.derby.sql | 6 ++---- metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql | 5 +---- .../scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql | 5 +---- metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql | 4 +--- .../scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql | 4 +--- .../scripts/upgrade/oracle/041-HIVE-16556.oracle.sql | 5 +---- .../scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql | 4 +--- .../scripts/upgrade/postgres/040-HIVE-16556.postgres.sql | 6 +----- .../upgrade/postgres/hive-schema-3.0.0.postgres.sql | 5 +---- metastore/src/model/package.jdo | 10 ++-------- 12 files changed, 16 insertions(+), 48 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestSchemaTool.java ---------------------------------------------------------------------- diff --git a/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestSchemaTool.java b/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestSchemaTool.java index 438a7d6..32f5903 100644 --- a/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestSchemaTool.java +++ b/itests/hive-unit/src/test/java/org/apache/hive/beeline/TestSchemaTool.java @@ -692,8 +692,8 @@ public class TestSchemaTool extends TestCase { assertTrue(isValid); // adding same property key twice should throw unique key constraint violation exception String[] scripts = new String[] { - "insert into METASTORE_DB_PROPERTIES values (1, 'guid', 'test-uuid-1', 'dummy uuid 1')", - "insert into METASTORE_DB_PROPERTIES values (2, 'guid', 'test-uuid-2', 'dummy uuid 2')", }; + "insert into METASTORE_DB_PROPERTIES values ('guid', 'test-uuid-1', 'dummy uuid 1')", + "insert into METASTORE_DB_PROPERTIES values ('guid', 'test-uuid-2', 'dummy uuid 2')", }; File scriptFile = generateTestScript(scripts); Exception ex = null; try { http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql b/metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql index ea5f24a..5c6b5f7 100644 --- a/metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql +++ b/metastore/scripts/upgrade/derby/041-HIVE-16556.derby.sql @@ -1,5 +1,3 @@ -CREATE TABLE "APP"."METASTORE_DB_PROPERTIES" ("PROPERTY_ID" BIGINT NOT NULL, "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000)); +CREATE TABLE "APP"."METASTORE_DB_PROPERTIES" ("PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000)); -ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "UNIQUE_PROPERTY_KEY" UNIQUE ("PROPERTY_KEY"); - -ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_ID_PK" PRIMARY KEY ("PROPERTY_ID"); +ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_KEY_PK" PRIMARY KEY ("PROPERTY_KEY"); http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/derby/hive-schema-3.0.0.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/hive-schema-3.0.0.derby.sql b/metastore/scripts/upgrade/derby/hive-schema-3.0.0.derby.sql index ee04bfd..0226d6f 100644 --- a/metastore/scripts/upgrade/derby/hive-schema-3.0.0.derby.sql +++ b/metastore/scripts/upgrade/derby/hive-schema-3.0.0.derby.sql @@ -108,7 +108,7 @@ CREATE TABLE "APP"."NOTIFICATION_SEQUENCE" ("NNI_ID" BIGINT NOT NULL, "NEXT_EVEN CREATE TABLE "APP"."KEY_CONSTRAINTS" ("CHILD_CD_ID" BIGINT, "CHILD_INTEGER_IDX" INTEGER NOT NULL, "CHILD_TBL_ID" BIGINT, "PARENT_CD_ID" BIGINT NOT NULL, "PARENT_INTEGER_IDX" INTEGER, "PARENT_TBL_ID" BIGINT NOT NULL, "POSITION" BIGINT NOT NULL, "CONSTRAINT_NAME" VARCHAR(400) NOT NULL, "CONSTRAINT_TYPE" SMALLINT NOT NULL, "UPDATE_RULE" SMALLINT, "DELETE_RULE" SMALLINT, "ENABLE_VALIDATE_RELY" SMALLINT NOT NULL); -CREATE TABLE "APP"."METASTORE_DB_PROPERTIES" ("PROPERTY_ID" BIGINT NOT NULL, "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000)); +CREATE TABLE "APP"."METASTORE_DB_PROPERTIES" ("PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000)); -- ---------------------------------------------- -- DDL Statements for indexes @@ -243,9 +243,7 @@ ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "NOTIFICATION_SEQUENCE_ ALTER TABLE "APP"."KEY_CONSTRAINTS" ADD CONSTRAINT "CONSTRAINTS_PK" PRIMARY KEY ("CONSTRAINT_NAME", "POSITION"); -ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_ID_PK" PRIMARY KEY ("PROPERTY_ID"); - -ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "UNIQUE_PROPERTY_KEY" UNIQUE ("PROPERTY_KEY"); +ALTER TABLE "APP"."METASTORE_DB_PROPERTIES" ADD CONSTRAINT "PROPERTY_KEY_PK" PRIMARY KEY ("PROPERTY_KEY"); -- foreign ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK1" FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION; http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql b/metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql index 088e702..869404e 100644 --- a/metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql +++ b/metastore/scripts/upgrade/mssql/026-HIVE-16556.mssql.sql @@ -1,10 +1,7 @@ CREATE TABLE METASTORE_DB_PROPERTIES ( - PROPERTY_ID BIGINT NOT NULL, PROPERTY_KEY VARCHAR(255) NOT NULL, PROPERTY_VALUE VARCHAR(1000) NOT NULL, DESCRIPTION VARCHAR(1000) ); -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT METASTORE_DB_PROPERTIES_PK PRIMARY KEY (PROPERTY_ID); - -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT UNIQUE_PROPERTY_KEY UNIQUE (PROPERTY_KEY); +ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_KEY_PK PRIMARY KEY (PROPERTY_KEY); http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql b/metastore/scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql index 416f3d9..d7bd3f8 100644 --- a/metastore/scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql +++ b/metastore/scripts/upgrade/mssql/hive-schema-3.0.0.mssql.sql @@ -1017,15 +1017,12 @@ CREATE TABLE WRITE_SET ( ); CREATE TABLE METASTORE_DB_PROPERTIES ( - PROPERTY_ID BIGINT NOT NULL, PROPERTY_KEY VARCHAR(255) NOT NULL, PROPERTY_VALUE VARCHAR(1000) NOT NULL, DESCRIPTION VARCHAR(1000) ); -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT METASTORE_DB_PROPERTIES_PK PRIMARY KEY (PROPERTY_ID); - -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT UNIQUE_PROPERTY_KEY UNIQUE (PROPERTY_KEY); +ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_KEY_PK PRIMARY KEY (PROPERTY_KEY); -- ----------------------------------------------------------------- -- Record schema version. Should be the last step in the init script http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql b/metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql index 8afc196..f8b97bb 100644 --- a/metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql +++ b/metastore/scripts/upgrade/mysql/041-HIVE-16556.mysql.sql @@ -2,10 +2,8 @@ -- Table structure for table METASTORE_DB_PROPERTIES -- CREATE TABLE IF NOT EXISTS `METASTORE_DB_PROPERTIES` ( - `PROPERTY_ID` BIGINT(20) NOT NULL, `PROPERTY_KEY` varchar(255) NOT NULL, `PROPERTY_VALUE` varchar(1000) NOT NULL, `DESCRIPTION` varchar(1000), - PRIMARY KEY(`PROPERTY_ID`), - UNIQUE KEY `UNIQUE_PROPERTY_KEY` (`PROPERTY_KEY`) + PRIMARY KEY(`PROPERTY_KEY`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql b/metastore/scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql index 09b8cf0..ff874bb 100644 --- a/metastore/scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql +++ b/metastore/scripts/upgrade/mysql/hive-schema-3.0.0.mysql.sql @@ -833,12 +833,10 @@ CREATE INDEX `CONSTRAINTS_PARENT_TABLE_ID_INDEX` ON KEY_CONSTRAINTS (`PARENT_TBL -- Metastore DB Properties table -- ----------------------------- CREATE TABLE IF NOT EXISTS `METASTORE_DB_PROPERTIES` ( - `PROPERTY_ID` BIGINT(20) NOT NULL, `PROPERTY_KEY` varchar(255) NOT NULL, `PROPERTY_VALUE` varchar(1000) NOT NULL, `DESCRIPTION` varchar(1000), - PRIMARY KEY(`PROPERTY_ID`), - UNIQUE KEY `UNIQUE_PROPERTY_KEY` (`PROPERTY_KEY`) + PRIMARY KEY(`PROPERTY_KEY`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ---------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/oracle/041-HIVE-16556.oracle.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/oracle/041-HIVE-16556.oracle.sql b/metastore/scripts/upgrade/oracle/041-HIVE-16556.oracle.sql index 14a2bdb..566570c 100644 --- a/metastore/scripts/upgrade/oracle/041-HIVE-16556.oracle.sql +++ b/metastore/scripts/upgrade/oracle/041-HIVE-16556.oracle.sql @@ -1,11 +1,8 @@ CREATE TABLE METASTORE_DB_PROPERTIES ( - PROPERTY_ID NUMBER NOT NULL, PROPERTY_KEY VARCHAR(255) NOT NULL, PROPERTY_VALUE VARCHAR(1000) NOT NULL, DESCRIPTION VARCHAR(1000) ); -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT UNIQUE_PROPERTY_KEY UNIQUE (PROPERTY_KEY); - -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_ID_PK PRIMARY KEY (PROPERTY_ID); +ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_KEY_PK PRIMARY KEY (PROPERTY_KEY); http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql b/metastore/scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql index 0ad28b2..0ca6143 100644 --- a/metastore/scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql +++ b/metastore/scripts/upgrade/oracle/hive-schema-3.0.0.oracle.sql @@ -802,15 +802,13 @@ CREATE INDEX CONSTRAINTS_PT_INDEX ON KEY_CONSTRAINTS(PARENT_TBL_ID); -- Table for METASTORE_DB_PROPERTIES and its constraints CREATE TABLE METASTORE_DB_PROPERTIES ( - PROPERTY_ID NUMBER NOT NULL, PROPERTY_KEY VARCHAR(255) NOT NULL, PROPERTY_VALUE VARCHAR(1000) NOT NULL, DESCRIPTION VARCHAR(1000) ); -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT UNIQUE_PROPERTY_KEY UNIQUE (PROPERTY_KEY); +ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_KEY_PK PRIMARY KEY (PROPERTY_KEY); -ALTER TABLE METASTORE_DB_PROPERTIES ADD CONSTRAINT PROPERTY_ID_PK PRIMARY KEY (PROPERTY_ID); ------------------------------ -- Transaction and lock tables ------------------------------ http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/postgres/040-HIVE-16556.postgres.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/postgres/040-HIVE-16556.postgres.sql b/metastore/scripts/upgrade/postgres/040-HIVE-16556.postgres.sql index 9a3ff03..41f5336 100644 --- a/metastore/scripts/upgrade/postgres/040-HIVE-16556.postgres.sql +++ b/metastore/scripts/upgrade/postgres/040-HIVE-16556.postgres.sql @@ -1,13 +1,9 @@ CREATE TABLE "METASTORE_DB_PROPERTIES" ( - "PROPERTY_ID" BIGINT NOT NULL, "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000) ); ALTER TABLE ONLY "METASTORE_DB_PROPERTIES" - ADD CONSTRAINT "UNIQUE_PROPERTY_KEY" UNIQUE ("PROPERTY_KEY"); - -ALTER TABLE ONLY "METASTORE_DB_PROPERTIES" - ADD CONSTRAINT "METASTORE_DB_PROPERTIES_pkey" PRIMARY KEY ("PROPERTY_ID"); + ADD CONSTRAINT "PROPERTY_KEY_PK" PRIMARY KEY ("PROPERTY_KEY"); http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/scripts/upgrade/postgres/hive-schema-3.0.0.postgres.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/postgres/hive-schema-3.0.0.postgres.sql b/metastore/scripts/upgrade/postgres/hive-schema-3.0.0.postgres.sql index 89183e7..91a71f5 100644 --- a/metastore/scripts/upgrade/postgres/hive-schema-3.0.0.postgres.sql +++ b/metastore/scripts/upgrade/postgres/hive-schema-3.0.0.postgres.sql @@ -609,7 +609,6 @@ CREATE TABLE "KEY_CONSTRAINTS" --- CREATE TABLE "METASTORE_DB_PROPERTIES" ( - "PROPERTY_ID" BIGINT NOT NULL, "PROPERTY_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000) @@ -960,10 +959,8 @@ ALTER TABLE ONLY "ROLE_MAP" ADD CONSTRAINT "USERROLEMAPINDEX" UNIQUE ("PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); ALTER TABLE ONLY "METASTORE_DB_PROPERTIES" - ADD CONSTRAINT "UNIQUE_PROPERTY_KEY" UNIQUE ("PROPERTY_KEY"); + ADD CONSTRAINT "PROPERTY_KEY_PK" PRIMARY KEY ("PROPERTY_KEY"); -ALTER TABLE ONLY "METASTORE_DB_PROPERTIES" - ADD CONSTRAINT "METASTORE_DB_PROPERTIES_pkey" PRIMARY KEY ("PROPERTY_ID"); -- -- Name: BUCKETING_COLS_N49; Type: INDEX; Schema: public; Owner: hiveuser; Tablespace: -- http://git-wip-us.apache.org/repos/asf/hive/blob/952fe6e1/metastore/src/model/package.jdo ---------------------------------------------------------------------- diff --git a/metastore/src/model/package.jdo b/metastore/src/model/package.jdo index 9c7c691..87099ef 100644 --- a/metastore/src/model/package.jdo +++ b/metastore/src/model/package.jdo @@ -971,11 +971,8 @@ </field> </class> - <class name="MMetastoreDBProperties" table="METASTORE_DB_PROPERTIES" identity-type="datastore" detachable="true"> - <datastore-identity> - <column name="PROPERTY_ID"/> - </datastore-identity> - <field name ="propertyKey"> + <class name="MMetastoreDBProperties" table="METASTORE_DB_PROPERTIES" identity-type="application" detachable="true"> + <field name ="propertyKey" primary-key="true"> <column name="PROPERTY_KEY" length="255" jdbc-type="VARCHAR" allows-null="false"/> </field> <field name ="propertyValue"> @@ -984,9 +981,6 @@ <field name ="description"> <column name="DESCRIPTION" length="1000" jdbc-type="VARCHAR" allows-null="false"/> </field> - <index name="PropertyKeyIndex" unique="true"> - <column name="PROPERTY_KEY"/> - </index> </class> <class name="MResourceUri" embedded-only="true" table="RESOURCE_URI" detachable="true">
