Introduction to Sqoop Part 1

Introduction

To use Hadoop for analytics, It is required to load the data into Hadoop clusters. Then later we can use this data for processing it  using traditional processing tool (e.g Map-Reduce/Hive/Pig).

Sqoop is  used to Import data from RDBMS system to Hadoop distributed File system (HDFS). And for Exporting data from HDFS back to RDBMS, Sqoop is used. Loading GBs and TBs of data into HDFS from production databases or accessing it from map-reduce applications is a challenging task. While doing so, we have to consider things like data consistency, overhead of running these jobs on production systems and at the end if this process would be efficient or not. Using batch scripts to load data is an inefficient way to go with.

Sqoop (“SQL-to-Hadoop”) is a straightforward command-line tool with the following capabilities:
  • Imports individual tables or entire databases to files in HDFS
  • Generates Java classes to allow you to interact with your imported data
  • Provides the ability to import from SQL databases straight into your Hive data warehouse

Sqoop Connectors

Sqoop has an extension framework that makes it possible to import data from—and export data to—any external storage system that has bulk data transfer capabilities. A Sqoop connector is a modular component that uses this framework to enable Sqoop imports and exports. Sqoop ships with connectors for working with a range of popular databases, including MySQL, PostgreSQL, Oracle, SQL Server, DB2, and Netezza. There is also a generic JDBC connector for connecting to any database that supports Java’s JDBC protocol. Sqoop provides optimized MySQL, PostgreSQL, Oracle, and Netezza connectors that use database-specific APIs to perform bulk transfers more efficiently.

MySQL Example -
generic sqoop command -
sqoop import --connect jdbc:mysql://localhost/db --table TABLENAME

Lets say we have a MySQL database "website" and inside that database we have a table USERS and we want to improt that data on hadoop. Below is the Creat table statement for USERS -

CREATE TABLE USERS (
 user_id INTEGER NOT NULL PRIMARY KEY,
 name VARCHAR(128) NOT NULL,
 state CHAR(2),
 email VARCHAR(128),
 password CHAR(64)
);
 

Importing this table into HDFS could be done with the command

sqoop import --connect jdbc:mysql://localhost/website --table USERS -m 1

Sqoop’s import tool will run a MapReduce job that connects to the MySQL database and reads the table. By default, this will use four map tasks in parallel to speed up the import process. Each task will write its imported results to a different file, but all in a
common directory. If you want to overwirte number of mapper to be run we can do that by specifying -m option. In above example we have mentioned number of mapper as 1 (-m 1).
By default, Sqoop will generate comma-delimited text files for our imported data. if you want to change the delimiter you can use --fields-terminated-by while importing.

sqoop --connect jdbc:mysql://localhost/website --table USERS --local --hive-import

The –-local option instructs Sqoop to take advantage of a local MySQL connection which performs very well. The –-hive-import option means that after reading the data into HDFS, Sqoop will connect to the Hive meta-store, create a table named USERS with the same columns and types (translated into their closest analogues in Hive), and load the data into the Hive warehouse directory on HDFS (instead of a sub directory of your HDFS home directory).

If MySQL is not installed on the same node but on some other node then instead of localhost you need to mention the ip address in the above command.

Different File Formats For Sqoop -

Sqoop is capable of importing into a few different file formats. Text files (the default) offer a human-readable representation of data, platform independence, and the simplest structure. However, they cannot hold binary fields (such as database columns of type VARBINARY).
Sqoop also supports SequenceFiles, Avro datafiles, and  Parquet files. These binary formats provide the most precise representation possible of
the imported data. They also allow data to be compressed while retaining MapReduce’s ability to process different sections of the same file in parallel.

Suppose you wanted to work with this data in MapReduce and weren’t concerned with Hive. When storing this table in HDFS, you might want to take advantage of compression, so you’d like to be able to store the data in SequenceFiles. In this case, you might want to import the data with the command:
sqoop --connect jdbc:mysql://localhost/web
site --table USERS --as-sequencefile



Comments

Post a Comment

Popular posts from this blog

Hadoop calculate maximum temperature explained

Image
Analyzing the Data with Hadoop Using Map Reduce  To take advantage of the parallel processing that Hadoop provides, we need to express our query as a MapReduce job because MapReduce framwork will manage the parallel processing by it self. MapReduce divides the processing into 2 phases - the map phase  the reduce phase  Each phase has input in the form of key value pair, and both phases produces output as key value pair. The output that has been generated by the map phase is given to a reduce phase as an input. It is the programmers responsibility to specifies two functions: the map function  the reduce function Lets take an example where input to the map phase is from the below link which has the NCDC data- https://raw.githubusercontent.com/lmsamarawickrama/Hadoop-MapReduce/master/NCDC%20weather%20files/1901 Using the above data we need to calculate maximum temperature per year. While writing a mapreudce code, We choose a text input...
Read more

Sqoop In Depth

Image
In earlier post we have seen  need of Sqqop ? what are the Sqoop commands ? And what are the file formats Sqoop supports. Now lets dive in into the functional aspect of Sqoop like How does Sqoop imports/exports data from/to RDBMS.. Sqoop Import Sqoop’s import tool will run a MapReduce job that connects to the MySQL database and reads the table. By default, this will use four map tasks in parallel.Each task will write its imported results to a different file.If a distributed Hadoop cluster is being used, localhost (in jdbc:mysql://localhost/dbname )should not be specified in the connect string, because map tasks not running on the same machine as the database will fail to connect. For example we have 3 nodes (machines) in a cluster with ip 192.168.0.1, 192.168.0.2, 192.168.0.3 (consider these ip addresses just for example purpose). Lets assume MySql is installed on machine 192.168.0.2 .After running sqoop import with connection string as " jdbc:mysql://localhost/dbname...
Read more