Exporting data from Hive table to RDBMS

22. April 2015 - 0:04

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.

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

1,MahendraSingh,Dhoni,mahendra@bcci.com2,Virat,Kohali,virat@bcci.com5,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
    • Sunil Patil
    http://feedproxy.google.com/~r/WebspherePortalCertification/~3/UFSuvKnGdeY/exporting-data-from-hive-table-to-rdbms.html