I'm designing a BigQuery job in python that updates and inserts into several tables. I thought of two ways to achieve that:
execute a query job and save the result into a temporary table with an update/insert indicator and process them after. But it's no clear how to update with python libraries.
load the whole data into a new partitioned table and skip updates/inserts. It takes a more space then I would like but partition expires in few days anyway.
Am I missing something? Is there other way to achieve this?
You can simply use Data Manipulation Language (DML) statements instead of SQL queries when using the Google BigQuery API.
For instance, in order to update specific rows in the following table:
Inventory
+-------------------+----------+--------------------+
| product | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher | 30 | NULL |
| dryer | 30 | NULL |
| front load washer | 30 | NULL |
| microwave | 30 | NULL |
+-------------------+----------+--------------------+
you could use the following code:
from google.cloud import bigquery
client = bigquery.Client()
dml_statement = (
"UPDATE dataset.Inventory "
"SET quantity = quantity - 10 "
"WHERE product like '%washer%'")
query_job = client.query(dml_statement) # API request
query_job.result() # Waits for statement to finish
obtaining the following results:
Inventory
+-------------------+----------+--------------------+
| product | quantity | supply_constrained |
+-------------------+----------+--------------------+
| dishwasher | 20 | NULL |
| dryer | 30 | NULL |
| front load washer | 20 | NULL |
| microwave | 30 | NULL |
+-------------------+----------+--------------------+
It took me a while to find among the many Google documents on the web, so worth to have it here, for reference. The information is here.
And this is a minimal example that should be easy to adapt to any use case:
import google.cloud.bigquery.dbapi as bq
try:
con = bq.connect()
cursor = con.cursor()
query = "<your sql command goes here>"
cursor.execute(query)
con.commit()
con.close()
except Exception as e:
str(e)
The method to update fields in python is implemented in idiomatic library, it's called update_table(). It calls into BigQuery API's patch method.