一 數據倉庫
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）一般針對某些主題的歷史數據進行分析，支持管理決策。
表 操作型處理與分析型處理的比較
操作型處理分析型處理
細節的綜合的或提煉的
實體——關係（E-R）模型星型模型或雪花模型
存取瞬間數據存儲歷史數據，不包含最近的數據
可更新的只讀、只追加
一次操作一個單元一次操作一個集合
性能要求高，響應時間短性能要求寬鬆
面向事務面向分析
一次操作數據量小一次操作數據量大
支持日常操作支持決策需求
數據量小數據量大
客戶訂單、庫存水平和銀行賬戶查詢等客戶收益分析、市場細分等
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。
hive.fetch.task.conversion more 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)
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在適當的時候自動啟動這個優化。
set hive.exec.mode.local.auto=true; //開啟本地mr
//設置local mr的最大輸入數據量，當輸入數據量小於這個值時採用local mr的方式，默認為134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//設置local mr的最大輸入文件個數，當輸入文件個數小於這個值時採用local mr的方式，默認為4
set hive.exec.mode.local.auto.input.files.max=10;
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後表的語句
create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
create table smalltable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
（2）分別向大表和小表中導入數據
hive (default)> load data local inpath '/opt/module/datas/bigtable' into table bigtable;
hive (default)>load data local inpath '/opt/module/datas/smalltable' into table smalltable;
（3）關閉mapjoin功能（默認是打開的）
set hive.auto.convert.join = false;
（4）執行小表JOIN大表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
left join bigtable b
on b.id = s.id;
Time taken: 35.921 seconds
（5）執行大表JOIN小表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable b
left join smalltable s
on s.id = b.id;
Time taken: 34.196 seconds；
2.3.2 大表Join大表
1）空KEY過濾
有時join超時是因為某些key對應的數據太多，而相同key對應的數據都會發送到相同的reducer上，從而導致內存不夠。此時我們應該仔細分析這些異常的key，很多情況下，這些key對應的數據是異常數據，我們需要在SQL語句中進行過濾。例如key對應的字段為空，操作如下：
案例實操
（1）配置歷史服務器
配置mapred-site.xml
mapreduce.jobhistory.address
node21:10020
mapreduce.jobhistory.webapp.address
node21:19888
啟動歷史服務器
sbin/mr-jobhistory-daemon.sh start historyserver
查看jobhistory
http://node21:19888/jobhistory
（2）創建原始數據表、空id表、合併後數據表
create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
（3）分別加載原始數據和空id數據到對應表中
hive (default)> load data local inpath '/opt/module/datas/ori' into table ori;
hive (default)> load data local inpath '/opt/module/datas/nullid' into table nullidtable;
（4）測試不過濾空id
hive (default)> insert overwrite table jointable
select n.* from nullidtable n left join ori o on n.id = o.id;
Time taken: 42.038 seconds
（5）測試過濾空id
hive (default)> insert overwrite table jointable
select n.* from (select * from nullidtable where id is not null ) n left join ori o on n.id = o.id;
Time taken: 31.725 seconds
2）空key轉換
有時雖然某個key為空對應的數據很多，但是相應的數據不是異常數據，必須要包含在join的結果中，此時我們可以表a中key為空的字段賦一個隨機的值，使得數據隨機均勻地分不到不同的reducer上。例如：
案例實操：
不隨機分佈空null值：
（1）設置5個reduce個數
set mapreduce.job.reduces = 5;
（2）JOIN兩張表
insert overwrite table jointable
select n.* from nullidtable n left join ori b on n.id = b.id;
結果：可以看出來，出現了數據傾斜，某些reducer的資源消耗遠大於其他reducer。
隨機分佈空null值
（1）設置5個reduce個數
set mapreduce.job.reduces = 5;
（2）JOIN兩張表
insert overwrite table jointable
select n.* from nullidtable n full join ori o on
case when n.id is null then concat('hive', rand()) else n.id end = o.id;
結果：可以看出來，消除了數據傾斜，負載均衡reducer的資源消耗
2.3.3 MapJoin
理論分析
如果不指定MapJoin或者不符合MapJoin的條件，那麼Hive解析器會將Join操作轉換成Common Join，即：在Reduce階段完成join。容易發生數據傾斜。可以用MapJoin把小表全部加載到內存在map端進行join，避免reducer處理。
1）開啟MapJoin參數設置：
（1）設置自動選擇Mapjoin
set hive.auto.convert.join = true; 默認為true
（2）大表小表的閥值設置（默認25M一下認為是小表）：
set hive.mapjoin.smalltable.filesize=25000000;
2）MapJoin工作機制
首先是Task A，它是一個Local Task（在客戶端本地執行的Task），負責掃描小表b的數據，將其轉換成一個HashTable的數據結構，並寫入本地的文件中，之後將該文件加載到DistributeCache中。
接下來是Task B，該任務是一個沒有Reduce的MR，啟動MapTasks掃描大表a,在Map階段，根據a的每一條記錄去和DistributeCache中b表對應的HashTable關聯，並直接輸出結果。
由於MapJoin沒有Reduce，所以由Map直接輸出結果文件，有多少個Map Task，就有多少個結果文件。
案例實操：
（1）開啟Mapjoin功能
set hive.auto.convert.join = true; 默認為true
（2）執行小表JOIN大表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from smalltable s
join bigtable b
on s.id = b.id;
Time taken: 24.594 seconds
（3）執行大表JOIN小表語句
insert overwrite table jointable
select b.id, b.time, b.uid, b.keyword, b.url_rank, b.click_num, b.click_url
from bigtable b
join smalltable s
on s.id = b.id;
Time taken: 24.315 seconds
2.3.4 Group By
默認情況下，Map階段同一Key數據分發給一個reduce，當一個key數據過大時就傾斜了。
並不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端進行部分聚合，最後在Reduce端得出最終結果。
1）開啟Map端聚合參數設置
（1）是否在Map端進行聚合，默認為True
hive.map.aggr = true
（2）在Map端進行聚合操作的條目數目
hive.groupby.mapaggr.checkinterval = 100000
（3）有數據傾斜的時候進行負載均衡（默認是false）
hive.groupby.skewindata = true
當選項設定為 true，生成的查詢計劃會有兩個MR Job。第一個MR Job中，Map的輸出結果會隨機分佈到Reduce中，每個Reduce做部分聚合操作，並輸出結果，這樣處理的結果是相同的Group By Key有可能被分發到不同的Reduce中，從而達到負載均衡的目的；第二個MR Job再根據預處理的數據結果按照Group By Key分佈到Reduce中（這個過程可以保證相同的Group By Key被分佈到同一個Reduce中），最後完成最終的聚合操作。
2.3.5 Count(Distinct) 去重統計
數據量小的時候無所謂，數據量大的情況下，由於COUNT DISTINCT操作需要用一個Reduce Task來完成，這一個Reduce需要處理的數據量太大，就會導致整個Job很難完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替換：
案例實操
（1）創建一張大表
hive (default)> create table bigtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\\t';
（2）加載數據
hive (default)> load data local inpath '/opt/module/datas/bigtable' into table bigtable;
（3）設置5個reduce個數
set mapreduce.job.reduces = 5;
（4）執行去重id查詢
hive (default)> select count(distinct id) from bigtable;
Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 7.12 sec HDFS Read: 120741990 HDFS Write: 7 SUCCESS
Total MapReduce CPU Time Spent: 7 seconds 120 msec
OK
c0
100001
Time taken: 23.607 seconds, Fetched: 1 row(s)
Time taken: 34.941 seconds, Fetched: 1 row(s)
（5）採用GROUP by去重id
hive (default)> select count(id) from (select id from bigtable group by id) a;
Stage-Stage-1: Map: 1 Reduce: 5 Cumulative CPU: 17.53 sec HDFS Read: 120752703 HDFS Write: 580 SUCCESS
Stage-Stage-2: Map: 3 Reduce: 1 Cumulative CPU: 4.29 sec HDFS Read: 9409 HDFS Write: 7 SUCCESS
Total MapReduce CPU Time Spent: 21 seconds 820 msec
OK
_c0
100001
Time taken: 50.795 seconds, Fetched: 1 row(s)
雖然會多用一個Job來完成，但在數據量大的情況下，這個絕對是值得的。
2.3.6 笛卡爾積
儘量避免笛卡爾積，join的時候不加on條件，或者無效的on條件，Hive只能使用1個reducer來完成笛卡爾積
2.3.7 行列過濾
列處理：在SELECT中，只拿需要的列，如果有，儘量使用分區過濾，少用SELECT *。
行處理：在分區剪裁中，當使用外關聯時，如果將副表的過濾條件寫在Where後面，那麼就會先全表關聯，之後再過濾，比如：
案例實操：
（1）測試先關聯兩張表，再用where條件過濾
hive (default)> select o.id from bigtable b
join ori o on o.id = b.id
where o.id <= 10;
Time taken: 34.406 seconds, Fetched: 100 row(s)
Time taken: 26.043 seconds, Fetched: 100 row(s)
（2）通過子查詢後，再關聯表
hive (default)> select b.id from bigtable b
join (select id from ori where id <= 10 ) o on b.id = o.id;
Time taken: 30.058 seconds, Fetched: 100 row(s)
Time taken: 29.106 seconds, Fetched: 100 row(s)
2.3.8 動態分區調整
關係型數據庫中，對分區表Insert數據時候，數據庫自動會根據分區字段的值，將數據插入到相應的分區中，Hive中也提供了類似的機制，即動態分區(Dynamic Partition)，只不過，使用Hive的動態分區，需要進行相應的配置。
1）開啟動態分區參數設置
（1）開啟動態分區功能（默認true，開啟）
hive.exec.dynamic.partition=true
（2）設置為非嚴格模式（動態分區的模式，默認strict，表示必須指定至少一個分區為靜態分區，nonstrict模式表示允許所有的分區字段都可以使用動態分區。）
hive.exec.dynamic.partition.mode=nonstrict
（3）在所有執行MR的節點上，最大一共可以創建多少個動態分區。
hive.exec.max.dynamic.partitions=1000
（4）在每個執行MR的節點上，最大可以創建多少個動態分區。該參數需要根據實際的數據來設定。比如：源數據中包含了一年的數據，即day字段有365個值，那麼該參數就需要設置成大於365，如果使用默認值100，則會報錯。
hive.exec.max.dynamic.partitions.pernode=100
（5）整個MR Job中，最大可以創建多少個HDFS文件。
hive.exec.max.created.files=100000
（6）當有空分區生成時，是否拋出異常。一般不需要設置。
hive.error.on.empty.partition=false
2）案例實操
需求：將ori中的數據按照時間(如：20111230000008)，插入到目標表ori_partitioned_target的相應分區中。
（1）創建分區表
create table ori_partitioned(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string)
partitioned by (p_time bigint)
row format delimited fields terminated by '\\t';
（2）加載數據到分區表中
hive (default)> load data local inpath '/opt/module/datas/ds1' into table ori_partitioned partition(p_time='20111230000010') ;
hive (default)> load data local inpath '/opt/module/datas/ds2' into table ori_partitioned partition(p_time='20111230000011') ;
（3）創建目標分區表
create table ori_partitioned_target(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) PARTITIONED BY (p_time STRING) row format delimited fields terminated by '\\t';
（4）設置動態分區
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.exec.max.dynamic.partitions = 1000;
set hive.exec.max.dynamic.partitions.pernode = 100;
set hive.exec.max.created.files = 100000;
set hive.error.on.empty.partition = false;
hive (default)> insert overwrite table ori_partitioned_target partition (p_time)
select id, time, uid, keyword, url_rank, click_num, click_url, p_time from ori_partitioned;
（5）查看目標分區表的分區情況
hive (default)> show partitions ori_partitioned_target;
2.4 數據傾斜
2.4.1 Map數
1）通常情況下，作業會通過input的目錄產生一個或者多個map任務。
主要的決定因素有：input的文件總個數，input的文件大小，集群設置的文件塊大小。
2）是不是map數越多越好？
答案是否定的。如果一個任務有很多小文件（遠遠小於塊大小128m），則每個小文件也會被當做一個塊，用一個map任務來完成，而一個map任務啟動和初始化的時間遠遠大於邏輯處理的時間，就會造成很大的資源浪費。而且，同時可執行的map數是受限的。
3）是不是保證每個map處理接近128m的文件塊，就高枕無憂了？
答案也是不一定。比如有一個127m的文件，正常會用一個map去完成，但這個文件只有一個或者兩個小字段，卻有幾千萬的記錄，如果map處理的邏輯比較複雜，用一個map任務去做，肯定也比較耗時。
針對上面的問題2和3，我們需要採取兩種方式來解決：即減少map數和增加map數；
2.4.2 小文件進行合併
在map執行前合併小文件，減少map數：CombineHiveInputFormat具有對小文件進行合併的功能（系統默認的格式）。HiveInputFormat沒有對小文件合併功能。
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2.4.3 複雜文件增加Map數
當input的文件都很大，任務邏輯複雜，map執行非常慢的時候，可以考慮增加Map數，來使得每個map處理的數據量減少，從而提高任務的執行效率。
增加map的方法為：根據computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，調整maxSize最大值。讓maxSize最大值低於blocksize就可以增加map的個數。
案例實操：
（1）執行查詢
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
（2）設置最大切片值為100個字節
hive (default)> set mapreduce.input.fileinputformat.split.maxsize=100;
hive (default)> select count(*) from emp;
Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1
2.4.4 Reduce數
1）調整reduce個數方法一
（1）每個Reduce處理的數據量默認是256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每個任務最大的reduce數，默認為1009
hive.exec.reducers.max=1009
（3）計算reducer數的公式
N=min(參數2，總輸入數據量/參數1)
2）調整reduce個數方法二
在hadoop的mapred-default.xml文件中修改
設置每個job的Reduce個數
set mapreduce.job.reduces = 15;
3）reduce個數並不是越多越好
1）過多的啟動和初始化reduce也會消耗時間和資源；
2）另外，有多少個reduce，就會有多少個輸出文件，如果生成了很多個小文件，那麼如果這些小文件作為下一個任務的輸入，則也會出現小文件過多的問題；
在設置reduce個數的時候也需要考慮這兩個原則：處理大數據量利用合適的reduce數；使單個reduce任務處理數據量大小要合適；
2.5 並行執行
Hive會將一個查詢轉化成一個或者多個階段。這樣的階段可以是MapReduce階段、抽樣階段、合併階段、limit階段。或者Hive執行過程中可能需要的其他階段。默認情況下，Hive一次只會執行一個階段。不過，某個特定的job可能包含眾多的階段，而這些階段可能並非完全互相依賴的，也就是說有些階段是可以並行執行的，這樣可能使得整個job的執行時間縮短。不過，如果有更多的階段可以並行執行，那麼job可能就越快完成。
通過設置參數hive.exec.parallel值為true，就可以開啟併發執行。不過，在共享集群中，需要注意下，如果job中並行階段增多，那麼集群利用率就會增加。
set hive.exec.parallel=true; //打開任務並行執行
set hive.exec.parallel.thread.number=16; //同一個sql允許最大並行度，默認為8。
當然，得是在系統資源比較空閒的時候才有優勢，否則，沒資源，並行也起不來。
2.6 嚴格模式
Hive提供了一個嚴格模式，可以防止用戶執行那些可能意向不到的不好的影響的查詢。
通過設置屬性hive.mapred.mode值為默認是非嚴格模式nonstrict 。開啟嚴格模式需要修改hive.mapred.mode值為strict，開啟嚴格模式可以禁止3種類型的查詢。
hive.mapred.mode
strict
The mode in which the Hive operations are being performed.
In strict mode, some risky queries are not allowed to run. They include:
Cartesian Product.
No partition being picked up for a query.
Comparing bigints and strings.
Comparing bigints and doubles.
Orderby without limit.
1）對於分區表，除非where語句中含有分區字段過濾條件來限制範圍，否則不允許執行。換句話說，就是用戶不允許掃描所有分區。進行這個限制的原因是，通常分區表都擁有非常大的數據集，而且數據增加迅速。沒有進行分區限制的查詢可能會消耗令人不可接受的巨大資源來處理這個表。
2）對於使用了order by語句的查詢，要求必須使用limit語句。因為order by為了執行排序過程會將所有的結果數據分發到同一個Reducer中進行處理，強制要求用戶增加這個LIMIT語句可以防止Reducer額外執行很長一段時間。
3）限制笛卡爾積的查詢。對關係型數據庫非常瞭解的用戶可能期望在執行JOIN查詢的時候不使用ON語句而是使用where語句，這樣關係數據庫的執行優化器就可以高效地將WHERE語句轉化成那個ON語句。不幸的是，Hive並不會執行這種優化，因此，如果表足夠大，那麼這個查詢就會出現不可控的情況。
2.7 JVM重用
JVM重用是Hadoop調優參數的內容，其對Hive的性能具有非常大的影響，特別是對於很難避免小文件的場景或task特別多的場景，這類場景大多數執行時間都很短。
Hadoop的默認配置通常是使用派生JVM來執行map和Reduce任務的。這時JVM的啟動過程可能會造成相當大的開銷，尤其是執行的job包含有成百上千task任務的情況。JVM重用可以使得JVM實例在同一個job中重新使用N次。N的值可以在Hadoop的mapred-site.xml文件中進行配置。通常在10-20之間，具體多少需要根據具體業務場景測試得出。
mapreduce.job.jvm.numtasks
10
How many tasks to run per jvm. If set to -1, there is
no limit.
這個功能的缺點是，開啟JVM重用將一直佔用使用到的task插槽，以便進行重用，直到任務完成後才能釋放。如果某個“不平衡的”job中有某幾個reduce task執行的時間要比其他Reduce task消耗的時間多的多的話，那麼保留的插槽就會一直空閒著卻無法被其他的job使用，直到所有的task都結束了才會釋放。
2.8 推測執行
在分佈式集群環境下，因為程序Bug（包括Hadoop本身的bug），負載不均衡或者資源分佈不均等原因，會造成同一個作業的多個任務之間運行速度不一致，有些任務的運行速度可能明顯慢於其他任務（比如一個作業的某個任務進度只有50%，而其他所有任務已經運行完畢），則這些任務會拖慢作業的整體執行進度。為了避免這種情況發生，Hadoop採用了推測執行（Speculative Execution）機制，它根據一定的法則推測出“拖後腿”的任務，併為這樣的任務啟動一個備份任務，讓該任務與原始任務同時處理同一份數據，並最終選用最先成功運行完成任務的計算結果作為最終結果。
設置開啟推測執行參數：Hadoop的mapred-site.xml文件中進行配置
mapreduce.map.speculative
true
If true, then multiple instances of some map tasks
may be executed in parallel.
mapreduce.reduce.speculative
true
If true, then multiple instances of some reduce tasks
may be executed in parallel.
不過hive本身也提供了配置項來控制reduce-side的推測執行：
hive.mapred.reduce.tasks.speculative.execution
true
Whether speculative execution for reducers should be turned on.
關於調優這些推測執行變量，還很難給一個具體的建議。如果用戶對於運行時的偏差非常敏感的話，那麼可以將這些功能關閉掉。如果用戶因為輸入數據量很大而需要執行長時間的map或者Reduce task的話，那麼啟動推測執行造成的浪費是非常巨大大。
2.9 執行計劃（Explain）
1）基本語法
EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query
2）案例實操
（1）查看下面這條語句的執行計劃
hive (default)> explain select * from emp;
hive (default)> explain select deptno, avg(sal) avg_sal from emp group by deptno;
（2）查看詳細執行計劃
hive (default)> explain extended select * from emp;
hive (default)> explain extended select deptno, avg(sal) avg_sal from emp group by deptno;
大數據開發高薪必備全套資源【免費獲取】
Oracle高級技術總監多年精心創作一套完整課程體系【大數據、人工智能開發必看】，全面助力大數據開發零基礎+入門+提升+項目=高薪！
「大數據零基礎入門」
「大數據架構系統組件」
「大數據全套系統工具安裝包」
Java必備工具
大數據必備工具
「大數據行業必備知資訊」
「大數據精品實戰案例」
「大數據就業指導方案」
最後說一下的，也就是以上教程的獲取方式！
領取方法：
還是那個萬年不變的老規矩
1.評論文章，沒字數限制，一個字都行！
3.私信小編：“大數據開發教程”即可！
謝謝大家，祝大家學習愉快！（拿到教程後一定要好好學習，多練習哦!)
閱讀更多 微笑數據工程師 的文章
