DuckDB 提供了便捷的工具和技巧，能有效查詢和分析 Parquet 和 SQLite 資料函式庫。藉由 parquet_schema 函式，可以深入瞭解 Parquet 檔案的內部結構描述，包含欄位名稱、資料型別、邏輯型別等資訊，方便後續資料處理和分析。針對 SQLite 資料函式庫，DuckDB 提供了 sqlite_scan 指令，搭配 CREATE VIEW 陳述式及資料型別轉換技巧，能有效解決 SQLite 弱型別系統與 DuckDB 強型別系統之間的資料型別轉換問題，確保查詢結果的正確性。此外，文章也提供了一些效能最佳化策略，例如將 64 位元整數轉換為 32 位元整數以節省儲存空間和提升查詢效率。
分析與查詢 Parquet 檔案
Parquet 檔案不僅廣泛用於資料處理流程中，也能作為 DuckDB 中查詢的優秀資料來源。相較於 CSV 或 JSON 檔案，Parquet 檔案更接近資料函式庫，因為它們在元資料中提供了結構描述。因此，即使你沒有建立 atp_rankings.parquet 檔案，而是從別人那裡接收到的，也能輕鬆查詢。
取得 Parquet 檔案的結構描述
如果你只對 Parquet 檔案中的欄位名稱和型別感興趣，可以使用 DESCRIBE 子句來查詢，就像對其他支援的資料來源一樣：
DESCRIBE FROM 'atp/atp_rankings.parquet';
查詢結果分析
查詢結果如下所示。請注意 ranking_date 和 dob 欄位，它們的型別都是 DATE，這表明在上一節中的型別轉換是成功的：
┌──────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├──────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ ranking_date │ DATE │ YES │ │ │ │
│ rank │ BIGINT │ YES │ │ │ │
│ points │ BIGINT │ YES │ │ │ │
│ player_id │ BIGINT │ YES │ │ │ │
│ name_first │ VARCHAR │ YES │ │ │ │
│ name_last │ VARCHAR │ YES │ │ │ │
│ hand │ VARCHAR │ YES │ │ │ │
│ dob │ DATE │ YES │ │ │ │
│ ioc │ VARCHAR │ YES │ │ │ │
│ height │ BIGINT │ YES │ │ │ │
├──────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 10 rows 6 columns │
└────────────────────────────────────────────────────────────────────┘
這個結構描述足以讓你查詢和分析資料。如果你剛剛使用 DuckDB 建立了這個檔案，你可能會想要了解更多。Parquet 檔案只有少數幾種實體型別（Boolean、不同大小的數字和位元組陣列），需要將這些型別轉換為更高層級的型別。
使用 parquet_schema 函式查詢內部結構描述
parquet_schema 函式可以用來查詢儲存在 Parquet 檔案中的內部結構描述。這可以讓我們瞭解哪些欄位可以使用現有的實體 Parquet 資料型別，以及哪些欄位需要轉換。首先，使用 DESCRIBE 子句來瞭解 parquet_schema 函式傳回的欄位：
DESCRIBE FROM parquet_schema('atp/atp_rankings.parquet');
parquet_schema 函式結果分析
結果如下所示：
┌─────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│ column_name │ column_type │ null │ key │ default │ extra │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├─────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ file_name │ VARCHAR │ YES │ │ │ │
│ name │ VARCHAR │ YES │ │ │ │
│ type │ VARCHAR │ YES │ │ │ │
│ type_length │ VARCHAR │ YES │ │ │ │
│ repetition_type │ VARCHAR │ YES │ │ │ │
│ num_children │ BIGINT │ YES │ │ │ │
│ converted_type │ VARCHAR │ YES │ │ │ │
│ scale │ BIGINT │ YES │ │ │ │
│ precision │ BIGINT │ YES │ | │
│ field_id | BIGINT | YES | | | |
| logical_type | VARCHAR | YES | | | |
├─────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 11 rows 6 columns │
└───────────────────────────────────────────────────────────────────────┘
最有趣的欄位是 name 和 type，因此可以寫一個查詢只傳回這些值：
SELECT name, type, converted_type, logical_type
FROM parquet_schema('atp/atp_rankings.parquet');
更新後的查詢結果
結果如下所示：
┌───────────────┬────────────┬────────────────┬──────────────┐
│ name | type | converted_type | logical_type |
| varchar | varchar | varchar | varchar |
├───────────────┼────────────┼────────────────┼──────────────┤
│ duckdb_schema | | | |
| ranking_date | INT32 | DATE | |
| rank | INT64 | INT_64 | |
| points | INT64 | INT_64 | |
| player_id | INT64 | INT_64 | |
| name_first | BYTE_ARRAY | UTF8 | |
| name_last | BYTE_ARRAY | UTF8 | |
| hand | BYTE_ARRAY | UTF8 | |
| dob | INT32 | DATE | |
內容解密：
parquet_schema函式：此函式用於檢視 Parquet 檔案的內部結構描述，包含欄位名稱、資料型別等資訊。DESCRIBE子句：用於檢視 Parquet 檔案或parquet_schema函式輸出的欄位資訊。SELECT陳述式：從parquet_schema的結果中選擇特定的欄位，如name、type、converted_type和logical_type，以便進一步分析。name和type欄位：這兩個欄位提供了每個欄位的名稱和在 Parquet 中的儲存型別，有助於理解資料的儲存方式和可能的轉換需求。
分析與查詢Parquet檔案
在處理和分析資料時，瞭解Parquet檔案的結構和內容是至關重要的。Parquet是一種列式儲存格式，廣泛用於大資料處理和分析。它提供了高效的壓縮和編碼機制，使得資料查詢和分析更加快速和高效。
檢視Parquet檔案的後設資料
要檢視Parquet檔案的後設資料，可以使用DuckDB提供的parquet_schema函式。這個函式能夠顯示Parquet檔案的結構，包括欄位名稱、資料型別等。
FROM parquet_schema('atp/atp_rankings.parquet');
輸出的結果如下：
┌───────────────┬────────────┬────────────────┬──────────────┐
│ column_name │ data_type │ type │ logical_type │
├───────────────┼────────────┼────────────────┼──────────────┤
│ ranking_date │ DATE │ INT32 │ DATE │
│ rank │ BIGINT │ INT64 │ │
│ points │ BIGINT │ INT64 │ │
│ player_id │ BIGINT │ INT64 │ │
│ ... │ ... │ ... │ ... │
└───────────────┴────────────┴────────────────┴──────────────┘
從結果中可以看出，ranking_date欄位被儲存為INT32型別，但其邏輯型別是DATE，表示它應該被當作日期處理。其他欄位如rank、points和player_id則被儲存為INT64型別。
內容解密：
column_name：欄位名稱。data_type：DuckDB中對應的資料型別。type：Parquet檔案中實際使用的儲存型別。logical_type：欄位的邏輯型別，用於描述欄位的實際意義。
查詢Parquet檔案的內容
可以使用DuckDB的SQL語法直接查詢Parquet檔案的內容。例如，查詢最大排名、最大積分、最大玩家ID和最大身高：
SELECT max(rank), max(points), max(player_id), max(height)
FROM 'atp/atp_rankings.parquet';
輸出的結果如下：
┌───────────┬─────────────┬────────────────┬─────────────┐
│ max(rank) │ max(points) │ max(player_id) │ max(height) │
│ int64 │ int64 │ int64 │ int64 │
├───────────┼─────────────┼────────────────┼─────────────┤
│ 2271 │ 16950 │ 211767 │ 211 │
└───────────┴─────────────┴────────────────┴─────────────┘
這些最大值表明，實際上並不需要使用64位整數來儲存這些欄位的值。因此，可以考慮在匯出到Parquet格式之前，將這些欄位轉換為INT32型別，以最佳化儲存空間和查詢效率。
內容解密：
- 查詢結果顯示了各個欄位的最大值。
- 這些最大值遠小於64位整數的最大值，表明可以使用32位整數來儲存。
使用parquet_metadata函式檢視詳細資訊
DuckDB的parquet_metadata函式能夠提供Parquet檔案的詳細後設資料，包括每個欄位在每個row group中的統計資訊。
.mode line
FROM parquet_metadata('atp/atp_rankings.parquet')
LIMIT 1;
輸出的結果如下：
file_name = atp/atp_rankings.parquet
row_group_id = 0
row_group_num_rows = 20726
...
stats_min = 1973-08-27
stats_max = 1979-12-26
...
這個結果顯示了第一個row group中ranking_date欄位的最小值和最大值。DuckDB可以利用這些統計資訊來最佳化查詢，例如在查詢特定日期範圍內的資料時，可以跳過不相關的row group。
內容解密：
stats_min和stats_max提供了欄位在該row group中的最小值和最大值。- 這些統計資訊對於查詢最佳化至關重要。
查詢SQLite和其他資料函式庫
DuckDB支援查詢SQLite和其他資料函式庫。首先，需要安裝和載入SQLite擴充套件：
INSTALL sqlite;
LOAD sqlite;
然後，可以將SQLite資料函式庫附加到DuckDB，並查詢其中的表格：
ATTACH 'database.sqlite' AS fifa (TYPE sqlite);
USE fifa;
PRAGMA show_tables;
輸出的結果如下：
┌───────────────────┐
│ name │
│ varchar │
├───────────────────┤
│ Country │
│ League │
│ Match │
│ Player │
│ ... │
└───────────────────┘
這樣就可以像查詢本地表格一樣查詢SQLite資料函式庫中的資料。
內容解密：
- 使用
ATTACH陳述式將SQLite資料函式庫附加到DuckDB。 - 使用
PRAGMA show_tables;列出資料函式庫中的所有表格。
處理資料型別不匹配的問題
在查詢SQLite資料函式庫時，可能會遇到資料型別不匹配的問題。例如，某個欄位被宣告為整數，但實際上包含了浮點數值。需要根據具體情況進行型別轉換或處理，以確保查詢的正確性。
內容解密：
- 資料型別不匹配是常見的問題，需要仔細檢查和處理。
- 可以使用型別轉換函式來解決資料型別不匹配的問題。
總之，DuckDB提供了強大的功能來處理和分析各種資料來源，包括Parquet檔案和SQLite資料函式庫。透過瞭解和使用DuckDB的各種功能，可以有效地提高資料處理和分析的效率。
查詢SQLite資料函式庫的挑戰與解決方案
在探索資料的過程中，我們遇到了使用DuckDB查詢SQLite資料函式庫的挑戰。由於DuckDB是強型別資料函式庫系統，而SQLite是弱型別系統，這導致了在資料型別轉換上的問題。
問題的根源
當我們嘗試查詢SQLite資料函式庫中的Player表格時，發現height欄位的資料型別被錯誤地識別為BIGINT，儘管它包含了浮點數值。這是因為SQLite的弱型別系統允許在INT欄位中儲存浮點數值，而DuckDB則嚴格遵守資料型別。
解決方案
為瞭解決這個問題，我們手動建立了Player檢視，並將所有SQLite欄位轉換為VARCHAR型別，以避免轉換錯誤。
- 首先，我們分離了SQLite資料函式庫：
USE memory;
DETACH fifa;
- 然後，我們設定全域變數
sqlite_all_varchar為true，以確保所有SQLite欄位被轉換為VARCHAR型別：
SET GLOBAL sqlite_all_varchar=true;
- 接下來，我們使用
sqlite_scan指令掃描Player表格，並手動將每個欄位轉換為正確的資料型別：
USE main;
CREATE OR REPLACE VIEW Player AS
SELECT * REPLACE (
id :: BIGINT AS id,
player_api_id :: BIGINT AS player_api_id,
player_fifa_api_id :: BIGINT AS player_fifa_api_id,
birthday :: DATE AS birthday,
height :: FLOAT AS height,
weight :: FLOAT AS weight
)
FROM sqlite_scan('database.sqlite', 'Player');
程式碼解析：
CREATE OR REPLACE VIEW Player AS：建立或替換名為Player的檢視。SELECT * REPLACE (...) FROM sqlite_scan('database.sqlite', 'Player')：掃描SQLite資料函式庫中的Player表格，並將欄位轉換為正確的資料型別。id :: BIGINT AS id、height :: FLOAT AS height等：將對應欄位轉換為正確的資料型別。
驗證結果
查詢新的Player檢視，我們得到了正確的結果：
SELECT * EXCLUDE player_fifa_api_id
FROM Player
LIMIT 5;
結果顯示，height和weight欄位現在正確地被識別為浮點數值。
後續步驟
在成功解決Player檢視的問題後，我們將sqlite_all_varchar設定回false，並手動建立其他檢視，例如Player_Attributes、Country、League、Match、Team和Team_Attributes。
SET GLOBAL sqlite_all_varchar=false;
CREATE OR REPLACE VIEW Player_Attributes AS
SELECT *
FROM sqlite_scan('database.sqlite', 'Player_Attributes');
-- 建立其他檢視...
查詢範例
最後，我們可以執行查詢來找出排名最高的球員：
SELECT player_name, arg_max(overall_rating, date) AS overall_rating
FROM Player
JOIN Player_Attributes PA ON PA.player_api_id = Player.player_api_id
WHERE overall_rating IS NOT NULL
GROUP BY ALL
ORDER BY overall_rating DESC, player_name
LIMIT 10;
結果：
查詢結果顯示了排名前10的球員及其最高評分。
其他擴充套件功能
DuckDB還支援其他擴充套件功能，例如Postgres擴充套件，可以用來查詢Postgres資料函式庫。安裝和載入Postgres擴充套件後，可以使用ATTACH指令連線到Postgres資料函式庫，並查詢其中的表格。
INSTALL postgres;
LOAD postgres;
同樣地，spatial擴充套件可以用來讀取Excel檔案。安裝和載入spatial擴充套件後，可以使用特定的指令來讀取Excel檔案。
INSTALL spatial;
LOAD spatial;