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
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;
Step 6 : change the database to sqoopdb
Syntax : use databasename;
> use sqoopdb;
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);
Step 8 : insert values into emp table
Syntax : insert into tablename values(value1,value2 …);
> insert into emp values (11,’aaa’, 600);
Step 9 : Grant the sqoopdb
> grant all privileges on sqoopdb.* to ‘%’@’localhost’
Step 10 :
> grant all privileges on sqoopdb.* to ”@localhost ;
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
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
To view the data in table using this bellow cat command in hadoop.
./hadoop fs -cat /user/geouser/emp/part-m-00000