如何用Shell脚本对比Oracle与Hive表列信息并输出不匹配项

Hey Sujit, no worries—let’s build a shell script that does exactly what you need: compare every column detail (name, data type, size, precision, scale) between an Oracle table and a Hive table, then spit out any mismatches. Here’s a step-by-step solution:

Step 1: Extract Oracle Table Metadata #

First, we’ll pull column details from Oracle using sqlplus. The query fetches column name, data type, length, precision, and scale—all the critical details you need.

#!/bin/bash

# Configure your Oracle credentials and table info
ORACLE_USER="your_oracle_username"
ORACLE_PWD="your_oracle_password"
ORACLE_DB="your_oracle_tns_entry"
ORACLE_TABLE="your_schema.your_oracle_table"

# Extract metadata to a temp file
echo "Pulling Oracle table metadata..."
sqlplus -S ${ORACLE_USER}/${ORACLE_PWD}@${ORACLE_DB} << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 1000
SELECT 
  LOWER(COLUMN_NAME) || ',' || 
  LOWER(DATA_TYPE) || ',' || 
  NVL(DATA_LENGTH, 'NA') || ',' || 
  NVL(DATA_PRECISION, 'NA') || ',' || 
  NVL(DATA_SCALE, 'NA')
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = UPPER('${ORACLE_TABLE##*.}') 
  AND OWNER = UPPER('${ORACLE_TABLE%.*}')
ORDER BY COLUMN_ID;
EXIT;
EOF > oracle_cols.txt

Step 2: Extract Hive Table Metadata #

Next, we’ll get matching details from Hive using beeline (more reliable than the old Hive CLI). We’ll parse the DESCRIBE FORMATTED output to extract the same column attributes.

# Configure your Hive credentials and table info
HIVE_SERVER="your_hive_server:10000"
HIVE_DB="your_hive_database"
HIVE_TABLE="your_hive_table"
HIVE_USER="your_hive_username"
HIVE_PWD="your_hive_password"

# Extract metadata to a temp file
echo "Pulling Hive table metadata..."
beeline -u jdbc:hive2://${HIVE_SERVER}/${HIVE_DB} -n ${HIVE_USER} -p ${HIVE_PWD} -e "
DESCRIBE FORMATTED ${HIVE_TABLE};
" | grep -E "^[a-zA-Z0-9_]+" | awk '
{
  # Handle decimal types (extract precision/scale)
  if ($2 ~ /decimal\((.*),(.*)\)/) {
    split($2, arr, /[()]/);
    split(arr[2], prec_scale, /,/);
    type = "decimal";
    prec = prec_scale[1];
    scale = prec_scale[2];
  } else {
    type = tolower($2);
    prec = "NA";
    scale = "NA";
  }
  # Hive doesn’t track "data length" like Oracle, so use NA
  print tolower($1) "," type ",NA," prec "," scale;
}
' > hive_cols.txt

Step 3: Compare Metadata & Flag Mismatches #

Now we’ll use diff to compare the two temp files, then format the output to highlight mismatches clearly.

echo "Comparing column details..."
echo "====================================="
echo "Mismatched Columns (Oracle vs Hive):"
echo "====================================="

# Use diff to find differences, then format the output
diff --side-by-side --suppress-common-lines oracle_cols.txt hive_cols.txt | while read line; do
  oracle_col=$(echo $line | awk -F'|' '{print $1}' | xargs)
  hive_col=$(echo $line | awk -F'|' '{print $2}' | xargs)
  
  # Split into individual attributes for readability
  IFS=',' read ora_name ora_type ora_len ora_prec ora_scale <<< "$oracle_col"
  IFS=',' read hive_name hive_type hive_len hive_prec hive_scale <<< "$hive_col"
  
  echo "Column Name: $ora_name"
  echo "  Oracle: Type=$ora_type, Length=$ora_len, Precision=$ora_prec, Scale=$ora_scale"
  echo "  Hive:   Type=$hive_type, Length=$hive_len, Precision=$hive_prec, Scale=$hive_scale"
  echo "-------------------------------------"
done

# Clean up temp files
rm oracle_cols.txt hive_cols.txt

Key Notes & Optimizations #

• Type Mapping: Oracle and Hive use different type names (e.g., VARCHAR2 vs STRING, NUMBER vs INT/DECIMAL). You can add a case statement in the script to normalize types before comparison.
• Case Sensitivity: We converted all column names/types to lowercase to avoid mismatches from case differences.
• Dependencies: Make sure sqlplus (for Oracle) and beeline (for Hive) are installed and in your system’s PATH.
• Security: For production, avoid hardcoding credentials—use environment variables or secure vaults instead.

内容的提问来源于stack exchange，提问作者Abhishek Jain