0

I have table in bigquery with struct

enter image description here

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...

mohd4482
  • 1,788
  • 14
  • 25
Дмитрий Запорожец
  • 1
  • 2
  • I have already understood that my code insert data into this struct - https://monosnap.com/direct/1sEuynps8iM6QVldR5CArVQnXq8Ru6 But how to insert data in the previous table? And what a difference between them? – Дмитрий Запорожец Jul 05 '19 at 15:34

1 Answers1

2

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.

justbeez
  • 1,367
  • 7
  • 12