BigQuery のパラメータ化クエリで構造体の配列を渡す方法-Python チュートリアル-php.cn

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

Mary-Kate Olsen

リリース： 2024-10-15 16:14:02

オリジナル

224 人が閲覧しました

How to pass an Array of Structs in 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 番目のパラメーター (名前) がオプションである場合でも同様です。これは実際にはサブフィールドでは必須です。そうでない場合は、新しい種類のエラーが発生します