Insert into table with record column which is repeated (screen in question)

Question

I have table in bigquery with struct

And problem with iserting data to test record

I try to insert in this way:

query = (
   """
   INSERT INTO test.qwe (name, nick, test)  
   VALUES(@name, @nick, @test)
   """
)
hitTimeStamp = int(time.time())
query_params = [
   bigquery.ScalarQueryParameter("name", "STRING", hitTimeStamp),
   bigquery.ArrayQueryParameter("nick", "STRING", ["k","TEST"]),
   bigquery.StructQueryParameter(
      "test",
       bigquery.ArrayQueryParameter("a", "STRING", ["adsfwerf","d"]),
       bigquery.ArrayQueryParameter("b", "STRING", ["asda","sdfds"]),
   ),
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params
query_job = client.query(
   query,
   location="US",
   job_config=job_config,
)

I expected the data will be inserted! Pls...

Answer 1

Right now I see a couple problems:

1. You're treating nick as an array, but it's defined as a STRING.
2. You're treating test as a struct containing arrays, but NESTED REPEATED is actually an array containing structs.

Unfortunately, it looks like the helpers you're using from the google-cloud-python library don't correctly support creating the output needed for NESTED REPEATED (array of struct) fields. There's an issue noting this that's currently in their "To Do" status (despite being marked as closed, there was no resolution).

This is possible through other libraries as well as natively in the BigQuery DML syntax, which would look something like this:

INSERT INTO test.qwe (name,nick,test)
VALUES(
  "name value",
  "nick value",
  [
    STRUCT("adsfwerf" AS a,"asda" AS b),
    STRUCT("d" AS a,"sdfds" AS b)
  ]
)

(Note that there are several different syntaxes for working with ARRAY and STRUCT types, but this is the most concise which is why I chose it here.)

So as a workaround, you could always build the query outside of the helpers provided by google-cloud-python and then run it. You may also want to bump that issue thread and see if the project maintainers can provide any other workarounds or help get that functionality added—or you may need to look for another library that fully supports the features around NESTED REPEATED fields.