HIVE-16575: Support for 'UNIQUE' and 'NOT NULL' constraints (Jesus Camacho Rodriguez, reviewed by Ashutosh Chauhan)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/696be9f5 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/696be9f5 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/696be9f5 Branch: refs/heads/master Commit: 696be9f52dfc6fb59c24de19726b4460100fc9ba Parents: 823f01c Author: Jesus Camacho Rodriguez <jcama...@apache.org> Authored: Wed May 3 10:09:49 2017 +0100 Committer: Jesus Camacho Rodriguez <jcama...@apache.org> Committed: Thu May 25 11:26:22 2017 +0100 ---------------------------------------------------------------------- .../listener/DummyRawStoreFailEvent.java | 28 +- .../org/apache/hive/jdbc/TestJdbcDriver2.java | 2 +- .../org/apache/hadoop/hive/ql/QTestUtil.java | 2 + metastore/if/hive_metastore.thrift | 61 +- .../upgrade/derby/042-HIVE-16575.derby.sql | 4 + .../upgrade/derby/hive-schema-3.0.0.derby.sql | 4 +- .../derby/upgrade-2.3.0-to-3.0.0.derby.sql | 2 +- .../upgrade/hive/hive-schema-3.0.0.hive.sql | 78 +- .../upgrade/mssql/027-HIVE-16575.mssql.sql | 1 + .../upgrade/mssql/hive-schema-3.0.0.mssql.sql | 2 + .../mssql/upgrade-2.3.0-to-3.0.0.mssql.sql | 1 + .../upgrade/mysql/042-HIVE-16575.mysql.sql | 1 + .../upgrade/mysql/hive-schema-3.0.0.mysql.sql | 2 + .../mysql/upgrade-2.3.0-to-3.0.0.mysql.sql | 1 + .../upgrade/oracle/042-HIVE-16575.oracle.sql | 1 + .../upgrade/oracle/hive-schema-3.0.0.oracle.sql | 2 + .../oracle/upgrade-2.3.0-to-3.0.0.oracle.sql | 1 + .../postgres/041-HIVE-16575.postgres.sql | 1 + .../postgres/hive-schema-3.0.0.postgres.sql | 2 + .../upgrade-2.3.0-to-3.0.0.postgres.sql | 1 + .../metastore/hbase/HbaseMetastoreProto.java | 4700 +- .../gen/thrift/gen-cpp/ThriftHiveMetastore.cpp | 5850 ++- .../gen/thrift/gen-cpp/ThriftHiveMetastore.h | 584 +- .../ThriftHiveMetastore_server.skeleton.cpp | 22 +- .../gen/thrift/gen-cpp/hive_metastore_types.cpp | 7378 +-- .../gen/thrift/gen-cpp/hive_metastore_types.h | 436 + .../hive/metastore/api/AbortTxnsRequest.java | 32 +- .../metastore/api/AddDynamicPartitions.java | 32 +- .../metastore/api/AddForeignKeyRequest.java | 36 +- .../api/AddNotNullConstraintRequest.java | 443 + .../metastore/api/AddPartitionsRequest.java | 36 +- .../hive/metastore/api/AddPartitionsResult.java | 36 +- .../metastore/api/AddPrimaryKeyRequest.java | 36 +- .../api/AddUniqueConstraintRequest.java | 443 + .../metastore/api/ClearFileMetadataRequest.java | 32 +- .../hive/metastore/api/ClientCapabilities.java | 32 +- .../hive/metastore/api/CompactionRequest.java | 44 +- .../metastore/api/DropPartitionsResult.java | 36 +- .../hive/metastore/api/FireEventRequest.java | 32 +- .../hadoop/hive/metastore/api/Function.java | 36 +- .../metastore/api/GetAllFunctionsResponse.java | 36 +- .../api/GetFileMetadataByExprRequest.java | 32 +- .../api/GetFileMetadataByExprResult.java | 48 +- .../metastore/api/GetFileMetadataRequest.java | 32 +- .../metastore/api/GetFileMetadataResult.java | 44 +- .../metastore/api/GetOpenTxnsInfoResponse.java | 36 +- .../hive/metastore/api/GetOpenTxnsResponse.java | 32 +- .../hive/metastore/api/GetTablesRequest.java | 32 +- .../hive/metastore/api/GetTablesResult.java | 36 +- .../api/HeartbeatTxnRangeResponse.java | 64 +- .../metastore/api/InsertEventRequestData.java | 64 +- .../hadoop/hive/metastore/api/LockRequest.java | 36 +- .../api/NotNullConstraintsRequest.java | 490 + .../api/NotNullConstraintsResponse.java | 443 + .../api/NotificationEventResponse.java | 36 +- .../hive/metastore/api/OpenTxnsResponse.java | 32 +- .../metastore/api/PartitionsByExprResult.java | 36 +- .../metastore/api/PartitionsStatsRequest.java | 64 +- .../metastore/api/PartitionsStatsResult.java | 76 +- .../metastore/api/PutFileMetadataRequest.java | 64 +- .../hive/metastore/api/RequestPartsSpec.java | 68 +- .../metastore/api/SQLNotNullConstraint.java | 1005 + .../hive/metastore/api/SQLUniqueConstraint.java | 1103 + .../hive/metastore/api/ShowCompactResponse.java | 36 +- .../hive/metastore/api/ShowLocksResponse.java | 36 +- .../hive/metastore/api/TableStatsRequest.java | 32 +- .../hive/metastore/api/TableStatsResult.java | 36 +- .../hive/metastore/api/ThriftHiveMetastore.java | 43424 +++++++++-------- .../metastore/api/UniqueConstraintsRequest.java | 490 + .../api/UniqueConstraintsResponse.java | 443 + .../gen-php/metastore/ThriftHiveMetastore.php | 2554 +- .../src/gen/thrift/gen-php/metastore/Types.php | 9511 ++-- .../hive_metastore/ThriftHiveMetastore-remote | 36 +- .../hive_metastore/ThriftHiveMetastore.py | 2096 +- .../gen/thrift/gen-py/hive_metastore/ttypes.py | 2207 +- .../gen/thrift/gen-rb/hive_metastore_types.rb | 166 + .../gen/thrift/gen-rb/thrift_hive_metastore.rb | 274 +- .../hadoop/hive/metastore/HiveMetaStore.java | 128 +- .../hive/metastore/HiveMetaStoreClient.java | 37 +- .../hadoop/hive/metastore/IMetaStoreClient.java | 21 +- .../hive/metastore/MetaStoreDirectSql.java | 111 +- .../hadoop/hive/metastore/ObjectStore.java | 324 +- .../apache/hadoop/hive/metastore/RawStore.java | 15 +- .../hive/metastore/cache/CachedStore.java | 37 +- .../hive/metastore/hbase/HBaseReadWrite.java | 76 +- .../hadoop/hive/metastore/hbase/HBaseStore.java | 103 +- .../hadoop/hive/metastore/hbase/HBaseUtils.java | 131 +- .../hive/metastore/model/MConstraint.java | 4 + .../metastore/hbase/hbase_metastore_proto.proto | 34 + .../DummyRawStoreControlledCommit.java | 32 +- .../DummyRawStoreForJdoConnection.java | 31 +- .../hive/metastore/hbase/TestHBaseStore.java | 138 +- .../org/apache/hadoop/hive/ql/ErrorMsg.java | 4 +- .../org/apache/hadoop/hive/ql/exec/DDLTask.java | 78 +- .../apache/hadoop/hive/ql/metadata/Hive.java | 73 +- .../hive/ql/metadata/NotNullConstraint.java | 86 + .../hive/ql/metadata/UniqueConstraint.java | 111 + .../formatting/JsonMetaDataFormatter.java | 12 +- .../formatting/MetaDataFormatUtils.java | 63 +- .../metadata/formatting/MetaDataFormatter.java | 8 +- .../formatting/TextMetaDataFormatter.java | 44 +- .../hive/ql/parse/BaseSemanticAnalyzer.java | 447 +- .../hive/ql/parse/DDLSemanticAnalyzer.java | 85 +- .../org/apache/hadoop/hive/ql/parse/HiveLexer.g | 1 + .../apache/hadoop/hive/ql/parse/HiveParser.g | 158 +- .../hadoop/hive/ql/parse/SemanticAnalyzer.java | 12 +- .../hadoop/hive/ql/plan/AlterTableDesc.java | 44 +- .../hadoop/hive/ql/plan/CreateTableDesc.java | 41 +- .../hadoop/hive/ql/plan/ImportTableDesc.java | 6 +- .../hive/ql/parse/TestHiveDecimalParse.java | 2 +- .../TestSQL11ReservedKeyWordsNegative.java | 13 + .../alter_table_constraint_duplicate_pk.q | 2 +- .../alter_table_constraint_invalid_fk_col1.q | 4 +- .../alter_table_constraint_invalid_fk_col2.q | 4 +- .../alter_table_constraint_invalid_fk_tbl1.q | 4 +- .../alter_table_constraint_invalid_fk_tbl2.q | 4 +- .../alter_table_constraint_invalid_pk_tbl.q | 2 +- .../create_with_constraints_duplicate_name.q | 4 +- .../create_with_constraints_enable.q | 2 +- .../clientnegative/create_with_fk_constraint.q | 2 + .../create_with_multi_pk_constraint.q | 1 + .../clientnegative/drop_invalid_constraint1.q | 2 +- .../clientnegative/drop_invalid_constraint2.q | 2 +- .../clientnegative/drop_invalid_constraint3.q | 2 +- .../clientnegative/drop_invalid_constraint4.q | 4 +- .../clientpositive/create_with_constraints.q | 84 +- .../alter_table_constraint_duplicate_pk.q.out | 4 +- ...alter_table_constraint_invalid_fk_col1.q.out | 8 +- ...alter_table_constraint_invalid_fk_col2.q.out | 8 +- ...alter_table_constraint_invalid_fk_tbl1.q.out | 8 +- ...alter_table_constraint_invalid_fk_tbl2.q.out | 8 +- .../alter_table_constraint_invalid_pk_tbl.q.out | 4 +- ...create_with_constraints_duplicate_name.q.out | 6 +- .../create_with_constraints_enable.q.out | 2 +- .../create_with_constraints_validate.q.out | 2 +- .../create_with_fk_constraint.q.out | 13 + .../create_with_multi_pk_constraint.q.out | 1 + .../drop_invalid_constraint1.q.out | 4 +- .../drop_invalid_constraint2.q.out | 4 +- .../drop_invalid_constraint3.q.out | 4 +- .../drop_invalid_constraint4.q.out | 8 +- .../create_with_constraints.q.out | 1241 +- .../results/clientpositive/llap/sysdb.q.out | 152 +- 143 files changed, 57491 insertions(+), 32342 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/itests/hcatalog-unit/src/test/java/org/apache/hive/hcatalog/listener/DummyRawStoreFailEvent.java ---------------------------------------------------------------------- diff --git a/itests/hcatalog-unit/src/test/java/org/apache/hive/hcatalog/listener/DummyRawStoreFailEvent.java b/itests/hcatalog-unit/src/test/java/org/apache/hive/hcatalog/listener/DummyRawStoreFailEvent.java index 3dc63bd..b016920 100644 --- a/itests/hcatalog-unit/src/test/java/org/apache/hive/hcatalog/listener/DummyRawStoreFailEvent.java +++ b/itests/hcatalog-unit/src/test/java/org/apache/hive/hcatalog/listener/DummyRawStoreFailEvent.java @@ -55,7 +55,9 @@ import org.apache.hadoop.hive.metastore.api.PrivilegeBag; import org.apache.hadoop.hive.metastore.api.Role; import org.apache.hadoop.hive.metastore.api.RolePrincipalGrant; import org.apache.hadoop.hive.metastore.api.SQLForeignKey; +import org.apache.hadoop.hive.metastore.api.SQLNotNullConstraint; import org.apache.hadoop.hive.metastore.api.SQLPrimaryKey; +import org.apache.hadoop.hive.metastore.api.SQLUniqueConstraint; import org.apache.hadoop.hive.metastore.api.Table; import org.apache.hadoop.hive.metastore.api.TableMeta; import org.apache.hadoop.hive.metastore.api.Type; @@ -893,8 +895,22 @@ public class DummyRawStoreFailEvent implements RawStore, Configurable { } @Override + public List<SQLUniqueConstraint> getUniqueConstraints(String db_name, String tbl_name) + throws MetaException { + return null; + } + + @Override + public List<SQLNotNullConstraint> getNotNullConstraints(String db_name, String tbl_name) + throws MetaException { + return null; + } + + @Override public void createTableWithConstraints(Table tbl, - List<SQLPrimaryKey> primaryKeys, List<SQLForeignKey> foreignKeys) + List<SQLPrimaryKey> primaryKeys, List<SQLForeignKey> foreignKeys, + List<SQLUniqueConstraint> uniqueConstraints, + List<SQLNotNullConstraint> notNullConstraints) throws InvalidObjectException, MetaException { } @@ -914,6 +930,16 @@ public class DummyRawStoreFailEvent implements RawStore, Configurable { } @Override + public void addUniqueConstraints(List<SQLUniqueConstraint> uks) + throws InvalidObjectException, MetaException { + } + + @Override + public void addNotNullConstraints(List<SQLNotNullConstraint> nns) + throws InvalidObjectException, MetaException { + } + + @Override public Map<String, List<ColumnStatisticsObj>> getColStatsForTablePartitions(String dbName, String tableName) throws MetaException, NoSuchObjectException { return objectStore.getColStatsForTablePartitions(dbName, tableName); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/itests/hive-unit/src/test/java/org/apache/hive/jdbc/TestJdbcDriver2.java ---------------------------------------------------------------------- diff --git a/itests/hive-unit/src/test/java/org/apache/hive/jdbc/TestJdbcDriver2.java b/itests/hive-unit/src/test/java/org/apache/hive/jdbc/TestJdbcDriver2.java index 6e9223a..1108934 100644 --- a/itests/hive-unit/src/test/java/org/apache/hive/jdbc/TestJdbcDriver2.java +++ b/itests/hive-unit/src/test/java/org/apache/hive/jdbc/TestJdbcDriver2.java @@ -141,7 +141,7 @@ public class TestJdbcDriver2 { + " (under_col int comment 'the under column', value string) comment '" + tableComment + "'"); stmt.execute("create table " + tableNameWithPk - + " (a STRING, b STRING, primary key (a) disable novalidate) "); + + " (a STRING, b STRING, primary key (a) disable) "); // load data stmt.execute("load data local inpath '" + dataFilePath.toString() + "' into table " + tableName); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java ---------------------------------------------------------------------- diff --git a/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java b/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java index 111cc11..cd6c41c 100644 --- a/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java +++ b/itests/util/src/main/java/org/apache/hadoop/hive/ql/QTestUtil.java @@ -1696,6 +1696,8 @@ public class QTestUtil { ".*.hive-staging.*", "pk_-?[0-9]*_[0-9]*_[0-9]*", "fk_-?[0-9]*_[0-9]*_[0-9]*", + "uk_-?[0-9]*_[0-9]*_[0-9]*", + "nn_-?[0-9]*_[0-9]*_[0-9]*", ".*at com\\.sun\\.proxy.*", ".*at com\\.jolbox.*", ".*at com\\.zaxxer.*", http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/if/hive_metastore.thrift ---------------------------------------------------------------------- diff --git a/metastore/if/hive_metastore.thrift b/metastore/if/hive_metastore.thrift index 53e5f29..0573f0c 100755 --- a/metastore/if/hive_metastore.thrift +++ b/metastore/if/hive_metastore.thrift @@ -69,6 +69,27 @@ struct SQLForeignKey { 14: bool rely_cstr // Rely/No Rely } +struct SQLUniqueConstraint { + 1: string table_db, // table schema + 2: string table_name, // table name + 3: string column_name, // column name + 4: i32 key_seq, // sequence number within unique constraint + 5: string uk_name, // unique key name + 6: bool enable_cstr, // Enable/Disable + 7: bool validate_cstr, // Validate/No validate + 8: bool rely_cstr // Rely/No Rely +} + +struct SQLNotNullConstraint { + 1: string table_db, // table schema + 2: string table_name, // table name + 3: string column_name, // column name + 4: string nn_name, // not null name + 5: bool enable_cstr, // Enable/Disable + 6: bool validate_cstr, // Validate/No validate + 7: bool rely_cstr // Rely/No Rely +} + struct Type { 1: string name, // one of the types in PrimitiveTypes or CollectionTypes or User defined types 2: optional string type1, // object type if the name is 'list' (LIST_TYPE), key type if the name is 'map' (MAP_TYPE) @@ -497,6 +518,24 @@ struct ForeignKeysResponse { 1: required list<SQLForeignKey> foreignKeys } +struct UniqueConstraintsRequest { + 1: required string db_name, + 2: required string tbl_name +} + +struct UniqueConstraintsResponse { + 1: required list<SQLUniqueConstraint> uniqueConstraints +} + +struct NotNullConstraintsRequest { + 1: required string db_name, + 2: required string tbl_name +} + +struct NotNullConstraintsResponse { + 1: required list<SQLNotNullConstraint> notNullConstraints +} + struct DropConstraintRequest { 1: required string dbname, 2: required string tablename, @@ -511,6 +550,14 @@ struct AddForeignKeyRequest { 1: required list<SQLForeignKey> foreignKeyCols } +struct AddUniqueConstraintRequest { + 1: required list<SQLUniqueConstraint> uniqueConstraintCols +} + +struct AddNotNullConstraintRequest { + 1: required list<SQLNotNullConstraint> notNullConstraintCols +} + // Return type for get_partitions_by_expr struct PartitionsByExprResult { 1: required list<Partition> partitions, @@ -1055,7 +1102,8 @@ service ThriftHiveMetastore extends fb303.FacebookService throws (1:AlreadyExistsException o1, 2:InvalidObjectException o2, 3:MetaException o3, 4:NoSuchObjectException o4) - void create_table_with_constraints(1:Table tbl, 2: list<SQLPrimaryKey> primaryKeys, 3: list<SQLForeignKey> foreignKeys) + void create_table_with_constraints(1:Table tbl, 2: list<SQLPrimaryKey> primaryKeys, 3: list<SQLForeignKey> foreignKeys, + 4: list<SQLUniqueConstraint> uniqueConstraints, 5: list<SQLNotNullConstraint> notNullConstraints) throws (1:AlreadyExistsException o1, 2:InvalidObjectException o2, 3:MetaException o3, 4:NoSuchObjectException o4) @@ -1065,6 +1113,10 @@ service ThriftHiveMetastore extends fb303.FacebookService throws(1:NoSuchObjectException o1, 2:MetaException o2) void add_foreign_key(1:AddForeignKeyRequest req) throws(1:NoSuchObjectException o1, 2:MetaException o2) + void add_unique_constraint(1:AddUniqueConstraintRequest req) + throws(1:NoSuchObjectException o1, 2:MetaException o2) + void add_not_null_constraint(1:AddNotNullConstraintRequest req) + throws(1:NoSuchObjectException o1, 2:MetaException o2) // drops the table and all the partitions associated with it if the table has partitions // delete data (including partitions) if deleteData is set to true @@ -1313,11 +1365,16 @@ service ThriftHiveMetastore extends fb303.FacebookService list<string> get_index_names(1:string db_name, 2:string tbl_name, 3:i16 max_indexes=-1) throws(1:MetaException o2) - //primary keys and foreign keys + //primary keys and foreign keys PrimaryKeysResponse get_primary_keys(1:PrimaryKeysRequest request) throws(1:MetaException o1, 2:NoSuchObjectException o2) ForeignKeysResponse get_foreign_keys(1:ForeignKeysRequest request) throws(1:MetaException o1, 2:NoSuchObjectException o2) + // other constraints + UniqueConstraintsResponse get_unique_constraints(1:UniqueConstraintsRequest request) + throws(1:MetaException o1, 2:NoSuchObjectException o2) + NotNullConstraintsResponse get_not_null_constraints(1:NotNullConstraintsRequest request) + throws(1:MetaException o1, 2:NoSuchObjectException o2) // column statistics interfaces http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/derby/042-HIVE-16575.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/042-HIVE-16575.derby.sql b/metastore/scripts/upgrade/derby/042-HIVE-16575.derby.sql new file mode 100644 index 0000000..c614f3e --- /dev/null +++ b/metastore/scripts/upgrade/derby/042-HIVE-16575.derby.sql @@ -0,0 +1,4 @@ +-- Remove the NOT NULL constraint from the CHILD_INTEGER_IDX column +ALTER TABLE "APP"."KEY_CONSTRAINTS" ALTER COLUMN "CHILD_INTEGER_IDX" NULL; + +CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE"); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/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 0226d6f..a9a5329 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 @@ -106,7 +106,7 @@ CREATE TABLE "APP"."NOTIFICATION_LOG" ("NL_ID" BIGINT NOT NULL, "DB_NAME" VARCHA CREATE TABLE "APP"."NOTIFICATION_SEQUENCE" ("NNI_ID" BIGINT NOT NULL, "NEXT_EVENT_ID" BIGINT NOT NULL); -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"."KEY_CONSTRAINTS" ("CHILD_CD_ID" BIGINT, "CHILD_INTEGER_IDX" INTEGER, "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_KEY" VARCHAR(255) NOT NULL, "PROPERTY_VALUE" VARCHAR(1000) NOT NULL, "DESCRIPTION" VARCHAR(1000)); @@ -150,6 +150,8 @@ CREATE INDEX "APP"."FUNC_RU_N49" ON "APP"."FUNC_RU" ("FUNC_ID"); CREATE INDEX "APP"."CONSTRAINTS_PARENT_TBL_ID_INDEX" ON "APP"."KEY_CONSTRAINTS"("PARENT_TBL_ID"); +CREATE INDEX "APP"."CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "APP"."KEY_CONSTRAINTS"("CONSTRAINT_TYPE"); + -- ---------------------------------------------- -- DDL Statements for keys -- ---------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/derby/upgrade-2.3.0-to-3.0.0.derby.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/derby/upgrade-2.3.0-to-3.0.0.derby.sql b/metastore/scripts/upgrade/derby/upgrade-2.3.0-to-3.0.0.derby.sql index 455651f..a31fc5e 100644 --- a/metastore/scripts/upgrade/derby/upgrade-2.3.0-to-3.0.0.derby.sql +++ b/metastore/scripts/upgrade/derby/upgrade-2.3.0-to-3.0.0.derby.sql @@ -1,5 +1,5 @@ -- Upgrade MetaStore schema from 2.3.0 to 3.0.0 - RUN '041-HIVE-16556.derby.sql'; +RUN '042-HIVE-16575.derby.sql'; UPDATE "APP".VERSION SET SCHEMA_VERSION='3.0.0', VERSION_COMMENT='Hive release version 3.0.0' where VER_ID=1; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/hive/hive-schema-3.0.0.hive.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/hive/hive-schema-3.0.0.hive.sql b/metastore/scripts/upgrade/hive/hive-schema-3.0.0.hive.sql index a70884c..70559cb 100644 --- a/metastore/scripts/upgrade/hive/hive-schema-3.0.0.hive.sql +++ b/metastore/scripts/upgrade/hive/hive-schema-3.0.0.hive.sql @@ -8,7 +8,7 @@ CREATE TABLE IF NOT EXISTS `BUCKETING_COLS` ( `SD_ID` bigint, `BUCKET_COL_NAME` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_BUCKETING_COLS` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_BUCKETING_COLS` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -24,7 +24,7 @@ FROM CREATE TABLE IF NOT EXISTS `CDS` ( `CD_ID` bigint, - CONSTRAINT `SYS_PK_CDS` PRIMARY KEY (`CD_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_CDS` PRIMARY KEY (`CD_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -42,7 +42,7 @@ CREATE TABLE IF NOT EXISTS `COLUMNS_V2` ( `COLUMN_NAME` string, `TYPE_NAME` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_COLUMN_V2` PRIMARY KEY (`CD_ID`,`COLUMN_NAME`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_COLUMN_V2` PRIMARY KEY (`CD_ID`,`COLUMN_NAME`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -62,7 +62,7 @@ CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( `DB_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_DATABASE_PARAMS` PRIMARY KEY (`DB_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_DATABASE_PARAMS` PRIMARY KEY (`DB_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -82,7 +82,7 @@ CREATE TABLE IF NOT EXISTS `DBS` ( `NAME` string, `OWNER_NAME` string, `OWNER_TYPE` string, - CONSTRAINT `SYS_PK_DBS` PRIMARY KEY (`DB_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_DBS` PRIMARY KEY (`DB_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -108,7 +108,7 @@ CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( `PRINCIPAL_NAME` string, `PRINCIPAL_TYPE` string, `DB_PRIV` string, - CONSTRAINT `SYS_PK_DB_PRIVS` PRIMARY KEY (`DB_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_DB_PRIVS` PRIMARY KEY (`DB_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -137,7 +137,7 @@ CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( `PRINCIPAL_NAME` string, `PRINCIPAL_TYPE` string, `USER_PRIV` string, - CONSTRAINT `SYS_PK_GLOBAL_PRIVS` PRIMARY KEY (`USER_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_GLOBAL_PRIVS` PRIMARY KEY (`USER_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -166,7 +166,7 @@ CREATE TABLE IF NOT EXISTS `IDXS` ( `LAST_ACCESS_TIME` int, `ORIG_TBL_ID` bigint, `SD_ID` bigint, - CONSTRAINT `SYS_PK_IDXS` PRIMARY KEY (`INDEX_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_IDXS` PRIMARY KEY (`INDEX_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -190,7 +190,7 @@ CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( `INDEX_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_INDEX_PARAMS` PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_INDEX_PARAMS` PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -211,7 +211,7 @@ CREATE TABLE IF NOT EXISTS `PARTITIONS` ( `PART_NAME` string, `SD_ID` bigint, `TBL_ID` bigint, - CONSTRAINT `SYS_PK_PARTITIONS` PRIMARY KEY (`PART_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PARTITIONS` PRIMARY KEY (`PART_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -234,7 +234,7 @@ CREATE TABLE IF NOT EXISTS `PARTITION_KEYS` ( `PKEY_NAME` string, `PKEY_TYPE` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_PARTITION_KEYS` PRIMARY KEY (`TBL_ID`,`PKEY_NAME`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PARTITION_KEYS` PRIMARY KEY (`TBL_ID`,`PKEY_NAME`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -254,7 +254,7 @@ CREATE TABLE IF NOT EXISTS `PARTITION_KEY_VALS` ( `PART_ID` bigint, `PART_KEY_VAL` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_PARTITION_KEY_VALS` PRIMARY KEY (`PART_ID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PARTITION_KEY_VALS` PRIMARY KEY (`PART_ID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -272,7 +272,7 @@ CREATE TABLE IF NOT EXISTS `PARTITION_PARAMS` ( `PART_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_PARTITION_PARAMS` PRIMARY KEY (`PART_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PARTITION_PARAMS` PRIMARY KEY (`PART_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -297,7 +297,7 @@ CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( `PRINCIPAL_NAME` string, `PRINCIPAL_TYPE` string, `PART_COL_PRIV` string, - CONSTRAINT `SYS_PK_PART_COL_PRIVS` PRIMARY KEY (`PART_COLUMN_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PART_COL_PRIVS` PRIMARY KEY (`PART_COLUMN_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -328,7 +328,7 @@ CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( `PRINCIPAL_NAME` string, `PRINCIPAL_TYPE` string, `PART_PRIV` string, - CONSTRAINT `SYS_PK_PART_PRIVS` PRIMARY KEY (`PART_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PART_PRIVS` PRIMARY KEY (`PART_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -353,7 +353,7 @@ CREATE TABLE IF NOT EXISTS `ROLES` ( `CREATE_TIME` int, `OWNER_NAME` string, `ROLE_NAME` string, - CONSTRAINT `SYS_PK_ROLES` PRIMARY KEY (`ROLE_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_ROLES` PRIMARY KEY (`ROLE_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -377,7 +377,7 @@ CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( `PRINCIPAL_NAME` string, `PRINCIPAL_TYPE` string, `ROLE_ID` bigint, - CONSTRAINT `SYS_PK_ROLE_MAP` PRIMARY KEY (`ROLE_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_ROLE_MAP` PRIMARY KEY (`ROLE_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -406,7 +406,7 @@ CREATE TABLE IF NOT EXISTS `SDS` ( `NUM_BUCKETS` int, `OUTPUT_FORMAT` string, `SERDE_ID` bigint, - CONSTRAINT `SYS_PK_SDS` PRIMARY KEY (`SD_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SDS` PRIMARY KEY (`SD_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -430,7 +430,7 @@ CREATE TABLE IF NOT EXISTS `SD_PARAMS` ( `SD_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_SD_PARAMS` PRIMARY KEY (`SD_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SD_PARAMS` PRIMARY KEY (`SD_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -447,7 +447,7 @@ FROM CREATE TABLE IF NOT EXISTS `SEQUENCE_TABLE` ( `SEQUENCE_NAME` string, `NEXT_VAL` bigint, - CONSTRAINT `SYS_PK_SEQUENCE_TABLE` PRIMARY KEY (`SEQUENCE_NAME`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SEQUENCE_TABLE` PRIMARY KEY (`SEQUENCE_NAME`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -464,7 +464,7 @@ CREATE TABLE IF NOT EXISTS `SERDES` ( `SERDE_ID` bigint, `NAME` string, `SLIB` string, - CONSTRAINT `SYS_PK_SERDES` PRIMARY KEY (`SERDE_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SERDES` PRIMARY KEY (`SERDE_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -482,7 +482,7 @@ CREATE TABLE IF NOT EXISTS `SERDE_PARAMS` ( `SERDE_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_SERDE_PARAMS` PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SERDE_PARAMS` PRIMARY KEY (`SERDE_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -500,7 +500,7 @@ CREATE TABLE IF NOT EXISTS `SKEWED_COL_NAMES` ( `SD_ID` bigint, `SKEWED_COL_NAME` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_SKEWED_COL_NAMES` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SKEWED_COL_NAMES` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -518,7 +518,7 @@ CREATE TABLE IF NOT EXISTS `SKEWED_COL_VALUE_LOC_MAP` ( `SD_ID` bigint, `STRING_LIST_ID_KID` bigint, `LOCATION` string, - CONSTRAINT `SYS_PK_COL_VALUE_LOC_MAP` PRIMARY KEY (`SD_ID`,`STRING_LIST_ID_KID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_COL_VALUE_LOC_MAP` PRIMARY KEY (`SD_ID`,`STRING_LIST_ID_KID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -534,7 +534,7 @@ FROM CREATE TABLE IF NOT EXISTS `SKEWED_STRING_LIST` ( `STRING_LIST_ID` bigint, - CONSTRAINT `SYS_PK_SKEWED_STRING_LIST` PRIMARY KEY (`STRING_LIST_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SKEWED_STRING_LIST` PRIMARY KEY (`STRING_LIST_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -550,7 +550,7 @@ CREATE TABLE IF NOT EXISTS `SKEWED_STRING_LIST_VALUES` ( `STRING_LIST_ID` bigint, `STRING_LIST_VALUE` string, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_SKEWED_STRING_LIST_VALUES` PRIMARY KEY (`STRING_LIST_ID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SKEWED_STRING_LIST_VALUES` PRIMARY KEY (`STRING_LIST_ID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -568,7 +568,7 @@ CREATE TABLE IF NOT EXISTS `SKEWED_VALUES` ( `SD_ID_OID` bigint, `STRING_LIST_ID_EID` bigint, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_SKEWED_VALUES` PRIMARY KEY (`SD_ID_OID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SKEWED_VALUES` PRIMARY KEY (`SD_ID_OID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -587,7 +587,7 @@ CREATE TABLE IF NOT EXISTS `SORT_COLS` ( `COLUMN_NAME` string, `ORDER` int, `INTEGER_IDX` int, - CONSTRAINT `SYS_PK_SORT_COLS` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_SORT_COLS` PRIMARY KEY (`SD_ID`,`INTEGER_IDX`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -606,7 +606,7 @@ CREATE TABLE IF NOT EXISTS `TABLE_PARAMS` ( `TBL_ID` bigint, `PARAM_KEY` string, `PARAM_VALUE` string, - CONSTRAINT `SYS_PK_TABLE_PARAMS` PRIMARY KEY (`TBL_ID`,`PARAM_KEY`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_TABLE_PARAMS` PRIMARY KEY (`TBL_ID`,`PARAM_KEY`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -633,7 +633,7 @@ CREATE TABLE IF NOT EXISTS `TBLS` ( `VIEW_EXPANDED_TEXT` string, `VIEW_ORIGINAL_TEXT` string, `IS_REWRITE_ENABLED` boolean, - CONSTRAINT `SYS_PK_TBLS` PRIMARY KEY (`TBL_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_TBLS` PRIMARY KEY (`TBL_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -666,7 +666,7 @@ CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( `PRINCIPAL_TYPE` string, `TBL_COL_PRIV` string, `TBL_ID` bigint, - CONSTRAINT `SYS_PK_TBL_COL_PRIVS` PRIMARY KEY (`TBL_COLUMN_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_TBL_COL_PRIVS` PRIMARY KEY (`TBL_COLUMN_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -697,7 +697,7 @@ CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( `PRINCIPAL_TYPE` string, `TBL_PRIV` string, `TBL_ID` bigint, - CONSTRAINT `SYS_PK_TBL_PRIVS` PRIMARY KEY (`TBL_GRANT_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_TBL_PRIVS` PRIMARY KEY (`TBL_GRANT_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -737,7 +737,7 @@ CREATE TABLE IF NOT EXISTS `TAB_COL_STATS` ( `NUM_TRUES` bigint, `NUM_FALSES` bigint, `LAST_ANALYZED` bigint, - CONSTRAINT `SYS_PK_TAB_COL_STATS` PRIMARY KEY (`CS_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_TAB_COL_STATS` PRIMARY KEY (`CS_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -788,7 +788,7 @@ CREATE TABLE IF NOT EXISTS `PART_COL_STATS` ( `NUM_TRUES` bigint, `NUM_FALSES` bigint, `LAST_ANALYZED` bigint, - CONSTRAINT `SYS_PK_PART_COL_STATS` PRIMARY KEY (`CS_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_PART_COL_STATS` PRIMARY KEY (`CS_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -823,7 +823,7 @@ CREATE TABLE IF NOT EXISTS `VERSION` ( `VER_ID` BIGINT, `SCHEMA_VERSION` string, `VERSION_COMMENT` string, - CONSTRAINT `SYS_PK_VERSION` PRIMARY KEY (`VER_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_VERSION` PRIMARY KEY (`VER_ID`) DISABLE ); INSERT INTO `VERSION` VALUES (1, '3.0.0', 'Hive release version 3.0.0'); @@ -832,7 +832,7 @@ CREATE TABLE IF NOT EXISTS `DB_VERSION` ( `VER_ID` BIGINT, `SCHEMA_VERSION` string, `VERSION_COMMENT` string, - CONSTRAINT `SYS_PK_DB_VERSION` PRIMARY KEY (`VER_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_DB_VERSION` PRIMARY KEY (`VER_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -855,7 +855,7 @@ CREATE TABLE IF NOT EXISTS `FUNCS` ( `FUNC_TYPE` int, `OWNER_NAME` string, `OWNER_TYPE` string, - CONSTRAINT `SYS_PK_FUNCS` PRIMARY KEY (`FUNC_ID`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_FUNCS` PRIMARY KEY (`FUNC_ID`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -879,7 +879,7 @@ FROM -- `RESOURCE_TYPE` int, -- `RESOURCE_URI` string, -- `INTEGER_IDX` int, --- CONSTRAINT `SYS_PK_FUNCS_RU` PRIMARY KEY (`FUNC_ID`, `INTEGER_IDX`) DISABLE NOVALIDATE +-- CONSTRAINT `SYS_PK_FUNCS_RU` PRIMARY KEY (`FUNC_ID`, `INTEGER_IDX`) DISABLE -- ) -- STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' -- TBLPROPERTIES ( @@ -901,7 +901,7 @@ CREATE TABLE IF NOT EXISTS `KEY_CONSTRAINTS` `UPDATE_RULE` string, `DELETE_RULE` string, `ENABLE_VALIDATE_RELY` int, - CONSTRAINT `SYS_PK_KEY_CONSTRAINTS` PRIMARY KEY (`CONSTRAINT_NAME`, `POSITION`) DISABLE NOVALIDATE + CONSTRAINT `SYS_PK_KEY_CONSTRAINTS` PRIMARY KEY (`CONSTRAINT_NAME`, `POSITION`) DISABLE ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/mssql/027-HIVE-16575.mssql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mssql/027-HIVE-16575.mssql.sql b/metastore/scripts/upgrade/mssql/027-HIVE-16575.mssql.sql new file mode 100644 index 0000000..213e649 --- /dev/null +++ b/metastore/scripts/upgrade/mssql/027-HIVE-16575.mssql.sql @@ -0,0 +1 @@ +CREATE INDEX CONSTRAINTS_CONSTRAINT_TYPE_INDEX ON KEY_CONSTRAINTS(CONSTRAINT_TYPE); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/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 d7bd3f8..1cfe2d1 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 @@ -1007,6 +1007,8 @@ ALTER TABLE KEY_CONSTRAINTS ADD CONSTRAINT CONSTRAINTS_PK PRIMARY KEY (CONSTRAIN CREATE INDEX CONSTRAINTS_PARENT_TBL_ID__INDEX ON KEY_CONSTRAINTS(PARENT_TBL_ID); +CREATE INDEX CONSTRAINTS_CONSTRAINT_TYPE_INDEX ON KEY_CONSTRAINTS(CONSTRAINT_TYPE); + CREATE TABLE WRITE_SET ( WS_DATABASE nvarchar(128) NOT NULL, WS_TABLE nvarchar(128) NOT NULL, http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql b/metastore/scripts/upgrade/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql index e579dac..f8bf581 100644 --- a/metastore/scripts/upgrade/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql +++ b/metastore/scripts/upgrade/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql @@ -1,6 +1,7 @@ SELECT 'Upgrading MetaStore schema from 2.3.0 to 3.0.0' AS MESSAGE; :r 026-HIVE-16556.mssql.sql +:r 027-HIVE-16575.mssql.sql UPDATE VERSION SET SCHEMA_VERSION='3.0.0', VERSION_COMMENT='Hive release version 3.0.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 2.3.0 to 3.0.0' AS MESSAGE; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/mysql/042-HIVE-16575.mysql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mysql/042-HIVE-16575.mysql.sql b/metastore/scripts/upgrade/mysql/042-HIVE-16575.mysql.sql new file mode 100644 index 0000000..b97c571 --- /dev/null +++ b/metastore/scripts/upgrade/mysql/042-HIVE-16575.mysql.sql @@ -0,0 +1 @@ +CREATE INDEX `CONSTRAINTS_CONSTRAINT_TYPE_INDEX` ON KEY_CONSTRAINTS (`CONSTRAINT_TYPE`) USING BTREE; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/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 ff874bb..232b097 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 @@ -829,6 +829,8 @@ CREATE TABLE IF NOT EXISTS `KEY_CONSTRAINTS` CREATE INDEX `CONSTRAINTS_PARENT_TABLE_ID_INDEX` ON KEY_CONSTRAINTS (`PARENT_TBL_ID`) USING BTREE; +CREATE INDEX `CONSTRAINTS_CONSTRAINT_TYPE_INDEX` ON KEY_CONSTRAINTS (`CONSTRAINT_TYPE`) USING BTREE; + -- ----------------------------- -- Metastore DB Properties table -- ----------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql b/metastore/scripts/upgrade/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql index 2884387..9371f35 100644 --- a/metastore/scripts/upgrade/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql +++ b/metastore/scripts/upgrade/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql @@ -1,6 +1,7 @@ SELECT 'Upgrading MetaStore schema from 2.3.0 to 3.0.0' AS ' '; SOURCE 041-HIVE-16556.mysql.sql; +SOURCE 042-HIVE-16575.mysql.sql; UPDATE VERSION SET SCHEMA_VERSION='3.0.0', VERSION_COMMENT='Hive release version 3.0.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 2.3.0 to 3.0.0' AS ' '; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/oracle/042-HIVE-16575.oracle.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/oracle/042-HIVE-16575.oracle.sql b/metastore/scripts/upgrade/oracle/042-HIVE-16575.oracle.sql new file mode 100644 index 0000000..77adc9d --- /dev/null +++ b/metastore/scripts/upgrade/oracle/042-HIVE-16575.oracle.sql @@ -0,0 +1 @@ +CREATE INDEX CONSTRAINTS_CT_INDEX ON KEY_CONSTRAINTS(CONSTRAINT_TYPE); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/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 0ca6143..8fdb552 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 @@ -799,6 +799,8 @@ ALTER TABLE KEY_CONSTRAINTS ADD CONSTRAINT CONSTRAINTS_PK PRIMARY KEY (CONSTRAIN CREATE INDEX CONSTRAINTS_PT_INDEX ON KEY_CONSTRAINTS(PARENT_TBL_ID); +CREATE INDEX CONSTRAINTS_CT_INDEX ON KEY_CONSTRAINTS(CONSTRAINT_TYPE); + -- Table for METASTORE_DB_PROPERTIES and its constraints CREATE TABLE METASTORE_DB_PROPERTIES ( http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql b/metastore/scripts/upgrade/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql index a15f288..33791d5 100644 --- a/metastore/scripts/upgrade/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql +++ b/metastore/scripts/upgrade/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql @@ -1,6 +1,7 @@ SELECT 'Upgrading MetaStore schema from 2.3.0 to 3.0.0' AS Status from dual; @041-HIVE-16556.oracle.sql; +@042-HIVE-16575.oracle.sql; UPDATE VERSION SET SCHEMA_VERSION='3.0.0', VERSION_COMMENT='Hive release version 3.0.0' where VER_ID=1; SELECT 'Finished upgrading MetaStore schema from 2.3.0 to 3.0.0' AS Status from dual; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/postgres/041-HIVE-16575.postgres.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/postgres/041-HIVE-16575.postgres.sql b/metastore/scripts/upgrade/postgres/041-HIVE-16575.postgres.sql new file mode 100644 index 0000000..b956c8f --- /dev/null +++ b/metastore/scripts/upgrade/postgres/041-HIVE-16575.postgres.sql @@ -0,0 +1 @@ +CREATE INDEX "CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "KEY_CONSTRAINTS" USING BTREE ("CONSTRAINT_TYPE"); http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/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 91a71f5..1cdeb6b 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 @@ -1209,6 +1209,8 @@ CREATE INDEX "FUNC_RU_N49" ON "FUNC_RU" ("FUNC_ID"); CREATE INDEX "CONSTRAINTS_PARENT_TBLID_INDEX" ON "KEY_CONSTRAINTS" USING BTREE ("PARENT_TBL_ID"); +CREATE INDEX "CONSTRAINTS_CONSTRAINT_TYPE_INDEX" ON "KEY_CONSTRAINTS" USING BTREE ("CONSTRAINT_TYPE"); + ALTER TABLE ONLY "SKEWED_STRING_LIST_VALUES" ADD CONSTRAINT "SKEWED_STRING_LIST_VALUES_fkey" FOREIGN KEY ("STRING_LIST_ID") REFERENCES "SKEWED_STRING_LIST"("STRING_LIST_ID") DEFERRABLE; http://git-wip-us.apache.org/repos/asf/hive/blob/696be9f5/metastore/scripts/upgrade/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql ---------------------------------------------------------------------- diff --git a/metastore/scripts/upgrade/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql b/metastore/scripts/upgrade/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql index 4d8f0cd..314198c 100644 --- a/metastore/scripts/upgrade/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql +++ b/metastore/scripts/upgrade/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql @@ -1,6 +1,7 @@ SELECT 'Upgrading MetaStore schema from 2.3.0 to 3.0.0'; \i 040-HIVE-16556.postgres.sql; +\i 041-HIVE-16575.postgres.sql; UPDATE "VERSION" SET "SCHEMA_VERSION"='3.0.0', "VERSION_COMMENT"='Hive release version 3.0.0' where "VER_ID"=1; SELECT 'Finished upgrading MetaStore schema from 2.3.0 to 3.0.0';