Sunil's Notes: April 2015

In the Importing data from RDBMS into Hadoop using sqoop i blogged about how to import data from RDBMS to Hive, but now i wanted to figure out how to export data from Hive back to RDBMS, Sqoop has export feature that allows you to export data from Hadoop directory(CSV files in a directory) to RDBMS, I wanted to try exporting data from sqoop so first i created a simple contact_hive table and populated some data in it, then i used sqoop to export the content of contact_hive table into contact table in MySQL, i followed these steps, if you already have a hive table populated then you can skip first 5 steps and go to step 6.

Create contacthive.csv file which has simple data with 4 columns separated by comma


1,MahendraSingh,Dhoni,mahendra@bcci.com
2,Virat,Kohali,virat@bcci.com
5,Sachin,Tendulkar,sachin@bcci.com

Upload the contacthive.csv that you created in last step in HDFS at /tmp folder using following command
```
hdfs dfs -put contacthive.csv /tmp
```

Define a contact_hive table that will have 4 columns, contactId, firstName, lastName and email, execute this command in hive console


CREATE TABLE contact_hive(contactId Int, firstName String, lastName String, email String) row format delimited fields terminated by "," stored as textfile;

In this step populate the contact_hive table that you created in the last step with the data from contacthive.csv file created in step 1. Execute this command in Hive console to populate contact_hive table
```
LOAD DATA INPATH  "/tmp/contacthive.csv" OVERWRITE INTO TABLE contact_hive;
```
Since i am using Hive managed table, it will move the contacthive.csv file to Hive managed directory in case of Hortonworks that directory is /apps/hive/warehouse, You can verify that by executing following command on HDFS
```
hdfs dfs -ls /apps/hive/warehouse/contact_hive
```

Before you export data into RDBMS, you will have to create the table in mysql, use following command to create the CONTACT table in mysql.



CREATE TABLE CUSTOMER (
      contactid INTEGER NOT NULL ,
      firstname VARCHAR(50),
      lastname  VARCHAR(50),
      email varchar(50)
);

Now last step is to execute sqoop export command that exports data from hive/hdfs directory to database


sqoop export --connect jdbc:mysql://localhost/test --table CONTACT --export-dir /apps/hive/warehouse/contact_hive

Exporting data from Hive table to RDBMS