En Bigquery de Google, las consultas SQL se pueden parametrizar. Si no está familiarizado con este concepto, básicamente significa que puede escribir consultas SQL como plantillas parametrizadas como esta:

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

Y pasar los valores por separado. Esto tiene numerosos beneficios:

• La consulta es más legible que cuando se crea mediante concatenación de cadenas
• El código es más robusto e industrializado
• Es una gran protección contra ataques de inyección SQL (XKCD obligatorio)

El paso de parámetros de consulta desde un script de Python parece sencillo... a primera vista. Por ejemplo:

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

El ejemplo anterior inserta valores simples ("Escalares") en las columnas A y B. Pero también puedes pasar parámetros más complejos:

• Matrices (ArrayQueryParameter)
• Estructuras (StructQueryParameter)

Los problemas surgen cuando se quieren insertar matrices de estructuras: hay muchos errores, casi ninguna documentación y muy pocos recursos sobre el tema en la web. El objetivo de este artículo es llenar este vacío.

Cómo conservar una serie de estructuras en bigquery usando consultas parametrizadas

Definamos el siguiente objeto que queremos almacenar en nuestra tabla de destino

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

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

invocando esta consulta parametrizada

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

El primer intento siguiendo la documentación superficial sería

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

que fracasaría estrepitosamente

AttributeError: el objeto 'dict' no tiene el atributo 'to_api_repr'

Gotcha n°1: los valores de ArrayQueryParameter deben ser instancias de StructQueryParameter

Resulta que el tercer argumento del constructor, valores, debe ser una colección de instancias de StructQueryParameter, no los valores deseados directamente. Así que vamos a construirlos:

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

Esta vez funciona... Hasta que intentas establecer una matriz vacía

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

ValueError: falta información detallada sobre el tipo de elemento de estructura para una matriz vacía; proporcione una instancia de StructQueryParameterType.

Gotcha n°2: proporcione el tipo de estructura completo como segundo argumento

El mensaje de error es bastante claro: "RECORD" no es suficiente para que Bigquery sepa qué hacer con tu matriz vacía. Necesita la estructura completamente detallada. Que así sea

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

(Observe cómo el orden de los argumentos del constructor ...ParameterType es el inverso del ...Constructor de parámetros. Sólo otra trampa en el camino...)

Y ahora también funciona para matrices vacías, ¡sí!

Un último problema a tener en cuenta: cada subcampo de un StructQueryParameterType debe tener un nombre, incluso si el segundo parámetro (nombre) es opcional en el constructor. En realidad, es obligatorio para los subcampos; de lo contrario, obtendrás un nuevo tipo de error

Nombre del campo de estructura vacío

Creo que eso es todo lo que necesitamos saber para completar el uso de matrices de registros en los parámetros de consulta. ¡Espero que esto ayude!

¡Gracias por leer! Soy Matthieu, ingeniero de datos de Stack Labs.
Si desea descubrir la plataforma de datos de Stack Labs o unirse a un entusiasta equipo de ingeniería de datos, contáctenos.

Foto de Denys Nevozhai sur Unsplash