Reply
New Contributor
Posts: 4
Registered: ‎06-30-2016

[HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

[ Edited ]

Created a database on hive

 

hive> create database hivetest;
OK
Time taken: 0.09 seconds

 

hive> show databases;
OK
default
hivetest
test
Time taken: 0.741 seconds, Fetched: 3 row(s)

Import data from psql to hive as below

$ sqoop import --connect jdbc:postgresql://myhost/cqdb --username test --table test --split-by value --hive-import --hive-table hivetest.test --direct

 

Below is error information:


Warning: /usr/lib/sqoop/../hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Warning: /usr/lib/sqoop/../hcatalog does not exist! HCatalog jobs will fail.
Please set $HCAT_HOME to the root of your HCatalog installation.
Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
Please set $ACCUMULO_HOME to the root of your Accumulo installation.
16/06/30 20:13:53 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6-cdh5.7.1
16/06/30 20:13:53 INFO tool.BaseSqoopTool: Using Hive-specific delimiters for output. You can override
16/06/30 20:13:53 INFO tool.BaseSqoopTool: delimiters with --fields-terminated-by, etc.
16/06/30 20:13:53 INFO manager.SqlManager: Using default fetchSize of 1000
16/06/30 20:13:53 INFO tool.CodeGenTool: Beginning code generation
16/06/30 20:13:54 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "test" AS t LIMIT 1
16/06/30 20:13:55 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /opt/cloudera/parcels/CDH/lib/hadoop-mapreduce
Note: /tmp/sqoop-root/compile/ff3b46874f8ef93db7a9388a340a7c4e/test.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
16/06/30 20:13:56 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-root/compile/ff3b46874f8ef93db7a9388a340a7c4e/test.jar
16/06/30 20:13:56 INFO manager.DirectPostgresqlManager: Beginning psql fast path import
16/06/30 20:13:57 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "test" AS t LIMIT 1
16/06/30 20:13:57 INFO manager.DirectPostgresqlManager: Copy command is COPY (SELECT "qtime", "value" FROM "test" WHERE 1=1) TO STDOUT WITH DELIMITER E'\1' CSV ;
16/06/30 20:13:57 INFO manager.DirectPostgresqlManager: Performing import of table test from database cqdb
16/06/30 20:13:59 INFO manager.DirectPostgresqlManager: Transfer loop complete.
16/06/30 20:13:59 INFO manager.DirectPostgresqlManager: Transferred 3 bytes in 0.8804 seconds (3.4075 bytes/sec)
16/06/30 20:14:00 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM "test" AS t LIMIT 1
16/06/30 20:14:01 WARN hive.TableDefWriter: Column qtime had to be cast to a less precise type in Hive
16/06/30 20:14:01 INFO hive.HiveImport: Loading uploaded data into Hive

Logging initialized using configuration in jar:file:/usr/lib/hive/lib/hive-common-1.1.0-cdh5.7.1.jar!/hive-log4j.properties
FAILED: SemanticException [Error 10072]: Database does not exist: hivetest

 

Cloudera Employee
Posts: 27
Registered: ‎11-04-2015

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

Hi,

 

Have you added "Sqoop 1 Client" role to that host? That's needed to be sure that the configurations are picked up correctly. If sqoop (and the Hive classes used by it) cannot find your hive configuration, then it will create an embedded hive metastore instance - which has nothing to do with your central DB backed HMS instance.

 

Miklos Szurap

Customer Operations Engineer

New Contributor
Posts: 4
Registered: ‎06-30-2016

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

 

Thanks for you replying.

 

I am using 'root' to run the sqoop command.

 

However I tried the sqoop on other 2 servers within my Cloudera cluster. Server2 and Server3 could access the hive database successfully.

Server1 Namenode & datanode

Server2 Datanode

Server3 Datanode

 

Notice the difference is as below when running the sqoop command

Server1 says: 

> Warning: /usr/lib/sqoop/../hbase does not exist! HBase imports will fail.
> Please set $HBASE_HOME to the root of your HBase installation.
> Warning: /usr/lib/sqoop/../hive-hcatalog does not exist! HCatalog jobs will fail.
> Please set $HCAT_HOME to the root of your HCatalog installation.
> Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
> Please set $ACCUMULO_HOME to the root of your Accumulo installation. 

Server2 & server3 say without HCAT & HBASE warnings:

Warning: /usr/lib/sqoop/../accumulo does not exist! Accumulo imports will fail.
> Please set $ACCUMULO_HOME to the root of your Accumulo installation. 

 

But I have never configured the 2 environments on server2&server3.

 

What's worse I don't know how to configure the 2 environments

 

Thanks,

King

New Contributor
Posts: 3
Registered: ‎08-15-2017

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

Hi,

What do you mean by "add "Sqoop 1 Client" role to that host"?

Like copy the sqoop directory to the host machine?

I searched the Sqoop User Guide and failed to find anything on that...

New Contributor
Posts: 3
Registered: ‎08-15-2017

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

I found that copying the hive-site.xml file into the $HADOOP_HOME/etc/hadoop/ directory can solve my problem.

New Contributor
Posts: 3
Registered: ‎08-15-2017

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

I found that copying the hive-site.xml file into the $HADOOP_HOME/etc/hadoop/ directory can solve my problem.

Champion
Posts: 547
Registered: ‎05-16-2016

Re: [HELP!!!] Sqoop could not access the hive database (CM 5.7.x)

[ Edited ]

@kingzhou @hejing195

you could have used the wizard that will perform everything in the background.

as you already knew that sqoop is a client tool that requires all the client configuration file and other binary , if you install gateway role on the host that you want to run the sqoop it will take care. please refer the below link to add the sqoop 1 role 

 

https://www.cloudera.com/documentation/enterprise/5-5-x/topics/cm_mc_sqoop1_client.html

Announcements