Google bigquery update rows

Question

So can anyone give ideas on how to update a set of rows?

I understand the concept of query -> new table, then dumping the "old" table and re-naming the "new", but to be honest this is very hokey.

I don't see anything in the documentation, web, or in the new ideas that will lead me to believe in the appearance of an "update" statement either.

Thoughts anyone?

Answer 1

On September 29, 2016, Google update there DML, and now we can write standard SQL, which enables us to insert, update, and delete rows and columns in your BigQuery datasets.

Answer 2

[UPDATE This answer is out-of-date]

BigQuery does not currently support direct updates to individual rows. You can append to a table, and you can truncate/overwrite a table, but you cannot apply an update to a single row while leaving the rest of the table untouched.

The flow you mentioned (create new table, replace old table) is a reasonable approach. If it helps, note that you do not need two separate steps to replace the old table with the new table. Since BigQuery applies job side-effects atomically, you can replace the old table in one step by setting the writeDisposition on the final copy job to WRITE_TRUNCATE. For example, you could do the following:

  query table -> table with WRITE_TRUNCATE

Just like an update, you should note that this is destructive to the old table. However, if you didn't change the schema, you can use a snapshot decorator to read the table as of a time before the truncate occurred.

While this update process is occurring, you can have query jobs running against "table", and those jobs are guaranteed to see either the old content or the new content, with no inconsistent or erroneous states in between.