loaddatatogp.sh_loadtopg-优快云博客

本文介绍了一个用于Greenplum数据库(GPDB)的Shell脚本，该脚本能够批量加载数据到指定的数据库表格中。脚本通过创建外部表并使用gpfdist服务从文件系统读取数据，然后将数据插入到目标表。此外，脚本还包含了错误处理和邮件通知功能。

#!/bin/bash
if [ $# -lt 2 ]
then
    echo "./loaddata schema tablename"
    exit
 fi

schema=$1
tablename=$2
basepath=$(cd `dirname $0`; pwd)
cd ${basepath}

LOAD_SQL="insert into ${schema}.${tablename} select * from ${schema}.r_ext_${tablename};"

tempfile="${basepath}/loaddatatogp.log"
export PGPASSWORD=abcdefghijklmn
/usr/bin/psql -h 10.10.10.10 -p 5432 -d gp_test -U gp_rd -c "$LOAD_SQL" > ${tempfile} 2>&1
if [ $? -eq 0 ]
   then
        logmsg=`cat ${tempfile}`
        echo "load to success ${tablename}:${logmsg}"
        emailTitle="[${schema}.${tablename}] load success"
        emailContent=`echo "datax load to gppay success: ${schema}.${tablename}: ${logmsg}"`
     else
        logmsg=`cat ${tempfile}`
        msg=`echo "datax load to gppay failed:${tablename}:${logmsg}"`
        emailTitle="[${schema}.${tablename}] load failed"
        emailContent=`echo "datax load to gppay failed: ${schema}.${tablename}: ${logmsg}"`
fi
echo ${emailContent} | mail -s "${emailTitle}" daiyuanpei@test.com
#echo ${emailContent} | mail -s "${emailTitle}" daiyuanpei@test.com -c "one@test.com two@test.com"
# 执行
nohup sh loaddatatogp.sh schema tablename > tablename.out 2>&1 &

#!/bin/bash
if [ $# -lt 2 ]
then
    echo "./loaddata schema tablename"
    exit
 fi

schema=$1
tablename=$2

i=1
while [ 1=1 ]
do
    if [ $i -le 100 ];then
        echo $i
        hostindex="000${i}_0"
        link_path="/home/work/gp_sync/input_ptr/pay/gp_test/${schema}/${index}"
        data_path="/home/work/hdfs_gpdata/alldata/tablename/${index}"
        echo "schema=$schema,tablename=${tablename},link_path=${link_path},data_path=${data_path}"
        basepath=$(cd `dirname $0`; pwd)
        cd ${basepath}
        if [ ! -e ${data_path}  ];then
            echo "no exist filepath:${data_path}"
            exit
        fi
        tempfile="${basepath}/loaddataxtogp-batch.log"
        ext_tablename="r_ext_${tablename}"
        CREATE_EXT_SQL="create external table ${schema}.${ext_tablename} (like ${schema}.${tablename})
        location ('gpfdist://20.20.20.20:6666/pay/gp_test/${schema}/${index}')
        format 'TEXT' (delimiter as E'|' null as '\N' escape 'OFF');"
        DEL_EXT_TB_SQL=" drop external table ${schema}.${ext_tablename}"
        export PGPASSWORD=abcdefghijklmn
        /usr/bin/psql -h 10.10.10.10 -p 5432 -d gp_test -U gp_rd -c "$DEL_EXT_TB_SQL" > ${tempfile} 2>&1
        /usr/bin/psql -h 10.10.10.10 -p 5432 -d gp_test -U gp_rd -c "$CREATE_EXT_SQL" >> ${tempfile} 2>&1
        if [ $? -eq 0  ]
        then
            echo "SUCCESS!  ${CREATE_EXT_SQL}"
        else
            echo "FAIL!  ${CREATE_EXT_SQL}"
            exit
        fi
        LOAD_SQL="insert into ${schema}.${tablename} select * from ${schema}.${ext_tablename};"
        rm -f ${link_path}
        ln -sf ${data_path}  ${link_path}
        export PGPASSWORD=abcdefghijklmn
        /usr/bin/psql -h 10.10.10.10 -p 5432 -d gp_test -U gp_rd -c "$LOAD_SQL" >> ${tempfile} 2>&1
        if [ $? -eq 0  ]
            then
            logmsg=`cat ${tempfile}`
            echo "load to success ${tablename}:${logmsg}"
        else
            logmsg=`cat ${tempfile}`
            echo "datax load paycoredb to gppay failed:${tablename}:${logmsg}"
            exit
        fi
    else
        break
    fi
    let i++
    echo "*********************"
done

# 执行
nohup sh loaddatatogp-batch.sh schema tablename > tablename.out 2>&1 &