Mysql库表数据随时从生产拉取到数仓(Mysql)的方案(1)

1、程序路径

[root@slave1 dw-etl]# pwd/root/dw-etl[root@slave1 dw-etl]# tree.├── data│   └── qrt_data├── etl-script│   └── qrt│       ├── nohup.out│       ├── qrt-hour_batch.sh│       └── src2qrt_script│           └── jellyfish│               ├── src2qrt_game_video.sh│               ├── src2qrt_space_happening.sh│               ├── src2qrt_time_line_comment.sh│               ├── src2qrt_time_line.sh│               └── src2qrt_time_line_up.sh├── run_log_dir│   └── qrt-src2dwqrt_run.log└── script_generate    ├── etl_table_list_update.sh    ├── exec_rdsqrt_create_tab.sh    ├── qrt-create_rdstab_script.sh    ├── qrt-create_src2qrt_script.sh    ├── real_table.list    └── script_dir        ├── list_dir        │   ├── dw_dbuserpass_info.list        │   ├── real_table_userpass.list        │   └── src_userpass_info.list        └── rds-qrt_create_tab_script            ├── game_video.sql            ├── space_happening.sql            ├── time_line_comment.sql            ├── time_line.sql            └── time_line_up.sql11 directories, 21 files

2、需要提前配置的列表清单及格式

[root@slave1 script_generate]# cat /root/dw-etl/script_generate/real_table.list time_line|jellyfish|121.121.121.159:50506|time_line|totaltime_line_comment|jellyfish|121.121.121.159:50506|time_line_comment|totaltime_line_up|jellyfish|121.121.121.159:50506|time_line_up|totalgame_video|jellyfish|121.121.121.159:50506|game_video|totalspace_happening|jellyfish|121.121.121.159:50506|space_happening|total[root@slave1 list_dir]# cat /root/dw-etl/script_generate/script_dir/list_dir/src_userpass_info.list 121.121.121.159:50506|hadoop|srcpasswd|qrt[root@slave1 list_dir]# cat /root/dw-etl/script_generate/script_dir/list_dir/dw_dbuserpass_info.list 192.168.16.71|root|123|qrt

3、生成脚本的程序
3.1、配置列表更新的Shell

[root@slave1 script_generate]# cat /root/dw-etl/script_generate/etl_table_list_update.sh #!/bin/bash#batch real time script proc ...sort -t "|" -k3,3 /root/dw-etl/script_generate/real_table.list > /root/dw-etl/script_generate/script_dir/list_dir/real_table_sort.listjoin /root/dw-etl/script_generate/script_dir/list_dir/real_table_sort.list /root/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 > /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.listrm -rf /root/dw-etl/script_generate/script_dir/list_dir/real_table_sort.list#get instance info for edit userpass#cat /root/dw-etl/script_generate/real_table.list|awk  -F "|"  '{print $3}'| sort | uniq > /root/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 /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list|awk  -F "|"  '{print $3"|"$2"|"$6"|"$7 }'| sort | uniq > /root/dw-etl/script_generate/script_dir/list_dir/table_schema_info.list[root@slave1 script_generate]# 

3.2、目标表建表语句的生成

[root@slave1 script_generate]# cat /root/dw-etl/script_generate/qrt-create_rdstab_script.sh #!/bin/bash#export yesterday=`date -d last-day +%Y%m%d`#script dir int ...rm -rf /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_scriptmkdir -p /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_scripti=1for tab in $(cat /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)docol_num=$itab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list | awk -F ":" '{print $1}')db_port=$(awk -F "|" 'NR=='$col_num' {print $3}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list | awk -F ":" '{print $2}')rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)echo -e `mysql -h$db_ip -P$db_port -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 (selectCOLUMN_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_typefrom information_schema.COLUMNSwhere TABLE_SCHEMA='$db_name' and table_name='$tab_name') a1;"` > /root/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 /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script`

3.3、目标表建表语句的执行

[root@slave1 script_generate]# cat /root/dw-etl/script_generate/exec_rdsqrt_create_tab.sh #/bin/bashexport yesterday=`date -d last-day +%Y%m%d`cd /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_scriptfor create_tab_script in /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script/*.sqldomysql -h192.168.16.71 -uroot -p123 -D qrt < $create_tab_scriptdone

3.4、数据传输处理脚本生成的Shell

[root@slave1 script_generate]# cat /root/dw-etl/script_generate/qrt-create_src2qrt_script.sh #!/bin/bashexport 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 /root/dw-etl/etl-script/qrt/src2qrt_script/i=1for tab in $(cat /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)docol_num=$itab_name=$(awk -F "|" 'NR=='$col_num' {print $1}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)db_name=$(awk -F "|" 'NR=='$col_num' {print $2}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)db_ip=$(awk -F "|" 'NR=='$col_num' {print $3}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list | awk -F ":" '{print $1}')db_port=$(awk -F "|" 'NR=='$col_num' {print $3}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list | awk -F ":" '{print $2}')rds_map_tab_name=$(awk -F "|" 'NR=='$col_num' {print $4}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)load_type=$(awk -F "|" 'NR=='$col_num' {print $5}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)user_name=$(awk -F "|" 'NR=='$col_num' {print $6}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)passwd=$(awk -F "|" 'NR=='$col_num' {print $7}' /root/dw-etl/script_generate/script_dir/list_dir/real_table_userpass.list)if [ ! -d /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name ];then   mkdir -p /root/dw-etl/etl-script/qrt/src2qrt_script/$db_namefirm -rf /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shif [ $load_type == 'total' ];thenmysql -h$db_ip -P$db_port -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 ... >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang#userpass info get ...huanhangsrc_dbip=\`sh /root/public-function/getsrcinfo.fc dbip $tab_name\`huanhangsrc_user=\`sh /root/public-function/getsrcinfo.fc user $tab_name\`huanhangsrc_pass=\`sh /root/public-function/getsrcinfo.fc pass $tab_name\`huanhanghuanhangdw_dbip=\`sh /root/public-function/getdwinfo.fc dbip qrt\`huanhangdw_user=\`sh /root/public-function/getdwinfo.fc user qrt\`huanhangdw_pass=\`sh /root/public-function/getdwinfo.fc pass qrt\`huanhanghuanhang#$tab_name total data download from src ...huanhang/usr/bin/mysql -h\$src_dbip -P$db_port -u\$src_user -p\$src_pass -N -e\"','set character_set_results=utf8;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,';','\" >/root/dw-etl/data/qrt_data/',TABLE_SCHEMA,'.',table_name,'_rt.dathuanhang','') mysql_export_and_hive_load_shellfrom information_schema.COLUMNSwhere TABLE_SCHEMA='$db_name' and table_name='$tab_name';"  >> /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shmysql -h$db_ip -P$db_port -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;selectconcat('huanhang#','$rds_map_tab_name',' table data download from src and upload to qrt rds...huanhang/usr/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/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use qrt;load data local infile \'/root/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! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang') sql_textfrom information_schema.TABLES twhere t.TABLE_SCHEMA='$db_name' and t.TABLE_NAME='$tab_name';"  >> /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shelif [ $load_type == 'increment' ];thenecho $tab_name,data is increment!mysql -h$db_ip -P$db_port -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 ... >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang#userpass info get ...huanhangsrc_dbip=\`sh /root/public-function/getsrcinfo.fc dbip $tab_name\`huanhangsrc_user=\`sh /root/public-function/getsrcinfo.fc user $tab_name\`huanhangsrc_pass=\`sh /root/public-function/getsrcinfo.fc pass $tab_name\`huanhanghuanhangdw_dbip=\`sh /root/public-function/getdwinfo.fc dbip qrt\`huanhangdw_user=\`sh /root/public-function/getdwinfo.fc user qrt\`huanhangdw_pass=\`sh /root/public-function/getdwinfo.fc pass qrt\`huanhanghuanhang#fetch maxtime from dw-src database ...huanhangexport last_maxtime=\`/usr/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/bin/mysql -h\$src_dbip -P$db_port -u\$src_user -p\$src_pass -N -e\"','set character_set_results=utf8;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\';','\" >/root/dw-etl/data/qrt_data/',TABLE_SCHEMA,'.',table_name,'_rt.dathuanhang','') mysql_export_and_hive_load_shellfrom information_schema.COLUMNSwhere TABLE_SCHEMA='$db_name' and table_name='$tab_name';"  >> /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shmysql -h$db_ip -P$db_port -u$user_name -p$passwd -N -e "set session group_concat_max_len=20000000;selectconcat('#','$rds_map_tab_name',' table current all data increment download from hive and upload to qrt rds...huanhang/usr/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/bin/mysql -h\$dw_dbip -u\$dw_user -p\$dw_pass -e \"use tmp;load data local infile \'/root/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/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! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.loghuanhang') sql_textfrom information_schema.TABLES tleft join information_schema.KEY_COLUMN_USAGE kon 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';"  >> /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shfised -i "s/\\\\\`/\\\`/g" /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh#echo -e \\n >> /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.shsed -i "s/huanhang/\n/g" `grep "mysql" -rl /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/`chmod 750 /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/src2qrt_$tab_name.sh: $(( i++ ))done#genrate batch scriptecho -e "#!/bin/bashexport yesterday=\`date -d last-day +%Y%m%d\`#src2qrt data proc ......echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,shell script exec start ................................................................ >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.logfor src_db in /root/dw-etl/etl-script/qrt/src2qrt_script/jellyfish*;do  echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,start exec \$src_db shell script... >> /root/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... >> /root/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! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log  done  echo \`date \"+%Y-%m-%d %H:%M:%S\"\`,\$src_db shell script exec finished! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.logdoneecho -e \"\\\n\\\n\\\n\\\n\\\n\" >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log" > /root/dw-etl/etl-script/qrt/qrt-hour_batch.shchmod 750 /root/dw-etl/etl-script/qrt/qrt-hour_batch.sh#sed -i "s/huanhang/\n/g" `grep "mysql" -rl /root/dw-etl/etl-script/qrt/src2qrt_script/$db_name/`[root@slave1 script_generate]# 

4、生成的建表语句及Etl脚本示例
4.1、建表语句示例

[root@slave1 rds-qrt_create_tab_script]# cat /root/dw-etl/script_generate/script_dir/rds-qrt_create_tab_script/game_video.sql use qrt;drop table if exists `game_video`;create table `game_video`(`id` bigint,`game_id` bigint,`game_name` varchar(200),`created_uid` bigint,`name` varchar(255),`uri` varchar(500),`process_state` bigint,`desc` varchar(2000),`size` bigint,`duration` bigint,`play_count` bigint,`mime_type` varchar(255),`real_play_count` bigint,`comment_count` bigint,`gift_count` bigint,`screenshot` varchar(200),`state` bigint,`watermark_state` bigint,`screenshot_state` bigint,`created_time` datetime,`updated_time` datetime) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4.2、Etl调度示例

[root@slave1 qrt]# cat /root/dw-etl/etl-script/qrt/qrt-hour_batch.sh #!/bin/bashexport yesterday=`date -d last-day +%Y%m%d`#src2qrt data proc ......echo `date "+%Y-%m-%d %H:%M:%S"`,shell script exec start ................................................................ >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.logfor src_db in /root/dw-etl/etl-script/qrt/src2qrt_script/jellyfish*;do  echo `date "+%Y-%m-%d %H:%M:%S"`,start exec $src_db shell script... >> /root/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... >> /root/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! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log  done  echo `date "+%Y-%m-%d %H:%M:%S"`,$src_db shell script exec finished! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.logdoneecho -e "\n\n\n\n\n" >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log

4.3、Etl脚本示例

[root@slave1 jellyfish]# cat /root/dw-etl/etl-script/qrt/src2qrt_script/jellyfish/src2qrt_game_video.sh #!/bin/bashecho -e `date +"%Y-%m-%d %H:%M:%S"`,qrt-src2dwqrt game_video data total load proc start ... >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log#userpass info get ...src_dbip=`sh /root/public-function/getsrcinfo.fc dbip game_video`src_user=`sh /root/public-function/getsrcinfo.fc user game_video`src_pass=`sh /root/public-function/getsrcinfo.fc pass game_video`dw_dbip=`sh /root/public-function/getdwinfo.fc dbip qrt`dw_user=`sh /root/public-function/getdwinfo.fc user qrt`dw_pass=`sh /root/public-function/getdwinfo.fc pass qrt`#game_video total data download from src .../usr/bin/mysql -h$src_dbip -P50506 -u$src_user -p$src_pass -N -e"set character_set_results=utf8;select CONCAT(ifnull(\`id\`,''),'|',ifnull(\`game_id\`,''),'|',ifnull(replace(replace(replace(\`game_name\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(\`created_uid\`,''),'|',ifnull(replace(replace(replace(\`name\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(replace(replace(replace(\`uri\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(\`process_state\`,''),'|',ifnull(replace(replace(replace(\`desc\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(\`size\`,''),'|',ifnull(\`duration\`,''),'|',ifnull(\`play_count\`,''),'|',ifnull(replace(replace(replace(\`mime_type\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(\`real_play_count\`,''),'|',ifnull(\`comment_count\`,''),'|',ifnull(\`gift_count\`,''),'|',ifnull(replace(replace(replace(\`screenshot\`,char(13),''),char(10),''),'|',''),''),'|',ifnull(\`state\`,''),'|',ifnull(\`watermark_state\`,''),'|',ifnull(\`screenshot_state\`,''),'|',ifnull(\`created_time\`,''),'|',ifnull(\`updated_time\`,'')) from jellyfish.game_video;" >/root/dw-etl/data/qrt_data/jellyfish.game_video_rt.dat#game_video table data download from src and upload to qrt rds.../usr/bin/mysql -h$dw_dbip -u$dw_user -p$dw_pass -e "use qrt;truncate table game_video;"#loop load dir file to rds /usr/bin/mysql -h$dw_dbip -u$dw_user -p$dw_pass -e "use qrt;load data local infile '/root/dw-etl/data/qrt_data/jellyfish.game_video_rt.dat' into table game_video fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"echo -e `date +"%Y-%m-%d %H:%M:%S"`,qrt-src2dwqrt game_video data total load proc finished! >> /root/dw-etl/run_log_dir/qrt-src2dwqrt_run.log[root@slave1 jellyfish]# 

5、说明
需要新加表或更改表，只要更新【real_table.list 】的内容，然后再执行对应的脚本就可以了。
这个方案在之前使用过，现在根据新环境进行了重新的整理；因为目前的数仓没有规范建立起来，所以通过这种形式的数据处理，可以提高效率，方便日常的数据拉取。