本文分享自華為雲社區《GaussDB資料庫SQL系列-SQL與ETL淺談》，作者：Gauss松鼠會小助手2。

一、前言

在SQL語言中，ETL（抽取、轉換和載入）是一種用於將數據從源系統抽取到目標系統的過程。ETL過程通常包括三個階段：抽取（Extract）、轉換（Transform）和載入（Load）。但這些其實都脫離不了資料庫系統，本節主要從GaussDB資料庫生態出發，給大家簡單講一下SQL 與 ETL的過程與關係。

二、SQL與ETL的概述

SQL（結構化查詢語言）

SQL是一種用於管理關係資料庫系統的標準編程語言（例如、MySql、GaussDB等）。它用於查詢、插入、更新和刪除資料庫中的數據。SQL語言主要用於資料庫管理系統的交互，它並不是一種通用的編程語言，而是專門設計用於操作關係資料庫的。

ETL（Extract-Transform-Load）

ETL是一個過程，用於從源系統提取數據，將其轉換為目標系統所需的格式，然後將其載入到目標系統庫。ETL是數據集成的一部分，用於將分散的、不一致的數據整合到一起，然後通過統一的介面將數據傳輸到目標系統庫進行分析和應用。

ETL是資料庫處理數據的重要環節，當在ETL過程中使用SQL時，通常涉及如下圖操作。

三、ETL過程中的SQL示例（GaussDB）

本章節涉及到的SQL適用於GaussDB等資料庫。

1、提取（Extract）

在ETL過程中，抽取是將數據從源系統中獲取並傳輸到目標系統的第一步。這可能涉及到連接到資料庫、讀取文件、調用API等操作。在抽取數據時，需要考慮以下幾個方面：

• 數據源的選擇：根據具體業務需求選擇數據源，並考慮數據量、數據質量、數據類型等因素。

• 抽取方式的選擇：可以選擇增量、全量更新等不同的抽取方式。

• 數據抽取的調度：需要考慮時間、頻率、併發等因素，以確保數據的及時性和準確性。

常用SQL語句示例：

1）全量（表）提取

SELECT * FROM source_table;

2）增量提取（例如，根據日期欄位，按天、月、年提取，或其他維度）

SELECT * FROM source_table WHERE t_date=’20230907’;

Tip：根據業務需求提取全欄位或者指定欄位。

2、轉換（Transform）

在ETL過程中，轉換是對抽取的數據進行清洗、轉換、過濾和格式化等操作，以滿足目標系統的需求。轉換的主要操作包括：

• 數據清洗：包括去重、填充缺失值、異常值處理等操作，以確保數據的質量和準確性。

• 數據轉換：包括數據類型轉換、欄位計算、格式化等操作，以使數據符合目標系統的數據結構和數據類型。

常用SQL語句示例：

1）數據行去重

--數據行去重（隨機保留或者優先保留）

SELECT order_id, user, product, number

FROM (

SELECT *,

ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY proctime ASC) as row_num

FROM Orders)

WHERE row_num = 1;
參數說明：

ROW_NUMBER(): 從第一行開始，依次為每一行分配一個唯一且連續的號碼。

PARTITION BY col1[, col2...]: 指定分區的列，例如去重的鍵。

ORDER BY time_attr [asc|desc]: 指定排序的列。升序（ ASC ）排列指只保留第一行，而降序排列（ DESC ）則指保留最後一行。

WHERE rownum = 1: 取ROW_NUMBER()生成的編號1。

可參考上一篇文章：

https://blog.csdn.net/GaussDB/article/details/132752614

2）欄位清洗（例如：去空格）

通過TRIM()、REPLACE()、CASE WHEN … THEN … END等關鍵字或函數進行異常字元處理。

--清洗空格

SELECT length(' 去空格 ')

,length(TRIM(' 去空格 '))     

,length(REPLACE(' 去空格 ',' ',''))

     ,length(CASE WHEN ' 去空格 ' <>'去空格' THEN '去空格' END);
說明：

Trim()，通過去空格函數進行清洗

Replace(), 通過替換清洗

case when … then …end 與字典表比對進行清洗，此處的與字典表比對省略，具體根據業務需求進行。

3）非法日期清洗

創建日曆表calendar，存儲19000101到30001231的所有日期，通過比對判斷是否為合規的日期格式。

--與字典表比對

SELECT *,CASE WHEN create_date NOT IN (SELECT c_date FROM calendar) THEN 0 ELSE 1 END status FROM T1
--剔除所有非法日期行

DELETE FROM T1 WHERE status =0;

Tip: 上文寫法適合GaussDB等關係型資料庫，且都是比較基礎的示意說明，具體需要根據業務需要進行編寫。

3、載入（Load）

在ETL過程中，載入是將轉換後的數據載入到目標系統中，通常是數據倉庫或數據集市。載入的主要操作包括：

• 數據映射。將轉換後的數據映射到目標系統中，包括表、欄位等。

• 數據載入。將轉換後的數據載入到目標系統中，併進行數據校驗、數據整合等操作。

常用SQL語句示例：

1）增量表（累加，欄位、表一 一映射）

INSERT INTO target_table (column1, column2, column3) SELECT column1, column2, column3 FROM source_table;

2）全量表（全刪全插，欄位、表一 一映射）

--情況目標表

TRUNCATE table target_table;
--全量插入

INSERT INTO target_table (column1,column2,…) SELECT column1,column2,… FROM source_table;

3）作業重跑，清空指定分區數據，重新載入

• --清理表分區的數據

--清空分區etl_date

ALTER TABLE orders TRUNCATE PARTITION etl_date;

--或者清空分區etl_date=20230911。

ALTER TABLE orders TRUNCATE PARTITION for (20230911);
--插入新數據

INSERT INTO target_table (column1,column2,…,etl_date) SELECT column1,column2,…,etl_date FROM source_table;

Tip：數據載入涉及到的演算法及表設計非常複雜，例如，涉及歷史拉鏈表（關鏈、開鏈）、全量表（全刪全插）、增量表（累加）等。設計時需要從數倉/數據集市的全局架構出發，確保合理、準確、高效等。

四、附DataArts Studio介紹

華為雲GaussDB相關的生態工具DataArts Studio數據治理中心是一個強大的ETL工具和技術，它可以幫助開發人員設計、編寫和管理ETL腳本。以下是DataArts Studio在這些方面的主要功能和優勢：

• 可視化的ETL設計：DataArts Studio提供了一個直觀的可視化界面，使開發人員能夠以圖形化方式設計和配置ETL流程。通過拖放組件和連接線，開發人員可以輕鬆定義數據提取、轉換和載入的步驟，而無需編寫複雜的代碼。

• 內置的數據轉換和處理功能：DataArts Studio提供了豐富的內置轉換和處理組件，如數據清洗、數據格式轉換、數據合併、數據計算等。開發人員可以直接使用這些組件，而無需自行編寫轉換邏輯，從而加快開發速度並減少錯誤。

• 強大的數據連接和集成能力：DataArts Studio支持與各種數據源的連接和集成，包括關係型資料庫、文件系統、雲存儲、API介面等。開發人員可以輕鬆地配置數據源連接，並直接從這些數據源中提取數據。

• 可擴展的腳本編寫和管理：雖然DataArts Studio提供了可視化的ETL設計界面，但它也支持自定義腳本編寫。開發人員可以使用內置的腳本編輯器編寫自定義的ETL腳本，以滿足特定的需求。此外，DataArts Studio還提供了ETL腳本的版本控制和管理功能，方便團隊協作和腳本的維護。

• 實時監控和調試：DataArts Studio提供了實時監控和調試功能，開發人員可以實時查看ETL流程的執行狀態、數據處理的結果和錯誤信息。這有助於快速發現和解決問題，提高ETL腳本的質量和可靠性。

五、小結

SQL與ETL的關係在於，SQL語言通常用於ETL過程中的數據提取和轉換階段。通過使用SQL查詢語句，可以從源資料庫中提取所需的數據，然後使用SQL語句對數據進行必要的轉換和處理，以便將其載入到目標系統。

當然了，現在好多企業都有專門的ETL工具，但其實後臺都是通過類似“PYTHON + SQL”、“PERL + SQL”等方式實現的，其重點在於ETL過程中的SQL處理。 同樣，在GaussDB資料庫生態中也是不可或缺的，掌握GaussDB資料庫相關的SQL寫法必不可少。

點擊關註，第一時間瞭解華為雲新鮮技術~