Port field from NULLABLE to REQUIRED in BigQuery

6
votes

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.

2
schemagoogle-bigquerydata-migration
When you append the results of a query to a table BigQuery requires that the schema of the query be compatible with the table. REQUIRED to NULLABLE is un-compatible, and "WHERE f IS NOT NULL" doesn't change the schema of the query. Sorry I couldn't think of a solution to this problem. – Hua Zhang
@HuaZhang Thanks for your comment. Thinking about it, it makes sense that a clause in the query doesn't change the schema of the query. In fact, I can imagine cases where it would be complicated to infer the schema just from the clauses of the query. I guess the solution I have left is to write a script that reads rows from the query and push them in the table with the new schema. – Alessandro Cosentino

2 Answers

2
votes

Try to use empty string where f is null.

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

Use second approach - create new table and append data there. But use StandardSQL dialect. It will avoid "changed mode" error. If you are trying to insert NULL, the query would still fail, of course.