CCU ETL指令碼列表

------------------------------------------------------------------------
/* kpi_auto_data.sh
 用於生成各遊戲呼叫ccu處理的指令碼*/
#!/bin/bash
#auth: maoyu
#date: 20100220
#desc: kpi ccu data import
 
cur_dt=$1
IF [ -z $cur_dt ]
THEN
  cur_dt=`DATE +%Y%m%d`
fi
 
cat kpi_ccu_site_cd.cfg |awk '{if(substr($1,1,1)!="#") {print "/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh ""'$cur_dt'"" "$2"'$cur_dt'""/ "$2"'$cur_dt'""/ orads dstage gamenow "tolower($1)"_ccu_his_""'$cur_dt'"".txt "tolower($1);}}' > /opt/yanguang/etl/orads/kpi/kpi_imp_data.sh
# /bin/bash /opt/yanguang/etl/orads/kpi/kpi_imp_data.sh
 
------------------------------------------------------------------------
/* kpi_imp_data.sh
   執行kpi_auto_data.sh產生的結果檔案*/
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/sunkpi/20100222/ /opt/pass9kpi/sunkpi/20100222/ orads dstage gamenow sun_ccu_his_20100222.txt sun
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/gekpi/20100222/ /opt/pass9kpi/gekpi/20100222/ orads dstage gamenow ge_ccu_his_20100222.txt ge
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/fifakpi/kpi/20100222/ /opt/pass9kpi/fifakpi/kpi/20100222/ orads dstage gamenow fifa_ccu_his_20100222.txt fifa
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/wofkpi/kpi/20100222/ /opt/pass9kpi/wofkpi/kpi/20100222/ orads dstage gamenow mjsg_ccu_his_20100222.txt mjsg
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/atkpi/20100222/ /opt/pass9kpi/atkpi/20100222/ orads dstage gamenow at_ccu_his_20100222.txt AT
/bin/bash /opt/yanguang/etl/orads/kpi/kpi_ccu_his.sh 20100222 /opt/pass9kpi/9zkpi/kpi/20100222/ /opt/pass9kpi/9zkpi/kpi/20100222/ orads dstage gamenow 9war_ccu_his_20100222.txt 9z
 
------------------------------------------------------------------------
/* kpi_ccu_his.sh
   匯入及後續處理指令碼*/
#!/usr/bin/bash
#auth: maoyu
#date: 20100221
#desc: import ccu_his
source /opt/yanguang/common/ora_env_var.sh
echo "------------------------------------------" >> /opt/yanguang/etl/orads/kpi/imp.LOG
#current DATE
cur_dt=$1
IF [ -z $cur_dt ]
THEN
  cur_dt=`DATE +%Y%m%d`
fi
 
#dest PATH 
work_path=$2
dest_path=$3
cd $dest_path
 
#set variable
# oracle USER password
db_server=$4 # "orads"
ora_user=$5 # "kpi"
ora_pwd=$6 # "kpigamenow"
 
# file name
init_file_name=$7
src_file_name="${init_file_name}"
ctl_file_name1="${init_file_name}.ctl"
des_file_name1="${init_file_name}"
 
# site_cd
site_cd=$8
 
IF [ -f ${src_file_name} ]; THEN
#build sqlldr ctl file
echo "load data" > $ctl_file_name1
echo "infile '$des_file_name1'" >> $ctl_file_name1
echo "append" >> $ctl_file_name1
echo "into table temp_kpi_ccu_daily_his" >> $ctl_file_name1
echo "fields terminated by '|' trailing nullcols" >> $ctl_file_name1
echo "(" >> $ctl_file_name1
echo "  site_cd, " >> $ctl_file_name1
echo "  create_ts, " >> $ctl_file_name1
echo "  site_id, " >> $ctl_file_name1
echo "  group_id, " >> $ctl_file_name1
echo "  accu_val, " >> $ctl_file_name1
echo "  pccu_val " >> $ctl_file_name1
echo ")" >> $ctl_file_name1
 
#load_data TO temporey TABLE
sqlldr $ora_user/$ora_pwd@"$db_server" control=$ctl_file_name1 bad=${des_file_name1}.bad LOG=${des_file_name1}.LOG rows=10000 skip=1
 
#process LOG 
echo "`date +%Y%m%d%H%M%S`--sqlldr ${dest_path}/${src_file_name} OK!" >> /opt/yanguang/etl/orads/kpi/imp.LOG
 
sqlplus -S $ora_user/$ora_pwd@"$db_server" >> /opt/yanguang/etl/orads/kpi/imp.LOG << EOF
  SET serveroutput ON; 
  DECLARE
    vcnt NUMBER(12) := 0;
  BEGIN
    SELECT COUNT(*) 
    INTO vcnt
    FROM temp_kpi_ccu_daily_his
    WHERE site_cd = '${site_cd}'
      AND create_ts >= TO_DATE('${cur_dt}', 'yyyymmdd') - 1  
      AND create_ts < TO_DATE('${cur_dt}', 'yyyymmdd') ; 
 
    IF vcnt <> 0 THEN
                  kpi.proc_update_kpi_rpt_ccu('${site_cd}', TO_DATE('${cur_dt}', 'yyyymmdd') - 1);
        DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || 'data is ok!');
 
    ELSE 
      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || 'data load failure!'); 
    END IF; 
 
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line(TO_CHAR(SYSDATE, 'yyyymmddhh24miss') || ' insert data error!'); 
  END;
/
EXIT;
EOF
ELSE
  echo "`date +%Y%m%d%H%M%S`--${work_path}/${src_file_name} not exists!" >> /opt/yanguang/etl/orads/kpi/imp.LOG
fi