Author: nzhang
Date: Fri Nov 4 16:43:44 2011
New Revision: 1197646
URL: http://svn.apache.org/viewvc?rev=1197646&view=rev
Log:
HIVE-2366. Metastore upgrade scripts for HIVE-2246 do not migrate indexes nor
rename the old COLUMNS table (Sohan Jain via Ning Zhang)
Modified:
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
hive/branches/branch-0.8/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql
Modified:
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
URL:
http://svn.apache.org/viewvc/hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql?rev=1197646&r1=1197645&r2=1197646&view=diff
==============================================================================
---
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
(original)
+++
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
Fri Nov 4 16:43:44 2011
@@ -91,3 +91,31 @@ UPDATE SDS sd
SET sd.CD_ID =
(SELECT tt.CD_ID FROM SESSION.TMP_TBL tt WHERE tt.SD_ID = sd.SD_ID)
WHERE sd.SD_ID IN (SELECT SD_ID FROM SESSION.TMP_TBL);
+
+/*
+ * Migrate IDXS
+ */
+INSERT INTO CDS (CD_ID)
+SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL ORDER BY i.SD_ID;
+
+UPDATE SDS
+ SET CD_ID = SD_ID
+WHERE SD_ID in
+(SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL ORDER BY i.SD_ID);
+
+INSERT INTO COLUMNS_V2
+ (CD_ID, COMMENT, COLUMN_NAME, TYPE_NAME, INTEGER_IDX)
+SELECT
+ c.SD_ID, c.COMMENT, c.COLUMN_NAME, c.TYPE_NAME, c.INTEGER_IDX
+FROM
+ COLUMNS c
+JOIN
+ IDXS i
+ON
+ i.SD_ID = c.SD_ID
+;
+
+/*
+ * rename the old COLUMNS table
+ */
+RENAME TABLE COLUMNS TO COLUMNS_OLD;
Modified:
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
URL:
http://svn.apache.org/viewvc/hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql?rev=1197646&r1=1197645&r2=1197646&view=diff
==============================================================================
---
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
(original)
+++
hive/branches/branch-0.8/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
Fri Nov 4 16:43:44 2011
@@ -11,3 +11,5 @@ ALTER TABLE SDS DROP COLUMN CD_ID;
DROP TABLE COLUMNS_V2;
DROP TABLE CDS;
+
+RENAME COLUMNS_OLD TO COLUMNS;
Modified:
hive/branches/branch-0.8/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql
URL:
http://svn.apache.org/viewvc/hive/branches/branch-0.8/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql?rev=1197646&r1=1197645&r2=1197646&view=diff
==============================================================================
---
hive/branches/branch-0.8/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql
(original)
+++
hive/branches/branch-0.8/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql
Fri Nov 4 16:43:44 2011
@@ -7,10 +7,12 @@ DROP PROCEDURE IF EXISTS CREATE_SDS $$
DROP PROCEDURE IF EXISTS CREATE_TABLES $$
DROP PROCEDURE IF EXISTS MIGRATE_TABLES $$
DROP PROCEDURE IF EXISTS MIGRATE_PARTITIONS $$
+DROP PROCEDURE IF EXISTS MIGRATE_IDXS $$
DROP PROCEDURE IF EXISTS MIGRATE $$
DROP PROCEDURE IF EXISTS PRE_MIGRATE $$
-DROP PROCEDURE IF EXISTS RENAME_TMP_COLUMNS $$
+DROP PROCEDURE IF EXISTS RENAME_OLD_COLUMNS $$
DROP PROCEDURE IF EXISTS CREATE_TABLE_SDS $$
+DROP PROCEDURE IF EXISTS POST_MIGRATE $$
/* Call this procedure to revert all changes by this script */
CREATE PROCEDURE REVERT()
@@ -20,10 +22,11 @@ CREATE PROCEDURE REVERT()
;
ALTER TABLE SDS
DROP COLUMN CD_ID
- ;
+ ;
DROP TABLE IF EXISTS COLUMNS_V2;
DROP TABLE IF EXISTS TABLE_SDS;
DROP TABLE IF EXISTS CDS;
+ RENAME COLUMNS_OLD TO COLUMNS;
END $$
@@ -31,7 +34,7 @@ CREATE PROCEDURE REVERT()
* - add the column CD_ID
* - add a foreign key on CD_ID
* - create an index on CD_ID
- */
+ */
CREATE PROCEDURE ALTER_SDS()
BEGIN
ALTER TABLE SDS
@@ -44,7 +47,7 @@ CREATE PROCEDURE ALTER_SDS()
FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`)
;
SELECT 'Created a FK Constraint on CD_ID in SDS';
- CREATE INDEX `SDS_N50` ON SDS
+ CREATE INDEX `SDS_N50` ON SDS
(CD_ID)
;
SELECT 'Added an index on CD_ID in SDS';
@@ -81,7 +84,7 @@ CREATE PROCEDURE CREATE_TABLES()
/*
* Procedures called before migration happens
- */
+ */
CREATE PROCEDURE PRE_MIGRATE()
BEGIN
call CREATE_TABLES();
@@ -97,25 +100,25 @@ CREATE PROCEDURE PRE_MIGRATE()
* Add entries into CDS.
* Populate the CD_ID field in SDS for tables
* Add entires to COLUMNS_V2 based on this table's sd's columns
- */
+ */
CREATE PROCEDURE MIGRATE_TABLES()
BEGIN
/* In the migration, there is a 1:1 mapping between CD_ID and SD_ID
- * for tables. For speed, just let CD_ID = SD_ID for tables
+ * for tables. For speed, just let CD_ID = SD_ID for tables
*/
INSERT INTO CDS (CD_ID)
SELECT SD_ID FROM TABLE_SDS;
SELECT 'Inserted into CDS';
-
+
UPDATE SDS
SET CD_ID = SD_ID
- WHERE SD_ID in
+ WHERE SD_ID in
(select SD_ID from TABLE_SDS);
SELECT 'Updated CD_ID in SDS';
INSERT INTO COLUMNS_V2
(CD_ID, COMMENT, COLUMN_NAME, TYPE_NAME, INTEGER_IDX)
- SELECT
+ SELECT
c.SD_ID, c.COMMENT, c.COLUMN_NAME, c.TYPE_NAME, c.INTEGER_IDX
FROM
COLUMNS c
@@ -138,9 +141,42 @@ CREATE PROCEDURE MIGRATE_PARTITIONS()
JOIN TBLS t on t.TBL_ID = p.TBL_ID
SET sd.CD_ID = t.SD_ID
where p.SD_ID is not null
- ;
SELECT 'Updated CD_IDs in SDS for partitions';
-
+ END $$
+
+/*
+ * Migrate the IDXS table
+ * Add entries into CDS.
+ * Populate the CD_ID field in SDS for tables
+ */
+CREATE PROCEDURE MIGRATE_IDXS()
+ BEGIN
+ /* In the migration, there is a 1:1 mapping between CD_ID and SD_ID
+ * for indexes. For speed, just let CD_ID = SD_ID for indexes
+ */
+ INSERT INTO CDS (CD_ID)
+ SELECT SD_ID FROM IDXS
+ WHERE SD_ID IS NOT NULL;
+ SELECT 'Inserted into CDS for IDXS';
+
+ UPDATE SDS
+ SET CD_ID = SD_ID
+ WHERE SD_ID in
+ (SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL);
+ SELECT 'Updated CD_ID in SDS for IDXS';
+
+ INSERT INTO COLUMNS_V2
+ (CD_ID, COMMENT, COLUMN_NAME, TYPE_NAME, INTEGER_IDX)
+ SELECT
+ c.SD_ID, c.COMMENT, c.COLUMN_NAME, c.TYPE_NAME, c.INTEGER_IDX
+ FROM
+ COLUMNS c
+ JOIN
+ IDXS i
+ ON
+ i.SD_ID = c.SD_ID
+ ;
+ SELECT 'Inserted table columns into COLUMNS_V2';
END $$
/*
@@ -153,7 +189,7 @@ CREATE PROCEDURE CREATE_TABLE_SDS()
PRIMARY KEY (`SD_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
- INSERT INTO TABLE_SDS
+ INSERT INTO TABLE_SDS
(SD_ID)
SELECT
t.SD_ID
@@ -161,34 +197,29 @@ CREATE PROCEDURE CREATE_TABLE_SDS()
TBLS t
WHERE
t.SD_ID IS NOT NULL
- ORDER BY
+ ORDER BY
t.SD_ID
;
END $$
/*
- * A currently unused function to igrate the COLUMNS_V2 table
- * to have the name COLUMNS
+ * Rename the old columns table, so old clients do not
+ * read from the unused COLUMNS table.
+ * After you are sure migration is successful, you can drop
+ * the table COLUMNS_OLD
*/
-CREATE PROCEDURE RENAME_TMP_COLUMNS()
+CREATE PROCEDURE RENAME_OLD_COLUMNS()
BEGIN
- /*DROP TABLE `COLUMNS`;*/
- RENAME TABLE `COLUMNS_V2` TO `COLUMNS`;
- SELECT 'Renamed COLUMNS_V2 to COLUMNS';
- ALTER TABLE `COLUMNS`
- DROP FOREIGN KEY `COLUMNS_V2_FK1`;
- SELECT 'Dropped FK on Columns';
- DROP INDEX `COLUMNS_V2_N49` ON COLUMNS;
- SELECT 'Dropped Index on Columns';
- CREATE INDEX `COLUMNS_N49` ON COLUMNS
- (CD_ID)
- ;
- SELECT 'Added index on Columns';
- ALTER TABLE COLUMNS_
- ADD CONSTRAINT `COLUMNS_FK1`
- FOREIGN KEY (`CD_ID`) REFERENCES `CDS` (`CD_ID`)
- ;
- SELECT 'Added FK on Columns';
+ RENAME TABLE `COLUMNS` TO `COLUMNS_OLD`;
+ END $$
+
+/*
+ * calls procedures that happen after migration
+ */
+CREATE PROCEDURE POST_MIGRATE()
+ BEGIN
+ call RENAME_OLD_COLUMNS();
+ SELECT 'Renamed columns to old columns';
END $$
/*
@@ -202,9 +233,13 @@ CREATE PROCEDURE MIGRATE()
SELECT 'Completed migrating tables';
call MIGRATE_PARTITIONS();
SELECT 'Completed migrating partitions';
- /* Migrate indexes? */
+ call MIGRATE_IDXS();
+ SELECT 'Completed migrating idxs';
+ call POST_MIGRATE();
+ SELECT 'Completed post migrate';
END $$
+
DELIMITER ;
CALL MIGRATE();
