Create Apache Iceberg external tables

Apache Iceberg external tables let you accessApache Icebergtables with finer-grained access control in a read-only format.

Iceberg is an open source table format that supportspetabyte scale data tables. The Iceberg open specificationlets you run multiple query engines on a single copy of data stored in an objectstore. Apache Iceberg external tables (hereafter calledIceberg external tables) supportIceberg specification version 2,including merge-on-read.

As a BigQuery administrator, you can enforce row- andcolumn-level access control including datamasking on tables. For information about how to set up access control at thetable level, seeSet up access control policies. Tableaccess policies are also enforced when you use theBigQuery Storage API as a data source for the table in Dataproc andServerless Spark.

You can create Iceberg external tables in the followingways:

• With BigLake metastore (recommended for Google Cloud).BigLake metastore is a unified, managed, serverless, and scalable metastorethat connects lakehouse data stored in Google Cloud to multipleruntimes, including open source engines (like Apache Spark) andBigQuery.

• With AWS Glue Data Catalog (recommended for AWS).AWS Glue is the recommended method for AWS because it's a centralized metadata repository whereyou define the structure and location of your data stored in various AWS services and providescapabilities like automatic schema discovery and integration with AWS analytics tools.

• With Iceberg JSON metadata files (recommended for Azure).If you use an Iceberg JSON metadata file, then you mustmanually update the latest metadata file whenever there are any table updates.You can use a BigQuery stored procedure forApache Spark to create Iceberg external tables thatreference an Iceberg metadata file.

For a full list of limitations, seeLimitations.

Before you begin

Enable the BigQuery Connection andBigQuery Reservation APIs.

Enable the APIs

• If you use a stored procedure for Spark inBigQuery to create Iceberg external tables, youmust follow these steps:

  1. Create a Spark connection.
  2. Set up access control for that connection.

• To store the Iceberg external table metadata and data files inCloud Storage,create a Cloud Storage bucket. Youneed to connect to your Cloud Storage bucket to access metadata files.To do so, follow these steps:

  1. Create a Cloud resource connection.
  2. Set up access for that connection.

Required roles

To get the permissions that you need to create an Iceberg external table, ask your administrator to grant you the following IAM roles on the project:

• BigQuery Admin (roles/bigquery.admin)
• Storage Object Admin (roles/storage.objectAdmin)

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

These predefined roles contain the permissions required to create an Iceberg external table. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Create tables with BigLake metastore

We recommend creating Iceberg external tables withBigLake metastore.

Create tables with a metadata file

You can create Iceberg external tables with aJSON metadata file.However, this is not the recommended method becauseyou have to manuallyupdate the URI of the JSON metadata fileto keep the Iceberg external table up to date. If the URI is not kept upto date, queries in BigQuery can either fail or provide differentresults from other query engines that directly use an Iceberg catalog.

Iceberg table metadata filesare created in the Cloud Storage bucketthat you specify when you create anIceberg table using Spark.

Select one of the following options:

  CREATE EXTERNAL TABLE myexternal-table  WITH CONNECTION `myproject.us.myconnection`  OPTIONS (         format = 'ICEBERG',         uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"]   )

bqmk
--table
--external_table_definition=TABLE_FORMAT=URI@projects/CONNECTION_PROJECT_ID/locations/CONNECTION_REGION/connections/CONNECTION_ID
PROJECT_ID:DATASET.EXTERNAL_TABLE

Update table metadata

If you use a JSON metadata file to create anIceberg external table, update the table definition to thelatest table metadata. To update the schema or the metadata file, select one ofthe following options:

PATCH https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT_ID/datasets/DATASET/tables/TABLE?autodetect_schema=true

{     "externalDataConfiguration": {      "sourceFormat": "ICEBERG",      "sourceUris": [        "URI"      ]    },    "schema": null  }'

Set up access control policies

You can control access to Iceberg external tables throughcolumn-level security,row-level security,anddata masking.

Query Iceberg external tables

For more information, seeQuery Iceberg data.

Query historical data

You can access snapshots of Iceberg external tables that areretained in your Iceberg metadata by using theFOR SYSTEM_TIME AS OF clause.

Time travel and fail-safe data retention windowsaren't supported for any external tables.

Data mapping

BigQuery converts Iceberg data types to BigQuerydata types as shown in the following table:

Limitations

Iceberg external tables tables haveexternal table limitations andthe following limitations:

• Tables using merge-on-read have the following limitations:

  • Each data file can be associated with up to 10,000 delete files.
  • No more than 100,000 equality deletes can be applied to a data file.
  • You can work around these limitations by either compacting delete filesfrequently, or creating a view on top of the Iceberg table that avoidsfrequently mutated partitions.

• BigQuery supports manifest pruning using allIceberg partitiontransformation functions.For information about how to prune partitions, seeQuery partitioned tables. Queriesreferencing Iceberg external tables must contain literals inpredicates compared to columns that are partitioned.

• Only Apache Parquet data files are supported.

Merge-on-read costs

On-demand billing for merge-on-read data is the sum of scans of the following data:

• All logical bytes read in the data file (including rows that are marked asdeleted by position and equality deletes).
• Logical bytes read loading the equality delete and position deletes filesto find the deleted rows in a data file.

Require partition filter

You can require the use of predicate filters by enabling therequire partition filter option for your Iceberg table. If you enable this option, attempts to query the table without specifying aWHERE clause that aligns with each manifest file will produce the following error:

Cannot query over tableproject_id.dataset.table without afilter that can be used for partition elimination.

Each manifest file requires at least one predicate suitable for partition elimination.

You can enable therequire_partition_filter in the following ways whilecreating an Iceberg table :

  CREATE EXTERNAL TABLETABLE  WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`  OPTIONS (         format = 'ICEBERG',         uris = [URI],         require_partition_filter =true   )

bqmk\--table\--external_table_definition=ICEBERG=URI@projects/CONNECTION_PROJECT_ID/locations/CONNECTION_REGION/connections/CONNECTION_ID\PROJECT_ID:DATASET.EXTERNAL_TABLE\--require_partition_filter

You can also update your Iceberg table to enable the require partition filter.

If you don't enable therequire partition filter option when you create the partitioned table, you can update the table to add the option.

bqupdate--require_partition_filterPROJECT_ID:DATASET.TABLE

What's next

• Learn aboutstored procedure for Spark.
• Learn aboutaccess control policies.
• Learn aboutrunning queries in BigQuery.
• Learn about thesupported statements and SQL dialects inBigQuery.