"Se um trabalhador quiser fazer bem o seu trabalho, ele deve primeiro afiar suas ferramentas." - Confúcio, "Os Analectos de Confúcio. Lu Linggong"
Primeira página > Programação > Como passar um array de estruturas nas consultas parametrizadas do Bigquery

Como passar um array de estruturas nas consultas parametrizadas do Bigquery

Publicado em 2024-11-08
Navegar:130

How to pass an Array of Structs in Bigquery

No Bigquery do Google, as consultas SQL podem ser parametrizadas. Se você não está familiarizado com esse conceito, isso basicamente significa que você pode escrever consultas SQL como modelos parametrizados como este:

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

E passe os valores separadamente. Isso tem vários benefícios:

  • A consulta é mais legível do que quando é construída por concatenação de strings
  • O código é mais robusto e industrializado
  • É uma ótima proteção contra ataques de injeção de SQL (XKCD obrigatório)

A passagem de parâmetros de consulta de um script Python parece simples... à primeira vista. Por exemplo:

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

O exemplo acima insere valores simples ("escalares") nas colunas A e B. Mas você também pode passar parâmetros mais complexos:

  • Matrizes (ArrayQueryParameter)
  • Estruturas (StructQueryParameter)

Os problemas surgem quando você deseja inserir arrays de structs: há muitas pegadinhas, quase nenhuma documentação e pouquíssimos recursos sobre o assunto na web. O objetivo deste artigo é preencher essa lacuna.

Como persistir um array de estruturas no bigquery usando consultas parametrizadas

Vamos definir o seguinte objeto que queremos armazenar em nossa tabela 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"

A primeira tentativa seguindo a documentação superficial seria

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

que falharia miseravelmente

AttributeError: o objeto 'dict' não tem atributo 'to_api_repr'

Entendi nº 1: os valores de ArrayQueryParameter devem ser instâncias de StructQueryParameter

Acontece que o terceiro argumento do construtor - valores - deve ser uma coleção de instâncias de StructQueryParameter, não os valores desejados diretamente. Então, vamos construí-los:

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

Desta vez funciona... Até você tentar definir um array vazio

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

ValueError: informações detalhadas do tipo de item de estrutura ausentes para uma matriz vazia. Forneça uma instância de StructQueryParameterType.

Gotcha n°2: Forneça o tipo de estrutura completo como segundo argumento

A mensagem de erro é bem clara: "RECORD" não é suficiente para o Bigquery saber o que fazer com seu array vazio. Precisa de uma estrutura totalmente detalhada. Assim seja

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

(Observe como a ordem dos argumentos do construtor ...ParameterType é o inverso do construtor ...Parameter. Apenas mais uma armadilha no caminho...)

E agora também funciona para arrays vazios, sim!

Uma última dica a ter em conta: cada subcampo de um StructQueryParameterType deve ter um nome, mesmo que o segundo parâmetro (nome) seja opcional no construtor. Na verdade, é obrigatório para subcampos, caso contrário, você receberá um novo tipo de erro

Nome do campo de estrutura vazio

Acho que isso é tudo que precisamos saber para completar o uso de arrays de registros em parâmetros de consulta, espero que isso ajude!


Obrigado pela leitura! Sou Matthieu, engenheiro de dados da Stack Labs.
Se você deseja descobrir a plataforma de dados Stack Labs ou ingressar em uma equipe entusiasta de engenharia de dados, entre em contato conosco.


Foto de Denys Nevozhai no Unsplash

Declaração de lançamento Este artigo está reproduzido em: https://dev.to/stack-labs/how-to-pass-an-array-of-structs-in-bigquerys-parameterized-queries-39nm?1 Se houver alguma violação, por favor entre em contato com study_golang@163 .comdelete
Tutorial mais recente Mais>

Isenção de responsabilidade: Todos os recursos fornecidos são parcialmente provenientes da Internet. Se houver qualquer violação de seus direitos autorais ou outros direitos e interesses, explique os motivos detalhados e forneça prova de direitos autorais ou direitos e interesses e envie-a para o e-mail: [email protected]. Nós cuidaremos disso para você o mais rápido possível.

Copyright© 2022 湘ICP备2022001581号-3