Hi hive community, I am new to Hive and it may be a stupid question but let me know if you know the answer.
I am attempting to upgrade hive metastore schema from 0.12 to 0.14. The whole log is here [2] At the end, VERSION table shows SCHEMA_VERSION 0.14.0. [1] which was 0.12.0 and it seems successful. However, if you take a closer look at the log, you find an error "Error: ERROR: relation "PART_COL_STATS" already exists" The error seems occured from pre-0-upgrade-0.13.0-to-0.14.0.postgres.sql, which is a sole CREATE TABLE command (e.g. https://github.com/apache/hive/blob/branch-0.14/metastore/scripts/upgrade/postgres/pre-0-upgrade-0.13.0-to-0.14.0.postgres.sql), and it is maybe OK to fail that way since my current postgres already has that table. I never tried but mysql's upgrade scripts has "IF NOT EXISTS" on CREATE TABLE, I think the error won't show up . (e.g. https://github.com/apache/hive/blob/master/metastore/scripts/upgrade/mysql/019-HIVE-7784.mysql.sql ) Questions are (a) is this considered as successful upgrade since pre-0-upgrade...sql is a sole create table? (b) Is this a legitimate bug specific to postgres in the hive product (specifically hive metastore schematool and missing "IF NOT EXIST"?) [1] [root@rhel65-4 database_backup]# ; psql -p10432 -d metastore hive -c'\d "DBS"' ; psql -p10432 -d metastore hive -c'SELECT * FROM "VERSION"' ;psql -p10432 -d metastore hive -c'\d "PART_COL_STATS"' psql -p10432 -d metastore hive -c'SELECT * FROM "VERSION"' VER_ID | SCHEMA_VERSION | VERSION_COMMENT --------+----------------+----------------------------- 1 | 0.14.0 | Hive release version 0.14.0 (1 row) [2] The whole log on upgradeScema [root@rhel65-4 hive]# su -s /bin/bash - hdfs -c 'HIVE_CONF_DIR=/etc/hive/conf.server /usr/phd/current/hive-metastore/bin/schematool -upgradeSchema -dbType postgres -verbose' 15/04/28 16:28:09 WARN conf.HiveConf: HiveConf of name hive.optimize.mapjoin.mapreduce does not exist 15/04/28 16:28:09 WARN conf.HiveConf: HiveConf of name hive.heapsize does not exist 15/04/28 16:28:09 WARN conf.HiveConf: HiveConf of name hive.server2.enable.impersonation does not exist 15/04/28 16:28:09 WARN conf.HiveConf: HiveConf of name hive.auto.convert.sortmerge.join.noconditionaltask does not exist Metastore connection URL: jdbc:postgresql://rhel65-4.localdomain:10432/metastore Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Starting upgrade metastore schema from version 0.12.0 to 0.14.0 Upgrade script upgrade-0.12.0-to-0.13.0.postgres.sql Looking for pre-0-upgrade-0.12.0-to-0.13.0.postgres.sql in /usr/phd/3.0.0.0-247/hive/scripts/metastore/upgrade/postgres Connecting to jdbc:postgresql://rhel65-4.localdomain:10432/metastore Connected to: PostgreSQL (version 8.4.18) Driver: PostgreSQL Native Driver (version PostgreSQL 8.4 JDBC4 (build 701)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:postgresql://rhel65-4.localdomain:104> !autocommit on Autocommit status: true 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE LANGUAGE plpgsql Error: ERROR: language "plpgsql" already exists (state=42710,code=0) Closing: 0: jdbc:postgresql://rhel65-4.localdomain:10432/metastore Warning in pre-upgrade script pre-0-upgrade-0.12.0-to-0.13.0.postgres.sql: Schema script failed, errorcode 2 java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:380) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:353) at org.apache.hive.beeline.HiveSchemaTool.runPreUpgrade(HiveSchemaTool.java:323) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:243) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:217) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:493) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Looking for pre-1-upgrade-0.12.0-to-0.13.0.postgres.sql in /usr/phd/3.0.0.0-247/hive/scripts/metastore/upgrade/postgres Connecting to jdbc:postgresql://rhel65-4.localdomain:10432/metastore Connected to: PostgreSQL (version 8.4.18) Driver: PostgreSQL Native Driver (version PostgreSQL 8.4 JDBC4 (build 701)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:postgresql://rhel65-4.localdomain:104> !autocommit on Autocommit status: true 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT 'Upgrading MetaStore schema from 0.12.0 to 0.13.0' +---------------------------------------------------+--+ | ?column? | +---------------------------------------------------+--+ | Upgrading MetaStore schema from 0.12.0 to 0.13.0 | +---------------------------------------------------+--+ 1 row selected (0.02 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT '< HIVE-6386 Database should have an owner >' +----------------------------------------------+--+ | ?column? | +----------------------------------------------+--+ | < HIVE-6386 Database should have an owner > | +----------------------------------------------+--+ 1 row selected (0.007 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> ALTER TABLE "DBS" ADD COLUMN "OWNER_NAME" character varying(128) No rows affected (0.007 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> ALTER TABLE "DBS" ADD COLUMN "OWNER_TYPE" character varying(10) No rows affected (0.006 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT '< HIVE-6458 Add schema upgrade scripts for metastore changes related to permanent functions >' +------------------------------------------------------------------------------------------------+--+ | ?column? | +------------------------------------------------------------------------------------------------+--+ | < HIVE-6458 Add schema upgrade scripts for metastore changes related to permanent functions > | +------------------------------------------------------------------------------------------------+--+ 1 row selected (0.006 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE "FUNCS" ( "FUNC_ID" BIGINT NOT NULL, "CLASS_NAME" VARCHAR(4000), "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "FUNC_NAME" VARCHAR(128), "FUNC_TYPE" INTEGER NOT NULL, "OWNER_NAME" VARCHAR(128), "OWNER_TYPE" VARCHAR(10), PRIMARY KEY ("FUNC_ID") ) No rows affected (0.028 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE UNIQUE INDEX "UNIQUEFUNCTION" ON "FUNCS" ("FUNC_NAME", "DB_ID") No rows affected (0.008 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE INDEX "FUNCS_N49" ON "FUNCS" ("DB_ID") No rows affected (0.004 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> ALTER TABLE ONLY "FUNCS" ADD CONSTRAINT "FUNCS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID") DEFERRABLE No rows affected (0.004 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE "FUNC_RU" ( "FUNC_ID" BIGINT NOT NULL, "RESOURCE_TYPE" INTEGER NOT NULL, "RESOURCE_URI" VARCHAR(4000), "INTEGER_IDX" INTEGER NOT NULL, PRIMARY KEY ("FUNC_ID", "INTEGER_IDX") ) No rows affected (0.007 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE INDEX "FUNC_RU_N49" ON "FUNC_RU" ("FUNC_ID") No rows affected (0.005 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> ALTER TABLE ONLY "FUNC_RU" ADD CONSTRAINT "FUNC_RU_FK1" FOREIGN KEY ("FUNC_ID") REFERENCES "FUNCS" ("FUNC_ID") DEFERRABLE No rows affected (0.002 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT '< HIVE-6757 Remove deprecated parquet classes from outside of org.apache package >' +-------------------------------------------------------------------------------------+--+ | ?column? | +-------------------------------------------------------------------------------------+--+ | < HIVE-6757 Remove deprecated parquet classes from outside of org.apache package > | +-------------------------------------------------------------------------------------+--+ 1 row selected (0.001 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> UPDATE "SDS" SET "INPUT_FORMAT" = 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' WHERE "INPUT_FORMAT"= 'parquet.hive.DeprecatedParquetInputFormat' or "INPUT_FORMAT" = 'parquet.hive.MapredParquetInputFormat' No rows affected (0.003 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> UPDATE "SDS" SET "OUTPUT_FORMAT" = 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat' WHERE "OUTPUT_FORMAT" = 'parquet.hive.DeprecatedParquetOutputFormat' or "OUTPUT_FORMAT" = 'parquet.hive.MapredParquetOutputFormat' No rows affected (0.001 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> UPDATE "SERDES" SET "SLIB" ='org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' WHERE "SLIB" = 'parquet.hive.serde.ParquetHiveSerDe' No rows affected (0.001 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE TXNS ( TXN_ID bigint PRIMARY KEY, TXN_STATE char(1) NOT NULL, TXN_STARTED bigint NOT NULL, TXN_LAST_HEARTBEAT bigint NOT NULL, TXN_USER varchar(128) NOT NULL, TXN_HOST varchar(128) NOT NULL ) No rows affected (0.009 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE TXN_COMPONENTS ( TC_TXNID bigint REFERENCES TXNS (TXN_ID), TC_DATABASE varchar(128) NOT NULL, TC_TABLE varchar(128), TC_PARTITION varchar(767) DEFAULT NULL ) No rows affected (0.012 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE COMPLETED_TXN_COMPONENTS ( CTC_TXNID bigint, CTC_DATABASE varchar(128) NOT NULL, CTC_TABLE varchar(128), CTC_PARTITION varchar(767) ) No rows affected (0.009 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE NEXT_TXN_ID ( NTXN_NEXT bigint NOT NULL ) No rows affected (0.003 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> INSERT INTO NEXT_TXN_ID VALUES(1) 1 row affected (0.004 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE HIVE_LOCKS ( HL_LOCK_EXT_ID bigint NOT NULL, HL_LOCK_INT_ID bigint NOT NULL, HL_TXNID bigint, HL_DB varchar(128) NOT NULL, HL_TABLE varchar(128), HL_PARTITION varchar(767) DEFAULT NULL, HL_LOCK_STATE char(1) NOT NULL, HL_LOCK_TYPE char(1) NOT NULL, HL_LAST_HEARTBEAT bigint NOT NULL, HL_ACQUIRED_AT bigint, HL_USER varchar(128) NOT NULL, HL_HOST varchar(128) NOT NULL, PRIMARY KEY(HL_LOCK_EXT_ID, HL_LOCK_INT_ID) ) No rows affected (0.017 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE INDEX HL_TXNID_INDEX ON HIVE_LOCKS USING hash (HL_TXNID) No rows affected (0.003 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE NEXT_LOCK_ID ( NL_NEXT bigint NOT NULL ) No rows affected (0.002 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> INSERT INTO NEXT_LOCK_ID VALUES(1) 1 row affected (0.002 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE COMPACTION_QUEUE ( CQ_ID bigint PRIMARY KEY, CQ_DATABASE varchar(128) NOT NULL, CQ_TABLE varchar(128) NOT NULL, CQ_PARTITION varchar(767), CQ_STATE char(1) NOT NULL, CQ_TYPE char(1) NOT NULL, CQ_WORKER_ID varchar(128), CQ_START bigint, CQ_RUN_AS varchar(128) ) No rows affected (0.023 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE NEXT_COMPACTION_QUEUE_ID ( NCQ_NEXT bigint NOT NULL ) No rows affected (0.005 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1) 1 row affected (0.003 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> UPDATE "VERSION" SET "SCHEMA_VERSION"='0.13.0', "VERSION_COMMENT"='Hive release version 0.13.0' where "VER_ID"=1 1 row affected (0.006 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT 'Finished upgrading MetaStore schema from 0.12.0 to 0.13.0' +------------------------------------------------------------+--+ | ?column? | +------------------------------------------------------------+--+ | Finished upgrading MetaStore schema from 0.12.0 to 0.13.0 | +------------------------------------------------------------+--+ 1 row selected (0.001 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> !closeall Closing: 0: jdbc:postgresql://rhel65-4.localdomain:10432/metastore beeline> Completed upgrade-0.12.0-to-0.13.0.postgres.sql Upgrade script upgrade-0.13.0-to-0.14.0.postgres.sql Looking for pre-0-upgrade-0.13.0-to-0.14.0.postgres.sql in /usr/phd/3.0.0.0-247/hive/scripts/metastore/upgrade/postgres Connecting to jdbc:postgresql://rhel65-4.localdomain:10432/metastore Connected to: PostgreSQL (version 8.4.18) Driver: PostgreSQL Native Driver (version PostgreSQL 8.4 JDBC4 (build 701)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:postgresql://rhel65-4.localdomain:104> !autocommit on Autocommit status: true 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE TABLE "PART_COL_STATS" ( "CS_ID" bigint NOT NULL, "DB_NAME" character varying(128) DEFAULT NULL::character varying, "TABLE_NAME" character varying(128) DEFAULT NULL::character varying, "PARTITION_NAME" character varying(767) DEFAULT NULL::character varying, "COLUMN_NAME" character varying(128) DEFAULT NULL::character varying, "COLUMN_TYPE" character varying(128) DEFAULT NULL::character varying, "PART_ID" bigint NOT NULL, "LONG_LOW_VALUE" bigint, "LONG_HIGH_VALUE" bigint, "DOUBLE_LOW_VALUE" double precision, "DOUBLE_HIGH_VALUE" double precision, "BIG_DECIMAL_LOW_VALUE" character varying(4000) DEFAULT NULL::character varying, "BIG_DECIMAL_HIGH_VALUE" character varying(4000) DEFAULT NULL::character varying, "NUM_NULLS" bigint NOT NULL, "NUM_DISTINCTS" bigint, "AVG_COL_LEN" double precision, "MAX_COL_LEN" bigint, "NUM_TRUES" bigint, "NUM_FALSES" bigint, "LAST_ANALYZED" bigint NOT NULL ) Error: ERROR: relation "PART_COL_STATS" already exists (state=42P07,code=0) Closing: 0: jdbc:postgresql://rhel65-4.localdomain:10432/metastore Warning in pre-upgrade script pre-0-upgrade-0.13.0-to-0.14.0.postgres.sql: Schema script failed, errorcode 2 java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:380) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:353) at org.apache.hive.beeline.HiveSchemaTool.runPreUpgrade(HiveSchemaTool.java:323) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:243) at org.apache.hive.beeline.HiveSchemaTool.doUpgrade(HiveSchemaTool.java:217) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:493) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.apache.hadoop.util.RunJar.run(RunJar.java:221) at org.apache.hadoop.util.RunJar.main(RunJar.java:136) Looking for pre-1-upgrade-0.13.0-to-0.14.0.postgres.sql in /usr/phd/3.0.0.0-247/hive/scripts/metastore/upgrade/postgres Connecting to jdbc:postgresql://rhel65-4.localdomain:10432/metastore Connected to: PostgreSQL (version 8.4.18) Driver: PostgreSQL Native Driver (version PostgreSQL 8.4 JDBC4 (build 701)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:postgresql://rhel65-4.localdomain:104> !autocommit on Autocommit status: true 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT 'Upgrading MetaStore schema from 0.13.0 to 0.14.0' +---------------------------------------------------+--+ | ?column? | +---------------------------------------------------+--+ | Upgrading MetaStore schema from 0.13.0 to 0.14.0 | +---------------------------------------------------+--+ 1 row selected (0.001 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> CREATE INDEX "PCS_STATS_IDX" ON "PART_COL_STATS" USING btree ("DB_NAME","TABLE_NAME","COLUMN_NAME","PARTITION_NAME") No rows affected (0.009 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> UPDATE "VERSION" SET "SCHEMA_VERSION"='0.14.0', "VERSION_COMMENT"='Hive release version 0.14.0' where "VER_ID"=1 1 row affected (0.003 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> SELECT 'Finished upgrading MetaStore schema from 0.13.0 to 0.14.0' +------------------------------------------------------------+--+ | ?column? | +------------------------------------------------------------+--+ | Finished upgrading MetaStore schema from 0.13.0 to 0.14.0 | +------------------------------------------------------------+--+ 1 row selected (0.007 seconds) 0: jdbc:postgresql://rhel65-4.localdomain:104> !closeall Closing: 0: jdbc:postgresql://rhel65-4.localdomain:10432/metastore beeline> Completed upgrade-0.13.0-to-0.14.0.postgres.sql schemaTool completed -- -jun
