Exporting data from Hive table to RDBMS

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.com
    2,Virat,Kohali,virat@bcci.com
    5,Sachin,Tendulkar,sachin@bcci.com
    
  2. Upload the contacthive.csv that you created in last step in HDFS at /tmp folder using following command
    
    hdfs dfs -put contacthive.csv /tmp
    
  3. 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;
    
  4. 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;
    
  5. 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
    
  6. 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)
    );
    
  7. 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
    

23 comments:

identifymecnu said...

Thank you for the clear steps Sunil.
Could you please also post how to export a parquet file from HDFS to RDBMS.

I have a directory with parquet files( generated from spark operations) and the corresponding metadata files. How do I specify sqoop that it is a parquet file and to load using the schema

Unknown said...

Great and interesting article to read.. i Gathered more useful and new information from this article.thanks a lot for sharing this article to us..

best big data training center in Chennai | best big data hadoop training and certification

Tejuteju said...

Thank you for providing useful content Big data Hadoop online training Hyderabad
Big data hadoop online training

gowsalya said...

Your good knowledge and kindness in playing with all the pieces were very useful. I don’t know what I would have done if I had not encountered such a step like this.
Devops Training in pune

Devops Training in Chennai

Devops Training in Bangalore

AWS Training in chennai

AWS Training in bangalore





Unknown said...

Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

java training in jayanagar | java training in electronic city

java training in chennai | java training in USA

Anonymous said...

UiPath Training in Bangalore by myTectra is one the best UiPath Training. myTectra is the market leader in providing Robotic Process Automation on UiPath
ui path training in bangalore

Unknown said...

I really like the dear information you offer in your articles. I’m able to bookmark your site and show the kids check out up here generally. Im fairly positive theyre likely to be informed a great deal of new stuff here than anyone
Data Science course in kalyan nagar | Data Science course in OMR
Data Science course in chennai | Data science course in velachery
Data science course in jaya nagar

pavithra dass said...

Great Article… I love to read your articles because your writing style is too good, its is very very helpful for all of us. Do check | Get trained by an expert who will enrich you with the latest updates.
Cloud computing Training in Chennai
Hadoop Training in Chennai
Cloud Training in Chennai
Best institute for Cloud computing in Chennai
Big Data Course in Chennai
Big Data Hadoop Training in Chennai

Anjali Siva said...

One of the great article, I have seen yet. Waiting for more updates.
DevOps certification in Chennai
DevOps Training in Chennai
Best AWS Training in Chennai
AWS course in Chennai
Data Science Course in Chennai
Big Data Analytics Courses in Chennai
DevOps Training in OMR
DevOps Training in Porur

kevin said...

This is most user friendly and informative.Keep posting more blog like this,Thank you...
Hadoop training in Bangalore|
Big Data Analytics Training in Bangalore|
Hadoop Training in Bellandur|
Hadoop Training in Bangalore
Hadoop Training in Marathahalli

jude said...

Amazing blog with the recent news. Thank you very much for sharing such helpful data...
Big Data Analytics Training in Bangalore|
Hadoop Training in Bellandur|
Hadoop Training in Bangalore|
Hadoop Training in Marathahalli|
Hadoop training in Bangalore

michale said...

I went through your blog,it helped me a lot,and I also received some new information...
Hadoop Training in Marathahalli|
Hadoop training in Bangalore|
Big Data Analytics Training in Bangalore|
Hadoop Training in Bellandur|
Hadoop Training in Bangalore

dhanush kumar said...


Actually I read it yesterday but I had some thoughts about it and today I wanted to read it again because it is very well written.

salesforce Training in Bangalore
uipath Training in Bangalore
blueprism Training in Bangalore

Sojani Maha said...

A very nice post. Thanks for sharing such a piece of valuable information...
AWS Training in Marathahalli
AWS Training in Bangalore
RPA Training in Kalyan Nagar
Data Science with Python Training Bangalore
AWS Training in Kalyan Nagar
RPA training in bellandur

Anonymous said...

For Hadoop Training in Bangalore- Hadoop Training in Bangalore

Bhanu Ravi said...

Your post is really awesome. It is very helpful for me to develop my skills in a right way.keep sharing such a worthy information

aws Training in Bangalore
python Training in Bangalore
hadoop Training in Bangalore
angular js Training in Bangalore
bigdata analytics Training in Bangalore

ethiraj raj said...

Very interesting, good job and thanks for sharing such a good blog. your article is so convincing that I never stop myself to say something about it. You’re doing a great job. Keep it up

aws Training in Bangalore
python Training in Bangalore
hadoop Training in Bangalore
angular js Training in Bangalore
bigdata analytics Training in Bangalore

veera Ravula said...

Very nice blog,keep sha4ing more topics with us.
Thank you for info...

Intrested one visit,

big data training
hadoop admin training
cognos training
cognos tm1 training
android online training
ios online training

Sarthak Yadav said...

Good Post! , it was so good to read and useful to improve my knowledge as an updated one, keep blogging.After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts likethis. https://www.3ritechnologies.com/course/sap-online-training-certification-course/

ram said...

Thank you for posting informative insights, I think we have got some more information to share with! Do check out
oracle training in chennai and let us know your thoughts. Let’s have great learning!

babuloo said...

Infycle Technologies is the best software training institute in Chennai, which offers amazing Oracle training in Chennai in 100% practical training with experienced trainers in the field. Apart from the training, the mock interviews will be arranged for the students, so that, they can face the interviews without any struggles. Of all that, complete placement assurance will be given in top MNC's. For more details, call 7502633633 to Infycle Technologies and grab a free demo to know more.
best oracle training in Chennai

RameshMSR said...

Python Training in Chennai | Infycle Technologies


If Python is a work you've always wanted, we at Infycle are here to help you make it a reality. Infycle Technologies provides Python Training in Chennai, with various levels of highly sought-after software courses such as Oracle, Java, Python, Big Data, and others, delivered through 100% hands-on practical training with industry experts. In addition, mock interviews will be conducted. For more details contact 7502633633 to grab a free demo.Best python training in Chennai

George said...

Thedata lake platformis designed to help organizations streamline the storage, analysis and use of data. Here's a breakdown of some of the ways it can help your organization more easily store, access, and use your data.