You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by ng...@apache.org on 2021/04/06 15:39:53 UTC
[hive] 09/38: Adding schema changes for mysql and postgres as well
This is an automated email from the ASF dual-hosted git repository.
ngangam pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
commit b30173d4c27d9c6b44913c3c8f1f766b36990189
Author: Naveen Gangam <ng...@cloudera.com>
AuthorDate: Wed Nov 18 13:37:31 2020 -0500
Adding schema changes for mysql and postgres as well
---
.../src/main/sql/derby/hive-schema-4.0.0.derby.sql | 3 ++-
.../sql/derby/upgrade-3.2.0-to-4.0.0.derby.sql | 2 ++
.../src/main/sql/mysql/hive-schema-4.0.0.mysql.sql | 24 ++++++++++++++++++++
.../sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql | 26 ++++++++++++++++++++++
.../sql/postgres/hive-schema-4.0.0.postgres.sql | 25 ++++++++++++++++++++-
.../postgres/upgrade-3.2.0-to-4.0.0.postgres.sql | 25 +++++++++++++++++++++
6 files changed, 103 insertions(+), 2 deletions(-)
diff --git a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql
index 7829f0f..f51b712 100644
--- a/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/derby/hive-schema-4.0.0.derby.sql
@@ -818,7 +818,8 @@ CREATE TABLE "APP"."STORED_PROCS" (
CREATE UNIQUE INDEX "UNIQUESTOREDPROC" ON "STORED_PROCS" ("NAME", "DB_ID");
ALTER TABLE "STORED_PROCS" ADD CONSTRAINT "STOREDPROC_FK1" FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID");
-CREATE TABLE "APP"."DATACONNECTORS" ("NAME" VARCHAR(128) NOT NULL, "TYPE" VARCHAR(32) NOT NULL, "URL" VARCHAR(4000) NOT NULL, "COMMENT" VARCHAR(256), "OWNER_NAME" VARCHAR(256), "OWNER_TYPE" VARCHAR(10), "CREATE_TIME" INTEGER);
+
+CREATE TABLE "APP"."DATACONNECTORS" ("NAME" VARCHAR(128) NOT NULL, "TYPE" VARCHAR(32) NOT NULL, "URL" VARCHAR(4000) NOT NULL, "COMMENT" VARCHAR(256), "OWNER_NAME" VARCHAR(256), "OWNER_TYPE" VARCHAR(10), "CREATE_TIME" INTEGER NOT NULL);
CREATE TABLE "APP"."DATACONNECTOR_PARAMS" ("NAME" VARCHAR(128) NOT NULL, "PARAM_KEY" VARCHAR(180) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
ALTER TABLE "APP"."DATACONNECTORS" ADD CONSTRAINT "DATACONNECTORS_KEY_PK" PRIMARY KEY ("NAME");
ALTER TABLE "APP"."DATACONNECTOR_PARAMS" ADD CONSTRAINT "DATACONNECTOR_PARAMS_KEY_PK" PRIMARY KEY ("NAME", "PARAM_KEY");
diff --git a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-3.2.0-to-4.0.0.derby.sql b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-3.2.0-to-4.0.0.derby.sql
index 0bc9003..ed37748 100644
--- a/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-3.2.0-to-4.0.0.derby.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/derby/upgrade-3.2.0-to-4.0.0.derby.sql
@@ -149,9 +149,11 @@ ALTER TABLE COMPLETED_COMPACTIONS ADD CC_WORKER_VERSION varchar(128);
-- HIVE-24396
CREATE TABLE "APP"."DATACONNECTORS" ("DC_NAME" VARCHAR(128) NOT NULL, "TYPE" VARCHAR(128) NOT NULL, "COMMENT" VARCHAR(256), "OWNER_NAME" VARCHAR(256), "OWNER_TYPE" VARCHAR(10), "CREATE_TIME" INTEGER);
+CREATE TABLE "APP"."DATACONNECTORS" ("DC_NAME" VARCHAR(128) NOT NULL, "TYPE" VARCHAR(128) NOT NULL, "COMMENT" VARCHAR(256), "OWNER_NAME" VARCHAR(256), "OWNER_TYPE" VARCHAR(10), "CREATE_TIME" INTEGER NOT NULL);
CREATE TABLE "APP"."DATACONNECTOR_PARAMS" ("DC_NAME" VARCHAR(128) NOT NULL, "PARAM_KEY" VARCHAR(180) NOT NULL, "PARAM_VALUE" VARCHAR(4000), "COMMENT" VARCHAR(256));
ALTER TABLE "APP"."DBS" ADD COLUMN "TYPE" VARCHAR(32) DEFAULT 'NATIVE';
ALTER TABLE "APP"."DBS" ADD COLUMN "DATACONNECTOR_NAME" VARCHAR(128);
+UPDATE "APP"."DBS" SET TYPE='NATIVE' WHERE TYPE IS NULL;
ALTER TABLE "APP"."DATACONNECTORS" ADD CONSTRAINT "DATACONNECTORS_KEY_PK" PRIMARY KEY ("DC_NAME");
ALTER TABLE "APP"."DATACONNECTOR_PARAMS" ADD CONSTRAINT "DATACONNECTOR_PARAMS_KEY_PK" PRIMARY KEY ("DC_NAME", "PARAM_KEY");
ALTER TABLE "APP"."DATACONNECTOR_PARAMS" ADD CONSTRAINT "DC_NAME_FK1" FOREIGN KEY ("DC_NAME") REFERENCES "APP"."DATACONNECTORS" ("DC_NAME") ON DELETE NO ACTION ON UPDATE NO ACTION;
diff --git a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0.mysql.sql b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0.mysql.sql
index 8744319..48e6f7d 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0.mysql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mysql/hive-schema-4.0.0.mysql.sql
@@ -105,6 +105,9 @@ CREATE TABLE IF NOT EXISTS `DBS` (
`CTLG_NAME` varchar(256) NOT NULL DEFAULT 'hive',
`CREATE_TIME` INT(11),
`DB_MANAGED_LOCATION_URI` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin,
+ `TYPE` VARCHAR(32) DEFAULT 'NATIVE',
+ `DATACONNECTOR_NAME` VARCHAR(128),
+ `REMOTE_DBNAME` VARCHAR(128),
PRIMARY KEY (`DB_ID`),
UNIQUE KEY `UNIQUE_DATABASE` (`NAME`, `CTLG_NAME`),
CONSTRAINT `CTLG_FK1` FOREIGN KEY (`CTLG_NAME`) REFERENCES `CTLGS` (`NAME`)
@@ -1304,6 +1307,27 @@ CREATE TABLE PACKAGES (
CREATE UNIQUE INDEX UNIQUEPKG ON PACKAGES (NAME, DB_ID);
ALTER TABLE `PACKAGES` ADD CONSTRAINT `PACKAGES_FK1` FOREIGN KEY (`DB_ID`) REFERENCES DBS (`DB_ID`);
+-- HIVE-24396
+-- Create DataConnectors and DataConnector_Params tables
+CREATE TABLE `DATACONNECTORS` (
+ `NAME` VARCHAR(128) NOT NULL,
+ `TYPE` VARCHAR(32) NOT NULL,
+ `URL` VARCHAR(4000) NOT NULL,
+ `COMMENT` VARCHAR(256),
+ `OWNER_NAME` VARCHAR(256),
+ `OWNER_TYPE` VARCHAR(10),
+ `CREATE_TIME` INT(11) NOT NULL,
+ PRIMARY KEY (`NAME`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE `DATACONNECTOR_PARAMS` (
+ `NAME` VARCHAR(128) NOT NULL,
+ `PARAM_KEY` VARCHAR(180) NOT NULL,
+ `PARAM_VALUE` VARCHAR(4000),
+ PRIMARY KEY (`NAME`, `PARAM_KEY`),
+ CONSTRAINT `DATACONNECTOR_NAME_FK1` FOREIGN KEY (`NAME`) REFERENCES `DATACONNECTORS` (`NAME`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
-- -----------------------------------------------------------------
-- Record schema version. Should be the last step in the init script
-- -----------------------------------------------------------------
diff --git a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql
index 889247d..e86c8b0 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mysql/upgrade-3.2.0-to-4.0.0.mysql.sql
@@ -154,6 +154,32 @@ ALTER TABLE COMPLETED_COMPACTIONS ADD CC_INITIATOR_ID varchar(128);
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_INITIATOR_VERSION varchar(128);
ALTER TABLE COMPLETED_COMPACTIONS ADD CC_WORKER_VERSION varchar(128);
+-- HIVE-24396
+-- Create DataConnectors and DataConnector_Params tables
+CREATE TABLE `DATACONNECTORS` (
+ `NAME` VARCHAR(128) NOT NULL,
+ `TYPE` VARCHAR(32) NOT NULL,
+ `URL` VARCHAR(4000) NOT NULL,
+ `COMMENT` VARCHAR(256),
+ `OWNER_NAME` VARCHAR(256),
+ `OWNER_TYPE` VARCHAR(10),
+ `CREATE_TIME` INT(11) NOT NULL,
+ PRIMARY KEY (`NAME`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE `DATACONNECTOR_PARAMS` (
+ `NAME` VARCHAR(128) NOT NULL,
+ `PARAM_KEY` VARCHAR(180) NOT NULL,
+ `PARAM_VALUE` VARCHAR(4000),
+ PRIMARY KEY (`NAME`, `PARAM_KEY`),
+ CONSTRAINT `DATACONNECTOR_NAME_FK1` FOREIGN KEY (`NAME`) REFERENCES `DATACONNECTORS` (`NAME`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+ALTER TABLE `DBS` ADD COLUMN `TYPE` VARCHAR(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT 'NATIVE';
+UPDATE `DBS` SET `TYPE` = 'NATIVE' WHERE `TYPE` IS NULL;
+ALTER TABLE `DBS` ADD COLUMN `DATACONNECTOR_NAME` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_bin;
+ALTER TABLE `DBS` ADD COLUMN `REMOTE_DBNAME` VARCHAR(128) CHARACTER SET latin1 COLLATE latin1_bin;
+
-- These lines need to be last. Insert any changes above.
UPDATE VERSION SET SCHEMA_VERSION='4.0.0', VERSION_COMMENT='Hive release version 4.0.0' where VER_ID=1;
SELECT 'Finished upgrading MetaStore schema from 3.2.0 to 4.0.0' AS MESSAGE;
diff --git a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0.postgres.sql b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0.postgres.sql
index 7f21179..998b66e 100644
--- a/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0.postgres.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/postgres/hive-schema-4.0.0.postgres.sql
@@ -83,7 +83,10 @@ CREATE TABLE "DBS" (
"OWNER_TYPE" character varying(10) DEFAULT NULL::character varying,
"CTLG_NAME" varchar(256) DEFAULT 'hive' NOT NULL,
"CREATE_TIME" bigint,
- "DB_MANAGED_LOCATION_URI" character varying(4000)
+ "DB_MANAGED_LOCATION_URI" character varying(4000),
+ "TYPE" character varying(32) DEFAULT 'NATIVE',
+ "DATACONNECTOR_NAME" character varying(128),
+ "REMOTE_DBNAME" character varying(128)
);
@@ -1966,6 +1969,26 @@ CREATE TABLE "PACKAGES" (
CREATE UNIQUE INDEX "UNIQUEPKG" ON "PACKAGES" ("NAME", "DB_ID");
ALTER TABLE ONLY "PACKAGES" ADD CONSTRAINT "PACKAGES_FK1" FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") DEFERRABLE;
+-- HIVE-24396
+-- Create DataConnectors and DataConnector_Params tables
+CREATE TABLE "DATACONNECTORS" (
+ "NAME" character varying(128) NOT NULL,
+ "TYPE" character varying(32) NOT NULL,
+ "URL" character varying(4000) NOT NULL,
+ "COMMENT" character varying(256),
+ "OWNER_NAME" character varying(256),
+ "OWNER_TYPE" character varying(10),
+ "CREATE_TIME" INTEGER NOT NULL,
+ PRIMARY KEY ("NAME")
+);
+
+CREATE TABLE "DATACONNECTOR_PARAMS" (
+ "NAME" character varying(128) NOT NULL,
+ "PARAM_KEY" character varying(180) NOT NULL,
+ "PARAM_VALUE" character varying(4000),
+ PRIMARY KEY ("NAME", "PARAM_KEY"),
+ CONSTRAINT "DATACONNECTOR_NAME_FK1" FOREIGN KEY ("NAME") REFERENCES "DATACONNECTORS"("NAME") ON DELETE CASCADE
+);
-- -----------------------------------------------------------------
-- Record schema version. Should be the last step in the init script
diff --git a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-3.2.0-to-4.0.0.postgres.sql b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-3.2.0-to-4.0.0.postgres.sql
index 216a3ec..afbdacf 100644
--- a/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-3.2.0-to-4.0.0.postgres.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/postgres/upgrade-3.2.0-to-4.0.0.postgres.sql
@@ -278,6 +278,31 @@ ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_INITIATOR_ID" varchar(128);
ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_INITIATOR_VERSION" varchar(128);
ALTER TABLE "COMPLETED_COMPACTIONS" ADD "CC_WORKER_VERSION" varchar(128);
+-- HIVE-24396
+-- Create DataConnectors and DataConnector_Params tables
+CREATE TABLE "DATACONNECTORS" (
+ "NAME" character varying(128) NOT NULL,
+ "TYPE" character varying(32) NOT NULL,
+ "URL" character varying(4000) NOT NULL,
+ "COMMENT" character varying(256),
+ "OWNER_NAME" character varying(256),
+ "OWNER_TYPE" character varying(10),
+ "CREATE_TIME" INTEGER NOT NULL,
+ PRIMARY KEY ("NAME")
+);
+
+CREATE TABLE "DATACONNECTOR_PARAMS" (
+ "NAME" character varying(128) NOT NULL,
+ "PARAM_KEY" character varying(180) NOT NULL,
+ "PARAM_VALUE" character varying(4000),
+ PRIMARY KEY ("NAME", "PARAM_KEY"),
+ CONSTRAINT "DATACONNECTOR_NAME_FK1" FOREIGN KEY ("NAME") REFERENCES "DATACONNECTORS"("NAME") ON DELETE CASCADE
+);
+ALTER TABLE "DBS" ADD "TYPE" character varying(32);
+UPDATE "DBS" SET "TYPE"= 'NATIVE' WHERE "TYPE" IS NULL;
+ALTER TABLE "DBS" ADD "DATACONNECTOR_NAME" character varying(128);
+ALTER TABLE "DBS" ADD "REMOTE_DBNAME" character varying(128);
+
-- These lines need to be last. Insert any changes above.
UPDATE "VERSION" SET "SCHEMA_VERSION"='4.0.0', "VERSION_COMMENT"='Hive release version 4.0.0' where "VER_ID"=1;
SELECT 'Finished upgrading MetaStore schema from 3.2.0 to 4.0.0';