Apache Sqoop Tutorial: Learn Data Transfer from Scratch (2026)
Apache Sqoop is a tool for efficiently transferring bulk data between Hadoop and relational databases. I have used it to import terabytes of data from Oracle and MySQL into HDFS for processing, and to export results back to relational databases for operational reporting. Sqoop generates MapReduce jobs that parallelize the transfer, making it far faster than single-threaded JDBC imports.
This tutorial covers the essential Sqoop operations: parallel import and export, incremental loads, connector management, and handling complex data types like LOBs and timestamps.
Parallel Import: How Sqoop Distributes Work
Sqoop imports data by generating a MapReduce job that reads from the database in parallel. The --split-by parameter specifies which column to use for splitting. Sqoop queries the min and max of the split column, divides the range into N splits, and each mapper reads one split via a WHERE clause. The number of mappers controls parallelism.
Choosing the right split column is critical. Use a numeric primary key or an indexed column for even distribution. String-based split columns can cause skewed distribution if values are not uniformly distributed. I use --boundary-query to let Sqoop optimize min/max queries.
# Basic parallel import:
sqoop import \
--connect jdbc:mysql://db.example.com/sales_db \
--username user --password pass \
--table orders \
--split-by order_id \
--num-mappers 10 \
--target-dir /data/sales/orders
# With boundary query:
sqoop import \
--connect ... --table orders \
--split-by order_id \
--boundary-query "SELECT MIN(order_id), MAX(order_id) FROM orders WHERE status='ACTIVE'""Incremental Import Strategies
Sqoop supports two incremental import modes: append (for tables with monotonically increasing keys) and lastmodified (for tables with timestamp-based updates). The --check-column specifies the column to check, and --last-value specifies the last imported value. Sqoop saves the last imported value in a metadata file for subsequent runs.
I use lastmodified incremental imports for tables with UPDATE timestamps. The merge-key parameter handles updates to existing rows by merging the new data with existing HDFS data. Append mode is simpler but does not handle updates, only inserts.
# Append incremental import:
sqoop import \
--connect ... --table orders \
--incremental append \
--check-column order_id \
--last-value 1000000 \
--target-dir /data/sales/orders
# Lastmodified incremental import with merge:
sqoop import \
--connect ... --table customers \
--incremental lastmodified \
--check-column updated_at \
--last-value "2024-11-01 00:00:00" \
--merge-key customer_id \
--target-dir /data/sales/customersExport: Moving Data from Hadoop to Databases
Sqoop export reads files from HDFS and inserts records into relational database tables. The --export-dir specifies the input, and --table specifies the target. Columns are mapped by name (default) or position (--columns). The --update-key enables upsert behavior for existing rows.
I use the --staging-table pattern for staging exports: write to a staging table first, then use --clear-staging-table and commit to the target. This prevents partial exports from leaving the table in an inconsistent state. The num-mappers parameter controls parallelism but must respect database connection limits.
# Basic export:
sqoop export \
--connect jdbc:mysql://db.example.com/reporting_db \
--username user --password pass \
--table sales_summary \
--export-dir /data/sales/daily_summary \
--num-mappers 4
# Staging export with upsert:
sqoop export \
--connect ... --table sales_summary \
--export-dir /data/sales/daily_summary \
--staging-table sales_summary_staging \
--clear-staging-table \
--update-key report_date \
--update-mode allowinsertConnectors and Driver Configuration
Sqoop uses JDBC connectors for database connectivity. The generic JDBC connector works for any JDBC-compliant database, but specialized connectors (Oracle, MySQL, PostgreSQL, Teradata) provide optimized performance with database-specific features like direct-mode access bypassing JDBC.
The MySQL connector supports --direct mode using mysqldump for faster exports and mysqlimport for faster imports. The Oracle connector supports --direct with SQL*Loader. I use specialized connectors when available — they are typically 2-3x faster than the generic JDBC connector for large transfers.
# MySQL direct-mode import:
sqoop import \
--connect jdbc:mysql://db.example.com/sales_db \
--username user --password pass \
--table orders \
--direct \
--split-by order_id \
--num-mappers 8 \
--target-dir /data/sales/orders
# List available connectors:
sqoop list-databases --connect jdbc:mysql://db.example.com --username user --password passHandling Complex Data Types
Databases contain complex types: LOBs (BLOB, CLOB), geometry types, ARRAYs, and custom types. Sqoop handles LOBs by storing them in separate LOB files referenced from the main data files. The --inline-lob-limit parameter controls the maximum size for inlining LOBs in the text output.
For spatial data, import geometry as WKT (Well-Known Text) using custom SQL queries. TIMESTAMP and DATE columns are imported as strings by default; use --map-column-java to cast to java.sql.Timestamp for finer control. The --as-avrodatafile or --as-parquetfile options preserve type information better than text.
# Import with Avro format (preserves types):
sqoop import \
--connect ... --table orders \
--as-avrodatafile \
--target-dir /data/sales/orders_avro
# Map complex types:
sqoop import \
--connect ... --table documents \
--map-column-java content=String,metadata=String \
--inline-lob-limit 16777216 \
--target-dir /data/documentsFree-Form Query Import and Code Generation
Instead of importing entire tables, Sqoop can import the results of arbitrary SQL queries using --query. The query must include $CONDITIONS placeholder for split-by substitution. Free-form queries enable joins, aggregations, and column filtering at the source — pushing computation to the database where it is most efficient.
Code generation creates a Java class representing the imported table schema, which can be used for serialization and deserialization in MapReduce jobs. The --class-name and --jar-file parameters control the generated code output.
# Free-form query import:
sqoop import \
--connect jdbc:mysql://db.example.com/sales_db \
--username user --password pass \
--query "SELECT o.order_id, o.amount, c.name \
FROM orders o JOIN customers c ON o.customer_id = c.customer_id \
WHERE o.order_date >= '2024-01-01' AND \$CONDITIONS" \
--split-by o.order_id \
--target-dir /data/sales/enriched_orders
# Generate Java class:
sqoop codegen --connect ... --table orders --class-name OrderRecord
# Use generated class:
hadoop jar myapp.jar process -libjars OrderRecord.jarFrequently Asked Questions
What is the difference between Sqoop import and export?
Import moves data from a relational database into HDFS (or Hive, HBase). Export moves data from HDFS back into a relational database. Both use parallel MapReduce jobs.
How does Sqoop determine parallelism?
Sqoop splits the data by the --split-by column range, assigning each range to a mapper. The --num-mappers parameter sets the number of parallel tasks.
Can Sqoop import data directly into Hive?
Yes. Use --hive-import to automatically create a Hive table and load the imported data. Sqoop generates the CREATE TABLE statement matching the source schema.
What happens if a Sqoop job fails mid-transfer?
Sqoop jobs are not transactional across mappers. Some rows may be partially imported. Use --staging-table for exports and idempotent output modes for imports to handle retries.
Originally published on Ayodhyyya. Last updated June 1, 2026.