Export from Hive to MySQL Issue

Technical Discussions Sqoop
#1

Hi All,

I’ve uploaded JSON Data in Hive as given in one the exercise in course. Then I tried to export this data to mysql using sqoop as one of my attempt. But the output I’m getting in mysql is totally different. It shows me numeric data whereas in Hive it is string data. Is there any issue with the data type conversion etc. Please help.

hadoop fs -cp /data/sample_json /user/manojgupta915880
hadoop fs -ls /user/manojgupta915880/sample_json
hadoop fs -tail /user/manojgupta915880/sample_json/user_country.json

ADD JAR hdfs:///data/serde/json-serde-1.3.6-SNAPSHOT-jar-with-dependencies.jar;

hive
use manojgupta915880

CREATE EXTERNAL TABLE user_countries(
    name string,
    country string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION 'hdfs:///user/manojgupta915880/sample_json';

hive> select * from user_countries ;
OK
Abhinav India
John    US
Bob     Germany
Richa   UK
Time taken: 0.53 seconds, Fetched: 4 row(s)
hive>


mysql -h cxln2.c.thelab-240901.internal -u sqoopuser -p

Enter Password : NHkkP876rp

use sqoopex

CREATE TABLE user_countries(
    name varchar(100),
    country varchar(100)
) ;

sqoop export --connect jdbc:mysql://cxln2.c.thelab-240901.internal/sqoopex -m 1 --table user_countries --export-dir /apps/hive/warehouse/sg.db/sales_test --username sqoopuser --password NHkkP876rp

mysql> select * from user_countries ;
+------+---------+
| name | country |
+------+---------+
| 1    | 15      |
| 3    | 4       |
| 2    | 5       |
| 2    | 7       |
+------+---------+
4 rows in set (0.00 sec)

mysql>

Regards
Manoj

#2

Hi @Manoj_Kumar,

Could you please let us know the output of MySQL. If datatypes are not properly defined in MySQL then there can be a mismatch.

#3 
mysql> select * from user_countries ;
+------+---------+
| name | country |
+------+---------+
| 1    | 15      |
| 3    | 4       |
| 2    | 5       |
| 2    | 7       |
+------+---------+
4 rows in set (0.00 sec)

mysql>

Complete script is also provided in my initial post above.