You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by vg...@apache.org on 2018/05/15 22:40:42 UTC
[22/50] [abbrv] hive git commit: HIVE-18193: Migrate existing ACID
tables to use write id per table rather than global transaction id (Sankar
Hariappan, reviewed by Mahesh Kumar Behera, Thejas M Nair, Eugene Koifman)
HIVE-18193: Migrate existing ACID tables to use write id per table rather than global transaction id (Sankar Hariappan, reviewed by Mahesh Kumar Behera, Thejas M Nair, Eugene Koifman)
Project: http://git-wip-us.apache.org/repos/asf/hive/repo
Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/11a7164f
Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/11a7164f
Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/11a7164f
Branch: refs/heads/branch-3.0.0
Commit: 11a7164f0b4c54fc1e9f54f842719537abb6ac53
Parents: 32e29cc
Author: Sankar Hariappan <sa...@apache.org>
Authored: Sat May 12 00:29:16 2018 +0530
Committer: Sankar Hariappan <sa...@apache.org>
Committed: Sat May 12 00:29:16 2018 +0530
----------------------------------------------------------------------
.../upgrade/derby/057-HIVE-18193.derby.sql | 24 ++++++++++++
.../hadoop/hive/metastore/txn/TxnDbUtil.java | 4 +-
.../main/sql/derby/hive-schema-3.0.0.derby.sql | 4 +-
.../sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql | 25 +++++++++++++
.../main/sql/mssql/hive-schema-3.0.0.mssql.sql | 4 +-
.../sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql | 25 +++++++++++++
.../main/sql/mysql/hive-schema-3.0.0.mysql.sql | 2 +-
.../sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql | 27 ++++++++++++++
.../sql/oracle/hive-schema-3.0.0.oracle.sql | 22 +++++------
.../oracle/upgrade-2.3.0-to-3.0.0.oracle.sql | 39 ++++++++++++++++----
.../sql/postgres/hive-schema-3.0.0.postgres.sql | 4 +-
.../upgrade-2.3.0-to-3.0.0.postgres.sql | 25 +++++++++++++
12 files changed, 178 insertions(+), 27 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/metastore/scripts/upgrade/derby/057-HIVE-18193.derby.sql
----------------------------------------------------------------------
diff --git a/metastore/scripts/upgrade/derby/057-HIVE-18193.derby.sql b/metastore/scripts/upgrade/derby/057-HIVE-18193.derby.sql
new file mode 100644
index 0000000..499d06e
--- /dev/null
+++ b/metastore/scripts/upgrade/derby/057-HIVE-18193.derby.sql
@@ -0,0 +1,24 @@
+
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND CAST(PARAM_VALUE AS VARCHAR(128))='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) NEXT_TXN_ID;
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND CAST(PARAM_VALUE AS VARCHAR(128))='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) TXN_INFO;
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java b/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
index cf89ab2..4597166 100644
--- a/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
+++ b/standalone-metastore/src/main/java/org/apache/hadoop/hive/metastore/txn/TxnDbUtil.java
@@ -82,14 +82,14 @@ public final class TxnDbUtil {
" TXN_HOST varchar(128) NOT NULL)");
stmt.execute("CREATE TABLE TXN_COMPONENTS (" +
- " TC_TXNID bigint REFERENCES TXNS (TXN_ID)," +
+ " TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID)," +
" TC_DATABASE varchar(128) NOT NULL," +
" TC_TABLE varchar(128)," +
" TC_PARTITION varchar(767)," +
" TC_OPERATION_TYPE char(1) NOT NULL," +
" TC_WRITEID bigint)");
stmt.execute("CREATE TABLE COMPLETED_TXN_COMPONENTS (" +
- " CTC_TXNID bigint," +
+ " CTC_TXNID bigint NOT NULL," +
" CTC_DATABASE varchar(128) NOT NULL," +
" CTC_TABLE varchar(128)," +
" CTC_PARTITION varchar(767)," +
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/derby/hive-schema-3.0.0.derby.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/derby/hive-schema-3.0.0.derby.sql b/standalone-metastore/src/main/sql/derby/hive-schema-3.0.0.derby.sql
index 8e09755..e818e1b 100644
--- a/standalone-metastore/src/main/sql/derby/hive-schema-3.0.0.derby.sql
+++ b/standalone-metastore/src/main/sql/derby/hive-schema-3.0.0.derby.sql
@@ -507,7 +507,7 @@ CREATE TABLE TXNS (
);
CREATE TABLE TXN_COMPONENTS (
- TC_TXNID bigint REFERENCES TXNS (TXN_ID),
+ TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID),
TC_DATABASE varchar(128) NOT NULL,
TC_TABLE varchar(128),
TC_PARTITION varchar(767),
@@ -518,7 +518,7 @@ CREATE TABLE TXN_COMPONENTS (
CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID);
CREATE TABLE COMPLETED_TXN_COMPONENTS (
- CTC_TXNID bigint,
+ CTC_TXNID bigint NOT NULL,
CTC_DATABASE varchar(128) NOT NULL,
CTC_TABLE varchar(256),
CTC_PARTITION varchar(767),
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql b/standalone-metastore/src/main/sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql
index 73bef36..7b7a8a2 100644
--- a/standalone-metastore/src/main/sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql
+++ b/standalone-metastore/src/main/sql/derby/upgrade-2.3.0-to-3.0.0.derby.sql
@@ -254,6 +254,31 @@ CREATE TABLE "APP"."RUNTIME_STATS" (
CREATE INDEX IDX_RUNTIME_STATS_CREATE_TIME ON RUNTIME_STATS(CREATE_TIME);
+-- HIVE-18193
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND CAST(PARAM_VALUE AS VARCHAR(128))='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) NEXT_WRITE;
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND CAST(PARAM_VALUE AS VARCHAR(128))='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) TXN_INFO;
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
+
-- This needs to be the last thing done. Insert any changes above this line.
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/11a7164f/standalone-metastore/src/main/sql/mssql/hive-schema-3.0.0.mssql.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/mssql/hive-schema-3.0.0.mssql.sql b/standalone-metastore/src/main/sql/mssql/hive-schema-3.0.0.mssql.sql
index 51df92c..c88fb18 100644
--- a/standalone-metastore/src/main/sql/mssql/hive-schema-3.0.0.mssql.sql
+++ b/standalone-metastore/src/main/sql/mssql/hive-schema-3.0.0.mssql.sql
@@ -1022,7 +1022,7 @@ PRIMARY KEY CLUSTERED
);
CREATE TABLE COMPLETED_TXN_COMPONENTS(
- CTC_TXNID bigint NULL,
+ CTC_TXNID bigint NOT NULL,
CTC_DATABASE nvarchar(128) NOT NULL,
CTC_TABLE nvarchar(128) NULL,
CTC_PARTITION nvarchar(767) NULL,
@@ -1091,7 +1091,7 @@ PRIMARY KEY CLUSTERED
);
CREATE TABLE TXN_COMPONENTS(
- TC_TXNID bigint NULL,
+ TC_TXNID bigint NOT NULL,
TC_DATABASE nvarchar(128) NOT NULL,
TC_TABLE nvarchar(128) NULL,
TC_PARTITION nvarchar(767) NULL,
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql b/standalone-metastore/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
index a7232dd..14c3deb 100644
--- a/standalone-metastore/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
+++ b/standalone-metastore/src/main/sql/mssql/upgrade-2.3.0-to-3.0.0.mssql.sql
@@ -321,6 +321,31 @@ CREATE TABLE RUNTIME_STATS (
CREATE INDEX IDX_RUNTIME_STATS_CREATE_TIME ON RUNTIME_STATS(CREATE_TIME);
+-- HIVE-18193
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND PARAM_VALUE='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) NEXT_WRITE;
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND PARAM_VALUE='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) TXN_INFO;
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
+
-- These lines need to be last. Insert any changes above.
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/11a7164f/standalone-metastore/src/main/sql/mysql/hive-schema-3.0.0.mysql.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/mysql/hive-schema-3.0.0.mysql.sql b/standalone-metastore/src/main/sql/mysql/hive-schema-3.0.0.mysql.sql
index 8e55e94..c54df55 100644
--- a/standalone-metastore/src/main/sql/mysql/hive-schema-3.0.0.mysql.sql
+++ b/standalone-metastore/src/main/sql/mysql/hive-schema-3.0.0.mysql.sql
@@ -983,7 +983,7 @@ CREATE TABLE TXNS (
CREATE TABLE TXN_COMPONENTS (
TC_TXNID bigint NOT NULL,
TC_DATABASE varchar(128) NOT NULL,
- TC_TABLE varchar(128) NOT NULL,
+ TC_TABLE varchar(128),
TC_PARTITION varchar(767),
TC_OPERATION_TYPE char(1) NOT NULL,
TC_WRITEID bigint,
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql b/standalone-metastore/src/main/sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql
index 9a48346..9b87563 100644
--- a/standalone-metastore/src/main/sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql
+++ b/standalone-metastore/src/main/sql/mysql/upgrade-2.3.0-to-3.0.0.mysql.sql
@@ -292,6 +292,33 @@ CREATE TABLE RUNTIME_STATS (
CREATE INDEX IDX_RUNTIME_STATS_CREATE_TIME ON RUNTIME_STATS(CREATE_TIME);
+-- HIVE-18193
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND PARAM_VALUE='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) NEXT_WRITE;
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND PARAM_VALUE='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) TXN_INFO;
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
+
+ALTER TABLE TXN_COMPONENTS MODIFY COLUMN TC_TABLE varchar(128) NULL;
+
-- These lines need to be last. Insert any changes above.
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/11a7164f/standalone-metastore/src/main/sql/oracle/hive-schema-3.0.0.oracle.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/oracle/hive-schema-3.0.0.oracle.sql b/standalone-metastore/src/main/sql/oracle/hive-schema-3.0.0.oracle.sql
index 3a12e08..63cc1f7 100644
--- a/standalone-metastore/src/main/sql/oracle/hive-schema-3.0.0.oracle.sql
+++ b/standalone-metastore/src/main/sql/oracle/hive-schema-3.0.0.oracle.sql
@@ -958,7 +958,7 @@ CREATE TABLE TXNS (
) ROWDEPENDENCIES;
CREATE TABLE TXN_COMPONENTS (
- TC_TXNID NUMBER(19) REFERENCES TXNS (TXN_ID),
+ TC_TXNID NUMBER(19) NOT NULL REFERENCES TXNS (TXN_ID),
TC_DATABASE VARCHAR2(128) NOT NULL,
TC_TABLE VARCHAR2(128),
TC_PARTITION VARCHAR2(767) NULL,
@@ -969,7 +969,7 @@ CREATE TABLE TXN_COMPONENTS (
CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID);
CREATE TABLE COMPLETED_TXN_COMPONENTS (
- CTC_TXNID NUMBER(19),
+ CTC_TXNID NUMBER(19) NOT NULL,
CTC_DATABASE VARCHAR2(128) NOT NULL,
CTC_TABLE VARCHAR2(256),
CTC_PARTITION VARCHAR2(767),
@@ -1066,26 +1066,26 @@ CREATE TABLE WRITE_SET (
);
CREATE TABLE TXN_TO_WRITE_ID (
- T2W_TXNID number(19) NOT NULL,
- T2W_DATABASE varchar(128) NOT NULL,
- T2W_TABLE varchar(256) NOT NULL,
- T2W_WRITEID number(19) NOT NULL
+ T2W_TXNID NUMBER(19) NOT NULL,
+ T2W_DATABASE VARCHAR2(128) NOT NULL,
+ T2W_TABLE VARCHAR2(256) NOT NULL,
+ T2W_WRITEID NUMBER(19) NOT NULL
);
CREATE UNIQUE INDEX TBL_TO_TXN_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID);
CREATE UNIQUE INDEX TBL_TO_WRITE_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_WRITEID);
CREATE TABLE NEXT_WRITE_ID (
- NWI_DATABASE varchar(128) NOT NULL,
- NWI_TABLE varchar(256) NOT NULL,
- NWI_NEXT number(19) NOT NULL
+ NWI_DATABASE VARCHAR2(128) NOT NULL,
+ NWI_TABLE VARCHAR2(256) NOT NULL,
+ NWI_NEXT NUMBER(19) NOT NULL
);
CREATE UNIQUE INDEX NEXT_WRITE_ID_IDX ON NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE);
CREATE TABLE MIN_HISTORY_LEVEL (
- MHL_TXNID number(19) NOT NULL,
- MHL_MIN_OPEN_TXNID number(19) NOT NULL,
+ MHL_TXNID NUMBER(19) NOT NULL,
+ MHL_MIN_OPEN_TXNID NUMBER(19) NOT NULL,
PRIMARY KEY(MHL_TXNID)
);
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql b/standalone-metastore/src/main/sql/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql
index 3be7e65..ce3437f 100644
--- a/standalone-metastore/src/main/sql/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql
+++ b/standalone-metastore/src/main/sql/oracle/upgrade-2.3.0-to-3.0.0.oracle.sql
@@ -191,19 +191,19 @@ UPDATE DBS
-- HIVE-18192
CREATE TABLE TXN_TO_WRITE_ID (
- T2W_TXNID number(19) NOT NULL,
- T2W_DATABASE varchar(128) NOT NULL,
- T2W_TABLE varchar(256) NOT NULL,
- T2W_WRITEID number(19) NOT NULL
+ T2W_TXNID NUMBER(19) NOT NULL,
+ T2W_DATABASE VARCHAR2(128) NOT NULL,
+ T2W_TABLE VARCHAR2(256) NOT NULL,
+ T2W_WRITEID NUMBER(19) NOT NULL
);
CREATE UNIQUE INDEX TBL_TO_TXN_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID);
CREATE UNIQUE INDEX TBL_TO_WRITE_ID_IDX ON TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_WRITEID);
CREATE TABLE NEXT_WRITE_ID (
- NWI_DATABASE varchar(128) NOT NULL,
- NWI_TABLE varchar(256) NOT NULL,
- NWI_NEXT number(19) NOT NULL
+ NWI_DATABASE VARCHAR2(128) NOT NULL,
+ NWI_TABLE VARCHAR2(256) NOT NULL,
+ NWI_NEXT NUMBER(19) NOT NULL
);
CREATE UNIQUE INDEX NEXT_WRITE_ID_IDX ON NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE);
@@ -310,6 +310,31 @@ CREATE TABLE RUNTIME_STATS (
CREATE INDEX IDX_RUNTIME_STATS_CREATE_TIME ON RUNTIME_STATS(CREATE_TIME);
+-- HIVE-18193
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND to_char(PARAM_VALUE)='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) NEXT_WRITE;
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT DB.NAME, TBL_INFO.TBL_NAME FROM DBS DB,
+ (SELECT TBL.DB_ID, TBL.TBL_NAME FROM TBLS TBL,
+ (SELECT TBL_ID FROM TABLE_PARAMS WHERE PARAM_KEY='transactional' AND to_char(PARAM_VALUE)='true') TBL_PARAM
+ WHERE TBL.TBL_ID=TBL_PARAM.TBL_ID) TBL_INFO
+ where DB.DB_ID=TBL_INFO.DB_ID) DB_TBL_NAME,
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) TXN_INFO;
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
+
-- These lines need to be last. Insert any changes above.
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/11a7164f/standalone-metastore/src/main/sql/postgres/hive-schema-3.0.0.postgres.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/postgres/hive-schema-3.0.0.postgres.sql b/standalone-metastore/src/main/sql/postgres/hive-schema-3.0.0.postgres.sql
index 0152f48..d210a55 100644
--- a/standalone-metastore/src/main/sql/postgres/hive-schema-3.0.0.postgres.sql
+++ b/standalone-metastore/src/main/sql/postgres/hive-schema-3.0.0.postgres.sql
@@ -1645,7 +1645,7 @@ CREATE TABLE TXNS (
);
CREATE TABLE TXN_COMPONENTS (
- TC_TXNID bigint REFERENCES TXNS (TXN_ID),
+ TC_TXNID bigint NOT NULL REFERENCES TXNS (TXN_ID),
TC_DATABASE varchar(128) NOT NULL,
TC_TABLE varchar(128),
TC_PARTITION varchar(767) DEFAULT NULL,
@@ -1656,7 +1656,7 @@ CREATE TABLE TXN_COMPONENTS (
CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS USING hash (TC_TXNID);
CREATE TABLE COMPLETED_TXN_COMPONENTS (
- CTC_TXNID bigint,
+ CTC_TXNID bigint NOT NULL,
CTC_DATABASE varchar(128) NOT NULL,
CTC_TABLE varchar(256),
CTC_PARTITION varchar(767),
http://git-wip-us.apache.org/repos/asf/hive/blob/11a7164f/standalone-metastore/src/main/sql/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql
----------------------------------------------------------------------
diff --git a/standalone-metastore/src/main/sql/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql b/standalone-metastore/src/main/sql/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql
index fed8a93..f2571d8 100644
--- a/standalone-metastore/src/main/sql/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql
+++ b/standalone-metastore/src/main/sql/postgres/upgrade-2.3.0-to-3.0.0.postgres.sql
@@ -329,6 +329,31 @@ CREATE TABLE RUNTIME_STATS (
CREATE INDEX IDX_RUNTIME_STATS_CREATE_TIME ON RUNTIME_STATS(CREATE_TIME);
+-- HIVE-18193
+-- Populate NEXT_WRITE_ID for each Transactional table and set next write ID same as next txn ID
+INSERT INTO NEXT_WRITE_ID (NWI_DATABASE, NWI_TABLE, NWI_NEXT)
+ SELECT * FROM
+ (SELECT "DB"."NAME", "TBL_INFO"."TBL_NAME" FROM "DBS" "DB",
+ (SELECT "TBL"."DB_ID", "TBL"."TBL_NAME" FROM "TBLS" "TBL",
+ (SELECT "TBL_ID" FROM "TABLE_PARAMS" WHERE "PARAM_KEY"='transactional' AND "PARAM_VALUE"='true') "TBL_PARAM"
+ WHERE "TBL"."TBL_ID"="TBL_PARAM"."TBL_ID") "TBL_INFO"
+ where "DB"."DB_ID"="TBL_INFO"."DB_ID") "DB_TBL_NAME",
+ (SELECT NTXN_NEXT FROM NEXT_TXN_ID) "NEXT_WRITE";
+
+-- Populate TXN_TO_WRITE_ID for each aborted/open txns and set write ID equal to txn ID
+INSERT INTO TXN_TO_WRITE_ID (T2W_DATABASE, T2W_TABLE, T2W_TXNID, T2W_WRITEID)
+ SELECT * FROM
+ (SELECT "DB"."NAME", "TBL_INFO"."TBL_NAME" FROM "DBS" "DB",
+ (SELECT "TBL"."DB_ID", "TBL"."TBL_NAME" FROM "TBLS" "TBL",
+ (SELECT "TBL_ID" FROM "TABLE_PARAMS" WHERE "PARAM_KEY"='transactional' AND "PARAM_VALUE"='true') "TBL_PARAM"
+ WHERE "TBL"."TBL_ID"="TBL_PARAM"."TBL_ID") "TBL_INFO"
+ where "DB"."DB_ID"="TBL_INFO"."DB_ID") "DB_TBL_NAME",
+ (SELECT TXN_ID, TXN_ID as WRITE_ID FROM TXNS) "TXN_INFO";
+
+-- Update TXN_COMPONENTS and COMPLETED_TXN_COMPONENTS for write ID which is same as txn ID
+UPDATE TXN_COMPONENTS SET TC_WRITEID = TC_TXNID;
+UPDATE COMPLETED_TXN_COMPONENTS SET CTC_WRITEID = CTC_TXNID;
+
-- These lines need to be last. Insert any changes above.
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';