tutorial

How to Monitor Apache Sqoop Data Transfer Jobs with Vigilmon

Sqoop job failures silently leave your Hadoop data warehouse stale. Learn how to monitor Sqoop job completion, HDFS landing zones, and transfer pipeline health with Vigilmon heartbeat monitors and HTTP probes.

Apache Sqoop is the bulk data transfer tool that moves data between Hadoop (HDFS, Hive, HBase) and relational databases (MySQL, PostgreSQL, Oracle, SQL Server). Unlike streaming systems, Sqoop jobs are discrete — they run on a schedule, load data into HDFS or Hive, and then exit. When a Sqoop import fails silently (connection timeout, schema mismatch, YARN failure), your data warehouse receives no fresh records and no error fires unless someone checks the logs. By the time analysts notice stale data in their dashboards, the pipeline may have been broken for hours.

Vigilmon gives you visibility into Sqoop pipeline health through heartbeat monitoring (pings after successful job completion) and HTTP probe monitoring for supporting infrastructure like the Sqoop Metastore. This tutorial covers both.

Why Sqoop Needs External Monitoring

Sqoop has no built-in health API or dashboard — it's a command-line tool that exits with code 0 on success and non-zero on failure. Monitoring Sqoop requires wrapping jobs in scripts that report status externally. Vigilmon makes this straightforward:

  • Heartbeat monitoring that fires an alert when a scheduled Sqoop job fails to complete within its expected window — your first signal that an import has broken
  • HDFS landing zone checks that confirm new data appeared in the expected path after each import run
  • HTTP probe monitoring for the Sqoop Metastore, if your team uses it to centrally store saved job configurations
  • Timing-based detection — Vigilmon's heartbeat grace period catches slow jobs that technically complete but run far beyond their expected duration, signaling resource or connectivity problems

Step 1: Wrap Sqoop Jobs in a Monitoring Script

Since Sqoop is a command-line tool, the monitoring strategy is to wrap each Sqoop invocation in a shell script that pings Vigilmon on success and remains silent on failure (missing heartbeat = alert).

Basic Import Wrapper

#!/bin/bash
# sqoop_import_wrapper.sh
# Usage: ./sqoop_import_wrapper.sh <job_name> <heartbeat_url>
set -euo pipefail

JOB_NAME="${1:-sqoop-import}"
HEARTBEAT_URL="${2:-${VIGILMON_HEARTBEAT_URL}}"
LOG_DIR="${SQOOP_LOG_DIR:-/var/log/sqoop}"
TIMESTAMP=$(date '+%Y%m%d_%H%M%S')
LOG_FILE="${LOG_DIR}/${JOB_NAME}_${TIMESTAMP}.log"

mkdir -p "$LOG_DIR"

echo "[$(date)] Starting Sqoop job: ${JOB_NAME}" | tee "$LOG_FILE"

# Run Sqoop import — customize these args for your environment
sqoop import \
  --connect "jdbc:mysql://${DB_HOST}:${DB_PORT}/${DB_NAME}" \
  --username "${DB_USER}" \
  --password-file "${DB_PASS_FILE}" \
  --table "${DB_TABLE}" \
  --target-dir "hdfs://${HDFS_NAMENODE}/data/imports/${DB_TABLE}/$(date +%Y/%m/%d)" \
  --delete-target-dir \
  --num-mappers 4 \
  --compress \
  --compression-codec org.apache.hadoop.io.compress.SnappyCodec \
  2>&1 | tee -a "$LOG_FILE"

EXIT_CODE=${PIPESTATUS[0]}

if [ $EXIT_CODE -eq 0 ]; then
  echo "[$(date)] Sqoop job ${JOB_NAME} succeeded" | tee -a "$LOG_FILE"
  # Ping Vigilmon heartbeat on success only
  curl -sf --max-time 10 "${HEARTBEAT_URL}" > /dev/null || \
    echo "[$(date)] WARNING: heartbeat ping failed (non-fatal)" | tee -a "$LOG_FILE"
else
  echo "[$(date)] Sqoop job ${JOB_NAME} FAILED with exit code ${EXIT_CODE}" | tee -a "$LOG_FILE"
  # Do NOT ping — missing heartbeat triggers the Vigilmon alert
  exit $EXIT_CODE
fi

Sqoop with Hive Import

#!/bin/bash
# sqoop_hive_import.sh
set -euo pipefail

HEARTBEAT_URL="${VIGILMON_HEARTBEAT_URL}"
TABLE="${1:-orders}"
HIVE_DATABASE="${HIVE_DB:-warehouse}"

sqoop import \
  --connect "jdbc:postgresql://${DB_HOST}:5432/${DB_NAME}" \
  --username "${DB_USER}" \
  --password-file /etc/sqoop/db.passwd \
  --table "${TABLE}" \
  --hive-import \
  --hive-overwrite \
  --hive-database "${HIVE_DATABASE}" \
  --hive-table "${TABLE}" \
  --num-mappers 8 \
  --hive-drop-import-delims \
  2>&1

if [ $? -eq 0 ]; then
  curl -sf --max-time 10 "${HEARTBEAT_URL}" > /dev/null || true
  echo "Sqoop Hive import for ${TABLE} complete"
else
  echo "Sqoop Hive import for ${TABLE} FAILED"
  exit 1
fi

Python Job Runner with HDFS Validation

# sqoop_monitor.py
import subprocess
import requests
import os
import logging
from datetime import date

logger = logging.getLogger(__name__)

HEARTBEAT_URL = os.environ['VIGILMON_HEARTBEAT_URL']
HDFS_WEB_URL = os.environ.get('HDFS_WEB_URL', 'http://namenode:9870')
TABLE = os.environ['DB_TABLE']
EXPECTED_PATH = f"/data/imports/{TABLE}/{date.today().strftime('%Y/%m/%d')}"

def verify_hdfs_landing_zone():
    """Confirm Sqoop actually wrote data to HDFS."""
    resp = requests.get(
        f'{HDFS_WEB_URL}/webhdfs/v1{EXPECTED_PATH}',
        params={'op': 'LISTSTATUS'},
        timeout=10
    )
    if resp.status_code != 200:
        return False, f"HDFS path not found: {EXPECTED_PATH}"

    files = resp.json().get('FileStatuses', {}).get('FileStatus', [])
    total_bytes = sum(f['length'] for f in files)
    if total_bytes == 0:
        return False, f"HDFS path exists but contains no data: {EXPECTED_PATH}"

    return True, f"HDFS landing zone OK: {len(files)} files, {total_bytes} bytes"

def run_sqoop_job():
    cmd = [
        'sqoop', 'import',
        '--connect', f"jdbc:mysql://{os.environ['DB_HOST']}:3306/{os.environ['DB_NAME']}",
        '--username', os.environ['DB_USER'],
        '--password-file', '/etc/sqoop/db.passwd',
        '--table', TABLE,
        '--target-dir', f"hdfs://{os.environ['HDFS_NAMENODE']}{EXPECTED_PATH}",
        '--delete-target-dir',
        '--num-mappers', '4',
    ]

    result = subprocess.run(cmd, capture_output=True, text=True)
    if result.returncode != 0:
        logger.error(f"Sqoop failed:\n{result.stderr}")
        return False

    # Verify data landed in HDFS
    ok, message = verify_hdfs_landing_zone()
    if not ok:
        logger.error(f"HDFS validation failed: {message}")
        return False

    logger.info(f"Sqoop import verified: {message}")
    return True

if __name__ == '__main__':
    success = run_sqoop_job()
    if success:
        try:
            requests.get(HEARTBEAT_URL, timeout=10)
            logger.info("Vigilmon heartbeat sent")
        except Exception as e:
            logger.warning(f"Heartbeat ping failed (non-fatal): {e}")
    else:
        logger.error("Sqoop job failed — no heartbeat sent, Vigilmon will alert")

Step 2: Build an HTTP Monitor for the Sqoop Metastore (Optional)

If your team uses the Sqoop Metastore to store saved job configurations, you can expose a health endpoint via a lightweight sidecar.

# sqoop_metastore_health.py
from flask import Flask, jsonify
import subprocess
import os

app = Flask(__name__)

SQOOP_METASTORE_HOST = os.environ.get('SQOOP_METASTORE_HOST', 'localhost')
SQOOP_METASTORE_PORT = os.environ.get('SQOOP_METASTORE_PORT', '16000')

@app.route('/health/sqoop-metastore')
def metastore_health():
    try:
        # Check if metastore port is accepting connections
        import socket
        with socket.create_connection(
            (SQOOP_METASTORE_HOST, int(SQOOP_METASTORE_PORT)), timeout=5
        ):
            pass

        # Optionally list saved jobs to confirm HSQLDB is functional
        result = subprocess.run(
            ['sqoop', 'job', '--meta-connect',
             f'jdbc:hsqldb:hsql://{SQOOP_METASTORE_HOST}:{SQOOP_METASTORE_PORT}/sqoop',
             '--list'],
            capture_output=True, text=True, timeout=15
        )
        if result.returncode != 0:
            return jsonify({
                'status': 'degraded',
                'reason': 'job_list_failed',
                'stderr': result.stderr[:200],
            }), 503

        job_lines = [l for l in result.stdout.splitlines() if l.strip()]
        return jsonify({
            'status': 'ok',
            'saved_job_count': len(job_lines),
        }), 200

    except Exception as e:
        return jsonify({'status': 'down', 'error': str(e)}), 503

if __name__ == '__main__':
    app.run(port=8093)

Configure a Vigilmon HTTP monitor for the metastore sidecar:

  • URL: https://your-sidecar.example.com/health/sqoop-metastore
  • Expected: 200, body contains "status":"ok"
  • Interval: 5 minutes

Step 3: Configure Vigilmon Heartbeat Monitors

Set up one heartbeat monitor per critical Sqoop job or pipeline:

  1. In Vigilmon, go to Monitors → New Monitor → Heartbeat
  2. Name it to match your job: sqoop-orders-daily-import
  3. Set the expected interval slightly above your job's typical run time + cron interval. For a daily job that typically runs in 20 minutes and triggers at 02:00, set 26 hours (25h daily cycle + 1h buffer)
  4. Set the grace period: 2 hours
  5. Save — copy the heartbeat URL and inject it as VIGILMON_HEARTBEAT_URL in your cron job environment

Crontab Example

# Crontab — run Sqoop import daily at 02:00
# Export heartbeat URL so the wrapper script can use it
0 2 * * * VIGILMON_HEARTBEAT_URL=https://vigilmon.online/heartbeat/abc123xyz \
  DB_HOST=mysql.example.com DB_USER=sqoop_reader \
  DB_PASS_FILE=/etc/sqoop/db.passwd DB_TABLE=orders \
  HDFS_NAMENODE=namenode:8020 \
  /opt/sqoop/scripts/sqoop_import_wrapper.sh orders-daily \
  https://vigilmon.online/heartbeat/abc123xyz \
  >> /var/log/sqoop/cron.log 2>&1

Oozie-Triggered Sqoop Job

If Sqoop runs inside an Oozie workflow, the heartbeat call goes in your Oozie Shell action (see the Oozie tutorial for workflow.xml structure), passing VIGILMON_HEARTBEAT_URL as a workflow property.

Step 4: Alert Routing for Sqoop Failures

| Monitor | Alert Channel | Priority | |---|---|---| | Heartbeat: critical daily imports | Slack + PagerDuty | P1 | | Heartbeat: secondary imports | Slack + email | P2 | | HTTP monitor: Sqoop Metastore | Slack | P3 |

For SLA-sensitive pipelines, tune the heartbeat grace period tightly. A nightly import that feeds a morning dashboard should alert by 04:00 if the 02:00 job hasn't pinged — giving on-call engineers time to investigate and rerun before business hours.

For high-volume tables, set a longer expected interval to avoid false positives on days when row counts spike (quarterly data loads, historical backfills). Consider two heartbeat monitors: one for the normal schedule and a separate one with a wider window for known heavy-load windows.

Summary

Sqoop job failures are silent by default — no running process, no dashboard, no alert. External heartbeat monitoring fills this gap by treating every successful job completion as a positive signal and alerting on its absence:

| Monitor Type | What It Covers | |---|---| | Heartbeat monitor (per job) | Job completion, YARN execution, HDFS data landing | | HTTP monitor on Sqoop Metastore | Saved job configuration availability | | HDFS validation in job script | Data actually written (not just exit code 0) |

Get started free at vigilmon.online — your first Sqoop heartbeat monitor is running in under two minutes.

Monitor your app with Vigilmon

Free plan — 5 monitors, no credit card required. Up and running in 60 seconds.

Start free →
← Back to all articles