DuckDB 提供 EXPLAIN 和 EXPLAIN ANALYZE 指令，能幫助開發者深入理解查詢計劃的執行過程與效能瓶頸。EXPLAIN 指令顯示查詢的邏輯執行計劃，包含運算子號樹狀結構與相關資訊；EXPLAIN ANALYZE 則更進一步，實際執行查詢並提供執行時間、資料量等統計資料，方便 pinpoint 效能問題。此外，DuckDB 支援將資料以 Parquet 格式匯出，大幅提升儲存效率與查詢速度。文章也示範如何設定多執行緒匯出，進一步最佳化匯出效能。對於處理巨量資料，DuckDB 能直接讀取 S3 上的 Parquet 檔案，並利用其高效的查詢引擎進行分析，無需將資料載入資料函式庫，有效降低網路傳輸成本。文章也詳細說明如何設定 S3 存取憑證、自動推斷檔案型別、探索 Parquet 結構描述以及建立檢視表，簡化查詢流程。
查詢規劃與執行視覺化：使用 EXPLAIN 和 EXPLAIN ANALYZE
在資料函式庫查詢最佳化過程中，瞭解查詢規劃器（query planner）如何轉換查詢陳述式至可執行的運算子號樹（operator tree）是非常重要的。DuckDB 提供了 EXPLAIN 和 EXPLAIN ANALYZE 兩種指令來幫助使用者視覺化查詢計劃並分析查詢效能。
使用 EXPLAIN 檢視查詢計劃
EXPLAIN 指令可以顯示查詢陳述式被轉換後的運算子號樹。例如，對於以下的 SQL 查詢：
EXPLAIN
SELECT
year(CreationDate) AS year,
count(*) AS count,
round(avg(ViewCount)) AS avg_view_count,
max(AnswerCount) AS max_answer_count
FROM
posts
GROUP BY
year
ORDER BY
year DESC
LIMIT 10;
執行結果會顯示查詢計劃的樹狀結構，包含各個運算子號及其相關資訊，如下所示：
┌───────────────────────────┐
│ TOP_N │
│ Top 10 │
│ year(posts.CreationDate)│
│ DESC │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PROJECTION │
│ 0 │
│ count_star() │
│ round(avg(ViewCount)) │
│ max(AnswerCount) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PERFECT_HASH_GROUP_BY │
│ #0 │
│ count_star() │
│ avg(#1) │
│ max(#2) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ posts │
│ CreationDate │
│ ViewCount │
│ AnswerCount │
│ EC: 81865857 │
└───────────────────────────┘
內容解密：
TOP_N運算子號：負責執行ORDER BY和LIMIT操作，限制輸出結果為前 10 筆資料並按照年份降冪排序。PROJECTION運算子號：進行欄位投影，將需要的欄位選取出來並進行計算，如count(*)、round(avg(ViewCount))和max(AnswerCount)。PERFECT_HASH_GROUP_BY運算子號：執行GROUP BY操作，利用雜湊演算法對資料進行分組並計算彙總值。SEQ_SCAN運算子號：進行順序掃描，讀取posts表格的資料並提取所需的欄位，如CreationDate、ViewCount和AnswerCount。
使用 EXPLAIN ANALYZE 分析查詢效能
EXPLAIN ANALYZE 不僅會顯示查詢計劃，還會實際執行查詢並提供執行時間、資源使用情況等詳細資訊。例如：
EXPLAIN ANALYZE
SELECT
year(CreationDate) AS year,
count(*) AS count,
round(avg(ViewCount)) AS avg_view_count,
max(AnswerCount) AS max_answer_count
FROM
posts
GROUP BY
year
ORDER BY
year DESC
LIMIT 10;
執行結果如下：
┌───────────────────────────┐
│ EXPLAIN_ANALYZE │
│ 0 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ TOP_N │
│ Top 10 │
│ year(posts.CreationDate)│
│ DESC │
│ 10 │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ PERFECT_HASH_GROUP_BY │
│ #0 │
│ count_star() │
│ avg(#1) │
│ max(#2) │
│ 16 │
│ (0.55s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ SEQ_SCAN │
│ posts │
│ CreationDate │
│ ViewCount │
│ AnswerCount │
│ EC: 81865857 │
│ 58329356 │
│ (0.98s) │
└───────────────────────────┘
內容解密：
EXPLAIN_ANALYZE總覽：顯示整體查詢的執行時間和結果。TOP_N詳細資訊：顯示LIMIT操作的執行時間和輸出結果數量。PERFECT_HASH_GROUP_BY詳細資訊：顯示分組操作的執行時間和處理的資料量。SEQ_SCAN詳細資訊：顯示順序掃描操作的執行時間和掃描的資料量。
將 Stack Overflow 資料匯出至 Parquet 檔案
DuckDB 支援將資料表匯出至 Parquet 格式，以利於儲存和後續處理。Parquet 是一種列式儲存格式，具有較好的壓縮率和高效的讀取效能。
匯出範例
COPY (FROM users)
TO 'users.parquet'
(FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);
-- Run Time (s): real 10.582 user 62.737265 sys 65.422181
COPY (FROM posts)
TO 'posts.parquet'
(FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);
-- Run Time (s): real 57.314 user 409.517658 sys 334.606894
內容解密：
COPY命令：用於將資料表匯出至 Parquet 檔案。FORMAT PARQUET：指定輸出格式為 Parquet。CODEC 'SNAPPY'：使用 Snappy 壓縮演算法進行壓縮，以減少檔案大小。ROW_GROUP_SIZE 100000：設定每個 row group 的大小，以平衡壓縮率和讀取效能。
多執行緒匯出最佳化
為了提升匯出效能，可以使用多執行緒匯出功能。DuckDB 將會為每個執行緒建立一個獨立的 Parquet 檔案。
多執行緒匯出範例
-- 設定多執行緒匯出（範例）
SET threads TO 10;
COPY (FROM users)
TO 'users.parquet'
(FORMAT PARQUET, CODEC 'SNAPPY', ROW_GROUP_SIZE 100000);
-- Run Time (s): real 1.7 user ... sys ...
內容解密：
SET threads TO 10：設定 DuckDB 使用 10 個執行緒進行匯出操作。- 多執行緒匯出效益：可顯著提升匯出效能，特別是在處理大型資料表時。
10.3 將 Stack Overflow 資料匯出至 Parquet 格式
使用 DuckDB 將 Stack Overflow 資料匯出至 Parquet 格式，可以大幅提升資料讀取效率。首先，我們使用 COPY 命令將 users 表格匯出至 users.parquet 檔案。
COPY (
SELECT *
FROM users
ORDER BY LastAccessDate DESC
) TO 'users.parquet'
(FORMAT PARQUET, CODEC 'SNAPPY', PER_THREAD_OUTPUT TRUE);
內容解密：
COPY命令用於將資料匯出至檔案。SELECT * FROM users ORDER BY LastAccessDate DESC選取users表格的所有欄位，並依照LastAccessDate欄位進行降冪排序。TO 'users.parquet'指定匯出的檔案名稱和路徑。(FORMAT PARQUET, CODEC 'SNAPPY', PER_THREAD_OUTPUT TRUE)設定匯出格式為 Parquet，使用SNAPPY壓縮演算法，並啟用多執行緒輸出。
同樣地，我們也將其他表格匯出至對應的 Parquet 檔案。匯出完成後，我們可以在磁碟上看到以下檔案：
comments.parquet(6.9G)posts.parquet(4.0G)votes.parquet(2.2G)users.parquet(734M)badges.parquet(518M)post_links.parquet(164M)tags.parquet(1.6M)
值得注意的是，這些 Parquet 檔案也可以直接從 S3 bucket (s3://us-prd-motherduck-open-datasets/stackoverflow/parquet/2023-05/) 讀取。
比較從 CSV 和 Parquet 檔案讀取資料的效能
為了比較從 CSV 和 Parquet 檔案讀取資料的效能，我們分別使用 read_parquet 和 read_csv_auto 函式讀取 users 表格的資料列數。
SELECT count(*) FROM read_parquet('users.parquet');
這條查詢在不到一秒的時間內（0.008s）傳回結果：19942787。
SELECT count(*) FROM read_csv_auto('Users.csv.gz');
這條查詢需要約 7 秒的時間傳回相同的結果。這表明從 Parquet 檔案讀取資料列數的速度比從 CSV 檔案快約 1000 倍。
內容解密：
read_parquet和read_csv_auto函式分別用於讀取 Parquet 和 CSV 檔案。- 由於 Parquet 檔案具有元資料，因此可以快速傳回資料列數，而 CSV 檔案則需要掃描整個檔案。
將整個資料函式庫匯出至 Parquet 檔案
除了個別匯出表格之外，DuckDB 也支援將整個資料函式庫匯出至指定的資料夾。
EXPORT DATABASE 'target_directory'
(FORMAT PARQUET);
這將在指定的資料夾中建立對應的 Parquet 檔案，以及兩個 SQL 檔案：schema.sql 和 load.sql。schema.sql 用於建立資料函式庫結構，而 load.sql 用於載入資料。
內容解密：
EXPORT DATABASE命令用於將整個資料函式庫匯出至指定的資料夾。(FORMAT PARQUET)指定匯出格式為 Parquet。
載入 Parquet 資料
當需要載入之前匯出的 Parquet 資料時，可以使用以下命令：
IMPORT DATABASE 'source_directory';
這將執行 schema.sql 中的指令建立資料函式庫結構，並執行 load.sql 中的指令載入 Parquet 資料。
10.4 使用 Parquet 檔案探索紐約計程車資料集
紐約計程車資料集是一個非常適合用來測試資料函式庫系統對於大型資料處理能力的資料集。該資料集包含超過 17 億筆記錄，共計 175 個 Parquet 檔案，總大小約為 28 GB。
使用 DuckDB 分析 Parquet 資料
DuckDB 可以直接讀取 Parquet 檔案，並利用 predicate pushdown 和 projection pushdown 等技術最佳化查詢效能，而無需實際載入資料函式庫。
SELECT * FROM read_parquet('nyc_taxi_data.parquet');
內容解密：
read_parquet函式用於讀取 Parquet 檔案。- DuckDB 可以直接對 Parquet 檔案進行查詢，無需事先載入資料函式庫。
對於如此龐大的資料集，將查詢計算移至資料儲存位置可以大幅減少網路傳輸成本和延遲。因此，在雲端環境中，建議在靠近資料儲存位置的雲端例項上執行 DuckDB，或者使用 MotherDuck 等託管服務。
最佳化 DuckDB 存取 S3 上的 Parquet 檔案效能
在處理大量資料時，DuckDB 提供了一個強大的工具來高效存取和分析資料。本章節將探討如何組態 DuckDB 以存取 Amazon S3 上的 Parquet 檔案，並利用其高效能查詢能力進行資料分析。
設定 S3 存取憑證
若要存取 S3 上的私有 bucket，需要設定存取憑證。DuckDB 允許透過建立一個臨時或持久的 SECRET 來組態 S3 憑證，如下所示：
CREATE [PERSISTENT] SECRET (
TYPE S3,
KEY_ID 'AKIA...',
SECRET 'Sr8VSfK...',
REGION 'us-east-1'
);
內容解密：
CREATE [PERSISTENT] SECRET：建立一個 S3 型別的 SECRET，可以選擇是否持久化儲存。TYPE S3：指定 SECRET 的型別為 S3。KEY_ID和SECRET：提供用於存取 S3 bucket 的 AWS 存取金鑰 ID 和秘密存取金鑰。REGION：指定 S3 bucket 所屬的 AWS 區域。
為了能夠存取 S3 上的檔案，需要載入 httpfs 擴充套件：
INSTALL httpfs;
LOAD httpfs;
內容解密：
INSTALL httpfs;：安裝httpfs擴充套件，該擴充套件允許 DuckDB 存取 HTTP(S) 檔案，包括 S3。LOAD httpfs;：載入已安裝的httpfs擴充套件，使其可用於當前會話。
自動推斷檔案型別
DuckDB 可以自動推斷 Parquet 檔案的結構和內容。例如，計算一個 Parquet 檔案中的記錄數：
SELECT count(*)
FROM 's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_2022-06.parquet';
-- 結果：3,558,124 行，耗時 600 毫秒
內容解密：
SELECT count(*)：對指定的 Parquet 檔案執行計數查詢。FROM 's3://...': 指定要查詢的 Parquet 檔案路徑，DuckDB 自動處理 S3 存取。
這類別查詢能快速傳回結果，因為 DuckDB 利用了 Parquet 檔案的中繼資料。
探索 Parquet 結構描述
在查詢 Parquet 檔案之前，瞭解其結構描述是非常有用的。可以使用 parquet_schema 函式來檢視 Parquet 檔案的欄位名稱和型別：
FROM parquet_schema('s3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_2022-06.parquet')
SELECT name, type;
內容解密：
parquet_schema：傳回指定 Parquet 檔案的結構描述。SELECT name, type：從結構描述中選取名稱和型別欄位。
輸出結果顯示了 NYC Taxi 資料集中的欄位及其對應的資料型別，如 VendorID、tpep_pickup_datetime 等。
建立檢視表
為了簡化查詢，可以在多個 Parquet 檔案上建立一個檢視表（View）：
CREATE OR REPLACE VIEW allRidesView AS
SELECT *
FROM 's3://us-prd-md-duckdb-in-action/nyc-taxis/yellow_tripdata_202*.parquet';
內容解密：
CREATE OR REPLACE VIEW：建立或替換一個名為allRidesView的檢視表。SELECT * FROM 's3://...': 從匹配指定模式的多個 Parquet 檔案中選取所有欄位。
這個檢視表提供了一個簡潔的方式來查詢多個相關的 Parquet 檔案，而無需每次都指定完整的檔案路徑。
