CODEWITHSUNDEEP
sqlgoogle-bigquery
Raed Khalaf
Raed Khalaf

Reputation: 2065

Insert into table contains Repeated Record in BigQuery

I have a table in BigQuery with very complex scheme (up to 2300 column)

in these columns I have RECORD type fields, someof them are in REPEATED mode,

The insert statement is generated by processor in the code, but when test this insertion statement on BigQuery Web-UI I see an error, after investigating the issue, I found that inserting array is not done in the appropriate way.

INSERT INTO Table_X (RECORD_FIELD) VALUES (
...

STRUCT([STRUCT(X), STRUCT(Y)]) as property_z
...

it this format is correct for inserting REPEATED fields?

INSERT INTO TABLE_NAME (columns) VALUES (STRUCT([ STRUCT(...), STRUCT(...) ]), ...)

Upvotes: 4

Views: 5805

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Repeated fields are arrays, so you want to insert them as arrays:

INSERT INTO TABLE_NAME (repeated_column)
    VALUES (ARRAY[ STRUCT(...), STRUCT(...) ]);

Note that the array is a single column, You can include values for other columns in the INSERT as well.

Upvotes: 4

Related Questions