Hi,
My version is 0.14 on Oracle metastore and there is no drop command there. Table seems to keep partition column stats. So it is just stats table CREATE TABLE PART_COL_STATS ( CS_ID NUMBER NOT NULL, DB_NAME VARCHAR2(128) NOT NULL, TABLE_NAME VARCHAR2(128) NOT NULL, PARTITION_NAME VARCHAR2(767) NOT NULL, COLUMN_NAME VARCHAR2(128) NOT NULL, COLUMN_TYPE VARCHAR2(128) NOT NULL, PART_ID NUMBER NOT NULL, LONG_LOW_VALUE NUMBER, LONG_HIGH_VALUE NUMBER, DOUBLE_LOW_VALUE NUMBER, DOUBLE_HIGH_VALUE NUMBER, BIG_DECIMAL_LOW_VALUE VARCHAR2(4000), BIG_DECIMAL_HIGH_VALUE VARCHAR2(4000), NUM_NULLS NUMBER NOT NULL, NUM_DISTINCTS NUMBER, AVG_COL_LEN NUMBER, MAX_COL_LEN NUMBER, NUM_TRUES NUMBER, NUM_FALSES NUMBER, LAST_ANALYZED NUMBER NOT NULL ); ALTER TABLE PART_COL_STATS ADD CONSTRAINT PART_COL_STATS_PKEY PRIMARY KEY (CS_ID); ALTER TABLE PART_COL_STATS ADD CONSTRAINT PART_COL_STATS_FK FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) INITIALLY DEFERRED; CREATE INDEX PART_COL_STATS_N49 ON PART_COL_STATS (PART_ID); CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME); Can you compare table defs with your previous version and 0.14 and see anything has changed in DDL? Do you have any records in that table? Mine is empty HTH Mich Talebzadeh http://talebzadehmich.wordpress.com Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15", ISBN 978-0-9563693-0-7. co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 978-0-9759693-0-4 Publications due shortly: Creating in-memory Data Grid for Trading Systems with Oracle TimesTen and Coherence Cache Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one out shortly NOTE: The information in this email is proprietary and confidential. This message is for the designated recipient only, if you are not the intended recipient, you should destroy it immediately. Any information in this message shall not be understood as given or endorsed by Peridale Ltd, its subsidiaries or their employees, unless expressly so stated. It is the responsibility of the recipient to ensure that this email is virus free, therefore neither Peridale Ltd, its subsidiaries nor their employees accept any responsibility. From: jun aoki [mailto:ja...@apache.org] Sent: 29 April 2015 01:33 To: user@hive.apache.org Subject: hive metastore's schematool -upgradeSchema on postgres throws an error on CREATE TABLE PART_COL_STATS 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
