Create contacthive.csv file which has simple data with 4 columns separated by comma
1,MahendraSingh,Dhoni,email@example.com 2,Virat,Kohali,firstname.lastname@example.org 5,Sachin,Tendulkar,email@example.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
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.