Unable to create table in hive : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.

[#1]

hive> create table t ( i int );
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Exception thrown when executing query)

Unable to create table as I am facing this error .

I am using cloudera quick start VM.

Thanks in advance.

[#2]

Hello Abhinav

The error message seems generic and there could be multiple reason for this error example service was bad , HMS not able to connect to backend db .

1. Please enabled debug level logging if using hive CLI , this will show us the error which is cuaing this failure:

Below are the steps to enable debug logging for session level

root@hostname ~]# export HADOOP_ROOT_LOGGER=DEBUG,console

[root@hostname ~]# hive --hiveconf hive.root.logger=DEBUG,console  -e "create table test(i int)"

2.. Was the hive service up and running when you observed this issue ?

To start HiveServer1:

$ sudo service hiveserver start

To start the Hive console:

$ hive
hive>

To confirm that Hive is working, issue the show tables; command to list the Hive tables; be sure to use a semi-colon after the command:

hive> show tables;
OK
Time taken: 10.345 seconds

3.. Did you try using Beeline as well ?  if not could you please try connecting via beeline and let us know if that works

Because of concurrency and security issues, HiveServer1 is deprecated in CDH 5 and will be removed in a future release. Cloudera recommends you migrate to Beeline and HiveServer2 as soon as possible. The Hive Console is not needed if you are using Beeline with HiveServer2.

https://blog.cloudera.com/blog/2014/02/migrating-from-hive-cli-to-beeline-a-primer/

Thanks

Manpreet Singh

[#3]

As Manpreet mentioned, it is better to run in Beeline and check the error message in HiveServer2 log, which can tell more about the story.

Eric

[#4]

Creating table throws error but database creation works fine.

(base) [cloudera@quickstart ~]$ beeline -u  jdbc:hive2://quickstart:10000/default --hiveconf hive.root.logger=DEBUG,console  -e "create table test(i int)"

20/03/08 15:55:10 DEBUG util.VersionInfo: version: 2.6.0-cdh5.16.2

OpenJDK 64-Bit Server VM warning: If the number of processors is expected to increase from one, then you should configure the number of parallel GC threads appropriately using -XX:ParallelGCThreads=N

scan complete in 8ms

Connecting to jdbc:hive2://quickstart:10000/default

Connected to: Apache Hive (version 1.1.0-cdh5.16.2)

(base) [cloudera@quickstart ~]$ beeline -u  jdbc:hive2://quickstart:10000/default --hiveconf hive.root.logger=DEBUG,console  -e "create database test"

20/03/08 15:57:51 DEBUG util.VersionInfo: version: 2.6.0-cdh5.16.2

OpenJDK 64-Bit Server VM warning: If the number of processors is expected to increase from one, then you should configure the number of parallel GC threads appropriately using -XX:ParallelGCThreads=N

scan complete in 5ms

Connecting to jdbc:hive2://quickstart:10000/default

Connected to: Apache Hive (version 1.1.0-cdh5.16.2)

Driver: Hive JDBC (version 1.1.0-cdh5.16.2)

Transaction isolation: TRANSACTION_REPEATABLE_READ

INFO  : Compiling command(queryId=hive_20200308155858_019a49da-ea7d-441c-863d-f37ff4c86d32): create database test

INFO  : Semantic Analysis Completed

INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)

INFO  : Completed compiling command(queryId=hive_20200308155858_019a49da-ea7d-441c-863d-f37ff4c86d32); Time taken: 0.096 seconds

INFO  : Concurrency mode is disabled, not creating a lock manager

INFO  : Executing command(queryId=hive_20200308155858_019a49da-ea7d-441c-863d-f37ff4c86d32): create database test

INFO  : Starting task [Stage-0:DDL] in serial mode

INFO  : Completed executing command(queryId=hive_20200308155858_019a49da-ea7d-441c-863d-f37ff4c86d32); Time taken: 3.029 seconds

INFO  : OK

No rows affected (3.281 seconds)

Beeline version 1.1.0-cdh5.16.2 by Apache Hive

Driver: Hive JDBC (version 1.1.0-cdh5.16.2)

Transaction isolation: TRANSACTION_REPEATABLE_READ

INFO  : Compiling command(queryId=hive_20200308155555_9a444e04-9523-4443-b312-64a94e5a5138): create table test(i int)

INFO  : Semantic Analysis Completed

INFO  : Returning Hive schema: Schema(fieldSchemas:null, properties:null)

INFO  : Completed compiling command(queryId=hive_20200308155555_9a444e04-9523-4443-b312-64a94e5a5138); Time taken: 0.268 seconds

INFO  : Concurrency mode is disabled, not creating a lock manager

INFO  : Executing command(queryId=hive_20200308155555_9a444e04-9523-4443-b312-64a94e5a5138): create table test(i int)

INFO  : Starting task [Stage-0:DDL] in serial mode

ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Exception thrown when executing query)

INFO  : Completed executing command(queryId=hive_20200308155555_9a444e04-9523-4443-b312-64a94e5a5138); Time taken: 20.625 seconds

Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Exception thrown when executing query) (state=08S01,code=1)

[#5]

Please use the query ID hive_20200308155555_9a444e04-9523-4443-b312-64a94e5a5138 and search in HS2 log to find out the exact reason for the failure. Looks like beeline did not capture the full error from server side.

Cheers
Eric

[#6]

facing issues while creating database

hive>create database anuj;

21/02/01 14:23:23 DEBUG metastore.warehouse: Creating directory if it doesn't exist: file:/user/hive/warehouse/anuj.db
21/02/01 14:23:23 ERROR metastore.RetryingHMSHandler: MetaException(message:Unable to create database path file:/user/hive/warehouse/anuj.db, failed to create database anuj)

[#7]

Hello @anujseeker 

The best option to submit queries to hive is to use HIVESERVER2 and not hive cli. Hive cli is deprecated.

Coming to your main query as to you are unable to create database. You need check the hive cli logs present here (/tmp/<userid>/hive) to gather more info as why the path was not created and the Hive Metastore logs present here (/var/log/hive/). This is because hive cli (client) here talks directly to HMS and bypasses hiveserver2.

If you could share the details from hive cli log and HMS logs, it would be easier to further guide you through the next steps.

Regards,

Tushar

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Cli

[#8]

Thanks for replying,

Here are the logs of  /tmp/i538738/hive.log

2021-02-02 03:46:31,405 WARN common.LogUtils (LogUtils.java:logConfigLocation(142)) - hive-site.xml not found on CLASSPATH
2021-02-02 03:46:32,110 WARN bonecp.BoneCPConfig (BoneCPConfig.java:sanitize(1537)) - Max Connections < 1. Setting to 20
2021-02-02 03:46:33,387 WARN bonecp.BoneCPConfig (BoneCPConfig.java:sanitize(1537)) - Max Connections < 1. Setting to 20
2021-02-02 03:46:33,755 ERROR thrift.ThriftCLIService (ThriftBinaryCLIService.java:run(81)) - Error:
org.apache.thrift.transport.TTransportException: Could not create ServerSocket on address 0.0.0.0/0.0.0.0:10000.
at org.apache.thrift.transport.TServerSocket.<init>(TServerSocket.java:93)
at org.apache.thrift.transport.TServerSocket.<init>(TServerSocket.java:79)
at org.apache.hive.service.cli.thrift.ThriftBinaryCLIService.run(ThriftBinaryCLIService.java:67)
at java.lang.Thread.run(Thread.java:748)
2021-02-02 03:46:54,742 WARN common.LogUtils (LogUtils.java:logConfigLocation(142)) - hive-site.xml not found on CLASSPATH
2021-02-02 03:47:15,088 ERROR processors.DfsProcessor (SessionState.java:printError(419)) - Command failed with exit code = 1
2021-02-02 03:49:11,355 WARN bonecp.BoneCPConfig (BoneCPConfig.java:sanitize(1537)) - Max Connections < 1. Setting to 20
2021-02-02 03:49:12,644 WARN bonecp.BoneCPConfig (BoneCPConfig.java:sanitize(1537)) - Max Connections < 1. Setting to 20
2021-02-02 03:49:12,924 ERROR metastore.RetryingHMSHandler (RetryingHMSHandler.java:invoke(141)) - MetaException(message:Unable to create database path file:/user/hive/warehouse/anuj.db, failed to create database anuj)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database_core(HiveMetaStore.java:566)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:602)
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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:103)
at com.sun.proxy.$Proxy10.create_database(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createDatabase(HiveMetaStoreClient.java:459)
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.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:89)
at com.sun.proxy.$Proxy11.createDatabase(Unknown Source)
at org.apache.hadoop.hive.ql.metadata.Hive.createDatabase(Hive.java:225)
at org.apache.hadoop.hive.ql.exec.DDLTask.createDatabase(DDLTask.java:3442)

at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:227)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:151)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:65)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1414)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1192)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1020)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:888)
at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:259)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:216)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:413)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:781)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:675)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:614)
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:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

2021-02-02 03:49:12,932 ERROR exec.DDLTask (DDLTask.java:execute(435)) - org.apache.hadoop.hive.ql.metadata.HiveException: MetaException(message:Unable to create database path file:/user/hive/warehouse/anuj.db, failed to create database anuj)
at org.apache.hadoop.hive.ql.metadata.Hive.createDatabase(Hive.java:231)
at org.apache.hadoop.hive.ql.exec.DDLTask.createDatabase(DDLTask.java:3442)
at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:227)
at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:151)
at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:65)
at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:1414)
at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1192)
at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1020)
at org.apache.hadoop.hive.ql.Driver.run(Driver.java:888)

at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:259)
at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:216)
at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:413)
at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:781)
at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:675)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:614)
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:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: MetaException(message:Unable to create database path file:/user/hive/warehouse/anuj.db, failed to create database anuj)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database_core(HiveMetaStore.java:566)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.create_database(HiveMetaStore.java:602)
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.hive.metastore.RetryingHMSHandler.invoke(RetryingHMSHandler.java:103)
at com.sun.proxy.$Proxy10.create_database(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.createDatabase(HiveMetaStoreClient.java:459)
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.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:89)
at com.sun.proxy.$Proxy11.createDatabase(Unknown Source)
at org.apache.hadoop.hive.ql.metadata.Hive.createDatabase(Hive.java:225)
... 20 more

2021-02-02 03:49:12,932 ERROR ql.Driver (SessionState.java:printError(419)) - FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Unable to create database path file:/user/hive/warehouse/anuj.db, failed to create database anuj)

[#9]

@anujseeker 

and the HMS logs ?