Pig Latin 二部曲

ETL (Extract-Transform-Load) 的縮寫，即數據抽取、轉換、裝載的過程)作為BI/DW（Business Intelligence）的核心和靈魂，能夠按照統一的規則集成並提高數據的價值，是負責完成數據從數據源向目標數據倉庫轉化的過程，是實施數據倉庫的重要步驟。如果說數據倉庫的模型設計是一座大廈的設計藍圖，數據是磚瓦的話，那麼ETL就是建設大廈的過程。在整個項目中最難部分是用戶需求分析和模型設計，而ETL規則設計和實施則是工作量最大的，約佔整個項目的60%～80%，這是國內外從眾多實踐中得到的普遍共識。

      ETL是數據抽取（Extract）、轉換（Transform）、清洗（Cleansing）、裝載（Load）的過程。是構建數據倉庫的重要一環，用戶從數據源抽取出所需的數據，經過數據清洗,最終按照預先定義好的數據倉庫模型，將數據載入到數據倉庫中去。

　　信息是現代企業的重要資源，是企業運用科學管理、決策分析的基礎。目前，大多數企業花費大量的資金和時間來構建聯機事務處理OLTP的業務系統和辦公自動化系統，用來記錄事務處理的各種相關數據。據統計，數據量每2～3年時間就會成倍增長，這些數據蘊含著巨大的商業價值，而企業所關注的通常只佔在總數據量的2％～4％左右。因此，企業仍然沒有最大化地利用已存在的數據資源，以致於浪費了更多的時間和資金，也失去制定關鍵商業決策的最佳契機。於是，企業如何通過各種技術手段，並把數據轉換為信息、知識，已經成了提高其核心競爭力的主要瓶頸。而ETL則是主要的一個技術手段。如何正確選擇ETL工具？如何正確應用ETL？

　　目前，ETL工具的典型代表有:Informatica、DataStage、owb、微軟DTS……

　　數據集成：快速實現ETL

　　ETL的質量問題具體表現為正確性、完整性、一致性、完備性、有效性、時效性和可獲取性等幾個特性。而影響質量問題的原因有很多，由系統集成和歷史數據造成的原因主要包括:業務系統不同時期系統之間數據模型不一致；業務系統不同時期業務過程有變化；舊系統模塊在運營、人事、財務、辦公系統等相關信息的不一致；遺留系統和新業務、管理系統數據集成不完備帶來的不一致性。

　　實現ETL，首先要實現ETL轉換的過程。它可以集中地體現為以下幾個方面：

　　空值處理 可捕獲欄位空值，進行載入或替換為其他含義數據，並可根據欄位空值實現分流載入到不同目標庫。

　　規範化數據格式 可實現欄位格式約束定義，對於數據源中時間、數值、字元等數據，可自定義載入格式。

　　拆分數據 依據業務需求對欄位可進行分解。例，主叫號 861084613409，可進行區域碼和電話號碼分解。

　　驗證數據正確性 可利用Lookup及拆分功能進行數據驗證。例如，主叫號861084613409，進行區域碼和電話號碼分解后，可利用Lookup返回主叫網關或交換機記載的主叫地區，進行數據驗證。

　　數據替換 對於因業務因素，可實現無效數據、缺失數據的替換。

　　Lookup 查獲丟失數據 Lookup實現子查詢，並返回用其他手段獲取的缺失欄位，保證欄位完整性。

　　建立ETL過程的主外鍵約束 對無依賴性的非法數據，可替換或導出到錯誤數據文件中，保證主鍵惟一記錄的載入。

　　為了能更好地實現ETL，筆者建議用戶在實施ETL過程中應注意以下幾點：

　　第一，如果條件允許，可利用數據中轉區對運營數據進行預處理，保證集成與載入的高效性；

　　第二，如果ETL的過程是主動「拉取」，而不是從內部「推送」，其可控性將大為增強；

　　第三，ETL之前應制定流程化的配置管理和標準協議；

　　第四，關鍵數據標準至關重要。目前，ETL面臨的最大挑戰是當接收數據時其各源數據的異構性和低質量。以電信為例，A系統按照統計代碼管理數據，B系統按照賬目數字管理，C系統按照語音ID管理。當ETL需要對這三個系統進行集成以獲得對客戶的全面視角時，這一過程需要複雜的匹配規則、名稱/地址正常化與標準化。而ETL在處理過程中會定義一個關鍵數據標準，並在此基礎上，制定相應的數據介面標準。

　　ETL過程在很大程度上受企業對源數據的理解程度的影響，也就是說從業務的角度看數據集成非常重要。一個優秀的ETL設計應該具有如下功能：

　　管理簡單；採用元數據方法，集中進行管理；介面、數據格式、傳輸有嚴格的規範；盡量不在外部數據源安裝軟體；數據抽取系統流程自動化，並有自動調度功能；抽取的數據及時、準確、完整；可以提供同各種數據系統的介面，系統適應性強；提供軟體框架系統，系統功能改變時，應用程序很少改變便可適應變化；可擴展性強。

　　數據模型：標準定義數據

　　合理的業務模型設計對ETL至關重要。數據倉庫是企業惟一、真實、可靠的綜合數據平台。數據倉庫的設計建模一般都依照三範式、星型模型、雪花模型，無論哪種設計思想，都應該最大化地涵蓋關鍵業務數據，把運營環境中雜亂無序的數據結構統一成為合理的、關聯的、分析型的新結構，而ETL則會依照模型的定義去提取數據源，進行轉換、清洗，並最終載入到目標數據倉庫中。

　　模型的重要之處在於對數據做標準化定義，實現統一的編碼、統一的分類和組織。標準化定義的內容包括：標準代碼統一、業務術語統一。ETL依照模型進行初始載入、增量載入、緩慢增長維、慢速變化維、事實表載入等數據集成，並根據業務需求制定相應的載入策略、刷新策略、匯總策略、維護策略。

　　元數據：拓展新型應用

　　對業務數據本身及其運行環境的描述與定義的數據，稱之為元數據（metadata）。元數據是描述數據的數據。從某種意義上說，業務數據主要用於支持業務系統應用的數據，而元數據則是企業信息門戶、客戶關係管理、數據倉庫、決策支持和B2B等新型應用所不可或缺的內容。

　　元數據的典型表現為對象的描述，即對資料庫、表、列、列屬性（類型、格式、約束等）以及主鍵/外部鍵關聯等等的描述。特別是現行應用的異構性與分佈性越來越普遍的情況下，統一的元數據就愈發重要了。「信息孤島」曾經是很多企業對其應用現狀的一種抱怨和概括，而合理的元數據則會有效地描繪出信息的關聯性。

　　而元數據對於ETL的集中表現為：定義數據源的位置及數據源的屬性、確定從源數據到目標數據的對應規則、確定相關的業務邏輯、在數據實際載入前的其他必要的準備工作，等等，它一般貫穿整個數據倉庫項目，而ETL的所有過程必須最大化地參照元數據，這樣才能快速實現ETL。

　　ETL體系結構

　　下圖為ETL體系結構，它體現了主流ETL產品框架的主要組成部分。ETL是指從源系統中提取數據，轉換數據為一個標準的格式，並載入數據到目標數據存儲區，通常是數據倉庫。

　　ETL體系結構圖

　　Design manager 提供一個圖形化的映射環境，讓開發者定義從源到目標的映射關係、轉換、處理流程。設計過程的各對象的邏輯定義存儲在一個元數據資料庫中。

　　Meta data management 提供一個關於ETL設計和運行處理等相關定義、管理信息的元數據資料庫。ETL引擎在運行時和其它應用都可參考此資料庫中的元數據。

　　Extract 通過介面提取源數據，例如ODBC、專用資料庫介面和平面文件提取器，並參照元數據來決定數據的提取及其提取方式。

　　Transform 開發者將提取的數據，按照業務需要轉換為目標數據結構，並實現匯總。

　　Load 載入經轉換和匯總的數據到目標數據倉庫中，可實現SQL或批量載入。

　　Transport services 利用網路協議或文件協議，在源和目標系統之間移動數據，利用內存在ETL處理的各組件中移動數據。

　　Administration and operation 可讓管理員基於事件和時間進行調度、運行、監測ETL作業、管理錯誤信息、從失敗中恢復和調節從源系統的輸出。

Pig Latin 資料抽取 (一)

重新定義 Schema

grunt> movies = LOAD 'movies_data.csv' USING PigStorage(',') as (id:int, name:chararray, year:int, rating:double, duration:int);

grunt> movies_50_65 = FILTER movies by year>1950 and year<1965;

grunt> movies_50_65_2 = FILTER movies_50_65 by duration>7200;

grunt> dump movies_50_65_2;

:::

ine.util.MapRedUtil - Total input paths to process : 1

(591,White Christmas,1954,4.3,7201)

(697,The Inn of the Sixth Happiness,1958,4.0,9470)

(704,The Man Who Shot Liberty Valance,1962,4.0,7399)

(714,Imitation of Life,1959,4.0,7472)

(719,War and Peace,1956,3.6,12500)

(1207,Zulu,1964,3.8,8298)

(2126,Boccaccio '70,1962,2.9,12235)

(2331,McLintock!,1963,4.1,7637)

(2437,Daddy Long Legs,1955,3.8,7593)

(3094,Becket,1964,3.9,8898)

(7126,Escape by Night,1960,3.0,8022)

Pig Latin 資料轉換 - 改變欄位結構與資料轉換

grunt> movies = LOAD 'movies_data.csv' USING PigStorage(',') as (id:int, name:chararray, year:int, rating:double, duration:int);

grunt> movies_50_65 = FILTER movies by year>1950 and year<1965;

grunt> movies_50_65_2 = FILTER movies_50_65 by duration>7200;

grunt> movie_duration = FOREACH movies_50_65_2 GENERATE name, (double)(duration/60);

grunt> mymovies = FILTER movie_duration by $1 is not null;

grunt> dump mymovies;

:::

ne.util.MapRedUtil - Total input paths to process : 1

(White Christmas,120.0)

(The Inn of the Sixth Happiness,157.0)

(The Man Who Shot Liberty Valance,123.0)

(Imitation of Life,124.0)

(War and Peace,208.0)

(Zulu,138.0)

(Boccaccio '70,203.0)

(McLintock!,127.0)

(Daddy Long Legs,126.0)

(Becket,148.0)

(Escape by Night,133.0)

Pig Latin 資料轉換 - 總計分析

grunt> desc_movies_by_year = ORDER movies BY year ASC;

grunt> grouped_by_year = group desc_movies_by_year by year;

grunt> count_by_year = FOREACH grouped_by_year GENERATE group, COUNT(desc_movies_by_year);

grunt> dump count_by_year;

:::

(2007,2892)

(2008,3358)

(2009,4451)

(2010,5107)

(2011,5511)

(2012,4339)

(2013,981)

(2014,1)

列出年電影產量最多的十年
grunt> order_2 = ORDER count_by_year by $1 desc;
grunt> top_10_year = LIMIT order_2 10;

grunt> dump top_10_year;

:::

2017-12-29 09:00:05,818 [main] INFO  org.apache.pig.backend.hadoop.executionengine.util.MapRedUtil - Total input paths to process : 1
(2011,5511)
(2010,5107)
(2009,4451)
(2012,4339)
(2008,3358)
(2007,2892)
(2006,2416)
(2005,1937)
(2003,1399)
(2004,1381)