5 SQOOP

Introduction to SQOOP
Sqoopis a tool used to import and trade information between Hadoop and tagged social databases. Characteristics of Sqoopare as follows:

• Uses SqoopImport and SqoopExport capacities.
• Is composed in Java which gives an API called Java Database Connectivity.
• Depends on the database to depict the composition of the information to be foreign made.
• Uses Mapreduce to import and fare the information, which gives parallel operation.

How SQOOP Works
The Sqoop includes activities such as:

• ImportAllows data imports from external data stores and enterprise data warehouses into Hadoop
• TransferParalleled data transfer for fast performance and optimal system utilization
• CopyCopies data quickly from external systems to Hadoop
• Increase efficiencyMakes data analysis more efficient
• Reduce loadMitigates excessive loads to external systems

Prerequisite for SQOOP Installation
The prerequisites to install Sqoop are:

• An arrival of Hadoop must be introduced and designed.
• Currently, Sqoop supports 4 noteworthy Hadoop discharges—0.20, 0.23, 1.0 and 2.0.
• Hadoop 2.2.0 is also introduced and it goes well with sqoop1.4.4.
• A Linux environment Ubuntu 12.04 is also required.

Installing and configuring Sqoop
There are ten steps in the process. The first four steps to install and configure sqoop 1.4.4 are:

• Download the sqoop-1.4.4.bin_hadoop-1.0.0.tar.gz file from www.apache.org/dyn/closer.cgl/sqoop/1.4.4
• Unzip the tar ?le: sudo tar -zxvf  sqoop-1.4.4.bin_hadoop-1.0.0.tar.gz
• Move  sqoop-1.4.4.bin_hadoop-1.0.0 to sqoop using command
  user@ubuntu:~$ sudo mv sqoop-1.4.4.bin_hadoop-1.0.0 /usr/local/sqoop
• Create a directory sqoop in usr/lib using command:
  user@ubuntu:~$ sudo mkdir /usr/lib/sqoop
• Go to the zipped folder sqoop-1.4.4.bin_hadoop-1.0.0 and run the command:
  user@ubuntu:~$ sudo mv ./* /usr/lib/sqoop
• Go to root directory using cd command (example)
  user@ubuntu:~$ sudo gedit ~/.bashrc
• Reduce remove unwanted data
• Add the following lines:
  export SQOOP_HOME:iusr/lib/sqoop
  export PATH=$PATH:$SQOOP_HOME/BIN
• To check if the sqoop has been installed successfully type the command:
  user@ubuntu:~$ sqoop version

Importing Data from MySQL

The steps to import Data from MySQL using sqoop are:

• Download mysql-connector-java-5.1.28-bin.jar and move to /usr/lib/sqoop/lib
• Login to mysql using command
  user@ubuntu:~$ mysql -u root -p 
• Login to secure shell using command
  user@ubuntu:~$ ssh localhost
• Start hadoop using the command
  user@ubuntu:~$ bin/hadoop start-all.sh
• Run the command

Business Scenario
Olivia is the EVP of IT Operations with Nutri Worldwide Inc. The data for the company has grwon exponentially to 500 terabytes, and the current RDBMS systems poses challenges in terms of latency. Hence, Olivia has decided to move all the data from RDBMS systems to HDFS, for which Sqoop needs to be installed.

Demo- Install sqoop

• Visit the site sqoop.apache.org in firefox Browser.
• Under the heading "Download" click on "Download a release from a nearby mirror"
• Click on the suggested mirror link on the top.
• Under the title "Index of /sqoop" click on the folder "1.4.4/"
• Right click and copy link to "sqoop-1.4.4.bin_hadoop-1.0.0.tar.gz"
• Open a terminal and then type "wget " and then paste the link:
  user@ubuntu:~$ wget http://apache.petsads.us/sqoop/1.4.4/sqoop-1.4.4.bin_hadoop-1.0.0.tar.gz
• Untar the downloaded tar file.
  user@ubuntu:~$ tar -xvf sqoop-1.4.4.bin_hadoop-1.0.0.tar.gz
• Copy the folder to the /usr/local/sqoop folder
  user@ubuntu:~$ sudo cp -r  sqoop-1.4.4.bin_hadoop-1.0.0    /usr/local/sqoop
• Edit the bashrc to update the environment variables.
  user@ubuntu:~$ sudo vi $HOME/.bashrc
  export PATH=$PATH:/usr/local/sqoop
  export HADOOP_COMMON_HOME=/usr/local/hadoop
  export HADOOP_MAPRED_HOME=/usr/local/hadoop
  export HIVE_HOME=/usr/local/hive
  export HBASE_HOME=/usr/local/hbase
  export PATH=$PATH:/usr/local/sqoop/bin
• Type the command
  user@ubuntu:~$ exec bash

Install My SQL Server

To perform the demo on hadoop you need to have a database server.

We will install mysql server.

• Type the command:
  user@ubuntu:~$ sudo apt-get install mysql-server
• One mysql server is installed type the command
  user@ubuntu:~$ mysql -u root -p
• To create the database sl type the command
  mysql> create database sl;
• To use the database sl type the command
  mysql> use sl;
• Create a table called authentication that has columns with headings as username and password.
  mysql> create table authentication(usename varchar(30), password varchar(30));
• Insert some data in the table
  mysql> insert into authentication value('admin','12345');
  mysql> insert into authentication value('s1001','s1001');
• Ensure that the data is inserted into the table using the command
  select * from authentication;
• The next step is to download the database driver that will be used by sqoop. This can be done by visiting the site http://www.mysql.com/downloads
• Click on the "Download from MySQL Developer Zone" link under My SQL Community Edition
• You will be taken to "http://dev.mysql.com/downloads/" page. Click the "DOWNLOAD" link under the title "MySQL Connectors".
• You will be taken to "http://dev.mysql.com/downloads/connector/". Click on the "Connector /J" link to download the required distribution. Select platform independent option. Click on the download. Click on "No thanks, just start my download link". A download file info pop-up box appears. Click on start download button. FTP the downloaded file in the folder /usr/local/sqoop/lib.
• Change the ownership of the folder using the command:
  user@ubuntu:~$ sudo chown s1000 /usr/local/sqoop
  user@ubuntu:~$ sudo chown s1000 /usr/local/sqoop/lib
• You have successfully installed and configured sqoop.

Summary: 
Sqoop is a tool to import and trade information between Hadoop and tagged social databases.