Port field from NULLABLE to REQUIRED in BigQuery

Question

I am trying to move a BigQuery table to a new schema that has some additional new NULLABLE fields and in which a field f has become REQUIRED (they were NULLABLE in the old schema.)

I tried to update the table with the new schema through the command

bq update <table> <new_schema>

and I get the error

BigQuery error in update operation: Provided Schema does not match Table

As a second attempt, I created a temporary empty table with the new field and then tried to append there the data coming from a query (SELECT * from the old table), but I get the error:

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

Is there a way to do this migration easily? Of course I am fine with ignoring rows of the table where the field f is actually NULL. It would be cool if BigQuery could infer that from the query. I tried to do

SELECT * FROM old_table WHERE f IS NOT NULL

and append the result to the table with the new schema, but that doesn't work.

Answer 1

Try to use empty string where f is null.

CASE
    WHEN f IS NULL THEN ""
    ELSE f
END as f