基於Hadoop生態圈的資料倉儲實踐 —— 環境搭建（三）

三、建立資料倉儲示例模型
        Hadoop及其相關服務安裝配置好後，下面用一個小而完整的示例說明多維模型及其相關ETL技術在Hadoop上的具體實現。

1. 設計ERD
        操作型系統是一個銷售訂單系統，初始時只有產品、客戶、訂單三個表，ERD如下圖所示。

        多維資料倉儲包含有一個銷售訂單事實表，產品、客戶、訂單、日期四個維度表，ERD如下圖所示。

        作為示例，上面這些ERD裡的屬性都很簡單，看屬性名字便知其含義。維度表除了日期維度外，其它三個表都在源表的基礎上增加了代理鍵、版本號、生效日期、過期日期四個屬性，用來處理漸變維（SCD）。日期維度有其特殊性，該維度資料一旦生成就不會改變，所以不需要版本號、生效日期、過期日期。代理鍵是維度表的主鍵。事實表引用維度表的代理鍵作為自己的外來鍵，銷售金額是當前事實表中的唯一度量。

2. Hive相關配置
        使用Hive作為多維資料倉儲的主要挑戰是處理漸變維（SCD）和生成代理鍵。處理漸變維需要配置Hive支援行級更新，並在建表時選擇適當的檔案格式。生成代理鍵在關聯式資料庫中一般都是用自增列或序列物件，但Hive中沒有這樣的機制，得用其它辦法實現，在後面ETL部分再詳細討論。
（1）選擇檔案格式
（本段摘譯自https://acadgild.com/blog/file-formats-in-apache-hive/）
         Hive是Hadoop上的資料倉儲元件，它便於查詢和管理分散式儲存上的大資料集。Hive提供了一種稱為HiveQL的語言，允許使用者進行類似於SQL的查詢。和SQL一樣，HiveQL只處理結構化資料。預設時Hive使用內建的derby資料庫儲存後設資料，也可以配置Hive使用MySQL資料庫儲存後設資料。Hive裡的資料最終儲存在HDFS的檔案中，它可以處理以下4種檔案格式：

• TEXTFILE
• SEQUENCEFILE
• RCFILE
• ORCFILE

        在深入各種型別的檔案格式前，先看一下什麼是檔案格式。

        檔案格式
        所謂檔案格式是一種資訊被儲存或編碼成計算機檔案的方式。在Hive中檔案格式指的是記錄怎樣被儲存到檔案中。當我們處理結構化資料時，每條記錄都有自己的結構。記錄在檔案中是如何編碼的即定義了檔案格式。
        不同檔案格式的主要區別在於它們的資料編碼、壓縮率、使用的空間和磁碟I/O。
        Hive在匯入資料時並不驗證資料與表模式是否匹配，但是它會驗證檔案格式是否和表定義的相匹配。

        TEXTFILE
        TEXTFILE是Hadoop裡最常用的輸入輸出格式，也是Hive的預設檔案格式。如果表定義為TEXTFILE，則可以向該表中匯入以逗號、Tab或空格作為分隔符的資料，也可以匯入JSON資料。TEXTFILE格式預設每一行被認為是一條記錄。
        TEXTFILE格式的輸入輸出包是：

org.apache.hadoop.mapred.TextInputFormat
org.apache.hadoop.mapred.TextOutputFormat

        示例：

-- 建立TEXTFILE格式的表
create table olympic(athelete STRING,age INT,country STRING,year STRING,closing STRING,sport STRING,gold INT,silver INT,bronze INT,total INT) row format delimited fields terminated by '\t' stored as textfile;
-- 向表中匯入資料
load data local inpath '/home/kiran/Downloads/olympic_data.csv' into table olympic;
-- 查詢表
select athelete from olympic;

        SEQUENCEFILE
        我們知道Hadoop處理少量大檔案比大量小檔案的效能要好。如果檔案小於Hadoop裡定義的塊尺寸，可以認為是小檔案。如果有大量小檔案，那麼後設資料的增長將轉化為NameNode的開銷。為了解決這個問題，Hadoop引入了sequence檔案，將sequence作為儲存小檔案的容器。
        Sequence檔案是由二進位制鍵值對組成的平面檔案。Hive將查詢轉換成MapReduce作業時，決定一個給定記錄的哪些鍵值對被使用。Sequence檔案是可分割的二進位制格式，主要的用途是聯合兩個或多個小檔案組成一個sequence檔案。
        SEQUENCEFILE格式的輸入輸出包是：

org.apache.hadoop.mapred.SequenceFileInputFormat
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

        示例：

-- 建立SEQUENCEFILE格式的表
create table olympic_sequencefile(athelete STRING,age INT,country STRING,year STRING,closing STRING,sport STRING,gold INT,silver INT,bronze INT,total INT) row format delimited fields terminated by '\t' stored as sequencefile;
-- 向表中匯入資料
-- 與TEXTFILE有些不同，因為SEQUENCEFILE是二進位制格式，所以需要從其它表向SEQUENCEFILE表插入資料。
INSERT OVERWRITE TABLE olympic_sequencefile SELECT * FROM olympic;
-- 查詢表
select athelete from olympic_sequencefile;

        RCFILE
        RCFILE指的是Record Columnar File，一種高壓縮率的二進位制檔案格式，被用於在一個時間點操作多行的場景。RCFILEs是由二進位制鍵值對組成的平面檔案，這點與SEQUENCEFILE非常相似。RCFILE以記錄的形式儲存表中的列，即列儲存方式。它先分割行做水平分割槽，然後分割列做垂直分割槽。RCFILE把一行的後設資料作為鍵，把行資料作為值。這種面向列的儲存在執行資料分析時更高效。
        RCFILE格式的輸入輸出包是：

org.apache.hadoop.hive.ql.io.RCFileInputFormat
org.apache.hadoop.hive.ql.io.RCFileOutputFormat

        示例：

-- 建立RCFILE格式的表
create table olympic_rcfile(athelete STRING,age INT,country STRING,year STRING,closing STRING,sport STRING,gold INT,silver INT,bronze INT,total INT) row format delimited fields terminated by '\t' stored as rcfile
-- 向表中匯入資料
-- 不能直接向RCFILE表中匯入資料，需要從其它表向RCFILE表插入資料。
INSERT OVERWRITE TABLE olympic_rcfile SELECT * FROM olympic;
-- 查詢表
select athelete from olympic_rcfile;

        ORCFILE
        ORC指的是Optimized Row Columnar，就是說相對於其它檔案格式，它以更優化的方式儲存資料。ORC能將原始資料的大小縮減75%，從而提升了資料處理的速度。OCR比Text、Sequence和RC檔案格式有更好的效能。而且ORC是目前Hive中唯一支援事務的檔案格式。
        ORCFILE格式的輸入輸出包是：

org.apache.hadoop.hive.ql.io.orc

        示例：

-- 建立ORCFILE格式的表
create table olympic_orcfile(athelete STRING,age INT,country STRING,year STRING,closing STRING,sport STRING,gold INT,silver INT,bronze INT,total INT) row format delimited fields terminated by '\t' stored as orcfile;
-- 向表中匯入資料
-- 不能直接向ORCFILE表中匯入資料，需要從其它表向ORCFILE表插入資料。
INSERT OVERWRITE TABLE olympic_orcfile SELECT * FROM olympic;
-- 查詢表
select athelete from olympic_orcfile;

        應該依據資料需求選擇適當的檔案格式，例如，
        a）如果資料有引數化的分隔符，那麼可以選擇TEXTFILE格式。
        b）如果資料所在檔案比塊尺寸小，可以選擇SEQUENCEFILE格式。
        c）如果想執行資料分析，並高效地儲存資料，可以選擇RCFILE格式。
        d）如果希望減小資料所需的儲存空間並提升效能，可以選額ORCFILE格式。

        對於多維資料倉儲來說，需要處理SCD，必然要用到行級更新，所以所有TDS（轉換後的資料儲存）裡的表，除日期維度表外，其它表都是用ORCFILE格式。日期維度表資料一旦生成就不會修改，所以使用TEXTFILE格式。RDS（原始資料儲存）裡的表使用預設的TEXTFILE格式。

（2）支援行級更新
        在一個典型的星型模式資料倉儲中，維度表隨時間的變化很緩慢。例如，一個零售商開了一家新商店，需要將新店資料加到商店表，或者一個已有商店的營業面積或其它需要跟蹤的特性改變了。這些改變會導致插入或修改個別記錄。Hive從0.14版本開始支援事務和行級更新，但預設是不支援的，需要一些附加的配置。

        a）配置Hive支援事務
        CDH 5.7.0包含的Hive版本是1.1.0，可以支援事務及行級更新，但此版本的中文支援問題較多。編輯hive-site.xml配置檔案，新增支援事務的屬性。
vi /etc/hive/conf.cloudera.hive/hive-site.xml

<!-- 新增如下配置項以支援事務 -->
<property>  
    <name>hive.support.concurrency</name>  
    <value>true</value>  
</property>  
<property>  
    <name>hive.exec.dynamic.partition.mode</name>  
    <value>nonstrict</value>  
</property>  
<property>  
    <name>hive.txn.manager</name>  
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>  
</property>  
<property>  
    <name>hive.compactor.initiator.on</name>  
    <value>true</value>  
</property>  
<property>  
    <name>hive.compactor.worker.threads</name>  
    <value>1</value>  
</property>

        b）新增Hive後設資料
[root@cdh2~]#mysql -u root -p hive

INSERT INTO NEXT_LOCK_ID VALUES(1);  
INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1);  
INSERT INTO NEXT_TXN_ID VALUES(1);  
COMMIT;  

        說明：如果這三個表沒有資料，執行行級更新時會報以下錯誤：org.apache.hadoop.hive.ql.lockmgr.DbTxnManager FAILED: Error in acquiring locks: Error communicating with the metastore

        c）測試
        重啟Hive，然後執行下面的HiveQL語句
[root@cdh2~]#beeline -u jdbc:hive2://

use test;  
-- 建立測試表
create table t1(id int, name string)   
clustered by (id) into 8 buckets   
stored as orc TBLPROPERTIES ('transactional'='true'); 

        說明：

• 必須儲存為ORC格式
• 建表語句必須帶有into buckets子句和stored as orc TBLPROPERTIES ('transactional'='true')子句，並且不能帶有sorted by子句。

-- 測試insert
insert into t1 values (1,'aaa');  
insert into t1 values (2,'bbb'); 
select* from t1;

        查詢結果如下圖所示。

-- 測試update
update t1 set name='ccc' where id=1;  
select* from t1;

        查詢結果如下圖所示。

-- 測試delete
delete from t1 where id=2; 
select* from t1;

        查詢結果如下圖所示。
-- 對已有非ORC表的轉換
-- 在本地檔案/root/a.txt中寫入以下4行資料

1,a,US,CA
2,b,US,CB
3,c,CA,BB
4,d,CA,BC

-- 建立非分割槽表並載入資料 
use test; 
CREATE TABLE t1 (id INT, name STRING, cty STRING, st STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';    
LOAD DATA LOCAL INPATH '/root/a.txt' INTO TABLE t1;    
SELECT * FROM t1;  

-- 建立外部分割槽事務表並載入資料  
CREATE EXTERNAL TABLE t2 (id INT, name STRING) PARTITIONED BY (country STRING, state STRING)  
CLUSTERED BY (id) INTO 8 BUCKETS  
STORED AS ORC TBLPROPERTIES ('transactional'='true');  
INSERT INTO T2 PARTITION (country, state) SELECT * FROM T1;  
SELECT * FROM t2;  

        查詢結果如下圖所示。

-- 修改資料  
INSERT INTO TABLE t2 PARTITION (country, state) VALUES (5,'e','DD','DD');  
UPDATE t2 SET name='f' WHERE id=1;    
DELETE FROM t2 WHERE name='b';  
SELECT * FROM t2;

        查詢結果如下圖所示。

        說明：

• 不能修改bucket列的值，否則會報以下錯誤：FAILED: SemanticException [Error 10302]: Updating values of bucketing columns is not supported.  Column id.
• 對已有非ORC表的轉換，只能通過新建ORC表再向新表遷移資料的方式，直接修改原表的檔案格式屬性是不行的（有興趣的可以試試，我是踩到過坑了）。

3. 建立資料庫表
        在本示例中，源資料庫表就是前面提到的操作型系統的模擬。在CDH1上的MySQL中建立源資料庫表。RDS儲存原始資料，作為源資料到資料倉儲的過渡，在CDH2上的Hive中建RDS庫表。TDS即為轉化後的多維資料倉儲，在CDH2上的Hive中建TDS庫表。

（1）建立源資料資料庫表
        建立源資料資料庫表的SQL指令碼如下：

-- 建立源資料庫
DROP DATABASE IF EXISTS source;
CREATE DATABASE source;

-- 建立源庫表  
USE source;
  
-- 建立客戶表  
CREATE TABLE customer (  
    customer_number INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '客戶編號，主鍵',  
    customer_name VARCHAR(50) comment '客戶名稱',  
    customer_street_address VARCHAR(50) comment '客戶住址',  
    customer_zip_code INT comment '郵編',  
    customer_city VARCHAR(30) comment '所在城市',  
    customer_state VARCHAR(2) comment '所在省份'  
);
  
-- 建立產品表  
CREATE TABLE product (     
    product_code INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '產品編碼，主鍵',  
    product_name VARCHAR(30) comment '產品名稱',  
    product_category VARCHAR(30) comment '產品型別'     
);
  
-- 建立銷售訂單表  
CREATE TABLE sales_order (  
    order_number INT NOT NULL AUTO_INCREMENT PRIMARY KEY comment '訂單號，主鍵',  
    customer_number INT comment '客戶編號',  
    product_code INT comment '產品編碼',  
    order_date DATETIME comment '訂單日期',  
    entry_date DATETIME comment '登記日期',  
    order_amount DECIMAL(10 , 2 ) comment '銷售金額',  
    foreign key (customer_number)  
        references customer (customer_number)  
        on delete cascade on update cascade,  
    foreign key (product_code)  
        references product (product_code)  
        on delete cascade on update cascade  
);

（2）生成源庫測試資料
        生成源庫測試資料的SQL指令碼如下：

USE source;
  
-- 生成客戶表測試資料  
INSERT INTO customer   
    (customer_name,  
    customer_street_address,  
    customer_zip_code,  
    customer_city,  
    customer_state)  
VALUES    
('Really Large Customers', '7500 Louise Dr.',17050, 'Mechanicsburg','PA'),  
('Small Stores', '2500 Woodland St.',17055, 'Pittsburgh','PA'),  
('Medium Retailers','1111 Ritter Rd.',17055,'Pittsburgh','PA'),  
('Good Companies','9500 Scott St.',17050,'Mechanicsburg','PA'),  
('Wonderful Shops','3333 Rossmoyne Rd.',17050,'Mechanicsburg','PA'),  
('Loyal Clients','7070 Ritter Rd.',17055,'Pittsburgh','PA'),  
('Distinguished Partners','9999 Scott St.',17050,'Mechanicsburg','PA');
  
-- 生成產品表測試資料  
INSERT INTO product  
    (product_name,  
    product_category )  
VALUES   
('Hard Disk Drive', 'Storage'),  
('Floppy Drive', 'Storage'),  
('LCD Panel', 'Monitor');

-- 生成100條銷售訂單表測試資料
DROP PROCEDURE IF EXISTS generate_sales_order_data;
DELIMITER //  
CREATE PROCEDURE generate_sales_order_data()
BEGIN 
    DROP TABLE IF EXISTS temp_sales_order_data;
    CREATE TABLE temp_sales_order_data AS SELECT * FROM sales_order WHERE 1=0;

    SET @start_date := unix_timestamp('2016-03-01');
    SET @end_date := unix_timestamp('2016-07-01');
    SET @i := 1;

    WHILE @i<=100 DO
        SET @customer_number := floor(1 + rand() * 6);
        SET @product_code := floor(1 + rand() * 2);
        SET @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));
        SET @amount := floor(1000 + rand() * 9000);

        INSERT INTO temp_sales_order_data VALUES (@i,@customer_number,@product_code,@order_date,@order_date,@amount);
        SET @i:=@i+1;
    END WHILE;

    TRUNCATE TABLE sales_order;
    INSERT INTO sales_order 
    SELECT NULL,customer_number,product_code,order_date,entry_date,order_amount FROM temp_sales_order_data ORDER BY order_date;
    COMMIT;

END 
//  
DELIMITER ; 

CALL generate_sales_order_data();

（3）建立RDS庫表
        建立RDS庫表的HiveQL指令碼如下：

-- 建立RDS資料庫  
DROP DATABASE IF EXISTS rds CASCADE;
CREATE DATABASE rds;

-- 建立RDS庫表
USE rds;
  
-- 建立客戶過渡表  
CREATE TABLE customer (  
    customer_number INT comment 'number',
    customer_name VARCHAR(30) comment 'name',
    customer_street_address VARCHAR(30) comment 'address',
    customer_zip_code INT comment 'zipcode',
    customer_city VARCHAR(30) comment 'city',
    customer_state VARCHAR(2) comment 'state'
);

-- 建立產品過渡表  
CREATE TABLE product (  
    product_code INT comment 'code',  
    product_name VARCHAR(30) comment 'name',  
    product_category VARCHAR(30) comment 'category'  
);

-- 建立銷售訂單過渡表
CREATE TABLE sales_order (  
    order_number INT comment 'order number',  
    customer_number INT comment 'customer number',  
    product_code INT comment 'product code',  
    order_date TIMESTAMP comment 'order date',  
    entry_date TIMESTAMP comment 'entry date',  
    order_amount DECIMAL(10 , 2 ) comment 'order amount'
);

（4）建立TDS庫表
        建立TDS庫表的HiveQL指令碼如下：

-- 建立資料倉儲資料庫  
DROP DATABASE IF EXISTS dw CASCADE;  
CREATE DATABASE dw;

-- 建立資料倉儲表  
USE dw;
  
-- 建立客戶維度表  
CREATE TABLE customer_dim (  
    customer_sk INT comment 'surrogate key',  
    customer_number INT comment 'number',  
    customer_name VARCHAR(50) comment 'name',  
    customer_street_address VARCHAR(50) comment 'address',  
    customer_zip_code INT comment 'zipcode',  
    customer_city VARCHAR(30) comment 'city',  
    customer_state VARCHAR(2) comment 'state',
    version INT comment 'version',
    effective_date DATE comment 'effective date',  
    expiry_date DATE comment 'expiry date'  
)
CLUSTERED BY (customer_sk) INTO 8 BUCKETS  
STORED AS ORC TBLPROPERTIES ('transactional'='true');
  
-- 建立產品維度表  
CREATE TABLE product_dim (  
    product_sk INT comment 'surrogate key',  
    product_code INT comment 'code',  
    product_name VARCHAR(30) comment 'name',  
    product_category VARCHAR(30) comment 'category',
    version INT comment 'version',
    effective_date DATE comment 'effective date',  
    expiry_date DATE comment 'expiry date'  
)
CLUSTERED BY (product_sk) INTO 8 BUCKETS  
STORED AS ORC TBLPROPERTIES ('transactional'='true');
  
-- 建立訂單維度表  
CREATE TABLE order_dim (  
    order_sk INT comment 'surrogate key',  
    order_number INT comment 'number', 
    version INT comment 'version',
    effective_date DATE comment 'effective date',
    expiry_date DATE comment 'expiry date'  
)
CLUSTERED BY (order_sk) INTO 8 BUCKETS  
STORED AS ORC TBLPROPERTIES ('transactional'='true');
  
-- 建立銷售訂單事實表  
CREATE TABLE sales_order_fact (  
    order_sk INT comment 'order surrogate key',  
    customer_sk INT comment 'customer surrogate key',  
    product_sk INT comment 'product surrogate key',  
    order_date_sk INT comment 'date surrogate key',  
    order_amount DECIMAL(10 , 2 ) comment 'order amount' 
)
CLUSTERED BY (order_sk) INTO 8 BUCKETS  
STORED AS ORC TBLPROPERTIES ('transactional'='true');

（5）建立日期維度表並生成資料
        使用下面的shell命令建立日期維度表並生成資料：

./date_dim_generate.sh 2000-01-01 2020-12-31

        date_dim_generate.sh shell指令碼檔案內容如下圖所示：

        create_table_date_dim.sql SQL指令碼內容如下：

drop table if exists date_dim;
create table date_dim (  
    date_sk int comment 'surrogate key',
    date date comment 'date,yyyy-mm-dd',
    month tinyint comment 'month',
    month_name varchar(9) comment 'month name',
    quarter tinyint comment 'quarter',
    year smallint comment 'year'
)
comment 'date dimension table'
row format delimited fields terminated by ','
stored as textfile;

        說明：
        a）HiveQL指令碼中的列註釋沒有使用中文，這是因為Hive 1.1.0中，中文註釋會在show create table命令中顯示亂碼，要解決這個問題需要重新編譯Hive的原始碼，簡單起見，這裡都是用了英文列註釋。關於1.1.0中的這個bug，可參考https://issues.apache.org/jira/browse/HIVE-11837。示例資料中沒有中文，也是出於類似的原因。
        b）維度表雖然使用了代理鍵，但不能將它設定為主鍵，在資料庫級也不能確保其唯一性。Hive中並沒有主外來鍵、唯一非空約束這些關聯式資料庫的概念。
        c）sales_order.entry_date表示訂單登記的日期，一般情況下應該等同於訂單日期（sales_order.order_date），但有時兩者是不同的，等實驗進行到“遲到的事實”時會詳細說明。
        d）關於日期維度資料裝載
        日期維度在資料倉儲中是一個特殊角色。日期維度包含時間概念，而時間是最重要的，因為資料倉儲的主要功能之一就是儲存歷史資料，所以每個資料倉儲裡的資料都有一個時間特徵。裝載日期資料有三個常用方法：

• 預裝載
• 每日裝載一天
• 從源資料裝載日期

        在三種方法中，預裝載最容易，也是本實驗所採用的方法。使用預裝載插入一個時間段裡的所有日期。比如，本示例預裝載21年的日期維度資料，從2000年1月1日到2020年12月31日。使用這個方法，在資料倉儲生命週期中，只需要預裝載日期維度一次。預裝載的缺點是：

• 提早消耗磁碟空間
• 可能不需要所有的日期（稀疏使用）