如何簡單實現ELT？

來源：Thoughtworks洞見在商業中，資料通常和業務、企業前景以及財務狀況相關，有效的資料管理可以幫助決策者快速有效地從大量資料中分析出有價值的資訊。資料整合(Data Integration)是整個資料管理流程中非常重要的一環，它是指將來自多個資料來源的資料組合在一起，提供一個統一的檢視。

資料整合可以透過各種技術來實現，本文主要介紹如何用ELT(extract, load, transform)實現資料整合。區別於傳統的ETL和其他的技術，ELT非常適合為資料湖倉或資料集市提供資料管道，並且可以用更低的成本，根據需求，隨時對大量資料進行分析。

接下來將透過一個簡單的示例（Demo）介紹如何實現ELT流程，具體的需求是將原始的電影票房資料儲存到資料倉儲，然後再對原始資料進行分析，得出相關的結果並且儲存在資料倉儲，供資料分析團隊使用，幫助他們預測未來的收入。

• Data Warehouse：Snowflake

  Snowflake是最受歡迎，最容易使用的資料倉儲之一，並且非常靈活，可以很方便地與AWS、Azure以及Google Cloud整合。

  
  

• Extract：透過k8s的cronjob將資料庫的資料存到S3。

  這裡的技術選型比較靈活，取決於源資料庫的型別以及部署的平臺。Demo中將源資料從資料庫提取出來後放在AWS S3，原因是Snowflake可以很方便與S3整合，支援複製資料到倉庫並且自動重新整理。

  
  

• Load：透過Snowflake的External Tables將S3中的資料複製進資料倉儲。

• Transform：dbt

  dbt支援使用SQL來進行簡單的轉換，同時提供了命令列工具，使用dbt我們可以進行良好的工程實踐比如版本控制，自動化測試以及自動化部署。但對於比較複雜的業務場景來說，轉換的過程一般都透過自己寫程式碼實現。

  
  

• Orchestrator: Airflow

  Airflow和Oozie相比有更加豐富的監控資料以及更友好的UI介面。
  

下圖描述瞭如何使用上述技術棧實現ELT：

圖中使用的Logo來自snowflake，dbt和Airflow的官方網站

Snowflake是一個將全新的SQL查詢引擎與一個專為雲設計的創新架構相結合的資料雲平臺，它支援更快更靈活地進行資料儲存、處理以及分析。

示例中將Snowflake作為資料倉儲，儲存原始資料電影院票房數量以及轉換後的資料。

許可權管理

和AWS類似，註冊Snowflake後會持有一個擁有所有許可權的root account。如果直接使用此賬號進行操作會非常危險，所以可以透過Snowflake提供的user和role來進行細粒度的許可權管理。

最佳實踐是用root account建立新的user並透過role賦予足夠的許可權，後續的操作都使用新建立的user來進行。

在下面的示例中，建立了一個名為TRANSFORMER的role，並且賦予它足夠的許可權。然後再建立一個使用這個role的user，在更方便地管理許可權的同時，也實踐了最小許可權原則：

資料結構

每一個Snowflake的資料庫都可以有多個schema，這裡我們根據常見的實踐，建立了schema RAW和ANALYTICS，分別用來存放原始資料和轉換之後的資料：

每一個schema下面都可以有table、view和stage等資料庫object。

stage是snowflake提供的一個空間，它支援我們將資料檔案上傳到這裡，然後透過copy命令把外部資料匯入到Snowflake。圖中MY_S3_STAGE就是Demo中用來載入存放在AWS S3中的資料檔案的，我們過這個stage實現了ELT中的L(Loading)。

在完成了原始資料的Extract和Loading後，怎樣根據需求對它們進行Transform從而獲得隱藏在資料中的有效資訊呢？

這裡我們選擇dbt來進行資料的轉化，它是一個支援我們透過簡單地編寫select語句來進行資料轉換的工具，在Demo中它幫助完成了歷史票房資料的統計工作。

Model

一個model就是一個寫在.sql檔案中的select語句，通常會預設使用檔名作為transform結果的表名。下面是demo中的一個model，from語句後面跟著的是一個dbt提供的引用源資料的方法。在model目錄裡的配置檔案中宣告源資料表之後，就可以直接透過source()方法來引用source table了。

Jinja Function

當需求變得更復雜時，如果僅僅透過SQL實現轉換將會很困難，所以可以透過Jinja Function來實現在SQL中無法做到的事。

比如在有多個Model的dbt工程中，通常會有一些可以複用的邏輯，類似於程式語言中的函式。有了Jinja Function，就可以把要複用的邏輯提取成單獨的Model，然後在其他Model中透過表示式{{ ref() }}來引用它：

Materializations

在上一部分的場景中，通常不希望把可複用的邏輯持久化在資料倉儲中。

這裡就可以引入配置Materializations來改變dbt對於model的持久化策略，比如將此配置設定為Ephemeral：

這樣model就僅被當作臨時表被其他model引用而不會被持久化在資料倉儲中。如果設定為View，model就會被在資料倉儲中建立為檢視。除此之外這個配置還支援型別：Table以及Incremental。

Test

為了防止原始資料有髒資料，所以在這裡引入測試幫助保證最後結果的正確性。dbt提供了兩種級別的測試：

• Generic test：這是一種比較通用的測試，為欄位級別，它通常可以加在對Source和Target的宣告裡，應用於某一個欄位並且可以重複使用。比如在demo中，我們希望ticket_year這個欄位不為空並且是不會重複的：

  

• Singular Test：它是透過一段SQL語句來定義的測試，是表級別。 

  比如查詢源資料表裡total_box_office小於0的記錄，當查詢不到結果時表示測試透過: 

  

有了把原始資料整合進資料倉儲的方法，也完成了資料轉化的工程， 那麼如何才能讓它們有順序地、定時地執行呢？

這裡我們選擇用Airflow進行任務的編排，它是一個支援透過程式設計編寫data pipeline，並且排程和監控各個任務的平臺。

DAG

第一步就是為我們的ELT流程建立一個流水線，在Airflow中，一個DAG(Directed Acyclic Graph)就可以看作是一個pipeline。宣告它的時候需要提供一些基本的屬性，比如DAG name, 執行間隔以及開始日期等等。

Airflow支援使用Python語言編寫pipeline的程式碼，因此也具有較強的擴充套件性。

Demo中我們設定這個DAG的開始日期是2022年5月20號，並且期望它每天執行一次：

Task

流水線建立完成之後，我們需要將ELT的各個步驟加入到這個流水線中。這裡的每一個步驟被稱為Task，Task是Airflow中的基本執行單位，類似於pipeline中的step。在Demo中，在資料倉儲中建立表、把原始資料載入到資料倉儲、測試和資料轉化分別是一個task。

在Airflow中，可以透過Operator快速宣告一個task，Operator是一個提前定義好的模版，只需要提供必要的引數比如task id，SQL語句等即可。

下面這個task的功能是在Snowflake中建立表，需要提供的是一個連線Snowflake的Connection，要執行的SQL語句以及目標database和schema：

Task dependency

當我們對於task的執行順序有特定要求時，比如為了保證最後報告的準確性，希望在對原始資料的測試透過之後再進行資料轉化。這時可以透過定義task之間的依賴關係，來對它們的執行順序進行編排，如下的依賴關係表示先在Snowflake建立資料表，然後將原資料載入到其中，完成後對於原始資料進行測試，如果測試失敗就不會再執行後續的task：

Backfill

在平時的工作中，我們經常會遇到業務變動導致資料表裡新增一個欄位的情況，此時就需要將原始資料重新同步一遍。這時就可以利用Airflow提供的Backfill機制，幫助我們一次性回填指定區間內缺失的所有歷史任務。

比如Demo中DAG的start date是5月20日，所以在開啟開關之後，Airflow幫我們回填了start date之後的所有DAG run：

上圖中DAG是在5月25日建立的，但Airflow卻只從開始日期建立任務到24號，看起來缺失了25號的任務。原因是上圖的24號是logical date(execution date)，即trigger DAG run的日期。因為在定義DAG的時候將schedule_interval屬性設定為daily，所以在25日(Actually Execute Date)當天只會執行24日(logical date)的任務。

監控和除錯

Airflow提供了友好的UI介面讓我們可以更方便地從各種維度監控以及除錯，比如檢視一年的執行情況：

或者每一個task的執行時間：

以及task的log：

等等，這裡只列舉了其中幾個，大家有興趣的話可以自己探索。

Parallelism

通常我們需要把多個資料來源的資料，整合到同一個資料倉儲中便於進行分析，因為這些task之間互相沒有影響，所以可以透過同步執行它們來提高效率。

這種場景下，一方面可以透過配置引數Parallelism來控制Airflow worker的數量，也就是同時可以執行的task的數量，另一方面也需要更改Executor的型別，因為預設的Sequential Executor只支援同時執行一個task。

假設task的依賴關係宣告為：

在更換到Local Executor並且設定parallelism為5之後，啟動Airflow，可以發現Airflow會建立5個worker。這時再觸發DAG run，task2和task3就可以同時執行了：

原始資料被載入到Snowflake的RAW schema中，dbt project可以隨時引用這些資料：

轉換結果被持久化在ANALYTICS schema裡，這些資料可以直接用來分析，也可以作為源資料被再次引用：

1. dbt project:

https://github.com/littlepainterdao/dbt_development

2. Airflow: 

本文整體比較基礎，希望之前沒有接觸過ELT的同學可以透過這篇文章對它以及Snowflake，dbt和Airflow有初步的瞭解。