I used .setSchemaUpdateOptions and ALLOW_FIELD_RELAXATION to append data to my table that had several nonexistent/null values for REQUIRED fields. I was under the impression that it would temporarily relax the REQUIRED restriction. Unfortunately, it appears to have permanently changed all my fields to NULLABLE.
Is there a way to revert this change without replaying the entire table?
As null values now exist in some rows in this table, you won't be able to revert the table to have REQUIRED CONSTRAINTS
Read more here: https://cloud.google.com/bigquery/docs/managing-table-schemas
Relaxing a column's mode Currently, the only supported modification you can make to a column's mode is changing it from REQUIRED to NULLABLE. Changing a column's mode from REQUIRED to NULLABLE is also called column relaxation. You can relax REQUIRED columns:
Manually When you overwrite a table using a load or query job When you append data to a table using a query job Caution: Changing a REQUIRED column to NULLABLE cannot be undone.
What BigQuery does very well is moving huge datasets between tables (note the costs) You can: 1. UPDATE the null values in your current table 2. Recreate a new table with the REQUIRED fields (if you want to stick with the name, you'll have to stop in a TEMP table as well) 3. Initiate a simple load job to migrate the data from the old to new table
You need to recreate the table, which you can do in place:
CREATE OR REPLACE TABLE dataset.table
(
x INT64 NOT NULL,
y STRING NOT NULL,
z BOOL NOT NULL
) AS
SELECT *
FROM dataset.table
You have to list the columns and their types, along with the NOT NULL attribute for the ones you want to be required.
