В Bigquery от Google можно параметризовать 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)

Проблемы возникают, когда вы хотите вставить массивы структур: много ошибок, почти нет документации и очень мало ресурсов по этой теме в сети. Цель данной статьи — восполнить этот пробел.

Как сохранить массив структур в 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.

Оказывается, третий аргумент конструктора — значения — должен быть коллекцией экземпляров 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: укажите полный тип структуры в качестве второго аргумента.

Сообщение об ошибке довольно ясно: «RECORD» недостаточно, чтобы Bigquery знал, что делать с пустым массивом. Нужна полностью подробная структура. Пусть будет так

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

(Обратите внимание, что порядок аргументов конструктора ...ParameterType обратный порядку аргументов конструктора ...Parameter. Еще одна ловушка на пути...)

И теперь это работает и для пустых массивов, ура!

И последнее, о чем следует помнить: каждое подполе StructQueryParameterType должно иметь имя, даже если второй параметр (имя) в конструкторе является необязательным. На самом деле это обязательно для подполей, иначе вы получите новый вид ошибки

Пустое имя поля структуры

Я думаю, это все, что нам нужно знать, чтобы завершить использование массивов записей в параметрах запроса, надеюсь, это поможет !

Спасибо, что читаете! Я Матье, инженер по обработке данных в Stack Labs.
Если вы хотите познакомиться с платформой данных Stack Labs или присоединиться к команде энтузиастов по разработке данных, свяжитесь с нами.

Фото Дениса Невожаи на Unsplash