etl工作中的設計問題，etl工作設計問題

etl工作中的設計問題，etl工作設計問題

http://blog.csdn.net/lili72

背景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.orderselect   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.sqlecho "               select     ">>mysql.sqlecho "                      userId  " >>mysql.sqlecho "                     case  ">>mysql.sql sed -i -e '1d' m.txtcat 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.sqlDone

 

 3) 所有的指令碼存放在資料庫中，用程式解析參數，並調用執行。

      可參考kettle設計：

         每個步驟組件化：輸入，輸出，執行指令碼，執行sql，管理執行順序。

         由於ETL過程或資料分析模型，都是一些有序的sql或指令碼操作。