Apache Sqoop Tutorial: Architecture, Commands & Example

What is SQOOP in Hadoop?

Apache Sqoop (SQL-to-Hadoop) is a tool designed to support bulk export and import of data into HDFS from structured data stores such as relational databases, enterprise data warehouses, and NoSQL systems. It is a data migration tool based upon a connector architecture which supports plugins to provide connectivity to new external systems.

An example use case of Hadoop Sqoop is an enterprise that runs a nightly Sqoop import to load the day’s data from a production transactional RDBMS into a Hive data warehouse for further analysis.

Next in this Apache Sqoop tutorial, we will learn about Apache Sqoop architecture.

Sqoop Architecture

All the existing Database Management Systems are designed with SQL standard in mind. However, each DBMS differs with respect to dialect to some extent. So, this difference poses challenges when it comes to data transfers across the systems. Sqoop Connectors are components which help overcome these challenges.

Data transfer between Sqoop Hadoop and external storage system is made possible with the help of Sqoop’s connectors.

Sqoop has connectors for working with a range of popular relational databases, including MySQL, PostgreSQL, Oracle, SQL Server, and DB2. Each of these connectors knows how to interact with its associated DBMS. There is also a generic JDBC connector for connecting to any database that supports Java’s JDBC protocol. In addition, Sqoop also provides optimized MySQL and PostgreSQL connectors that use database-specific APIs to perform bulk transfers efficiently.

The diagram below places the Sqoop client between the external data store and the Hadoop cluster, with the connector layer on one side and HDFS, Hive and HBase on the other.

Underneath the connector layer, Sqoop generates a Java class that mirrors one row of the table and then submits a map-only MapReduce job. Each map task reads its own slice of the split column range, so throughput scales with the number of mappers rather than with a single JDBC cursor.

In addition to this, Sqoop has various third-party connectors for data stores, ranging from enterprise data warehouses (including Netezza, Teradata, and Oracle) to NoSQL stores (such as Couchbase). However, these connectors do not come with Sqoop bundle; those need to be downloaded separately and can be added easily to an existing Sqoop installation.

Why do we need Sqoop?

Analytical processing using Hadoop requires loading of huge amounts of data from diverse sources into Hadoop clusters. This process of bulk data load into Hadoop, from heterogeneous sources and then processing it, comes with a certain set of challenges. Maintaining and ensuring data consistency and ensuring efficient utilization of resources, are some factors to consider before selecting the right approach for data load.

Major Issues:

1. Data load using scripts — The traditional approach of using scripts to load data is not suitable for bulk data load into Hadoop; this approach is inefficient and very time-consuming.
2. Direct access to external data via a Map-Reduce application — Providing direct access to the data residing at external systems (without loading into Hadoop) for map-reduce applications complicates these applications. So, this approach is not feasible.

In addition to having the ability to work with enormous data, Hadoop can work with data in several different forms. So, to load such heterogeneous data into Hadoop, different tools have been developed. Sqoop and Flume are two such data loading tools.

Next in this Sqoop tutorial with examples, we will learn about the difference between Sqoop, Flume and HDFS.

Sqoop vs Flume vs HDFS in Hadoop

Key Features of Sqoop

The comparison above explains where Sqoop belongs. The feature set below decides whether it fits a specific ingestion job.

• Full load: A single command copies an entire table, and import-all-tables copies every table in a schema in one pass.
• Incremental load: append mode tracks a monotonically increasing column such as a surrogate key, while lastmodified mode tracks a timestamp column, so only new or changed rows travel.
• Parallel transfer: The split column divides the key range across map tasks, and the mapper count sets how many run at once.
• Free-form query import: The result of an arbitrary SQL statement can be imported instead of a whole table, which keeps joins and filters on the database side.
• Direct load into the warehouse: An import can create and populate a Hive table or write straight into an HBase table rather than stopping at raw HDFS files.
• Compression and file formats: Output can be written as delimited text, SequenceFile, Avro or Parquet, with optional codec-level compression.
• Security: Sqoop integrates with Kerberos, and credentials can be read from a password file or prompted for instead of being typed on the command line.

Sqoop Import and Export Commands

Both directions of the transfer run through one command-line utility. The import tool moves rows from the database into Hadoop, and the export tool moves files from Hadoop back into a database table.

A typical import names the JDBC connection, the source table, the destination directory, the split column and the mapper count:

sqoop import \
  --connect jdbc:mysql://localhost:3306/employees \
  --username hadoop \
  --password-file /user/hadoop/.mysql.password \
  --table employees \
  --target-dir /user/hadoop/warehouse/employees \
  --split-by emp_no \
  --num-mappers 4

An export reverses the flow. It reads the delimited files already sitting in an HDFS directory and inserts them into an existing table, so the target table has to be created first:

sqoop export \
  --connect jdbc:mysql://localhost:3306/employees \
  --username hadoop \
  --password-file /user/hadoop/.mysql.password \
  --table employee_summary \
  --export-dir /user/hadoop/warehouse/employee_summary \
  --input-fields-terminated-by ','

A nightly job rarely needs the whole table twice. An incremental import records the highest value it has already seen and starts from there on the next run:

sqoop import \
  --connect jdbc:mysql://localhost:3306/sales \
  --table orders \
  --incremental append \
  --check-column order_id \
  --last-value 15000 \
  --target-dir /user/hadoop/warehouse/orders

These are the arguments that appear in almost every job:

⚠️ Watch out: an export is not a single transaction. Each map task commits its own batch, so a failure halfway through can leave part of the data in the target table. Route the write through a staging table when the load has to be all-or-nothing.

Sqoop Project Status and Modern Alternatives

One version detail matters before any of the commands above go into a scheduler.

The Sqoop developers voted to retire the project in June 2021, and the move to the Apache Attic completed in July 2021. The website, mailing lists, git repository, issue tracker and downloads all remain available, but read-only, and no further releases are planned. The last production release is Sqoop 1.4.7 from 2017; the separate Sqoop 2 line (1.99.x) never reached feature parity and was never declared production-ready.

Existing Sqoop jobs still run, and commercial Hadoop distributions continue to ship and support the tool inside their own platforms. New ingestion work, however, is usually built on one of these instead:

FAQs