hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jun aoki <ja...@apache.org>
Subject hive metastore's schematool -upgradeSchema on postgres throws an error on CREATE TABLE PART_COL_STATS
Date Wed, 29 Apr 2015 00:32:49 GMT
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

Mime
View raw message