Menu
I'm trying to add a nested field to my BigQuery table schema. I can usually do this through the Web UI or command line, but when I try with a nested field I get the following error since the new field name had a . in it:
Error updating schema: Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.
How can I add a nested field?
907
To create a column with nested data, set the data type of the column to RECORD in the schema. A RECORD can be accessed as a STRUCT type in Google Standard SQL. A STRUCT is a container of ordered fields. To create a column with repeated data, set the mode of the column to REPEATED in the schema.
In the Google Cloud console, go to the BigQuery page. In the Explorer panel, expand your project and dataset, then select the table. In the details panel, click the Schema tab. Click Edit schema.
Adding a Column via the WebUI Open the BigQuery WebUI. Select the project , dataset , and finally table you wish to alter. Click the Add New Fields button. For each field you wish to add, enter the name , select the type , and alter the mode (if necessary).
Using the bq command line tool, first export the table's schema:
bq show --format=prettyjson your-project:your_dataset.table_to_update | python -c 'import sys,json; print(json.dumps(json.load(sys.stdin)["schema"]["fields"]))' > table_schema.json
Then manually add your new nested field to the JSON. Once it's added, push the updated schema to BigQuery:
bq update -t --schema='updated_table_schema.json' your-project:your_dataset.table_to_update
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With