BigQuery のパラメータ化クエリで構造体の配列を渡す方法

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 番目の引数 (values) は、必要な値を直接指定するのではなく、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 の写真

以上がBigQuery のパラメータ化クエリで構造体の配列を渡す方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。