SSIS 團隊工作

 

收到一封詢問 SSIS 團隊工作的 Email,節錄內容如下…

但,大哉問,恐怕是面對面地討論,才能有個共識。最佳解法要靠每天工作磨出來,從團隊文化土壤中長成。

 

 

2. **** ap人員已有使用SSIS 2008經驗, 目前單純做ETL資料傳輸(transfer data from source to target),

但想加強

2-1. SSIS專案管理

2-2. 各專案deployment的分工與分權方法.

2-3. 封裝執行的監控與除錯方法.

(各專案人員可部署/監控/除錯自己的專案與封裝, 但看不到/碰不到別人的專案與封裝)

2-4. 從PM與SA角度, 業界如何設計ETL workflow.

ð 前題自然是要運用到SSIS, 另外想了解的如

ð i.     workflow發動點建議在source or SSIS or target?  Why? 優缺點? (push data or pull data)

ð ii.    Data若需加工, 加工動作建議在source or SSIS or target? Why? 優缺點?

ð Iii.   其他?

 

 

首先,節錄我在 SSIS 2012 一書第一章的內容,說明 ETL 專案的特性與困難

近幾年來,資料倉儲(data warehouse)/線上分析(OLAP)/資料採礦(data mining)等系統開始流行,因為企業已能夠利用各型資料庫系統累積「線上交易(OLTP)」資料,而這些累積的資料大多潛藏著公司的營運指標或是客戶的行為邏輯。為了找出其中的模式,必須將存在各交易子系統的資料;其中符合分析主題之內容,以一致的表現方式集中儲存到資料倉儲。

這時第一個系統性的難題就是:容易上手、有彈性且高效率的資料轉換工具,且提供了資料整合的架構。其主要的困難點如下:

資料面:

l 異質型資料來源:用來集中儲存資料的資料倉儲一般就是個超大型關聯式資料庫,如專屬的 Teradata 或通用的 MS SQL Server…等,而提供資料的各系統卻可能多所不同,設計師要同時了解多種資料存取機制並不容易。我就常常因不同資料平台間的資料格式、溝通方式而困擾不已(如將 Informix、Oracle 等的資料轉到 SQL Server)。

l 商業邏輯運算與彈性:一般在匯入資料至目的端之前,可能會先完成分割、過濾、查閱(將代碼轉成值)、彙總、聯集、排序…等動作。且在流程中輔之以優先順序、條件判斷、交易管理、資料庫物件的維護、錯誤處理、訊息發佈…等。
且隨著資料來源增加,資料目的地用途變異與擴增,設計用來轉換資料的物件要能容易修改與重用。

l 異動資料擷取與資料同步:在兩個系統間,已經載入的資料在下一次整合時,不需要再重做一次。但如何知道哪些來源資料已經變動,又哪些資料曾經更新到目的地?

l 一致化:凡是要轉進資料倉儲的資料應先求得格式與意義之一致,在不同的交易系統中,如公司人事資料可能用「男」、「女」來標示性別,但客戶資料卻可能用「0」、「1」,其他系統的性別欄位還可能使用 True、False 或 Female、Male 等等,隨著當時的系統開發人員喜好而定。但這在資料倉儲內一定要一致。另外,某個系統的地址相關欄位可能是五欄,另一個可能是六欄或四欄,但你的分析可能只需要三欄,因此轉到資料倉儲時需要合併或分割欄位,乃至於原來三欄的資料可能要改成三列,或著反之。更有甚者,跨國企業的系統在全年不斷的運作下,如何克服時區差異,訂出一致的時間區段(例如財務的關帳時間、幣值匯率時間…等)。種種語意、格式、時差、資料的正確性等諸多問題要在轉換時期解決;這不是一件容易的事情。

l 檢驗與校正:未深入了解端對端的資料整合流程中,兩個系統各自資料的意義。而那些資料在轉換過程中錯誤與遺漏,這往往需要過度的人力介入,以分析了解資料誤差之成因。我們需要了解過程中資料記錄數量的變動,與集中的例外處理機制,提供整合的分析報表,以減少人員的介入。

l 資料歷程:針對資料整合所做的人事時地物追蹤,回答如下的問題:哪個資料來源?執行哪個資料整合工作?哪個版本的程式碼產生這樣的結果?若修改了某個欄位結構會影響哪些資料整合工作?如何追蹤中繼資料與資料的變異?

l 容易改變資料流程:資料第一次同步、重複帶入更新資料、錯誤重跑、保存與清空…等。資料與資料間往往有連帶關係,在轉換時也有先後關係,因此形成轉換流程。而局部流程是否需要搭配交易管理,一起成功失敗。哪些流程錯了可以清掉重跑,哪些流程可以忽略錯誤…。諸此種種,最好要能透過設定完成。

系統架構面:

l 週期性處理:一般資料整合的工作是週期性的,如從線上交易系統將資料轉進資料倉儲,絕不是累一次就可以一勞永逸,要讓整個轉換工作可以輕易地周期性、背景批次執行,如此系統才可能長久。

l 安全:按照安全的最小接觸面原理,整合就有可能增加安全漏洞。由於是多種資料彙集,一定會碰上各系統的存取介面、網路流通、彼此系統的帳號登入、機密性資料處理、經手人的授權、背景批次執行的帳號、加解密資料的流程、監控…等問題。

l 效率:彈性與效率基本上是兩個互相衝突的面向,彈性代表著工具本身會做廣泛地測試,讓使用者簡單設定完畢後,它會自己找到正確的處理解決方式,但這需要較複雜的準備工作。而效率則需要以最簡單直觀的方式處理單一事情,這意味著一點設定錯誤就全毀了。
在廣泛地整合時,我們需要彈性,但對於大量資料的載入,又必須有效率。筆者曾碰過需要轉換數天,以兆(tera)為單位的資料量。而在轉換過程中,一般只能乾等,我們稱此為資料處理的空窗期。空窗期越長,資料及時性越糟,延誤下一個資料使用階段,這是大家所不樂見的。
且在全球化的公司中,系統沒有間斷的運行,如何善用各區域系統的不忙碌時間交換資料,如何在各區域資料中心交換最小的資料,但能做最廣泛而即時的分析。資料交換的拓樸為何?採用星狀(各地區與總部交換)、環狀(各區域資料中心一棒接一棒,成環狀交換)、點對點彼此交換,抑或是前述三種拓樸的整合,這些都考驗設計者的經驗,一切尚需實務的驗證與改善。

l 集中的設定:整個架構設計要能易於重用,勢必要設定導向。也就是盡量能透過設定,就改變轉換資料的流程。大小設定要集中,且有獨立的UI介面來完成設定。

l 客製化:雖然現今的資料整合產品提供之功能都非常豐富,但仍有可能碰到特殊的需求,必須靠客製化的程式來滿足。這時,平台所提供的 API、可存取的程式語言,整合開發環境、測試、部署…等議題也變成需要考量的一部分。

l 系統的部署與過版:如何在符合安全架構下,部署與過版開發設計的ELT物件,有彈性又安全地存放中繼資料,還能跨系統地正常執行。

l 程式與資料物件的版控與升級:ETL麻煩的地方在既有程式開發,又有異質型資料。程式、資料結構或封裝物件需要有版本,各資料源與資料目的也須維持一致性。換版時除了要維持程式的正確性外,還需小心中繼資料和實體資料的正確性。

l 管理與監控:就筆者接觸過的大型企業,其資料整合的工作從 50 多個到 1000 多個都有,平均而言有 2~300 個工作,而參與使用該資料轉換技術的工程師規模從 2~3 人到幾十人。因此,如何妥善管理,其面向包含部署、備份、版本控管、災難復原、教育訓練、監控當下多個工作的執行進度與狀況、成功/失敗、效能…等,都需要考慮。不管要監控進度、監控錯誤、監控效能、監控安全…,前提是所有資訊要集中

l 友善的UI前述各項的資訊最好都能統一呈現,而各種中繼資料的設定最好都在一處,因此,需要提供一個ETL的後台管理介面,以方便開發和維運。

上述的問題,各版之SSIS並沒有提供好的解法,因為這如同設計一套系統,它需長久維運,還時時變更,而上述需求彼此可能互相衝突。配合不同的團隊開發文化與技術能力,往往企業要長出自己的ETL架構(Framework),而你在設計這個些架構時,心中要拿捏上述問題的輕重。不用強求一次做好,但求一次次改善。

此外,若你應用ETL要做的是商業智慧系統(Business Intelligence),這又是另一個難題。如何在資料來源(Data Source)、中繼資料庫(ODS Operational Data Store)、星狀的資料超市(Data Mart)和彙整企業資料的資料倉儲[1](Data Warehouse)間搬移資料,這會因為ODS、Data Mart和Data Warehouse本身具備不確定性,而讓ETL變得更複雜。

到此,我們已經談了許多,它們可視為資料整合平台的整體架構需求。另外,現今企業內的資料整合工作大多是單兵作戰,工程師各自處理手邊的資料,很少能深入了解整體的資料處理活動流程,這會導致以下的問題:

問題面相

現有作法

建議

人員與流程

開發者獨自地轉換/整合資料。沒有團隊結構,無專職人員,各自做一點資料流程。分散的資料整合應用程式,導致難以維護的程式碼、重複的程序和資料。

開發資料整合的共通架構、範本和最佳作法,內含共通的資料轉換流程記錄,安全架構等,保證一致性和可管理。

統一的設定與執行

企業 IT 內有太多「用一次」的資料整合流程,複製與修改程式碼後,用一兩次就放著。

設計集中的模型導向(Model Driven)、中介資料導向(Metadata Driven)、設定導向(Configuration Driven)環境。

整體環境的管理

工程師各自開發、驗證與上線。且未設計通用的執行記錄和分析報表。

規劃共通的執行記錄方式,統一監控與分析。

表 1.1:資料整合的現有作法與建議

現今資料整合的工作大多獨立進行,雖有些亂,但還可以忍受。隨著我們邁入 tera 世紀,這將變成一個需要深思的架構問題。


[1] 筆者建議資料倉儲(Data Warehouse)採用關聯資料設計,以方便統合各資料源的資料,並提供基礎深入的分析。資料超市(Data Mart)採用星狀結構(Star schema),以方便分析模型,快速直觀地做多維線上分析。但若超大型資料,需要採如 SQL Server PDW,則一定須採用星狀模式。以複寫維度資料表,分割數值資料表。

 

 

 

接下來,讓我們審思一下上述的問題:

SSIS專案管理

專案管理除了專案進程技巧外,是團隊文化與團隊學習的大議題,這一部分非我所著墨處,僅就 SSIS 略述於業界所見。

首先,參照 SSIS: Team Development Experiences

一般就我看到的,團隊中若有 2 位以上的 ETL 工程師,會是一個人完全負責一組資料的生命週期。這組資料有意義上的相關性,不侷限單一資料庫或單一技術。與開發程式的分工差不多,但劃分邊界較凸顯資料關聯性。

 

各專案deployment的分工與分權方法

各企業的系統上線/過版流程不盡相同,團隊組成與權限各異,這直接影響部署專案的分工與分權,或許,你需要參照上文對 ETL 特性的描述,再設計你 IT 成員的分工與分權。

可能的組成方式很多:

  • ETL 是 AP 的一部分,由 Developer 包裹 ETL,則它只是某種程式邏輯內的元件
  • 專門的 ETL 團隊,成員只負責 ETL,通常是 BI 系統 Data warehouse 團隊成員中的一個小團隊。

上述兩種,ETL 的開發如同 Developer,則分工與授權近似原企業本身 Developer 與 DBA 的合作模式。

 

  • DB 整合資料的一環,由 DBA 自行開發 ETL 流程,則全部掌握在 DBA。

 

封裝執行的監控與除錯方法

以下的討論侷限在線上系統,在開發與測試

這是規模的問題。若只有 10 來個封裝,轉 10 來個 table,套用 SSIS 既有的機制即可。

就大型企業批次作業而言,SSIS 本身的排程執行、監控、Log 與除錯,完全不夠用。需要自行設計一套架構(framework)集中 configuration、log,再設計設定 configuration/讀取 log 的介面,以完成排程執行、監控、Log 與除錯。

 

各專案人員可部署/監控/除錯自己的專案與封裝, 但看不到/碰不到別人的專案與封裝

SSIS 2012 是一個分界點,前後版本就這個議題的作法完全不同。因為詢問的人未採用 2012,也就不談。

在 2008 R2 以前,若部署到 SQL Server MSDB,可依 msdb 的角色授權。

若要監控與除錯,最好每個封裝自行撰寫輸出 Log,並在自行設計的架構中控制授權,有權的人得以監控/除錯。若僅以 SSIS 自家的 log,則要看 log 採用的機制,對存放的地方授權。

 

從PM與SA角度, 業界如何設計ETL workflow

也因企業需求而異,一般而言,若有專門的資料負責人(Data steward 清楚資料的來龍去脈,並有權存取資料,僅稍懂技術),則會當 ETL 的 sa。若分工沒有這麼細,大多是一位 Developer 完成所有的工作。

設計 ETL workflow 如同設計應用程式,除了本身的商業邏輯,還須注意前文的 ETL 特性。舉一位朋友針對考量的項目回答如下:
1.資料流程的執行時間(例如每天、每月、每工作日)       
2.每個流程執行的優先順序
3.每個流程同一時間最多可執行多少,也就每個機器同時可執行幾個DTEXEC
4.利用參數表記錄執行資料流的來源與目的伺服器
5.目的端要建立參數表,記錄某資料表是否已完成轉檔、以及轉檔時間、完成的轉檔筆數

 

workflow發動點建議在source or SSIS or target?  Why? 優缺點? 

一般是透過 SQL Agent 批次排程發動 ETL 流程,再由封裝內部設計或自行設計的 Framework 內之設定決定排程時間雖然到了,依照某個邏輯決定是否要執行。或是臨時啟用特定流程。

若自行開發的 ETL Framework 有撰寫特定的 Services 以處理流程,則依靠自己。

 

 

push data or pull data?Data若需加工, 加工動作建議在source or SSIS or target? Why? 優缺點?

往往取決於運算力與開發人員、系統間的存取權限。

控制流程交給 SSIS,資料處理建議在 DB 內做,參考 Top 10 SQL Server Integration Services Best Practices 第七點。

且因為資料處理集中在 Stored Procedure,而非散在各封裝,較容易維護與除錯。

  • 若是用 SQL Server Linked Server 做,建議用 Pull,可以 Bulk 行為,若是 Push,會變成單筆操作。
  • 若以 SSIS 資料流程的 SQL Server 目的地(SQL Server Destination),則在目的端 Pull 可以直接以記憶體交換(Shared Memory)將資料放進 SQL Server 較快。不過仍要看目的端記憶體是否足夠,SSIS Data Flow 只要用到硬碟就回慢

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: