0

I've created a cloud function using Python that receives some data and inserts it into a BigQuery table. Currently, it uses the insert_rows method to insert a new row.

row = client.insert_rows(table, row_to_insert)  # API request to insert row

The problem is that I already have data with unique primary keys in the table, and I just need one measurement value to be updated in those rows.

I would like it to update or replace that row instead of creating a new one (assuming the primary keys in the table and input data match). Is this possible?

Hyena
  • 11
  • 1
  • 4
  • You can see this [answer](https://stackoverflow.com/questions/48177241/google-bq-how-to-upsert-existing-data-in-tables) about upsert in BQ – Ka Boom Jul 01 '21 at 01:15

1 Answers1

0

BigQuery is not designed for transactional data, it prefer append-only. Please refer documentation on Bigquery DML quota. That means you can only apply a limited number of DML commands on a table per day.

Updating rows will not work on BQ tables.

Recommended solution:-

  1. Create 2 tables (T1 & T2).
  2. Insert All transactional records on T1 table, through your existing Function.
  3. Then Write a BQ-SQL to read most recent record from T1 table and then insert most recent records to T2 table
Vibhor Gupta
  • 670
  • 7
  • 16