hive-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From nzh...@apache.org
Subject svn commit: r1197644 - in /hive/trunk/metastore/scripts/upgrade: derby/008-HIVE-2246.derby.sql derby/008-REVERT-HIVE-2246.derby.sql mysql/008-HIVE-2246.mysql.sql
Date Fri, 04 Nov 2011 16:39:39 GMT
Author: nzhang
Date: Fri Nov  4 16:39:38 2011
New Revision: 1197644

URL: http://svn.apache.org/viewvc?rev=1197644&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/trunk/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
    hive/trunk/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
    hive/trunk/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql

Modified: hive/trunk/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql?rev=1197644&r1=1197643&r2=1197644&view=diff
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql (original)
+++ hive/trunk/metastore/scripts/upgrade/derby/008-HIVE-2246.derby.sql Fri Nov  4 16:39:38
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/trunk/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql?rev=1197644&r1=1197643&r2=1197644&view=diff
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql (original)
+++ hive/trunk/metastore/scripts/upgrade/derby/008-REVERT-HIVE-2246.derby.sql Fri Nov  4 16:39:38
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/trunk/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql?rev=1197644&r1=1197643&r2=1197644&view=diff
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql (original)
+++ hive/trunk/metastore/scripts/upgrade/mysql/008-HIVE-2246.mysql.sql Fri Nov  4 16:39:38
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();



Mime
View raw message