Sqoop Export

Let’s study about different ways of Sqoop Export,

1. Export data from hdfs to RDBMS

In this article will learn how to export data from hdfs to RDBMS. Before exporting the data target table must be present in the mysql, to send data from hdfs to mysql.

Example

Consider “newemp” data file in hdfs (shown below). The “newemp” file available in the path of   “/user/beyond-corner/empdata/newemp”.

100,’roy’,’mysore’,25000

200,’sitha’,’Delhi’,20000

300,’raju’,’mumbai’,30000

400,’koyal’,’jaipur’,23000

Now the above “newemp” file need to export into mysql, so in mysql target table must be create.

Below commands explains how to login to mysql to create table called “beyondemp

$ mysql –u root –p

mysql> create database beyondcorner;

> use beyondcorner;

>create table beyondemp(id int,name varchar(25),city varchar(25),salary int);

>desc beyondemp;

Now we are importing “newemp” data file from hdfs to mysql table called “beyondemp”.

$ sqoop export –connect jdbc:mysql://localhost/beyondcorner

–table “beyondemp”

–export-dir “/user/beyond-corner/empdata/newemp”

–username “root”

–password “beyonduser”

-m 1

The below command is used to verify the table in mysql command line.

mysql> select * from beyondemp;

> select count(*) from beyondemp;

2. Export data from hive to mysql

In this stage we are exporting data from hive to RDBMS. Before exporting the data target table must be present in the mysql, to send data from hive to mysql.

Example

Consider “newemp1” data file in hive (shown below). The “newemp1” file available in the path of   “/user/hive/warehouse/newemp1”.

100,’roy’,’mysore’,25000

200,’sitha’,’Delhi’,20000

300,’raju’,’mumbai’,30000

400,’koyal’,’jaipur’,23000

Now the above “newemp1” file need to export into mysql, so in mysql target table must be create.

Below commands explains how to login to mysql to create table called “beyondemp2

$ mysql –u root –p

mysql> create database beyondcorner;

> use beyondcorner;

>create table beyondemp2(id int,name varchar(25),city varchar(25),salary int);

>desc beyondemp2;

Now we are importing “newemp1” data file from hdfs to mysql table called “beyondemp2”.

$ sqoop export –connect jdbc:mysql://localhost/beyondcorner

–table “beyondemp2”

–export-dir “/user/hive/warehouse/newemp1”

–username “root”

–password “beyonduser”

-m 1

–driver com.mysql.jdbc.Driver

The below command is used to verify the table in mysql command line.

mysql> select * from beyondemp2;

> select count(*) from beyondemp2;

3. Update the records in the RDBMS from HDFS data

In this case it will only update already existing records, it will not insert new records in the RDBMS.

Example

$ sqoop export –connect jdbc:mysql://localhost/beyondcorner

–username “root”

–password “beyonduser”

–table “beyondemp”

–export-dir “/user/beyond-corner/empdata/newemp/part-m-00000”

–update-key id

References

https://sqoop.apache.org/docs/1.4.6/SqoopUserGuide.html#_literal_sqoop_export_literal

Thats all about the Sqoop export, i hope this article help full for Sqoop beginners.