How to get the site table to DW table mapping
###This article records the process that how to get site table to DW table mapping
###Step 1: Get the etl_id list from teradata
Vivaldi
exclude those etl id with long number serials
select distinct etl_id
from da_tables.dw_infra_etl_log
where dwi_start_date >= date -30
and REGEXP_SUBSTR(etl_id, '(\w\d\d\d\d)', 1,1,'c') is null;
Copy the etl_id list to ETL server
Host: Dev01 `zaisetldevha01.smf.ebay.com`
/home/lucao/dataflow/etl_id.lis
###Step 2: Generate the tmp file on ETL server
-bash-3.00$ ksh
$ ls
etl_id.lis fild_bak id_src_stg.map id_src_stg.map.tmp id_src_stg.map.tmp2 load2vv.ksh sync_site2dw.ksh
$ . ./sync_site2dw.ksh
The Kshell file sync_site2dw.ksh is a parser which get the source table and target table mapping from the ETL config file and source list.
-bash-3.00$ cat sync_site2dw.ksh
#!/usr/bin/ksh -eu
> /export/home/lucao/dataflow/src_stg_id/id_src_stg.map
while read ETL_ID
do
src_tbl=""
stg_db=""
stg_tbl=""
SEL_SITE_TBL="NA"
DB_TYPE=""
SEL_FILE=$DW_SQL/${ETL_ID}.sel.sql
CFG_FILE=$DW_CFG/${ETL_ID}.cfg
SRC_FILE=$DW_CFG/${ETL_ID}.sources.lis
if [ -f $CFG_FILE ]
then
set +e
grep -i STAGE_DB $CFG_FILE|read A stg_db other
grep -i STAGE_TABLE $CFG_FILE|read A stg_tbl other
set -e
fi
if [ -f $SRC_FILE ]
then
set +e
head $SRC_FILE|read A DBC_FILE C SITE_TBL OTHER
echo $DBC_FILE |awk -F\_ '{print $1}'|read DB_TYPE
if [[ ${DB_TYPE} == @(oracle||mysql||teradata||odbc) ]]
then
SEL_SITE_TBL=$SITE_TBL
if [ -f $SEL_FILE ]
then
set +e
daquery ${SEL_FILE} -table-names -dbms ${DB_TYPE} |read src_tbl
set -e
echo "${DB_TYPE}|${ETL_ID}|${src_tbl:-$SEL_SITE_TBL}|${stg_db:-"NA"}|${stg_tbl:-"NA"}" >> /export/home/lucao/dataflow/src_stg_id/id_src_stg.map
fi
fi
set -e
fi
done < /export/home/lucao/dataflow/src_stg_id/etl_id.lis
echo "cat <<EOF" > /export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp
grep -v NA /export/home/lucao/dataflow/src_stg_id/id_src_stg.map|grep -v "\#" >>
/export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp
echo "\nEOF" >> /export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp
chmod +x /export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp
set +u
. /export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp > /export/home/lucao/dataflow/src_stg_id/id_src_stg.map.tmp2
set -u
###Step 3: Load data from ETL server to Teradata
Use teradata tool TPT load data.
-bash-3.00$ cat load2vv.ksh
#!/bin/ksh
/dw/etl/mstr_bin/tpt_load.64 \
-d dev_scratch \
-t site_dw_mapping \
-wd dev_scratch \
-s vivaldi \
-u lucao \
-p ******** \
-mn zaisetldevha01.smf.ebay.com \
-po 8712 \
-i 1 \
-z 1 \
-n 1 \
-ns 32 \
-l ~/tpt_load_test_ld.log \
-ot 1 \
-to 1 \
-f id_src_stg.map.tmp2 \
-dl '7C' \