In Googles Bigquery können SQL-Abfragen parametrisiert werden. Wenn Sie mit diesem Konzept nicht vertraut sind, bedeutet es im Grunde, dass Sie SQL-Abfragen als parametrisierte Vorlagen wie folgt schreiben können:

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

Und übergeben Sie die Werte separat. Das hat zahlreiche Vorteile:

• Die Abfrage ist besser lesbar als wenn sie durch Zeichenfolgenverkettung erstellt wird
• Der Code ist robuster und industrialisierter
• Es ist ein großartiger Schutz gegen SQL-Injection-Angriffe (obligatorisches XKCD)

Die Übergabe von Abfrageparametern aus einem Python-Skript erscheint auf den ersten Blick unkompliziert. Zum Beispiel:

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")
])

Das obige Beispiel fügt einfache („Skalare“) Werte in die Spalten A und B ein. Sie können aber auch komplexere Parameter übergeben:

• Arrays (ArrayQueryParameter)
• Strukturen (StructQueryParameter)

Probleme treten auf, wenn Sie Arrays von Strukturen einfügen möchten: Es gibt viele Fallstricke, fast keine Dokumentation und nur sehr wenige Ressourcen zu diesem Thema im Web. Das Ziel dieses Artikels ist es, diese Lücke zu schließen.

So behalten Sie mithilfe parametrisierter Abfragen ein Array von Strukturen in BigQuery bei

Lassen Sie uns das folgende Objekt definieren, das wir in unserer Zieltabelle speichern möchten

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

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

durch Aufrufen dieser parametrisierten Abfrage

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

Der erste Versuch, der oberflächlichen Dokumentation zu folgen, wäre

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

was kläglich scheitern würde

AttributeError: Das Objekt „dict“ hat kein Attribut „to_api_repr“

Problem Nr. 1: Die Werte von ArrayQueryParameter müssen Instanzen von StructQueryParameter sein

Es stellt sich heraus, dass das dritte Argument des Konstruktors – Werte – eine Sammlung von StructQueryParameter-Instanzen sein muss, nicht direkt die gewünschten Werte. Also lasst uns sie bauen:

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])
]))

Diesmal funktioniert es... Bis Sie versuchen, ein leeres Array festzulegen

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

ValueError: Es fehlen detaillierte Informationen zum Strukturelementtyp für ein leeres Array. Bitte geben Sie eine StructQueryParameterType-Instanz an.

Problem Nr. 2: Geben Sie den vollständigen Strukturtyp als zweites Argument an

Die Fehlermeldung ist ziemlich klar: „RECORD“ reicht nicht aus, damit Bigquery weiß, was mit Ihrem leeren Array zu tun ist. Es braucht die vollständig detaillierte Struktur. So sei es

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

(Beachten Sie, dass die Reihenfolge der Argumente des ...ParameterType-Konstruktors umgekehrt ist wie der ...Parameter-Konstruktor. Nur eine weitere Falle auf der Straße...)

Und jetzt funktioniert es auch für leere Arrays, juhuu!

Ein letzter Punkt, den Sie beachten sollten: Jedes Unterfeld eines StructQueryParameterType muss einen Namen haben, auch wenn der zweite Parameter (Name) im Konstruktor optional ist. Es ist eigentlich für Unterfelder obligatorisch, sonst erhalten Sie eine neue Art von Fehler

Leerer Strukturfeldname

Ich denke, das ist alles, was wir wissen müssen, um die Verwendung von Arrays von Datensätzen in Abfrageparametern abzuschließen. Ich hoffe, das hilft!

Danke fürs Lesen! Ich bin Matthieu, Dateningenieur bei Stack Labs.
Wenn Sie die Stack Labs Data Platform entdecken oder einem begeisterten Data Engineering-Team beitreten möchten, kontaktieren Sie uns bitte.

Foto von Denys Nevozhai auf Unsplash