BigQuery append results of query to table with Required fields

Question

I am trying to append the results of a query to an existing table that has the same schema as the result of the query, except that some of the fields in the existing table are required.

This is the error for all the required fields when I try and run the query:

Invalid schema update. Field user has changed mode from REQUIRED to NULLABLE

When I write the results of the query to a new table it has the same name and types for all the fields, however the mode for all the fields is NULLABLE (except for 1 repeated field). I've tried using AND user IS NOT NULL for all the required fields with no luck. I've also tried saving the results of the query to a separate table then modifying its schema, but BQ doesn't allow required fields to be set retroactively on a table.

Ben Caine · Answer 1 · 2017-10-23T13:14:20.700

5

REQUIRED fields are no longer supported in Standard SQL. If you're using Standard SQL (as opposed to Legacy SQL), they recommend you change all your REQUIRED fields to NULLABLE.

Edit (10/23/2017): Looks like they've added support for REQUIRED fields in Standard SQL.

edited Oct 23 '17 at 13:14

answered Nov 21 '16 at 14:35

Ben Caine

1,128
3
15
25

Where on that page does it say that? – Sai Oct 22 '17 at 18:53
Huh, looks like they've added support for required fields: https://cloud.google.com/bigquery/docs/schemas#field_modes – Ben Caine Oct 23 '17 at 13:13

score 1 · Answer 2 · answered Sep 28 '17 at 21:07

FWIW, I found that creating the table with the schema, and then querying into it specifying the same schema, does work. Like this:

bq mk --schema=myschema.json me.newstuff &&
bq query --format=none --allow_large_results \
  --destination_table=me.newstuff \
  --destination_schema=myschema.json \
  --use_legacy_sql=false \
  "
    SELECT * FROM me.oldstuff
    WHERE data_to_keep is > 0
"

score 0 · Answer 3 · answered Sep 09 '16 at 16:28

0

You cannot change schema of the query result to flip NULLABLE to REQUIRED, so the only thing you can do is to change schema of the table to convert REQUIRED column to become NULLABLE (note - if you do it, you won't be able to do the reverse operation later).

answered Sep 09 '16 at 16:28

Mosha Pasumansky

13,206
5
32
55

1

so its basically impossible to append to a table with a query for tables with required fields... That seems like a bit of an oversight. The only way I can think of making this work is to export the data to GCS as a CSV, then do a load job. – Jonathan Norris Sep 09 '16 at 17:15
It is possible in some cases when query produces REQUIRED field, but not in the general case. It seems to me that REQUIRED causes more harm than good, so I would just change schema to be NULLABLE for all fields. – Mosha Pasumansky Sep 09 '16 at 17:18
my tables are already generated and being written to with REQUIRED fields, so I can't really switch the existing tables. – Jonathan Norris Sep 09 '16 at 17:26
1

You can change schema of existing table from REQUIRED to NULLABLE without touching data - it is a compatible schema change. – Mosha Pasumansky Sep 09 '16 at 17:27

score 0 · Answer 4 · answered Nov 19 '19 at 15:08

0

I was getting same error "Field has changed mode from REQUIRED to NULLABLE" when I did a load with bq load command. The way I solved was to change the schema. Changed the field from "REQUIRED" to "NULLABLE"

answered Nov 19 '19 at 15:08

Addy

147
1
1
7

BigQuery append results of query to table with Required fields

4 Answers4

Linked