Major Description
$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.