大数据平台用于生成数据跑批脚本的脚本

1、脚本的目录结构
/home/hs/opt/dw-etl/script_generate
│  exec_hivessa_create_tab.sh
│  exec_rdsssa_create_tab.sh
│  rds-increment_tab_should_create_idx_sql.sh
│  ssa-create_hive2mysql_increment_int_script.sh
│  ssa-create_hive2mysql_script.sh
│  ssa-create_hivetab_script.sh
│  ssa-create_rdstab_script1.sh
│  ssa-create_rdstab_script2.sh
│  ssa-create_src2ssa_increment_int_script.sh
│  ssa-create_src2ssa_increment_script.sh
│  ssa-create_src2ssa_total_script.sh
│  ssa-hive_increment_data_rollback.sh
│  ssa-increment_data_create_current_all_script.sh
│  table_create.list
│  table_create_increment.list
│  table_create_total.list
│
└─script_dir
    │  rds-increment_tab_should_create_idx.sql
    │  second_day_ssa_data_rollback.hql
    │  ssa-hive_increment_data_rollback.hql
    │
    ├─hive-ssa_create_tab_script
    │
    └─rds-ssa_create_tab_script
   
2、脚本生成的依照
所有的脚本生成都依据如下三个脚本进行，其中文本存储的格式为“ 表名|库名|实例地址”；其中， table_create.list可以通过“cat table_create_increment.list table_create_total.list > table_create.list ”生成。
cat table_create_increment.list

99_user|db99huanx|rdsipaddress
f_auth_call|db99finance|rdsipaddress
f_auth_contacts|db99finance|rdsipaddress
f_user_device|db99finance|rdsipaddress
gala_questionnaire_record|db99gala|rdsipaddress
c_credit|db99creditmall|rdsipaddress

cat table_create_total.list

99_zone|db99huanx|rdsipaddress
b_order|db99huanx|rdsipaddress
dict|db99finance|rdsipaddress
dict_type|db99finance|rdsipaddress
f_account|db99finance|rdsipaddress

cat table_create.list

99_user|db99huanx|rdsipaddress
f_auth_call|db99finance|rdsipaddress
f_auth_contacts|db99finance|rdsipaddress
f_user_device|db99finance|rdsipaddress
gala_questionnaire_record|db99gala|rdsipaddress
c_credit|db99creditmall|rdsipaddress
99_zone|db99huanx|rdsipaddress
b_order|db99huanx|rdsipaddress
dict|db99finance|rdsipaddress
dict_type|db99finance|rdsipaddress
f_account|db99finance|rdsipaddress

3、生成建表语句的脚本
hive库ssa建表：
ssa-create_hivetab_script.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create.list)

echo -e `mysql -h$db_ip -udbreader -piloveyou -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"\' 
huanxd as textfile;')
from (
select 
COLUMN_NAME,table_name,
case when DATA_TYPE in('int','bigint','mediumint','smallint','tinyint') and COLUMN_NAME not like '%time' then 'bigint' when DATA_TYPE in('varchar','char') then CONCAT(DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH*3,')') when DATA_TYPE in('decimal') then CONCAT('decimal','(',NUMERIC_PRECISION*2,',',NUMERIC_SCALE*2,')') when DATA_TYPE in('text','enum') then 'string' when COLUMN_NAME like '%time' 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`

rds库ssa建（两个，其中脚本1被弃用）：
ssa-create_rdstab_script1.sh

#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create.list)

echo "drop table if exists $tab_name;" > /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_create_tab_script/$tab_name.sql
mysql -h$db_ip -udbreader -piloveyou -N -e"use $db_name;show create table $tab_name \G;" >> /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_create_tab_script/$tab_name.sql
sed -i '2,3s/^/#/'  /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_create_tab_script/$tab_name.sql
sed -i '4s/Create Table: //'  /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_create_tab_script/$tab_name.sql
sed -i '$s/$/;/'  /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_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-ssa_create_tab_script`

ssa-create_rdstab_script2.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create.list)

echo -e `mysql -h$db_ip -udbreader -piloveyou -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,''),
') ENGINE=InnoDB DEFAULT CHARSET=utf8;')
from (
select 
COLUMN_NAME,table_name,
case when DATA_TYPE in('int','bigint','mediumint','smallint','tinyint') and COLUMN_NAME not like '%time' then 'bigint' when DATA_TYPE in('varchar','char') then CONCAT(DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH*3,')') when DATA_TYPE in('decimal') then CONCAT('decimal','(',NUMERIC_PRECISION*2,',',NUMERIC_SCALE*2,')') when DATA_TYPE in('text','enum') then 'text' when COLUMN_NAME like '%time' 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-ssa_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-ssa_create_tab_script`

4、执行建表语句
在hive库：
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

在rds上：
exec_rdsssa_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-ssa_create_tab_script
for create_tab_script in /home/hs/opt/dw-etl/script_generate/script_dir/rds-ssa_create_tab_script/*.sql
do
mysql -hrdsipadress.mysql.rds.aliyuncs.com -udatauser -piloveyou -D ssa < $create_tab_script
done

5、生成从Src取数的脚本
全量取数：
ssa-create_src2ssa_total_script.sh

#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)

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

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

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhang#$tab_name total data proc ...huanhang/usr/local/bin/mysql -h$db_ip -udbreader -piloveyou -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' 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/',TABLE_SCHEMA,'.',table_name,'_total_\$1.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/',TABLE_SCHEMA,'.',table_name,'_total_\$1.dat\' overwrite into table ',table_name,';\"') 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-total/$db_name/src2ssa_$tab_name.sh

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

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

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

chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-total/$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-total/$db_name/`

增量取数：
ssa-create_src2ssa_increment_script.sh

#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

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

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

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhang#$tab_name data proc ...huanhang/usr/local/bin/mysql -h$db_ip -udbreader -piloveyou -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' then concat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') else concat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from ',TABLE_SCHEMA,'.',table_name,' where DATE_FORMAT(update_time,\'%Y%m%d\')=\$1;'
,'\" >/home/hs/opt/dw-etl/data/',TABLE_SCHEMA,'.',table_name,'_\$1.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;load data local inpath \'/home/hs/opt/dw-etl/data/',TABLE_SCHEMA,'.',table_name,'_\$1.dat\' into table ',table_name,';\"') 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-increment/$db_name/src2ssa_$tab_name.sh

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

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

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

chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-increment/$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-increment/$db_name/`

增量取数初始化：
ssa-create_src2ssa_increment_int_script.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

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

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

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select CONCAT('#!/bin/bashhuanhang#$tab_name total(increment data int) data proc ...huanhang/usr/local/bin/mysql -h$db_ip -udbreader -piloveyou -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' 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/',TABLE_SCHEMA,'.',table_name,'_total_\$1.dathuanhang'
,'/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;truncate table ',table_name,';load data local inpath \'/home/hs/opt/dw-etl/data/',TABLE_SCHEMA,'.',table_name,'_total_\$1.dat\' into table ',table_name,';\"') 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-increment-int/$db_name/src2ssa_$tab_name.sh

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

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

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

chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-increment-int/$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-increment-int/$db_name/`

6、生成ssa层数据向mysql传送的脚本
日常传送：
ssa-create_hive2mysql_script.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`

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

" > /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh

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

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

mysql -h$db_ip -udbreader -piloveyou -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 ssa rds...huanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;insert overwrite local directory \'/home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,' where substr(update_time,1,10)=\'\$yesterday\';\"huanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use tmp;drop table if exists ',t.TABLE_NAME,'_today;create table ',t.TABLE_NAME,'_today as select * from ssa.',t.TABLE_NAME,' where 1=2;\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use tmp;load data local infile \'\$tabdt_path\' into table ',t.TABLE_NAME,'_today fields terminated by \'|\' enclosed by \'\' lines terminated by \'\\n\' ignore 0 lines;\"huanhangdonehuanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use tmp;alter table tmp.',t.TABLE_NAME,'_today add index idx_',t.TABLE_NAME,'_',k.COLUMN_NAME,' (',k.COLUMN_NAME,') using btree;delete ca.* from ssa.',t.TABLE_NAME,' ca left join tmp.',t.TABLE_NAME,'_today i on ca.',k.COLUMN_NAME,' = i.',k.COLUMN_NAME,' where i.',k.COLUMN_NAME,' is not null;insert into ssa.',t.TABLE_NAME,' select * from tmp.',t.TABLE_NAME,'_today;\"huanhang') 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/ssa-hive2mysql/ssa-hive2mysql.sh

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

: $(( i++ ))

done

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

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_total.list)

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table data download from hive and upload to ssa rds...huanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;insert overwrite local directory \'/home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,';\"huanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use ssa;truncate table ',t.TABLE_NAME,';\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use ssa;load data local infile \'\$tabdt_path\' into table ',t.TABLE_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/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh

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

: $(( i++ ))

done

#script file final proc ...
sed -i 's/huanhang/\n/g' /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh
chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh

增量传送数据初始化：
ssa-create_hive2mysql_increment_int_script.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`

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

" > /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql_increment_int.sh

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

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select
concat('#',t.TABLE_NAME,' table data download from hive and upload to ssa rds...huanhang/home/hs/opt/hive-1.2.1/bin/hive -e \"use ssa;insert overwrite local directory \'/home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'\' row format delimited fields terminated by \'|\' select * from ',t.TABLE_NAME,';\"huanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use ssa;truncate table ',t.TABLE_NAME,';\"huanhang#loop load dir file to rds huanhangfor tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/',t.TABLE_NAME,'/*;huanhangdohuanhang/usr/local/bin/mysql -rdsipaddress -udatauser -piloveyou -e \"use ssa;load data local infile \'\$tabdt_path\' into table ',t.TABLE_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/etl-script/ssa-hive2mysql/ssa-hive2mysql_increment_int.sh

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

: $(( i++ ))

done

#script file final proc ...
sed -i 's/huanhang/\n/g' /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql_increment_int.sh
chmod 777 /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql_increment_int.sh

7、其他相关生成的脚本
hive上ssa增量数据回滚：
ssa-hive_increment_data_rollback.sh

#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/script_generate/script_dir/ssa-hive_increment_data_rollback.hql

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)


echo "drop table if exists tmp.$tab_name; create table tmp.$tab_name as select * from ssa.$tab_name where substr(update_time,1,10)<=date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),2); insert overwrite table ssa.$tab_name select * from tmp.$tab_name;"  >>/home/hs/opt/dw-etl/script_generate/script_dir/ssa-hive_increment_data_rollback.hql


: $(( i++ ))

done

hive上ssa增量数据生成当前全量数据：
ssa-increment_data_create_current_all_script.sh

#/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`

rm -rf /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql

i=1

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

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select CONCAT('drop table if exists ssa.',a1.TABLE_NAME,'_ca;create table ssa.',a1.TABLE_NAME,'_ca as select * from (select a1.*,row_number() over(partition by a1.',a2.column_name,' order by a1.update_time 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';"  >> /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql

#echo -e \\n >> /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql

: $(( i++ ))

done

rds上增量传送的表应生成索引：
rds-increment_tab_should_create_idx_sql.sh

#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`

rm -rf /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/table_create_increment.list)
do

col_num=$i
tab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)
db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /home/hs/opt/dw-etl/script_generate/table_create_increment.list)

mysql -h$db_ip -udbreader -piloveyou -N -e "set session group_concat_max_len=20000000;
select
concat('use ssa;alter table ssa.',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

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

: $(( i++ ))

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