0

How to upsert using google-cloud-bigquery gem?

If not possible, how would I achieve it in a Ruby app?

Would I need to create a new table and copy unique rows to it?

user1186842
  • 73
  • 1
  • 5

1 Answers1

0

For this purpose look at Bigquery MERGE clause.

A MERGE statement is a DML statement that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically

A good example for practical usage of this statement you can find in this Stack thread.

I assume for Bigquery Ruby client would be enough appending the MERGE query statements to the appropriate bigquery.query_job method ultimately creating a Bigquery Job object.

Having some more doubts/concerns feel free to extend the initial question exploring more context about your current use case.

Nick_Kh
  • 5,089
  • 2
  • 10
  • 16
  • Thanks @nick_kh! This is very helpful and fulfil my needs. Just want to double check that the MERGE will only work when there are already some data in a table otherwise there's nothing to compare/update/insert? – user1186842 Oct 25 '21 at 03:04
  • There is always option in `MERGE` clause to supply the three options: `MATCHED`, `NOT MATCHED BY TARGET` and `NOT MATCHED BY SOURCE` flexibly setting the appropriate action, for more details look at the command [reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/dml-syntax?hl=en#merge_statement). Does it close to you question? – Nick_Kh Oct 26 '21 at 06:57