Sqoop: How to Export data from Hadoop (Hive or Hbase) to RDBMS

Overview:

We have an sqoop export tool to export the data back to RDBMS from Hadoop. The only thing you should take care here is, you must have the table created in RDBMS before exporting data from Hadoop to a relational database.

Scenario: I need to migrate a hive table "largedeck" to a RDBMS ( mysql) table "demo" using sqoop.

Step 1:

Download a sample largedeck.txt file to local folder of the hive server.
-------
cd /tmp/testing/
wget https://github.com/dgadiraju/data/raw/master/cards/largedeck.txt.gz

-----

Sample content of largedeck of cards:

------

[root@manoj-testing]# head -10 largedeck.txt

BLACK|SPADE|2

BLACK|SPADE|3

BLACK|SPADE|4

BLACK|SPADE|5

BLACK|SPADE|6

BLACK|SPADE|7

BLACK|SPADE|8

BLACK|SPADE|9

BLACK|SPADE|10

BLACK|SPADE|J

------

Step 2:
-------
Create an external table "largedeck" in hive and Load the data from local folder "/tmp/testing" to
 that external table.
--------

Command to create a hive external table "largedeck" to location "/user/manoj/largedeck":
-----------
create external table largedeck (color string, suit string, pip string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
LOCATION '/user/manoj/largedeck.txt';

-----------

STEP 3:

LOAD the data to hive table "largedeck"
--------
LOAD DATA  LOCAL INPATH '/tmp/testing/largedeck.txt' INTO TABLE largedeck;
--------


STEP 4: Run the following commands from hive beeline shell to make sure data has been load
-------
>select * from largedeck limit 10;
> select suit, count(1) from largedeck group by suit;
-------

STEP 5:Create a database "demo" in mysql servers and set the user as "demo_user". The password for it is 'changeit'


------
create database demo;
create user 'demo_user' identified by 'changeit';
grant all on demo.* to demo_user;
flush privileges;
------

STEP 6: Now create the following tables in table

----------
mysql -u demo_user -p

create table deck_of_cards (color varchar(10), suit varchar(10), pip varchar(2));
----------

STEP 7:  Run the command from sqoop server to start exporting the hive table "largedeck" to mysql table "demo_user".

--------
 sqoop export --connect "jdbc:mysql://<hostname-of-mysql-server>/demo" --username demo_user --password changeit --table deck_of_cards --export-dir /user/manoj/largedeck.txt --input-fields-terminated-by '|' --num-mappers 2 --outdir java_files
 -------

STEP 8:  Once sqoop export is complete run the below command in mysql shell to see the output

-------
> select count(1) from deck_of_cards;
 -----