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
/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
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
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
在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
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
全量取数:
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
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
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
日常传送:
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
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
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
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
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