Copying data from HDFS to hive using SQOOP

Question

I want to copy data from HDFS to hive table. I tried below code but it doesn't throw any error and data is also not copied in mentioned hive table. Below is my code:

sqoop import --connect jdbc:mysql://localhost/sampleOne \
--username root \
--password root \
--external-table-dir "/WithFields" \
--hive-import \
--hive-table "sampleone.customers"

where sampleone is database in hive and customers is newly created table in hive and --external-table-dir is the HDFS path from where I want to load data in hive table. What else I am missing in this above code ??

You do not need Sqoop to copy data from HDFS to Hive. Just create Hive table with your HDFS location or copy data to existing Hive table location using hadoop fs -scp command — leftjoin
Stack Overflow is a site for programming and development questions. This question appears to be off-topic because it is not about programming or development. See What topics can I ask about here in the Help Center. Perhaps Super User or Unix & Linux Stack Exchange would be a better place to ask. — jww

Jagrut Sharma Jagrut Sharma · Accepted Answer · 2018-04-29T08:16:14

If data is in HDFS, you do not need Sqoop to populate a Hive table. Steps to do this are below:

This is the data in HDFS

# hadoop fs -ls /example_hive/country
/example_hive/country/country1.csv

# hadoop fs -cat /example_hive/country/*
1,USA
2,Canada
3,USA
4,Brazil
5,Brazil
6,USA
7,Canada

This is the Hive table creation DDL

CREATE TABLE sampleone.customers
(
  id int, 
  country string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

Verify Hive table is empty

hive (sampleone)> select * from sampleone.customers;
<no rows>

Load Hive table

hive (sampleone)> LOAD DATA INPATH '/example_hive/country' INTO TABLE sampleone.customers;

Verify Hive table has data

hive (sampleone)> select * from sampleone.customers;
1   USA
2   Canada
3   USA
4   Brazil
5   Brazil
6   USA
7   Canada

Note: This approach will move data from /example_hive/country location on HDFS to Hive warehouse directory (which will again be on HDFS) backing the table.

Copying data from HDFS to hive using SQOOP

1 Answers