BigQuery tables for Apache Iceberg

For support during the preview, email bigquery-tables-for-apache-iceberg-help@google.com.

BigQuery tables for Apache Iceberg, (hereafter, Iceberg tables) provide the foundation for building open-format lakehouses on Google Cloud. Iceberg tables offer the same fully managed experience as BigQuery tables, but store data in customer-owned storage buckets using Parquet to be interoperable with Iceberg open table formats.

Iceberg tables support the following features:

• Table mutations using GoogleSQL data manipulation language (DML).
• Unified batch and high throughput streaming using the Storage Write API through BigLake connectors for Spark, Dataflow, and many other engines.
• Schema evolution, which lets you add, drop, and rename columns to suit your needs. This feature also lets you change an existing column's data type and column mode. For more information, see type conversion rules.
• Automatic storage optimization, including adaptive file sizing, automatic clustering, garbage collection, and metadata optimization.
• *Row-level security, column-level security, and data masking.

Architecture

Iceberg tables bring the convenience of BigQuery resource management to tables that reside in your own cloud buckets. Iceberg tables let you use BigQuery on these tables without moving the data out of buckets that you control.

The following diagram shows the managed table architecture at a high level:

This table management has the following implications on your bucket:

• BigQuery creates new data files in the bucket in response to write requests and background storage optimizations, such as DML statements and streaming.
• When you delete a managed table in BigQuery, BigQuery doesn't delete associated data files. You must confirm the deletion by deleting the files and any exported table metadata from the bucket manually.
• Iceberg tables incur no BigQuery storage costs. For more information, see Billing.

Creating a Iceberg table is similar to creating BigQuery tables. Because it stores data in open formats on Cloud Storage, it has more options for the following:

• Specifying the Cloud resource connection with WITH CONNECTION to configure the connection credentials for BigLake to access Cloud Storage.
• Specifying the file format of data storage with file_format. PARQUET is supported in Preview.
• Specifying the open-source metadata table format with table_format. ICEBERG is supported in Preview.

Best practices

It is crucial to modify Iceberg tables solely through BigQuery. Any direct changes or additions to the bucket could lead to data loss or unrecoverable errors. The following table expands on possible scenarios:

Billing

The following features are charged using the existing published pricing:

• Cloud Storage pricing for all data stored in Cloud Storage buckets, data processing done by Cloud Storage, and network usage for the amount of data read from your bucket.
• BigQuery compute pricing for queries, DML, and background storage optimization (including clustering, coalescence, and garbage collection).
  • Charges using reservations (slots) follow existing slot pricing.
  • Charges using the on-demand stock-keeping units (SKUs) follow existing on-demand pricing. For more information, see BigLake costs.
• Batch load and Extract compute are charged using either on-demand SKUs or reservations (slots).
• Storage Write API pricing for reading from Spark through the Read API.
• Storage Write API pricing for streaming.

Iceberg table workflows

The following sections describe how to create, load, manage, and query managed tables.

Before you begin

Before creating and using Iceberg tables, 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.

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 create Iceberg tables:
  • BigQuery Data Owner (roles/bigquery.dataOwner) on your project
  • BigQuery Connection Admin (roles/bigquery.connectionAdmin) on your project
• To query Iceberg tables:
  • BigQuery Data Viewer (roles/bigquery.dataViewer) on your project
  • BigQuery User (roles/bigquery.user) on your project
• For the connection service account to read and write data in Cloud Storage:
  • Storage Admin (roles/storage.admin) on the bucket
  • Storage Legacy Object Reader (roles/storage.legacyObjectReader) on the bucket
  • Storage Legacy Bucket Writer (roles/storage.legacyBucketWriter) on the bucket
  • Storage Object Admin (roles/storage.objectAdmin) 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 permissions section:

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

Create Iceberg tables

To create a Iceberg table, select one of the following methods:

CREATE TABLE [PROJECT_NAME.]DATASET_NAME.TABLE_NAME (
COLUMN DATA_TYPE[, ...]
)
CLUSTER BY CLUSTER_COLUMN_LIST
WITH CONNECTION CONNECTION_NAME
OPTIONS (
file_format = 'PARQUET',
table_format = 'ICEBERG',
storage_uri = 'STORAGE_URI');

bq --project_id=PROJECT_NAME mk \
    --file_format=PARQUET \
    --table_format=ICEBERG \
    --connection_id=CONNECTION_NAME \
    --storage_uri=STORAGE_URI \
    --schema=COLUMN_NAME:DATA_TYPE[, ...] \
    --clustering_fields=CLUSTER_COLUMN_LIST \
    MANAGED_TABLE_NAME

{
"tableReference": {
  "tableId": "TABLE_NAME"
},
"biglakeConfiguration": {
  "connectionId": "CONNECTION_NAME",
  "fileFormat": "PARQUET",
  "tableFormat": "ICEBERG",
  "storageUri": "STORAGE_URI"
},
"schema": {
  "fields": [
    {
      "name": "COLUMN_NAME",
      "type": "DATA_TYPE"
    }
    [, ...]
  ]
}
}

Import data into Iceberg table

The following sections describe how to import data from various table formats into Iceberg tables.

Fast load from Parquet files

The copy_files_only option lets you load data faster by copying your existing Parquet files, instead of reading the content and rewriting the content as new files. Fast load uses less compute capacity compared to a regular file load. Parquet files must be compatible with the Apache Iceberg specification and have complete column statistics. Fast load doesn't detect invalid values (such as out-of-range timestamps) in files because the files are not read and reprocessed. For more information on loading Parquet files, see Loading Parquet data into a new table.

To fast load flat Parquet files into an existing Iceberg table, use the bq load command:

bq load \
    --copy_files_only \
    --source_format=PARQUET \
    DATASET_NAME.TABLE_NAME \
    PATH_TO_SOURCE

Replace the following:

• DATASET_NAME: the dataset containing your Iceberg table.
• TABLE_NAME: the name of the Iceberg table into which you're loading data.
• PATH_TO_SOURCE: a fully qualified Cloud Storage URI or a comma-separated list of URIs. Wildcards are also supported. For example, gs://mybucket/mydata*.parquet.

Standard load data from flat files

Iceberg tables use BigQuery load jobs to load external files into Iceberg tables. If you have an existing Iceberg tables, follow the bq load CLI guide or the LOAD SQL guide to load external data. After loading the data, new Parquet files are written into the STORAGE_URI/data folder.

If the prior instructions are used without an existing Iceberg table, a BigQuery table is created instead.

See the following for tool-specific examples of batch loads into managed tables:

LOAD DATA INTO MANAGED_TABLE_NAME
FROM FILES (
uris=['STORAGE_URI'],
format='FILE_FORMAT');

bq load \
  --source_format=FILE_FORMAT \
  MANAGED_TABLE \
  STORAGE_URI

Standard load from Hive-partitioned files

You can load Hive-partitioned files into Iceberg tables using standard BigQuery load jobs. For more information, see Loading externally partitioned data.

Load streaming data from Pub/Sub

You can load streaming data into Iceberg tables by using a Pub/Sub BigQuery subscription.

Export data from Iceberg tables

The following sections describe how to export data from Iceberg tables into various table formats.

Export data into flat formats

To export a Iceberg table into a flat format, use the EXPORT DATA statement and select a destination format. For more information, see Exporting data.

Export metadata as an Iceberg table

To export a Iceberg table's metadata as an Iceberg table, select one of the following methods:

bq query \
  --nouse_legacy_sql \
  --nouse_cache "EXPORT TABLE METADATA FROM TABLE_NAME"

spark-sql \
  --packages org.apache.iceberg:iceberg-spark-runtime-ICEBERG_VERSION_NUMBER \
  --conf spark.sql.catalog.CATALOG_NAME=org.apache.iceberg.spark.SparkCatalog \
  --conf spark.sql.catalog.CATALOG_NAME.type=hadoop \
  --conf spark.sql.catalog.CATALOG_NAME.warehouse='BUCKET_PATH' \

# Queries the table
spark-sql> SELECT * FROM CATALOG_NAME.FOLDER_NAME;

Modify Iceberg tables

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

Pricing

Iceberg table pricing consists of three separate components:

Storage

Iceberg table store all data in storage_name. You are charged for all data stored, including historical table data. Cloud Storage data processing and transfer charges may also apply, as applicable. There are no BigQuery-specific storage fees. For more information, see Cloud Storage Pricing.

Storage optimization

Iceberg tables require storage optimization operations, such as file coalescing and re-clustering. These optimization operations use Enterprise Edition pay as you go slots, and don't use existing BACKGROUND reservations.

Data export operations taking place while streaming through the BigQuery Storage Write API are included in Storage Write API pricing and are not charged as background maintenance. For more information, see Data ingestion pricing.

Queries and jobs

Similar to BigQuery tables, you are charged for queries and bytes read (per TiB) if you are using BigQuery on-demand pricing, or slot consumption (per slot hour) if you are using BigQuery capacity compute pricing.

BigQuery pricing also applies to the BigQuery Storage Read API and the BigQuery Storage Write API.

Load and export operations (such as EXPORT METADATA) use Enterprise Edition pay as you go slots. This differs from BigQuery tables, which are not charged for these operations. If PIPELINE reservations with Enterprise or Enterprise Plus slots are available, load and export operations preferentially use these reservation slots instead.

Limitations

Iceberg tables have the following limitations:

• Iceberg tables don't support renaming operations.
• Iceberg tables don't support the following table schema:
  • Empty schema
  • Schema with INTERVAL, JSON, RANGE, or GEOGRAPHY data types.
  • Schema with field collations.
  • Schema with default value expressions.
• EXPORT METADATA doesn't support tables containing the following:
  • GEOGRAPHY data types
  • BIGNUMERIC or NUMERIC data types with precision greater than 38 places.
• Iceberg tables don't support the following schema evolution cases:
  • NUMERIC to FLOAT type coercions
  • INT to FLOAT type coercions
  • Adding new nested fields to an existing RECORD columns using SQL DDL statements
• Iceberg tables display a 0-byte storage size when queried by the console or APIs.
• Iceberg tables don't support materialized views.
• Iceberg tables don't support multi-statement transactions.
• Iceberg tables don't support table copies, clones, or snapshots.
• Iceberg tables don't support change data capture (CDC) updates.
• Iceberg tabled don't support managed disaster recovery
• Iceberg tables don't support partitioning. Consider clustering as an alternative.
• Iceberg tables don't support row-level security.
• Iceberg tables don't support time travel.
• Iceberg tables don't support fail-safe windows.
• Iceberg tables don't support extract jobs.
• The INFORMATION_SCHEMA.TABLE_STORAGE view doesn't include Iceberg tables.
• Iceberg tables are not supported as query result destinations.
• CREATE OR REPLACE doesn't support replacing standard tables with Iceberg tables, or Iceberg tables with standard tables.
• CREATE TABLE COPY statements don't support Iceberg tables.
• ALTER TABLE RENAME TO statements don't support Iceberg tables.
• LOAD DATA OVERWRITE doesn't support Iceberg tables as an overwrite destination.
• TRUNCATE TABLE doesn't support Iceberg tables. There are two alternatives:
  • CREATE OR REPLACE TABLE, using the same table creation options.
  • DELETE FROM table WHERE true
• The APPENDS table-valued function (TVF) doesn't support Iceberg tables.
• Batch loading into Iceberg tables doesn't support:
  • Adding columns to existing tables.
  • Relaxing the column modes or types.
  • Creating new Iceberg tables.
  • Overwriting a table (also known as writeDisposition WRITE_TRUNCATE).
• Iceberg exports in Apache Spark don't contain recently streamed data in write-optimized storage.
• Fast load doesn't support files with flexible column names.