細說SQL與ETL之間的小秘密

来源:https://www.cnblogs.com/huaweiyun/archive/2023/12/06/17879504.html
-Advertisement-
Play Games

本文分享自華為雲社區《GaussDB資料庫SQL系列-SQL與ETL淺談》,作者:Gauss松鼠會小助手2。 一、前言 在SQL語言中,ETL(抽取、轉換和載入)是一種用於將數據從源系統抽取到目標系統的過程。ETL過程通常包括三個階段:抽取(Extract)、轉換(Transform)和載入(Loa ...


本文分享自華為雲社區《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寫法必不可少。

 

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Parallel.ForEach Parallel.ForEach 是一個用於在集合上並行執行迭代操作的強大工具。它通過有效地利用多核處理器的能力來提高性能。Parallel.ForEach 不僅能夠簡化並行編程,而且它在執行簡單迴圈時可以提供比傳統迭代更好的性能。 下麵是一個簡單的示例,演示瞭如何 ...
  • 版本 Linux 6.5 背景 在學習cgroupv2的時候,想給子cgroup開啟cpu控制器結果失敗了: # 查看可以開啟哪些控制器 root@ubuntu-vm:/sys/fs/cgroup# cat cgroup.controllers cpuset cpu io memory hugetl ...
  • 使用 ssh-keygen 生成密鑰 參考資料 https://learn.microsoft.com/zh-cn/azure/virtual-machines/linux/create-ssh-keys-detailed 快速開始 以下 ssh-keygen 命令預設在 ~/.ssh 目錄中生成 ...
  • 家中有閑置的小新,是A卡正好合適裝linux 安裝前 關閉安全引導 通過關機鍵旁邊的重置口重新開機 OR 在開機界面按F2(開啟Hotkey 模式的要按Fn+F2) 進入BIOS 設置界面,關閉Secure Boot,這樣方便安裝linux 系統刻錄 linux有很多發行版 大家可以自行選擇,推薦U ...
  • ssh介紹 SSH,全稱為Secure Shell,是一種用於在網路中安全地進行遠程登錄和執行命令的協議。它通過加密的方式提供了對網路通信的保護,使得用戶可以在不安全的網路環境下安全地進行遠程管理和數據傳輸。 以下是 SSH 協議的一些重要特點和功能: 安全性:SSH 使用加密技術來保護數據在網路中 ...
  • 資料庫完整性包括正確性和相容性,DBMS通過提供約束條件機制、檢查方法和違約處理功能維護資料庫完整性,其中實體完整性通過主鍵確保唯一標識,參照實體性通過外鍵關聯,用戶定義完整性滿足特定應用需求,而觸發器和斷言提供事件驅動和複雜約束支持,共同構成資料庫保持一致性的關鍵機制。 ...
  • SQL(結構化查詢語言)的演變從IBM的SystemR開始,經過ANSI的標準化,近年來SQL標準變得更加豐富和複雜。SQL的特點包括綜合統一、高度非過程化、面向集合的操作方式以及提供多種使用方式的統一語法結構。在資料庫實例中,基本表獨立存在,而視圖是基本表導出的虛表,用於供人查看。資料庫模式結構包... ...
  • SQL ALTER TABLE 語句 SQL ALTER TABLE 語句用於在現有表中添加、刪除或修改列,也可用於添加和刪除各種約束。 ALTER TABLE - 添加列 要在表中添加列,請使用以下語法: ALTER TABLE 表名 ADD 列名 數據類型; 以下 SQL 向 "Customer ...
一周排行
    -Advertisement-
    Play Games
  • 隨著Aspire發佈preview5的發佈,Microsoft.Extensions.ServiceDiscovery隨之更新, 服務註冊發現這個屬於老掉牙的話題解決什麼問題就不贅述了,這裡主要講講Microsoft.Extensions.ServiceDiscovery(preview5)以及如何 ...
  • 概述:通過使用`SemaphoreSlim`,可以簡單而有效地限制非同步HTTP請求的併發量,確保在任何給定時間內不超過20個網頁同時下載。`ParallelOptions`不適用於非同步操作,但可考慮使用`Parallel.ForEach`,儘管在非同步場景中謹慎使用。 對於併發非同步 I/O 操作的數量 ...
  • 1.Linux上安裝Docken 伺服器系統版本以及內核版本:cat /etc/redhat-release 查看伺服器內核版本:uname -r 安裝依賴包:yum install -y yum-utils device-mapper-persistent-data lvm2 設置阿裡雲鏡像源:y ...
  • 概述:WPF界面綁定和渲染大量數據可能導致性能問題。通過啟用UI虛擬化、非同步載入和數據分頁,可以有效提高界面響應性能。以下是簡單示例演示這些優化方法。 在WPF中,當你嘗試綁定和渲染大量的數據項時,性能問題可能出現。以下是一些可能導致性能慢的原因以及優化方法: UI 虛擬化: WPF提供了虛擬化技術 ...
  • 引言 上一章節介紹了 TDD 的三大法則,今天我們講一下在單元測試中模擬對象的使用。 Fake Fake - Fake 是一個通用術語,可用於描述 stub或 mock 對象。 它是 stub 還是 mock 取決於使用它的上下文。 也就是說,Fake 可以是 stub 或 mock Mock - ...
  • 為.net6在CentOS7上面做準備,先在vmware虛擬機安裝CentOS 7.9 新建CentOS764位的系統 因為CentOS8不更新了,所以安裝7;簡單就一筆帶過了 選擇下載好的操作系統的iso文件,下載地址https://mirrors.aliyun.com/centos/7.9.20 ...
  • 經過前面幾篇的學習,我們瞭解到指令的大概分類,如:參數載入指令,該載入指令以 Ld 開頭,將參數載入到棧中,以便於後續執行操作命令。參數存儲指令,其指令以 St 開頭,將棧中的數據,存儲到指定的變數中,以方便後續使用。創建實例指令,其指令以 New 開頭,用於在運行時動態生成並初始化對象。方法調用指... ...
  • LiteDB 是一個輕量級的嵌入式 NoSQL 資料庫,其設計理念與 MongoDB 類似,但它是完全使用 C# 開發的,因此與 C# 應用程式的集成非常順暢。與 SQLite 相比,LiteDB 提供了 NoSQL(即鍵值對)的數據存儲方式,並且是一個開源且免費的項目。它適用於桌面、移動以及 We ...
  • 1 開源解析和拆分文檔 第三方的工具去對文件解析拆分,去將我們的文件內容給提取出來,並將我們的文檔內容去拆分成一個小的chunk。常見的PDF word mark down, JSON、HTML。都可以有很好的一些模塊去把這些文件去進行一個東西去提取。 優勢 支持豐富的文檔類型 每種文檔多樣化選擇 ...
  • OOM是什麼?英文全稱為 OutOfMemoryError(記憶體溢出錯誤)。當程式發生OOM時,如何去定位導致異常的代碼還是挺麻煩的。 要檢查OOM發生的原因,首先需要瞭解各種OOM情況下會報的異常信息。這樣能縮小排查範圍,再結合異常堆棧、heapDump文件、JVM分析工具和業務代碼來判斷具體是哪 ...