CODEWITHSUNDEEP
google-bigquery
Tony B
Tony B

Reputation: 191

How do I copy from one BigQuery Table to another when the target contains REPEATED fields?

I have two tables which have similar schema, but one has a subset of fields which have Mode set to REPEATED. For example:

Schema 1:

[
    {"name": "id", "type": "NUMERIC", "mode": "REQUIRED"},
    {"name": "name", "type": "STRING", "mode": "REQUIRED"},
    {"name": "created", "type": "TIMESTAMP", "mode": "REQUIRED"},
    {"name": "valid", "type": "BOOLEAN", "mode": "REQUIRED"},
    {"name": "parameter1", "type": "STRING", "mode": "REQUIRED"},
    {"name": "parameter2", "type": "FLOAT", "mode": "REQUIRED"},
    {"name": "parameter3", "type": "BOOLEAN", "mode": "REQUIRED"}
]

Schema 2:

[
    {"name": "id", "type": "NUMERIC", "mode": "REQUIRED"},
    {"name": "name", "type": "STRING", "mode": "REQUIRED"},
    {"name": "created", "type": "TIMESTAMP", "mode": "REQUIRED"},
    {"name": "valid", "type": "BOOLEAN", "mode": "REQUIRED"},
    {"name": "parameters", "type": "RECORD", "mode": "REPEATED", "fields":
        [
            {"name": "parameter1", "type": "STRING", "mode": "REQUIRED"},
            {"name": "parameter2", "type": "FLOAT", "mode": "REQUIRED"},
            {"name": "parameter3", "type": "BOOLEAN", "mode": "REQUIRED"}
        ]
    }
]

I've created Table1 and Table2 using these schemas. Table1 has been written to, Table2 is empty. I'd like to copy the contents of Table1 to Table2, where Table1.parameter1, Table1.parameter2 and Table1.parameter3 are written to an array of Table2.parameters of exactly one element each. I'm assuming a SQL command like INSERT INTO TABLE2 (id, name, created ...) SELECT id, name, created FROM TABLE1; should do the job, but I can't find the correct syntax.

(In future Table 2 will have data with multiple lines of parameters written to it, hence the need for the copy)

I've seen the documentation at https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax and none of the suggestions seem to fit my use case. Can you point me in the right direction?

Thanks!

Upvotes: 0

Views: 1853

Answers (1)

Daniel Duato
Daniel Duato

Reputation: 378

This can be done by grouping the parameter columns of the first table into a STRUCT and an ARRAY when inserting them into the second table:

INSERT INTO table2 (id, name, created, valid, parameters)
SELECT id, name, created, valid, [(parameter1, parameter2, parameter3)]
FROM table1

Upvotes: 1

Related Questions