大数据环境平台仓库日常跑批整理

最新推荐文章于 2024-05-18 22:12:11 发布
最新推荐文章于 2024-05-18 22:12:11 发布
阅读量2.3k 收藏 1
点赞数
分类专栏： Solution DataWarehouse BigData 文章标签： 大数据 hadoop mysql hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/BabyFish13/article/details/50972937
版权
Solution 同时被 3 个专栏收录
85 篇文章 1 订阅
订阅专栏
BigData
52 篇文章 2 订阅
订阅专栏
DataWarehouse
27 篇文章 6 订阅
订阅专栏
1、树型目录环境
/home/hs/opt/dw-etl
│  date.list
│  dw_batch.sh
│  sor-all.sh
│  sor-rds.sh
│  ssa-all.sh
│
├─bigtab_int
│      init-99dianzhangitem.sh
│      init-99dingdan.sh
│      init-99dingdanfood.sh
│      ssa-increment_data_int_except3tab.sh
│
├─data
├─etl-script
│  ├─dpa-hive2mysql
│  │  │  date.list
│  │  │  dpa-hive2mysql1.sh
│  │  │  dpa-hive2mysql2.sh
│  │  │
│  │  └─data-dpa
│  ├─sor
│  │      TMD_ORGANIZATION_INFO.sql
│  │      TSF_ACCOUNT_INFO.sql
│  │      TSF_AUTH_CONTACTS_INFO.sql
│  │      TSF_AUTH_SETUPANDPOSITION_INFO.sql
│  │
│  ├─sor-pyproc
│  │      hezi_item.py
│  │
│  ├─ssa-hive2mysql
│  │  │  date.list
│  │  │  ssa-hive2mysql.sh
│  │  │  ssa-hive2mysql_increment_int.sh
│  │  │  ssa-hive_data_rollback.hql
│  │  │  ssa_data_current_all_proc.hql
│  │  │
│  │  └─data-ssa
│  ├─ssa-increment
│  │  ├─db99jifen
│  │  │      src2ssa_c_credit.sh
│  │  │      src2ssa_c_lottery_ticket.sh
│  │  │
│  │  ├─db99jinrong
│  │  │      src2ssa_f_auth_call.sh
│  │  │      src2ssa_f_auth_contacts.sh
│  │  │
│  │  ├─db99wejuan
│  │  │      src2ssa_wejuan_questionnaire_record.sh
│  │  │
│  │  └─db99huanx
│  │          src2ssa_99_coupon.sh
│  │          src2ssa_99_dianzhangbestpurchaseitem.sh
│  │
│  ├─ssa-increment-int
│  │  ├─db99jifen
│  │  │      src2ssa_c_credit.sh
│  │  │
│  │  ├─db99jinrong
│  │  │      src2ssa_f_auth_call.sh
│  │  │
│  │  ├─db99wejuan
│  │  │      src2ssa_wejuan_questionnaire_record.sh
│  │  │
│  │  └─db99huanx
│  │          src2ssa_99_coupon.sh
│  │          src2ssa_99_dianzhangbestpurchaseitem.sh
│  │
│  └─ssa-total
│      ├─db99hezi
│      │      src2ssa_99_dianzhang.sh
│      │      src2ssa_b_hezi.sh
│      │
│      ├─db99jifen
│      │      src2ssa_c_coupon_template.sh
│      │
│      ├─db99drink
│      │      src2ssa_d_dingdan.sh
│      │      src2ssa_d_dingdan_item.sh
│      │
│      ├─db99jinrong
│      │      src2ssa_dict.sh
│      │      src2ssa_dict_type.sh
│      │      src2ssa_f_account.sh
│      │
│      ├─db99wejuan
│      │      src2ssa_wejuan_questionnaire.sh
│      │      src2ssa_wejuan_questionnaire_base_record.sh
│      │
│      ├─db99print
│      │      src2ssa_p_dingdan.sh
│      │      src2ssa_p_dingdan_detail.sh
│      │
│      ├─db99huanx
│      │      src2ssa_99_city.sh
│      │      src2ssa_99_cityitem.sh
│      │
│      └─db99team
│              src2ssa_99_dianzhangteam.sh
│
└─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
        │      99_dianzhang.hql
        │
        └─rds-ssa_create_tab_script
                99_dianzhang.sql
               
2、每天定时调度的总控脚本
[hs@master ~]$ crontab -l
9 5 * * * sh /home/hs/opt/dw-etl/dw_batch.sh

cat /home/hs/opt/dw-etl/dw_batch.sh 
#!/bin/bash
export yesterday=`date -d last-day +%Y-%m-%d`
#echo -n "please enter a day for runing :"
#read yesterday

export ytd=${yesterday//-/}

#src data download and load in ssa
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/ssa-all.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/ssa-all.sh $ytd
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/ssa-all.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log

if false;then
#generate ssa current all data
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log
fi

#put ssa ca data on dw-rds
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh $yesterday
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log

#run sor data on dw-rds
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/sor-rds.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/sor-rds.sh $ytd
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/sor-rds.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log

cat manual_dw_batch.sh 
#!/bin/bash
#export yesterday=`date -d last-day +%Y-%m-%d`
echo -n "please enter a day for runing :"
read yesterday

export ytd=${yesterday//-/}

#src data download and load in ssa
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/ssa-all.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/ssa-all.sh $ytd
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/ssa-all.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log

if false;then
#generate ssa current all data
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[/home/hs/opt/hive-1.2.1/bin/hive -f /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa_data_current_all_proc.hql] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log
fi

#put ssa ca data on dw-rds
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh $yesterday
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/ssa-hive2mysql.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log

#run sor data on dw-rds
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/sor-rds.sh] exec start ... " >>/home/hs/opt/dw-etl/dw_batch.log
sh /home/hs/opt/dw-etl/sor-rds.sh $ytd
echo `date +"%Y-%m-%d %H:%M:%S"` >>/home/hs/opt/dw-etl/dw_batch.log
echo "$yesterday,[sh /home/hs/opt/dw-etl/sor-rds.sh] exec finished !" >>/home/hs/opt/dw-etl/dw_batch.log


3、ssa级总控
ssa-all.sh 
#!/bin/bash
#export yesterday=`date -d last-day +%Y%m%d`
export yesterday=$1

#src2ssa data proc ......
for src_db in /home/hs/opt/dw-etl/etl-script/ssa-increment/db59* /home/hs/opt/dw-etl/etl-script/ssa-total/db59*;
do
  echo `date "+%Y-%m-%d %H-%M-%S"`,start exec $src_db shell script... 
  for src2ssa_script in $src_db/*.sh;
  do
   sh $src2ssa_script $yesterday
  done
  echo `date "+%Y-%m-%d %H-%M-%S"`,$src_db shell script exec finished!
done


4、hive库ssa数据装载到rds
ssa-hive2mysql.sh 
#!/bin/bash
export yesterday=`date -d last-day +%Y-%m-%d`


#99_youhuiquan table current all data increment download from hive and upload to ssa rds...
/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/99_youhuiquan' row format delimited fields terminated by '|' select * from 99_youhuiquan where substr(update_time,1,10)='$yesterday';"
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;drop table if exists 99_youhuiquan_today;create table 99_youhuiquan_today as select * from ssa.99_youhuiquan where 1=2;"
#loop load dir file to rds 
for tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/99_youhuiquan/*;
do
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;load data local infile '$tabdt_path' into table 99_youhuiquan_today fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"
done
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;alter table tmp.99_youhuiquan_today add index idx_99_youhuiquan_item_id (item_id) using btree;delete ca.* from ssa.99_youhuiquan ca left join tmp.99_youhuiquan_today i on ca.item_id = i.item_id where i.item_id is not null;insert into ssa.99_youhuiquan select * from tmp.99_youhuiquan_today;"

#99_dzcaigou table current all data increment download from hive and upload to ssa rds...
/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/99_dzcaigou' row format delimited fields terminated by '|' select * from 99_dzcaigou where substr(update_time,1,10)='$yesterday';"
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;drop table if exists 99_dzcaigou_today;create table 99_dzcaigou_today as select * from ssa.99_dzcaigou where 1=2;"
#loop load dir file to rds 
for tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/99_dzcaigou/*;
do
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;load data local infile '$tabdt_path' into table 99_dzcaigou_today fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"
done
/usr/local/bin/mysql -hiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use tmp;alter table tmp.99_dzcaigou_today add index idx_99_dzcaigou_item_id (item_id) using btree;delete ca.* from ssa.99_dzcaigou ca left join tmp.99_dzcaigou_today i on ca.item_id = i.item_id where i.item_id is not null;insert into ssa.99_dzcaigou select * from tmp.99_dzcaigou_today;"
.....................


5、hive库sor级总控
sor-all.sh 
#/bin/bash
export yesterday=`date -d last-day +%Y-%m-%d`

#cd /home/hs/opt/dw-etl/etl-script/sor/
for sor_script in /home/hs/opt/dw-etl/etl-script/sor/*.sql; 
do
hive -hiveconf yesterday=$yesterday -f  $sor_script;
done


6、sor-rds总控
sor-rds.sh 
#/bin/bash
export yesterday=`date -d last-day +%Y-%m-%d`

#cd /home/hs/opt/dw-etl/etl-script/sor/
for sor_script in /home/hs/opt/dw-etl/etl-script/sor/*.sql; 
do
hive -hiveconf yesterday=$yesterday -f  $sor_script;
done
[hs@master dw-etl]$ cat sor-rds.sh 
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

python /home/hs/opt/dw-etl/etl-script/sor-pyproc/box_item.py
/usr/local/bin/mysql -hrdsiparrdess.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use sor;call procedure_batch_call($yesterday); "


7、rds中sor批量调度的存储过程 
CREATE DEFINER=`datauser`@`%` PROCEDURE `procedure_batch_call`(`p_etldate` int)
BEGIN

  #存储过程调用开始
	call p_tmd_organization_info(p_etldate);
	call p_tsf_account_info(p_etldate);
	call p_tsf_auth_contacts_info(p_etldate);
	..........

  #调用完成返回信息
	#RETURN 'procedure call finished!';

END


8、处理json数据的中间python脚本
hezi_item.py 
# -*- coding:utf-8 -*-
# adjust dormitem every day
import MySQLdb
import json
import warnings
warnings.filterwarnings("ignore")

db_config = {
    'host': 'rdsiparrdess.mysql.rds.aliyuncs.com',
    'user': 'datauser',
    'passwd': 'iloveyou',
    'port': 3306,
    'db': 'ssa'
}

def getDB():
    try:
        conn = MySQLdb.connect(host=db_config['host'],user=db_config['user'],passwd=db_config['passwd'],port=db_config['port'])
        conn.autocommit(True)
        curr = conn.cursor()
        curr.execute("SET NAMES utf8");
        curr.execute("USE %s" % db_config['db']);
    
        return conn, curr
    except MySQLdb.Error,e:
        print "Mysql Error %d: %s" % (e.args[0], e.args[1])
        return None, None

conn, curr = getDB()

i = 0

'''
sql = "SELECT max(order_id) FROM b_dingdan_item"
curr.execute(sql)
max_order_ids = curr.fetchall()
for item in max_order_ids:
    max_order_id = item[0]

sql = "SELECT order_id,detail_json FROM b_dingdan where order_id > %d" % (max_order_id)

'''

sql = "truncate table b_dingdan_item;"
curr.execute(sql)

sql = "SELECT order_id,detail_json FROM b_dingdan " 

curr.execute(sql)
foodbox_item_list = curr.fetchall()
for foodbox_item in foodbox_item_list:
    order_id = foodbox_item[0]
    foodbox_item1 = foodbox_item[1]
    items = json.loads(foodbox_item1)
    for x in items:
        rid = x['rid']
        quantity = x['quantity']
        price = x['price']
        amount = x['amount']
        sql = "insert into b_dingdan_item(order_id,rid,quantity,price,amount) values (%d,%d,%d,%.2f,%.2f)" % (order_id,rid,quantity,price,amount)
        print sql
        curr.execute(sql)
        i += 1

curr.close()
conn.close()

print 'done'
print 'total rows:' + str(i)


9、ssa层增量数据初始化调度脚本
ssa-increment_data_int_except3tab.sh 
#!/bin/bash
export yesterday=`date -d last-day +%Y%m%d`

#src2ssa data increment load int proc ......
for src_db in /home/hs/opt/dw-etl/etl-script/ssa-increment-int/db99*;
do
  echo `date "+%Y-%m-%d %H-%M-%S"`,start exec $src_db shell script... 
  for src2ssa_script in $src_db/*.sh;
  do
   if [ $src2ssa_script = "/home/hs/opt/dw-etl/etl-script/ssa-increment-int/db99store/src2ssa_99_dianzhangitem.sh" ] || [ $src2ssa_script = "/home/hs/opt/dw-etl/etl-script/ssa-increment-int/db99store/src2ssa_99_dingdan.sh" ] || [ $src2ssa_script = "/home/hs/opt/dw-etl/etl-script/ssa-increment-int/db99store/src2ssa_99_dingdanfood.sh" ];
   then
     echo $src2ssa_script,The table is too big, not on the loading!
   else
     #echo $src2ssa_script
     sh $src2ssa_script $yesterday
   fi 
  done
  echo `date "+%Y-%m-%d %H-%M-%S"`,$src_db shell script exec finished!
done


其他3个（主要由于数据量太大，所以根据主键字段最后一位或两位分10次或100次单独处理）：
init-99dianzhangitem.sh 
#/bin/bash
export yesterday=`date -d last-day +%Y%m%d`
#echo -n "please enter a day for runing :"
#read yesterday

#99_dingdanitem data proc...
mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=0;" > /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=1;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=2;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=3;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=4;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=5;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=6;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=7;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=8;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

mysql -hrdsipaddress -udbreader -piloveyou -N -e"select CONCAT(ifnull(item_id,''),'|',ifnull(dingdan_id,''),'|',ifnull(rid,''),'|',ifnull(status,''),'|',ifnull(update_time,'')) from db99huanx.99_dingdanitem where DATE_FORMAT(update_time,'%Y%m%d')<=$yesterday and substr(rid,-1,1)=9;" >> /home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat

hive -e "use ssa;truncate table 99_dingdanitem;load data local inpath '/home/hs/opt/dw-etl/data/db99huanx.99_dingdanitem_$yesterday.dat' into table 99_dingdanitem;"

#99_dingdanitem table current all data download from hive and upload to ssa rds...
/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/99_dingdanitem' row format delimited fields terminated by '|' select * from 99_dingdanitem;"
/usr/local/bin/mysql -hrdsipaddress.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use ssa;truncate table 99_dingdanitem;"
#loop load dir file to rds 
for tabdt_path in /home/hs/opt/dw-etl/etl-script/ssa-hive2mysql/data-ssa/99_dingdanitem/*;
do
/usr/local/bin/mysql -hrdsipaddress.mysql.rds.aliyuncs.com -udatauser -piloveyou -e "use ssa;load data local infile '$tabdt_path' into table 99_dingdanitem fields terminated by '|' enclosed by '' lines terminated by '\n' ignore 0 lines;"
done
.........

10、其他具体实现脚本
其他数据转换清洗的具体脚本，通过shell脚本自动生成(见上一篇： http://blog.csdn.net/babyfish13/article/details/50971817)，在此不作多述。
BabyFish13
关注
专栏目录
大数据时代高校档案管理初探.pdf
07-04
传统的库房环境管理通常采用人工监测，难以保证数据的准确性和监测工作的日常化。因此，文章建议搭建智能库房环境监测系统，对库房的内温湿度、烟雾、视频监控等环境要素进行动态监测，并实现远程视频实时监控功能，...
一个简单的小时级数据仓库构建
qq_47944580的博客
11-03 487
利用Kafka作为中间件，通过spring boot项目开放一个接口传数据，通过生产者发送，消费者来接收数据后按照小时划分存为文件，后load到hive仓库。原因百度应该是有什么特殊符号问题可能是命令行换行符，，但是我截取出来只有数字。可能是shell哪里的问题吧。一开始直接把时间切分剩小时，结果报错 ambiguous redirect，又切分一下就好了。把文件load到hive数据仓库，根据小时作为分区。启动消费者并把接收到的数据重定向到对应时间文件中。创建生产者，发送接收到的数据。
什么是跑批？
热门推荐
S_Sinsoledad的博客
09-18 1万+
将要处理的数据积攒成“批”，在指定时间一次性进行处理，被称为：批处理，也叫：跑批。 拓展： 1.跑批业务的特点：处理量大（成批），有特定的触发时机（指定时间点），可自动处理（无需人工干预）。 2.计算机诞生伊始，尚没有现代计算机的输入和输出设备，只能通过在纸带上打孔的方式进行输入输出，一笔一笔地处理数据，需要不停地打孔，读孔，既费时又费力。为了提高效率，渐渐形成了将要处理的数据积攒成“批”，在指定时间一次性进行处理，极大地减少了频繁打孔读孔的操作，提高了效率。这种处理方法，被称为批处理，这也是使用计算机处理
架构师自诉：如何做到百万数据半小时跑批结束
weixin_57907028的博客
05-09 1825
业务背景 跑批通常指代的是我们应用程序针对某一批数据进行特定的处理 在金融业务中一般跑批的场景有分户日结、账务计提、欠款批扣、不良资产处理等等 具体举一个例子 🌰 客户在我司进行借款，并约定每月 10 号码还款，在客户自主授权银行卡签约后 在每月 10 号（通常是凌晨）我们会在客户签约的银行卡上进行扣款 然后可能会有一个客户、两个客户、三个客户、四个客户、好多个客户都需要进行扣款，所以这一“批” 所有数据，我们都要统一地进行扣款处理，即为我们“跑批”的意思 跑批任务是要通过定时地去处理这些数据，不能因为其中
大数据环境平台日志日常跑批整理
BabyFish13
03-24 1825
1、日志处理模块文件目录路径 /home/spark/opt/User_Behavior_Log │  date.list │  db.py │  dbjson_proc.py │  dbjson_proc_commit_optimalize.py │  json2file.py │  log_batch.sh │  ublog2hive+mysql.sh │  ublog2hi
互联网大数据时代对医院财务会计的影响.pdf
07-07
因此，医院需要采用更大容量的数据仓库、分布式数据库和存储集群等大数据存储模式，来实现数据的高效存储和快速处理。这些数据存储方式能够帮助医院全面掌握运行情况，并在数据分析和决策制定中发挥作用。 其次，...
【面试宝典】2021年超全超详细的最新大数据开发面试题，附答案解析(一版).pdf
03-20
本套面试题集整理了多个大数据相关技术领域的面试问题，这些问题涉及了Hadoop生态系统、数据仓库技术Hive、大数据处理框架Spark、流处理框架Flink以及分布式消息系统Kafka等。下面将对上述技术点的知识点逐一梳理，...
管道工程大数据与人工智能应用研究.pptx
最新发布
05-20
- 大数据技术能够有效整合这些多源数据，通过清洗、整理，建立统一的数据仓库，为后续的分析和应用提供坚实的基础。 - **管道工程数据分析与挖掘：** - 利用大数据分析工具，如Hadoop、Spark等，可以从海量数据中...
轻量级数据处理和分析平台设计与应用.pdf
08-15
在当今数据驱动的商业环境中，商业智能(BI)解决方案作为帮助企业转化数据为决策支持的关键工具，其重要性不言而喻。本文将深入探讨商业智能系统架构的各个关键组成部分，包括操作数据库、数据预处理、查询数据库、...
定时跑批项目数据库（补传）
02-11
定时跑批项目数据库（补传），前期忘啦，现在重新上传 定时跑批项目数据库（补传），前期忘啦，现在重新上传
如何设计离线跑批系统
lbqjtyykx的博客
05-18 949
当然，别忘了最后一块拼图——日志打印与监控告警，我们需要根据实际业务情况，配置好定时任务执行时间，以及理论上应该执行完成的时间，针对“数据团队是否推送文件”、“应用是否读完文件”、“应用是否消费完 MQ ”、“当日跑批整体情况”进行监控告警，以便第一时间发现问题。这里注意文件解析任务、统计数量任务都要能支持重跑（手动传参执行），同时还有一个 MQ 发送失败的补偿任务，这样在各种异常情况下，程序都能够处理，另外，通过数据库唯一索引来保证幂等。确认文件传输完成标识，文件地址、路径、格式，数据格式、分隔符？
脚本跑批到调度平台再到运维平台流程的自我总结（大数据方向）（Impala）（JMP）(DCP)
Talentdeyang的博客
08-03 1061
脚本跑批到调度平台再到运维平台流程的自我总结（大数据方向）（Impala）
数据批处理速度慢？不妨试试这个
字母哥博客
09-27 5051
业务系统产生的明细数据通常要经过加工处理，按照一定逻辑计算成需要的结果，用以支持企业的经营活动。这类数据加工任务一般会有很多个，需要批量完成计算，在银行和保险行业常常被称为跑批，其它像石油、电力等行业也经常会有跑批的需求。大部分业务统计都会要求以某日作为截止点，而且为了不影响生产系统的运行，跑批任务一般会在夜间进行，这时候才能将生产系统当天产生的新明细数据导出来，送到专门的数据库或数据仓库完成跑批计算。第二天早上，跑批结果就可以提供给业务人员使用了。
大数据平台用于生成数据跑批脚本的脚本
BabyFish13
03-24 1万+
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_scrip
大数据平台下利用Python进行Hql并行数据跑批
BabyFish13
07-05 1922
此是一个简单的示例，如有类似需求进行些许的改动，即可使用。 如有基础数据准备的工作，需要提前准备好；本示例只关注Hql并行跑批方面。 1、具体业务处理的脚本 /Users/nisj/PycharmProjects/BiDataProc/parallelBatchOnBigData-forHql/business_proc.py # -*- coding=utf-8 -*- import o
大数据平台用于生成数据跑批脚本的脚本(version2.0)
BabyFish13
06-01 1万+
 一、脚本文件路径 [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 ├─
环境评估大数据管理平台：技术探索与实践
"环境评估大数据管理平台初探及技术综述" 本文主要探讨了环境评估领域如何利用大数据技术构建高效管理平台，以适应不断增长的数据处理需求。文章指出，随着软硬件成本降低和技术进步，传统的数据分析和管理手段已经...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值