大数据平台用于生成数据跑批脚本的脚本(version3.0)

最新推荐文章于 2024-01-20 19:31:57 发布

BabyFish13

最新推荐文章于 2024-01-20 19:31:57 发布

阅读量2.9k

点赞数

分类专栏： DataWarehouse BigData Shell Solution projectCase 文章标签： hadoop hive linux shell 大数据脚本

本文链接：https://blog.csdn.net/BabyFish13/article/details/51753568

版权

Solution 同时被 3 个专栏收录

85 篇文章 1 订阅

订阅专栏

BigData

52 篇文章 2 订阅

订阅专栏

projectCase

36 篇文章 0 订阅

订阅专栏

一、脚本文件路径
[hs@master script_generate]$ pwd
/home/hs/opt/dw-etl/script_generate

[hs@master script_generate]$ tree -f
.
├── ./batch_table.list
├── ./bigtable-create_allstage_suppertab_int_script.sh
├── ./etl_table_list_update.sh
├── ./exec_hivessa_create_tab.sh
├── ./exec_rdsqrt_create_tab.sh
├── ./exec_rdssor_close2source_create_tab.sh
├── ./exec_rdssrc_create_tab.sh
├── ./qrt-create_rdstab_script.sh
├── ./qrt-create_src2qrt_script.sh
├── ./rds-increment_tab_should_create_idx_sql.sh
├── ./real_table.list
├── ./script_dir
│   ├── ./script_dir/hive-ssa_create_tab_script
│   │   ├── ./script_dir/hive-ssa_create_tab_script/99_areajoin.hql
│   │   ├── ./script_dir/hive-ssa_create_tab_script/99_cashieronlineorderitem.hql
│   │   └── ./script_dir/rds-qrt_create_tab_script/b_box.sql
│   ├── ./script_dir/rds-sor_close2source_create_tab_script
│   │   ├── ./script_dir/rds-sor_close2source_create_tab_script/99_areajoin.sql
│   │   ├── ./script_dir/rds-sor_close2source_create_tab_script/99_cashieronlineorderitem.sql
│   │   └── ./script_dir/rds-sor_close2source_create_tab_script/x_shop.sql
│   ├── ./script_dir/rds-src_create_tab_script
│   │   ├── ./script_dir/rds-src_create_tab_script/99_areajoin.sql
│   │   ├── ./script_dir/rds-src_create_tab_script/99_cashieronlineorderitem.sql
│   │   └── ./script_dir/rds-ssa_create_tab_script/x_shop.sql
│   └── ./script_dir/ssa-hive_increment_data_rollback.hql
├── ./sor-close2source_create_hive2mysql_script.sh
├── ./sor-close2source_create_rdstab_script.sh
├── ./sor-close2source_data_pull_from_ssa_script.sh
├── ./src-create_rdstab_script.sh
├── ./src-create_src2dwsrc_script.sh
├── ./ssa-create_dwsrc2ssa_script.sh
├── ./ssa-create_hivetab_script.sh
└── ./tabsrc_strurecord_script.sh

8 directories, 1111 files(因脚本太多，实际展示内容有省略)

二、配置文件列表清单及处理脚本
列表清单参照version2.0,并无改动。
用户跑批清单处理脚本：
[hs@master script_generate]$ cat etl_table_list_update.sh
#!/bin/bash

#batch per day script proc ...
sort -t "|" -k3,3 /home/hs/opt/dw-etl/script_generate/batch_table.list > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_sort.list
join /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_sort.list /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/src_userpass_info.list -t "|" -1 3 -2 1 -o 1.1,1.2,1.3,1.4,1.5,2.2,2.3 > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list
rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_sort.list

#batch real time script proc ...
sort -t "|" -k3,3 /home/hs/opt/dw-etl/script_generate/real_table.list > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_sort.list
join /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_sort.list /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/src_userpass_info.list -t "|" -1 3 -2 1 -o 1.1,1.2,1.3,1.4,1.5,2.2,2.3 > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list
rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_sort.list

#get instance info for edit userpass
cat /home/hs/opt/dw-etl/script_generate/batch_table.list /home/hs/opt/dw-etl/script_generate/real_table.list|awk -F "|" '{print $3}'| sort | uniq > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_instance_info.list

#get schema,instanct,user,pass info for table stru pull per day
cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list|awk -F "|" '{print $3"|"$2"|"$6"|"$7 }'| sort | uniq > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list
[hs@master script_generate]$

三、源数据拉取到dw-rds中脚本
1、生成rds-src建表脚本
[hs@master script_generate]$ cat src-create_rdstab_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

#script dir int ...
rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script
mkdir -p /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

col_num=$i

tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)

echo -e `mysql -h$db_ip -u$user_name -p$passwd -N -e"set session group_concat_max_len=20000000;
select CONCAT('use src;
drop table if exists \\\`',table_name,'\\\`;
create table \\\`',table_name,'\\\`(',
GROUP_CONCAT('\n\\\`',COLUMN_NAME,'\\\` ',DATA_TYPE,' comment ''',column_comment,''''),
') ENGINE=InnoDB DEFAULT CHARSET=utf8;')
from (
select
COLUMN_NAME,table_name table_name,column_type data_type,column_comment
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name') a1;"` > /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script/$tab_name.sql

: $(( i++ ))

done

#sed -i "s/'n'/'\\\n'/g" `grep "lines terminated by" -rl /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script`

2、生成dwsrc从src拉取数据的脚本
[hs@master script_generate]$ cat src-create_src2dwsrc_script.sh
#!/bin/bash
export today=`date +%Y-%m-%d`
export yesterday=`date -d last-day +%Y-%m-%d`
export beforeytd=`date -d "2 days ago" +%Y-%m-%d`
export ytd=${yesterday//-/}

rm -rf /home/hs/opt/dw-etl/etl-script/src/

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

col_num=$i

if [ ! -d /home/hs/opt/dw-etl/etl-script/src/$db_name ];then
mkdir -p /home/hs/opt/dw-etl/etl-script/src/$db_name
fi

rm -rf /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

if [ $load_type == 'total' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#','$tab_name',' table total data upload to dw-src ...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use src;truncate table ','$tab_name',';\"huanhang#load data file to dw-rds huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use src;load data local infile \'/home/hs/opt/dw-etl/data/src2dwsrc_data/','$db_name','.','$tab_name','.dat\' into table ','$tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-src2dwsrc $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/src-src2dwsrc_run.loghuanhang') mysql_export_and_dwrds_load_shell
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

elif [ $load_type == 'increment' ];then

echo $tab_name,data is increment load!

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-src2dwsrc $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/src-src2dwsrc_run.loghuanhang#userpass info get ...huanhangsrc_dbip=\`sh /home/hs/opt/public-function/getsrcinfo.fc dbip $tab_name\`huanhangsrc_user=\`sh /home/hs/opt/public-function/getsrcinfo.fc user $tab_name\`huanhangsrc_pass=\`sh /home/hs/opt/public-function/getsrcinfo.fc pass $tab_name\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhanghuanhang#fetch maxtime from dw-src database ...huanhangexport last_maxtime=\`/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -N -e \"use src;select max(timeline) max_time from src.','$tab_name',';\"\`huanhanghuanhangif [[ \$last_maxtime = \'NULL\' ]]; thenhuanhang last_maxtime=\'1970-01-01 00:00:00\'huanhangelsehuanhang last_maxtime=\$last_maxtimehuanhangfihuanhanghuanhang#$tab_name data download from src ...huanhang/usr/local/bin/mysql -h\$src_dbip -u\$src_user -p\$src_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar','char') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from ',TABLE_SCHEMA,'.',table_name,' where timeline >= \'\$last_maxtime\';'
,'\" >/home/hs/opt/dw-etl/data/src2dwsrc_data/',TABLE_SCHEMA,'.',table_name,'.dathuanhang'
,'') mysql_export_and_dwrds_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#','$tab_name',' table increment data upload to dw-src ...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;drop table if exists ','$tab_name','_day;create table ','$tab_name','_day as select * from src.','$tab_name',' where 1=2;\"huanhang#loop load data file to rds huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;load data local infile \'/home/hs/opt/dw-etl/data/src2dwsrc_data/','$db_name','.','$tab_name','.dat\' into table ','$tab_name','_day fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;alter table tmp.','$tab_name','_day add index idx_','$tab_name','_',k.COLUMN_NAME,' (',k.COLUMN_NAME,') using btree;delete ca.* from src.','$tab_name',' ca left join tmp.','$tab_name','_day i on ca.',k.COLUMN_NAME,' = i.',k.COLUMN_NAME,' where i.',k.COLUMN_NAME,' is not null;insert into src.','$tab_name',' select * from tmp.','$tab_name','_day;\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-src2dwsrc $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/src-src2dwsrc_run.loghuanhang') mysql_export_and_dwrds_load_shell
from information_schema.TABLES t
left join information_schema.KEY_COLUMN_USAGE k
on t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME and k.CONSTRAINT_name='PRIMARY'
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

elif [ $load_type == 'addto' ];then

echo $tab_name,data is addto load!

sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/src/$db_name/`

chmod 777 /home/hs/opt/dw-etl/etl-script/src/$db_name/src2dwsrc_$tab_name.sh

: $(( i++ ))

done

#sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/src/$db_name/`

[hs@master script_generate]$

四、ssa层数据处理脚本
1、生成ssa的hive建表脚本
[hs@master script_generate]$ cat ssa-create_hivetab_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script
if [ ! -d /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script ];then
mkdir -p /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script
fi

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)

echo -e `mysql -h$db_ip -u$user_name -p$passwd -N -e"set session group_concat_max_len=20000000;
select CONCAT('use ssa;
drop table if exists \\\`',table_name,'\\\`;
create table \\\`',table_name,'\\\`(',
GROUP_CONCAT('\n\\\`',COLUMN_NAME,'\\\` ',DATA_TYPE,''),
')
row format delimited
fields terminated by \'|\'
lines terminated by \'"\\n"\'
stored as textfile;')
from (
select
COLUMN_NAME,table_name,
case when DATA_TYPE in('int','bigint','mediumint','smallint','tinyint') and (COLUMN_NAME not like '%time' and COLUMN_NAME not like '%date') then 'bigint' when DATA_TYPE in('varchar','char') then CONCAT('varchar','(',CHARACTER_MAXIMUM_LENGTH*1,')') when DATA_TYPE in('decimal') then CONCAT('decimal','(',NUMERIC_PRECISION*1,',',NUMERIC_SCALE*1,')') when DATA_TYPE in('text','enum','bit','mediumtext','longtext') then 'string' when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then 'varchar(19)' else DATA_TYPE end data_type
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name') a1;"` > /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script/$tab_name.hql

: $(( i++ ))

done

sed -i "s/'n'/'\\\n'/g" `grep "lines terminated by" -rl /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script`
[hs@master script_generate]$

2、生成ssa从dwsrc拉取数据的脚本
[hs@master script_generate]$ cat ssa-create_dwsrc2ssa_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/etl-script/ssa-increment /home/hs/opt/dw-etl/etl-script/ssa-total /home/hs/opt/dw-etl/etl-script/ssa-addto

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

col_num=$i

if [ ! -d /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name ];then
mkdir -p /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name
fi

rm -rf /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

if [ $load_type == 'total' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang#userpass info get ...huanhangdwsrc_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src_read\`huanhangdwsrc_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src_read\`huanhangdwsrc_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src_read\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhanghuanhanghuanhang#$tab_name data download from src ...huanhang/usr/local/bin/mysql -h\$dwsrc_dbip -u\$dwsrc_user -p\$dwsrc_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from src.',table_name,';'
,'\" >/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dat\' overwrite into table ',table_name,';\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang') mysql_export_and_hive_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

elif [ $load_type == 'increment' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang#userpass info get ...huanhangdwsrc_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src_read\`huanhangdwsrc_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src_read\`huanhangdwsrc_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src_read\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhanghuanhanghuanhang#$tab_name data download from src ...huanhang/usr/local/bin/mysql -h\$dwsrc_dbip -u\$dwsrc_user -p\$dwsrc_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from src.',table_name,' where timeline between str_to_date(\'\$1 00:00:00\',\'%Y-%m-%d %H:%i:%s\') and str_to_date(\'\$2 00:00:00\',\'%Y-%m-%d %H:%i:%s\');'
,'\" >/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dat\' into table ',table_name,';\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang') mysql_export_and_hive_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

elif [ $load_type == 'addto' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang#userpass info get ...huanhangdwsrc_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src_read\`huanhangdwsrc_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src_read\`huanhangdwsrc_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src_read\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhanghuanhanghuanhang#$tab_name data download from src ...huanhang/usr/local/bin/mysql -h\$dwsrc_dbip -u\$dwsrc_user -p\$dwsrc_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from src.',table_name,' where timeline between str_to_date(\'\$1 00:00:00\',\'%Y-%m-%d %H:%i:%s\') and str_to_date(\'\$2 00:00:00\',\'%Y-%m-%d %H:%i:%s\');'
,'\" >/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'_$load_type','.dat\' into table ',table_name,';\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang') mysql_export_and_hive_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/`

chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/src2ssa_$tab_name.sh

: $(( i++ ))

done

#sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/ssa-$load_type/$db_name/`

[hs@master script_generate]$

五、sor层数据处理脚本
1、生成sor整合数据从ssa获取脚本
[hs@master script_generate]$ cat sor-close2source_data_pull_from_ssa_script.sh
#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/etl-script/sor/sor_close2source_data_pull_proc_from_ssa.hql

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

if [ $load_type == 'total' ] || [ $load_type == 'addto' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('drop table if exists sor.',a1.TABLE_NAME,';create table sor.',a1.TABLE_NAME,' as select * from ssa.',a1.TABLE_NAME,' a1;' ) current_all_sql
from information_schema.TABLES a1
where a1.TABLE_SCHEMA ='$db_name' and a1.table_name ='$tab_name'
group by a1.TABLE_NAME;" >> /home/hs/opt/dw-etl/etl-script/sor/sor_close2source_data_pull_proc_from_ssa.hql

elif [ $load_type == 'increment' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('drop table if exists sor.',a1.TABLE_NAME,';create table sor.',a1.TABLE_NAME,' as select * from (select a1.*,row_number() over(partition by ',GROUP_CONCAT('a1.',a2.column_name),' order by a1.timeline desc)as rn from ssa.',a1.TABLE_NAME,' a1) b1 where b1.rn=1;' ) current_all_sql
from information_schema.TABLES a1
left join information_schema.KEY_COLUMN_USAGE a2
on a1.TABLE_NAME=a2.TABLE_NAME and a1.TABLE_SCHEMA=a2.TABLE_SCHEMA and a2.CONSTRAINT_name='PRIMARY'
where a1.TABLE_SCHEMA ='$db_name' and a1.table_name ='$tab_name'
group by a1.TABLE_NAME;" >> /home/hs/opt/dw-etl/etl-script/sor/sor_close2source_data_pull_proc_from_ssa.hql

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/sor/sor_close2source_data_pull_proc_from_ssa.hql

: $(( i++ ))

done

[hs@master script_generate]$

2、生成sor-rds数据库建表脚本
[hs@master script_generate]$ cat sor-close2source_create_rdstab_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script
if [ ! -d /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script ];then
mkdir -p /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script
fi

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

echo -e `mysql -h$db_ip -u$user_name -p$passwd -N -e"set session group_concat_max_len=20000000;
select CONCAT('use sor;
drop table if exists \\\`',table_name,'\\\`;
create table \\\`',table_name,'\\\`(',
GROUP_CONCAT('\n\\\`',COLUMN_NAME,'\\\` ',DATA_TYPE,''),
') ENGINE=InnoDB DEFAULT CHARSET=utf8;')
from (
select
COLUMN_NAME,'$rds_map_tab_name' table_name,
case when DATA_TYPE in('int','bigint','mediumint','smallint','tinyint') and (COLUMN_NAME not like '%time' and COLUMN_NAME not like '%date') then 'bigint' when DATA_TYPE in('varchar','char') then CONCAT('varchar','(',CHARACTER_MAXIMUM_LENGTH*1,')') when DATA_TYPE in('decimal') then CONCAT('decimal','(',NUMERIC_PRECISION*1,',',NUMERIC_SCALE*1,')') when DATA_TYPE in('text','enum') then 'text' when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then 'timestamp' else DATA_TYPE end data_type
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name') a1;"` > /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script/$tab_name.sql

: $(( i++ ))

done

#sed -i "s/'n'/'\\\n'/g" `grep "lines terminated by" -rl /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script`
[hs@master script_generate]$

3、生成sor数据从hive向mysql传输脚本
[hs@master script_generate]$ cat sor-close2source_create_hive2mysql_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`

rm -rf /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh
echo "#!/bin/bash
#export yesterday=\`date -d last-day +%Y-%m-%d\`
export yesterday=\$1

huanhang#userpass info get ...huanhangdwsrc_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src_read\`huanhangdwsrc_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src_read\`huanhangdwsrc_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src_read\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhanghuanhang" > /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh

#hive load data sync mysql script generate ...
i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list)
do

col_num=$i

if [ $load_type == 'increment' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table current all data increment download from hive and upload to sor rds...huanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc start ... >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use sor;insert overwrite local directory \'/home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,' where timeline >= \'\$yesterday 00:00:00\';\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;drop table if exists ','$rds_map_tab_name','_today;create table ','$rds_map_tab_name','_today as select * from sor.','$rds_map_tab_name',' where 1=2;\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;load data local infile \'\$tabdt_path\' into table ','$rds_map_tab_name','_today fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhangdonehuanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;alter table tmp.','$rds_map_tab_name','_today add index idx_','$rds_map_tab_name','_',k.COLUMN_NAME,' (',k.COLUMN_NAME,') using btree;delete ca.* from sor.','$rds_map_tab_name',' ca left join tmp.','$rds_map_tab_name','_today i on ca.',k.COLUMN_NAME,' = i.',k.COLUMN_NAME,' where i.',k.COLUMN_NAME,' is not null;insert into sor.','$rds_map_tab_name',' select * from tmp.','$rds_map_tab_name','_today;\"huanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc finished ! >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhanghuanhanghuanhang') sql_text
from information_schema.TABLES t
left join information_schema.KEY_COLUMN_USAGE k
on t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME and k.CONSTRAINT_name='PRIMARY'
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh

elif [ $load_type == 'total' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table data download from hive and upload to sor rds...huanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc start ... >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use sor;insert overwrite local directory \'/home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,';\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;truncate table ','$rds_map_tab_name',';\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;load data local infile \'\$tabdt_path\' into table ','$rds_map_tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhangdonehuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc finished ! >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhanghuanhanghuanhang') sql_text
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh

elif [ $load_type == 'addto' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table data download from hive and upload to sor rds...huanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc start ... >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use sor;insert overwrite local directory \'/home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,' where timeline >= \'\$yesterday 00:00:00\';\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;delete from ','$rds_map_tab_name',' where timeline >= \'\$yesterday 00:00:00\';\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;load data local infile \'\$tabdt_path\' into table ','$rds_map_tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhangdonehuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,sor-hive2mysql $tab_name data $load_type proc finished ! >> /home/hs/opt/dw-etl/run_log_dir/sor-hive2mysql_run.loghuanhanghuanhanghuanhang') sql_text
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh

: $(( i++ ))

done

#script file final proc ...
sed -i 's/huanhang/\n/g' /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh
chmod 777 /home/hs/opt/dw-etl/etl-script/sor-hive2mysql/sor-hive2mysql_close2source.sh
[hs@master script_generate]$

六、生成超级表一体化初始脚本
[hs@master script_generate]$ cat bigtable-create_allstage_suppertab_int_script.sh
#!/bin/bash
export today=`date +%Y-%m-%d`
export yesterday=`date -d last-day +%Y-%m-%d`
export beforeytd=`date -d "2 days ago" +%Y-%m-%d`
export ytd=${yesterday//-/}

rm -rf /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/
mkdir -p /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/

echo -e "#!/bin/bashhuanhang" > /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh

#cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list |grep increment|grep -i '99_order|\|99_orderfood|\|99_dormitem|' > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list
cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list |grep increment|grep -i '99_order|' > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
do

col_num=$i

tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list)

if [ ! -d /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name ];then
mkdir -p /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name
fi

rm -rf /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/suppertable_createtab_$tab_name.sh

echo -e "#!/bin/bash
export today=\`date +%Y-%m-%d\`
export yesterday=\`date -d last-day +%Y-%m-%d\`
export beforeytd=\`date -d \"2 days ago\" +%Y-%m-%d\`
export ytd=\${yesterday//-/}

#1.update manual_update_tab.list info ..
echo $tab_name >/home/hs/opt/dw-etl/manual_proc/manual_update_tab.list

#2.update generate all table create and etl script ...
sh /home/hs/opt/dw-etl/script_generate/etl_table_list_update.sh

sh /home/hs/opt/dw-etl/script_generate/src-create_rdstab_script.sh
sh /home/hs/opt/dw-etl/script_generate/ssa-create_hivetab_script.sh
sh /home/hs/opt/dw-etl/script_generate/sor-close2source_create_rdstab_script.sh

sh /home/hs/opt/dw-etl/script_generate/src-create_src2dwsrc_script.sh
sh /home/hs/opt/dw-etl/script_generate/ssa-create_dwsrc2ssa_script.sh
sh /home/hs/opt/dw-etl/script_generate/sor-close2source_data_pull_from_ssa_script.sh
sh /home/hs/opt/dw-etl/script_generate/sor-close2source_create_hive2mysql_script.sh

sh /home/hs/opt/dw-etl/script_generate/bigtable-create_allstage_suppertab_int_script.sh
sh /home/hs/opt/dw-etl/script_generate/rds-increment_tab_should_create_idx_sql.sh

#3.srcdw-rds,ssa-hive,sor-rds create table ...
while read tab_name
do
export currtime=\`date \"+%Y-%m-%d %H:%M:%S\"\`

cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list |grep -w \$tab_name > /home/hs/opt/dw-etl/manual_proc/table_info_only.list

tab_name=\$(awk -F \"|\" 'NR==1 {print \$1}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
src_dbname=\$(awk -F \"|\" 'NR==1 {print \$2}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
src_dbip=\$(awk -F \"|\" 'NR==1 {print \$3}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
rds_map_tab_name=\$(awk -F \"|\" 'NR==1 {print \$4}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
load_type=\$(awk -F \"|\" 'NR==1 {print \$5}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
src_user=\$(awk -F \"|\" 'NR==1 {print \$6}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)
src_pass=\$(awk -F \"|\" 'NR==1 {print \$7}' /home/hs/opt/dw-etl/manual_proc/table_info_only.list)

dw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`
dw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`
dw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`

mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -D src < \"/home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script/\"\$tab_name\".sql\"

hive -f \"/home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script/\"\$tab_name\".hql\"

mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -D sor < \"/home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script/\"\$tab_name\".sql\"

#table is updated log info record ...
echo \"current time:\"\$currtime,\$tab_name\" is updated!\" >> /home/hs/opt/dw-etl/manual_proc/table_manual_isupdated.log

done < /home/hs/opt/dw-etl/manual_proc/manual_update_tab.list

#delete temp script file ...
rm -rf /home/hs/opt/dw-etl/manual_proc/table_info_only.list" > /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/suppertable_createtab_$tab_name.sh

rm -rf /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/src2dwsrc_$tab_name.sh

for (( j=0;j<=9;j++ ))
do
mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('huanhang#$tab_name total data of $((j+1)) download from src ...huanhang/usr/local/bin/mysql -h\$src_dbip -u\$src_user -p\$src_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when c.data_type in ('varchar','char') then concat('replace(replace(replace(\\\\\`',c.COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') else concat('\\\\\`',c.COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from ',c.TABLE_SCHEMA,'.',c.table_name,' where substr(',k.column_name,',-1,1) = $j ;',''
,'\" >>/home/hs/opt/dw-etl/data/src2dwsrc_data/',c.TABLE_SCHEMA,'.',c.table_name,'.dathuanhang'
,'') mysql_export_and_dwrds_load_shell
from information_schema.COLUMNS c left join information_schema.KEY_COLUMN_USAGE k on c.TABLE_SCHEMA=k.TABLE_SCHEMA and c.TABLE_NAME=k.TABLE_NAME and c.COLUMN_NAME=k.COLUMN_NAME and k.CONSTRAINT_name='PRIMARY'
where c.TABLE_SCHEMA='$db_name' and c.table_name='$tab_name';" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/src2dwsrc_$tab_name.sh

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('huanhang#$tab_name total data of $((j+1)) download from dw-src ...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when c.data_type in ('varchar','char') then concat('replace(replace(replace(\\\\\`',c.COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (c.COLUMN_NAME like '%time' or c.COLUMN_NAME like '%date') and c.data_type like '%int' then concat('from_unixtime(\\\\\`',c.COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',c.COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from src.',c.table_name,' where substr(',k.column_name,',-1,1) = $j ;',''
,'\" >>/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',c.TABLE_SCHEMA,'.',c.table_name,'.dathuanhang'
,'') mysql_export_and_dwrds_load_shell
from information_schema.COLUMNS c left join information_schema.KEY_COLUMN_USAGE k on c.TABLE_SCHEMA=k.TABLE_SCHEMA and c.TABLE_NAME=k.TABLE_NAME and c.COLUMN_NAME=k.COLUMN_NAME and k.CONSTRAINT_name='PRIMARY'
where c.TABLE_SCHEMA='$db_name' and c.table_name='$tab_name';" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/dwsrc2ssa_$tab_name.sh
done

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#','$tab_name',' table total data upload to dw-src ...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use src;truncate table ','$tab_name',';\"huanhang#load data file to dw-rds huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use src;load data local infile \'/home/hs/opt/dw-etl/data/src2dwsrc_data/','$db_name','.','$tab_name','.dat\' into table ','$tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-src2dwsrc $tab_name supper table data $load_type int proc finished! >> /home/hs/opt/dw-etl/run_log_dir/src-src2dwsrc_run.loghuanhang') mysql_export_and_dwrds_load_shell
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/src2dwsrc_$tab_name.sh

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#','$tab_name',' table total data upload to ssa on hive ...huanhang','/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/dwsrc2ssa_data/',TABLE_SCHEMA,'.',table_name,'.dat\' overwrite into table ',table_name,';\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,src-dwsrc2ssa $tab_name data $load_type int load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/ssa-dwsrc2ssa_run.loghuanhang') mysql_export_and_dwrds_load_shell
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/dwsrc2ssa_$tab_name.sh

#sor data pull on hive and hive to mysql
echo -e "#!/bin/bash
#export yesterday=\`date -d last-day +%Y-%m-%d\`
export yesterday=\$1

huanhang#userpass info get ...huanhangdwsrc_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src_read\`huanhangdwsrc_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src_read\`huanhangdwsrc_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src_read\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhang
" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/sor_proc_hive+mysql_$tab_name.sh

#get sor data from ssa
echo -e "#get sor data from ssahuanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"drop table if exists sor.$tab_name;create table sor.$tab_name as select * from ssa.$tab_name a1;\"huanhang" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/sor_proc_hive+mysql_$tab_name.sh

#sor data hive to mysql
mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table data download from hive and upload to sor rds...huanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use sor;insert overwrite local directory \'/home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,';\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;truncate table ','$rds_map_tab_name',';\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/data/sor-hive2mysql_data/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;load data local infile \'\$tabdt_path\' into table ','$rds_map_tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhangdonehuanhang') sql_text
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/sor_proc_hive+mysql_$tab_name.sh

sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/src2dwsrc_$tab_name.sh
sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/dwsrc2ssa_$tab_name.sh

echo -e "sh /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/suppertable_createtab_$tab_name.sh" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh
echo -e "sh /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/src2dwsrc_$tab_name.sh" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh
echo -e "sh /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/dwsrc2ssa_$tab_name.sh" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh
echo -e "sh /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/sor_proc_hive+mysql_$tab_name.sh" >> /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh

sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/`
sed -i "s/huanhang/\n/g" /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh
sed -i "s/huanhang/\n/g" /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/sor_proc_hive+mysql_$tab_name.sh

chmod 750 /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/*_$tab_name.sh
chmod 750 /home/hs/opt/dw-etl/bigtab_int/suppertable_int_batch.sh

: $(( i++ ))

done

#sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/bigtab_int/suppertable_int_script/$db_name/`

rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass-for-suppertable-int.list

[hs@master script_generate]$

七、qrt准实时数据处理
1、生成创建rds准实时数据库表脚本
[hs@master script_generate]$ cat qrt-create_rdstab_script.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

#script dir int ...
rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script
mkdir -p /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
do

col_num=$i

tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)

echo -e `mysql -h$db_ip -u$user_name -p$passwd -N -e"set session group_concat_max_len=20000000;
select CONCAT('use qrt;
drop table if exists \\\`',table_name,'\\\`;
create table \\\`',table_name,'\\\`(',
GROUP_CONCAT('\n\\\`',COLUMN_NAME,'\\\` ',DATA_TYPE,''),
') ENGINE=InnoDB DEFAULT CHARSET=utf8;')
from (
select
COLUMN_NAME,'$rds_map_tab_name' table_name,
case when DATA_TYPE in('int','bigint','mediumint','smallint','tinyint') and (COLUMN_NAME not like '%time' and COLUMN_NAME not like '%date') then 'bigint' when DATA_TYPE in('varchar','char') then CONCAT('varchar','(',CHARACTER_MAXIMUM_LENGTH*1,')') when DATA_TYPE in('decimal') then CONCAT('decimal','(',NUMERIC_PRECISION*1,',',NUMERIC_SCALE*1,')') when DATA_TYPE in('text','enum') then 'text' when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then 'timestamp' else DATA_TYPE end data_type
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name') a1;"` > /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script/$tab_name.sql

: $(( i++ ))

done

#sed -i "s/'n'/'\\\n'/g" `grep "lines terminated by" -rl /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script`
[hs@master script_generate]$

2、生成qrt库从src准实时数据拉取脚本
[hs@master script_generate]$ cat qrt-create_src2qrt_script.sh
#!/bin/bash
export today=`date +%Y-%m-%d`
export yesterday=`date -d last-day +%Y-%m-%d`
export beforeytd=`date -d "2 days ago" +%Y-%m-%d`
export ytd=${yesterday//-/}

rm -rf /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)
do

col_num=$i

if [ ! -d /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name ];then
mkdir -p /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name
fi

rm -rf /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

if [ $load_type == 'total' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,qrt-src2dwqrt $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang#userpass info get ...huanhangsrc_dbip=\`sh /home/hs/opt/public-function/getsrcinfo.fc dbip $tab_name\`huanhangsrc_user=\`sh /home/hs/opt/public-function/getsrcinfo.fc user $tab_name\`huanhangsrc_pass=\`sh /home/hs/opt/public-function/getsrcinfo.fc pass $tab_name\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip qrt\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user qrt\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass qrt\`huanhanghuanhang#$tab_name total data download from src ...huanhang/usr/local/bin/mysql -h\$src_dbip -u\$src_user -p\$src_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from ',TABLE_SCHEMA,'.',table_name,';'
,'\" >/home/hs/opt/dw-etl/data/qrt_data/',TABLE_SCHEMA,'.',table_name,'_rt.dathuanhang'
,'') mysql_export_and_hive_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('huanhang#','$rds_map_tab_name',' table data download from src and upload to qrt rds...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use qrt;truncate table ','$rds_map_tab_name',';\"huanhang#loop load dir file to rds huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use qrt;load data local infile \'/home/hs/opt/dw-etl/data/qrt_data/','$db_name','.','$tab_name','_rt.dat\' into table ','$rds_map_tab_name',' fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,qrt-src2dwqrt $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang') sql_text
from information_schema.TABLES t
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

elif [ $load_type == 'increment' ];then

echo $tab_name,data is increment!

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,qrt-src2dwqrt $tab_name data $load_type load proc start ... >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang#userpass info get ...huanhangsrc_dbip=\`sh /home/hs/opt/public-function/getsrcinfo.fc dbip $tab_name\`huanhangsrc_user=\`sh /home/hs/opt/public-function/getsrcinfo.fc user $tab_name\`huanhangsrc_pass=\`sh /home/hs/opt/public-function/getsrcinfo.fc pass $tab_name\`huanhanghuanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip qrt\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user qrt\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass qrt\`huanhanghuanhang#fetch maxtime from dw-src database ...huanhangexport last_maxtime=\`/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -N -e \"use qrt;select max(timeline) max_time from qrt.','$rds_map_tab_name',';\"\`huanhanghuanhangif [[ \$last_maxtime = \'NULL\' ]]; thenhuanhang last_maxtime=\'1970-01-01 00:00:00\'huanhangelsehuanhang last_maxtime=\$last_maxtimehuanhangfihuanhanghuanhang#$tab_name total data download from src ...huanhang/usr/local/bin/mysql -h\$src_dbip -u\$src_user -p\$src_pass -N -e\"'
,'select CONCAT(',trim(TRAILING ',\'|\'' FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') then concat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')') when (COLUMN_NAME like '%time' or COLUMN_NAME like '%date') and data_type like '%int' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from ',TABLE_SCHEMA,'.',table_name,' where timeline >= \'\$last_maxtime\';'
,'\" >/home/hs/opt/dw-etl/data/qrt_data/',TABLE_SCHEMA,'.',table_name,'_rt.dathuanhang'
,'') mysql_export_and_hive_load_shell
from information_schema.COLUMNS
where TABLE_SCHEMA='$db_name' and table_name='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('#','$rds_map_tab_name',' table current all data increment download from hive and upload to qrt rds...huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;drop table if exists ','$rds_map_tab_name','_minutes;create table ','$rds_map_tab_name','_minutes as select * from qrt.','$rds_map_tab_name',' where 1=2;\"huanhang#loop load dir file to rds huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;load data local infile \'/home/hs/opt/dw-etl/data/qrt_data/','$db_name','.','$tab_name','_rt.dat\' into table ','$rds_map_tab_name','_minutes fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhang/usr/local/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;alter table tmp.','$rds_map_tab_name','_minutes add index idx_','$rds_map_tab_name','_',k.COLUMN_NAME,' (',k.COLUMN_NAME,') using btree;delete ca.* from qrt.','$rds_map_tab_name',' ca left join tmp.','$rds_map_tab_name','_minutes i on ca.',k.COLUMN_NAME,' = i.',k.COLUMN_NAME,' where i.',k.COLUMN_NAME,' is not null;insert into qrt.','$rds_map_tab_name',' select * from tmp.','$rds_map_tab_name','_minutes;\"huanhanghuanhangecho -e \`date +\\\"%Y-%m-%d %H:%M:%S\\\"\`,qrt-src2dwqrt $tab_name data $load_type load proc finished! >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang') sql_text
from information_schema.TABLES t
left join information_schema.KEY_COLUMN_USAGE k
on t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME and k.CONSTRAINT_name='PRIMARY'
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/`

chmod 750 /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh

: $(( i++ ))

done

#genrate batch script
echo -e "#!/bin/bash
export yesterday=\`date -d last-day +%Y%m%d\`

#src2qrt data proc ......
echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,shell script exec start ................................................................ >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log
for src_db in /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/db59*;
do
echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,start exec \$src_db shell script... >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log
for src2qrt_script in \$src_db/*.sh;
do
   echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,start exec [\$src2qrt_script] shell script... >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log
   sh \$src2qrt_script
   echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,[\$src2qrt_script] shell script exec finished! >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log
done
echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,\$src_db shell script exec finished! >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log
done
echo -e \"\\\n\\\n\\\n\\\n\\\n\" >> /home/hs/opt/dw-etl/run_log_dir/qrt-src2dwqrt_run.log" > /home/hs/opt/dw-etl/etl-script/qrt/qrt-hour_batch.sh

chmod 750 /home/hs/opt/dw-etl/etl-script/qrt/qrt-hour_batch.sh

#sed -i "s/huanhang/\n/g" `grep "mysql" -rl /home/hs/opt/dw-etl/etl-script/qrt/src2qrt_script/$db_name/`

[hs@master script_generate]$

八、数据库建表统一执行脚本
1、dwsrc建库执行
[hs@master script_generate]$ cat exec_rdssrc_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

dw_dbip=`sh /home/hs/opt/public-function/getdwinfo.fc dbip src`
dw_user=`sh /home/hs/opt/public-function/getdwinfo.fc user src`
dw_pass=`sh /home/hs/opt/public-function/getdwinfo.fc pass src`

#cd /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script
for create_tab_script in /home/hs/opt/dw-etl/script_generate/script_dir/rds-src_create_tab_script/*.sql
do
mysql -h$dw_dbip -u$dw_user -p$dw_pass -D src < $create_tab_script
done

[hs@master script_generate]$

2、hive-ssa建库执行
[hs@master script_generate]$ cat exec_hivessa_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

cd /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script
for create_tab_script in *.hql
do
hive -f $create_tab_script
done

[hs@master script_generate]$

3、rds-sor贴源表建库执行
[hs@master script_generate]$ cat exec_hivessa_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

cd /home/hs/opt/dw-etl/script_generate/script_dir/hive-ssa_create_tab_script
for create_tab_script in *.hql
do
hive -f $create_tab_script
done

[hs@master script_generate]$ cat exec_rdssor_close2source_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

cd /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script
for create_tab_script in /home/hs/opt/dw-etl/script_generate/script_dir/rds-sor_close2source_create_tab_script/*.sql
do
mysql -hdwipaddress.mysql.rds.aliyuncs.com -udwdatauser -ppasswordchar -D sor < $create_tab_script
done

[hs@master script_generate]$

4、rds-qrt表建库执行
[hs@master script_generate]$ cat exec_rdssor_close2source_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

[hs@master script_generate]$ cat exec_rdsqrt_create_tab.sh
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

cd /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script
for create_tab_script in /home/hs/opt/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script/*.sql
do
mysql -hdwipaddress.mysql.rds.aliyuncs.com -udwdatauser -ppasswordchar -D qrt < $create_tab_script
done

[hs@master script_generate]$

九、其他独立生成脚本
1、rds数据抽取需要索引生成脚本
[hs@master script_generate]$ cat rds-increment_tab_should_create_idx_sql.sh
#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`

cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list |grep increment > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list

rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

#echo -e "primary\ntimeline" > /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/index_type.list
#for index_type in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/index_type.list)
index_type_list="primary timeline"
for index_type in $index_type_list
do

echo -e \# $index_type type index sql create start ... >> /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

#rds increment load table should create index script generate ...
i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
do

col_num=$i

tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list)

if [ $index_type == 'primary' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('use src;alter table src.',t.TABLE_NAME,' add index idx_',t.TABLE_NAME,'_',k.COLUMN_NAME,' (',k.COLUMN_NAME,') using btree;') sql_text
from information_schema.TABLES t
left join information_schema.KEY_COLUMN_USAGE k
on t.TABLE_SCHEMA=k.TABLE_SCHEMA and t.TABLE_NAME=k.TABLE_NAME and k.CONSTRAINT_name='PRIMARY'
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';" >> /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

elif [ $index_type == 'timeline' ];then

mysql -h$db_ip -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;
select
concat('use src;alter table src.',t.TABLE_NAME,' add index idx_',t.TABLE_NAME,'_',c.COLUMN_NAME,' (',c.COLUMN_NAME,') using btree;') sql_text
from information_schema.TABLES t
left join information_schema.COLUMNS c
on t.TABLE_SCHEMA=c.TABLE_SCHEMA and t.TABLE_NAME=c.TABLE_NAME
where t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name' and c.COLUMN_NAME='timeline';" >> /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

: $(( i++ ))

done

echo -e \\n >> /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

done

#script file final proc ...
sed -i 's/huanhang/\n/g' /home/hs/opt/dw-etl/script_generate/script_dir/rds-increment_tab_should_create_idx.sql

#delete temp list file
rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/rds-batch_table_userpass-for-create_index.list
[hs@master script_generate]$

2、源表结构变化比对脚本
[hs@master script_generate]$ cat tabsrc_strurecord_script.sh
#!/bin/bash
export today=`date +%Y-%m-%d`
export yesterday=`date -d last-day +%Y-%m-%d`
export beforeytd=`date -d "2 days ago" +%Y-%m-%d`
export ytd=${yesterday//-/}

rm -rf /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh
echo -e "#!/bin/bashhuanhanghuanhangexport yesterday=\`date -d last-day +%Y-%m-%d\`huanhangrm -rf /home/hs/opt/dw-etl/tabsrc_strurecord/db59src_\$yesterday.dathuanhang" >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh
echo -e "huanhang#dw userpass info get ...huanhangdw_dbip=\`sh /home/hs/opt/public-function/getdwinfo.fc dbip src\`huanhangdw_user=\`sh /home/hs/opt/public-function/getdwinfo.fc user src\`huanhangdw_pass=\`sh /home/hs/opt/public-function/getdwinfo.fc pass src\`huanhang" >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

i=1

for tab in $(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list)
do

col_num=$i

db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list)
user_name=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list)
passwd=$(awk -F "|" 'NR=='$col_num' {print $4}' /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list)

echo -e "#$db_name src tab stru data pull ..." >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

echo -e "huanhang#src uerpass info get ...huanhangsrc_dbip=\`sh /home/hs/opt/public-function/getsrcinfo_from_db.fc dbip $db_name\`huanhangsrc_user=\`sh /home/hs/opt/public-function/getsrcinfo_from_db.fc user $db_name\`huanhangsrc_pass=\`sh /home/hs/opt/public-function/getsrcinfo_from_db.fc pass $db_name\`huanhanghuanhang" >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

echo -e "/usr/local/bin/mysql -h\$src_dbip -u\$src_user -p\$src_pass -N -e\"select huanhangCONCAT(huanhang'\$src_dbip' ,'|',huanhangifnull(replace(replace(replace(a1.TABLE_SCHEMA,char(13),''),char(10),''),'|',''),'') ,'|',huanhangifnull(replace(replace(replace(a1.TABLE_NAME,char(13),''),char(10),''),'|',''),'') ,'|',huanhangifnull(replace(replace(replace(a1.TABLE_COMMENT,char(13),''),char(10),''),'|',''),'') ,'|',huanhangifnull(a1.TABLE_TYPE,'') ,'|',huanhangifnull(a1.fanxiegang\`ENGINEfanxiegang\`,'') ,'|',huanhangifnull(a1.CREATE_TIME,'') ,'|',huanhangifnull(a1.UPDATE_TIME,'') ,'|',huanhangifnull(a1.TABLE_COLLATION,'') ,'|',huanhangifnull(replace(replace(replace(a2.COLUMN_NAME,char(13),''),char(10),''),'|',''),'') ,'|',huanhangifnull(replace(replace(replace(a2.COLUMN_COMMENT,char(13),''),char(10),''),'|',''),'') ,'|',huanhangifnull(a2.ORDINAL_POSITION,'') ,'|',huanhangifnull(a2.COLUMN_TYPE,'') ,'|',huanhang\${yesterday//-/} ,'|',huanhangNOW()) src_tab_stru_infohuanhangfrom information_schema.fanxiegang\`TABLESfanxiegang\` a1 huanhangleft join information_schema.fanxiegang\`COLUMNSfanxiegang\` a2 on a1.TABLE_SCHEMA=a2.TABLE_SCHEMA and a1.TABLE_NAME=a2.TABLE_NAME huanhangwhere a1.TABLE_SCHEMA = '$db_name'; huanhang\" >> /home/hs/opt/dw-etl/tabsrc_strurecord/db59src_\$yesterday.dathuanhang" >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

: $(( i++ ))

done

echo -e "/usr/local/mysql/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use sor;delete from src_tab_stru_info where data_date=\${yesterday//-/};load data local infile '/home/hs/opt/dw-etl/tabsrc_strurecord/db59src_\$yesterday.dat' into table src_tab_stru_info fields terminated by '|' enclosed by '' lines terminated by '\\\n' ignore 0 lines;\"huanhanghuanhang/usr/local/mysql/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use dm;call p_srctab_change_info(59);\"huanhanghuanhangpython /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_change_sendmail.py" >> /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

sed -i "s/\\\\\`/\\\`/g" /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

sed -i "s/fanxiegang/\\\\/g" /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

sed -i "s/huanhang/\n/g" /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

chmod 750 /home/hs/opt/dw-etl/tabsrc_strurecord/srctab_stru_pull.sh

[hs@master script_generate]$

十、获取参数信息的公共shell函数信息
[hs@master public-function]$ pwd
/home/hs/opt/public-function
[hs@master public-function]$ cat getdwinfo.fc
#!/bin/bash

#usage and parameter definition ...
usage="Usage: `basename $0` (dbip|user|pass|all) dbname"
command=$1
first=$2

#function info ...
function dbip() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/dw_dbuserpass_info.list|grep $first\$ |awk -F "|" 'NR==1 {print $1}')
        echo $result
}
function user() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/dw_dbuserpass_info.list|grep $first\$ |awk -F "|" 'NR==1 {print $2}')
        echo $result
}
function pass() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/dw_dbuserpass_info.list|grep $first\$ |awk -F "|" 'NR==1 {print $3}')
        echo $result
}

#function call ...
case $command in
(dbip)
    dbip
    ;;
(user)
    user
    ;;
(pass)
    pass
    ;;
(all)
    dbip
    user
    pass
    ;;
(*)
    echo "Error command"
    echo "$usage"
    ;;
esac
[hs@master public-function]$ cat getsrcinfo.fc
#!/bin/bash

#function info ...
function dbname() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $2}')
        echo $result
}
function dbip() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $3}')
        echo $result
}
function rdstabname() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $4}')
        echo $result
}
function loadtype() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $5}')
        echo $result
}
function user() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $6}')
        echo $result
}
function pass() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/batch_table_userpass.list|grep ^$first\| |awk -F "|" 'NR==1 {print $7}')
        echo $result
}

#function call ...
case $command in
(dbname)
    dbname
    ;;
(dbip)
    dbip
    ;;
(rdstabname)
    rdstabname
    ;;
(loadtype)
    loadtype
    ;;
(user)
    user
    ;;
(pass)
    pass
    ;;
(all)
    dbname
    dbip
    rdstabname
    loadtype
    user
    pass
    ;;
(*)
    echo "Error command"
    echo "$usage"
    ;;
esac
[hs@master public-function]$ cat getsrcinfo_from_db.fc
#!/bin/bash

#usage and parameter definition ...
usage="Usage: `basename $0` (dbname|dbip||user|pass|all) dbname"
command=$1
first=$2

#function info ...
function dbname() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list|grep $first\| |awk -F "|" 'NR==1 {print $2}')
        echo $result
}
function dbip() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list|grep $first\| |awk -F "|" 'NR==1 {print $1}')
        echo $result
}
function user() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list|grep $first\| |awk -F "|" 'NR==1 {print $3}')
        echo $result
}
function pass() {
        result=$(cat /home/hs/opt/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list|grep $first\| |awk -F "|" 'NR==1 {print $4}')
        echo $result
}

#function call ...
case $command in
(dbname)
    dbname
    ;;
(dbip)
    dbip
    ;;
(user)
    user
    ;;
(pass)
    pass
    ;;
(all)
    dbname
    dbip
    user
    pass
    ;;
(*)
    echo "Error command"
    echo "$usage"
    ;;
esac
[hs@master public-function]$

【后记说明】
1、version3.0的数据流
每天批量传输数据，首先从源系统不作任何变化的抽取到rds-dw库的src层（这个src层实际上可以独立出来，单独放在一个数据库上）；然后将数据导出成文件装载到hive库的ssa层，在这一层只是对源数据的日期格式作了一定程序的转换，每日数据全量表清空装载，增量表直接追加；然后，在hive内部将ssa数据进行去重等操作，装载到hive的sor层；之后将sor数据转移到mysql数据，供第三层dpa进行取数据及使用。
准实时数据，不经过hive数据库的中转，直接从src装载到qrt库中，暂定每一小时更新一次。
2、与以往版本相比更新的内容
生成的脚本都以参数形式传递数据库实例、用户名、密码信息，避免了脚本密码私密信息泄露；添加了超级表数据初始化脚本自动生成功能；生成的脚本中，使结构更趋合理，日志单独文件夹存放、临时数据文件单独文件夹存放等。

BabyFish13

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
大数据平台用于生成数据跑批脚本的脚本(version3.0)

一、脚本文件路径[hs@master script_generate]$ pwd/home/hs/opt/dw-etl/script_generate [hs@master script_generate]$ tree -f.├── ./batch_table.list├── ./bigtable-create_allstage_suppertab_int_sc
复制链接

扫一扫