Support arrays of record/structs as query parameters

[michellen-hf]

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Is your feature request related to a problem? Please describe.
Setting nested repeated fields via the python-bigquery API works (despite not being documented by ArrayQueryParameter).
ArrayQueryParamter.to_api_repr has code to handle "RECORD" or "STRUCT" but doesn't check if the array is empty and so throw exceptions.

Describe the solution you'd like
I'd a way to define the schema for the array of records so that it doesn't matter that it's empty - I can still use the BQ python client. There appears to be solutions for typed languages googleapis/google-cloud-java#2678 as well as dynamic languages googleapis/google-cloud-ruby#4269 and losely typed languages googleapis/nodejs-bigquery#400.

Describe alternatives you've considered
Our current solution involves naming each individual field in the repeated struct (eg fieldA_0, fieldB_0, fieldA_1,...)
and sending over MANY query parameters. As there's a limit to the number of named parameters the API allows, this is far from perfect and makes our code significantly harder to read and understand.

Additional context

from google.cloud import bigquery
client = bigquery.Client()

def query(params):
 sql = "SELECT * FROM UNNEST(@params) as param"
 query_parameter = bigquery.ArrayQueryParameter(
 "params",
 "RECORD",
 [
 bigquery.StructQueryParameter.positional(
 bigquery.ScalarQueryParameter("key", "STRING", key),
 bigquery.ScalarQueryParameter("value", "STRING", value),
 )
 for key, value in (params.items())
 ],
 )
 for row in client.query(sql, job_config=bigquery.QueryJobConfig(query_parameters=[query_parameter])):
 print(dict(row))

query({"one": "a", "two": "b"}) # works
query({}) # IndexError: list index out of range

[plamut]

@michellen-hf Thanks for the detailed report and a runnable example, it made the reproduction easy! It appears that most (all?) of the BigQuery libraries for other languages suffered from the same issue.

The problem here is that when serializing data to the API format, the ArrayQueryParameter class determines the array type from the first array item, but does not check if the array is empty. I'll reclassify this as a bug.

(at the very least, the outcome should not be an uninformative unhandled IndexError)

[plamut]

After tinkering with BigQuery REST API, unsuccessfully trying various tricks, and reading the comment on a related Ruby issue, it seems that the Python client, too, could benefit from an optional parameter (to QueryJobConfig) to specify query parameter types when that cannot be determined (example: empty arrays of STRUCT/RECORD elements).

I suppose loosening up the checks on the backend is not feasible? In the example below, the backend could tolerate empty (or missing) parameterType.arrayType values for ARRAY parameters if the corresponding parameterValue is empty - but that might have some other negative implications.

cc: @tswast @shollyman

"configuration": {
 "query": {
 "query": "SELECT * FROM UNNEST(@params) as param",
 "parameterMode": "NAMED",
 "queryParameters": [
 {
 "name": "params",
 "parameterType": {
 "type": "ARRAY",
 "arrayType": {
 "type": "STRUCT",
 "structTypes": [],
 }
 },
 "parameterValue": {
 "arrayValues": []
 }
 }
 ],
 "useLegacySql": false,
 }
}

[tswast]

Doubtful that the type parameter could be loosened. I think an extra parameter for array type would be beneficial.