Apache Sqoop with Microsoft SQL Server, Updated Guide

This guide updates an older 2011 post on using Microsoft’s SQL Server Hadoop Connector with Sqoop. It explains how the legacy connector worked, how to configure Sqoop with the current Microsoft JDBC driver for SQL Server, and how to tune Sqoop exports with mappers, JDBC batching, rows per statement and statements per transaction. It also shows how to generate and reuse a precompiled export job JAR, and what to consider now that Apache Sqoop is retired and modern data stacks often use other ingestion tools.

---

Back in 2011, Microsoft shipped a dedicated SQL Server-Hadoop Connector that plugged into Apache Sqoop as an additional connector package. It automated import and export between SQL Server, HDFS, and Hive, and required a separate download plus the Microsoft JDBC driver.

Today, the situation is different:

• Apache Sqoop is retired and has been moved to the Apache Attic (June 2021). It still works but is no longer actively developed or recommended for new projects.
• The original SQL Server-Hadoop Connector tarball is effectively legacy; most distributions simply use Sqoop’s generic JDBC support plus the Microsoft JDBC driver.
• Modern stacks often use Kafka Connect, Spark-based ingestion, or managed services (e.g. Azure Data Factory, HDInsight with Sqoop) to move data between SQL Server and data lakes.

However, many clusters still run Sqoop, and a lot of people land here from search. So this article keeps the core idea, Sqoop <=> SQL Server, but refreshes the details and points out what is legacy versus current practice.

1. Legacy SQL Server-Hadoop Connector (Historical Context)

Originally, Microsoft provided a separate package named something like sqoop-sqlserver-1.0.tar.gz. It contained:

• install.sh, installer that dropped the connector jar and config into your Sqoop installation
• lib/, Sqoop connector jar
• conf/, connector configuration
• PDF user guide and license files

The setup looked roughly like this:

# 1. Install Microsoft JDBC driver
#   (e.g. sqljdbc4.jar) into $SQOOP_HOME/lib

# 2. Unpack the connector
tar -zxvf sqoop-sqlserver-1.0.tar.gz
cd sqoop-sqlserver-1.0

# 3. Set MSSQL_CONNECTOR_HOME and run installer
export MSSQL_CONNECTOR_HOME=/usr/sqoop/connector/mssql
sh ./install.sh

On modern systems, you will rarely use this legacy connector. Sqoop’s generic JDBC support plus the SQL Server JDBC driver is enough for most use cases; vendor distributions that still ship Sqoop often embed any required SQL Server integration out of the box.

2. Modern Setup with Sqoop + Microsoft JDBC Driver

If you still run Sqoop and want to integrate with SQL Server, the minimal and current approach is:

1. Install Sqoop (usually as part of a Hadoop distribution or HDP/CDH/CDP stack).
2. Download the latest Microsoft JDBC driver for SQL Server.
3. Copy the appropriate driver jar (for Java 8/11, etc.) into $SQOOP_HOME/lib.

Example (simplified):

# Download and unpack JDBC driver (version/name will vary)
tar -zxvf sqljdbc_<version>_enu.tar.gz
# Current drivers ship as mssql-jdbc-<version>.jre8.jar / .jre11.jar and so on;
# pick the jar that matches your Java version
cp sqljdbc_<version>/enu/jars/mssql-jdbc-<version>.jre11.jar $SQOOP_HOME/lib

# Verify Sqoop sees the driver
sqoop list-databases \
  --connect "jdbc:sqlserver://<HOST>:1433;database=<DB>" \
  --username dbuser \
  --password dbpasswd

At this point, Sqoop uses the Microsoft JDBC driver via the standard connector mechanism; no extra Microsoft “Sqoop adapter” is strictly required.

Note that driver 10.2 and later sets encrypt=true by default. The SQL Server certificate must be trusted by the JVM, or for a quick test you can add ;trustServerCertificate=true to the connection string.

3. Importing from SQL Server to HDFS with Sqoop

The original article showed a simple example that split work across three mappers. A modernized version looks like this:

sqoop import \
  --connect "jdbc:sqlserver://<HOST>:1433;database=<DB>" \
  --username dbuser \
  --password dbpasswd \
  --table <TABLE> \
  --target-dir /path/to/hdfs/dir \
  --split-by <PRIMARY_KEY_COLUMN> \
  -m 3

Notes:

• split-by should be a numeric, evenly distributed column (typically the primary key).
• The number of mappers -m should match available cluster capacity and database limits.
• For large tables, consider incremental imports (--incremental, --check-column, --last-value).

The original PoC reported roughly one minute for ~1.3 GB over three mappers. Real-world performance depends heavily on network bandwidth, database tuning, transaction isolation, and how busy the cluster is.

4. Exporting from HDFS back to SQL Server

The reverse direction, writing processed data back to SQL Server, uses sqoop export:

sqoop export \
  --connect "jdbc:sqlserver://<HOST>:1433;database=<DB>" \
  --username dbuser \
  --password dbpasswd \
  --table <TARGET_TABLE> \
  --export-dir /path/from/hdfs/dir \
  --batch

The original example used --direct, which leveraged SQL Server bulk APIs through the connector. On some modern distributions and driver combinations this is no longer supported or behaves differently. When in doubt:

• Start with standard JDBC-based export (no --direct).
• Use --batch for better throughput, but monitor transaction logs and locking.
• Make sure HDFS files and the table schema (columns, types, nullability) align closely.

5. Tuning Sqoop Exports

Once you export more than a few thousand rows, tuning matters. These settings work for any JDBC target, SQL Server included. Raise them step by step and watch the database, not just the Sqoop job.

Parallelism with --num-mappers

Each mapper opens its own JDBC connection and writes a slice of the data. More mappers means more throughput, but also more pressure on the database. Check the connection limits and transaction log capacity before you raise it.

JDBC batch mode with --batch

--batch lets the JDBC driver group INSERTs into batched executions. That cuts network round trips and helps most on high-latency connections. It can use more memory on the driver side, so keep an eye on large rows.

Rows per statement with sqoop.export.records.per.statement

This sets how many rows go into one INSERT:

INSERT INTO table VALUES (...), (...), (...);

Bigger values help only if the target handles multi-row INSERTs well. Oracle, for example, can behave differently than MySQL or Postgres.

Statements per transaction with sqoop.export.statements.per.transaction

This sets how many INSERT statements go into one transaction:

BEGIN;
INSERT ... ;
INSERT ... ;
...
COMMIT;

Large transactions cut commit overhead but cost more on rollback. Small ones reduce log pressure but can slow the export. Pick a value that fits the transaction log of your database.

Putting it together with -D

You set both properties with Hadoop-style overrides, and you can combine them with batching:

sqoop export \
  -Dsqoop.export.records.per.statement=500 \
  -Dsqoop.export.statements.per.transaction=50 \
  --batch \
  --num-mappers 4 \
  --connect jdbc:... \
  --table target_table \
  --export-dir /path/to/data

6. Faster Exports with a Precompiled Job JAR

A lesser-known Sqoop feature: it can write out the job code it generates for an export as a standalone JAR. You can then reuse that JAR instead of letting Sqoop generate and compile code on every run.

Run the export once with --package-name, --outdir and --bindir:

sqoop export \
  --connect jdbc:RDBMS:thin:@HOSTNAME:PORT:DBNAME \
  --table TABLENAME \
  --username USERNAME \
  --password PASSWORD \
  --export-dir HDFS_DIR \
  --direct \
  --fields-terminated-by ',' \
  --package-name JOBNAME.IDENTIFIER \
  --outdir OUTPUT_DIR \
  --bindir BIN_DIR

A JAR shows up in the output directory. Unpack it to look at the generated Java source, the compiled classes and the record handling and mapper logic. Then point later runs at it:

sqoop export \
  --connect jdbc:RDBMS:thin:@HOSTNAME:PORT:DBNAME \
  --table TABLENAME \
  --username USERNAME \
  --password PASSWORD \
  --export-dir HDFS_DIR \
  --direct \
  --fields-terminated-by ',' \
  --jar-file PATH/TO/JAR \
  --class-name JOBNAME.IDENTIFIER.CLASSNAME

This skips the on-the-fly compilation and gives you a place to optimize the generated code. In one case, exporting one hundred thousand records went from sixteen seconds to eight. It also makes debugging easier, and it helps when you migrate the job to another ingestion tool because you can see exactly what Sqoop did.

7. Security and Connectivity Considerations

For anything beyond quick PoCs:

• Use encrypted connections to SQL Server (JDBC connection string with SSL/TLS options).
• Consider Windows/AD authentication via Kerberos if supported by your environment and driver.
• Lock down Sqoop hosts so the JDBC URL, user, and password are not world-readable (no plain text passwords in shared shell histories).
• Use dedicated service logins with restricted privileges instead of full dbo access.

8. Where This Fits in Modern Architectures

Apache Sqoop’s mission was to be the bulk data transfer tool between Hadoop and relational databases. That mission is effectively complete: Sqoop is now in the Apache Attic and no longer actively developed.

In modern lakehouse and cloud-native architectures, you will see more of:

• Debezium (SQL Server CDC) for change data capture, and the Kafka Connect JDBC connector for query-based polling.
• Spark / Flink connectors for SQL Server and other RDBMS.
• Azure Data Factory, Synapse pipelines, HDInsight Sqoop wrappers for managed bulk transfers with monitoring and orchestration.

However, many on-premise or long-lived Hadoop clusters still rely on Sqoop jobs. For those environments, the pattern in this article remains a pragmatic way to move data between SQL Server and HDFS, as long as you understand that you are building on top of a retired but still functional tool.

Summary

The original 2011 post focused on Microsoft’s SQL Server-Hadoop Connector and showed how to move data between SQL Server and HDFS via Sqoop. This refreshed version:

• Explains that Sqoop is now retired and the Microsoft connector is legacy.
• Shows how to integrate Sqoop with modern SQL Server JDBC drivers.
• Updates import/export examples and flags (--batch, careful use of --direct).
• Shows how to tune exports and reuse a precompiled job JAR.
• Places the pattern in context of modern ingestion options.

If you are maintaining long-lived Hadoop clusters, this pattern is still useful. For completely new architectures, you should also evaluate more modern, managed, and streaming-friendly ingestion options.