DuckDB關聯式API與SQL查詢整合

– 9 分鐘閱讀

DuckDB 的關聯式 API 提供了類別似 SQL 的操作方式，讓 Python 資料處理與 SQL 查詢之間的轉換更加流暢。這允許開發者使用熟悉的 SQL 語法操作關聯式物件，如同處理一般的 SQL 資料表。文章將會逐步示範如何使用 DuckDB 執行 SQL 查詢，包含引數化查詢的應用，以提升查詢的安全性與可重用性，避免 SQL 注入風險。此外，DuckDB 與 Pandas DataFrame 的無縫整合，讓資料分析更加便捷。透過將 DataFrame 註冊到 DuckDB 連線，開發者可以直接使用 SQL 查詢 DataFrame，結合資料函式庫引擎的效能與 Pandas 的靈活性。

使用關聯式API與SQL查詢

DuckDB的關聯式API提供了一種靈活的方式來處理特定資料需求。關聯式物件可以像SQL表格一樣被處理，這使得在Python資料操作和SQL查詢之間實作了流暢的轉換。

SQL查詢範例

假設我們的目標是確定GDP每 capita超過$10,000的中等規模以上國家的數量。以下是實作此目標的過程：

con.sql("""
SELECT count(*)
FROM over_10m
WHERE "GDP ($ per capita)" > 10000
""")

這種查詢方式展示瞭如何使用SQL陳述式來操作關聯式物件。

引數化查詢

雖然DuckDB的關聯式API提供了豐富的功能，但在某些場景下，特別是涉及引數化查詢時，其內建功能可能會顯得侷限。引數化查詢使用預留位置在陳述式文字中，陳述式文字和實際值（引數）獨立傳遞給引擎。使用引數有幾個優點：使用者輸入應該始終作為引數傳遞，而不應直接放入陳述式文字中。如果直接將使用者輸入與陳述式片段拼接，就有可能遭受SQL注入攻擊。

引數化查詢範例

con.execute("""
SELECT count(*)
FROM over_10m
WHERE "GDP ($ per capita)" > $gdp
""", {
    "gdp": 10000
}).fetchone()

內容解密：

  1. con.execute()：執行SQL查詢，並支援引數化查詢。
  2. SELECT count(*) FROM over_10m：查詢over_10m表格中的記錄數。
  3. WHERE "GDP ($ per capita)" > $gdp：根據$gdp引數過濾GDP每capita大於指定值的記錄。
  4. {"gdp": 10000}：定義查詢引數，將$gdp設為10000。
  5. .fetchone()：取得查詢結果的第一筆記錄。

結果(20,)表示有20個國家符合條件。這種方式避免了字串拼接，提高了查詢的安全性和可重用性。

查詢Pandas DataFrame

DuckDB的強大之處在於其能夠與各種資料基礎設施工具的記憶體資料結構無縫整合。其中一個值得注意的工具是pandas，一個廣泛使用的開源資料分析函式庫。DuckDB和pandas的結合使得資料操作變得更加強大，既發揮了資料函式庫引擎的能力，又具備了資料操作函式庫的靈活性。

安裝與匯入必要的函式庫

首先，確保pandas已安裝在環境中：

pip install pandas

接下來，匯入pandas和DuckDB函式庫：

import duckdb
import pandas as pd

建立Pandas DataFrame

建立一個包含作者資訊的pandas DataFrame：

people = pd.DataFrame({
    "name": ["Michael Hunger", "Michael Simons", "Mark Needham"],
    "country": ["Germany", "Germany", "Great Britain"]
})

內容解密：

  1. pd.DataFrame()：建立一個pandas DataFrame。
  2. {"name": [...], "country": [...]}：定義DataFrame的列及其內容。

使用DuckDB查詢Pandas DataFrame

DuckDB可以直接與pandas DataFrame互動，將它們視為普通的關聯式物件。這意味著我們可以對people執行類別似SQL的查詢，就像操作一個常規的資料函式庫表格一樣：

con.sql("""
SELECT *
FROM people
WHERE country = 'Germany'
""")

內容解密：

  1. con.sql()：執行SQL查詢。
  2. SELECT * FROM people：查詢people DataFrame中的所有列。
  3. WHERE country = 'Germany'：過濾出國家為德國的記錄。

這種結合使用DuckDB和pandas的方式，使得資料分析和操作的流程更加高效和靈活。

與Python生態系統的整合

DuckDB能夠與Python生態系統無縫整合，使得資料分析變得更加方便。在本章中，我們將探討如何使用DuckDB查詢pandas DataFrames，以及如何建立使用者自訂函式（UDFs）。

查詢pandas DataFrames

我們可以使用DuckDB的SQL介面來查詢pandas DataFrames。首先，我們需要建立一個DuckDB連線，並將DataFrame註冊到該連線中。然後，我們可以使用SQL陳述式來查詢DataFrame。

import pandas as pd
import duckdb

# 建立一個pandas DataFrame
data = {'name': ['Mark', 'Michael Hunger', 'Michael Simons'],
        'country': ['Great Britain', 'Germany', 'Germany']}
people = pd.DataFrame(data)

# 建立一個DuckDB連線
con = duckdb.connect()

# 將DataFrame註冊到DuckDB連線中
con.register('people', people)

# 使用SQL陳述式查詢DataFrame
result = con.sql("""
SELECT *
FROM people
WHERE country = 'Germany'
""")

# 顯示查詢結果
print(result)

內容解密：

  • 我們首先建立了一個pandas DataFrame，包含了人名和國家資訊。
  • 然後，我們建立了一個DuckDB連線，並將DataFrame註冊到該連線中。
  • 使用SQL陳述式查詢DataFrame，篩選出國家為德國的人員。
  • 查詢結果顯示了符合條件的資料列。

查詢結果如下：

┌────────────────┬─────────┐
│ name           │ country │
│ varchar        │ varchar │
├────────────────┼─────────┤
│ Michael Hunger │ Germany │
│ Michael Simons │ Germany │
└────────────────┴─────────┘

我們也可以使用引數化查詢來查詢DataFrame。

params = {"country": "Germany"}
result = con.execute("""
SELECT *
FROM people
WHERE country <> $country
""", params).fetchdf()

print(result)

內容解密：

  • 我們定義了一個引數字典params，包含了國家引數。
  • 使用引數化查詢來查詢DataFrame，篩選出國家不是德國的人員。
  • 查詢結果以DataFrame的形式傳回。

查詢結果如下：

             name       country
0           Mark  Great Britain

使用者自訂函式（UDFs）

DuckDB允許使用者建立自訂函式（UDFs），以擴充套件SQL語言的功能。我們可以使用Python來定義UDFs，並將其註冊到DuckDB中。

def remove_spaces(field: str) -> str:
    if field:
        return field.strip()
    else:
        return field

con.create_function('remove_spaces', remove_spaces)

result = con.sql("""
SELECT function_name, function_type, parameters, parameter_types, return_type
from duckdb_functions()
where function_name = 'remove_spaces'
""")

print(result)

內容解密：

  • 我們定義了一個Python函式remove_spaces，用於去除字串前後的空格。
  • 將該函式註冊到DuckDB中，名稱為remove_spaces。
  • 查詢DuckDB的duckdb_functions表，以確認函式是否註冊成功。

查詢結果如下：

┌──────────────┬──────────────┬────────────┬─────────────────┬─────────────┐
│function_name │function_type │ parameters │ parameter_types │ return_type │
│ varchar      │ varchar      │ varchar[]  │ varchar[]       │ varchar     │
├──────────────┼──────────────┼────────────┼─────────────────┼─────────────┤
│remove_spaces │scalar        │ [col0]     │ [VARCHAR]       │ VARCHAR     │
└──────────────┴──────────────┴────────────┴─────────────────┴─────────────┘

現在，我們可以在SQL陳述式中使用remove_spaces函式。

result = con.sql("select length(remove_spaces(' foo '))")

print(result)

內容解密：

  • 我們在SQL陳述式中使用remove_spaces函式，去除字串前後的空格。
  • 使用length函式計算去除空格後的字串長度。

本章介紹瞭如何使用DuckDB與Python生態系統整合，包括查詢pandas DataFrames和建立使用者自訂函式（UDFs）。這些功能使得資料分析變得更加方便和靈活。

與 Python 生態系統整合

在與 Python 生態系統整合的過程中，明確指定資料型別對於確保程式碼的清晰度、避免潛在的型別推斷陷阱以及確保不同環境之間的一致性行為至關重要。為了重新定義我們的函式並明確指定型別，我們首先需要移除之前註冊的函式版本，以避免衝突：

con.remove_function('remove_spaces')

完成此操作後，我們現在可以重新註冊我們的函式，這次我們將明確定義資料型別。如以下程式碼片段所示，函式的引數型別和傳回型別被明確指定：

from duckdb.typing import *

con.create_function(
    'remove_spaces',
    remove_spaces,
    [(VARCHAR)],
    VARCHAR
)

內容解密：

  1. con.create_function 用於在 DuckDB 中建立一個新的函式。
  2. 'remove_spaces' 是函式的名稱，remove_spaces 是對應的 Python 函式。
  3. [(VARCHAR)] 指定了函式的輸入引數型別為 VARCHAR。
  4. VARCHAR 指定了函式的傳回型別為 VARCHAR。

明確定義這些型別可以作為一個清晰的合約，規定了函式應該如何被使用以及預期會傳回什麼，從而確保系統和開發者之間的同步。

測試自定義函式

接下來，讓我們使用這個函式來寫一個查詢，展示如果我們從 Region 列中移除空格會看到什麼。這個查詢做了兩件事：

  • 顯示原始的 Region 值及其字元長度（len1）
  • 展示清理後的 Region 值（使用 remove_spaces）及其字元長度（len2）
con.sql("""
SELECT DISTINCT Region, length(Region) AS len1,
remove_spaces(Region) AS cleanRegion,
length(cleanRegion) AS len2
FROM population
WHERE len1 BETWEEN 20 AND 30
LIMIT 3
""")

內容解密：

  1. SELECT DISTINCT Region 選擇了唯一的 Region 值。
  2. length(Region) AS len1 計算了原始 Region 值的字元長度。
  3. remove_spaces(Region) AS cleanRegion 使用 remove_spaces 函式清除了 Region 中的空格。
  4. length(cleanRegion) AS len2 計算了清理後 Region 值的字元長度。

檢視 len1 和 len2 之間的差異，可以立即顯現出我們的函式確實刪除了不必要的空格。

更新原始資料集

在確認我們的函式正常運作後，我們可以繼續更新原始資料集：

con.sql("""
UPDATE population
SET Region = remove_spaces(Region);
""")

內容解密：

  1. UPDATE population 更新了 population 表。
  2. SET Region = remove_spaces(Region) 使用 remove_spaces 函式清除了 Region 列中的空格。

處理區域資料差異

在處理來自不同地區的資料時，經常會遇到由於地區差異引起的挑戰。一個很好的例子是小數的表示法。在歐洲地區，通常使用逗號（,）作為小數分隔符，而不是其他地區使用的句點（.）。當將資料匯入資料函式庫時，這些地區特定的表示法可能會引入複雜性，尤其是當系統的地區設定與資料格式不一致時。

使用 Python 的 locale 模組

為了糾正這種情況，我們可以利用 Python 廣泛的函式庫生態系統。locale 模組為這個特定的挑戰提供瞭解決方案。我們可以定義一個函式，convert_locale，將這些歐洲格式的小數值轉換為 DuckDB 可以解釋為數字型別的格式：

from duckdb.typing import *
import locale

def convert_locale(field: str) -> float:
    locale.setlocale(locale.LC_ALL, 'de_DE')
    return locale.atof(field)

內容解密：

  1. locale.setlocale(locale.LC_ALL, 'de_DE') 設定了地區設定為德國（‘de_DE’），這裡使用逗號作為小數分隔符。
  2. locale.atof(field) 將字串轉換為浮點數，根據設定的地區設定。

註冊這個函式後，我們可以在 SQL 查詢中使用它：

con.create_function('convert_locale', convert_locale)

應用 convert_locale 函式

讓我們將這個函式應用到幾個列，例如 Coastline (coast/area ratio) 和 Pop. Density (per sq. mi.)：

con.sql("""
SELECT "Coastline (coast/area ratio)" AS coastline,
convert_locale(coastline) as cleanCoastline,
"Pop. Density (per sq. mi.)" as popDen,
convert_locale(popDen) as cleanPopDen
FROM population
LIMIT 5
""")

內容解密：

  1. convert_locale(coastline) 將 coastline 列的值從歐洲格式轉換為 DuckDB 可以解釋的數字格式。
  2. 同樣地，convert_locale(popDen) 對 popDen 列進行了相同的轉換。

檢查結果後，原始值和清理後的值之間的區別是顯而易見的。我們的函式成功地將像 0,00 這樣的值轉換為 0.0。

修改資料表欄位型別

一旦對函式的操作充滿信心，就可以透過修改欄位型別使這些更改在資料集中永久生效。ALTER TABLE 子句允許我們同時修改欄位型別和更新值：

con.sql("""
ALTER TABLE population
ALTER "Coastline (coast/area ratio)"
SET DATA TYPE DOUBLE
USING
convert_locale("Coastline (coast/area ratio)")
""")

內容解密：

  1. ALTER TABLE population 修改了 population 表。
  2. ALTER "Coastline (coast/area ratio)" SET DATA TYPE DOUBLE 將指定的欄位型別更改為 DOUBLE。
  3. USING convert_locale("Coastline (coast/area ratio)") 使用 convert_locale 函式更新了該欄位的所有值。

這個過程強調了理解和適應區域資料細微差別的重要性。它還突出了 DuckDB 的靈活性和整合能力。

玄貓 BlackCat

玄貓 BlackCat

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