Now Available: Multi Statements Transactions in BigQuery

BigQuery SQL now supports Multi Statement Transactions

BigQuery now supports multi statement transactions inside a single query or even across multiple queries when using sessions [1]. Google announces that multi-statement transaction lets you perform mutating operations, such as inserting or deleting rows on one or more tables, and either commit or roll back the changes atomically. Use Cases can be for example [2]:

• Performing DML mutations on multiple tables as a single transaction. The tables can span multiple datasets or projects.
• Performing mutations on a single table in several stages, based on intermediate computations.

Similar to other relational databases like MySQL or MSSQL, you can now use the transaction functions in BigQuery:

• BEGIN TRANSACTION to start a transaction
• COMMIT TRANSACTION to commit all changes made inside the transaction.
• ROLLBACK TRANSACTION to abandons all changes made inside the transaction.

A transaction is a single unit of work. If a transaction is successful, a commit is performed for all data changes made during the transaction, and they are permanently stored in the database. If errors occur during a transaction that require the transaction to be aborted or rolled back, all data changes are rolled back.

In the code example below, I illustrated how you can use the new functionality in BigQuery:

BEGIN

BEGIN TRANSACTION

 INSERT INTO Person VALUES('Anna', 'Banana','California',22)
 DELETE Person WHERE ID=3

COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

But this is only a superficial description of how these transactions work in theory, all further information can be found on Google’s official documentation:

Although such transactions are more commonly used in relational databases, an example would be a transactional database in which a bank manages its account debits. But you can also use it in BigQuery to perform operations on tables where you have to be careful to fix something. Otherwise, BigQuery is used more as a Data Warehouse. But the new functionality certainly does not hurt.

Other new features in BigQuery that are also relatively fresh and can be interesting for you, are:

Sources and Further Readings

[1] Google, BigQuery release notes (2022)

[2] Google, Multi-statement transactions (2022)