Modify, partition, and use transactions with BigLake tables for Apache Iceberg in BigQuery

The following sections describe how to modify, use multi-statement transactions, and use partitioning with managed tables using BigLake tables for Apache Iceberg in BigQuery (hereafter BigLake Iceberg tables in BigQuery ).

Before you begin

Understand the different types of BigLake tables and the implications of using them, in the BigLake table overview .
Before modifying BigLake Iceberg tables in BigQuery, ensure that you have set up a Cloud resource connection to a storage bucket. Your connection needs write permissions on the storage bucket, as specified in the following Required roles section. For more information about required roles and permissions for connections, see Manage connections .

Required roles

To get the permissions that you need to let BigQuery manage tables in your project, ask your administrator to grant you the following IAM roles:

To modify and query data:
- BigQuery Data Editor ( roles/bigquery.dataEditor ) on your project
- BigQuery User ( roles/bigquery.user ) on your project
Grant the connection service account the following roles so it can read and write data in Cloud Storage:
- Storage Object User ( roles/storage.objectUser ) on the bucket
- Storage Legacy Bucket Reader ( roles/storage.legacyBucketReader ) on the bucket

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

These predefined roles contain the permissions required to let BigQuery manage tables in your project. To see the exact permissions that are required, expand the Required permissionssection:

Required permissions

The following permissions are required to let BigQuery manage tables in your project:

All:
- bigquery.connections.delegate on your project
- bigquery.jobs.create on your project
- bigquery.readsessions.create on your project
- bigquery.tables.update on your project
- bigquery.tables.get on your project
- bigquery.tables.getData on your project
- storage.buckets.get on your bucket
- storage.objects.create on your bucket
- storage.objects.delete on your bucket
- storage.objects.get on your bucket
- storage.objects.list on your bucket

You might also be able to get these permissions with custom roles or other predefined roles .

Modify BigLake Iceberg tables in BigQuery

To modify a BigLake Iceberg table in BigQuery, follow the steps shown in Modifying table schemas .

Use multi-statement transactions

To gain access to multi-statement transactions for BigLake Iceberg tables in BigQuery, fill out the sign-up form .

Use partitioning

To gain access to partitioning for BigLake Iceberg tables in BigQuery, fill out the sign-up form .

You partition a table by specifying a partition column, which is used to segment the table. The following column types are supported for BigLake Iceberg tables in BigQuery:

DATE
DATETIME
TIMESTAMP

Partitioning a table on a DATE , DATETIME , or TIMESTAMP column is known as time-unit column partitioning . You choose whether the partitions have hourly, daily, monthly, or yearly granularity .

BigLake Iceberg tables in BigQuery also support clustering and combining clustered and partitioned tables .

Partitioning limitations

All BigQuery partitioned table limitations apply.
Partitioning column types other than DATE , DATETIME , or TIMESTAMP aren't supported.
Partition expiration isn't supported.
Partition evolution isn't supported.

Create a partitioned BigLake Iceberg table in BigQuery

To create a partitioned BigLake Iceberg table in BigQuery, follow the instructions to create a standard BigLake Iceberg table in BigQuery , and include one of the following, depending on your environment:

Modify and query partitioned BigLake Iceberg tables in BigQuery

BigQuery data manipulation language (DML) statements and queries for partitioned BigLake Iceberg tables in BigQuery are the same as for standard BigLake Iceberg tables in BigQuery. BigQuery automatically scopes the job to the right partitions, similar to Iceberg hidden partitioning . Additionally, any new data that you add to the table is automatically partitioned.

You can also query partitioned BigLake Iceberg tables in BigQuery with other engines in the same way as standard BigLake Iceberg tables in BigQuery. We recommend enabling metadata snapshots for the best experience.

For enhanced security, partitioning information for BigLake Iceberg tables in BigQuery is decoupled from the data path and is managed entirely by the metadata layer.