How to insert data into a big query column of type Array from an insert query?

Question

I am new to Bigquery and trying to insert values into a table I created in my project. I have a table cannabis on bigquery which contains columns of below types:

reference_name  STRING  NULLABLE    
start   INTEGER NULLABLE    
end INTEGER NULLABLE    
reference_bases STRING  NULLABLE    
alternate_bases STRING  REPEATED    
variant_id  STRING  NULLABLE    
quality FLOAT   NULLABLE    
filter  STRING  REPEATED    
names   STRING  REPEATED    
call    RECORD  REPEATED    
call. call_set_id   STRING  NULLABLE    
call. call_set_name STRING  NULLABLE    
call. genotype  INTEGER REPEATED    
call. phaseset  STRING  NULLABLE    
call. genotype_likelihood   FLOAT   REPEATED    
call. AD    INTEGER REPEATED    
call. AO    INTEGER REPEATED    
call. DP    INTEGER NULLABLE    
call. QA    INTEGER REPEATED    
call. QR    INTEGER NULLABLE    
call. QUAL  FLOAT   NULLABLE    
call. RO    INTEGER NULLABLE

I am trying to insert data into the table using the query below:

INSERT into default_dataset.genomics_cannabis_table_stg (reference_name, start, `end`, reference_bases, alternate_bases, variant_id, quality, filter, names, call)
VALUES ('reference_name', 1, 2, 'reference_bases', ['alternate_bases'], 'variant_id', 1.0, ['filter'], ['names'], 
("call[OFFSET(0)].call_set_id",  "call[OFFSET(0)].call_set_name", 1, 'call[OFFSET(0)].phaseset', 1.0, 1, 1, 1, 1, 1, 1.0, 1);

I got an error exactly at the column call at ("call[OFFSET(0)].......

Value has type STRUCT<STRING, STRING, INT64, ...> which cannot be inserted into column call, which has type ARRAY<STRUCT<call_set_id STRING, call_set_name STRING, genotype ARRAY, ...>> at [14:5]

I understood the mistake I was doing and changed the style of brackets from (...) to [...] just for the values of the column call as below:

INSERT into default_dataset.genomics_cannabis_table_stg (reference_name, start, `end`, reference_bases, alternate_bases, variant_id, quality, filter, names, call)
VALUES ('reference_name', 1, 2, 'reference_bases', ['alternate_bases'], 'variant_id', 1.0, ['filter'], ['names'], 
["call[OFFSET(0)].call_set_id",  "call[OFFSET(0)].call_set_name", 1, 'call[OFFSET(0)].phaseset', 1.0, 1, 1, 1, 1, 1, 1.0, 1]);

But this time I am facing a different error:

Array elements of types {INT64, DOUBLE, STRING} do not have a common supertype at [14:5]

Now I am not sure what is the issue and how can I fix it. Could anyone let me know what is the mistake I am doing here and how can I fix the issue ? Any help is much appreciated.

Sergey Geron Sergey Geron · Accepted Answer · 2021-02-16T13:49:17

You were moving in the correct direction. You should have enveloped like [(...)] instead of replacing (...) with [...]:

INSERT into default_dataset.genomics_cannabis_table_stg (reference_name, start, `end`, reference_bases, alternate_bases, variant_id, quality, filter, names, call)
VALUES ('reference_name', 1, 2, 'reference_bases', ['alternate_bases'], 'variant_id', 1.0, ['filter'], ['names'], 
[("call[OFFSET(0)].call_set_id",  "call[OFFSET(0)].call_set_name", [1], 'call[OFFSET(0)].phaseset', 1.0, 1, 1, 1, 1, 1, 1.0, 1)];

How to insert data into a big query column of type Array from an insert query?

1 Answers