傳統數倉從 Teradata 遷移到 Hadoop平台實踐
引言
數據倉庫是面向數據分析應用的資料庫。在國內的電信、金融、航空、郵政等多行業中應用，數據倉庫的應用經過近十年的發展，已經具有相當的規模。其中，經營分析系統是以數據倉庫技術為基礎，綜合運用聯機分析處理、統計報表和KPI等多種技術手段，為企業經營管理、市場營銷、業務運營提供分析決策支持的系統。
Teradata 數據倉庫是一種成熟穩定的適合於經營分析系統的架構。然而大數據的到來，Teradata的優勢慢慢變為劣勢，數據處理、分析不僅僅局限於傳統的經營分析系統，而需要更多的結構化和非結構化數據整合、數據分析、數據挖掘、數據探索方面的處理。Hadoop 平台正是一種探索新的適合於實時、批量數據處理，語音識別、文本挖掘的架構。本篇文章將以實際案例講解傳統數據倉庫向Hadoop遷移的過程。
01
第一章 數據倉庫現狀
目前要遷移的經營分析系統是一個基於Teradata平台10年多期建設，接入6個業務系統，日接入數據量20G，處理作業近5000個，為2萬人提供系統訪問，同時1000人在線查詢的數據分析系統。
經過10年的使用，Teradata 數據倉庫逐漸暴露了很多的不足，(1)從硬體兼容性看。Teradata 是內部封閉的系統環境，不能兼容其他Hadoop開放式平台組件。(2)從技術實現看。Teradata 採用封閉的 Bynet 技術，限制了 MPP 節點之間的通訊帶寬。(3) 橫向擴展能力弱。Teradata 推出的新的硬體平台與以往的平台互不兼容。很多客戶甚至被逼迫採購舊型號的設備以滿足整體系統的兼容性。(4) 維護成本高昂。Teradata每台設備的年維護費用都在百萬以上。
02
第二章 遷移方案
系統總體架構中，原系統包含一個主數據倉庫和兩個異構數據集市(一個TD集市、一個Oracle集市)，本遷移方案僅將原系統的數據倉庫和數據集市，使用Hadoop平台進行替換，其他架構基本保持不變。平台通過Hadoop資料庫遷移原系統歷史數據、通過增量介面方式接收增量數據，並對數據進行ETL加工處理，為數據集市和報表提供數據, 同時負責給下游系統發送數據。門戶應用和報表通過訪問Hadoop數據倉庫、數據集市獲取數據。
圖 1. 原系統邏輯架構示意圖
圖 2. 遷移系統邏輯架構示意圖
2.1 遷移方案選擇
從 Teradata 向 Hadoop 數據倉庫遷移，首先需要確定遷移方案。針對具體情況，可以從下述三種方案中選擇其一。
1. 自上而下：從數據集市開始遷移，屬於數據集市的表遷移到Hadoop中，然後逐層向下，擴展至整個數據倉庫。
優點：通常符合項目的立項目標和要求；確定屬於某個數據集市的表可以完整地遷移到Hadoop 中，空間縮減是逐步的、持續的；在整個過程中可以梳理各個數據集市的元數據。
缺點：這個過程對 Teradata 數據量的減少較為緩慢；在遷移改造的過程中需要考慮現有 Teradata數據倉庫擴容壓力以及維護成本的問題。
2. 自下而上：從數據的採集，ETL 層面開始遷移，隨後把整個數據倉庫層面改造到 Hadoop 系統中，最後再遷移數據集市。
優點：首先解決 ETL 遷移的問題，效率較高。
缺點：並不能最大限度地縮減 Teradata 系統的空間，只有在整個系統都遷移完成的時候，才能夠一下子縮減 Teradata 的空間，所以整個遷移過程中壓力較大。對於系統上線和性能壓力非常大。
3. 自部分到整體：把一部分核心業務系統數據的全部流程（從ETL 到數據集市）遷移到 Hadoop 中，然後逐步擴大規模，最後到整個數據倉庫。
優點：這個方案涉及了兩套資料庫表和 ETL 系統的維護，而且很多分析的應用需要訪問全範圍的數據。這個方案在空間縮減方面比較直接。
缺點：實施風險很大；在邏輯上的交叉訪問不容易實現。
03
第三章 遷移路線和步驟
3.1 Teradata遷移Hadoop路線圖
圖 3. 遷移路線圖
如圖 3 所示，從邏輯上整體分為八個步驟:
1、了解現狀
(一)對原系統現狀調研，了解系統架構，系統使用人數，系統訪問頻次，系統數據存儲量，增量數據量，系統每日載入作業數量，系統報表數量，原系統維護人員情況，資料及文檔完備等情況。
(二)資料收集主要從文檔資料(終驗文檔、介面規範、ER圖、操作手冊等)；遷移內容清單(介面清單、ETL作業清單、伺服器清單、倉庫清單(庫、用戶、表、視圖等)、軟硬體清單)；流程梳理(系統數據流程、日常運維流程)，最新代碼(ETL作業及知識庫、DDL)；其他內容(增量介面文件、資料庫訪問用戶、門戶訪問用戶)等幾個方面收集。
(三)搭建系統模擬環境，包括ORACLE資料庫，Hadoop資料庫,Teradata資料庫，Weblogic門戶應用，Cognos報表應用，Automation調度工具等。
(四)範圍梳理，根據調研和收集資料內容進行分析和整理，形成原系統遷移範圍說明書《系統遷移需求規格說明書》，包括門戶功能，報表應用，ETL作業，介面數量，DDL範圍等。
(五)技術驗證，本次遷移後將使用Hadoop組件替代，在大規模遷移之前，需要將一些技術問題進行驗證，包括Cognos8.4部署、Cognos8.4連接Hadoop產品，Automation連接Hadoop產品，TD和Hadoop語法差異分析，將一個介面數據文件載入、加工處理生成應用數據、提供給門戶和報表報表展示的典型案例驗證等。
(六)遷移工作，重點遷移了4872個ETL作業，915個報表，2706個DDL修改，20T數據遷移。遷移原系統報表、門戶功能。
(七)數據核對，915個報表數據核對，4872個ETL作業數據核對。單元測試和集成測試過程中進行多維度，多組合條件的數據核對測試。
(八)性能優化，在開發階段針對資料庫級優化、DDL表級優化，在上線後對性較慢SQL進行優化。
3.2 重點遷移步驟
圖 4. 五階段遷移步驟
1、資料庫對象遷移
資料庫對象的遷移，主要包括表、視圖、索引、函數、系統日期表Sys_calendar.Calendar等的遷移工作。對比Teradata和Hadoop語法的區別將DDL改成Hadoop上能夠執行的語句。
2、 ETL 工具和 Perl 腳本遷移
在 Teradata 中，ETL 過程是 Perl 腳本中嵌入 Teradata SQL 的程序處理模式。調度工具使用 Teradata Automation 伺服器啟動任務（Job）來調度 Perl 腳本。就工具的遷移而言，可以根據具體需要，選擇下面的方式。
方式一，繼續沿用 Teradata Automation 工具，工作重點是遷移 Per l 腳本調用Hadoop Connection。修改SQL的語法，盡量保持業務邏輯不變。
方式二，放棄 Teradata Automation 工具，使用 Trinity 或 Informatic，工作重點是編寫新的 ETL 腳本來實現業務邏輯，通過ETL工具實現原本ETL SQL腳本中的業務邏輯，工作量巨大。
3、 數據遷移(如圖 5 所示)
首先使用 FastExport 工具將Teradata 數據倉庫中的歷史數據導出為文本，然後將數據文件put 到 HDFS 外表中，再通過insert 的方式插入到內表 (orc表)完成TD數據倉庫中的歷史數據遷移。
然後，通過執行修改的ETL腳本，接收業務源系統數據，載入到HDFS外表中，再通過insert 的方式插入到內表 (orc表)來完成業務數據的增量數據接入和處理。增量數據載入方式有全量載入和增量載入，數據存儲形式有拉鏈表、全量表、增量表。
圖 5. 從 Teradata 載入數據到 Hadoop平台中
4. 應用遷移
應用的遷移，其實質是將應用中的 Teradata 的標準擴展 SQL 轉化成 Hive SQL，同時還要處理 Teradata 中的存儲過程、觸發器和用戶自定義函數等。典型的數據應用包括查詢、分析、報表、駕駛艙等。本文不進行詳細描述。
5. 測試和性能優化
設計測試用例，對數據驗證，完成對應用的功能測試。最後在產品環境中進行性能測試，並和原系統進行性能對比。如果需要，則對數據倉庫系統進行性能優化。
值得強調的是，上述 5 個階段只是邏輯上的步驟。實際上數據倉庫的遷移是由各個任務組成的，相互可以並行、依賴項目的整體進度要求。例如，我們經常在實踐中，採取關鍵核心內容，進行上下推進的策略，來加快實施。另外第三階段的數據遷移，通常分初次裝載和日常連續裝載兩個部分。日常連續裝載是以後每天要做的事情，初次裝載情況各異，面對全量數據的接入也可以不做。
04
第四章 資料庫對象遷移
不同於通常的 DDL 腳本轉換，我們對資料庫對象遷移採取了從目標 Teradata 數據倉庫直接獲取的方式，以滿足和生產系統狀態完全同步以及遷移便捷性的需要。另外，我們遷移的是一個10年建設的數據倉庫，客戶的數據倉庫中總是會有一些由於歷史原因不再使用的資料庫對象例如表、索引等，或者會有一些當前階段暫不需要遷移的資料庫對象，其中資料庫對象有10000多個，資料庫有60多個，所以我們通過排查表名在ETL腳本以及應用中的出現作為主要遷移範圍，排查後的對象作為需要獲取的資料庫對象列表，來做有針對性的遷移。
當然，在遷移資料庫對象的同時，也考慮同時遷移數據，同步進行。但實際項目中，我們發現很難做到。因為數據倉庫的數據量通常都非常大，如果遷數據需要很多時間，特別是一旦出現問題，會影響後續工作的開展。所以通常都是先遷移資料庫對象，這時候一些上層應用就可以進行測試了，隨後再遷移大量數據。
就資料庫對象遷移的技術細節來看，Teradata 的 SQL 語法標準和 Hadoop hive 大部分是一致的。但也有少數部分有所區別，例如在 Teradata 的建表語句中的 TITLE 欄位描述 , 需要轉換為 Hadoop 的COMMENT 描述；Teradata 數據表的分區、數據散列，需要用 Hadoop 的 表分區、分桶來做相應實現。
遷移工作最大的痛點就是數據在Hadoop的分布以及後續的應用訪問性能，首先參考原有Teradata中的分區和PI創建的欄位進行參考，作為Hadoop的分區和分桶欄位。其次參考TD表中的記錄條數和數據大小，單個桶的文件大小為100M，記錄條數200萬條為參考基準作為劃分原則。當然，Teradata資料庫在數據存儲上也有一定的壓縮比例，所以上述的原則僅做參考，在後續的數據遷移、ETL開發、應用使用過程中，我們針對具體的場景和不合理的分區分桶做了進一步的優化。
Teradata到Hadoop的DDL語法轉換
05
第五章 ETL工具和 Perl 腳本遷移
在 Teradata 數據倉庫中，其數據倉庫各個層次之間的 ETL 過程是通過 Perl 腳本來進行的，如下圖 6 所示。Perl 腳本主要是用於在數據倉庫內部各層次之間的數據抽取、清理和轉換。同時，我們使用 Teradata 自動化調度工具驅動 Perl 腳本進行工作。例如 Staging 區到中間層的模型層，由模型層層到上層的匯總層，其都是由 Perl 腳本在自動化調度工具（Automation Server）的控制下來完成各層次之間的數據抽取和載入。
ETL遷移過程中包含兩部分內容：
遷移一：數據掃描、載入、數據校驗、業務邏輯處理的腳本處理，這部分主要是Perl腳本的遷移。
遷移二：由 Perl 腳本中包含的主要為對資料庫的 INSERT，UPDATE 和DELETE 操作，所以對 Perl 腳本的遷移稱為 DML（Database Manipulation Language）遷移。
圖 6. 典型數據倉庫中的ETL 過程
5.1 ETL數據載入遷移
數據掃描載入的目的不同於 DML 腳本，即在上文提到的 Perl 腳本遷移。與 DML 用在處理數據倉庫內部的轉換和載入不同，數據掃描和載入是用於將外部數據源中的數據，如將業務資料庫中的數據（Teradata，Oracle 中的數據）載入到數據倉庫的表中，即在 ETL 過程中我們提到的數據抽取過程。如圖 6 中由 OLTP 資料庫將數據按T+1的時間周期根據預定義的介面格式將數據導出到數據文件中。同時，我們使用掃描程序定時掃描由業務資料庫中發送的數據文件，並且掃描程序會將數據發送給調度工具，通過在Automation自動化調度工具配置的每晚、每月或者在指定的時間驅動由數據文件到數據倉庫的數據載入。
每一個 Perl 腳本會去調用的 run_bteq_command()函數，如上圖 7 所示。此函數會被調用用來執行外部的一個SQL 文件。對於此種方式執行的 perl SQL， 我們需要對 run_bteq_command 函數和相關的 SQL 文件進行修改，將 Teradata SQL 轉為 Hadoop 支持的 SQL。
1. 底層資料庫介面進行封裝
圖 7. Perl 腳本調用底層函數方式
經過對原有系統的所有介面載入程序腳本分析，總結出介面載入腳本分為6類即6個模板，分別為數據質量檢查，貼源層表建立，數據導入，機構代碼校驗，記錄合法性校驗，虛擬機構代碼更新。
介面載入數據流程圖如下：
圖8 介面載入數據流程圖
介面載入數據步驟詳解：
1)數據質量檢查
首先在系統任務日誌表中插入開始校驗的記錄,然後開始用C程序校驗。此處主要是根據SQL查詢出來對應從介面參數配置表中查詢出該介面對應的相應的欄位,如下所示：
把對應的數據文件和以上參數傳遞給C校驗程序，校驗成功之後會把校驗成功的狀態信息插入到任務日誌表中。如果校驗失敗會把報錯信息據列印到報錯文件中，接著把該文件中的數據插入到系統校驗結果表中,並把校驗失敗的狀態信息插入到系統任務日誌表，程序進行報錯退出，此介面載入數據及後續流程會終止。
2)創建貼源層目標表
根據TDDL庫中介面建表語句創建貼源層表,此處建立的表為落地介面數據最終導入的表。如有異常，程序進行報錯退出，此介面載入數據及後續流程會終止。
3)數據導入
介面文件導入到Hadoop貼源層目標表中，Teradata在做fastload的時候會對數據進行去重操作，所以在這裡我們也模擬對錶的重複記錄去重，去重原則為保留最小文件及最小行號的記錄。如有異常，程序進行報錯退出，此介面載入數據及後續流程會終止。
4)組織機構代碼合法性校驗
從系統配置表中選擇檢查類型為3(代碼檢查),檢查標識為1(校驗),取值為TB_PRT_CPORG.CpOrgCd(組織機構代碼校驗)對應省份的介面單元，如果存在相應的配置數據則進行組織機構代碼合法性校驗，如果配置表中不存在相應的校驗配置則程序正常退出。
校驗過程如下：
1.1創建臨時表用於存放不規範機構代碼對應的信息，以便於將這些不規範代碼歸為不詳。
1.2將不規範機構代碼對應的信息插入到臨時表
此處實現是通過介面表中導入的數據與TB_PRT_CPORG即組織機構代碼表的CpOrgCd的欄位關聯，把不規範機構代碼對應的信息插入到臨時表。
1.3將臨時表中的不規範代碼更新為對應的省級不詳。
1.4從介面表中刪除無效的組織機構代碼對應的業務數據。
1.5將臨時表不規範代碼的信息回插入到介面表。
1.6檢查出來的不規則的組織機構代碼插入到校驗結果表中做記錄，並在程序退出的時候把不規範的組織機構代碼列印出來。如果沒有不規則的組織機構代碼則列印為空。
1.7介面記錄合法性校驗
此處檢查主要包含數據類型及格式錯誤、數據值域錯誤、主鍵約束錯誤、外鍵約束錯誤、編碼規範性錯誤、業務規則錯誤相關檢查。
從系統配置表中排除檢查項為0:不檢查2:外鍵檢查 4:數據類型檢查，檢查標誌為1的對應省份介面的校驗規則,欄位如下：
目前程序支持5種規則的校驗:
1)聯合主鍵約束檢查 #欄位序號必須是00，且ChekItem必須為1。
目前主鍵檢查只檢查本介面中數據是否唯一，不查與歷史是否重複。
2)非聯合主鍵約束檢查 #欄位序號不是00，且ChekItem必須為1。
3)編碼規範性檢查 #ChekItem必須為3。
4)數據值域檢查 #ChekItem必須為5。
5)非空編碼規範性檢查 #ChekItem必須為9。
根據配置表中的記錄進行相應的檢查，如果有檢查出來的不規範記錄則會把相應的記錄插入到檢查結果表中，程序退出前會檢查結果表相應的記錄是否為空，如果有記錄，則程序退出異常，不執行後續的載入流程，如果沒有的則表示介面記錄符合規則，程序正常退出。
虛擬機構代碼更新:
將介面表中的機構代碼進行UPDATE，轉換其中非葉子節點機構為虛擬機構。
從虛擬機構介面表配置表中取出對應介面表中哪些欄位是機構欄位，用於對機構欄位進行虛擬機構化，查詢欄位如下：
一個介面表可能有多個機構代碼需要虛擬化更新，機構代碼更新語句示例如下：
mergeinto PIMS_SDATA.PEO_SUMDRP_20160502_11A using (select CpOrgCd,ParntCpOrgCd fromPIMS_BVIEW2.TB_PRT_CPORG_VIRTUAL ) B on A.CpOrgCd =B.ParntCpOrgCd when matchedthen update set A.CpOrgCd =B.cporgcd;
5.2 ETL業務邏輯遷移
由於 Teradata 的 SQL 在某些細節上和 Hadoop 的 SQL 應用有細微的區別，因此我們需要對這些有變化的 SQL 進行改寫，在SQL處理過程中總結了三類內容的處理方式（函數轉換、事務處理、多表關聯處理），下面給出了上述內容在 Teradata 和 Hadoop 上的對應關係。
為了保證轉換後 DML 腳本轉換後的可用性和一致性，我們使用如下原則進行轉換：
? 保證原有業務邏輯、演算法等功能的正確運行
? DML腳本和其他程序流程盡少改變
? DML腳本或其他文件盡量少進行修改
? 轉換過程可以模式化
原有 Teradata 系統使用Perl 腳本的 ETL 過程根據不同的應用有以下 2 種典型方式。第一種方式是使用在 ETL 的 Perl 腳本中嵌入SQL 語句的方式來完成各層之間的 ETL 過程。如下圖 9 所示。
圖 9. Perl 腳本中嵌入 SQL 的方式
第二種方式是將調用底層資料庫的介面進行封裝，上層的 ETL 腳本通過調用底層的 Perl 程序來執行 SQL 語句。如下圖 10 所示。
圖 10. Perl 腳本調用底層函數執行 SQL 語句
實際上，無論是採用嵌入式的 SQL 的Perl 腳本，還是在底層進行了函數封裝。對於 Hadoop 的遷移來說，我們本質上還是要將 ETL 的 Perl 腳本中和Teradata 相關的命令和 SQL 語句， 轉換為Hadoop 支持的語句。
5.2.1 函數轉換
表 11.語句對應表
1COALESCE函數：
如果coalesce函數出現在匹配兩值是否相等的條件中時，將coalesce函數改為NVL函數。例如下：
TD語法：
COALESCE(A.SellStmpTime,CAST("1900-01-01" AS DATE )) = COALESCE(B.SellStmpTime,CAST("1900-01-01" AS DATE ))
改寫為TDH語法：
NVL(A.SellStmpTime,CAST("1900-01-01" AS DATE )) = NVL(B.SellStmpTime,CAST("1900-01-01" AS DATE ))
2、 Add_months函數：
a) 對於StatsMt欄位，臨時表中類型為：
Date format "yyyymm"
修改成CHAR(6)
TD語法：
?StatsMt=CAST((ADD_MONTHS(CAST("$StatsMt"AS DATE FORMAT "YYYYMM"),-12)(FORMAT "YYYYMM")) AS date format "yyyymm")
?cast("$StatsMt"as date format "yyyymm")
改寫為TDH語法：
?StatsMt=tdh_todate(add_months(cast("$StatsMt"||"01" asdate),-12),"yyyy-MM-dd","yyyyMM")
?Tdh_todate(cast("$StatsMt"||』01』 as date), "yyyy-MM-dd","yyyyMM")
b) 如果有欄位如year欄位，類型為Date format "yyyy" cast("**" as date format "yyyy")
修改模式同上，只是欄位類型為char(4),拼接』0101』再cast為date型，將"yyyyMM"修改為"yyyy"
c) 如果日期欄位類型為Date format "yyyyMMdd"
只需刪除format "yyyyMMdd"
具體修改方法根據實際情況而定。
d) Char轉換成date：CAST( 『20160101』 AS DATE format "YYYYMMDD") 直接刪除format "YYYYMMDD"
e) Char轉換成decimal：如果目標表該欄位為DECIMAL類型，則需 CAST( AS DECIMAL())轉換類型。
3、EXTRACT()函數
Td語法：
EXTRACT(YEAR FROM CAST("$StatsMt" AS DATE FORMAT"YYYYMM"))
改寫為TDH語法：
EXTRACT(YEAR FROM cast(statsmt||"01" as date))
Td語法：
(select * from TMP_TB_PRT_CUSTINFO qualify row_number() over (partition bycustcd order by syssrc desc) = 1)c
改寫為TDH語法：
(select * from (select
CustCd,
CpOrgCd,
IndustTypCd,
CustTypCd,
CustLevCd,
HisUsed,
syssrc,
CustNm,
row_number() over (partition by custcd order by syssrc desc) as qualify_condition
fromTMP_TB_PRT_CUSTINFO)
where qualify_condition = 1)c
4、子查詢中使用group
需要在SQL外面再嵌套一層
TD語法：
FROM $ETL::BVIEW.TB_PRT_CUSTINFO T0
WHERE (T0.CustCd, T0.CpOrgCd) IN
(SELECT T1.CustCd
,MIN(T1.CpOrgCd)
FROM $ETL::BVIEW.TB_PRT_CUSTINFO T1
WHERE T1.DistCd="$DistCd』
GROUPBY 1
)
改寫為TDH語法：
FROM$ETL::BVIEW.TB_PRT_CUSTINFO T0
WHERE (T0.CustCd, T0.CpOrgCd) IN
(SELECT CustCd,CpOrgCd FROM
(SELECT T1.CustCd
,MIN(T1.CpOrgCd) AS CpOrgCd
FROM $ETL::BVIEW.TB_PRT_CUSTINFO T1
WHERE T1.DistCd="$DistCd』
GROUP BY 1)
)
5.2.2 事務處理
在Teradata中，系統為保證一個交易的完整，可以使用BT (Begin Transaction)和ET (End Transaction)來顯示地定義一個交易。在BT和ET之間的所有SQL是一個交易，只有所有SQL都執行成功後，系統自動進行提交（Commit）處理，才會真正地更新資料庫；執行過程中任何一個SQL語句失敗，都會使整個交易失敗，系統將自動進行恢復(Rollback)處理。並且支持.IF ERRORCODE 0 THEN QUIT 12寫法，上面的sql執行失敗後可以退出，之後的sql將不再執行。
在Tdh中要保證這種機制，需使用事務，即將除ddl語句外的其他sql都放在事務塊中。
修改方法：將建臨時表語句(CREATE TABLE)統一放在腳本前面，之後添加
!setplsqlUseSlash true
settransaction.type=inceptor;
BEGIN
BEGINTRANSACTION;
將其餘sql放在事務塊里，並在每段sql後加
dbms_output.put_line("step1:"||sql%rowcount);
sql完成後添加
COMMIT;
EXCEPTION
WHENOTHERS THEN
rollback;
RAISE_APPLICATION_ERROR(sqlcode(),sqlerrm());
dbms_output.put_line("Exceptioncaught, error code = " || sqlcode() || ", error message = " || sqlerrm());
END;
/
如果最後有刪除臨時表操作，如drop table ..，需放在end;/ 後面。
如果腳本里有幾對BT ET塊，並且這些事務塊里操作沒有依賴關聯關係，可以放在兩個BEGIN END 塊里，具體修改方法視腳本情況而定。
5.2.3 多表關聯處理
? 兩表關聯刪除語法：
TD語法：
DELETE B FROM $.TB_PHL_INPROCINFO A ,tmp_inprocinfo B WHERE A.InnInd =B.InnInd AND A.StampCd = B.StampCd … ;
改寫為TDH語法：
DELETE FROM tmp_inprocinfo B WHERE EXISTS(
SELECT 1FROM ETL::TARGETDB.TB_PHL_INPROCINFO A WHERE A.InnInd = B.InnInd
AND A.StampCd = B.StampCd … );
? 多表關聯更新語法：
TD語法：
updatePIMS_SDATA.PEO_PRTCIN_20160730_11 A set A.CpOrgCd = (select B.CpOrgCd from
PIMS_BVIEW2.TB_PRT_CPORG_VIRTUALB where A.CpOrgCd=B.ParntCpOrgCd)
whereexists (select 1 from PIMS_BVIEW2.TB_PRT_CPORG_VIRTUAL B
whereA.CpOrgCd=B.ParntCpOrgCd);
改寫為TDH語法：
mergeinto PIMS_SDATA.PEO_PRTCIN_20160730_11 a
using(selectcporgcd,ParntCpOrgCd from PIMS_BVIEW2.TB_PRT_CPORG_VIRTUAL) b
ona.cporgcd=b.parntcporgcd
whenmatched then update
seta.cporgcd=b.cporgcd;
06
第六章 數據遷移
數據遷移是整個資料庫遷移中非常重要的一環。我們需要保證原 Teradata 中的數據被無損的，快速的裝載到Hadoop數據倉庫。因此我們開發了數據導出和載入的通用程序，來完成 Teradata 資料庫到Hadoop 的數據遷移工作。
數據遷移根據對錶的梳理，根據數據記錄形式分為拉鏈表、碼錶(全量表)、交易流水表(增量表)，根據文件大小將10G以上表算作大表。小表採用全量數據抽取，大表採用業務截止時間的抽取方式。
數據抽取分三個階段，第一階段，完成交易流水表歷史數據抽取(截止某一業務日期)，碼錶當前數據(盡量跟交易流水表保持日期一致)。第二階段補充抽取上次截止時間點到今天的交易流水表部分的歷史數據抽取、碼錶抽取到最新狀態，相當於與TD生產系統拉齊到最新的數據。第三階段接入增量數據到新系統，以滿足新系統的ETL處理及應用訪問。
數據遷移過程中的難點，基於Teradata的遷移重點關注數據內容中是否含有回車換行。另外一個問題是基於數據集市的遷移，數據集市和數據倉庫本身有部分數據是不是完全重合的，幾種情況：（1）Teradata主資料庫和Oracle數據集市DDL一致，但Teradata主資料庫數據保留最近1個月數據、Oracle集市保留全部歷史數據；（2）Teradata主資料庫和Oracle數據集市DDL不一致，數據集市是主資料庫的子集；（3）Teradata主資料庫和Oracle數據集市DDL不一致，數據集市的欄位多於主資料庫，也就是說在數據集市一層有做相應的加工處理；（4）Oracle數據集市根據日期、業務欄位等進行拆表處理(畢竟Oracle計算能力不如Teradata，不拆表是跑不動滴)，上述幾種情況，第一、二中相對好處理，第三、四中需要進行詳細的差異分析，以及配合ETL腳本的修改分別進行處理。
6.1 數據初始化
全量數據的遷移，我們這裡選擇自己開發了導出和導入程序實現全量數據的遷移。劃定數據初始化日期，進行數據初始化；增量數據的載入沿用原系統之前的增量數據載入模式，使用已遷移完畢的ETL作業，從初始化日期開始載入增量數據，進行增量遷移。
數據遷移完畢之後，我們這裡會進行數據核對的後續處理。數據核對一致，則遷移結束；不一致則進行問題的分析定位，解決問題，以提升分析質量。
1、根據需要導出的表DDL不同，動態的拼出需要導出表的SQL語句
2、使用fastexport 執行需要導出表的SQL語句
3、創建Hadoop外表將數據文件put到HDFS上
4、將外表數據導入到正式表，由於外表不支持DML操作，所以這裡我們將外表數據載入到事務表中，使其能夠對報表系統等提供數據服務。
6.2 增量數據載入
增量數據的載入，我們這裡沿用之前原系統增量數據的載入方式。
從ESB接收業務系統傳輸過來的增量文本文件，然後將文本載入到HDFS並創建外表在插入到內表，再將內表數據按照原有的SOU、DWH、FCS、FCT等數據載入層級邏輯進行加工，詳細的載入方法如下：
1、通過ESB將業務系統數據傳輸到ETL集群本地磁碟
2、沿用原系統的作業調度工具，將經過改造的ETL腳本將數據載入到Hadoop
3、在inceptor中創建外表映射到hdfs相應介面的增量數據目錄，再將數據插入到內表
4、根據相應的數據載入邏輯，將增量數據載入後逐層的進行清洗加工，最終處理到應用展現層的表中
07
第七章 數據核對
數據核對從SOU DWH FCT 一層層，一天天數據單元測試。配置好作業依賴觸發關係，進行作業依賴關係聯調測試。數據載入聯調測試。最後進入數據核對。
數據核對，我們分為初始化數據核對和增量數據核對。由於初始化數據量較大，無法一次性將大表落地為文本；因此我們這裡分為記錄條數核對和指標核對兩種核對方案。
7.1 初始化數據核對
記錄條數核對：TD源系統記錄條數、導出文件記錄條數、PUT到HDFS記錄條數、插入ORC表記錄條數
指標核對：所有數值型欄位統一進行SUM計算後進行比對
7.2 增量數據核對
數據比對會嵌入在整個數據載入流程中，每張表在數據載入完畢後，會進行數據比對，並且把比對結果項記入到如下表格中：
? 表名
? 關鍵指標名稱
? TD系統匯總值
? Hadoop平台匯總值
? 是否有差異
? 比對時間
? 處理時間
數據載入完成後，會進行數據比對的後續處理，以解決數據不一致問題，此時的處理，需要人工介入，以便對不一致問題進行分析定位，提出解決辦法，讓數據儘快一致，以提升分析質量。
7.3 常見的數據異常問題
? 1、Teradata與Hadoop語法差異，語法理解、使用錯誤
? 2、Hadoop平台產品本身BUG，計算結果不準確
? 3、小數點四捨五入問題導致數據不一致
? 4、原系統作業依賴關係配置不全
? 5、原系統腳本中業務邏輯計算錯誤
? 6、原系統腳本中歷史數據重新載入邏輯錯誤
? 7、Teradata與Hadoop在中文欄位排序，排序欄位相同時但其他欄位不同
08
第八章 遷移最佳實踐
? 制定遷移計劃之前，首先要做好遷移評估。這包括收集 Teradata 的系統現狀，例如用戶量，數據量，表個數，腳本個數，I/O 吞吐量，是否存在性能瓶頸等。
? 關於數據集市的遷移。把所有存放在 Teradata 、Oracle中的數據集市進行優先順序排序，制定遷移計劃。
? 數據核對，保障Teradata表數據、導出數據問的記錄條數、欄位數量、欄位格式、關鍵指標值是一致的。在數據遷移過程中很容易出現欄位數據含有回車換行符，導致put 到 HDFS的數據比Teradata數據量多，記錄有串列的情況。
? 關於海量數據遷移。例如交易明細數據，就盡量提前來做，不能成為整個遷移的關鍵路徑。
? 要有回退機制。如果遷移過程中或者遷移後，資料庫出現了問題，要有機制能恢復到原有狀態。
? 關於 Teradata Automation 自動化調度工具。通常在 TeradataAutomation 中，有數以萬計的任務被調度。即便我們完成了每個任務的 Perl 腳本和 Teradata SQL 的遷移，在前期方案中，梳理了原有Perl模板的處理邏輯，相應的在 ETL 調度工具中進行驗證和實踐，最終選擇保持原有Automation 調度工具不變的方式進行遷移，制定與工作量相適應的遷移計劃。
? 關於性能調整和優化，需要分階段來做。軟硬體安裝完畢後，做一次平台性能測試，結果和基準數據做比較。遷移完畢後，在對生產環境中的 Hadoop 數據倉庫做第二次性能測試，結果和 Teradata 舊系統的數據做比較，來對比性能情況。
09
第九章 結束語
本文介紹了從 Teradata 遷移到Hadoop 資料庫時所需要進行的 DML 遷移方法、數據載入加工過程遷移、數據初始化以及數據核對的實現。在本文中提到的方法已經在真實的遷移項目中得到驗證，並被證明是行之有效的。Hadoop平台將是建設數據倉庫的又一選擇。
圖片來源於網路
關於IDEADATA
IDEADATA專註於從數據到信息的有效管理與應用，是領先的商業信息服務技術提供商，是數據倉庫及大數據技術和應用的先行實踐者。
TAG:IDEADATA大數據 |