Import table Mysql to HDFS Using Apache Sqoop

Hi everyone, In this article, I will transfer a table on Mysql to the HDFS file system using sqoop.

Sqoop provides the ability to transfer from any RDBMS to an HDFS system.

Now let’s continue with the example, Let’s download a sample set from kaggle


Move the csv file to the virtual machine through WinSCP or a similar program.  connect to mysql and create a table that fits the data set.

create table  Border_Crossing_Entry ( port_Name varchar(50), state varchar(50), port int, border varchar(50), border_date date, measure varchar(50), Value int, Location varchar(50));

This script transfers the data from our csv file to the table.

load data local infile '/home/cloudera/Border_Crossing_Entry_Data.csv' into table Border_Crossing_Entry fields terminated by ',' enclosed by '"' lines terminated by '\n' (port_Name,state,port,border,border_date,measure,Value,Location); 

blank

 

Now let’s create the target directory in HDFS.  

hadoop fs -mkdir /user/cloudera/sqoop_exp 

For import, let’s set the sqoop parameters and start the operations.

sqoop import --connect jdbc:mysql://quickstart:3306/retail_db --username=retail_dba --password=cloudera --table Border_Crossing_Entry --m 1 --target-dir /user/cloudera/sqoop.exp

blank

Finally, let’s take a look at the file we show as output to hdfs.

hadoop fs -ls /user/cloudera/sqoop.exp/

blank

 

If you look inside the file with m-00000, you can see that the data set is there.

See you in the next article…

 302 views last month,  4 views today

About Deniz Parlak

blank
Hi, i’m Security Data Scientist & Data Engineer at My Security Analytics. I have experienced Advance Python, Machine Learning and Big Data tools. Also i worked Oracle Database Administration, Migration and upgrade projects. For your questions parlak.deniss@gmail.com

Check Also

Goldengate Replication – Oracle To Bigdata

GOLDENGATE Oracle to Hdfs ############# Project Information ############### #target pbigdata001 10.34.81.67 bigdata /bigdata_disk/gg/ ########################################################## GGSCI …

Leave a Reply