1、需求:
从指定服务器上获取txt文件数据,导入指定到数据库中
load data
CHARACTERSET UTF8
#要读取的文件
infile '/ test _cpcout / test /test_20170811_000001.txt'
append into table TBL_test_BASE_INFO
fields terminated by X'1'
TRAILING NULLCOLS
(
user_name "decode(:user_name ,'\\N',null,'--',null,:user_name )",
age "decode(:age ,'\\N',null,'--',null,:age )",
sex "decode(:sex ,'\\N',null,'--',null,:sex )",
user_id "decode(:user_id ,'\\N',null,'--',null,:user_id )",
create_date "to_char(sysdate,'YYYYMMDDHH24MISS')",
id "sys_guid()"
)
3、test.sh脚本如下执行命令如下:
#!/bin/bash
. $HOME/.bash_profile
cd /test_cpcout/test;
v_out_path="/test_cpcout/test/";
v_out_file="test_"`date -d "+0 day" "+%Y%m%d"`;
v_file_new=$(find $v_out_path$v_out_file*|head -1);
#v_file_name=$(basename $v_file_new .txt);
#v_file=$(find /holly_cpcout/test/ -name test_`date -d "+0 day" "+%Y%m%d"`_*|head -1)
echo $v_file_new
#count=`awk '{print NR}' $v_file_new|tail -n1`
#if [ "$count" > "0" ]; then
if [ -f "$v_file_new" ]; then
echo "load data" >INTERFACE_test_LABEL.ctl;
echo "CHARACTERSET UTF8">>INTERFACE_test_LABEL.ctl;
echo "infile '"$v_file_new"'">>INTERFACE_test_LABEL.ctl;
echo "append into table TBL_test_BASE_INFO">>INTERFACE_test_LABEL.ctl;
echo "fields terminated by X'1' ">>INTERFACE_test_LABEL.ctl;
echo "TRAILING NULLCOLS">>INTERFACE_test_LABEL.ctl;
echo "(
user_name \"decode(:user_name ,'\\\N',null,'--',null,:user_name )\",
age \"decode(:age ,'\\\N',null,'--',null,:age )\",
sex \"decode(:sex ,'\\\N',null,'--',null,:sex )\",
user_id \"decode(:user_id ,'\\\N',null,'--',null,:user_id )\",
create_date \"to_char(sysdate,'YYYYMMDDHH24MISS')\",
id \"sys_guid()\"
)">>INTERFACE_test_LABEL.ctl;
sqlldr test/test@127.0.0.1:1521/wxclyy control=INTERFACE_test_LABEL.ctl direct=true log=error.log errors=1000 rows=30000;
mv $v_file_new $v_out_path/test_history/;
else
echo "file is empty!"
fi
exit;
4、执行sh脚本,即可导入
./holly_cpcout/test/test.sh