Figured out the issue. We are running postgres on 8.4. The create table if not exists "key constraints" was failing: reason: create table if not exists is not supported in postgresql 8.4 need to change the 033-HIVE-13076.postgres.sql should do the trick. waiting for the confirmation. ... View more

Just re did the whole operation. the schema upgrade here is a three step process. 1.2.0 to 1.2.1000 followed by 1.2.1000 to 2.0.0 and then from 2.0.0 to 2.1.0. The upgrade is failing at the third upgrade statement i.e. from 2.0.0 to 2.1.0. Upgrade script upgrade-2.0.0-to-2.1.0.postgres.sql Error: ERROR: syntax error at or near "NOT" Position: 17 (state=42601,code=0) Is this expected ? ... View more

In addition. I have tried restoring my backup and that worked out. I was able to run the schematool -upgrade and that went on successfully. Before: # ./schematool -info -dbType postgres -userName hive -passWord hive -verbose Metastore connection URL: jdbc:postgresql://db-host:5432/hive Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Hive distribution version: 1.2.1000 Metastore schema version: 1.2.0 org.apache.hadoop.hive.metastore.HiveMetaException: Metastore schema version is* org.apache.hadoop.hive.metastore.HiveMetaException: Metastore schema version is* at org.apache.hive.beeline.HiveSchemaTool.assertCompatibleVersion(HiveSc at org.apache.hive.beeline.HiveSchemaTool.showInfo(HiveSchemaTool.java:1 at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:500) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148) *** schemaTool failed *** Upgrade: # ./schematool -upgradeSchema -dbType postgres -userName hive -passWord hive Metastore connection URL: jdbc:postgresql://db-host:5432/hive Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Starting upgrade metastore schema from version 1.2.0 to 1.2.1000 Upgrade script upgrade-1.2.0-to-1.2.1000.postgres.sql Completed upgrade-1.2.0-to-1.2.1000.postgres.sql schemaTool completed Post Upgrade Info: # ./schematool -info -dbType postgres -userName hive -passWord hive -verbose Metastore connection URL: jdbc:postgresql://db-host:5432/hive Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Hive distribution version: 1.2.1000 Metastore schema version: 1.2.1000 schemaTool completed # But when I try to start the metastore it is trying to initialize the schema on 2.1.0 PFB the error when trying to start hive metastore. Restarting Hive-metastore: resource_management.core.exceptions.Fail: Execution of 'export HIVE_CONF_DIR=/usr/hdp/current/hive-metastore/conf/conf.server ; /usr/hdp/current/hive-server2-hive2/bin/schematool -initSchema -dbType postgres -userName hive -passWord [PROTECTED] -verbose' returned 1. SLF4J: Class path contains multiple SLF4J bindings. SLF4J: Found binding in [jar:file:/usr/hdp/2.5.3.0-37/hive2/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: Found binding in [jar:file:/usr/hdp/2.5.3.0-37/hadoop/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation. SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory] Metastore connection URL: jdbc:postgresql://db-host:5432/hive Metastore Connection Driver : org.postgresql.Driver Metastore connection User: hive Starting metastore schema initialization to 2.1.0 Initialization script hive-schema-2.1.0.postgres.sql Connecting to jdbc:postgresql://db-host:5432/hive Connected to: PostgreSQL (version 8.4.20) Driver: PostgreSQL Native Driver (version PostgreSQL 8.4 JDBC4 (build 704)) Transaction isolation: TRANSACTION_READ_COMMITTED 0: jdbc:postgresql://db-host> !autocommit on Autocommit status: true 0: jdbc:postgresql://db-host> SET statement_timeout = 0 No rows affected (0.002 seconds) 0: jdbc:postgresql://db-host> SET client_encoding = 'UTF8' No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET standard_conforming_strings = off No rows affected (0 seconds) 0: jdbc:postgresql://db-host> SET check_function_bodies = false No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET client_min_messages = warning No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET escape_string_warning = off No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET search_path = public, pg_cata log No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET default_tablespace = '' No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> SET default_with_oids = false No rows affected (0.001 seconds) 0: jdbc:postgresql://db-host> CREATE TABLE "BUCKETING_COLS" ( " SD_ID" bigint NOT NULL, "BUCKET_COL_NAME" character varying(256) DEFAULT NULL::c haracter varying, "INTEGER_IDX" bigint NOT NULL ) Error: ERROR: relation "BUCKETING_COLS" already exists (state=42P07,code=0) Closing: 0: jdbc:postgresql://db-host:5432/hive org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! Underlying cause: java.io.IOException : Schema script failed, errorcode 2 org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !! at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:304) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:277) at org.apache.hive.beeline.HiveSchemaTool.main(HiveSchemaTool.java:526) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.util.RunJar.run(RunJar.java:233) at org.apache.hadoop.util.RunJar.main(RunJar.java:148) Caused by: java.io.IOException: Schema script failed, errorcode 2 at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:410) at org.apache.hive.beeline.HiveSchemaTool.runBeeLine(HiveSchemaTool.java:367) at org.apache.hive.beeline.HiveSchemaTool.doInit(HiveSchemaTool.java:300) ... 8 more *** schemaTool failed *** ... View more

Hello @apappu. The Hive metastore is failing to start. because it tried running a intialization on the metastore and errored out saying bucketing_cols already exists. It is trying to initial with version hive-schema-2.1.0 ... View more

Hello Terry, Was the oozie check failing because the job went into prep state ? Did this issue resolve? Were the two boxes of same hardware ? ... View more