如何编写Sqoop命令导入Oracle近3天增量数据到HDFS？

Got it, let's figure out how to add the 3-day incremental import logic to your existing generic Sqoop shell script. I'll break this down step by step so it fits seamlessly with your multi-Oracle, multi-factory setup:

First, choose the mode that matches your Oracle table's data pattern:

• Append Mode: Use this if your table only gets new rows (no updates to existing records). Sqoop imports rows newer than the last imported entry based on your check column.
• Lastmodified Mode: Opt for this if rows can be updated. Sqoop will pull both new rows and any rows modified in the last 3 days.

For most real-world scenarios with updateable data, lastmodified is the better choice.

Oracle has built-in date functions to easily target the 3-day window. Use one of these in your Sqoop --where clause:

• Basic server time filter: your_update_column >= SYSDATE - INTERVAL '3' DAY
• UTC-aligned filter (avoids timezone mismatches between Oracle and Hadoop): your_update_column >= SYS_EXTRACT_UTC(SYSDATE) - INTERVAL '3' DAY

Replace your_update_column with the actual timestamp column in your table (e.g., last_updated, create_time).

Here's a parameterized Sqoop command that fits your multi-factory/multi-database script. All variables can be pulled from your existing config or passed dynamically:

# Define reusable variables (adjust these to match your script's setup)
ORACLE_HOST="db-host-01"
ORACLE_PORT="1521"
ORACLE_SID="ORCL"
ORACLE_USER="factory_user"
ORACLE_PASS="secure_pass" # Use --password-file in production for security
FACTORY_ID="factory_001"
TABLE_NAME="production_data"
UPDATE_COLUMN="last_updated"
SPLIT_COLUMN="record_id" # Use a numeric, evenly distributed column for parallelism
HDFS_BASE_PATH="/user/hadoop/incremental/${FACTORY_ID}"

# Calculate 3 days ago timestamp (matches Oracle's date format for --last-value)
LAST_VALUE=$(date -d '3 days ago' +'%Y-%m-%d %H:%M:%S')

# Sqoop incremental import command
sqoop import \
  --connect jdbc:oracle:thin:@${ORACLE_HOST}:${ORACLE_PORT}:${ORACLE_SID} \
  --username ${ORACLE_USER} \
  --password ${ORACLE_PASS} \
  --table ${TABLE_NAME} \
  --where "${UPDATE_COLUMN} >= SYSDATE - INTERVAL '3' DAY" \
  --target-dir ${HDFS_BASE_PATH}/${TABLE_NAME}/$(date +%Y%m%d) \
  --incremental lastmodified \
  --check-column ${UPDATE_COLUMN} \
  --last-value "${LAST_VALUE}" \
  --split-by ${SPLIT_COLUMN} \
  --num-mappers 4 # Tune based on your cluster and Oracle capacity

Key Parameter Explanations: #

• --target-dir: Uses a date-based subdirectory to organize incremental loads (easy to track and clean up old data)
• --check-column: The timestamp column Sqoop uses to identify incremental records
• --last-value: Tells Sqoop the oldest timestamp to include (we calculate this as 3 days ago)
• --split-by: Ensures parallel mappers work evenly across your data

To scale this to multiple factories and databases, use a config file to store all factory-database pairs, then loop through it in your script:

Example Config File (factory_db_configs.txt): #

factory_001,db-host-01,1521,ORCL,user1,pass1,prod_table,last_updated,record_id
factory_002,db-host-02,1521,ORCL2,user2,pass2,prod_table2,update_time,order_id

Updated Shell Script Loop: #

CONFIG_FILE="./factory_db_configs.txt"
HDFS_ROOT="/user/hadoop/incremental"

while IFS=, read -r FACTORY_ID ORACLE_HOST ORACLE_PORT ORACLE_SID ORACLE_USER ORACLE_PASS TABLE_NAME UPDATE_COLUMN SPLIT_COLUMN
do
  # Skip comment lines in config
  [[ $FACTORY_ID == "#"* ]] && continue

  LAST_VALUE=$(date -d '3 days ago' +'%Y-%m-%d %H:%M:%S')
  HDFS_PATH="${HDFS_ROOT}/${FACTORY_ID}/${TABLE_NAME}/$(date +%Y%m%d)"

  sqoop import \
    --connect jdbc:oracle:thin:@${ORACLE_HOST}:${ORACLE_PORT}:${ORACLE_SID} \
    --username ${ORACLE_USER} \
    --password ${ORACLE_PASS} \
    --table ${TABLE_NAME} \
    --where "${UPDATE_COLUMN} >= SYSDATE - INTERVAL '3' DAY" \
    --target-dir ${HDFS_PATH} \
    --incremental lastmodified \
    --check-column ${UPDATE_COLUMN} \
    --last-value "${LAST_VALUE}" \
    --split-by ${SPLIT_COLUMN} \
    --num-mappers 3
done < "${CONFIG_FILE}"

• Password Security: Replace hardcoded passwords with --password-file hdfs:///path/to/secure/password/file to avoid exposing credentials.
• Time Zone Consistency: Use UTC for both Oracle and Hadoop timestamps to prevent missing data or duplicates caused by timezone offsets.
• Validation: After each import, run a count comparison:
  • Oracle: SELECT COUNT(*) FROM ${TABLE_NAME} WHERE ${UPDATE_COLUMN} >= SYSDATE - INTERVAL '3' DAY;
  • HDFS: hdfs dfs -count ${HDFS_PATH} | awk '{print $2}'
• Mapper Tuning: Adjust --num-mappers based on your cluster's resources—too many can overwhelm Oracle, too few will slow imports.

内容的提问来源于stack exchange，提问作者Gyanaraj Das