Home >Backend Development >Python Tutorial >How to pass an Array of Structs in Bigquerys parameterized queries
In Google's Bigquery, SQL queries can be parameterized. If you're not familiar with this concept, it basically means that you can write SQL queries as parameterized templates like this:
INSERT INTO mydataset.mytable(columnA, columnB) VALUES (@valueA, @valueB)
And pass the values separately. This has numerous benefits:
The passing of query parameters from a Python script appears straightforward... at first sight. For example:
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") ])
The example above inserts simple ("Scalar") values in columns A and B. But you can also pass more complex parameters:
Problems arise when you want to insert arrays of structs : there are many gotchas, almost no documentation and very few resources on the subject on the web. The goal of this article is to fill this gap.
Let's define the following object that we want to store in our destination table
from dataclasses import dataclass @dataclass class Country: name: str capital_city: str @dataclass class Continent: name: str countries: list[Country]
by invoking this parameterized query
query = UPDATE continents SET countries=@countries WHERE name="Oceania"
The first try by following the shallow documentation would be
client.query(query, job_config=QueryJobConfig(query_parameters=[ ArrayQueryParameter("countries", "RECORD", [ {name="New Zealand", capital_city="Wellington"}, {name="Fiji", capital_city="Suva"} ...] ]))
which would fail miserably
AttributeError: 'dict' object has no attribute 'to_api_repr'
It turns out that the third argument of the constructor - values- must be a collection of StructQueryParameter instances, not the wanted values directly. So let's build them:
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]) ]))
This time it works... Until you try to set an empty array
client.query(query, job_config=QueryJobConfig( query_parameters=[ ArrayQueryParameter("countries", "RECORD", []) ]))
ValueError: Missing detailed struct item type info for an empty array, please provide a StructQueryParameterType instance.
The error message is pretty clear: "RECORD" is not enough for Bigquery to know what to do with your empty array. It needs the fully detailed structure. So be it
client.query(query, job_config=QueryJobConfig(query_parameters=[ ArrayQueryParameter("countries", StructQueryParameterType( ScalarQueryParameterType("STRING","name"), ScalarQueryParameterType("STRING","capital_city") ), []) ]))
(Notice how the order of the arguments of the ...ParameterType constructor is the reverse of ...Parameter constructor. Just another trap on the road...)
And now it works for empty arrays too, yay !
One last gotcha to be aware of: every subfield of a StructQueryParameterType must have a name, even if the second parameter (name) is optional in the constructor. It's actually mandatory for subfields, otherwise you'll get a new kind of error
Empty struct field name
I think that's all we need to know to complete the usage of arrays of records in query parameters, I hope this helps !
Thanks for reading! I’m Matthieu, data engineer at Stack Labs.
If you want to discover the Stack Labs Data Platform or join an enthousiast Data Engineering team, please contact us.
Photo de Denys Nevozhai sur Unsplash
The above is the detailed content of How to pass an Array of Structs in Bigquerys parameterized queries. For more information, please follow other related articles on the PHP Chinese website!