Dans Bigquery de Google, les requêtes SQL peuvent être paramétrées. Si vous n'êtes pas familier avec ce concept, cela signifie essentiellement que vous pouvez écrire des requêtes SQL sous forme de modèles paramétrés comme celui-ci :

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

Et transmettez les valeurs séparément. Cela présente de nombreux avantages :

• La requête est plus lisible que lorsqu'elle est construite par concaténation de chaînes
• Le code est plus robuste et industrialisé
• C'est une excellente protection contre les attaques par injection SQL (XKCD obligatoire)

La transmission des paramètres de requête à partir d'un script Python semble simple... à première vue. Par exemple:

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

L'exemple ci-dessus insère des valeurs simples ("Scalaire") dans les colonnes A et B. Mais vous pouvez également passer des paramètres plus complexes :

• Tableaux (ArrayQueryParameter)
• Structs (StructQueryParameter)

Des problèmes surviennent lorsqu'on veut insérer des tableaux de structs : il y a de nombreux pièges, quasiment aucune documentation et très peu de ressources sur le sujet sur le web. Le but de cet article est de combler cette lacune.

Comment conserver un tableau de structures dans bigquery à l'aide de requêtes paramétrées

Définissons l'objet suivant que nous souhaitons stocker dans notre table de destination

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

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

en appelant cette requête paramétrée

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

Le premier essai en suivant la documentation superficielle serait

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

qui échouerait lamentablement

AttributeError : l'objet 'dict' n'a pas d'attribut 'to_api_repr'

Gotcha n°1 : les valeurs d'ArrayQueryParameter doivent être des instances de StructQueryParameter

Il s'avère que le troisième argument du constructeur - valeurs - doit être une collection d'instances de StructQueryParameter, et non directement les valeurs souhaitées. Alors construisons-les :

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

Cette fois, cela fonctionne... Jusqu'à ce que vous essayiez de définir un tableau vide

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

ValueError : informations détaillées manquantes sur le type d'élément de structure pour un tableau vide, veuillez fournir une instance de StructQueryParameterType.

Gotcha n°2 : Fournir le type de structure complet en deuxième argument

Le message d'erreur est assez clair : "RECORD" ne suffit pas à Bigquery pour savoir quoi faire de votre tableau vide. Il a besoin d’une structure entièrement détaillée. Qu'il en soit ainsi

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

(Remarquez comment l'ordre des arguments du constructeur ...ParameterType est l'inverse du constructeur ...Parameter. Juste un autre piège sur la route...)

Et maintenant, cela fonctionne aussi pour les tableaux vides, ouais !

Un dernier piège à prendre en compte : chaque sous-champ d'un StructQueryParameterType doit avoir un nom, même si le deuxième paramètre (nom) est facultatif dans le constructeur. C'est en fait obligatoire pour les sous-champs, sinon vous obtiendrez un nouveau type d'erreur

Nom du champ de structure vide

Je pense que c'est tout ce que nous devons savoir pour compléter l'utilisation des tableaux d'enregistrements dans les paramètres de requête, j'espère que cela aidera !

Merci d'avoir lu ! Je m'appelle Matthieu, data Engineer chez Stack Labs.
Si vous souhaitez découvrir la Data Platform Stack Labs ou rejoindre une équipe passionnée de Data Engineering, contactez-nous.

Photo de Denys Nevozhai sur Unsplash