diff --git metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql new file mode 100644 index 0000000..ac2daba --- /dev/null +++ metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql @@ -0,0 +1,3 @@ +-- HIVE-972: Support views +ALTER TABLE "TBLS" ADD "VIEW_ORIGINAL_TEXT" LONG VARCHAR DEFAULT NULL; +ALTER TABLE "TBLS" ADD "VIEW_EXPANDED_TEXT" LONG VARCHAR DEFAULT NULL; diff --git metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql new file mode 100644 index 0000000..f20d79c --- /dev/null +++ metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql @@ -0,0 +1,2 @@ +-- HIVE-1068: CREATE VIEW followup: add a 'table type' enum attribute in metastore +ALTER TABLE "TBLS" ADD COLUMN "TBL_TYPE" VARCHAR(128); diff --git metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql new file mode 100644 index 0000000..01e4a60 --- /dev/null +++ metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql @@ -0,0 +1,3 @@ +-- HIVE-675: Add database/schema support for Hive QL +ALTER TABLE "DBS" ALTER "DESC" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "DBS" ADD "DB_LOCATION_URI" VARCHAR(4000) NOT NULL DEFAULT ''; diff --git metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql new file mode 100644 index 0000000..e635578 --- /dev/null +++ metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql @@ -0,0 +1,15 @@ +-- HIVE-1364: Increase the maximum length of various metastore fields, +-- and remove TYPE_NAME from COLUMNS primary key +ALTER TABLE "TBLS" ALTER "OWNER" SET DATA TYPE VARCHAR(767); +ALTER TABLE "COLUMNS" DROP PRIMARY KEY; +ALTER TABLE "COLUMNS" ADD PRIMARY KEY ("SD_ID", "COLUMN_NAME"); +ALTER TABLE "COLUMNS" ALTER "TYPE_NAME" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "PARTITION_KEYS" ALTER "PKEY_COMMENT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SD_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "INPUT_FORMAT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "LOCATION" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SDS" ALTER "OUTPUT_FORMAT" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SERDE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "SERDES" ALTER "SLIB" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "TABLE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); +ALTER TABLE "PARTITION_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); diff --git metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql new file mode 100644 index 0000000..01a9b59 --- /dev/null +++ metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql @@ -0,0 +1,47 @@ +-- +-- HIVE-417 Implement Indexing in Hive +-- + +CREATE TABLE "IDXS" ( + "INDEX_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DEFERRED_REBUILD" CHAR(1) NOT NULL, + "INDEX_HANDLER_CLASS" VARCHAR(256), + "INDEX_NAME" VARCHAR(128), + "INDEX_TBL_ID" BIGINT, + "LAST_ACCESS_TIME" INTEGER NOT NULL, + "ORIG_TBL_ID" BIGINT, + "SD_ID" BIGINT); + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1" + FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2" + FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3" + FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK" + PRIMARY KEY ("INDEX_ID"); + +ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK" + CHECK (DEFERRED_REBUILD IN ('Y','N')); + + +CREATE TABLE "INDEX_PARAMS" ( + "INDEX_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(256) NOT NULL, + "PARAM_VALUE" VARCHAR(767)); + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" + FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" + PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); + +CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); diff --git metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql new file mode 100644 index 0000000..f27324c --- /dev/null +++ metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql @@ -0,0 +1,15 @@ +-- +-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs +-- +CREATE TABLE "DATABASE_PARAMS" ( + "DB_ID" BIGINT NOT NULL, + "PARAM_KEY" VARCHAR(180) NOT NULL, + "PARAM_VALUE" VARCHAR(4000)); + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" + PRIMARY KEY ("DB_ID", "PARAM_KEY"); + diff --git metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql new file mode 100644 index 0000000..8e8a586 --- /dev/null +++ metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql @@ -0,0 +1,184 @@ +-- +-- HIVE-78 Authorization model for Hive +-- + +-- +-- ROLES +-- +CREATE TABLE "ROLES" ( + "ROLE_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "OWNER_NAME" VARCHAR(128), + "ROLE_NAME" VARCHAR(128)); + +ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK" + PRIMARY KEY ("ROLE_ID"); + +CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME"); + +CREATE TABLE "ROLE_MAP" ( + "ROLE_GRANT_ID" BIGINT NOT NULL, + "ADD_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "ROLE_ID" BIGINT); + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" + FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" + PRIMARY KEY ("ROLE_GRANT_ID"); + +CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" ( + "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- GLOBAL_PRIVS +-- +CREATE TABLE "GLOBAL_PRIVS" ( + "USER_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "USER_PRIV" VARCHAR(128)); + +ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" + PRIMARY KEY ("USER_GRANT_ID"); + +CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" ( + "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", + "GRANTOR", "GRANTOR_TYPE"); + +-- +-- DB_PRIVS +-- +CREATE TABLE "DB_PRIVS" ( + "DB_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "DB_ID" BIGINT, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "DB_PRIV" VARCHAR(128)); + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" + FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" + PRIMARY KEY ("DB_GRANT_ID"); + +CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" ( + "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- TBL_PRIVS +-- +CREATE TABLE "TBL_PRIVS" ( + "TBL_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" + PRIMARY KEY ("TBL_GRANT_ID"); + +CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" ( + "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- TBL_COL_PRIVS +-- +CREATE TABLE "TBL_COL_PRIVS" ( + "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "TBL_COL_PRIV" VARCHAR(128), + "TBL_ID" BIGINT); + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" + FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" + PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); + +CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" ( + "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- PART_PRIVS +-- +CREATE TABLE "PART_PRIVS" ( + "PART_GRANT_ID" BIGINT NOT NULL, + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" + PRIMARY KEY ("PART_GRANT_ID"); + +CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" ( + "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); + +-- +-- PART_COL_PRIVS +-- +CREATE TABLE "PART_COL_PRIVS" ( + "PART_COLUMN_GRANT_ID" BIGINT NOT NULL, + "COLUMN_NAME" VARCHAR(128), + "CREATE_TIME" INTEGER NOT NULL, + "GRANT_OPTION" SMALLINT NOT NULL, + "GRANTOR" VARCHAR(128), + "GRANTOR_TYPE" VARCHAR(128), + "PART_ID" BIGINT, + "PRINCIPAL_NAME" VARCHAR(128), + "PRINCIPAL_TYPE" VARCHAR(128), + "PART_COL_PRIV" VARCHAR(128)); + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" + FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") + ON DELETE NO ACTION ON UPDATE NO ACTION; + +ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" + PRIMARY KEY ("PART_COLUMN_GRANT_ID"); + +CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" ( + "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", + "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); diff --git metastore/scripts/upgrade/derby/README metastore/scripts/upgrade/derby/README index 506eb54..0b0200f 100644 --- metastore/scripts/upgrade/derby/README +++ metastore/scripts/upgrade/derby/README @@ -3,13 +3,14 @@ 2) Perform a backup of your Derby metastore database. Probably the easiest way of doing this is to just create a copy of the - Derby database "metastore_db" directory. + Derby database directory. -3) Execute the upgrade script: +3) Execute one or more upgrade scripts: % ij ij version 10.4 ij> CONNECT 'jdbc:derby:/Users/bob/hive/metastore_db;databaseName=metastore_db'; - ij> RUN 'upgrade-0.6.0.derby.sql'; + ij> RUN 'upgrade-0.5.0-to-0.6.0.derby.sql'; + ij> RUN 'upgrade-0.6.0-to-0.7.0.derby.sql'; ij> quit; NOTE: You may need to install the Derby 'ij' utility. diff --git metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql new file mode 100644 index 0000000..493069a --- /dev/null +++ metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql @@ -0,0 +1,5 @@ +-- Upgrade MetaStore schema from 0.5.0 to 0.6.0 +RUN '001-HIVE-972.derby.sql'; +RUN '002-HIVE-1068.derby.sql'; +RUN '003-HIVE-675.derby.sql'; +RUN '004-HIVE-1364.derby.sql'; diff --git metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql new file mode 100644 index 0000000..9c5c6f3 --- /dev/null +++ metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql @@ -0,0 +1,4 @@ +-- Upgrade MetaStore schema from 0.6.0 to 0.7.0 +RUN '005-HIVE-417.derby.sql'; +RUN '006-HIVE-1823.derby.sql'; +RUN '007-HIVE-78.derby.sql'; diff --git metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql deleted file mode 100644 index 77a0f39..0000000 --- metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql +++ /dev/null @@ -1,27 +0,0 @@ --- HIVE-972: Support views -ALTER TABLE "TBLS" ADD "VIEW_ORIGINAL_TEXT" LONG VARCHAR DEFAULT NULL; -ALTER TABLE "TBLS" ADD "VIEW_EXPANDED_TEXT" LONG VARCHAR DEFAULT NULL; - --- HIVE-1068: CREATE VIEW followup: add a 'table type' enum --- attribute in metastore -ALTER TABLE "TBLS" ADD COLUMN "TBL_TYPE" VARCHAR(128); - --- HIVE-675: Add database/schema support for Hive QL -ALTER TABLE "DBS" ALTER "DESC" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "DBS" ADD "DB_LOCATION_URI" VARCHAR(4000) NOT NULL DEFAULT ''; - --- HIVE-1364: Increase the maximum length of various metastore fields, --- and remove TYPE_NAME from COLUMNS primary key -ALTER TABLE "TBLS" ALTER "OWNER" SET DATA TYPE VARCHAR(767); -ALTER TABLE "COLUMNS" ALTER "TYPE_NAME" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "PARTITION_KEYS" ALTER "PKEY_COMMENT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SD_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "INPUT_FORMAT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "LOCATION" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SDS" ALTER "OUTPUT_FORMAT" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SERDE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "SERDES" ALTER "SLIB" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "TABLE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); -ALTER TABLE "COLUMNS" DROP PRIMARY KEY; -ALTER TABLE "COLUMNS" ADD PRIMARY KEY ("SD_ID", "COLUMN_NAME"); -ALTER TABLE "PARTITION_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000); diff --git metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql deleted file mode 100644 index ccad4e7..0000000 --- metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql +++ /dev/null @@ -1,235 +0,0 @@ --- --- HIVE-417 Implement Indexing in Hive --- - -CREATE TABLE "IDXS" ( - "INDEX_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "DEFERRED_REBUILD" CHAR(1) NOT NULL, - "INDEX_HANDLER_CLASS" VARCHAR(256), - "INDEX_NAME" VARCHAR(128), - "INDEX_TBL_ID" BIGINT, - "LAST_ACCESS_TIME" INTEGER NOT NULL, - "ORIG_TBL_ID" BIGINT, - "SD_ID" BIGINT); - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1" - FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2" - FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3" - FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK" - PRIMARY KEY ("INDEX_ID"); - -ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK" - CHECK (DEFERRED_REBUILD IN ('Y','N')); - - -CREATE TABLE "INDEX_PARAMS" ( - "INDEX_ID" BIGINT NOT NULL, - "PARAM_KEY" VARCHAR(256) NOT NULL, - "PARAM_VALUE" VARCHAR(767)); - -ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" - FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" - PRIMARY KEY ("INDEX_ID", "PARAM_KEY"); - -CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID"); - - --- --- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs --- -CREATE TABLE "DATABASE_PARAMS" ( - "DB_ID" BIGINT NOT NULL, - "PARAM_KEY" VARCHAR(180) NOT NULL, - "PARAM_VALUE" VARCHAR(4000)); - -ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" - FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" - PRIMARY KEY ("DB_ID", "PARAM_KEY"); - -ALTER TABLE "DBS" DROP COLUMN "PARAMETERS"; - --- --- HIVE-78 Authorization model for Hive --- - -CREATE TABLE "DB_PRIVS" ( - "DB_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "DB_ID" BIGINT, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "DB_PRIV" VARCHAR(128)); - -ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" - FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" - PRIMARY KEY ("DB_GRANT_ID"); - -CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" ( - "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "DB_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "PART_COL_PRIVS" ( - "PART_COLUMN_GRANT_ID" BIGINT NOT NULL, - "COLUMN_NAME" VARCHAR(128), - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PART_ID" BIGINT, - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "PART_COL_PRIV" VARCHAR(128)); - -ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" - FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" - PRIMARY KEY ("PART_COLUMN_GRANT_ID"); - -CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" ( - "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - -CREATE TABLE "PART_PRIVS" ( - "PART_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PART_ID" BIGINT, - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "PART_PRIV" VARCHAR(128)); - -ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" - FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" - PRIMARY KEY ("PART_GRANT_ID"); - -CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" ( - "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "PART_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "ROLES" ( - "ROLE_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "OWNER_NAME" VARCHAR(128), - "ROLE_NAME" VARCHAR(128)); - -ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK" - PRIMARY KEY ("ROLE_ID"); - -CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME"); - - -CREATE TABLE "ROLE_MAP" ( - "ROLE_GRANT_ID" BIGINT NOT NULL, - "ADD_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "ROLE_ID" BIGINT); - -ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" - FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" - PRIMARY KEY ("ROLE_GRANT_ID"); - -CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" ( - "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "TBL_COL_PRIVS" ( - "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, - "COLUMN_NAME" VARCHAR(128), - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "TBL_COL_PRIV" VARCHAR(128), - "TBL_ID" BIGINT); - -ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" - FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" - PRIMARY KEY ("TBL_COLUMN_GRANT_ID"); - -CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" ( - "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "TBL_PRIVS" ( - "TBL_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "TBL_PRIV" VARCHAR(128), - "TBL_ID" BIGINT); - -ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" - FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID") - ON DELETE NO ACTION ON UPDATE NO ACTION; - -ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" - PRIMARY KEY ("TBL_GRANT_ID"); - -CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" ( - "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", - "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE"); - - -CREATE TABLE "GLOBAL_PRIVS" ( - "USER_GRANT_ID" BIGINT NOT NULL, - "CREATE_TIME" INTEGER NOT NULL, - "GRANT_OPTION" SMALLINT NOT NULL, - "GRANTOR" VARCHAR(128), - "GRANTOR_TYPE" VARCHAR(128), - "PRINCIPAL_NAME" VARCHAR(128), - "PRINCIPAL_TYPE" VARCHAR(128), - "USER_PRIV" VARCHAR(128)); - -ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" - PRIMARY KEY ("USER_GRANT_ID"); - -CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" ( - "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", - "GRANTOR", "GRANTOR_TYPE"); diff --git metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql new file mode 100644 index 0000000..d58c824 --- /dev/null +++ metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql @@ -0,0 +1,3 @@ +SELECT '< HIVE-972: Support views >' AS ' '; +ALTER TABLE `TBLS` ADD `VIEW_EXPANDED_TEXT` mediumtext; +ALTER TABLE `TBLS` ADD `VIEW_ORIGINAL_TEXT` mediumtext; diff --git metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql new file mode 100644 index 0000000..5765961 --- /dev/null +++ metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql @@ -0,0 +1,2 @@ +SELECT '< HIVE-1068: CREATE VIEW followup: add a "table type" enum attribute in metastore >' AS ' '; +ALTER TABLE `TBLS` ADD `TBL_TYPE` VARCHAR(128); diff --git metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql new file mode 100644 index 0000000..d2dd8f0 --- /dev/null +++ metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql @@ -0,0 +1,3 @@ +SELECT '< HIVE-675: Add database/schema support for Hive QL >' AS ' '; +ALTER TABLE `DBS` MODIFY `DESC` VARCHAR(4000); +ALTER TABLE `DBS` ADD `DB_LOCATION_URI` VARCHAR(4000) NOT NULL DEFAULT ''; diff --git metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql new file mode 100644 index 0000000..fbd11d1 --- /dev/null +++ metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql @@ -0,0 +1,14 @@ +SELECT '< HIVE-1364: Increase the maximum length of various metastore fields >' AS ' '; +ALTER TABLE `TBLS` MODIFY `OWNER` VARCHAR(767); +ALTER TABLE `COLUMNS` DROP PRIMARY KEY; +ALTER TABLE `COLUMNS` ADD PRIMARY KEY (`SD_ID`, `COLUMN_NAME`); +ALTER TABLE `COLUMNS` MODIFY `TYPE_NAME` VARCHAR(4000); +ALTER TABLE `PARTITION_KEYS` MODIFY `PKEY_COMMENT` VARCHAR(4000); +ALTER TABLE `SD_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `INPUT_FORMAT` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `LOCATION` VARCHAR(4000); +ALTER TABLE `SDS` MODIFY `OUTPUT_FORMAT` VARCHAR(4000); +ALTER TABLE `SERDE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `SERDES` MODIFY `SLIB` VARCHAR(4000); +ALTER TABLE `TABLE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); +ALTER TABLE `PARTITION_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); diff --git metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql new file mode 100644 index 0000000..3ccf454 --- /dev/null +++ metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql @@ -0,0 +1,28 @@ +SELECT '< HIVE-417 Implement Indexing in Hive >' AS ' '; +CREATE TABLE IF NOT EXISTS `IDXS` ( + `INDEX_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DEFERRED_REBUILD` bit(1) NOT NULL, + `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `INDEX_TBL_ID` bigint(20) DEFAULT NULL, + `LAST_ACCESS_TIME` int(11) NOT NULL, + `ORIG_TBL_ID` bigint(20) DEFAULT NULL, + `SD_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`), + UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), + KEY `IDXS_FK1` (`SD_ID`), + KEY `IDXS_FK2` (`INDEX_TBL_ID`), + KEY `IDXS_FK3` (`ORIG_TBL_ID`), + CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), + CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), + CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( + `INDEX_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), + CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql new file mode 100644 index 0000000..3e725f8 --- /dev/null +++ metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql @@ -0,0 +1,8 @@ +SELECT '< HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs >' AS ' '; +CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( + `DB_ID` bigint(20) NOT NULL, + `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, + `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_ID`,`PARAM_KEY`), + CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql new file mode 100644 index 0000000..d62ae74 --- /dev/null +++ metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql @@ -0,0 +1,114 @@ +SELECT '< HIVE-78 Authorization model for Hive >' AS ' '; + +CREATE TABLE IF NOT EXISTS `ROLES` ( + `ROLE_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`ROLE_ID`), + UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( + `ROLE_GRANT_ID` bigint(20) NOT NULL, + `ADD_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `ROLE_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`ROLE_GRANT_ID`), + UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( + `USER_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`USER_GRANT_ID`), + UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( + `DB_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `DB_ID` bigint(20) DEFAULT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`DB_GRANT_ID`), + UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( + `TBL_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_GRANT_ID`), + KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( + `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `TBL_ID` bigint(20) DEFAULT NULL, + PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), + KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( + `PART_GRANT_ID` bigint(20) NOT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_GRANT_ID`), + KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; + +CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( + `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, + `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `CREATE_TIME` int(11) NOT NULL, + `GRANT_OPTION` smallint(6) NOT NULL, + `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_ID` bigint(20) DEFAULT NULL, + `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + PRIMARY KEY (`PART_COLUMN_GRANT_ID`), + KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), + CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; diff --git metastore/scripts/upgrade/mysql/README metastore/scripts/upgrade/mysql/README index 3d4360f..8b823c3 100644 --- metastore/scripts/upgrade/mysql/README +++ metastore/scripts/upgrade/mysql/README @@ -4,6 +4,9 @@ 2) Perform a backup of your MySQL metastore database: % mysqldump --opt > metastore_backup.sql -3) Execute the upgrade script: - % mysql --user= --password= \ - < upgrade-0.6.0.mysql.sql +3) Execute one or more upgrade scripts, e.g: + mysql> use metastore_db; + Database changed + mysql> source upgrade-0.5.0-to-0.6.0.mysql.sql + mysql> source upgrade-0.6.0-to-0.7.0.mysql.sql + diff --git metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql new file mode 100644 index 0000000..20341b7 --- /dev/null +++ metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql @@ -0,0 +1,6 @@ +SELECT '< Upgrading MetaStore schema from 0.5.0 to 0.6.0 >' AS ' '; +SOURCE 001-HIVE-972.mysql.sql; +SOURCE 002-HIVE-1068.mysql.sql; +SOURCE 003-HIVE-675.mysql.sql; +SOURCE 004-HIVE-1364.mysql.sql; +SELECT '< Finished upgrading MetaStore schema from 0.5.0 to 0.6.0 >' AS ' '; diff --git metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql new file mode 100644 index 0000000..0ea656e --- /dev/null +++ metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql @@ -0,0 +1,5 @@ +SELECT 'Upgrading MetaStore schema from 0.6.0 to 0.7.0' AS ' '; +SOURCE 005-HIVE-417.mysql.sql; +SOURCE 006-HIVE-1823.mysql.sql; +SOURCE 007-HIVE-78.mysql.sql; +SELECT 'Finished upgrading MetaStore schema from 0.6.0 to 0.7.0' AS ' '; diff --git metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql deleted file mode 100644 index 8cbd501..0000000 --- metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql +++ /dev/null @@ -1,27 +0,0 @@ --- HIVE-972: Support views -ALTER TABLE `TBLS` ADD `VIEW_EXPANDED_TEXT` mediumtext; -ALTER TABLE `TBLS` ADD `VIEW_ORIGINAL_TEXT` mediumtext; - --- HIVE-1068: CREATE VIEW followup: add a 'table type' enum --- attribute in metastore -ALTER TABLE `TBLS` ADD `TBL_TYPE` VARCHAR(128); - --- HIVE-675: Add database/schema support for Hive QL -ALTER TABLE `DBS` MODIFY `DESC` VARCHAR(4000); -ALTER TABLE `DBS` ADD `DB_LOCATION_URI` VARCHAR(4000) NOT NULL DEFAULT ''; - --- HIVE-1364: Increase the maximum length of various metastore fields, --- and remove TYPE_NAME from COLUMNS primary key -ALTER TABLE `TBLS` MODIFY `OWNER` VARCHAR(767); -ALTER TABLE `COLUMNS` MODIFY `TYPE_NAME` VARCHAR(4000); -ALTER TABLE `PARTITION_KEYS` MODIFY `PKEY_COMMENT` VARCHAR(4000); -ALTER TABLE `SD_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `INPUT_FORMAT` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `LOCATION` VARCHAR(4000); -ALTER TABLE `SDS` MODIFY `OUTPUT_FORMAT` VARCHAR(4000); -ALTER TABLE `SERDE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `SERDES` MODIFY `SLIB` VARCHAR(4000); -ALTER TABLE `TABLE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); -ALTER TABLE `COLUMNS` DROP PRIMARY KEY; -ALTER TABLE `COLUMNS` ADD PRIMARY KEY (`SD_ID`, `COLUMN_NAME`); -ALTER TABLE `PARTITION_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000); diff --git metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql deleted file mode 100644 index aab664e..0000000 --- metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql +++ /dev/null @@ -1,160 +0,0 @@ --- --- HIVE-417 Implement Indexing in Hive --- -CREATE TABLE IF NOT EXISTS `IDXS` ( - `INDEX_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `DEFERRED_REBUILD` bit(1) NOT NULL, - `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `INDEX_TBL_ID` bigint(20) DEFAULT NULL, - `LAST_ACCESS_TIME` int(11) NOT NULL, - `ORIG_TBL_ID` bigint(20) DEFAULT NULL, - `SD_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`INDEX_ID`), - UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`), - KEY `IDXS_FK1` (`SD_ID`), - KEY `IDXS_FK2` (`INDEX_TBL_ID`), - KEY `IDXS_FK3` (`ORIG_TBL_ID`), - CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`), - CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`), - CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` ( - `INDEX_ID` bigint(20) NOT NULL, - `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`), - CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - - --- --- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs --- -CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` ( - `DB_ID` bigint(20) NOT NULL, - `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, - `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`DB_ID`,`PARAM_KEY`), - CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -ALTER TABLE `DBS` DROP COLUMN `PARAMETERS`; - --- --- HIVE-78 Authorization model for Hive --- -CREATE TABLE IF NOT EXISTS `DB_PRIVS` ( - `DB_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `DB_ID` bigint(20) DEFAULT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `DB_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`DB_GRANT_ID`), - UNIQUE KEY `DBPRIVILEGEINDEX` (`DB_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`DB_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `DB_PRIVS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `PART_COL_PRIVS` ( - `PART_COLUMN_GRANT_ID` bigint(20) NOT NULL, - `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_ID` bigint(20) DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`PART_COLUMN_GRANT_ID`), - KEY `PARTITIONCOLUMNPRIVILEGEINDEX` (`PART_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `PART_COL_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `PART_PRIVS` ( - `PART_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_ID` bigint(20) DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PART_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`PART_GRANT_ID`), - KEY `PARTPRIVILEGEINDEX` (`PART_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`PART_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `PART_PRIVS_FK1` FOREIGN KEY (`PART_ID`) REFERENCES `PARTITIONS` (`PART_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `ROLES` ( - `ROLE_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `OWNER_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `ROLE_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`ROLE_ID`), - UNIQUE KEY `ROLEENTITYINDEX` (`ROLE_NAME`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `ROLE_MAP` ( - `ROLE_GRANT_ID` bigint(20) NOT NULL, - `ADD_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `ROLE_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`ROLE_GRANT_ID`), - UNIQUE KEY `USERROLEMAPINDEX` (`PRINCIPAL_NAME`,`ROLE_ID`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `ROLE_MAP_FK1` FOREIGN KEY (`ROLE_ID`) REFERENCES `ROLES` (`ROLE_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `TBL_COL_PRIVS` ( - `TBL_COLUMN_GRANT_ID` bigint(20) NOT NULL, - `COLUMN_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_COL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`TBL_COLUMN_GRANT_ID`), - KEY `TABLECOLUMNPRIVILEGEINDEX` (`TBL_ID`,`COLUMN_NAME`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_COL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `TBL_COL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `TBL_PRIVS` ( - `TBL_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `TBL_ID` bigint(20) DEFAULT NULL, - PRIMARY KEY (`TBL_GRANT_ID`), - KEY `TABLEPRIVILEGEINDEX` (`TBL_ID`,`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`TBL_PRIV`,`GRANTOR`,`GRANTOR_TYPE`), - CONSTRAINT `TBL_PRIVS_FK1` FOREIGN KEY (`TBL_ID`) REFERENCES `TBLS` (`TBL_ID`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1; - -CREATE TABLE IF NOT EXISTS `GLOBAL_PRIVS` ( - `USER_GRANT_ID` bigint(20) NOT NULL, - `CREATE_TIME` int(11) NOT NULL, - `GRANT_OPTION` smallint(6) NOT NULL, - `GRANTOR` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `GRANTOR_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `PRINCIPAL_TYPE` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - `USER_PRIV` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, - PRIMARY KEY (`USER_GRANT_ID`), - UNIQUE KEY `GLOBALPRIVILEGEINDEX` (`PRINCIPAL_NAME`,`PRINCIPAL_TYPE`,`USER_PRIV`,`GRANTOR`,`GRANTOR_TYPE`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1;