当前位置:Gxlcms > mysql > etl工作中的设计问题

etl工作中的设计问题

时间:2021-07-01 10:21:17 帮助过:22人阅读

背景1 : 随着接入数据和处理数据的增加,生产脚本也越来越多,脚本由于前期的开发人员没有做到规范管理,导致脚本很乱。 解决方案: 1) 在lunix上规范目录,按平台,业务模块分目录存放。 2) 做好版本管理,提交到生产的脚本必须要commit到svn服务器。 3

背景1 : 随着接入数据和处理数据的增加,生产脚本也越来越多,脚本由于前期的开发人员没有做到规范管理,导致脚本很乱。

解决方案:

1) 在lunix上规范目录,按平台,业务模块分目录存放。

2) 做好版本管理,提交到生产的脚本必须要commit到svn服务器。

3) lunix上的目录是反应到svn的目录映射。

背景2 :脚本中很多地方有范围,指标,参数值,怎么把这些做的更灵活,而不是写死?

解决方案:

1)尽量把中文或英文映射为数字,不仅节省存储资源,还使程序更灵活。

比如:平台有 pc电脑,手机等,那就可以分别用1代表pc电脑 2 代表手机 3 代表其它。同时做一个码表来解释对应的关系。

主页 0 a

电台 1 b

语种 2 c

华语 3 d

多级目录的解析

/主页/电台/语种/华语

/0/1/2/3

/1/2/3

/xxx/xxx/xxx/xxx

如果多级目录有变化,怎么自动适应目录变化或者 回归二进制本质,用二进制表示。

2) 灵活应用参数列表,做一个的参数码表,动态生成hql语句。

比如:现在要分时段统计,用户出现次数,时段如下:

早上 6:00 -8:00

上午 8:00-12:00

中午 12:00-14:00

下午 14:00-18:00

晚上 18:00-23:00

深夜 23:00-00:00

凌晨 00:00-6:00

做一个码表 id comment time_region val par1 par2

1 早上 6:00 -8:00 1 6 8

2 上午 8:00-12:00 2 8 12

3 中午 12:00-14:00 3 12 14

4 下午 14:00-18:00 4 14 18

5 晚上 18:00-23:00 5 18 23

6 深夜 23:00-24:00 6 23 24

7 凌晨 00:00-6:00 7 0 6

读取该表,动态拼hql ,用后面的val分别代表这些时段。不管以后时段怎么修改,只要修改码表就行了。


比如要写如下的hive_sql

insert overwrite table common.order
select
   userid
  ,case 
        when hour_time>=0  and hour_time<=2  then '00_03'
        when hour_time>=3  and hour_time<=5  then '03_06' 
        when hour_time>=6  and hour_time<=7  then '06_08'
        when hour_time>=8  and hour_time<=11  then '08_12'
        when hour_time>=12 and hour_time<=13  then '12_14'
        when hour_time>=14 and hour_time<=17  then '14_18'
        when hour_time>=18 and hour_time<=23  then '18_24'
        else '0'
      end 
        as hour_time 
   ,count(distinct dt) hour_dt_num
 where dt >='2014-10-01'
 and dt<='2014-10-30'
 group by userid,
 case when hour_time>=0  and hour_time<=2  then '00_03'
        when hour_time>=3  and hour_time<=5  then '03_06' 
        when hour_time>=6  and hour_time<=7  then '06_08'
        when hour_time>=8  and hour_time<=11 then '08_12'
        when hour_time>=12 and hour_time<=13 then '12_14'
        when hour_time>=14 and hour_time<=17 then '14_18'
        when hour_time>=18 and hour_time<=23 then '18_24'
        else '0'
      end 


可以写成这样子:

#!/bin/bash
#
# add by lishc  2014-11-25
 
mysql=`which mysql`
user="root"
password="123"
database="test"
table="parm"
 
command="select par1,par2,id from test.$table "
$mysql -u${user} -p${password}  -e "${command}" >m.txt
 
###初始化
echo "       insert overwrite table common.order">mysql.sql
echo "               select     ">>mysql.sql
echo "                      userId  " >>mysql.sql
echo "                     case  ">>mysql.sql
 
sed -i -e '1d' m.txt
cat m.txt |while read line  
do
  par1=$(echo "${line}"|awk -F ' ' '{print $1}')
  par2=$(echo "${line}"|awk -F ' ' '{print $2}') 
  id=$(echo "${line}"|awk -F ' ' '{print $3}')  
   echo "par1: ${par1}"
   echo "par2: ${par2}"
   echo "                      when hour_time >=${par1}  and hour_time<=${par2}  then '${id}' ">>mysql.sql
Done


3) 所有的脚本存放在数据库中,用程序解析参数,并调用执行。

可参考kettle设计:

每个步骤组件化:输入,输出,执行脚本,执行sql,管理执行顺序。

由于ETL过程或数据分析模型,都是一些有序的sql或脚本操作。

人气教程排行