DuckDB 自動推斷檔案型別與結構解析

DuckDB 在資料處理領域以其靈活性和效率著稱，尤其在自動推斷檔案型別和結構方面表現出色。無論是常見的 CSV、JSON 或列式儲存的 Parquet，DuckDB 都能有效讀取和解析。對於 CSV 檔案，DuckDB 透過取樣機制自動偵測分隔符號、參照規則等，並根據預設的型別偵測優先順序（布林值、整數、浮點數、時間、日期、時間戳記，最後是字串）判斷欄位型別。在處理 JSON 資料時，DuckDB 的內建 JSON 擴充功能能自動解析巢狀結構，並可利用 unnest 函式展開陣列，簡化查詢。此外，DuckDB 還支援將 CSV 檔案轉換為 Parquet 格式，以提升在大資料處理框架下的效能。透過 read_csv_auto 函式讀取 CSV 檔案並自動推斷型別，再利用 COPY 指令搭配 FORMAT PARQUET 和壓縮選項，即可生成 Parquet 檔案。

深入剖析 DuckDB：自動推斷檔案型別與結構

DuckDB 在處理資料時展現了其強大的靈活性，尤其是在自動推斷檔案型別與結構方面。無論是 CSV、JSON 或 Parquet 格式，DuckDB 都能有效地讀取並解析內容。本章節將探討 DuckDB 如何自動推斷檔案型別，並介紹其在處理 JSON 資料時的強大功能。

CSV 解析的挑戰與 DuckDB 的解決方案

CSV 檔案格式看似簡單，但實際解析時卻可能遇到許多挑戰。DuckDB 採用取樣的方式來自動偵測 CSV 檔案的結構，包括分隔符號、參照規則、跳脫字元等。預設情況下，DuckDB 會讀取前 20,480 筆資料來進行分析，以確定欄位的資料型別。

資料型別偵測優先順序

DuckDB 會依照以下順序來偵測資料型別：

BOOLEAN：布林值型別
BIGINT：大整數型別
DOUBLE：雙精確度浮點數型別
TIME：時間型別
DATE：日期型別
TIMESTAMP：時間戳記型別
VARCHAR：字串型別（最低優先順序）

若資料無法轉換為上述任何型別，則預設為 VARCHAR 型別。

-- 查詢 DuckDB 函式引數以自訂 CSV 讀取行為
SELECT DISTINCT function_name, 
       unnest(parameters) AS parameter
FROM duckdb_functions()
WHERE function_name = 'read_csv'
ORDER BY parameter;

處理巢狀 JSON 資料

DuckDB 的內建 JSON 擴充功能提供了強大的 JSON 處理能力，能夠自動偵測 JSON 資料的結構並轉換為 DuckDB 的向量格式。

探索 JSON 資料結構

以下範例展示如何使用 DuckDB 查詢多個 JSON 檔案，並自動推斷其結構：

-- 設定輸出模式為 line 以顯示完整資訊
.mode line

-- 查詢 JSON 檔案結構
DESCRIBE FROM 'xg/shots_*.json';

輸出結果顯示每個 JSON 物件包含 h 和 a 兩個屬性，分別指向結構陣列。由於不同檔案的結構不完全一致，DuckDB 無法自動合併這些結構。

解封裝 JSON 陣列

為了更方便地處理資料，我們可以使用 unnest 函式將 JSON 陣列展開為個別列：

-- 解封裝 h 和 a 陣列並合併結果
SELECT unnest(h) AS row FROM 'xg/shots_*.json'
UNION ALL
SELECT unnest(a) AS row FROM 'xg/shots_*.json'
LIMIT 3;

建立檢視表簡化查詢

為了方便後續分析，我們可以根據上述查詢建立一個檢視表：

-- 建立檢視表 shots_data
CREATE VIEW shots_data AS 
SELECT unnest(h) AS row FROM 'xg/shots_*.json'
UNION ALL
SELECT unnest(a) AS row FROM 'xg/shots_*.json';

#### 內容解密：

unnest函式的作用：將JSON陣列展開為個別列，方便後續查詢和分析。
UNION ALL的使用：合併多個SELECT陳述式的結果，並保留重複的行。
CREATE VIEW陳述式：建立一個虛擬表，簡化複雜查詢的過程。

本章節探討了 DuckDB 在自動推斷檔案型別和處理 JSON 資料方面的強大功能。透過取樣和型別偵測，DuckDB 能夠有效地解析 CSV 檔案。同時，其內建的 JSON 擴充功能使得處理巢狀 JSON 資料變得更加簡單。這些功能大大簡化了資料探索和分析的過程，使 DuckDB 成為處理多種資料格式的理想工具。

5.3 解析巢狀 JSON 資料

在處理 JSON 資料時，DuckDB 提供了一種便利的方式來解析巢狀結構。讓我們首先建立一個名為 shots 的檢視（view），該檢視根據巢狀 JSON 檔案中的資料。

CREATE VIEW shots AS
SELECT row
FROM (
  SELECT unnest(h) AS row FROM 'xg/shots_*.json'
  UNION ALL
  SELECT unnest(a) AS row FROM 'xg/shots_*.json'
);

接下來，我們檢視該檢視的結構描述：

DESCRIBE shots;

輸出結果類別似如下：

column_name = row
column_type = STRUCT(
  id BIGINT, "minute" BIGINT, result VARCHAR, X VARCHAR, Y VARCHAR,
  xG VARCHAR, player VARCHAR, h_a VARCHAR, player_id BIGINT,
  situation VARCHAR, season BIGINT, shotType VARCHAR, match_id BIGINT,
  h_team VARCHAR, a_team VARCHAR, h_goals BIGINT, a_goals BIGINT,
  date TIMESTAMP, player_assisted VARCHAR, lastAction VARCHAR)
null = YES
key =
default =
extra =

資料型別修正

DuckDB 的自動推斷並不總是完美。在這個例子中，X、Y 和 xG 欄位應該是數值型別（例如 DOUBLE），否則我們將無法對這些欄位執行數值運算。為了修正這一點，我們需要重新定義 STRUCT 的型別，並將這些欄位轉換為正確的型別。

CREATE OR REPLACE VIEW shots AS
SELECT CAST(row AS STRUCT(
  id BIGINT, "minute" BIGINT, result VARCHAR,
  X DOUBLE, Y DOUBLE, xG DOUBLE,
  player VARCHAR, h_a VARCHAR, player_id BIGINT,
  situation VARCHAR, season BIGINT, shotType VARCHAR,
  match_id BIGINT, h_team VARCHAR, a_team VARCHAR,
  h_goals BIGINT, a_goals BIGINT, date TIMESTAMP,
  player_assisted VARCHAR, lastAction VARCHAR)) AS row
FROM (
  SELECT unnest(h) AS row FROM 'xg/shots_*.json'
  UNION ALL
  SELECT unnest(a) AS row FROM 'xg/shots_*.json'
);

內容解密：

使用 CAST 轉換 STRUCT 型別：將 row 欄位轉換為具有正確資料型別的 STRUCT。
X、Y 和 xG 的型別修正：將這三個欄位的型別從 VARCHAR 改為 DOUBLE，以便進行數值運算。
unnest 的作用：將巢狀的 JSON 陣列展開成多行資料。

再次檢視檢視的結構描述，可以看到這些欄位的型別已經被更新為 DOUBLE。

資料驗證與錯誤處理

如果 DuckDB 的型別推斷正確，但某些欄位確實無法自動轉換為非字串型別，那麼查詢檢視時可能會出現問題。例如，數值型別中可能包含意外的值。為瞭解決這個問題，可以使用 try_cast 代替 cast，這樣無法轉換的欄位將被傳回為 NULL。

結構展開

DuckDB 支援將 STRUCT 展開為多個欄位，這使得查詢和操作資料更加方便。

CREATE OR REPLACE VIEW shotsFlattened AS (
  SELECT row.*
  FROM shots
);

內容解密：

row.* 的作用：將 row 結構中的每個欄位展開為獨立的欄位。
簡化查詢：展開後的檢視使得查詢和操作個別欄位更加直觀。

檢視 shotsFlattened 的結構描述，可以看到每個欄位都已展開為獨立的欄位。

.mode duckbox
DESCRIBE shotsFlattened;

輸出結果如下（部分截斷以提高可讀性）：

┌─────────────────┬─────────────┬─────────┐
│ column_name     │ column_type │ null    │
│ varchar         │ varchar     │ varchar │
├─────────────────┼─────────────┼─────────┤
│ id              │ BIGINT      │ YES     │
│ minute          │ BIGINT      │ YES     │
│ result          │ VARCHAR     │ YES     │
│ X               │ DOUBLE      │ YES     │
│ Y               │ DOUBLE      │ YES     │
│ xG              │ DOUBLE      │ YES     │
│ player          │ VARCHAR     │ YES     │
│ h_a             │ VARCHAR     │ YES     │
│ player_id       │ BIGINT      │ YES     │
│ situation       │ VARCHAR     │ YES     │
└─────────────────┴─────────────┴─────────┘

深入解析巢狀 JSON 資料處理與檔案格式轉換

在資料工程領域中，處理不同格式的資料是一項常見的任務。本文將探討如何使用 DuckDB 來處理巢狀 JSON 資料，並將 CSV 檔案轉換為 Parquet 格式。

處理巢狀 JSON 資料

首先，我們來處理巢狀的 JSON 資料。假設我們有一個名為 shotsFlattened 的表格，其中包含足球比賽中的射門資料。我們的目標是找出 2022 年賽季中預期進球數（xG）最高的球隊。

SQL 查詢範例

SELECT 
    CASE 
        WHEN h_a = 'h' AND result <> 'OwnGoal' THEN h_team
        WHEN h_a = 'a' AND result = 'OwnGoal' THEN h_team
        ELSE a_team
    END AS team,
    round(sum(xg), 2) AS totalXG,
    count(*) FILTER(WHERE result IN ('Goal', 'OwnGoal')) AS goals
FROM shotsFlattened
WHERE season = 2022
GROUP BY ALL
ORDER BY totalXG DESC
LIMIT 10;

內容解密：

CASE 陳述式：根據 h_a 和 result 的值，決定哪個球隊應該被計入 team 欄位。
sum(xg)：計算每個球隊的預期進球數總和，並使用 round 函式四捨五入到小數點後兩位。
count(*) FILTER：計算每個球隊的實際進球數（包括烏龍球）。
GROUP BY ALL：按照所有欄位進行分組。
ORDER BY totalXG DESC：按照預期進球數總和進行降序排序。
LIMIT 10：只顯示前 10 名的球隊。

執行上述查詢後，我們可以得到 2022 年賽季中預期進球數最高的球隊排名。

將 CSV 檔案轉換為 Parquet 格式

接下來，我們將探討如何將 CSV 檔案轉換為 Parquet 格式。Parquet 是一種列式儲存格式，適用於大資料處理框架，如 Apache Spark。

使用 DuckDB 進行轉換

首先，我們需要使用 read_csv_auto 函式來讀取 CSV 檔案，並自動推斷資料型別。

SELECT filename, count(*)
FROM read_csv_auto(
    'atp/atp_rankings_*.csv',
    filename=true
)
GROUP BY ALL
ORDER BY ALL;

內容解密：

read_csv_auto：自動讀取符合指定模式的 CSV 檔案，並推斷資料型別。
filename=true：在結果中新增一個包含檔名的欄位。
GROUP BY ALL：按照所有欄位進行分組。
ORDER BY ALL：按照所有欄位進行排序。

執行上述查詢後，我們可以得到每個 CSV 檔案的行數。

將CSV資料轉換為Parquet格式的探索與實踐

在處理大規模資料時，選擇合適的儲存格式對於資料處理的效率和效能至關重要。CSV（逗號分隔值）檔案是一種常見的資料交換格式，但它缺乏對資料型別的明確定義和高效的壓縮機制。Parquet是一種列式儲存格式，專為大資料處理設計，具備高效壓縮、自描述schema等優點。本篇文章將探討如何利用DuckDB將CSV資料轉換為Parquet格式，並在此過程中最佳化資料結構。

初始資料探索

首先，我們面對的是分散在七個CSV檔案中的大約300萬條記錄。這些檔案大小不一，最大的接近100萬條記錄，最小的約有2萬條記錄。這種規模的資料集適合轉換為Parquet格式，以利用其高效的儲存和查詢能力。

SELECT *
FROM 'atp/atp_rankings_*.csv'
LIMIT 5;

執行上述查詢後，我們獲得了前五條記錄的輸出：

┌──────────────┬───────┬────────┬────────┐
│ ranking_date │ rank │ player │ points │
│ int64 │ int64 │ int64 │ int64 │
├──────────────┼───────┼────────┼────────┤
│ 20000110 │ 1 │ 101736 │ 4135 │
│ 20000110 │ 2 │ 102338 │ 2915 │
│ 20000110 │ 3 │ 101948 │ 2419 │
│ 20000110 │ 4 │ 103017 │ 2184 │
│ 20000110 │ 5 │ 102856 │ 2169 │
└──────────────┴───────┴────────┴────────┘

內容解密：

ranking_date欄位被識別為int64型別，但實際上代表日期，格式為%Y%m%d。
其他欄位如rank、player和points均為int64型別，分別代表排名、球員ID和積分。

資料型別最佳化

為了更好地利用Parquet的優勢，我們需要對資料型別進行最佳化。首先，將ranking_date轉換為日期型別：

SELECT * REPLACE (
    cast(strptime(ranking_date::VARCHAR, '%Y%m%d') AS DATE) AS ranking_date
)
FROM 'atp/atp_rankings_*.csv'
LIMIT 5;

轉換後的結果如下：

┌──────────────┬───────┬────────┬────────┐
│ ranking_date │ rank │ player │ points │
│ date │ int64 │ int64 │ int64 │
├──────────────┼───────┼────────┼────────┤
│ 2000-01-10 │ 1 │ 101736 │ 4135 │
│ 2000-01-10 │ 2 │ 102338 │ 2915 │
│ 2000-01-10 │ 3 │ 101948 │ 2419 │
│ 2000-01-10 │ 4 │ 103017 │ 2184 │
│ 2000-01-10 │ 5 │ 102856 │ 2169 │
└──────────────┴───────┴────────┴────────┘

內容解密：

使用strptime函式將ranking_date從整數轉換為日期格式。
這種轉換使得日期欄位能夠被正確地解釋和處理。

聯合查詢與資料合併

接下來，我們需要將球員資訊與排名資料合併，並對球員的出生日期（dob）進行類別似的轉換：

SELECT * EXCLUDE (
    player,
    wikidata_id,
    name_first,
    name_last,
    player_id,
    hand,
    ioc
)
REPLACE (
    cast(strptime(ranking_date::VARCHAR, '%Y%m%d') AS DATE) AS ranking_date,
    cast(strptime(dob, '%Y%m%d') AS DATE) AS dob
),
name_first || ' ' || name_last AS name
FROM 'atp/atp_rankings_*.csv' rankings
JOIN (FROM 'atp/atp_players.csv') players
ON players.player_id = rankings.player
ORDER BY ranking_date DESC
LIMIT 5;

查詢結果如下：

┌──────────────┬───────┬────────┬────────────┬────────┬────────────────────┐
│ ranking_date │ rank │ points │ dob        │ height │ name               │
│ date         │ int64 │ int64  │ date       │ int64  │ varchar            │
├──────────────┼───────┼────────┼────────────┼────────┼────────────────────┤
│ 2022-12-26   │ 1     │ 6820   │ 2003-05-05 │ 185    │ Carlos Alcaraz    │
│ 2022-12-26   │ 2     │ 6020   │ 1986-06-03 │ 185    │ Rafael Nadal      │
│ 2022-12-26   │ 3     │ 5820   │ 1998-12-22 │ 183    │ Casper Ruud       │
│ 2022-12-26   │ 4     │ 5550   │ 1998-08-12 │ 193    │ Stefanos Tsitsipas│
│ 2022-12-26   │ 5     │ 4820   │ 1987-05-22 │ 188    │ Novak Djokovic    │
└──────────────┴───────┴────────┴────────────┴────────┴────────────────────┘

內容解密：

聯合atp_rankings_*.csv和atp_players.csv兩個檔案，根據player_id進行匹配。
對dob欄位進行日期格式轉換，並將球員的名字合併為一個欄位。

生成Parquet檔案

最後，我們將處理好的資料匯出為Parquet檔案。為了控制記憶體使用，我們調整了DuckDB的memory_limit設定，並選擇了SNAPPY壓縮演算法以最佳化寫入速度：

duckdb -s "SET memory_limit='100MB';
COPY (
SELECT * EXCLUDE (player, wikidata_id)
REPLACE (
    cast(strptime(ranking_date::VARCHAR, '%Y%m%d') AS DATE) AS ranking_date,
    cast(strptime(dob, '%Y%m%d') AS DATE) AS dob
)
FROM 'atp/atp_rankings_*.csv' rankings
JOIN (
    FROM 'atp/atp_players.csv'
) players ON players.player_id = rankings.player
)
TO 'atp_rankings.parquet'
(FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);"

執行後生成的Parquet檔案大小約為36MB：

du -h *.parquet
36M atp_rankings.parquet

此圖示說明瞭CSV到Parquet轉換流程及其最佳化的主要步驟：

@startuml
skinparam backgroundColor #FEFEFE
skinparam defaultTextAlignment center
skinparam rectangleBackgroundColor #F5F5F5
skinparam rectangleBorderColor #333333
skinparam arrowColor #333333

title 此圖示說明瞭CSV到Parquet轉換流程及其最佳化的主要步驟：

rectangle "讀取與探索" as node1
rectangle "聯合查詢" as node2
rectangle "匯出為Parquet" as node3
rectangle "壓縮與最佳化" as node4

node1 --> node2
node2 --> node3
node3 --> node4

@enduml

此圖示詳細展示了整個流程，從初始的CSV資料讀取，到最終生成最佳化的Parquet檔案的過程。

玄貓 BlackCat

技術愛好者，專注於分享程式開發、雲端技術與 AI 應用的心得體會。