Sqoop import data to hive and hdfs

1
votes

Can we import data with sqoop into hdfs and hive? I have try it with two different user (root and admin) Below the command between root and admin

[Root]

sqoop import --connect jdbc:mysql://10.6.162.58/test_alpha --username pbd -P --table posts --hive-import --hive-database test_root --hive-table posts1 --hive-drop-import-delims --null-string '\\N' --null-non-string '\\N' --target-dir test_hive_root/2

[Admin]

sqoop import --connect jdbc:mysql://10.6.162.58/test_alpha --username pbd -P --table posts --hive-import --hive-database test_admin --hive-table posts1 --hive-drop-import-delims --null-string '\\N' --null-non-string '\\N' --target-dir test_hive_admin/2

It returns:

Both ways successful import data to hive but both fail import to HDFS.

For root, it don't create directory that I've defined "test_hive_root/2"

For admin, it's just create directory "test_hive_admin/2" but only "success" file that have been created not the data (usually when I import it to HDFS, it will create "success" file and 4 more file)

How I can solve this problem? Can sqoop import to HDFS and Hive?

2
mysqlhivehdfssqoophortonworks-data-platform
How do you 'import to HDFS' ? Hive table is nothing more than Table created with HDFS location. I you created HDFS files you should always be able to read them using Hive table if location is the same HDFS where you loaded files - leftjoin
This how I import to HDFS using user root " sqoop import --connect jdbc:mysql://10.6.162.58/test_alpha --username pbd -P --table posts --hive-drop-import-delims --null-string '\\N' --null-non-string '\\N' --target-dir test_hive_root/2 " - m hanif f
Or if using user admin "sqoop import --connect jdbc:mysql://10.6.162.58/test_alpha --username pbd -P --table posts --hive-drop-import-delims --null-string '\\N' --null-non-string '\\N' --target-dir test_hive_admin/2" - m hanif f
If I import to HDFS , I can track where the files imported to hdfs but when I import to Hive (using --hive-import) I can't track where the files imported in hdfs. In target-dir, there is only _success file - m hanif f
and is target dir the same as table location? - leftjoin

2 Answers

2
votes

target-dir controls where the data needs to store temporarily before loading into Hive table, but doesn't create hive table in that location.

Table is created in the directory specified in hive.metastore.warehouse.dir property in the hive-site.xml.

If you want to import to specific directory then use target-dir without hive-import argument and create hive table on top of HDFS directory.

(or)

Create Hive external table pointing to your target-dir then in sqoop import remove --create-hive-table argument and --target-dir.

Read the answer here: https://community.hortonworks.com/questions/198586/running-a-sqoop-job-with-a-target-dir-destination.html

And here: https://community.hortonworks.com/questions/193934/sqoop-import-mssql-table-into-hdfs.html

0
votes

Hope you used the / prefix when giving the path for --target-dir 

sqoop import --connect jdbc:mysql://10.6.162.58/test_alpha --username pbd -P --table posts --hive-import --hive-database test_root --hive-table posts1 --hive-drop-import-delims --null-string '\\N' --null-non-string '\\N' --target-dir /test_hive_root/2