在Google的Bigquery中，SQL查詢可以參數化。如果您不熟悉這個概念，它基本上意味著您可以將 SQL 查詢編寫為參數化模板，如下所示：

INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)

並分別傳遞數值。這有很多好處：

• 查詢比透過字串連接建構的查詢更​​具可讀性
• 代碼更加健全和工業化
• 這是針對 SQL 注入攻擊的強大保護（強制 XKCD）

乍一看，從 Python 腳本傳遞查詢參數似乎很簡單。例如：

from google.cloud.bigquery import (
    Client,
    ScalarQueryParameter,
    ArrayQueryParameter,
    StructQueryParameter,
    QueryJobConfig,
)

client=Client()

client.query("
INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)
", job_config=QueryJobConfig(
    query_parameters=[
        ScalarQueryParameter("valueA","STRING","A"), 
        ScalarQueryParameter("valueB","STRING","B")
])

上面的範例在 A 列和 B 列中插入簡單（「標量」）值。但您也可以傳遞更複雜的參數：

• 陣列（ArrayQueryParameter）
• 結構（StructQueryParameter）

當您想要插入結構數組時，就會出現問題：有很多陷阱，幾乎沒有文檔，網絡上有關該主題的資源也很少。本文的目標就是填補這一空白。

如何使用參數化查詢在 bigquery 中持久保存結構數組

讓我們定義要儲存在目標表中的以下物件

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

@dataclass
class Continent:
    name: str
    countries: list[Country]

透過呼叫此參數化查詢

query = UPDATE continents SET countries=@countries WHERE name="Oceania"

遵循淺薄文件的第一次嘗試是

client.query(query, 
    job_config=QueryJobConfig(query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [
             {name="New Zealand", capital_city="Wellington"},
             {name="Fiji", capital_city="Suva"} ...]
]))

這會慘敗

AttributeError：'dict'物件沒有屬性'to_api_repr'

問題 1：ArrayQueryParameter 的值必須是 StructQueryParameter 的實例

事實證明，建構子的第三個參數 - value - 必須是 StructQueryParameter 實例的集合，而不是直接想要的值。那麼讓我們來建構它們：

client.query(query, 
job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries", "RECORD", [
    StructQueryParameter("countries",
        ScalarQueryParameter("name", "STRING", ct.name), 
        ScalarQueryParameter("capital_city", "STRING", ct.capital_city)
    )
    for ct in countries])
]))

這次有效...直到你試著設定一個空數組

client.query(query, 
    job_config=QueryJobConfig(
    query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [])
]))

ValueError：缺少空數組的詳細結構項類型信息，請提供 StructQueryParameterType 實例。

陷阱 n°2：提供完整的結構類型作為第二個參數

錯誤訊息非常清楚：「RECORD」不足以讓 Bigquery 知道如何處理空數組。它需要完整詳細的結構。就這樣吧

client.query(query, job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries",
        StructQueryParameterType(
            ScalarQueryParameterType("STRING","name"),
            ScalarQueryParameterType("STRING","capital_city")
        ), [])
]))

（注意 ...ParameterType 建構子的參數順序與 ...Parameter 建構子相反。這只是路上的另一個陷阱...）

現在它也適用於空數組，耶！

最後一個需要注意的問題：StructQueryParameterType 的每個子欄位都必須有一個名稱，即使第二個參數（名稱）在建構函式中是可選的。它實際上對於子字段是強制性的，否則你會得到一種新的錯誤

空結構欄位名稱

我想這就是我們完成查詢參數中記錄數組的使用所需要知道的一切，我希望這會有所幫助！

感謝您的閱讀！我是 Matthieu，Stack Labs 的資料工程師。
如果您想了解 Stack Labs 資料平台或加入熱情的資料工程團隊，請與我們聯絡。

Denys Nevozhai 在 Unsplash 上的照片