Updating and deleting
BigQuery tables are made for appending rows, but the library supports updates and deletes by primary key too:
db.fluent().update()writes whole rows: each replaces the row with its primary key, or is inserted if there is none;db.fluent().delete()deletes the rows with the given primary keys.
Both are written through BigQuery change data capture (CDC), as upserts and
deletes on the table’s default write stream. There is no UPDATE or MERGE statement to run,
BigQuery applies the changes itself.
CDC or DML queries
BigQuery has its own UPDATE, DELETE and MERGE statements too, and the library runs them as
queries, see DML and DDL. The two do not mix on one table: BigQuery
does not support mutating DML on a table while CDC changes are streamed to it. So the choice is
one for the whole table.
Use update() and delete() when:
- you change rows by primary key, and have the whole row for an update;
- changes come often, one by one or as a steady stream, like mirroring an OLTP database. A CDC change is a Storage Write API write, so it does not wait in the DML queue below;
- a change can arrive late or twice, sequence numbers put them in order;
- queries can read data up to
max_stalenessold, or pay for merging the changes at query time.
Use a DML query when:
- the rows to change are given by a condition, for example
DELETE FROM shop.orders WHERE created < @cutoff; - you change some columns and keep the rest, or compute the new value from the old one or from
another table:
SET total = total * 2, aMERGEfrom a staging table, etc.; - the table has no primary key, or its key is not unique;
- a change must apply to all its rows or none of them. Each DML statement is an implicit transaction, committed when it succeeds. CDC writes go through the default stream, which has no atomic commit, see the limits of CDC tables;
- you need to know how many rows matched. A DML statement returns
num_dml_affected_rows, a CDC write returnsrows_written, the rows sent, whether a row with the key existed or not; - the change is a rare bulk rewrite or cleanup. Google’s
best practices
describe
UPDATEandDELETEas “oriented towards periodic rewrites of your data”.
Be aware not to run DML for every single change. From BigQuery’s DML documentation:
- up to 2 mutating statements (
UPDATE,DELETE,MERGE) run on a table at once, up to 20 more wait asPENDING, and the next one fails withToo many DML statements outstanding against table; - two statements running at once that change the same partition conflict, and BigQuery reruns the failed one up to three times.
On demand, an UPDATE or DELETE is billed for the bytes the statement reads plus the size of
the table before the change, or of the partitions it changes for a partitioned table, as the
on-demand query size calculation
describes. Every statement pays that size, even one that changes one row.
CDC has its own costs, the background applying and the merge at query time, see the
limits of CDC tables.
On a table that takes CDC changes, a change by condition goes through CDC as well: query the keys that match, then delete them by key:
use bigquery::*;
use serde::Deserialize;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
#[derive(Deserialize)]
struct OrderId {
id: i64,
}
async fn example(db: BigQueryDb) -> BigQueryResult<()> {
let cancelled: Vec<OrderId> = db
.fluent()
.query("SELECT id FROM shop.orders WHERE status = @status")
.param("status", "cancelled")
.obj::<OrderId>()
.query()
.await?;
db.fluent()
.delete()
.from(SHOP.table(ORDERS))
.keys(cancelled.iter().map(|order| order.id))
.execute()
.await?;
Ok(())
}
The table
The table needs a primary key, declared with schema():
use bigquery::*;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
struct Order {
id: i64,
customer: String,
status: String,
note: Option<String>,
}
async fn example(db: BigQueryDb) -> BigQueryResult<()> {
db.fluent()
.schema()
.table(SHOP.table(ORDERS))
.columns(|columns| {
columns.fields([
columns.field(path!(Order::id)).int64().required(),
columns.field(path!(Order::customer)).string(),
columns.field(path!(Order::status)).string(),
columns.field(path!(Order::note)).string(),
])
})
.primary_key([path!(Order::id)])
.sync()
.await?;
Ok(())
}
BigQuery never enforces the key, it only uses it to find the row a change is for. So it is up to you that the key is unique.
Updates
use bigquery::*;
use serde::Serialize;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
#[derive(Serialize)]
struct Order {
id: i64,
customer: String,
status: String,
note: Option<String>,
}
async fn example(db: BigQueryDb, orders: Vec<Order>) -> BigQueryResult<()> {
let order = Order {
id: 42,
customer: "Ada".to_string(),
status: "shipped".to_string(),
note: None,
};
db.fluent()
.update()
.in_table(SHOP.table(ORDERS))
.object(&order)
.execute()
.await?;
// Many rows through one writer
db.fluent()
.update()
.in_table(SHOP.table(ORDERS))
.objects(&orders)
.execute()
.await?;
Ok(())
}
An update replaces the whole row. There is no field mask, so a column you leave out or set to
None becomes NULL. To change one column, write the whole row with the new value. Be aware
that BigQuery does not support UPDATE, DELETE or MERGE statements on a table while CDC
changes are streamed to it, so a DML query is not a way around this.
Without sequence numbers, of two rows with one key the row BigQuery ingested last wins, see ordering changes.
Deletes
A delete needs only the primary key. .key(..) takes the key value itself: a plain value for a
key of one column, a tuple in the key’s column order for a key of several:
use bigquery::*;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
const ORDER_LINES: BigQueryTableId = BigQueryTableId::from_static("order_lines");
async fn example(db: BigQueryDb) -> BigQueryResult<()> {
db.fluent()
.delete()
.from(SHOP.table(ORDERS))
.key(42)
.execute()
.await?;
// The primary key of order_lines is (order_id, line)
db.fluent()
.delete()
.from(SHOP.table(ORDER_LINES))
.keys([(42, "line-1"), (42, "line-2")])
.execute()
.await?;
Ok(())
}
The library reads the key’s columns from the table’s metadata, one GetTable call per
execute(), and writes rows with only those columns. A table without a primary key, or a tuple
with another number of values than the key has columns, fails with InvalidParametersError
before anything is written. A value for a key of one column is written as it is, so a Vec<u8>
works for a BYTES key, and a value of the wrong type fails at the write with
SerializeError.
A row works as well, .object(..) and .objects(..) take a struct of just the key, or the whole
row:
use bigquery::*;
use serde::Serialize;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
#[derive(Serialize)]
struct OrderKey {
id: i64,
}
async fn example(db: BigQueryDb) -> BigQueryResult<()> {
db.fluent()
.delete()
.from(SHOP.table(ORDERS))
.object(&OrderKey { id: 42 })
.execute()
.await?;
db.fluent()
.delete()
.from(SHOP.table(ORDERS))
.objects([OrderKey { id: 1 }, OrderKey { id: 2 }])
.execute()
.await?;
Ok(())
}
The columns the key struct or .key(..) leaves out must be NULLABLE. A row without a
REQUIRED column does not serialize, so for a table with other REQUIRED columns pass the whole
row instead.
Ordering changes
Without sequence numbers, of the changes to one key the one BigQuery ingested last wins. That is fine for one producer at a time. If a change can arrive late or twice, give it a sequence number, the highest one wins:
use bigquery::*;
use serde::Serialize;
const SHOP: BigQueryDatasetId = BigQueryDatasetId::from_static("shop");
const ORDERS: BigQueryTableId = BigQueryTableId::from_static("orders");
#[derive(Serialize)]
struct Order {
id: i64,
customer: String,
status: String,
note: Option<String>,
}
async fn example(db: BigQueryDb, order: Order, orders: Vec<Order>) -> BigQueryResult<()> {
db.fluent()
.update()
.in_table(SHOP.table(ORDERS))
.object(&order)
.sequence_number(BigQueryChangeSequenceNumber::from(1041))
.execute()
.await?;
// One number for the rows of one source transaction
db.fluent()
.update()
.in_table(SHOP.table(ORDERS))
.objects(&orders)
.sequence_number(BigQueryChangeSequenceNumber::from(1042))
.execute()
.await?;
db.fluent()
.delete()
.from(SHOP.table(ORDERS))
.key(42)
.sequence_number(BigQueryChangeSequenceNumber::from(1043))
.execute()
.await?;
Ok(())
}
.sequence_number(..) gives the same number to every row of the call. Between changes to one key
with the same number, the one BigQuery ingested last wins. For changes each with its own number,
use .changes(..) on an insert, see change data capture.
Be aware that sequence numbers are a choice for the whole table: once a table takes changes with sequence numbers, send one with every change to it. Mixing changes with and without them gives an unpredictable order.
When the changes show up
BigQuery applies the changes in the background, and the table’s max_staleness option says how
old the applied data may be:
- without
max_staleness, a query merges the changes not applied yet at query time, so it sees every change as soon asexecute()returns, and pays for that merge; - with
max_staleness, say 10 minutes, a query may read data up to 10 minutes old, so a read right after an update may not show it yet.
More on max_staleness, its costs and the limits of CDC tables in
change data capture.
Each execute() opens a CDC writer and finishes it, which costs a round trip or so. For a
steady stream of changes keep one writer open with db.create_cdc_writer(..) instead.
Full example available here.