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)

構造体の配列を挿入したいときに問題が発生します。落とし穴がたくさんあり、ドキュメントはほとんどなく、Web 上にはこの主題に関するリソースがほとんどありません。この記事の目的は、このギャップを埋めることです。

パラメータ化されたクエリを使用して 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 のインスタンスである必要があります

コンストラクターの 3 番目の引数 (値) は、直接必要な値ではなく、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 インスタンスを提供してください。

注意事項 2: 完全な構造タイプを 2 番目の引数として指定します

エラー メッセージは非常に明確です。BigQuery が空の配列をどう処理するかを知るには、「RECORD」だけでは十分ではありません。完全に詳細な構造が必要です。それでいい

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

(...ParameterType コンストラクターの引数の順序が ...Parameter コンストラクターの逆になっていることに注意してください。また、道中にある罠が 1 つあります...)

そして空の配列でも機能するようになりました、やったー!

注意すべき最後の注意点: StructQueryParameterType のすべてのサブフィールドには名前が必要です。これは、コンストラクターの 2 番目のパラメーター (名前) がオプションである場合でも同様です。これは実際にはサブフィールドでは必須です。そうでない場合は、新しい種類のエラーが発生します

空の構造体フィールド名

クエリ パラメーターでのレコードの配列の使用を完了するために知っておく必要があるのはこれだけだと思います。これがお役に立てば幸いです !

読んでいただきありがとうございます！私は Stack Labs のデータ エンジニアの Matthieu です。
Stack Labs データ プラットフォームを知りたい場合、または熱心なデータ エンジニアリング チームに参加したい場合は、お問い合わせください。

Unsplash の Denys Nevozhai の写真