CODEWITHSUNDEEP
hadoophivehdfssqoop
elprup
elprup

Reputation: 1998

sqoop import complete but hive show tables can't see table

After install hadoop, hive (CDH version) I execute

./sqoop import -connect jdbc:mysql://10.164.11.204/server -username root -password password -table user -hive-import --hive-home /opt/hive/

All goes fine, but when I enter hive command line and execute show tables, there are nothing. I use ./hadoop fs -ls, I can see /user/(username)/user existing.

Any help is appreciated.

---EDIT-----------

/sqoop import -connect jdbc:mysql://10.164.11.204/server -username root -password password -table user -hive-import --target-dir /user/hive/warehouse

import fail due to :

11/07/02 00:40:00 INFO hive.HiveImport: FAILED: Error in semantic analysis: line 2:17 Invalid Path 'hdfs://hadoop1:9000/user/ubuntu/user': No files matching path hdfs://hadoop1:9000/user/ubuntu/user
11/07/02 00:40:00 ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: Hive exited with status 10
        at com.cloudera.sqoop.hive.HiveImport.executeExternalHiveScript(HiveImport.java:326)
        at com.cloudera.sqoop.hive.HiveImport.executeScript(HiveImport.java:276)
        at com.cloudera.sqoop.hive.HiveImport.importTable(HiveImport.java:218)
        at com.cloudera.sqoop.tool.ImportTool.importTable(ImportTool.java:362)
        at com.cloudera.sqoop.tool.ImportTool.run(ImportTool.java:423)
        at com.cloudera.sqoop.Sqoop.run(Sqoop.java:144)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:79)
        at com.cloudera.sqoop.Sqoop.runSqoop(Sqoop.java:180)
        at com.cloudera.sqoop.Sqoop.runTool(Sqoop.java:218)
        at com.cloudera.sqoop.Sqoop.main(Sqoop.java:228)

Upvotes: 1

Views: 11401

Answers (7)

Samba Siva Reddy
Samba Siva Reddy

Reputation: 59

no need of creating the table in hive..refer the below query 

sqoop import --connect jdbc:mysql://xxxx.com/Database name --username root --password admin --table tablename (mysql table) --direct -m 1 --hive-import --create-hive-table --hive-table table name --target-dir '/user/hive/warehouse/Tablename(which u want create in hive)' --fields-terminated-by '\t'

Upvotes: 2

BalaramRaju
BalaramRaju

Reputation: 439

I think all you need is to specify the hive table where data should go. add "--hive-table database.tablename" to the sqoop command and remove the --hive-home /opt/hive/. I think that should resolve the problem.

Upvotes: 0

amitava
amitava

Reputation: 505

in my case it creates table in hive default database, you can give it a try.

sqoop import --connect jdbc:mysql://xxxx.com/Database name --username root --password admin --table NAME --hive-import --warehouse-dir DIR --create-hive-table --hive-table NAME -m 1

Upvotes: 1

user1631977
user1631977

Reputation: 61

Check your hive-site.xml for the value of the property javax.jdo.option.ConnectionURL. If you do not define this explicitly, the default value will use a relative path for creation of hive metastore (jdbc:derby:;databaseName=metastore_db;create=true) which will be different depending upon where you launch the process from. This would explain why you cannot see the table via show tables.

define this property value in your hive-site.xml using an absolute path

Upvotes: 3

Debaditya
Debaditya

Reputation: 2497

First of all , create the table definition in Hive with exact field names and types as in mysql.

Then, perform the import operation

For Hive Import 

sqoop import --verbose --fields-terminated-by ','  --connect jdbc:mysql://localhost/test --table tablename --hive-import --warehouse-dir /user/hive/warehouse --fields-terminated-by ',' --split-by id --hive-table tablename
  • 'id' can be your primary key of the existing table
  • 'localhost' can be your local ip
  • 'test' is database
  • 'warehouse' directory is in HDFS

Upvotes: 0

Kartik
Kartik

Reputation: 3

Hive tables will be created by Sqoop import process. Please make sure the /user/hive/warehouse is created in you HDFS. You can browse the HDFS (http://localhost:50070/dfshealth.jsp - Browse the File System option.

Also include the HDFS local in -target dir i.e hdfs://:9000/user/hive/warehouse in the sqoop import command.

Upvotes: 0

wlk
wlk

Reputation: 5785

In my case Hive stores data in /user/hive/warehouse directory in HDFS. This is where Sqoop should put it.

So I guess you have to add:

--target-dir /user/hive/warehouse

Which is default location for Hive tables (might be different in your case).

You might also want to create this table in Hive:

sqoop create-hive-table --connect jdbc:mysql://host/database --table tableName --username user --password password

Upvotes: 1

Related Questions