Append a column and its data to a BigQuery table

Question

I have successfully been able to append a column to a BigQuery table using this link. This only update the schema but I'd like also to fill the field of the newly added column. Is there a way to do it ?

Thank you !

Answer 1

BigQuery is append-only, so you cannot update existing rows. For new inserts you can populate the new column you added.

In case you want to update the previous data, you need to do recreate the table into a new one, then you will be able to add on insert time the data you want.

Answer 2

If you want to fill in the new column, you can run a query that does so, with the write disposition "write truncate". For example, if you have the table mydataset.mytable with fields a, b, and c, then add d, and want to fill in the value "foo" for d in any row that doesn't have it, you can do:

SELECT a,b,c,IFNULL(d, "foo") as d FROM mydataset.mytable

You would then set the destination table to be mydataset.mytable, set 'allow large results', and set the write preference to 'Overwrite Table'. (This is if you're using the UI... if you are using the API directly, there are mappings in the Query Options portion of the query job configuration.)

Note that if you have any nested fields, (let's say 'b' is nested), you'd use b.* instead of b in the select list and also clear the 'flatten results' field in the query options.