2025年02月07日玄貓（BlackCat）

DuckDB高效能資料分析實務技巧

本文探討 DuckDB 的高效能資料分析技巧，以紐約計程車資料集為例，示範如何使用 SQL 進行資料探索、過濾、聚合和統計分析。文章涵蓋了 DuckDB 的核心功能，包括資料載入、SQL 查詢、效能最佳化以及與 Python 的整合，並探討了 DuckDB 在處理大型資料集時的優勢和應用場景。

資料函式庫資料分析

DuckDB SQL Parquet 資料分析效能最佳化大型資料集

DuckDB 是一款高效能的嵌入式分析型資料函式庫，尤其擅長處理 Parquet 等格式的大型資料集。本文以紐約計程車資料集為例，示範如何利用 DuckDB 進行資料探索和分析。首先，我們透過建立檢視 allRidesView 來載入儲存在 S3 的 Parquet 檔案，並使用 SUMMARIZE 函式快速概覽資料的統計資訊，例如最小值、最大值和資料型別。接著，我們示範如何過濾異常資料，例如 trip_distance 為負值的資料，並重新定義檢視以提升分析準確性。為了進一步提升查詢效能，我們展示瞭如何針對特定欄位進行分析，例如只查詢 trip_distance 的統計資訊，以及如何利用 Parquet 的元資料特性加速聚合運算，例如計算行程距離的平均值和標準差。此外，文章也介紹了 DuckDB 的進階 SQL 功能，例如使用 Star Expressions 和正規表示式對多個欄位進行聚合分析，以及如何使用 SUMMARIZE 作為子查詢。最後，文章討論了 DuckDB 在處理大型資料集時的效能考量，並提供了一些效能最佳化的建議。

探索紐約計程車資料集：來自Parquet檔案的洞察

為了保持從S3讀取的資料量合理，我們僅包含了2020年及之後的檔案，這仍然包含了1.18億行資料。當然，如果您希望查詢所有資料，可以擴充套件萬用字元以包含更多檔案。

這個查詢應該立即傳回，因為它並沒有讀取任何資料——它只是定義了一個檢視。這個檢視涵蓋了大約1.18億行資料，因此它不是一個小資料集，但也不是巨大的。

分析資料

為了獲得資料值及其分佈的概覽，並測試DuckDB在從S3檢索資料時的讀取效能，我們將使用以下查詢，再次使用SUMMARIZE。

.timer on
SUMMARIZE allRidesView;

這個查詢需要讀取實際資料以取得所有統計資訊，因此需要一些時間才能傳回結果。即使在靠近資料的AWS EC2例項上執行查詢，也需要超過30秒才能從1.18億行資料中產生結果：

┌──────────────────────┬─────────────┬────────────┬────────────┬────────────┐
│ column_name │ column_type │ min │ max │approx_uniq │
│ varchar │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼─────────────┼────────────┼────────────┼────────────┤
│ VendorID │ BIGINT │ 1 │ 6 │ 4 │
│ tpep_pickup_datetime │ TIMESTAMP │ 2001-01-01 │ 2098-09-11 │ 59777057 │
│ tpep_dropoff_datetime│ TIMESTAMP │ 2001-01-01 │ 2098-09-11 │ 60115892 │
│ passenger_count │ DOUBLE │ 0.0 │ 112.0 │ 12 │
│ trip_distance │ DOUBLE │ -30.62 │ 389678.46 │ 14254 │
│ RatecodeID │ DOUBLE │ 1.0 │ 99.0 │ 7 │
│ store_and_fwd_flag │ VARCHAR │ N │ Y │ 2 │
│ PULocationID │ BIGINT │ 1 │ 265 │ 264 │
│ DOLocationID │ BIGINT │ 1 │ 265 │ 265 │
│ payment_type │ BIGINT │ 0 │ 5 │ 6 │
│ fare_amount │ DOUBLE │ -133391414 │ 998310.03 │ 17618 │
│ extra │ DOUBLE │ -27.0 │ 500000.8 │ 671 │
│ mta_tax │ DOUBLE │ -0.55 │ 500000.5 │ 82 │
│ tip_amount │ DOUBLE │ -493.22 │ 133391363 │ 9064 │
│ tolls_amount │ DOUBLE │ -99.99 │ 956.55 │ 3897 │
│ improvement_surcharge│ DOUBLE │ -1.0 │ 1.0 │ 5 │
│ total_amount │ DOUBLE │ -2567.8 │ 1000003.8 │ 36092 │
│ congestion_surcharge │ DOUBLE │ -2.5 │ 3.0 │ 16 │
│ airport_fee │ INTEGER │ -2 │ 2 │ 5 │
├──────────────────────┴─────────────┴────────────┴────────────┴────────────┤
│ 19 rows │
└───────────────────────────────────────────────────────────────────────────┘
Run Time (s): real 33.907 user 586.582679 sys 8.706259

程式碼解密：

此查詢使用SUMMARIZE命令對allRidesView檢視進行統計分析，包括欄位名稱、資料型別、最小值、最大值和近似唯一值等。由於需要讀取實際資料，因此查詢耗時較長。

資料過濾與再分析

從結果中可以看到，有些行程的距離為負值，因此在進行進一步分析之前過濾掉這些異常值是有意義的。

CREATE OR REPLACE VIEW allRidesView AS
SELECT * 
FROM 's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_202*.parquet'
WHERE trip_distance > 0;

程式碼解密：

此查詢重新定義了allRidesView檢視，排除了行程距離小於或等於零的資料，從而避免了異常值對後續分析的影響。

對單一欄位進行匯總分析

如果我們只讀取trip_distance這一欄位，查詢速度會大大提高，只需要約3到4秒。

.timer on
.mode line
SUMMARIZE (SELECT trip_distance FROM allRidesView);

結果如下：

column_name = trip_distance
column_type = DOUBLE
min = 0.01
max = 389678.46
approx_unique = 13114
avg = 5.430654112761637
std = 536.0306563795983
q25 = 1.093394143132663
q50 = 1.8271836758492157
q75 = 3.389607008136687
count = 115976028
null_percentage = 0.0%
Run Time (s): real 3.033 user 49.924611 sys 1.228218

程式碼解密：

此查詢對allRidesView檢視中的trip_distance欄位進行匯總分析，提供了包括最小值、最大值、平均值、標準差等在內的多項統計指標。由於只讀取了一個欄位，因此查詢速度明顯加快。

使用`SUMMARIZE`進行靈活查詢

從DuckDB版本0.10.0開始，SUMMARIZE可以用作SELECT陳述式的來源，因此您可以根據需要選擇特定的欄位進行查詢。

SELECT column_name, column_type, count, max FROM SUMMARIZE allRidesView;

程式碼解密：

此查詢展示瞭如何使用SUMMARIZE作為子查詢，選取特定的欄位進行進一步分析。

高效能的聚合查詢

如果我們只執行某些可作為Parquet中元資料的聚合操作，可以獲得更快的結果，耗時不到一秒。

SELECT 
    min(trip_distance),
    max(trip_distance),
    avg(trip_distance),
    stddev(trip_distance),
    count(trip_distance) AS nonNull,
    count(*) as total,
    1-(nonNull/total) AS nullPercentage
FROM allRidesView;

程式碼解密：

此查詢對trip_distance欄位進行多種聚合運算，包括最小值、最大值、平均值、標準差等。由於這些運算可以直接利用Parquet的元資料，因此查詢速度非常快。

對多個欄位進行聚合分析

DuckDB的高階SQL功能允許我們對多個欄位進行聚合運算，如下所示：

.mode line
SELECT 
    count(*),
    min(columns(*)), 
    max(columns(*)),
    round(avg(columns('_(distance|amount|tax|surcharge|fee)')),2),
    round(stddev(columns(c -> c SIMILAR TO '.+(distance|amount|tax|surcharge|fee)')),2)
FROM allRidesView;

程式碼解密：

此查詢展示瞭如何使用DuckDB的Star Expressions功能對多個欄位進行聚合分析。透過正規表示式篩選出包含特定關鍵字的欄位，並計算它們的平均值和標準差。這種方法大大簡化了對多個相關欄位進行統計分析的過程。

大型資料集的效能考量：以紐約計程車資料集為例

在處理大型資料集時，查詢效能是至關重要的。本章節將探討如何使用DuckDB最佳化查詢效能，並以紐約計程車資料集作為範例進行詳細分析。

資料集概述與初步探索

首先，我們使用SUMMARIZE子句對資料集進行初步探索，以瞭解資料的基本統計資訊。透過對allRidesView檢視執行SUMMARIZE，我們能夠快速獲得各欄位的最小值、最大值、平均值和標準差等重要統計資料。

內容解密：

使用SUMMARIZE子句能夠快速概覽資料集的整體狀況。
同時對所有欄位進行聚合運算，大幅提升處理效率。

SUMMARIZE allRidesView;

執行結果顯示，聚合運算被一次性應用於所有欄位，處理時間僅需約7秒，展現出極高的效率。

深入分析：各年度計程車行駛資料

接下來，我們針對2020年至2024年間的計程車資料進行深入分析，計算每年的平均行駛距離、車費金額以及每英里的車費費率。

程式碼範例

SELECT 
  year(tpep_pickup_datetime) AS year,
  round(avg(trip_distance)) AS dist,
  round(avg(fare_amount), 2) AS fare,
  round(avg(fare_amount / trip_distance), 2) AS rate,
  count(*) AS trips
FROM 
  allRidesView
GROUP BY 
  year
HAVING 
  year BETWEEN 2020 AND 2024
ORDER BY 
  year;

內容解密：

透過year(tpep_pickup_datetime)提取年份資訊，並依此進行分組統計。
使用avg函式計算平均行駛距離和平均車費金額。
HAVING子句用於篩選特定年份範圍內的資料。
結果按年份排序，便於觀察趨勢變化。

查詢結果顯示，平均車費金額隨時間逐漸上漲，這可能與通貨膨脹有關。同時，我們觀察到2020年的資料出現明顯下降，這很可能是受到COVID-19疫情的影響。

乘客數量分佈分析

進一步地，我們分析了乘客數量少於10人且行駛距離超過10英里的旅程資料，以瞭解乘客數量的分佈情況。

程式碼範例

SELECT 
  passenger_count, 
  count(*) 
FROM 
  allRidesView 
WHERE 
  passenger_count < 10
GROUP BY 
  passenger_count 
ORDER BY 
  count(*) DESC;

內容解密：

使用WHERE子句篩選乘客數量少於10人的旅程。
按乘客數量進行分組，並統計每組的旅程次數。
結果按旅程次數降序排列，以突出主要的分佈特徵。

結果表明，大多數旅程僅有一名乘客，呈現出冪律分佈的特徵。

本文學習重點

本文首先介紹瞭如何安裝和使用DuckDB，包括其CLI和Python API。接著，我們展示瞭如何從CSV、JSON和Parquet檔案載入資料，並使用SQL進行分析，而無需建立資料表。同時，我們也探討瞭如何透過Python API使用SQL和fluent查詢，以及與pandas DataFrame的緊密整合。

在SQL的使用上，我們從基礎到進階功能，如視窗函式和CTE，進行了詳細的說明。DuckDB對標準SQL的擴充套件，如支援JSON、巢狀資料結構、進階JOIN和靈活的選擇、分組和聚合功能，也得到了深入的探討。

此外，我們還展示瞭如何將DuckDB整合到資料架構中，構建資料管道，並使用Streamlit和Apache Superset進行資料視覺化。最後，我們討論了處理大型資料集時的效能考量。

未涵蓋的內容

儘管本文對DuckDB進行了全面的介紹，但仍有一些領域未被探討。首先，DuckDB的內部實作和工作原理未被詳細說明。讀者可以透過研究DuckDB的原始碼、部落格文章、影片和檔案來進一步瞭解其架構、查詢執行引擎、索引功能、儲存層和向量化執行模型。

此外，DuckDB還支援多種語言的API，如C、R、Rust、Go和JavaScript等，讀者可以根據自己的需求選擇合適的環境進行開發。效能最佳化也是未來可以進一步探索的領域，儘管DuckDB在預設情況下表現良好，但仍有許多方法可以最佳化查詢和資料。

未來發展

隨著DuckDB 1.0版本的即將發布，我們可以期待更多的穩定性和相容性。未來的發展將繼續圍繞著擴充套件其功能和最佳化效能展開，包括開發新的擴充套件，如空間索引等，以滿足更多樣化的應用需求。

內容解密：

本文總結了本文的主要內容，並對未來的發展方向進行了展望。DuckDB作為一個強大的資料分析工具，不僅能夠處理大型資料集，還能與多種工具和語言整合，為資料工程師提供了極大的便利。未來，我們可以期待DuckDB在效能最佳化和功能擴充套件方面的進一步發展。

DuckDB的未來與擴充套件

隨著資料工程領域的不斷發展，DuckDB作為一個高效、靈活的資料分析工具，將繼續在多個方面進行擴充套件和最佳化。

效能最佳化

DuckDB在預設情況下已經具備出色的效能，但仍有許多方法可以進一步最佳化查詢和資料處理流程。例如，透過合理設計索引、使用適當的儲存格式等，可以顯著提升查詢效能。

擴充套件開發

DuckDB支援使用C++和Rust開發擴充套件，這為開發者提供了豐富的功能擴充可能性。例如，空間索引擴充套件可以為地理空間資料分析提供強大的支援。

多語言支援

除了Python API外，DuckDB還支援多種語言的API，如C、R、Rust、Go和JavaScript等。這使得開發者可以在自己熟悉的環境中使用DuckDB，進一步提升開發效率。

內容解密：

本文對DuckDB的未來發展進行了展望，包括效能最佳化、擴充套件開發和多語言支援等方面。這些發展將進一步鞏固DuckDB在資料分析領域的地位，為使用者提供更強大的功能和更靈活的使用方式。

SELECT * FROM 'https://example.com/data.parquet'
WHERE column_name = 'value';

內容解密：

此SQL查詢陳述式示範瞭如何直接從遠端Parquet檔案中查詢資料。透過指設定檔案URL和查詢條件，可以高效地檢索所需資料，而無需將整個檔案下載到本地。這種能力對於處理大型資料集尤其有用，因為它減少了不必要的網路傳輸，從而提高了查詢效能。

資料工程的未來與 DuckDB

我們認為 DuckDB 是一種非常有前途的技術，將在未來的資料工程中發揮重要作用。它不僅可以用於本地的中型資料集分析，也能夠在雲端進行大規模的資料處理，特別是在資料儲存位置附近進行處理，能夠提高效率並降低成本。

隨著本地資料的增長，例如來自健康監測、家庭自動化和個人資訊系統的資料，高效處理私人資料的需求將日益增加。由於 DuckDB 與 SQLite 的相似性，我們預計它將被廣泛應用於目前使用 SQLite 的領域，如應用程式、遊戲、瀏覽器、手機、物聯網和邊緣裝置，用於分析、資料聚合、預過濾和向其他系統及終端使用者提供資料。

在商業應用中，DuckDB 可以作為雲端資料倉儲（如 BigQuery、Redshift、Snowflake 等）的補充方案。許多使用這些系統的使用者並不需要處理 PB 級的資料，而是處理 GB 或 TB 級的資料，這些資料可以使用 DuckDB 以更低的成本、更少的資源和更簡單的操作來處理。

未來值得關注的創新領域包括：

在生成式 AI 應用中的整合，如高效的嵌入向量儲存和索引，以及支援串流資料處理。
DuckDB 團隊在提升 SQL 和資料處理的易用性和靈活性方面的工作，預計將帶來更多可組合性，使系統的各個方面能夠被靈活查詢和組合。

現在，是時候讓您參與進來，塑造 DuckDB 在資料工程未來的角色了！

探索紐約計程車資料集：來自Parquet檔案的洞察

分析資料

程式碼解密：

資料過濾與再分析

程式碼解密：

對單一欄位進行匯總分析

程式碼解密：

使用SUMMARIZE進行靈活查詢

程式碼解密：

高效能的聚合查詢

程式碼解密：

對多個欄位進行聚合分析

程式碼解密：

大型資料集的效能考量：以紐約計程車資料集為例

資料集概述與初步探索

內容解密：

深入分析：各年度計程車行駛資料

程式碼範例

內容解密：

乘客數量分佈分析

程式碼範例

內容解密：

本文學習重點

未涵蓋的內容

未來發展

內容解密：

DuckDB的未來與擴充套件

效能最佳化

擴充套件開發

多語言支援

內容解密：

內容解密：

資料工程的未來與 DuckDB

使用`SUMMARIZE`進行靈活查詢