SQOOP

SQOOP= (SQ)L + HAD(OOP)

Relational database systems often store valuable in a company. If made available, that data can be managed and processed by Apache Hadoop, which is fast becoming the standard for big data processing. Several relational database vendors championed developing integration with Hadoop within one or more on there.

Transferring data to and from relational databases is challenging and laborious. Because data transfer requires careful handling, Apache Sqoop, short for “SQL to Hadoop,” was created to perform bidirectional data transfer between Hadoop and almost any external structured datastore. Taking advantage of MapReduce, Hadoop’s execution engine, Sqoop performs the transfers in a parallel manner.

Step 1: Download sqoop

Download the sqoop-1.4.5.bin.tar.gz file into your required location and extract it.

Step 2: Set path in bashrc

Open bashrc file and set the sqoop path

export SQOOP_HOME=$HOME/sqoop 1.4.5

export PATH= $SQOOP_HOME/bin

sqoop tutorial, sqoop installation

Step 3 : Copy mysql connector

Copy mysql-connector.jar file from the location of /usr/share/java

and copyto sqoop-1.4.5/lib

Step 4 : Open mysql

To open mysql type the following command in the terminal.

mysql -h localhost -u root –p

Step 5 : Create database in mysql

Sysntax : create database databasename;

> create database sqoopdb;

sqoop tutorial, sqoop installation

Step 6 : change the database to sqoopdb

Syntax : use databasename;

> use sqoopdb;

sqooop installation, sqoop tutoril

Step 7 : create table emp

Syntax : create table tablename(column 1 datatype,column 2 datatype …);

> create table emp(empno int,ename varchar(10),sal int);

sqoop installation, sqoop tutorial

Step 8 : insert values into emp table

Syntax : insert into tablename values(value1,value2 …);

> insert into emp values (11,’aaa’, 600);

sqoop installation, sqoop tutorial

Step 9 : Grant the sqoopdb

> grant all privileges on sqoopdb.* to ‘%’@’localhost’

sqoop installation,sqoop tutorial

Step 10 :

> grant all privileges on sqoopdb.* to ”@localhost ;

sqoop installation, sqoop tutorial

Step 11 : Run the sqoop commands

Go to Sqoop/bin folder in the terminal and run the following commands

To load the data from Mysql to HDFS

./sqoop-import –connect jdbc:mysql://localhost/sqoopdb -username root -password root -table emp -m 1

sqoop installation , sqoop tutorial

Step 12 : View imported table

To view the imported table from mysql to hdfs

Open localhost in the browser and go to the import path /user/geouser

sqoop installation, sqoop tutorial

Step 13:

To view the data in table using this bellow cat command in hadoop.

./hadoop fs -cat /user/geouser/emp/part-m-00000

sqoop tutorial, sqoop installation