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 Commit: 11a7164f0b4c54fc1e9f54f842719537abb6ac53 Parents: 32e29cc Author: Sankar Hariappan <sank...@apache.org> Authored: Sat May 12 00:29:16 2018 +0530 Committer: Sankar Hariappan <sank...@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';
