What is Apache Sqoop?

Updated: Aug 16, 2021

Sqoop provides a mechanism to connect the external systems like EDW (Enterprise Data Warehouse like Amazon Redshift), Relational Database Management Systems like MySQL, Oracle, MS SQL Server, etc. to transfer data between Hadoop System and the above mentioned external Systems. It efficiently transfers bulk data between Hadoop systems and databases.

Sqoop helps us to have the structured data from RDBMS into HDFS, whereas the other Hadoop eco system components such as MapReduce, HBase, Hive, etc. are dealing with big data and unstructured data for analyzing the data. Sqoop paradigm are categorized into two such as Sqoop Import and Sqoop Export.

Sqoop automates most of this process, relying on the database to describe the schema for the data to be imported. Sqoop uses MapReduce to import and export the data, which provides parallel operation as well as fault tolerance.

The following diagram clearly illustrates how Sqoop connects and works.

Connectors are forced to follow the JDBC model and are required to use common JDBC vocabulary (URL, database, table, etc), regardless if it is applicable.

Sqoop-Import – Purpose:

The import tool imports an individual table from an RDBMS to HDFS. Each row from a table is represented as a separate record in HDFS. Records can be stored as text files (one record per line), or in binary representation as Avro or SequenceFiles.

Connecting to database server:

Sqoop is designed to import tables from a database into HDFS. To do so, you must specify a connect string that describes how to connect to the database. The connect string is similar to a URL, and is communicated to Sqoop with the --connect argument. This describes the server and database to connect to; it may also specify the port. For example

$ sqoop import --connect jdbc:mysql://<db_url>/<db_name>

It’s important that you do not use the URL localhost if you intend to use Sqoop with a distributed Hadoop cluster. The connect string you supply will be used on TaskTracker nodes throughout your MapReduce cluster; if you specify the literal name localhost, each node will connect to a different database (or more likely, no database at all). Instead, you should use the full hostname or IP address of the database host that can be seen by all your remote nodes.

Alternatively, we can authenticate the database before sqoop access it; for example,

$ sqoop import --connect jdbc:mysql://<db_url>/<db_name>

--username <username> --password <password>

You can use Sqoop with any other JDBC-compliant database. First, download the appropriate JDBC driver for the type of database you want to import, and install the .jar file in the $SQOOP_HOME/lib directory on your client machine. For example,

$ sqoop import --driver com.microsoft.jdbc.sqlserver.SQLServerDriver

--connect <connect-string> ...

Importing Table Data into HDFS:

The syntax is used to import tables from database systems to Hadoop file system is as follows :

$ sqoop import (generic-args) (import-args)

$ sqoop-import (generic-args) (import-args)

The following is the command to import data from database to Hadoop file system; in HDFS, the table data will be stored in delimited format by default. We can specify the control arguments as --as-textfile if it has to be stored as text file.

$ sqoop import --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name> --m 1

Suppose if we want to have the table data into a specific directory in HDFS, we need to specify the control arguments as follows:

$ sqoop import --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name>

--target-dir /<target_dir_name>

Filtering records: Sqoop allows us to fetching off subset of table data using --where conditional argument. It is as same as the where clause in a typical SQL. The syntax will be as follows:

$ sqoop import --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name>

--where <condition>

--target-dir /<target_dir_name>

Incremental Imports: Sqoop provides a methodology which can be used to retrieve only rows newer than some previously-imported set of rows. The following arguments are controllingincremental imports:

You should specify append mode when importing a table where new rows are continually being added with increasing row id values. You specify the column containing the row’s id with --check-column. Sqoop imports rows where the check column has a value greater than the one specified with --last-value.

$ sqoop import --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name>

--incremental append

--check-column <col_name>

--last value <col_value>

All Tables Imports: Sqoop provides options to imports all tables data from a database to Hadoop file system; the syntax to import all tables is as follows:

$ sqoop import-all-tables --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

Sqoop-Export – Purpose:

The export tool exports a set of files from HDFS back to an RDBMS. The target table must already exist in the database. The input files are read and parsed into a set of records according to the user-specified delimiters.

The default operation is to transform these into a set of INSERT statements that inject the records into the database. In "update mode," Sqoop will generate UPDATE statements that replace existing records in the database, and in "call mode" Sqoop will make a stored procedure call for each record.

Exporting HDFS Data into Database Tables:

The syntax is used to export into tables from Hadoop file system is as follows :

$ sqoop export (generic-args) (import-args)

$ sqoop-export (generic-args) (import-args)

The following is the command to export data to database

$ sqoop export --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name>

--export-dir <hdfs_dir>

Failed Exports

Exports may fail for a number of reasons:

  • Loss of connectivity from the Hadoop cluster to the database (either due to hardware fault, or server software crashes)

  • Attempting to INSERT a row which violates a consistency constraint (for example, inserting a duplicate primary key value)

  • Attempting to parse an incomplete or malformed record from the HDFS source data

  • Attempting to parse records using incorrect delimiters

  • Capacity issues (such as insufficient RAM or disk space)

If an export map task fails due to these or other reasons, it will cause the export job to fail. The results of a failed export are undefined. Each export map task operates in a separate transaction. Furthermore, individual map tasks commit their current transaction periodically. If a task fails, the current transaction will be rolled back. Any previously-committed transactions will remain durable in the database, leading to a partially-complete export.

Sqoop Job – Purpose:

The job tool allows you to create and work with saved jobs. Saved jobs remember the parameters used to specify a job, so they can be re-executed by invoking the job by its handle.

If a saved job is configured to perform an incremental import, state regarding the most recently imported rows is updated in the saved job to allow the job to continually import only the newest rows.

Syntax: The following is the syntax used to create sqoop job.

$ sqoop job (generic-args) (job-args) [-- [subtool-name] (subtool-args)]

$ sqoop-job (generic-args) (job-args) [-- [subtool-name] (subtool-args)

Create Job: This is the syntax to create sqoop job:

$ sqoop job --create <job_name> -- import --connect jdbc:mysql://<db_host>/<db_name>

--username <<user> --password <password>

--table <table_name>

This creates a job named <job_name> which can be executed later. The job is not run. This job is now available in the list of saved jobs:

List Job Syntax: $ sqoop job –list

We can inspect the configuration of a job with the show action:

Description of a Job: $ sqoop job –show <job_name>

Execution of a job: $ sqoop job –exec <job_name>

Featured Posts
Recent Posts
Search By Tags
No tags yet.
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square