CODEWITHSUNDEEP
google-bigquery
Jonathan Norris
Jonathan Norris

Reputation: 101

BigQuery append results of query to table with Required fields

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.

Upvotes: 6

Views: 6010

Answers (4)

Addy
Addy

Reputation: 147

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"

Upvotes: 0

Ben Caine
Ben Caine

Reputation: 1256

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.

Upvotes: 5

daemonalec
daemonalec

Reputation: 11

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
"

Upvotes: 1

Mosha Pasumansky
Mosha Pasumansky

Reputation: 13994

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).

Upvotes: 0

Related Questions