Hive數據倉庫與企業級優化

一 數據倉庫

1 什麼是數據倉庫

數據倉庫，英文名稱為Data Warehouse，可簡寫為DW或DWH。數據倉庫，是為企業所有級別的決策制定過程，提供所有類型數據支持的戰略集合。它出於分析性報告和決策支持目的而創建。為需要業務智能的企業，提供指導業務流程改進、監視時間、成本、質量以及控制。

2 數據倉庫能幹什麼？

1）年度銷售目標的指定，需要根據以往的歷史報表進行決策，不能拍腦袋。

2）如何優化業務流程

例如：一個電商網站訂單的完成包括：瀏覽、下單、支付、物流，其中物流環節可能和中通、申通、韻達等快遞公司合作。快遞公司每派送一個訂單，都會有訂單派送的確認時間，可以根據訂單派送時間來分析哪個快遞公司比較快捷高效，從而選擇與哪些快遞公司合作，剔除哪些快遞公司，增加用戶友好型。

3 數據倉庫的特點

1）數據倉庫的數據是面向主題的

與傳統資料庫面嚮應用進行數據組織的特點相對應，數據倉庫中的數據是面向主題進行組織的。什麼是主題呢？首先，主題是一個抽象的概念，是較高層次上企業信息系統中的數據綜合、歸類併進行分析利用的抽象。在邏輯意義上，它是對應企業中某一巨集觀分析領域所涉及的分析對象。面向主題的數據組織方式，就是在較高層次上對分析對象的數據的一個完整、一致的描述，能完整、統一地刻劃各個分析對象所涉及的企業的各項數據，以及數據之間的聯繫。所謂較高層次是相對面嚮應用的數據組織方式而言的，是指按照主題進行數據組織的方式具有更高的數據抽象級別。

2）數據倉庫的數據是集成的

數據倉庫的數據是從原有的分散的資料庫數據抽取來的。操作型數據與DSS分析型數據之間差別甚大。第一，數據倉庫的每一個主題所對應的源數據在原有的各分散資料庫中有許多重覆和不一致的地方，且來源於不同的聯機系統的數據都和不同的應用邏輯捆綁在一起；第二，數據倉庫中的綜合數據不能從原有的資料庫系統直接得到。因此在數據進入數據倉庫之前，必然要經過統一與綜合，這一步是數據倉庫建設中最關鍵、最複雜的一步，所要完成的工作有：

（1）要統一源數據中所有矛盾之處，如欄位的同名異義、異名同義、單位不統一、字長不一致等。

（2）進行數據綜合和計算。數據倉庫中的數據綜合工作可以在從原有資料庫抽取 數據時生成，但許多是在數據倉庫內部生成的，即進入數據倉庫以後進行綜合生成的。

3）數據倉庫的數據是不可更新的

數據倉庫的數據主要供企業決策分析之用，所涉及的數據操作主要是數據查詢，一般情況下並不進行修改操作。數據倉庫的數據反映的是一段相當長的時間內歷史數據的內容，是不同時點的資料庫快照的集合，以及基於這些快照進行統計、綜合和重組的導出數據，而不是聯機處理的數據。資料庫中進行聯機處理的數據經過集成輸入到數據倉庫中，一旦數據倉庫存放的數據已經超過數據倉庫的數據存儲期限，這些數據將從當前的數據倉庫中刪去。因為數據倉庫只進行數據查詢操作，所以數據倉庫管理系統相比資料庫管理系統而言要簡單得多。資料庫管理系統中許多技術難點，如完整性保護、併發控制等等，在數據倉庫的管理中幾乎可以省去。但是由於數據倉庫的查詢數據量往往很大，所以就對數據查詢提出了更高的要求，它要求採用各種複雜的索引技術；同時由於數據倉庫面向的是商業企業的高層管理者，他們會對數據查詢的界面友好性和數據表示提出更高的要求。

4）數據倉庫的數據是隨時間不斷變化的

數據倉庫中的數據不可更新是針對應用來說的，也就是說，數據倉庫的用戶進行分析處理時是不進行數據更新操作的。但並不是說，在從數據集成輸入數據倉庫開始到最終被刪除的整個數據生存周期中，所有的數據倉庫數據都是永遠不變的。

數據倉庫的數據是隨時間的變化而不斷變化的，這是數據倉庫數據的第四個特征。這一特征表現在以下3方面：

（1）數據倉庫隨時間變化不斷增加新的數據內容。數據倉庫系統必須不斷捕捉OLTP資料庫中變化的數據，追加到數據倉庫中去，也就是要不斷地生成OLTP資料庫的快照，經統一集成後增加到數據倉庫中去；但對於確實不再變化的資料庫快照，如果捕捉到新的變化數據，則只生成一個新的資料庫快照增加進去，而不會對原有的資料庫快照進行修改。

（2）數據倉庫隨時間變化不斷刪去舊的數據內容。數據倉庫的數據也有存儲期限，一旦超過了這一期限，過期數據就要被刪除。只是數據倉庫內的數據時限要遠遠長於操作型環境中的數據時限。在操作型環境中一般只保存有60~90天的數據，而在數據倉庫中則需要保存較長時限的數據（如5~10年），以適應DSS進行趨勢分析的要求。

（3）數據倉庫中包含有大量的綜合數據，這些綜合數據中很多跟時間有關，如數據經常按照時間段進行綜合，或隔一定的時間片進行抽樣等等。這些數據要隨著時間的變化不斷地進行重新綜合。因此，數據倉庫的數據特征都包含時間項，以標明數據的歷史時期。

4 數據倉庫發展歷程

數據倉庫的發展大致經歷了這樣的三個過程：

1）簡單報表階段：這個階段，系統的主要目標是解決一些日常的工作中業務人員需要的報表，以及生成一些簡單的能夠幫助領導進行決策所需要的彙總數據。這個階段的大部分表現形式為資料庫和前端報表工具。

2）數據集市階段：這個階段，主要是根據某個業務部門的需要，進行一定的數據的採集，整理，按照業務人員的需要，進行多維報表的展現，能夠提供對特定業務指導的數據，並且能夠提供特定的領導決策數據。

3）數據倉庫階段：這個階段，主要是按照一定的數據模型，對整個企業的數據進行採集，整理，並且能夠按照各個業務部門的需要，提供跨部門的，完全一致的業務報表數據，能夠通過數據倉庫生成對對業務具有指導性的數據，同時，為領導決策提供全面的數據支持。

通過數據倉庫建設的發展階段，我們能夠看出，數據倉庫的建設和數據集市的建設的重要區別就在於數據模型的支持。因此，數據模型的建設，對於我們數據倉庫的建設，有著決定性的意義。

5 資料庫與數據倉庫的區別

瞭解資料庫與數據倉庫的區別之前，首先掌握三個概念。資料庫軟體、資料庫、數據倉庫。

資料庫軟體：是一種軟體，可以看得見，可以操作。用來實現資料庫邏輯功能。屬於物理層。

資料庫：是一種邏輯概念，用來存放數據的倉庫。通過資料庫軟體來實現。資料庫由很多表組成，表是二維的，一張表裡可以有很多欄位。欄位一字排開，對應的數據就一行一行寫入表中。資料庫的表，在於能夠用二維表現多維關係。目前市面上流行的資料庫都是二維資料庫。如：Oracle、DB2、MySQL、Sybase、MS SQL Server等。

數據倉庫：是資料庫概念的升級。從邏輯上理解，資料庫和數據倉庫沒有區別，都是通過資料庫軟體實現的存放數據的地方，只不過從數據量來說，數據倉庫要比資料庫更龐大得多。數據倉庫主要用於數據挖掘和數據分析，輔助領導做決策。

在IT的架構體系中，資料庫是必須存在的。必須要有地方存放數據。比如現在的網購，淘寶，京東等等。物品的存貨數量，貨品的價格，用戶的賬戶餘額之類的。這些數據都是存放在後臺資料庫中。或者最簡單理解，我們現在微博，QQ等賬戶的用戶名和密碼。在後臺資料庫必然有一張user表，欄位起碼有兩個，即用戶名和密碼，然後我們的數據就一行一行的存在表上面。當我們登錄的時候，我們填寫了用戶名和密碼，這些數據就會被傳回到後臺去，去跟表上面的數據匹配，匹配成功了，你就能登錄了。匹配不成功就會報錯說密碼錯誤或者沒有此用戶名等。這個就是資料庫，資料庫在生產環境就是用來幹活的。凡是跟業務應用掛鉤的，我們都使用資料庫。

數據倉庫則是BI下的其中一種技術。由於資料庫是跟業務應用掛鉤的，所以一個資料庫不可能裝下一家公司的所有數據。資料庫的表設計往往是針對某一個應用進行設計的。比如剛纔那個登錄的功能，這張user表上就只有這兩個欄位，沒有別的欄位了。但是這張表符合應用，沒有問題。但是這張表不符合分析。比如我想知道在哪個時間段，用戶登錄的量最多？哪個用戶一年購物最多？諸如此類的指標。那就要重新設計資料庫的表結構了。對於數據分析和數據挖掘，我們引入數據倉庫概念。數據倉庫的表結構是依照分析需求，分析維度，分析指標進行設計的。

資料庫與數據倉庫的區別實際講的是OLTP與OLAP的區別。

操作型處理，叫聯機事務處理OLTP（On-Line Transaction Processing），也可以稱面向交易的處理系統，它是針對具體業務在資料庫聯機的日常操作，通常對少數記錄進行查詢、修改。用戶較為關心操作的響應時間、數據的安全性、完整性和併發支持的用戶數等問題。傳統的資料庫系統作為數據管理的主要手段，主要用於操作型處理。

分析型處理，叫聯機分析處理OLAP（On-Line Analytical Processing）一般針對某些主題的歷史數據進行分析，支持管理決策。

表 操作型處理與分析型處理的比較

6 數據倉庫架構分層

6.1 數據倉庫架構

數據倉庫標準上可以分為四層：ODS（臨時存儲層）、PDW（數據倉庫層）、DM（數據集市層）、APP（應用層）。

1）ODS層：

為臨時存儲層，是介面數據的臨時存儲區域，為後一步的數據處理做準備。一般來說ODS層的數據和源系統的數據是同構的，主要目的是簡化後續數據加工處理的工作。從數據粒度上來說ODS層的數據粒度是最細的。ODS層的表通常包括兩類，一個用於存儲當前需要載入的數據，一個用於存儲處理完後的歷史數據。歷史數據一般保存3-6個月後需要清除，以節省空間。但不同的項目要區別對待，如果源系統的數據量不大，可以保留更長的時間，甚至全量保存；

2）PDW層：

為數據倉庫層，PDW層的數據應該是一致的、準確的、乾凈的數據，即對源系統數據進行了清洗（去除了雜質）後的數據。這一層的數據一般是遵循資料庫第三範式的，其數據粒度通常和ODS的粒度相同。在PDW層會保存BI系統中所有的歷史數據，例如保存10年的數據。

3）DM層：

為數據集市層，這層數據是面向主題來組織數據的，通常是星形或雪花結構的數據。從數據粒度來說，這層的數據是輕度彙總級的數據，已經不存在明細數據了。從數據的時間跨度來說，通常是PDW層的一部分，主要的目的是為了滿足用戶分析的需求，而從分析的角度來說，用戶通常只需要分析近幾年（如近三年的數據）的即可。從數據的廣度來說，仍然覆蓋了所有業務數據。

4）APP層：

為應用層，這層數據是完全為了滿足具體的分析需求而構建的數據，也是星形或雪花結構的數據。從數據粒度來說是高度彙總的數據。從數據的廣度來說，則並不一定會覆蓋所有業務數據，而是DM層數據的一個真子集，從某種意義上來說是DM層數據的一個重覆。從極端情況來說，可以為每一張報表在APP層構建一個模型來支持，達到以空間換時間的目的數據倉庫的標準分層只是一個建議性質的標準，實際實施時需要根據實際情況確定數據倉庫的分層，不同類型的數據也可能採取不同的分層方法。

6.2 為什麼要對數據倉庫分層？

1）用空間換時間，通過大量的預處理來提升應用系統的用戶體驗（效率），因此數據倉庫會存在大量冗餘的數據。

2）如果不分層的話，如果源業務系統的業務規則發生變化將會影響整個數據清洗過程，工作量巨大。

3）通過數據分層管理可以簡化數據清洗的過程，因為把原來一步的工作分到了多個步驟去完成，相當於把一個複雜的工作拆成了多個簡單的工作，把一個大的黑盒變成了一個白盒，每一層的處理邏輯都相對簡單和容易理解，這樣我們比較容易保證每一個步驟的正確性，當數據發生錯誤的時候，往往我們只需要局部調整某個步驟即可。

7 元數據介紹

當需要瞭解某地企業及其提供的服務時，電話黃頁的重要性就體現出來了。元數據（Metadata）類似於這樣的電話黃頁。

1）元數據的定義

    數據倉庫的元數據是關於數據倉庫中數據的數據。它的作用類似於資料庫管理系統的數據字典，保存了邏輯數據結構、文件、地址和索引等信息。廣義上講，在數據倉庫中，元數據描述了數據倉庫內數據的結構和建立方法的數據。

      元數據是數據倉庫管理系統的重要組成部分，元數據管理器是企業級數據倉庫中的關鍵組件，貫穿數據倉庫構建的整個過程，直接影響著數據倉庫的構建、使用和維護。

（1）構建數據倉庫的主要步驟之一是ETL。這時元數據將發揮重要的作用，它定義了源數據系統到數據倉庫的映射、數據轉換的規則、數據倉庫的邏輯結構、數據更新的規則、數據導入歷史記錄以及裝載周期等相關內容。數據抽取和轉換的專家以及數據倉庫管理員正是通過元數據高效地構建數據倉庫。

（2）用戶在使用數據倉庫時，通過元數據訪問數據，明確數據項的含義以及定製報表。

（3）數據倉庫的規模及其複雜性離不開正確的元數據管理，包括增加或移除外部數據源，改變數據清洗方法，控制出錯的查詢以及安排備份等。

元數據可分為技術元數據和業務元數據。技術元數據為開發和管理數據倉庫的IT人員使用，它描述了與數據倉庫開發、管理和維護相關的數據，包括數據源信息、數據轉換描述、數據倉庫模型、數據清洗與更新規則、數據映射和訪問許可權等。而業務元數據為管理層和業務分析人員服務，從業務角度描述數據，包括商務術語、數據倉庫中有什麼數據、數據的位置和數據的可用性等，幫助業務人員更好地理解數據倉庫中哪些數據是可用的以及如何使用。

由上可見，元數據不僅定義了數據倉庫中數據的模式、來源、抽取和轉換規則等，而且是整個數據倉庫系統運行的基礎，元數據把數據倉庫系統中各個鬆散的組件聯繫起來，組成了一個有機的整體，如圖所示

2）元數據的存儲方式

     元數據有兩種常見存儲方式：一種是以數據集為基礎，每一個數據集有對應的元數據文件，每一個元數據文件包含對應數據集的元數據內容；另一種存儲方式是以資料庫為基礎，即元資料庫。其中元數據文件由若幹項組成，每一項表示元數據的一個要素，每條記錄為數據集的元數據內容。上述存儲方式各有優缺點，第一種存儲方式的優點是調用數據時相應的元數據也作為一個獨立的文件被傳輸，相對資料庫有較強的獨立性，在對元數據進行檢索時可以利用資料庫的功能實現，也可以把元數據文件調到其他資料庫系統中操作；不足是如果每一數據集都對應一個元數據文檔，在規模巨大的資料庫中則會有大量的元數據文件，管理不方便。第二種存儲方式下，元資料庫中只有一個元數據文件，管理比較方便，添加或刪除數據集，只要在該文件中添加或刪除相應的記錄項即可。在獲取某數據集的元數據時，因為實際得到的只是關係表格數據的一條記錄，所以要求用戶系統可以接受這種特定形式的數據。因此推薦使用元資料庫的方式。

      元資料庫用於存儲元數據，因此元資料庫最好選用主流的關係資料庫管理系統。元資料庫還包含用於操作和查詢元數據的機制。建立元資料庫的主要好處是提供統一的數據結構和業務規則，易於把企業內部的多個數據集市有機地集成起來。目前，一些企業傾向建立多個數據集市，而不是一個集中的數據倉庫，這時可以考慮在建立數據倉庫（或數據集市）之前，先建立一個用於描述數據、服務應用集成的元資料庫，做好數據倉庫實施的初期支持工作，對後續開發和維護有很大的幫助。元資料庫保證了數據倉庫數據的一致性和準確性，為企業進行數據質量管理提供基礎。

3）元數據的作用

      在數據倉庫中，元數據的主要作用如下。

（1）描述哪些數據在數據倉庫中，幫助決策分析者對數據倉庫的內容定位。

（2）定義數據進入數據倉庫的方式，作為數據彙總、映射和清洗的指南。

（3）記錄業務事件發生而隨之進行的數據抽取工作時間安排。

（4）記錄並檢測系統數據一致性的要求和執行情況。

（5）評估數據質量。

8 星型模型和雪花模型

在多維分析的商業智能解決方案中，根據事實表和維度表的關係，又可將常見的模型分為星型模型和雪花型模型。在設計邏輯型數據的模型的時候，就應考慮數據是按照星型模型還是雪花型模型進行組織。

8.1 星型模型

當所有維表都直接連接到“ 事實表”上時，整個圖解就像星星一樣，故將該模型稱為星型模型。

星型架構是一種非正規化的結構，多維數據集的每一個維度都直接與事實表相連接，不存在漸變維度，所以數據有一定的冗餘，如在地域維度表中，存在國家A 省B的城市C以及國家A省B的城市D兩條記錄，那麼國家A和省B的信息分別存儲了兩次，即存在冗餘。

8.2 雪花模型

當有一個或多個維表沒有直接連接到事實表上，而是通過其他維表連接到事實表上時，其圖解就像多個雪花連接在一起，故稱雪花模型。雪花模型是對星型模型的擴展。它對星型模型的維表進一步層次化，原有的各維表可能被擴展為小的事實表，形成一些局部的" 層次" 區域，這些被分解的表都連接到主維度表而不是事實表。如圖所示，將地域維表又分解為國家，省份，城市等維表。它的優點是：通過最大限度地減少數據存儲量以及聯合較小的維表來改善查詢性能。雪花型結構去除了數據冗餘。

星型模型因為數據的冗餘所以很多統計查詢不需要做外部的連接，因此一般情況下效率比雪花型模型要高。星型結構不用考慮很多正規化的因素，設計與實現都比較簡單。雪花型模型由於去除了冗餘，有些統計就需要通過表的聯接才能產生，所以效率不一定有星型模型高。正規化也是一種比較複雜的過程，相應的資料庫結構設計、數據的 ETL、以及後期的維護都要複雜一些。因此在冗餘可以接受的前提下，實際運用中星型模型使用更多，也更有效率。

8.3 星型模型和雪花模型對比

星形模型和雪花模型是數據倉庫中常用到的兩種方式，而它們之間的對比要從四個角度來進行討論。

　　1）數據優化

雪花模型使用的是規範化數據，也就是說數據在資料庫內部是組織好的，以便消除冗餘，因此它能夠有效地減少數據量。通過引用完整性，其業務層級和維度都將存儲在數據模型之中。

雪花模型

相比較而言，星形模型使用的是反規範化數據。在星形模型中，維度直接指的是事實表，業務層級不會通過維度之間的參照完整性來部署。

星形模型

　　2）業務模型

主鍵是一個單獨的唯一鍵(數據屬性)，為特殊數據所選擇。在上面的例子中，Advertiser_ID就將是一個主鍵。外鍵(參考屬性)僅僅是一個表中的欄位，用來匹配其他維度表中的主鍵。在我們所引用的例子中，Advertiser_ID將是Account_dimension的一個外鍵。

在雪花模型中，數據模型的業務層級是由一個不同維度表主鍵-外鍵的關係來代表的。而在星形模型中，所有必要的維度表在事實表中都只擁有外鍵。

　　3）性能

第三個區別在於性能的不同。雪花模型在維度表、事實表之間的連接很多，因此性能方面會比較低。舉個例子，如果你想要知道Advertiser 的詳細信息，雪花模型就會請求許多信息，比如Advertiser Name、ID以及那些廣告主和客戶表的地址需要連接起來，然後再與事實表連接。

而星形模型的連接就少的多，在這個模型中，如果你需要上述信息，你只要將Advertiser的維度表和事實表連接即可。

　　4）ETL

雪花模型載入數據集市，因此ETL操作在設計上更加複雜，而且由於附屬模型的限制，不能並行化。

星形模型載入維度表，不需要再維度之間添加附屬模型，因此ETL就相對簡單，而且可以實現高度的並行化。

　　總結

雪花模型使得維度分析更加容易，比如“針對特定的廣告主，有哪些客戶或者公司是線上的?”星形模型用來做指標分析更適合，比如“給定的一個客戶他們的收入是多少?”

二 企業級優化

2.1 Fetch抓取

1）理論分析

Fetch抓取是指，Hive中對某些情況的查詢可以不必使用MapReduce計算。例如：SELECT * FROM employees;在這種情況下，Hive可以簡單地讀取employee對應的存儲目錄下的文件，然後輸出查詢結果到控制台。

在hive-default.xml.template文件中hive.fetch.task.conversion預設是more，老版本hive預設是minimal，該屬性修改為more以後，在全局查找、欄位查找、limit查找等都不走mapreduce。

<property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
    <description>
      Expects one of [none, minimal, more].
      Some select queries can be converted to single FETCH task minimizing latency.
      Currently the query should be single sourced not having any subquery and should not have
      any aggregations or distincts (which incurs RS), lateral views and joins.
      0. none : disable hive.fetch.task.conversion
      1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
      2. more  : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
    </description>
  </property>

2）案例實操：

（1）把hive.fetch.task.conversion設置成none，然後執行查詢語句，都會執行mapreduce程式。

hive (default)> set hive.fetch.task.conversion=none;

hive (default)> select * from emp;

hive (default)> select ename from emp;

hive (default)> select ename from emp limit 3;

（2）把hive.fetch.task.conversion設置成more，然後執行查詢語句，如下查詢方式都不會執行mapreduce程式。

hive (default)> set hive.fetch.task.conversion=more;

hive (default)> select * from emp;

hive (default)> select ename from emp;

hive (default)> select ename from emp limit 3;

2.2 本地模式

1）理論分析

大多數的Hadoop Job是需要Hadoop提供的完整的可擴展性來處理大數據集的。不過，有時Hive的輸入數據量是非常小的。在這種情況下，為查詢觸發執行任務時消耗可能會比實際job的執行時間要多的多。對於大多數這種情況，Hive可以通過本地模式在單台機器上處理所有的任務。對於小數據集，執行時間可以明顯被縮短。

用戶可以通過設置hive.exec.mode.local.auto的值為true，來讓Hive在適當的時候自動啟動這個優化。

2）案例實操：

（1）開啟本地模式，並執行查詢語句

hive (default)> set hive.exec.mode.local.auto=true; 

hive (default)> select * from emp cluster by deptno;

Time taken: 1.328 seconds, Fetched: 14 row(s)

（2）關閉本地模式，並執行查詢語句

hive (default)> set hive.exec.mode.local.auto=false; 

hive (default)> select * from emp cluster by deptno;

Time taken: 20.09 seconds, Fetched: 14 row(s)；

2.3 表的優化

2.3.1 小表、大表Join

1）理論分析

將key相對分散，並且數據量小的表放在join的左邊，這樣可以有效減少記憶體溢出錯誤發生的幾率；再進一步，可以使用Group讓小的維度表（1000條以下的記錄條數）先進記憶體。在map端完成reduce。

實際測試發現：新版的hive已經對小表JOIN大表和大表JOIN小表進行了優化。小表放在左邊和右邊已經沒有明顯區別。

2）案例實操

（0）需求：測試大表JOIN小表和小表JOIN大表的效率

（1）建大表、小表和JOIN後表的語句