當前資料倉儲建設過程

雨客發表於2016-04-08

一個典型的企業資料倉儲通常包含資料採集、資料加工和儲存、資料展現等幾個過程，本篇文章將按照這個順序記錄部門當前建設資料倉儲的過程。

1. 資料採集和儲存

採集資料之前，先要定義資料如何存放在 hadoop 以及一些相關約束。約束如下：

所有的日誌資料都存放在 hdfs 上的 /logroot 路徑下面
hive 中資料庫命名方式為 dw_XXXX，例如：dw_srclog 存放外部來源的原始資料，dw_stat 存放統計結果的資料
原始資料都加工成為結構化的文字檔案，欄位分隔符統一使用製表符，並在 lzo 壓縮之後上傳到 hdfs 中。
hive 中使用外部表儲存資料，資料存放在 /logroot 下，如果不是分割槽表，則檔名為表名；如果是分割槽表，則按月和天分割槽，每天分割槽下的檔名為表名_日期，例如：test_20141023

資料採集的來源可能是關聯式資料庫或者一些系統日誌，採集工具可以是日誌採集系統，例如：flume、sqoop 、storm以及一些 ETL 工具等等。

目前，主要是從 mysql 中匯出資料然後在匯入到 hdfs 中，對於儲存不需要按天分割槽的表，這部分過程程式碼如下：

#!/bin/bash

if [ "$1" ]; then 
  DAY="$1"
else 
  DAY="yesterday"
fi

datestr=`date +%Y-%m-%d -d"$DAY"`;
logday=`date +%Y%m%d -d"$DAY"`;
logmonth=`date +%Y%m -d"$DAY"`

#hive table
table=test
#mysql db config file
srcdb=db_name

sql="select * from test"

hql="
use dw_srclog;
create external table if not exists test (
  id int,
  name int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY `	`
STORED AS INPUTFORMAT
  `com.hadoop.mapred.DeprecatedLzoTextInputFormat`
OUTPUTFORMAT
  `org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat`
LOCATION
  `/logroot/test`;
"

#begin
chmod +x $srcdb.sql
. ./$srcdb.sql

file="${table}"
sql_var=" -r -quick --default-character-set=utf8  --skip-column"

mysql $sql_var -h${db_host} -u${db_user} -p${db_pass} -P${db_port} -D${db_name} -e "$sql" | sed "s/NULL/\\N/g"> $file 2>&1

lzop -U $file
hadoop fs -mkdir -p /logroot/$table
hadoop fs -ls /logroot/$table |grep lzo|awk `{print $8}`|xargs -i hadoop fs -rm {} 
hadoop fs -moveFromLocal $file.lzo /logroot/$table/
hadoop jar /usr/lib/hadoop/lib/hadoop-lzo.jar com.hadoop.compression.lzo.LzoIndexer  /logroot/$table/$file.lzo 2>&1

echo "create table if not exists"
hive -v -e "$hql;" 2>&1

上面 bash 程式碼邏輯如下：

1、判斷是否輸入引數，如果沒有引數，則取昨天，意思是每天讀取 mysql 資料庫中昨天的資料。
2、定義 mysql 中 select 查詢語句
3、定義 hive 中建表語句
4、讀取 mysql 資料庫連線資訊，上例中為從 db_name.sql 中讀取 db_host、db_user、db_pass、db_port、db_name 五個變數
5、執行 mysql 命令匯出指定 sql 查詢的結果，並將結果中的 NULL 欄位轉換為 \N，因為在 bash 中是轉義字元，故需要使用兩個
6、lzo 壓縮檔案並上傳到 hdfs，並且建立 lzo 索引
7、最後刪除本地檔案

對於分割槽表來說，建表語句如下：

use dw_srclog;
create external table if not exists test_p (
  id int,
  name int
)
partitioned by (key_ym int, key_ymd int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY `	`
STORED AS INPUTFORMAT
  `com.hadoop.mapred.DeprecatedLzoTextInputFormat`
OUTPUTFORMAT
  `org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat`
LOCATION
  `/logroot/test_p`;

從 mysql 匯出檔案並上傳到 hdfs 命令如下：

#begin
chmod +x $srcdb.sql
. ./$srcdb.sql

file="${table}_$logday"
sql_var=" -r -quick --default-character-set=utf8  --skip-column"

mysql $sql_var -h${db_host} -u${db_user} -p${db_pass} -P${db_port} -D${db_name} -e "$sql" | sed "s/NULL/\\N/g"> $file 2>&1

lzop -U $file
hadoop fs -mkdir -p /logroot/$table/key_ym=$logmonth/key_ymd=$logday
hadoop fs -ls /logroot/$table/key_ym=$logmonth/key_ymd=$logday/ |grep lzo|awk `{print $8}`|xargs -i hadoop fs -rm {} 2>&1
hadoop fs -moveFromLocal $file.lzo /logroot/$table/key_ym=$logmonth/key_ymd=$logday/
hadoop jar /usr/lib/hadoop/lib/hadoop-lzo.jar com.hadoop.compression.lzo.LzoIndexer  /logroot/$table/key_ym=$logmonth/key_ymd=$logday/$file.lzo 2>&1

hive -v -e "$hql;ALTER TABLE $table ADD IF NOT EXISTS PARTITION(key_ym=$logmonth,key_ymd=$logday) location `/logroot/$table/key_ym=$logmonth/key_ymd=$logday` " 2>&1

通過上面的兩個命令就可以實現將 mysql 中的資料匯入到 hdfs 中。

這裡需要注意以下幾點：

1、 hive 中原始日誌使用預設的 textfile 方式儲存，是為了保證日誌的可讀性，方便以後從 hdfs 下載來之後能夠很方便的轉換為結構化的文字檔案並能瀏覽檔案內容。
2、使用 lzo 壓縮是為了節省儲存空間
3、使用外包表建表，在刪除表結構之後資料不會刪，方便修改表結構和分割槽。

使用 Sqoop

使用 sqoop 主要是用於從 oracle 中通過 jdbc 方式匯出資料到 hdfs，sqoop 命令如下：

sqoop import --connect jdbc:oracle:thin:@192.168.56.121:2154:db --username bi_user_limit --password `XXXX` --query "select * from test where  $CONDITIONS" --split-by id  -m 5 --fields-terminated-by `	` --lines-terminated-by `
`  --null-string `\N` --null-non-string `\N` --target-dir "/logroot/test/key_ymd=20140315"  --delete-target-dir

2. 資料加工

對於資料量比較小任務可以使用 impala 處理，對於資料量大的任務使用 hive hql 來處理。

impala 處理資料：

impala-shell -i `192.168.56.121:21000` -r -q "$sql;"

有時候需要使用 impala 匯出資料：

impala-shell -i `192.168.56.121:21000` -r -q "$sql;" -B --output_delimiter="	" -o $file
sed -i `1d` $file  #匯出的第一行有不可見的字元

使用 hive 處理資料生成結果表：

#!/bin/bash

if [ "$1" ]; then 
  DAY="$1"
else 
  DAY="yesterday"
fi

echo "DAY=$DAY"

datestr=`date +%Y-%m-%d -d"$DAY"`;
logday=`date +%Y%m%d -d"$DAY"`;
logmonth=`date +%Y%m -d"$DAY"`

#target table
table=stat_test_p
sql="use dw_srclog;insert OVERWRITE table stat_test_p partition(key_ym=$logmonth,key_ymd=$logday)
select id,count(name) from test_p where key_ymd=$logday group by id
"

hql="
use dw_web;
create external table if not exists goods_sales_info_day (
  id int,
  count int
) partitioned by (key_ym int, key_ymd int)
STORED AS RCFILE
LOCATION `/logroot/stat_test_p`;
"
#begin
hive -v -e "
$hql;
SET hive.exec.compress.output=true;
SET mapreduce.input.fileinputformat.split.maxsize=128000000;
SET mapred.output.compression.type=BLOCK;
SET mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
$sql" 2>&1

這裡主要是先判斷是否建立外包表（外包表儲存為 RCFILE 格式），然後設定 map 的輸出結果使用 snappy 壓縮，並設定每個 map 的大小，最後執行 insert 語句。結果表儲存為 RCFILE 的原因是，在 CDH 5.2 之前，該格式的表可以被 impala 讀取。

任務排程

當任務多了之後，每個任務之間會有一些依賴，為了保證任務的先後執行順序，這裡使用的是 azkaban 任務排程框架。

該框架的使用方式很簡單：

首先建立一個 bi_etl 目錄，用於存放執行指令碼。
在 bi_etl 目錄下建立一個 properties 檔案，檔名稱任意，檔案內容為：DAY=yesterday，這是一個系統預設引數，即預設 DAY 變數的值為 yesterday，該變數在執行時可以被覆蓋：在 azkaban 的 web 管理介面，執行一個 Flow 時，新增一個 Flow Parameters 引數，Name 為 DAY，Value 為你想要指定的值，例如：20141023。
建立一個 bash 指令碼 test.sh，檔案內容如第一章節內容，需要注意的是該指令碼中會判斷是否有輸出引數。
針對 bash 指令碼，建立 azkaban 需要的 job 檔案，檔案內容如下（azkaban 執行該 job 時候，會替換 ${DAY} 變數為實際的值）：

type=command
command=sh test.sh ${DAY}
failure.emails=XXX@163.com
dependencies=xxx

最後，將 bi_etl 目錄打包成 zip 檔案，然後上傳到 azkaban 管理介面上去，就可以執行或者是設定排程任務了。

使用上面的方式編寫 bash 指令碼和 azkaban 的 job 的好處是：

azkaban 的 job 可以指定引數來控制執行哪一天的任務
job 中實際上執行的是 bash 指令碼，這些指令碼脫離了 azkaban 也能正常執行，同樣也支援傳引數。

3. 資料展現

目前是將 hive 或者 impala 的處理結果推送到關聯式資料庫中，由傳統的 BI 報表工具展示資料或者直接通過 impala 查詢資料生成報表併傳送郵件。

為了保證報表的正常傳送，需要監控任務的正常執行，當任務失敗的時候能夠傳送郵件，這部分通過 azkaban 可以做到。另外，還需要監控每天執行的任務同步的記錄數，下面指令碼是統計記錄數為0的任務：

#!/bin/bash

if [ "$1" ]; then
  DAY="$1"
else
  DAY="yesterday"
fi

echo "DAY=$DAY"

datestr=`date +%Y-%m-%d -d"$DAY"`;
logday=`date +%Y%m%d -d"$DAY"`;
logmonth=`date +%Y%m -d"$DAY"`
datemod=`date +%w -d "yesterday"`

rm -rf /tmp/stat_table_day_count_$logday
touch /tmp/stat_table_day_count_$logday
for db in `hadoop fs -ls /user/hive/warehouse|grep -vE `testdb|dw_etl`|grep `.db`|awk `{print $8}`|awk -F `/` `{print $5}` |awk -F `.` `{print $1}``;do
    for table in `hive -S -e "set hive.cli.print.header=false; use $db;show tables" ` ;do
        count_new=""
        result=`hive -S -e "set hive.cli.print.header=false; use $db;show create table $table;"  2>&1 | grep PARTITIONED`
        if [ ${#result} -gt 0 ];then
      is_part=1
      count_new=`impala-shell -k -i 10.168.35.127:21089 --quiet -B --output_delimiter="	" -q "select count(1) from ${db}.$table where key_ymd=$logday "`
        else
      is_part=0
      count_new=`impala-shell -k -i 10.168.35.127:21089 --quiet -B --output_delimiter="	" -q "select count(1) from ${db}.$table; "`
        fi
        echo "$db,$table,$is_part,$count_new" >> /tmp/stat_table_day_count_$logday
    done
done

#mail -s "The count of the table between old and new cluster in $datestr" -c $mails < /tmp/stat_table_day_count_$logday

sed -i `s/1034h//g` /tmp/stat_table_day_count_$logday
sed -i `s/[//g` /tmp/stat_table_day_count_$logday
sed -i `s/?//g` /tmp/stat_table_day_count_$logday
sed -i `s/x1B//g` /tmp/stat_table_day_count_$logday

res=`cat /tmp/stat_table_day_count_$logday|grep -E `1,0|0,0`|grep -v stat_table_day_count`

echo $res

hive -e "use dw_default;
LOAD DATA LOCAL INPATH `/tmp/stat_table_day_count_$logday` overwrite INTO TABLE stat_table_day_count  PARTITION (key_ym=$logmonth,key_ymd=$logday)
"
python mail.py "Count is 0 in $datestr" "$res"

4. 總結

上面介紹了資料採集、加工和任務排程的過程，有些地方還可以改進：

引入 ETL 工具實現關聯式資料庫匯入到 hadoop，例如：Kettle 工具
目前是每天一次從 mysql 同步資料到 hadoop，以後需要修改同步頻率，做到更實時
hive 和 impala 在欄位型別、儲存方式、函式的相容性上存在一些問題

優化方案：ETL的過程原理和資料倉儲建設（R2）
2014-03-21
優化
查詢當前資料庫存在某個字串的儲存過程
2018-06-21
資料庫字串儲存過程
構建資料倉儲
2009-10-08
資料倉儲建設-OLAP和資料立方體
2018-02-07
基於OneData的資料倉儲建設
2022-11-30
【資料倉儲】|5 維度建模設計和實施過程
2021-06-07
設計資料倉儲和資料倉儲的粒度
2011-10-17
使用error stack 抓取儲存過程的當前SQL
2014-10-23
Error儲存過程SQL
如何構建資料倉儲模型？
2022-09-26
模型
資料倉儲的構建(ZT)
2007-12-13
資料庫設計：儲存過程
2016-07-05
資料庫儲存過程
最最最全資料倉儲建設指南，速速收藏！！
2019-12-13
雲端資料倉儲的模式選型與建設
2019-08-29
模式
中小銀行資料倉儲建設 | 最佳實踐
2023-01-09
資料倉儲能為你當前資料庫體系的不足做些什麼? (轉)
2008-03-20
資料庫
[數倉]資料倉儲設計方案
2019-06-11
Oracle資料倉儲高階課程
2007-04-17
Oracle
滴滴資料倉儲指標體系建設實踐
2020-09-01
指標
Hive：資料倉儲構建步驟
2018-10-16
Hive
資料倉儲和後設資料
2009-08-28
資料倉儲設計(轉)
2009-08-10
金倉資料庫KingbaseES儲存過程 RETURN語句
2023-03-31
資料庫儲存過程
資料倉儲,資料探勘,OLAP,BI等系統技術深度建設
2007-04-17
資料庫設計：儲存過程主體
2016-07-05
資料庫儲存過程
資料倉儲(6)數倉分層設計
2022-04-14
資料倉儲(7)數倉規範設計
2022-04-20
資料倉儲—資料倉儲—Sybase IQ 介紹
2007-01-27
資料倉儲設計指南(ZT)
2007-12-13
資料倉儲
2010-10-13
企業為什麼要建資料倉儲？
2022-11-08
SQL Server資料倉儲的構建與分析
2007-03-06
SQLServer
MySql資料庫——儲存過程
2021-09-09
MySql資料庫儲存過程
儲存過程返回資料集
2014-03-27
儲存過程
關於資料倉儲的設計！
2008-02-13
資料倉儲—資料倉儲—NCR Teradata Warehouse 介紹
2007-01-27
資料倉儲中的分析SQL——資料倉儲手冊
2009-10-23
SQL
資料湖和中央資料倉儲的設計
2023-12-06
【資料庫】資料庫儲存過程(一)
2019-09-05
資料庫儲存過程