Sqoop import for existing hive table

Big Data Apache Sqoop
#1

Hi All,

I did created a database and table in hive, later i try to load the data using sqoop import to that table as shown below. But when i select that table i was getting a null values. Please advice.

hive> show databases;
OK
default
hadoopexam

hive> use hadoopexam;
OK

hive> show tables;
OK
departments

hive> desc departments;
OK
department_id int
department_name string
Time taken: 0.072 seconds, Fetched: 2 row(s)

hive> select * from departments;
OK
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
Time taken: 0.06 seconds, Fetched: 6 row(s)

Sqoop command which i am using as :-

sqoop import --connect jdbc:mysql://quickstart:3306/retail_db --username retail_dba --password cloudera --table departments --hive-home /user/hive/warehouse --hive-import --hive-overwrite --hive-table hadoopexam.departments;

Is anything missing ?

#2

with the information you have posted I would think that the datatype which is used in your hive table is not compatible with your data… Hive is schema on read and hence the problem while doing select…

#3

Can you paste the output of describe formatted on the hive table.

#4

@dksrinivasa … I believe you have used different delimiter while creating Hive table. Please check the delimiter in the file you downloaded from Sqoop and the Delimiter you used for Hive table.

#5

hive> desc formatted departments;
OK

col_name data_type comment

department_id int
department_name string

Detailed Table Information

Database: hadoopexam
Owner: cloudera
CreateTime: Mon Dec 05 09:39:04 PST 2016
LastAccessTime: UNKNOWN
Protect Mode: None
Retention: 0
Location: hdfs://quickstart.cloudera:8020/user/hive/warehouse/hadoopexam.db/departments
Table Type: MANAGED_TABLE
Table Parameters:
COLUMN_STATS_ACCURATE true
numFiles 4
numRows 0
rawDataSize 0
totalSize 60
transient_lastDdlTime 1480963240

Storage Information

SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
InputFormat: org.apache.hadoop.mapred.TextInputFormat
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Compressed: No
Num Buckets: -1
Bucket Columns: []
Sort Columns: []
Storage Desc Params:
field.delim ,
serialization.format ,
Time taken: 0.174 seconds, Fetched: 33 row(s)

#6

@SrikanthGanapavarapu, how to change the delimiter explicitly while importing table to Hive. Please advice.

#7

@dksrinivasa Please go through http://www.itversity.com/topic/sqoop-delimiters-file-formats/

#8

Thanks got it @SrikanthGanapavarapu.