Importing large datasets from relational databases into Hive can be resource-intensive and time-consuming. For production-grade data pipelines, it’s essential to optimize imports using incremental loads, so that only new or updated records are ingested.

Apache Sqoop makes this possible with its built-in incremental import modes, allowing you to transfer only the delta changes to Hive. This approach significantly reduces I/O, speeds up ETL jobs, and minimizes the strain on source systems.

In this blog, we explore how to use Apache Sqoop to implement incremental loads into Hive, understand the two modes (append and lastmodified), and share best practices to ensure accurate and efficient imports.

What is Apache Sqoop?

Apache Sqoop is a tool designed to efficiently transfer bulk data between relational databases and Hadoop ecosystems. It supports imports into HDFS, Hive, and HBase, and works with MySQL, PostgreSQL, Oracle, SQL Server, and others.

Key features:

  • Parallel data import/export
  • Incremental import modes
  • Direct Hive integration
  • Compression support
  • Custom SQL queries for flexibility

Why Use Incremental Loads?

Full data imports are:

  • Time-consuming for large tables
  • Redundant for unchanged records
  • Costly in terms of compute and storage

Incremental loads solve this by importing only:

  • New rows based on an incremental ID
  • Changed rows based on a last-modified timestamp

This is ideal for nightly batch updates, CDC (change data capture), and data lake synchronization.

Incremental Load Modes in Sqoop

Sqoop supports two incremental modes:

  1. Append Mode: Imports rows where a specified check column value is greater than the last imported value. Ideal for monotonically increasing IDs.

  2. LastModified Mode: Imports rows where a timestamp column is more recent than the last imported time. Useful for tables with updates.

Append Mode Example (New Rows)

Assume you have a MySQL table orders with an auto-incrementing order_id.

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
total_amount DOUBLE,
created_at TIMESTAMP
);

To import only new records since the last run:

sqoop import \
--connect jdbc:mysql://dbhost/sales \
--username dbuser --password dbpass \
--table orders \
--incremental append \
--check-column order_id \
--last-value 1000 \
--target-dir /user/hive/warehouse/orders_incremental \
--merge-key order_id \
--hive-import --hive-table orders

Sqoop will import only rows where order_id > 1000.

LastModified Mode Example (Updated Rows)

If the source table has a column like updated_at:

ALTER TABLE orders ADD COLUMN updated_at TIMESTAMP;

Use lastmodified mode to import recently changed rows:

sqoop import \
--connect jdbc:mysql://dbhost/sales \
--username dbuser --password dbpass \
--table orders \
--incremental lastmodified \
--check-column updated_at \
--last-value "2024-11-15 00:00:00" \
--target-dir /user/hive/warehouse/orders_delta \
--hive-import --hive-table orders_staging

Then use Hive to MERGE or INSERT OVERWRITE data into the target table.

Automating the Last Value

To persist the last-value, use the --incremental import with the --last-value dynamically read from a tracking file or metadata table.

Use --outdir and --bindir to store job metadata, or save the last value in HDFS or a database for reuse in your next job.

Example using a shell script:

LAST_VAL=$(cat last_val.txt)

sqoop import ... --last-value "$LAST_VAL" ...

# Save the new max value
hive -e "SELECT MAX(order_id) FROM orders" > last_val.txt

Merging Incremental Loads in Hive

If the table supports UPSERTs (update/insert), use Hive’s MERGE INTO statement (Hive 3.0+):

MERGE INTO orders o
USING orders_staging s
ON o.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET o.total_amount = s.total_amount
WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.customer_id, s.total_amount);

For older versions, use INSERT OVERWRITE or partition pruning to replace partitions with delta loads.

Best Practices

  • Use append mode when data only grows (e.g., logs, transactions)
  • Use lastmodified for mutable datasets with updates
  • Ensure the check column is indexed on the source DB
  • Store last-value securely and track it consistently
  • Validate import counts against source system for accuracy
  • Use compression and Parquet/ORC for efficient Hive storage
  • Combine with Oozie or Airflow for scheduled incremental ingestion

Common Pitfalls

  • Duplicates from re-importing the same data due to wrong last-value
  • Data gaps if last-value skips valid records (use inclusive logic)
  • Nulls in check-column preventing rows from being picked up
  • Timezone mismatches in lastmodified mode

Always test queries with edge cases and monitor counts.

Conclusion

Apache Sqoop and Hive together provide a powerful mechanism for incremental data loading from RDBMS into big data lakes. By using append or lastmodified modes effectively, you can build reliable, cost-efficient ETL pipelines that scale.

Adopting incremental strategies reduces redundancy, improves performance, and aligns with modern data engineering best practices — all while keeping your Hive tables up to date with minimal effort.