oracle数据导入hive及自动处理type错误

1、oracle2hive

#!/bin/sh
user='skoanasqp'
passwd='temp0628'
db=`echo $user|sed 's/sqp//'`
jdbc='100.73.57.56:1534'
sqlplus -S "$user/$passwd@100.73.57.56:1534/$db" >./tables.txt <<EOF
set echo off term off head off pages 0 feedback off
select distinct owner||'.'||table_name from all_tables where owner not in ('SYS', 'SYSTEM', 'WMSYS');
exit;
EOF

split -l 30 ./tables.txt -d -a 2 tab-
if [ ! -d "./$user-log" ]; then
    mkdir ./$user-log
fi
function sqoopOracle2hive()
{
    schema=`echo $1|awk -F '.' '{print $1}'`
    otab=`echo $1|awk -F '.' '{print $2}'`
    if [[ $otab =~ '$' ]]; then htab=$(echo $otab|sed  s/\$//g);else htab=$otab ;fi
    echo "$schema----$otab---$htab">>tinfo.log
    su - hdfs -c " \
        sqoop import --connect \"jdbc:oracle:thin:@$jdbc/$db \" --username $user --password $passwd \
        --table $schema.$otab \
        --hive-import \
        --fields-terminated-by '\001' \
        --hive-drop-import-delims \
        --delete-target-dir \
        --hive-database $db \
        --hive-table $schema\_$htab \
        --fetch-size 200 \
        -m 1 " >$user-log/$schema-$htab.log 2>&1
}

for f in `ls -l|awk '{print $9}'|grep "^tab-"`
do
  for tab in `cat $f`
   do
   (sqoopOracle2hive $tab)&
   done
   wait;
done
rm -rf ./tab*

2、自动处理类型错误

#!/bin/bash
grep -i 'exception' logs/*|grep "Hive does not support the SQL type"|awk '{print $1":"$NF}'|sed 's/\-/:/'|sed 's/\./:/' >erroinfo.log
user='hkoasqp'
passwd='temp0628'
db=`echo $user|sed 's/sqp//'`
jdbc='100.73.57.56:1534'

if [ ! -d "./erro" ]; then
    mkdir ./erro
fi
function sqoopOracle2hive()
{
    schema=`echo $1|awk '{print $1}'`
    otab=`echo $1|awk '{print $2}'`
    column=`echo $1|awk '{print $NF}'`
    echo "---------------begin sqoop-------------- "
    echo "sqoop $schema--$otab--$column"
    if [[ $otab =~ '$' ]]; then htab=$(echo $otab|sed  s/\$//g);else htab=$otab ;fi
    su - hdfs -c " \
        sqoop import --connect \"jdbc:oracle:thin:@$jdbc/$db \" --username $user --password $passwd \
        --table $schema.$otab \
        --hive-import \
        --fields-terminated-by '\001' \
        --hive-drop-import-delims \
        --delete-target-dir \
        --hive-database skoana \
        --hive-table $schema\_$htab \
        --map-column-java $column=String --map-column-hive $column=String \
        --fetch-size 200 \
        -m 1 " >erro/$schema-$htab.log 2>&1
}

for tab in `cat erroinfo.log`
do
   (sqoopOracle2hive $tab)&
   wait;
done