You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by da...@apache.org on 2019/03/22 06:38:51 UTC
[hive] branch master updated: HIVE-21462: Upgrading SQL server
backed metastore when changing data type of a column with constraints
(Ashutosh Bapat, reviewed by Daniel Dai)
This is an automated email from the ASF dual-hosted git repository.
daijy pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new e157814 HIVE-21462: Upgrading SQL server backed metastore when changing data type of a column with constraints (Ashutosh Bapat, reviewed by Daniel Dai)
e157814 is described below
commit e15781455aacf729c587b47d89d525d08eafb6b8
Author: Ashutosh Bapat <ab...@cloudera.com>
AuthorDate: Thu Mar 21 23:34:34 2019 -0700
HIVE-21462: Upgrading SQL server backed metastore when changing data type of a column with constraints (Ashutosh Bapat, reviewed by Daniel Dai)
Signed-off-by: Daniel Dai <da...@gmail.com>
---
.../sql/mssql/upgrade-2.1.0-to-2.2.0.mssql.sql | 47 ++++++++++++++++++++--
.../sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql | 27 ++++++++-----
.../sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql | 30 +++++++++-----
.../sql/mssql/upgrade-3.2.0-to-4.0.0.mssql.sql | 4 +-
4 files changed, 83 insertions(+), 25 deletions(-)
diff --git a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.1.0-to-2.2.0.mssql.sql b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.1.0-to-2.2.0.mssql.sql
index 64d8fca..b3f985c 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.1.0-to-2.2.0.mssql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.1.0-to-2.2.0.mssql.sql
@@ -1,14 +1,53 @@
SELECT 'Upgrading MetaStore schema from 2.1.0 to 2.2.0' AS MESSAGE;
+-- We can not change the datatype of a column with default value. Hence we first drop the default constraint
+-- and then change the datatype. We wrap the code to drop the default constraint in a stored procedure to avoid
+-- code duplicate. We create temporary stored procedures since we do not need them during normal
+-- metastore operation.
+CREATE PROCEDURE #DROP_DEFAULT_CONSTRAINT @TBL_NAME sysname, @COL_NAME sysname
+AS
+BEGIN
+ DECLARE @constraintname sysname
+ SELECT @constraintname = default_constraints.name
+ FROM sys.all_columns INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
+ INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
+ INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
+ WHERE schemas.name = 'dbo' AND tables.name = @TBL_NAME AND all_columns.name = @COL_NAME
+
+ IF (@constraintname IS NOT NULL)
+ BEGIN
+ DECLARE @sql nvarchar(max) = 'ALTER TABLE [dbo].' + QUOTENAME(@TBL_NAME) + ' DROP CONSTRAINT ' + QUOTENAME(@constraintname)
+ EXEC(@sql)
+ END
+END;
+
+-- Similarly for primary key constraint
+CREATE PROCEDURE #DROP_PRIMARY_KEY_CONSTRAINT @TBL_NAME sysname
+AS
+BEGIN
+ DECLARE @constraintname sysname
+ SELECT @constraintname = constraint_name
+ FROM information_schema.table_constraints
+ WHERE constraint_type = 'PRIMARY KEY' AND table_schema = 'dbo' AND table_name = @TBL_NAME
+ IF @constraintname IS NOT NULL
+ BEGIN
+ DECLARE @sql_pk nvarchar(max) = 'ALTER TABLE [dbo].' + QUOTENAME(@TBL_NAME) + ' DROP CONSTRAINT ' + @constraintname
+ EXEC(@sql_pk)
+ end
+END;
+
--:r 022-HIVE-14496.mssql.sql
-ALTER TABLE TBLS ADD IS_REWRITE_ENABLED bit NOT NULL DEFAULT(0);
+ALTER TABLE TBLS ADD IS_REWRITE_ENABLED bit NOT NULL CONSTRAINT DEFAULT_IS_REWRITE_ENABLED DEFAULT(0);
--:r 023-HIVE-10562.mssql.sql
ALTER TABLE NOTIFICATION_LOG ADD MESSAGE_FORMAT nvarchar(16);
--:r 024-HIVE-12274.mssql.sql
+EXEC #DROP_DEFAULT_CONSTRAINT "SERDE_PARAMS", "PARAM_VALUE";
ALTER TABLE "SERDE_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);
+EXEC #DROP_DEFAULT_CONSTRAINT "TABLE_PARAMS", "PARAM_VALUE";
ALTER TABLE "TABLE_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);
+EXEC #DROP_DEFAULT_CONSTRAINT "SD_PARAMS", "PARAM_VALUE";
ALTER TABLE "SD_PARAMS" ALTER COLUMN "PARAM_VALUE" nvarchar(MAX);
ALTER TABLE "TBLS" ALTER COLUMN "TBL_NAME" nvarchar(256);
@@ -20,8 +59,10 @@ ALTER TABLE "COMPLETED_TXN_COMPONENTS" ALTER COLUMN "CTC_TABLE" nvarchar(256);
-- A number of indices and constraints reference COLUMN_NAME. These have to be dropped before the not null constraint
--- can be added.
-ALTER TABLE COLUMNS_V2 DROP CONSTRAINT COLUMNS_PK;
+-- can be added. Earlier versions may not have created named constraints, so use IF EXISTS and also
+-- the stored procedure.
+ALTER TABLE COLUMNS_V2 DROP CONSTRAINT IF EXISTS COLUMNS_PK;
+EXEC #DROP_PRIMARY_KEY_CONSTRAINT COLUMNS_V2;
DROP INDEX PARTITIONCOLUMNPRIVILEGEINDEX ON PART_COL_PRIVS;
DROP INDEX TABLECOLUMNPRIVILEGEINDEX ON TBL_COL_PRIVS;
DROP INDEX PCS_STATS_IDX ON PART_COL_STATS;
diff --git a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
index 14c3deb..da42e77 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
@@ -62,7 +62,7 @@ CREATE TABLE WM_TRIGGER
"NAME" nvarchar(128) NOT NULL,
TRIGGER_EXPRESSION nvarchar(1024),
ACTION_EXPRESSION nvarchar(1024),
- IS_IN_UNMANAGED bit NOT NULL DEFAULT 0
+ IS_IN_UNMANAGED bit NOT NULL CONSTRAINT DEF_WMT_IS_IN_UNMANAGED DEFAULT 0
);
ALTER TABLE WM_TRIGGER ADD CONSTRAINT WM_TRIGGER_PK PRIMARY KEY (TRIGGER_ID);
@@ -110,10 +110,10 @@ ALTER TABLE "SERDES" ADD "DESERIALIZER_CLASS" nvarchar(4000);
ALTER TABLE "SERDES" ADD "SERDE_TYPE" int;
CREATE TABLE "I_SCHEMA" (
- "SCHEMA_ID" bigint primary key,
+ "SCHEMA_ID" bigint CONSTRAINT I_SCHEMA_PK primary key,
"SCHEMA_TYPE" int not null,
"NAME" nvarchar(256) unique,
- "DB_ID" bigint references "DBS" ("DB_ID"),
+ "DB_ID" bigint CONSTRAINT I_SCHEMA_DB_ID_FK references "DBS" ("DB_ID"),
"COMPATIBILITY" int not null,
"VALIDATION_LEVEL" int not null,
"CAN_EVOLVE" bit not null,
@@ -122,17 +122,17 @@ CREATE TABLE "I_SCHEMA" (
);
CREATE TABLE "SCHEMA_VERSION" (
- "SCHEMA_VERSION_ID" bigint primary key,
- "SCHEMA_ID" bigint references "I_SCHEMA" ("SCHEMA_ID"),
+ "SCHEMA_VERSION_ID" bigint CONSTRAINT SCHEMA_VERSION_PK primary key,
+ "SCHEMA_ID" bigint CONSTRAINT SCHEMA_VERSION_ID_FK references "I_SCHEMA" ("SCHEMA_ID"),
"VERSION" int not null,
"CREATED_AT" bigint not null,
- "CD_ID" bigint references "CDS" ("CD_ID"),
+ "CD_ID" bigint CONSTRAINT SCHEMA_VERSION_CD_ID_FK references "CDS" ("CD_ID"),
"STATE" int not null,
"DESCRIPTION" nvarchar(4000),
"SCHEMA_TEXT" varchar(max),
"FINGERPRINT" nvarchar(256),
"SCHEMA_VERSION_NAME" nvarchar(256),
- "SERDE_ID" bigint references "SERDES" ("SERDE_ID"),
+ "SERDE_ID" bigint CONSTRAINT SCHEMA_VERSION_SERDE_ID_FK references "SERDES" ("SERDE_ID"),
unique ("SCHEMA_ID", "VERSION")
);
@@ -162,7 +162,7 @@ ALTER TABLE MV_TABLES_USED ADD FOREIGN KEY(TBL_ID) REFERENCES TBLS (TBL_ID);
ALTER TABLE TBLS ADD OWNER_TYPE nvarchar(10) NULL;
-ALTER TABLE COMPLETED_TXN_COMPONENTS ADD CTC_TIMESTAMP datetime2 NOT NULL DEFAULT(CURRENT_TIMESTAMP);
+ALTER TABLE COMPLETED_TXN_COMPONENTS ADD CTC_TIMESTAMP datetime2 NOT NULL CONSTRAINT DEF_CTC_TIMESTAMP DEFAULT(CURRENT_TIMESTAMP);
CREATE INDEX COMPLETED_TXN_COMPONENTS_IDX ON COMPLETED_TXN_COMPONENTS (CTC_DATABASE, CTC_TABLE, CTC_PARTITION);
-- 034-HIVE-18489.mssql.sql
@@ -215,7 +215,11 @@ ALTER TABLE KEY_CONSTRAINTS ADD DEFAULT_VALUE VARCHAR(400);
ALTER TABLE KEY_CONSTRAINTS ALTER COLUMN PARENT_CD_ID bigint NULL;
+-- Need to drop index changing column to NOT NULL
+DROP INDEX HL_TXNID_INDEX ON HIVE_LOCKS;
ALTER TABLE HIVE_LOCKS ALTER COLUMN HL_TXNID bigint NOT NULL;
+CREATE NONCLUSTERED INDEX HL_TXNID_INDEX ON HIVE_LOCKS(HL_TXNID ASC)
+ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE TABLE REPL_TXN_MAP (
RTM_REPL_POLICY nvarchar(256) NOT NULL,
@@ -228,6 +232,7 @@ ALTER TABLE REPL_TXN_MAP ADD CONSTRAINT REPL_TXN_MAP_PK PRIMARY KEY (RTM_REPL_PO
-- Table SEQUENCE_TABLE is an internal table required by DataNucleus.
-- NOTE: Some versions of SchemaTool do not automatically generate this table.
-- See http://www.datanucleus.org/servlet/jira/browse/NUCRDBMS-416
+IF OBJECT_ID('SEQUENCE_TABLE', 'U') IS NULL
CREATE TABLE SEQUENCE_TABLE
(
SEQUENCE_NAME nvarchar(256) NOT NULL,
@@ -253,8 +258,8 @@ CREATE UNIQUE INDEX UNIQUE_CTLG ON CTLGS ("NAME");
-- Insert a default value. The location is TBD. Hive will fix this when it starts
INSERT INTO CTLGS VALUES (1, 'hive', 'Default catalog for Hive', 'TBD');
--- Drop the unique index on DBS
-DROP INDEX UNIQUEDATABASE ON DBS;
+-- Drop the unique index on DBS, it may not exist in the earlier versions.
+DROP INDEX IF EXISTS UNIQUEDATABASE ON DBS;
-- Add the new column to the DBS table, can't put in the not null constraint yet
ALTER TABLE DBS ADD CTLG_NAME nvarchar(256);
@@ -304,7 +309,7 @@ UPDATE NOTIFICATION_LOG
CREATE TABLE MIN_HISTORY_LEVEL (
MHL_TXNID bigint NOT NULL,
MHL_MIN_OPEN_TXNID bigint NOT NULL,
-PRIMARY KEY CLUSTERED
+CONSTRAINT MIN_HISTORY_LEVEL_PK PRIMARY KEY CLUSTERED
(
MHL_TXNID ASC
)
diff --git a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql
index bc71fb8..2469598 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.0.0-to-3.1.0.mssql.sql
@@ -2,27 +2,39 @@ SELECT 'Upgrading MetaStore schema from 3.0.0 to 3.1.0' AS MESSAGE;
-- HIVE-19440
ALTER TABLE GLOBAL_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX GLOBAL_PRIVS.GLOBALPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE GLOBAL_PRIVS DROP CONSTRAINT IF EXISTS GLOBALPRIVILEGEINDEX;
+DROP INDEX IF EXISTS GLOBAL_PRIVS.GLOBALPRIVILEGEINDEX;
CREATE UNIQUE INDEX GLOBALPRIVILEGEINDEX ON GLOBAL_PRIVS (AUTHORIZER,PRINCIPAL_NAME,PRINCIPAL_TYPE,USER_PRIV,GRANTOR,GRANTOR_TYPE);
ALTER TABLE DB_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX DB_PRIVS.DBPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE DB_PRIVS DROP CONSTRAINT IF EXISTS DBPRIVILEGEINDEX;
+DROP INDEX IF EXISTS DB_PRIVS.DBPRIVILEGEINDEX;
CREATE UNIQUE INDEX DBPRIVILEGEINDEX ON DB_PRIVS (AUTHORIZER,DB_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,DB_PRIV,GRANTOR,GRANTOR_TYPE);
ALTER TABLE TBL_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX TBL_PRIVS.TABLEPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE TBL_PRIVS DROP CONSTRAINT IF EXISTS TABLEPRIVILEGEINDEX;
+DROP INDEX IF EXISTS TBL_PRIVS.TABLEPRIVILEGEINDEX;
CREATE INDEX TABLEPRIVILEGEINDEX ON TBL_PRIVS (AUTHORIZER,TBL_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_PRIV,GRANTOR,GRANTOR_TYPE);
ALTER TABLE PART_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX PART_PRIVS.PARTPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE PART_PRIVS DROP CONSTRAINT IF EXISTS PARTPRIVILEGEINDEX;
+DROP INDEX IF EXISTS PART_PRIVS.PARTPRIVILEGEINDEX;
CREATE INDEX PARTPRIVILEGEINDEX ON PART_PRIVS (AUTHORIZER,PART_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_PRIV,GRANTOR,GRANTOR_TYPE);
ALTER TABLE TBL_COL_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX TBL_COL_PRIVS.TABLECOLUMNPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE TBL_COL_PRIVS DROP CONSTRAINT IF EXISTS TABLECOLUMNPRIVILEGEINDEX;
+DROP INDEX IF EXISTS TBL_COL_PRIVS.TABLECOLUMNPRIVILEGEINDEX;
CREATE INDEX TABLECOLUMNPRIVILEGEINDEX ON TBL_COL_PRIVS (AUTHORIZER,TBL_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_COL_PRIV,GRANTOR,GRANTOR_TYPE);
ALTER TABLE PART_COL_PRIVS ADD AUTHORIZER nvarchar(128) NULL;
-DROP INDEX PART_COL_PRIVS.PARTITIONCOLUMNPRIVILEGEINDEX;
+-- Earlier versions have unique constraint instead of unique index.
+ALTER TABLE PART_COL_PRIVS DROP CONSTRAINT IF EXISTS PARTITIONCOLUMNPRIVILEGEINDEX;
+DROP INDEX IF EXISTS PART_COL_PRIVS.PARTITIONCOLUMNPRIVILEGEINDEX;
CREATE INDEX PARTITIONCOLUMNPRIVILEGEINDEX ON PART_COL_PRIVS (AUTHORIZER,PART_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_COL_PRIV,GRANTOR,GRANTOR_TYPE);
-- HIVE-19340
@@ -32,17 +44,17 @@ CREATE INDEX TAB_COL_STATS_IDX ON TAB_COL_STATS (CAT_NAME, DB_NAME, TABLE_NAME,
-- HIVE-19027
-- add column MATERIALIZATION_TIME (bigint) to MV_CREATION_METADATA table
-ALTER TABLE MV_CREATION_METADATA ADD MATERIALIZATION_TIME bigint NOT NULL DEFAULT(0);
+ALTER TABLE MV_CREATION_METADATA ADD MATERIALIZATION_TIME bigint NOT NULL CONSTRAINT DEF_MATERIALIZATION_TIME DEFAULT(0);
-- add column CTC_UPDATE_DELETE (char) to COMPLETED_TXN_COMPONENTS table
-ALTER TABLE COMPLETED_TXN_COMPONENTS ADD CTC_UPDATE_DELETE char(1) NOT NULL DEFAULT('N');
+ALTER TABLE COMPLETED_TXN_COMPONENTS ADD CTC_UPDATE_DELETE char(1) NOT NULL CONSTRAINT DEF_CTC_UPDATE_DELETE DEFAULT('N');
CREATE TABLE MATERIALIZATION_REBUILD_LOCKS (
MRL_TXN_ID bigint NOT NULL,
MRL_DB_NAME nvarchar(128) NOT NULL,
MRL_TBL_NAME nvarchar(256) NOT NULL,
MRL_LAST_HEARTBEAT bigint NOT NULL,
-PRIMARY KEY CLUSTERED
+CONSTRAINT PK_MATERIALIZATION_REBUILD_LOCKS PRIMARY KEY CLUSTERED
(
MRL_TXN_ID ASC
)
diff --git a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.2.0-to-4.0.0.mssql.sql b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.2.0-to-4.0.0.mssql.sql
index fccd34a..b077b41 100644
--- a/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.2.0-to-4.0.0.mssql.sql
+++ b/standalone-metastore/metastore-server/src/main/sql/mssql/upgrade-3.2.0-to-4.0.0.mssql.sql
@@ -1,8 +1,8 @@
SELECT 'Upgrading MetaStore schema from 3.2.0 to 4.0.0' AS MESSAGE;
-- HIVE-19416
-ALTER TABLE TBLS ADD WRITE_ID bigint NOT NULL DEFAULT 0;
-ALTER TABLE PARTITIONS ADD WRITE_ID bigint NOT NULL DEFAULT 0;
+ALTER TABLE TBLS ADD WRITE_ID bigint NOT NULL CONSTRAINT DEF_TBLS_WRITE_ID DEFAULT(0);
+ALTER TABLE PARTITIONS ADD WRITE_ID bigint NOT NULL CONSTRAINT DEF_PARITITIONS_WRITE_ID DEFAULT(0);
-- HIVE-20793
ALTER TABLE WM_RESOURCEPLAN ADD NS nvarchar(128);