DuckDB資料匯出入：CSV與Parquet檔案實戰

– 8 分鐘閱讀

DuckDB 作為新興的分析型資料函式庫，在資料處理效能上表現出色。本文除了介紹 DuckDB 如何有效處理 CSV 與 Parquet 檔案的匯入與匯出，也涵蓋了 Excel 檔案的處理技巧。透過 Python 與 SQL 程式碼範例，示範如何使用 DuckDB 的 COPY 指令與相關函式，包含設定欄位分隔符號、標題列、限制資料筆數等。此外，針對 Parquet 檔案的 Column-based 特性，說明其在資料分析上的優勢，並提供 DuckDB 建立表格、讀取、載入及匯出 Parquet 檔案的實務操作。最後，針對 Excel 檔案處理，說明如何使用 spatial extension 讀取 Excel 工作表，並透過環境變數控制欄位名稱與資料型別，提供更全面的 DuckDB 資料處理。

DuckDB資料匯出入：CSV與Parquet實戰

在資料分析的世界裡，DuckDB以其高效能和便捷性成為了資料科學家的新寵。除了基本的資料函式庫操作，DuckDB在處理不同格式的資料檔案方面也表現出色。本文將探討如何使用DuckDB匯入和匯出CSV及Parquet檔案，助你輕鬆應對各種資料處理情境。

CSV檔案匯出技巧：從DuckDB到通用格式

DuckDB不僅能輕鬆匯入CSV檔案，還能將資料函式庫中的資料匯出為CSV格式，方便與其他系統或工具整合。

以下是如何將DuckDB資料函式庫中的資料匯出到CSV檔案的範例：

import duckdb
# 建立 DuckDB 連線
conn = duckdb.connect()

# 假設 df_airlines 是一個 pandas DataFrame
# 將 DataFrame 註冊為 DuckDB 的一個表格
conn.register("airlines", df_airlines)

# 將 airports 表格中的 IATA_CODE, LATITUDE, LONGITUDE 欄位匯出到 CSV 檔案
conn.execute("""
    COPY
    (SELECT IATA_CODE, LATITUDE, LONGITUDE FROM airports)
    TO
    'airports_location.csv' WITH (HEADER 1, DELIMITER ',');
""")

內容解密：

  1. COPY ... TO ...: 這是 DuckDB 中用於匯出資料的語法。
  2. (SELECT IATA_CODE, LATITUDE, LONGITUDE FROM airports): 指定要匯出的資料，這裡選擇了 airports 表格中的三個欄位。
  3. 'airports_location.csv': 指定匯出檔案的名稱。
  4. WITH (HEADER 1, DELIMITER ','): 設定 CSV 檔案的格式，HEADER 1 表示包含標頭列（欄位名稱），DELIMITER ',' 表示使用逗號作為分隔符。

執行上述程式碼後，將會產生一個名為 airports_location.csv 的CSV檔案，其中包含 IATA_CODE, LATITUDE, LONGITUDE 三個欄位，以及 airports 表格中的相應資料。

若只想匯出部分資料，可以使用 LIMIT 語法限制匯出的資料筆數：

conn.execute("""
    COPY
    (SELECT
        IATA_CODE, LATITUDE, LONGITUDE
    FROM 'airports.csv'
    LIMIT 10)
    TO
    'airports_location.csv' WITH (HEADER 1, DELIMITER ',');
""")

內容解密：

  • LIMIT 10: 表示只匯出前 10 筆資料。

玄貓提醒您，完成資料函式庫操作後，記得關閉連線：

conn.close()

Parquet檔案：DuckDB資料分析的效率加速器

Parquet 是一種專為快速資料處理設計的檔案格式，尤其適合複雜資料的分析。它以Column-based儲存資料，能更有效地壓縮資料，並在提取特定欄位時提供更高的效率。

為什麼Parquet檔案格式在資料分析中越來越受歡迎？

傳統的CSV檔案使用Row-based儲存，當資料量大時，讀取效率會顯著下降。而Parquet檔案則以Column-based儲存，將相同資料型別的資料放在一起，不僅壓縮效率更高，而與在查詢特定欄位時，只需讀取相關的資料，大大提升了效率。

以下是如何將CSV檔案轉換為Parquet檔案的範例：

import pandas as pd

# 讀取 CSV 檔案為 pandas DataFrame
df_airports = pd.read_csv("airports.csv")

# 將 DataFrame 儲存為 Parquet 檔案
df_airports.to_parquet('airports.parquet', engine='fastparquet')

內容解密：

  1. import pandas as pd: 匯入 pandas 函式庫，用於讀取和處理 CSV 檔案。
  2. pd.read_csv("airports.csv"): 使用 pandas 讀取名為 “airports.csv” 的 CSV 檔案，並將資料儲存到名為 df_airports 的 DataFrame 中。
  3. df_airports.to_parquet('airports.parquet', engine='fastparquet'): 將 DataFrame 儲存為 Parquet 檔案。
    • 'airports.parquet'：指定 Parquet 檔案的名稱。
    • engine='fastparquet'：指定使用的 Parquet 引擎。fastparquet 是一個高效的 Python 函式庫，用於讀寫 Parquet 檔案。

DuckDB讀取Parquet檔案：快速載入，高效分析

DuckDB可以輕鬆讀取Parquet檔案，並將資料載入到資料函式庫中進行分析。

以下是如何將Parquet檔案載入到DuckDB資料函式庫的範例：

import duckdb

# 建立 DuckDB 連線
conn = duckdb.connect()

# 建立名為 airports 的表格，並從 Parquet 檔案讀取前 100 筆資料
conn.execute("""
    CREATE TABLE airports
    as
    SELECT * FROM read_parquet('airports.parquet')
    LIMIT 100
""")

內容解密：

  1. CREATE TABLE airports AS ...: 建立一個名為 airports 的新表格，並將查詢結果插入到該表格中。
  2. SELECT * FROM read_parquet('airports.parquet'): 使用 read_parquet() 函式讀取名為 airports.parquet 的 Parquet 檔案，並選擇所有欄位。
  3. LIMIT 100: 限制只讀取前 100 筆資料。

若要載入Parquet檔案中的最後100筆資料，可以先將資料依照第一欄排序（降序），然後再讀取前100筆：

conn.execute("""
    INSERT INTO airports
    SELECT * FROM read_parquet('airports.parquet')
    ORDER BY 1 DESC
    LIMIT 100
""")

內容解密：

  • ORDER BY 1 DESC: 依照第一欄（IATA_CODE）進行降序排序。

若要將Parquet檔案載入到已存在的表格中，可以使用 COPY FROM 語法：

conn.execute("""
    COPY airports
    FROM 'airports.parquet' (FORMAT PARQUET);
""")

DuckDB匯出Parquet檔案：高效儲存，方便分享

DuckDB也能將資料函式庫中的資料匯出為Parquet檔案，方便與其他系統或工具分享。

以下是如何將DuckDB資料函式庫中的資料匯出到Parquet檔案的範例：

conn.execute("""
    COPY airports
    TO
    'airports_all.parquet' (FORMAT PARQUET);
""")

內容解密：

  • FORMAT PARQUET: 指定匯出檔案的格式為 Parquet。

若只想匯出部分資料，可以使用 LIMIT 語法限制匯出的資料筆數：

conn.execute("""
    COPY
    (SELECT * FROM airports LIMIT 100)
    TO
    'airports_100.parquet' (FORMAT PARQUET);
""")

總結來說，DuckDB提供了強大的CSV和Parquet檔案匯入匯出功能，讓資料科學家能更靈活地處理各種資料格式，並充分利用Column-based儲存的優勢，提升資料分析的效率。無論是資料的整合、轉換還是分享，DuckDB都能成為你得力的助手。


## 玄貓解密：DuckDB匯出Parquet資料與Excel檔案處理實戰

Parquet格式因其高效的儲存、快速的查詢能力，以及與大資料工具的互通性，在雲端儲存、資料湖、分析工作負載和機器學習流程中成為首選。Parquet針對欄位資料進行最佳化，並支援壓縮，使其成為理想的選擇。

學會DuckDB中操作Parquet檔案後，接下來玄貓將帶領大家探索最受歡迎的資料檔案格式：Excel檔案。

### Excel檔案處理：玄貓實戰經驗

Excel是一款功能廣泛的試算表應用程式，被廣泛應用於各行各業。無論你是否為程式設計師，都有可能以某種方式使用過Excel。本文玄貓將分享如何在DuckDB中處理Excel檔案的實戰經驗。

**玄貓小提示：** 本文的示範將使用一個包含兩個工作表（airports和airlines）的Excel檔案（如圖2-15所示）。airports工作表的內容來自airports.csv檔案，而airlines工作表的內容來自airlines.csv。

**圖 2-15. Excel試算表的內容**

**注意：** airlines工作表沒有標題。這是為了讓玄貓可以學習如何在將工作表載入到DuckDB的表格中時手動指定標題。

### 玄貓教你載入Excel檔案

要從Excel試算表載入資料，需要使用spatial擴充功能，該擴充功能提供對DuckDB中地理空間資料處理的支援。

DuckDB支援多種擴充功能：

*   **httpfs：** 支援透過HTTP或雲端儲存讀寫檔案。
*   **icu：** 透過ICU（Unicode國際元件）函式庫提供進階字串處理和國際化功能。
*   **sqlite：** 提供讀取和查詢SQLite資料函式庫檔案的功能。
*   **inet：** 增加對處理IP位址和網路資料的支援。

要安裝這些擴充功能，可以使用INSTALL關鍵字，這是一個用於下載和安裝擴充功能的SQL指令。

首先，讓我們將一個Excel工作表載入到DuckDB資料函式庫中。以下程式碼片段將airports工作表載入到一個名為airports的DuckDB表格中：

```python
import duckdb
conn = duckdb.connect()
conn.execute('INSTALL spatial')
conn.execute('LOAD spatial')
conn.execute('''
CREATE TABLE airports
as
SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airports');
''')
display(conn.execute('SELECT * FROM airports').df())

玄貓解密：

  • 必須先安裝並載入spatial擴充功能，才能匯入Excel資料。這只需要做一次，擴充功能會被記住，直到DuckDB被解除安裝。
  • st_read()函式從Excel試算表讀取資料。要載入的工作表透過layer引數指定。DuckDB目前不支援讀取受密碼保護的Excel試算表。如果你的檔案受到密碼保護，你需要先解密。

圖 2-16. 從Excel試算表載入的airports表格的內容

如你所見，工作表第一列的值會自動被偵測並用作表格的欄位名稱。然而，這種行為可以透過環境變數OGR_XLSX_HEADERS來控制，它是GDAL/OGR函式庫的一部分，DuckDB使用它來讀取Excel檔案。

如果你不希望使用第一列的欄位作為表格的欄位名稱，請將OGR_XLSX_HEADERS環境變數設定為DISABLE：

import os
os.environ['OGR_XLSX_HEADERS'] = 'DISABLE'

這樣做之後，預設的欄位名稱將會是Field1、Field2等等。

然而，如果你想強制使用第一列的欄位作為表格的欄位名稱，請將環境變數設定為FORCE：

os.environ['OGR_XLSX_HEADERS'] = 'FORCE'

環境變數的預設值為AUTO，這表示行為是自動的，允許Excel檔案的OGR驅動程式根據其內容決定是否將第一列視為欄位標題：

os.environ['OGR_XLSX_HEADERS'] = 'AUTO'

現在讓我們嘗試將airlines工作表載入到DuckDB中：

conn.execute('''
CREATE TABLE airlines
AS
SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airlines');
''')
display(conn.execute('SELECT * FROM airlines').df())

這次，觀察到st_read()函式已偵測到沒有可用作表格欄位名稱的欄位名稱。因此，預設的欄位名稱為Field1和Field2（見圖2-17）。

圖 2-17. 欄位名稱設定為預設值

為了確保你的表格具有自訂欄位名稱，你可以先建立一個具有所需欄位名稱和資料型別的表格，然後使用INSERT陳述式從Excel試算表載入資料：

conn = duckdb.connect()
conn.execute('INSTALL spatial')
conn.execute('LOAD spatial')
conn.execute('''
CREATE TABLE airlines (
IATA_CODE STRING,
AIRLINES STRING
);
INSERT INTO airlines
SELECT * FROM st_read('airports_and_airlines.xlsx', layer='airlines');
''')
display(conn.execute('SELECT * FROM airlines').df())

玄貓提醒： 請注意在DuckDB中使用INSERT陳述式的效能影響。通常，盡量避免使用INSERT逐個插入列，特別是如果你的資料集很大。另外，請注意DuckDB中的COPY陳述式不適用於Excel檔案。

圖 2-18. 具有正確欄位名稱的表格

另一個可以在載入Excel資料時使用的環境變數是OGR_XLSX_FIELD_TYPES。預設情況下，在解析Excel試算表時，DuckDB會自動偵測檔案中的資料型別。如果你想強制所有資料型別為字串，請將此環境變數設定為STRING：

os.environ['OGR_XLSX_FIELD_TYPES'] = 'STRING' # 預設為AUTO

總結來說，玄貓在本篇文章中分享瞭如何在DuckDB中處理Excel檔案的實戰經驗，包括載入Excel檔案、控制欄位名稱和資料型別等。這些技巧對於需要在DuckDB中處理Excel資料的開發者來說非常實用。

玄貓

玄貓

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