Copy tables from query in Bigquery

Question

I am attempting to fix the schema of a Bigquery table in which the type of a field is wrong (but contains no data). I would like to copy the data from the old schema to the new using the UI ( select * except(bad_column) from ... ).

The problem is that:

• if I select into a table, then Bigquery is removing the required of the columns and therefore rejecting the insert.
• Exporting via json loses information on dates.

Is there a better solution than creating a new table with all columns being nullable/repeated or manually transforming all of the data?

Answer 1

Update (2018-06-20): BigQuery now supports required fields on query output in standard SQL, and has done so since mid-2017.

Specifically, if you append your query results to a table with a schema that has required fields, that schema will be preserved, and BigQuery will check as results are written that it contains no null values. If you want to write your results to a brand-new table, you can create an empty table with the desired schema and append to that table.

---

Outdated:

You have several options:

1. Change your field types to nullable. Standard SQL returns only nullable fields, and this is intended behavior, so going forward it may be less useful to mark fields as required.

2. You can use legacy SQL, which will preserve required fields. You can't use except, but you can explicitly select all other fields.

3. You can export and re-import with the desired schema.

You mention that export via JSON loses date information. Can you clarify? If you're referring to the partition date, then unfortunately I think any of the above solutions will collapse all data into today's partition, unless you explicitly insert into a named partition using the table$yyyymmdd syntax. (Which will work, but may require lots of operations if you have data spread across many dates.)