讀資料工程之道：設計和構建健壯的資料系統25查詢

躺柒發表於2024-11-02

1. 查詢

1.1. 透過理解查詢、建模和轉換​，你會掌握將原始資料轉化為下游利益相關者可用資料的工具

1.2. 被很多人熟知的SQL，這是最流行和通用的查詢語言

1.3. 查詢是資料工程、資料科學和資料分析的基礎

1.4. 在瞭解資料轉換的基本模式和技術之前，你需要了解什麼是查詢、它如何在各種資料上工作，以及如何提高查詢效能

1.5. 查詢允許你檢索和處理資料

1.6. 資料工程師使用的常見的SQL DDL表示式包括：CREATE，DROP和UPDATE

1.7. 資料控制語言(Data Control Language，DCL)允許你透過使用SQL命令[如授予(GRANT)、拒絕(DENY)和撤銷(REVOKE)]來控制對資料庫物件或資料的訪問

1.8. 事務控制語言(Transaction Control Language，TCL)是支援控制事務細節的命令

  • 1.8.1. 透過事務控制語言，我們可以定義檢查點，以及操作回滾的條件

  • 1.8.2. 兩個常見的事務控制語言命令包括提交(COMMIT)和回滾(ROLLBACK)

2. 查詢的生命週期

2.1. 資料庫引擎編譯SQL，解析程式碼以檢查語義是否正確，確保引用的資料庫物件存在，並且當前使用者對這些物件有訪問權

2.2. SQL程式碼會被轉換為位元組碼

  • 2.2.1. 這個位元組碼以一種有效的、機器可讀的形式表示查詢在資料庫引擎中的執行步驟

2.3. 資料庫的查詢最佳化器分析位元組碼，以確定如何執行查詢、重新排序和重構步驟，儘可能有效地使用資源

2.4. 查詢被執行，併產生結果

3. 查詢最佳化器

3.1. 查詢最佳化器的任務是最佳化查詢效能，並透過有效的順序將查詢分成適當的步驟，使成本最小化

  • 3.1.1. 最佳化器將評估連線、索引、掃描資料大小和一些其他因素

  • 3.1.2. 查詢最佳化器試圖以成本最低的方式執行查詢

3.2. 連線是組合資料集和建立新資料集的最常見手段之一

  • 3.2.1. 通常一個資料集（如表或檔案）很少會被單獨使用，我們透過將其與其他資料集相結合來創造價值

  • 3.2.2. 連線在資料工程中擔任很關鍵的角色，許多資料庫都支援連線操作並具有很好的效能

  • 3.2.3. 即使是過去以連線效能差而聞名的列式資料庫，現在也普遍具有出色的效能

3.3. 提高查詢效能的一個常見技術是預連線資料

  • 3.3.1. 提前連線資料並讓查詢讀取預連線的資料往往是有幫助的，這樣你就不會重複進行計算密集型的工作了

  • 3.3.2. 另一個策略是保持一個更規範化的模式，但為最常見的分析和資料科學用例建立預連線表

  • 3.3.3. 可以很容易地建立預連線表，並培訓使用者利用這些表或在物化檢視內進行連線

  • 3.3.4. 使用公用表表示式(Common Table Expression，CTE)而不是巢狀子查詢或臨時表

  • 3.3.4.1. 公用表表示式會比建立中間表的效能更好

  • 3.3.5. 行爆炸

3.4. 查詢最佳化器的解釋計劃將向你展示查詢最佳化器如何確定其最佳的最低成本的查詢，使用了哪些資料庫物件（表、索引、快取等）​，以及每個查詢階段的各種資源消耗和效能統計

  • 3.4.1. 可以透過SQL的EXPLAIN命令提供解釋計劃，顯示資料庫執行查詢的步驟順序

3.5. 監控的內容

  • 3.5.1. 關鍵資源的使用情況，如磁碟、記憶體和網路

  • 3.5.2. 資料載入時間與處理時間

  • 3.5.3. 查詢的執行時間、記錄數、掃描的資料大小，以及重分配的資料量

  • 3.5.4. 可能導致資料庫資源爭奪的競爭性查詢

  • 3.5.5. 使用的併發連線數與可用連線數。併發連線過多會導致一些使用者無法連線資料庫

3.6. 避免全表掃描

  • 3.6.1. 所有的查詢都會掃描資料，但掃描的資料量是不一樣的

  • 3.6.2. 作為一個經驗法則，你應該只查詢你需要的資料

  • 3.6.3. 只要有可能，就使用剪枝來減少查詢中掃描的資料

  • 3.6.3.1. 列式資料庫和行式資料庫需要不同的剪枝策略

  • 3.6.3.2. 在一個列式資料庫中，你應該只選擇你需要的列

  • 3.6.3.3. 大多數面向列的OLAP資料庫還提供了額外的工具來最佳化你的表以提高查詢效能

  • 3.6.3.4. 可以將資料排序，從而更高效地訪問非常大的資料集

  • 3.6.3.5. BigQuery還允許你將表分割成更小的部分，允許你只查詢特定的部分，而不是整個表

  • 3.6.3.6. 在行式資料庫中，剪枝通常以表索引為中心

  • 3.6.3.7. 一般的策略是建立表索引以提高對效能最敏感的查詢的效能，同時不要讓表的索引太多，以免降低性

3.7. 資料庫的提交

  • 3.7.1. 資料庫的提交是指在資料庫中產生的一個變更，如建立、更新或刪除一條記錄、表或其他資料庫物件

  • 3.7.2. 許多資料庫都支援事務，即以一種保持一致狀態的方式同時提交幾個操作的概念

  • 3.7.3. 事務的目的是在資料庫處於健康狀態和發生故障時，保持資料庫的一致狀態

  • 3.7.4. 當多個併發事件在同一資料庫物件中進行讀、寫和刪除時，事務也會處理隔離問題

  • 3.7.5. 如果沒有事務，使用者在查詢資料庫時就會得到潛在的不一致的資訊

  • 3.7.6. 如果不符合ACID，你的查詢可能會返回意外的結果

  • 3.7.6.1. 可能是由於髒讀造成的，髒讀是指在一行資料被讀取的同時，另一個未提交的事務改變了該行資料

  • 3.7.7. 在更新和刪除事務期間，一些資料庫會建立新的檔案來代表資料庫的新狀態，並保留舊的檔案作為失敗檢查點的參考

  • 3.7.8. 執行大量的小提交會消耗大量的儲存空間且產生許多雜亂的檔案，需要定期進行資料清理

  • 3.7.9. 用在適當的場景並正確配置時，都是很棒的資料庫

  • 3.7.9.1. PostgreSQL

>  3.7.9.1.1. PostgreSQL方法的缺點是它需要行鎖定（阻止對某些行的讀寫）​，這在很多情況下可能會降低效能

>  3.7.9.1.2. PostgreSQL沒有針對大規模掃描或適合大規模分析應用程式的資料進行最佳化
  • 3.7.9.2. MongoDB
>  3.7.9.2.1. 可變一致性資料庫

>  3.7.9.2.2. 支援超高的寫入效能

>  3.7.9.2.3. 代價是如果資料庫寫入流量過大，它會在沒有通知的情況下放棄部分寫入

>  3.7.9.2.4. 物聯網應用，我們只想獲得儘可能多的測量資料，並不關心捕獲的測量資料是否完整
  • 3.7.9.3. BigQuery
>  3.7.9.3.1. 利用了一個時間點全表提交模型

>  3.7.9.3.2. 可以在一個單一寫查詢中並行地寫入大量的資料，這個操作是高度併發的

>  3.7.9.3.3. BigQuery的提交模型與Snowflake、Spark和其他一些公司使用的提交模型相似

3.8. 清理“死”記錄

  • 3.8.1. 事務在某些操作中會產生建立新記錄的開銷，比如更新、刪除和索引操作，同時保留舊記錄作為資料庫最後狀態的指標

  • 3.8.2. 舊記錄在資料庫檔案系統中會逐漸積累，而且永遠不再需要使用

  • 3.8.3. 應該在一個叫作清理的過程中刪除這些記錄

  • 3.8.3.1. 為新記錄釋放了空間，從而減少了表的臃腫，加快了查詢速度

  • 3.8.3.2. 經過清理後的記錄意味著查詢計劃可以更準確，過時的記錄會導致查詢最佳化器產生次優和不準確的執行計劃

  • 3.8.3.3. 可以清理低效的索引，使索引效能更好

  • 3.8.4. 在由物件儲存支援的資料庫中(BigQuery、Snowflake、Databricks)，保留舊資料的唯一缺點是它佔用了儲存空間，根據資料庫的儲存定價模式，可能會多花錢

  • 3.8.4.1. Databricks通常無限期地保留資料，直到它被手動清理

  • 3.8.5. Amazon Redshift叢集的磁碟支援多種配置方式，清理會影響其效能和可用儲存

3.9. 利用快取查詢結果

  • 3.9.1. 如果你的資料庫快取了查詢結果，重新執行同一個查詢可能會在1秒或更短時間內返回結果

  • 3.9.1.1. 結果被快取了，而且查詢不需要冷啟動

  • 3.9.2. 只要有可能，就利用查詢快取結果來減少資料庫的壓力，為頻繁執行的查詢提供更好的使用者體驗

  • 3.9.3. 物化檢視提供了另一種形式的查詢快取

4. 流資料上的查詢

4.1. 流資料是不斷產生的

  • 4.1.1. 為了充分利用資料流的優勢，我們必須調整查詢模式，以反映其實時性

4.2. CDC基本上是將分析資料庫設定為生產資料庫的從庫

  • 4.2.1. 歷史最悠久的流查詢模式之一是查詢分析資料庫，在略微滯後於生產資料庫的情況下查詢統計結果和聚合

4.3. 快速追隨者的方法

  • 4.3.1. 生產資料庫一般不具備在處理生產工作負載的同時又執行大型分析掃描的能力

  • 4.3.2. 快速追隨者模式可以利用傳統的事務資料庫作為跟隨者，但使用適當的OLAP系統也有很大優勢

  • 4.3.3. Druid和BigQuery都將流快取與列儲存結合起來，其設定有點類似於Lambda架構

  • 4.3.4. 沒有從根本上重新思考批處理查詢模式

  • 4.3.4.1. 仍在對錶的當前狀態執行SELECT查詢，錯過了根據流中的變更動態觸發事件的機會

4.4. Kappa架構

  • 4.4.1. 架構的主要思想是像處理事件一樣處理所有資料，並將這些事件儲存為一個流而不是一個表

  • 4.4.2. Kappa架構儲存來自變更資料捕獲的事件

  • 4.4.2.1. 事件流也可以直接從應用程式後端、物聯網裝置群或任何生成事件並能透過網路推送的系統中流出來

  • 4.4.3. Kappa架構不是簡單地把流式儲存系統當作一個緩衝區，而是在一個較長的保留期將事件保留在儲存中，並且可以直接從這個儲存中查詢資料

  • 4.4.3.1. 保留期可以相當長（幾個月或幾年）

  • 4.4.4. “核心思想”是將流式儲存作為用於檢索和查詢歷史資料的實時傳輸層和資料庫

4.5. 視窗

  • 4.5.1. 傳統批處理查詢的一個基本限制是將查詢引擎視為一個外部觀測者

  • 4.5.2. 視窗是流查詢和處理的一個基本特性

  • 4.5.2.1. 視窗是透過動態觸發器進行處理的小批次資料

  • 4.5.2.2. 視窗是以某種方式根據時間動態生成的

  • 4.5.3. 會話視窗

  • 4.5.3.1. 會話視窗將發生在一起的事件分組，並過濾掉沒有事件發生的非活動期

  • 4.5.3.2. 會話視窗也可以為遲到的資料做要求

  • 4.5.4. 固定時間視窗

  • 4.5.4.1. 固定時間（又稱滾動）視窗的特點是按固定的時間段執行並處理自上一個視窗關閉後的所有資料

  • 4.5.5. 滑動視窗

  • 4.5.5.1. 滑動視窗中的事件被歸入固定時間長度的視窗中，視窗可能會重疊在一起

  • 4.5.6. 水印

  • 4.5.6.1. 水印是一個視窗用來確定資料是否屬於既定的時間間隔或是否被認為遲到的閾值

  • 4.5.6.2. 資料有時會不按照資料來源的生成順序獲取

>  4.5.6.2.1. 如果到達的資料對視窗來說是新的，但比水印的時間戳要早，那麼它就被認為是遲到的資料

5. 連線

5.1. 傳統的表連線

  • 5.1.1. 簡單地在資料庫中連線這兩個表

  • 5.1.2. 流可以為這些表中的一個或兩個提供資料

5.2. 豐富性意味著我們將流加入其他資料中

  • 5.2.1. 用於增強的資料來源幾乎可以來自任何地方（雲資料倉儲、RDBMS）中的表，或物件儲存中的檔案

5.3. 流對流的連線

  • 5.3.1. 越來越多的流式系統支援流對流的直接連線

5.4. 典型的流資料連線架構依賴於流的快取

  • 5.4.1. 快取資料的保留時間是可配置的

  • 5.4.2. 更長的保留間隔需要更多的儲存和其他資源

相關文章