[
https://issues.apache.org/jira/browse/HIVE-3535?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13469778#comment-13469778
]
Zhenxiao Luo commented on HIVE-3535:
------------------------------------
The new upgrade script gets rid of all the errors:
ij> run 'upgrade-0.7.0-to-0.8.0.derby.sql';
ij> – Upgrade MetaStore schema from 0.7.0 to 0.8.0
RUN '008-HIVE-2246.derby.sql';
ij> /*
Creates the following tables:
- CDS
- COLUMNS_V2
The new columns table is called COLUMNS_V2
because many columns are removed, and the schema is changed.
It'd take too long to migrate and keep the same table.
*/
CREATE TABLE "CDS" (
"CD_ID" bigint NOT NULL,
PRIMARY KEY ("CD_ID")
);
0 rows inserted/updated/deleted
ij> CREATE TABLE "COLUMNS_V2" (
"CD_ID" bigint NOT NULL,
"COMMENT" varchar(4000),
"COLUMN_NAME" varchar(128) NOT NULL,
"TYPE_NAME" varchar(4000),
"INTEGER_IDX" INTEGER NOT NULL,
PRIMARY KEY ("CD_ID", "COLUMN_NAME")
);
0 rows inserted/updated/deleted
ij> ALTER TABLE "COLUMNS_V2"
ADD CONSTRAINT "COLUMNS_V2_FK1"
FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
ON DELETE NO ACTION ON UPDATE NO ACTION
;
0 rows inserted/updated/deleted
ij> /* Alter the SDS table to:
- add the column CD_ID
- add a foreign key on CD_ID
- create an index on CD_ID
*/
ALTER TABLE SDS
ADD COLUMN "CD_ID" bigint
;
0 rows inserted/updated/deleted
ij> ALTER TABLE SDS
ADD CONSTRAINT "SDS_FK2"
FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
;
0 rows inserted/updated/deleted
ij> /*
Migrate the TBLS table
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
*/
/* 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
*/
INSERT INTO CDS (CD_ID)
SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL;
1 row inserted/updated/deleted
ij> UPDATE SDS
SET CD_ID = SD_ID
WHERE SD_ID in
(SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL);
1 row inserted/updated/deleted
ij> 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
TBLS t
ON
t.SD_ID = c.SD_ID
;
2 rows inserted/updated/deleted
ij> /*
Migrate the partitions.
Update the partitions' SDS to use the parent tables' CD_ID BEGIN
Derby does not allow joins in update statements,
so we have to make a temporary tableh
*/
DECLARE GLOBAL TEMPORARY TABLE "TMP_TBL" (
"SD_ID" bigint not null,
"CD_ID" bigint not null
) ON COMMIT PRESERVE ROWS NOT LOGGED;
0 rows inserted/updated/deleted
ij> INSERT INTO "SESSION"."TMP_TBL" SELECT
p.SD_ID, sds.CD_ID
FROM PARTITIONS p
JOIN TBLS t ON t.TBL_ID = p.TBL_ID
JOIN SDS sds on t.SD_ID = sds.SD_ID
WHERE p.SD_ID IS NOT NULL AND sds.CD_ID IS NOT NULL;
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.
ij> /*
Migrate IDXS
*/
INSERT INTO CDS (CD_ID)
SELECT i.SD_ID FROM IDXS i WHERE i.SD_ID IS NOT NULL;
0 rows inserted/updated/deleted
ij> 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);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.
ij> 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
;
0 rows inserted/updated/deleted
ij> /*
rename the old COLUMNS table
*/
RENAME TABLE COLUMNS TO COLUMNS_OLD;
0 rows inserted/updated/deleted
ij> RUN '009-HIVE-2215.derby.sql';
ij> – Table PARTITION_EVENTS for classes
[org.apache.hadoop.hive.metastore.model.MPartitionEvent]
CREATE TABLE PARTITION_EVENTS
(
PART_NAME_ID BIGINT NOT NULL,
DB_NAME VARCHAR(128),
EVENT_TIME BIGINT NOT NULL,
EVENT_TYPE INTEGER NOT NULL,
PARTITION_NAME VARCHAR(767),
TBL_NAME VARCHAR(128)
);
0 rows inserted/updated/deleted
ij> ALTER TABLE PARTITION_EVENTS ADD CONSTRAINT PARTITION_EVENTS_PK PRIMARY KEY
(PART_NAME_ID);
0 rows inserted/updated/deleted
ij>
> derby metastore upgrade script throw errors when updating from 0.7 to 0.8
> -------------------------------------------------------------------------
>
> Key: HIVE-3535
> URL: https://issues.apache.org/jira/browse/HIVE-3535
> Project: Hive
> Issue Type: Bug
> Components: Metastore
> Affects Versions: 0.9.0
> Reporter: Zhenxiao Luo
> Assignee: Zhenxiao Luo
> Fix For: 0.10.0
>
> Attachments: HIVE-3535.1.patch.txt
>
>
> $DERBY_HOME/bin/ij upgrade.sql
> ij version 10.4
> ij> CONNECT 'jdbc:derby:/var/lib/hive/metastore/metastore_db';
> ij> RUN
> '/usr/lib/hive/scripts/metastore/upgrade/derby/upgrade-0.7.0-to-0.8.0.derby.sql';
> ij> -- Upgrade MetaStore schema from 0.7.0 to 0.8.0
> RUN '008-HIVE-2246.derby.sql';
> ij> /*
> * Creates the following tables:
> * - CDS
> * - COLUMNS_V2
> * The new columns table is called COLUMNS_V2
> * because many columns are removed, and the schema is changed.
> * It'd take too long to migrate and keep the same table.
> */
> CREATE TABLE "CDS" (
> "CD_ID" bigint NOT NULL,
> PRIMARY KEY ("CD_ID")
> );
> 0 rows inserted/updated/deleted
> ij> CREATE TABLE "COLUMNS_V2" (
> "CD_ID" bigint NOT NULL,
> "COMMENT" varchar(4000),
> "COLUMN_NAME" varchar(128) NOT NULL,
> "TYPE_NAME" varchar(4000),
> "INTEGER_IDX" INTEGER NOT NULL,
> PRIMARY KEY ("CD_ID", "COLUMN_NAME")
> );
> 0 rows inserted/updated/deleted
> ij> ALTER TABLE "COLUMNS_V2"
> ADD CONSTRAINT "COLUMNS_V2_FK1"
> FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
> ON DELETE NO ACTION ON UPDATE NO ACTION
> ;
> 0 rows inserted/updated/deleted
> ij> /* Alter the SDS table to:
> * - add the column CD_ID
> * - add a foreign key on CD_ID
> * - create an index on CD_ID
> */
> ALTER TABLE SDS
> ADD COLUMN "CD_ID" bigint
> ;
> 0 rows inserted/updated/deleted
> ij> ALTER TABLE SDS
> ADD CONSTRAINT "SDS_FK2"
> FOREIGN KEY ("CD_ID") REFERENCES "CDS" ("CD_ID")
> ;
> 0 rows inserted/updated/deleted
> ij> /*
> * Migrate the TBLS table
> * 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
> */
> /* 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
> */
> INSERT INTO CDS (CD_ID)
> SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL ORDER BY t.SD_ID;
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 13, column 54.
> ij> UPDATE SDS
> SET CD_ID = SD_ID
> WHERE SD_ID in
> (SELECT t.SD_ID FROM TBLS t WHERE t.SD_ID IS NOT NULL ORDER BY t.SD_ID);
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
> ij> 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
> TBLS t
> ON
> t.SD_ID = c.SD_ID
> ;
> ERROR 23503: INSERT on table 'COLUMNS_V2' caused a violation of foreign key
> constraint 'COLUMNS_V2_FK1' for key (1). The statement has been rolled back.
> ij> /*
> * Migrate the partitions.
> * Update the partitions' SDS to use the parent tables' CD_ID BEGIN
> * Derby does not allow joins in update statements,
> * so we have to make a temporary tableh
> */
> DECLARE GLOBAL TEMPORARY TABLE "TMP_TBL" (
> "SD_ID" bigint not null,
> "CD_ID" bigint not null
> ) ON COMMIT PRESERVE ROWS NOT LOGGED;
> 0 rows inserted/updated/deleted
> ij> INSERT INTO "SESSION"."TMP_TBL" SELECT
> p.SD_ID, sds.CD_ID
> FROM PARTITIONS p
> JOIN TBLS t ON t.TBL_ID = p.TBL_ID
> JOIN SDS sds on t.SD_ID = sds.SD_ID
> WHERE p.SD_ID IS NOT NULL;
> ERROR 23502: Column 'CD_ID' cannot accept a NULL value.
> ij> 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);
> 0 rows inserted/updated/deleted
> WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of
> a query is an empty table.
> ij> /*
> * 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;
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 5, column 54.
> ij> 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);
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
> ij> 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
> ;
> ERROR 42X05: Table/View 'IDXS' does not exist.
> ij> /*
> * rename the old COLUMNS table
> */
> RENAME TABLE COLUMNS TO COLUMNS_OLD;
> 0 rows inserted/updated/deleted
> ij> RUN '009-HIVE-2215.derby.sql';
> ij> -- Table PARTITION_EVENTS for classes
> [org.apache.hadoop.hive.metastore.model.MPartitionEvent]
> CREATE TABLE PARTITION_EVENTS
> (
> PART_NAME_ID BIGINT NOT NULL,
> DB_NAME VARCHAR(128),
> EVENT_TIME BIGINT NOT NULL,
> EVENT_TYPE INTEGER NOT NULL,
> PARTITION_NAME VARCHAR(767),
> TBL_NAME VARCHAR(128)
> );
> 0 rows inserted/updated/deleted
> ij> ALTER TABLE PARTITION_EVENTS ADD CONSTRAINT PARTITION_EVENTS_PK PRIMARY
> KEY (PART_NAME_ID);
> 0 rows inserted/updated/deleted
> 7 Errors in the middle:
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 13, column 54.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
> ERROR 23503: INSERT on table 'COLUMNS_V2' caused a violation of foreign key
> constraint 'COLUMNS_V2_FK1' for key (1). The statement has been rolled back.
> ERROR 23502: Column 'CD_ID' cannot accept a NULL value.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 5, column 54.
> ERROR 42X01: Syntax error: Encountered "ORDER" at line 4, column 55.
> ERROR 42X05: Table/View 'IDXS' does not exist.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
