ITPub博客

首页 > 大数据 > 数据挖掘 > 一个数据仓库数据刷新的实现机制(五)

一个数据仓库数据刷新的实现机制(五)

原创 数据挖掘 作者:zhyuh 时间:2005-04-07 19:13:56 0 删除 编辑

shell refresh_table

用于调用一个sql文件刷新一张表,并纪录刷新信息

[@more@]

用法:refresh_table SQL_FILE_NAME SCHEMA PASSWORD

$refresh_table eden_flat_ord_cmpt_fr.sql edenfr ******

该脚本中注意以下语句:

TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

其中${1%_*}是bash里面的模式匹配。shell refresh_table位于目录/erdb/bin下,而sql文件位于/erdb/sql目录下,sql文件名的命名规则为TABLENAME_COUNTRY.sql, 其中COUNTRY为缩写,FR代表法国,UK代表英国等。

通过修改函数rfsh_env()中的参数,可以方便连到不同的数据库(比如开发库和生产库),也可以修改日志文件目录等。

shell refresh_table的脚本如下:

#####################################################################
#
# Purpose: This script is to refresh tables, and log refresh
#     information both in table and OS file
#
# Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS
#
# Global varialbles
# FILE_NAME: the file needed by refresh
# USERNAME: database login ID
# PASSWORD: password for database user
# DATABASE: service name of database
# SCHEMA: the owner of objects to be refreshed
# SCHEMA_PASS: the password for the user SCHEMA
# TABLE_NAME: the table to be refreshed
# START_TIME: when refresh begins
# END_TIME: when refresh ends
# BEFORE_ROWS: rows before refresh
# AFTER_ROWS: rows after refresh
# REFRESH_SEQ: the refresh sequence number for the table
# LOG_PATH: where the log file locates
# LOG_FILE: the file to store log information
#     file name in format refresh_log.yyyy-mm-dd
# LOG_DETAIL: the file to log detail information
#     file name in format log_detail.SCHEMA.yyyy-mm-dd
# LOG_ROWS: a mid_stage parameter
#
#
#####################################################################

#!/usr/bin/bash

rfsh_env()
{
LOG_PATH=/erdb/log
LOG_FILE=refresh_log.`date "+%Y-%m-%d"`
LOG_DETAIL=log_detail.$SCHEMA.`date "+%Y-%m-%d"`
USERNAME=eden
PASSWORD=******
DATABASE=service_name
}

rfsh_refresh()
{
TABLE_NAME=`echo ${1%_*}|cut -d"/" -f 4`

LOG_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
 SET FEED OFF
 SET TERM OFF
 SET HEAD OFF
 SELECT count(*)
   FROM eden_refr_log
   WHERE user_nam='$SCHEMA'
   AND obj_nam='$TABLE_NAME';
EOF`

if [ $LOG_ROWS -eq 0 ]; then
  REFRESH_SEQ=1
else
  REFRESH_SEQ=`sqlplus -s
$USERNAME/$PASSWORD@$DATABASE<
 SET FEED OFF
 SET TERM OFF
 SET HEAD OFF
 SELECT max(refr_seq_nb+1)
   FROM eden_refr_log
   WHERE user_nam='$SCHEMA'
   AND obj_nam='$TABLE_NAME';
EOF`
fi

BEFORE_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
        SET FEED OFF
        SET TERM OFF
        SET HEAD OFF
 select count(*) from $SCHEMA.$TABLE_NAME;
EOF`

START_TIME=`date "+%Y-%m-%d %H:%M:%S"`

echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
echo "start refreshing $TABLE_NAME ..." >>$LOG_PATH/$LOG_DETAIL
echo "-----------------------------------------">>$LOG_PATH/$LOG_DETAIL
sqlplus -s
>$LOG_PATH/$LOG_DETAIL$SCHEMA/$SCHEMA_PASS@$DATABASE>>$LOG_PATH/$LOG_DETAIL<
 
  INSERT INTO $USERNAME.eden_refr_log(user_nam,obj_nam,obj_type,refr_seq_nb,strt_dat_tim,rows_bef_refr)
  VALUES('${SCHEMA}','${TABLE_NAME}','T',${REFRESH_SEQ},sysdate,$BEFORE_ROWS);
  COMMIT;
 
 
  spool /tmp/$SCHEMA_$TABLE_NAME.out
  set head off
  @$FILE_NAME
  select 'updating table eden_refr_log...' from dual;
  set head on
  spool off
 
  UPDATE $USERNAME.eden_refr_log
    SET end_dat_tim=sysdate
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

END_TIME=`date "+%Y-%m-%d %H:%M:%S"`

ERRORMSG=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|head -n 1`

ERR_COUNT=`cat /tmp/$SCHEMA_$TABLE_NAME.out|grep ORA-|wc -l`

if [ $ERR_COUNT -eq 0 ]; then
  REFRESH_STATUS='Y'
  RETURN_VAL=0
else
  REFRESH_STATUS='N'
  RETURN_VAL=1
fi

sqlplus -s >$LOG_PATH/$LOG_DETAIL$USERNAME/$PASSWORD@$DATABASE>>$LOG_PATH/$LOG_DETAIL<
  UPDATE eden_refr_log
    SET sta_cod='$REFRESH_STATUS',
        tot_drtn_tim=( end_dat_tim - strt_dat_tim )*3600*24,
 err_txt='$ERRORMSG'
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

AFTER_ROWS=`sqlplus -s $USERNAME/$PASSWORD@$DATABASE<
  SET FEED OFF
  SET TERM OFF
  SET HEAD OFF
  select count(*) from $SCHEMA.$TABLE_NAME;
EOF`

sqlplus -s >$LOG_PATH/$LOG_DETAIL$USERNAME/$PASSWORD@$DATABASE>>$LOG_PATH/$LOG_DETAIL<
  UPDATE eden_refr_log
    SET   rows_aft_refr=$AFTER_ROWS
    WHERE user_nam='$SCHEMA'
    AND obj_nam='$TABLE_NAME'
    AND refr_seq_nb=$REFRESH_SEQ;
  COMMIT;

  EXIT;
EOF

echo "owner: "$SCHEMA>>$LOG_PATH/$LOG_FILE
echo "table name: "$TABLE_NAME>>$LOG_PATH/$LOG_FILE
echo "start time: "$START_TIME>>$LOG_PATH/$LOG_FILE
echo "end time: "$END_TIME>>$LOG_PATH/$LOG_FILE
echo "rows before refresh: "$BEFORE_ROWS>>$LOG_PATH/$LOG_FILE
echo "rows after refresh: "$AFTER_ROWS>>$LOG_PATH/$LOG_FILE
echo "refresh succeed(Y/N): "$REFRESH_STATUS>>$LOG_PATH/$LOG_FILE
echo "error messages if any: "$ERRORMSG>>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
echo " ">>$LOG_PATH/$LOG_FILE
}


#################################
## Main
#################################

if [ $# -lt 3 ]; then
  echo " "
  echo "Usage: refresh_table SQL_FILE_NAME SCHEMA SCHEMA_PASS"
  echo " "
  exit 1
fi

FILE_NAME=`echo $1|tr "[A-Z]" "[a-z]"`
FILE_NAME=/erdb/sql/$FILE_NAME
SCHEMA=`echo $2|tr "[A-Z]" "[a-z]"`
SCHEMA_PASS=`echo $3|tr "[A-Z]" "[a-z]"`

if [ ! -e $FILE_NAME ]; then
  echo " "
  echo "File $FILE_NAME does NOT exist."
  echo "Please specify an existed SQL file."
  echo " "
  echo " ">>$LOG_PATH/$LOG_FILE
  echo "----------------------------------">>$LOG_PATH/$LOG_FILE
  echo "  REFRESHING `echo ${1%_*}|cut -d"/" -f 3` ...">>$LOG_PATH/$LOG_FILE
  echo "----------------------------------">>$LOG_PATH/$LOG_FILE
  echo "File $FILE_NAME does NOT exist! ">>$LOG_PATH/$LOG_FILE
  exit 1
fi

rfsh_env

rfsh_refresh $FILE_NAME $SCHEMA $SCHEMA_PASS

if [ $RETURN_VAL -eq 1 ]; then
  echo ""
  echo "Errors during refresh table $TABLE_NAME."
  echo ""
fi

exit $RETURN_VAL

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/207/viewspace-795320/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2008666