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

最新推荐文章于 2022-09-27 05:39:17 发布

BabyFish13

最新推荐文章于 2022-09-27 05:39:17 发布

阅读量1.7w

点赞数

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

本文链接：https://blog.csdn.net/babyfish13/article/details/51556527

版权

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

├── ./etl_table_list_update.sh

├── ./exec_hivessa_create_tab.sh

├── ./exec_rdsqrt_create_tab.sh

├── ./exec_rdssor_close2source_create_tab.sh

├── ./qrt-create_rdstab_script.sh

├── ./qrt-create_src2qrt_script.sh

├── ./rds-sor-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_city.hql

│ │ └── .........

│ ├──./script_dir/list_dir

│ │ ├──./script_dir/list_dir/batch_table_userpass.list

│ │ ├──./script_dir/list_dir/dw_dbuserpass_info.list

│ │ ├──./script_dir/list_dir/real_table_userpass.list

│ │ ├──./script_dir/list_dir/src_userpass_info.list

│ │ └──./script_dir/list_dir/table_instance_info.list

│ ├── ./script_dir/rds-qrt_create_tab_script

│ │ ├──./script_dir/rds-qrt_create_tab_script/99_coupon.sql

│ │ ├──./script_dir/rds-qrt_create_tab_script/99_dormdealerpurchaseitem.sql

│ │ └── .........

│ ├──./script_dir/rds-sor_close2source_create_tab_script

│ │ ├──./script_dir/rds-sor_close2source_create_tab_script/99_city.sql

│ │ ├──./script_dir/rds-sor_close2source_create_tab_script/99_coupon.sql

│ │ └── .........

│ ├──./script_dir/rds-sor_increment_tab_should_create_idx.sql

│ ├──./script_dir/rds-ssa_add_comment_script.sql

│ ├──./script_dir/rds-ssa_create_tab_script

│ │ ├──./script_dir/rds-ssa_create_tab_script/99_city.sql

│ │ ├──./script_dir/rds-ssa_create_tab_script/99_coupon.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

├── ./ssa-create_hivetab_script.sh

├── ./ssa-create_src2ssa_increment_int_script.sh

└── ./ssa-create_src2ssa_script.sh

7 directories, 603 files

二、配置文件列表清单及处理脚本

1、批量跑批脚本清单

[hs@master script_generate]$ catbatch_table.list

99_dormteam|db99team|ipaddress.mysql.rds.aliyuncs.com|h_99_dormteam|total

99_dormteamchangelog|db99team|ipaddress.mysql.rds.aliyuncs.com|h_99_dormteamchangelog|total

99_dormteamcombatgains|db99team|ipaddress.mysql.rds.aliyuncs.com|h_99_dormteamcombatgains|total

99_dormteamcombatgainsitem|db99team|ipaddress.mysql.rds.aliyuncs.com|h_99_dormteamcombatgainsitem|total

99_coupon|db99hx|ipaddress.mysql.rds.aliyuncs.com|h_99_coupon|increment

2、准实时跑批脚本清单

[hs@master script_generate]$ catreal_table.list

99_coupon|db99hx|ipaddress.mysql.rds.aliyuncs.com|real_99_coupon|increment

99_dorm|db99hx|ipaddress.mysql.rds.aliyuncs.com|real_99_dorm|total

99_dormdealerpurchase|db99hx|ipaddress.mysql.rds.aliyuncs.com|real_99_dormdealerpurchase|total

99_dormdealerpurchaseitem|db99hx|ipaddress.mysql.rds.aliyuncs.com|real_99_dormdealerpurchaseitem|increment

3、源系统连接用户密码信息

[hs@master list_dir]$ catsrc_userpass_info.list

ipaddress.mysql.rds.aliyuncs.com|dbreader|passwdchar

4、数据仓库连接用户密码信息

[hs@master list_dir]$ catdw_dbuserpass_info.list

ipaddress.mysql.rds.aliyuncs.com|datauser|passwdchar|sor

ipaddress.mysql.rds.aliyuncs.com|datauser|passwdchar|qrt

5、用户跑批清单处理脚本

[hs@master script_generate]$ catetl_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

三、数据仓库第一层SSA脚本生成

数据仓库第一层是基于hive库进行建表语句及数据装载语句的生成。

1、ssa生成hive建表语句的脚本

[hs@master script_generate]$ catssa-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

i=1

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

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_TYPEin('int','bigint','mediumint','smallint','tinyint') and COLUMN_NAME not like'%time' then 'bigint' when DATA_TYPE in('varchar','char') thenCONCAT('varchar','(',CHARACTER_MAXIMUM_LENGTH*3,')') when DATA_TYPEin('decimal') thenCONCAT('decimal','(',NUMERIC_PRECISION*2,',',NUMERIC_SCALE*2,')') whenDATA_TYPE in('text','enum') then 'string' when COLUMN_NAME like '%time' anddata_type = 'int' then 'varchar(19)' else DATA_TYPE end data_type

from information_schema.COLUMNS

where TABLE_SCHEMA='$db_name' andtable_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`

2、ssa生成从源系统拉取数据并装载到hive上的脚本

[hs@master script_generate]$ catssa-create_src2ssa_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

i=1

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

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)

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

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;

selectCONCAT('#!/bin/bashhuanhang#$tab_name total data proc...huanhang/usr/local/bin/mysql -h$db_ip -u$user_name -p$passwd -N -e\"'

,'select CONCAT(',trim(TRAILING ',\'|\''FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') thenconcat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')')when COLUMN_NAME like '%time' and data_type = '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/',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' andtable_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;

selectCONCAT('#!/bin/bashhuanhang#$tab_name data proc ...huanhang/usr/local/bin/mysql-h$db_ip -u$user_name -p$passwd -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' and data_type = 'int' thenconcat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') elseconcat('\\\\\`',COLUMN_NAME,'\\\\\`') end,',\'\')',',\'|\''))) ,') from',TABLE_SCHEMA,'.',table_name,' where timeline between str_to_date(\'\$103:00:00\',\'%Y-%m-%d %H:%i:%s\') and str_to_date(\'\$2 02:59:59\',\'%Y-%m-%d%H:%i:%s\');'

,'\">/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' andtable_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-$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/`

3、ssa源数据增量数据拉取的初始化脚本生成

[hs@master script_generate]$ catssa-create_src2ssa_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-increment-int/

i=1

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

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-ssa-increment-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-ssa-increment-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-ssa-increment-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-ssa-increment-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-ssa-increment-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-ssa-increment-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-ssa-increment-int.list)

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

mkdir -p /home/hs/opt/dw-etl/etl-script/ssa-$load_type-int/$db_name

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

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('#!/bin/bashhuanhang#$tab_nametotal data proc ...huanhang/usr/local/bin/mysql -h$db_ip -u$user_name -p$passwd-N -e\"'

,'select CONCAT(',trim(TRAILING ',\'|\''FROM GROUP_CONCAT(CONCAT('ifnull(',case when data_type in ('varchar') thenconcat('replace(replace(replace(\\\\\`',COLUMN_NAME,'\\\\\`,char(13),\'\'),char(10),\'\'),\'|\',\'\')')when COLUMN_NAME like '%time' and data_type = 'int' thenconcat('from_unixtime(\\\\\`',COLUMN_NAME,'\\\\\`,\'%Y-%m-%d %H:%i:%s\')') elseconcat('\\\\\`',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' andtable_name='$tab_name';" >>/home/hs/opt/dw-etl/etl-script/ssa-$load_type-int/$db_name/src2ssa_$tab_name.sh

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

#echo -e \\n >>/home/hs/opt/dw-etl/etl-script/ssa-$load_type-int/$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-int/$db_name/`

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

: $(( i++ ))

done

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

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

四、数据仓库第二层SOR脚本生成

数据仓库第二层，是在Hive上进行运算的基础上，再将数据从hive传输到rds-mysql数据库。

1、sor贴源数据部分从ssa拉取数据（ssa全量加载的全量拉取，ssa增量摘取的生成全量数据拉取）

[hs@master script_generate]$ catsor-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)

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)

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('drop table if existssor.',a1.TABLE_NAME,';create table sor.',a1.TABLE_NAME,' as select * fromssa.',a1.TABLE_NAME,' a1;' ) current_all_sql

from information_schema.TABLES a1

left joininformation_schema.KEY_COLUMN_USAGE a2

on a1.TABLE_NAME=a2.TABLE_NAME anda1.TABLE_SCHEMA=a2.TABLE_SCHEMA and a2.CONSTRAINT_name='PRIMARY'

where a1.TABLE_SCHEMA ='$db_name' anda1.table_name ='$tab_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 existssor.',a1.TABLE_NAME,';create table sor.',a1.TABLE_NAME,' as select * from(select a1.*,row_number() over(partition by a1.',a2.column_name,' order bya1.timeline desc)as rn from ssa.',a1.TABLE_NAME,' a1) b1 where b1.rn=1;' )current_all_sql

from information_schema.TABLES a1

left joininformation_schema.KEY_COLUMN_USAGE a2

on a1.TABLE_NAME=a2.TABLE_NAME anda1.TABLE_SCHEMA=a2.TABLE_SCHEMA and a2.CONSTRAINT_name='PRIMARY'

where a1.TABLE_SCHEMA ='$db_name' anda1.table_name ='$tab_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

2、sor的rds库贴源部分建表语句的生成

[hs@master script_generate]$ catsor-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