How to create a new table with a nested schema completely in BigQuery

Question

I have a nested table A in BigQuery with a schema:

{ "name": "page_event", "mode": "repeated", "type": "RECORD", "fields": [ { "name": "id", "type": "STRING" } ] } 

I would like to enrich table A with data from another table and save the result as a new nested table. Let's say I would like to add a “description” to table A (creating table B), so my schema would look like this:

  { "name": "page_event", "mode": "repeated", "type": "RECORD", "fields": [ { "name": "id", "type": "STRING" }, { "name": "description", "type": "STRING" } ] } 

How to do it in BigQuery? It seems that there are no functions for creating nested structures in BigQuery SQL (except for the NEST functions that create the list, but this function does not seem to work, does not work with an unexpected error)

The only way I can think of this is:

• use string concatenation functions to create table B with one json field with content that enriches data from A is converted to json string
• export B to GCS as a set of F files
• load F as table C

Is there an easier way to do this?

Answer 1

To enrich the schema of an existing table, you can use the patch API for tables
https://cloud.google.com/bigquery/docs/reference/v2/tables/patch

The request will look below

 PATCH https://www.googleapis.com/bigquery/v2/projects/{project_id}/datasets/{dataset_id}/tables/{table_id}?key={YOUR_API_KEY} { "schema": { "fields": [ { "name": "page_event", "mode": "repeated", "type": "RECORD", "fields": [ { "name": "id", "type": "STRING" }, { "name": "description", "type": "STRING" } ] } ] } } 

Before correction

After the patch