Modify data with DML statements

Modifying data lets you update, delete, merge, and truncate records in your Apache Iceberg tables in the Lakehouse runtime catalog.

When BigQuery DML is enabled on your table, you can run standard DML statements from BigQuery alongside open source engines like Spark and Trino, achieving full write interoperability on a single copy of data stored in Cloud Storage.

Before you begin

  1. Verify that billing is enabled for your Google Cloud project.

  2. Enable the BigLake API.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the API

  3. Set up the Lakehouse runtime catalog with the Apache Iceberg REST catalog endpoint.

Required roles

To get the permissions that you need to modify data in a table, ask your administrator to grant you the following IAM roles on your project and storage bucket:

  • Write table data in credential vending mode: BigLake Editor (roles/biglake.editor) - the project
  • Write table data in non-credential vending mode:
    • BigLake Editor (roles/biglake.editor) - the project
    • Storage Object User (roles/storage.objectUser) - the Cloud Storage bucket

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Table capabilities and support

When using tables in the Lakehouse runtime catalog, it's helpful to understand the different table types and their opt-in capabilities. To learn more about using Apache Iceberg tables specifically, see Overview of Apache Iceberg tables.

Supported Iceberg tables

Only Apache Iceberg V2 (GA) and V3 (Preview) tables are supported. Iceberg V1 tables aren't supported. To upgrade existing V1 tables, see Upgrade Iceberg V1 tables to V2.

Use table options (Preview)

You can opt in to use BigQuery managed capabilities, such as BigQuery Data Manipulation Language (DML) and automatic table management, by configuring specific table properties. These features are enabled in different ways depending on where the table is created:

  • From BigQuery: BigQuery DML and automatic table management are enabled by default.
  • From open source engines: To opt-in, you must explicitly configure table properties. See Configure table options for more information.

Update data

Update existing rows in the table:

Spark 

ALTER TABLE TABLE_NAME SET TBLPROPERTIES ('gcp.biglake.bigquery-dml.enabled' = true);

UPDATE TABLE_NAME SET data = 'updated row' WHERE id = 1;

Trino 

ALTER TABLE TABLE_NAME SET PROPERTIES "gcp.biglake.bigquery-dml.enabled" = 'true';

UPDATE TABLE_NAME SET data = 'updated row' WHERE id = 1;

BigQuery 

UPDATE `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
SET data = "updated row"
WHERE id = 1;

Replace the following:

  • PROJECT_ID: your Google Cloud project ID.
  • CATALOG_ID: your Lakehouse runtime catalog ID.
  • NAMESPACE: your Iceberg namespace name.
  • TABLE_NAME: the name of your Iceberg table.

Delete data

Delete specific rows from the table:

Spark 

ALTER TABLE TABLE_NAME SET TBLPROPERTIES ('gcp.biglake.bigquery-dml.enabled' = true);

DELETE FROM TABLE_NAME WHERE id = 1;

Trino 

ALTER TABLE TABLE_NAME SET PROPERTIES "gcp.biglake.bigquery-dml.enabled" = 'true';

DELETE FROM TABLE_NAME WHERE id = 1;

BigQuery 

DELETE FROM `PROJECT_ID.CATALOG_ID.NAMESPACE.TABLE_NAME`
WHERE id = 1;

Merge data

Merge data from a source table into your target Iceberg table:

Spark 

ALTER TABLE TARGET_TABLE SET TBLPROPERTIES ('gcp.biglake.bigquery-dml.enabled' = true);

MERGE INTO TARGET_TABLE t
USING SOURCE_TABLE s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET t.data = s.data
WHEN NOT MATCHED THEN
  INSERT (id, data) VALUES (s.id, s.data);

Trino 

ALTER TABLE TARGET_TABLE SET PROPERTIES "gcp.biglake.bigquery-dml.enabled" = 'true';

MERGE INTO TARGET_TABLE t
USING SOURCE_TABLE s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET t.data = s.data
WHEN NOT MATCHED THEN
  INSERT (id, data) VALUES (s.id, s.data);

BigQuery 

MERGE `PROJECT_ID.CATALOG_ID.NAMESPACE.TARGET_TABLE` t
USING `PROJECT_ID.CATALOG_ID.NAMESPACE.SOURCE_TABLE` s
ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET data = s.data
WHEN NOT MATCHED THEN
  INSERT (id, data) VALUES (id, data);

What's next