Create Apache Iceberg BigLake tables
With BigLake, you can access Iceberg tables with finer-grained access control. To do so, you must first create an Iceberg BigLake table.
Iceberg is an open source table format that supports petabyte scale data tables. The Iceberg open specification lets you run multiple query engines on a single copy of data stored in an object store.
As a BigQuery administrator, you can enforce row- and column-level access control including data masking on tables. For information about how to set up access control at the table level, see Set up access control policies. Table access policies are also enforced when you use the BigQuery Storage API as a data source for the table in Dataproc and Serverless Spark. BigLake tables provide additional integrations with other BigQuery services. For a full list of available integrations, see Introduction to BigLake tables.
You can create Iceberg BigLake tables in the following ways:
With BigLake Metastore (recommended for Google Cloud). BigLake Metastore is a custom Iceberg catalog. Using BigLake Metastore is the recommended method for Google Cloud because it enables synchronization of tables between Spark and BigQuery workloads. To do this, you can use a BigQuery stored procedure for Apache Spark to initialize BigLake Metastore and create the Iceberg BigLake table. However, schema updates still require you to run an update query in BigQuery.
With AWS Glue Data Catalog (recommended for AWS). AWS Glue is the recommended method for AWS because it's a centralized metadata repository where you define the structure and location of your data stored in various AWS services and provides capabilities 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 must manually update the latest metadata file whenever there are any table updates. You can use a BigQuery stored procedure for Apache Spark to create Iceberg BigLake tables that reference an Iceberg metadata file. To avoid this, you can use BigLake Metastore for Google Cloud or AWS Glue Data Catalog for AWS.
For a full list of limitations, see Limitations.
Before you begin
Enable the BigQuery Connection, BigQuery Reservation, and BigLake APIs.
If you use a stored procedure for Spark in BigQuery to create Iceberg BigLake tables, you must follow these steps:
To store the Iceberg BigLake table metadata and data files in Cloud Storage, create a Cloud Storage bucket. You need to connect to your Cloud Storage bucket to access metadata files. To do so, follow these steps:
If you're using BigLake Metastore, install the appropriate Iceberg Custom Catalog for Apache Spark. Select the Custom Catalog version that best matches the Iceberg version you are using.
Iceberg 1.5.0
: gs://spark-lib/biglake/biglake-catalog-iceberg1.5.0-0.1.1-with-dependencies.jarIceberg 1.2.0
: gs://spark-lib/biglake/biglake-catalog-iceberg1.2.0-0.1.1-with-dependencies.jarIceberg 0.14.0
: gs://spark-lib/biglake/biglake-catalog-iceberg0.14.0-0.1.1-with-dependencies.jar
Required roles
To ensure that the caller of the BigLake API has the necessary permissions to create a BigLake table, ask your administrator to grant the caller of the BigLake API the following IAM roles on the project:
-
BigQuery Admin (
roles/bigquery.admin
) -
BigLake Admin (
roles/biglake.admin
)
For more information about granting roles, see Manage access to projects, folders, and organizations.
These predefined roles contain the permissions required to create a BigLake table. To see the exact permissions that are required, expand the Required permissions section:
Required permissions
The following permissions are required to create a BigLake table:
-
bigquery.tables.create
-
bigquery.connections.delegate
-
bigquery.jobs.create
Your administrator might also be able to give the caller of the BigLake API these permissions with custom roles or other predefined roles.
Additionally, to allow BigQuery users to query the table, the
service account associated with the connection must have the BigLake Viewer
(roles/biglake.viewer
) role and access to the Cloud Storage bucket
that contains that data.
To create Iceberg BigLake tables with BigLake Metastore, the caller of the BigLake API changes. You must grant the Dataproc or the Spark service account access to the Cloud Storage bucket that contains that data:
If you run Spark on Dataproc, then the caller in Dataproc is Dataproc service account.
If you run Spark procedures in BigQuery, then the caller is a Spark connection service account.
Create tables with BigLake Metastore
We recommend creating Iceberg BigLake tables with BigLake Metastore. You can use Apache Spark to create these tables. A convenient way to do this is using BigQuery stored procedures for Spark by following these steps:
Go to the BigQuery page.
In the Explorer pane, click the connection in the project that you used to create the connection resource.
To create a stored procedure for Spark, click
Create stored procedure.In the query editor, modify the sample code for initializing BigLake Metastore and creating an Iceberg BigLake table by using the
CREATE PROCEDURE
statement that appears:# Creates a stored procedure that initializes BLMS and database. # Creates a table in the database and populates a few rows of data. CREATE OR REPLACE PROCEDURE iceberg_demo.iceberg_setup_3_3 () WITH CONNECTION `PROCEDURE_CONNECTION_PROJECT_ID.PROCEDURE_CONNECTION_REGION.PROCEDURE_CONNECTION_ID` OPTIONS(engine="SPARK", jar_uris=["gs://spark-lib/biglake/biglake-catalog-iceberg1.2.0-0.1.0-with-dependencies.jar"], properties=[ ("spark.jars.packages","org.apache.iceberg:iceberg-spark-runtime-3.3_2.12:1.2.0"), ("spark.sql.catalog.CATALOG", "org.apache.iceberg.spark.SparkCatalog"), ("spark.sql.catalog.CATALOG.catalog-impl", "org.apache.iceberg.gcp.biglake.BigLakeCatalog"), ("spark.sql.catalog.CATALOG.hms_uri", "HMS_URI"), ("spark.sql.catalog.CATALOG.gcp_project", "PROJECT_ID"), ("spark.sql.catalog.CATALOG.gcp_location", "LOCATION"), ("spark.sql.catalog.CATALOG.blms_catalog", "CATALOG"), ("spark.sql.catalog.CATALOG.warehouse", "DATA_WAREHOUSE_URI") ] ) LANGUAGE PYTHON AS R''' from pyspark.sql import SparkSession spark = SparkSession \ .builder \ .appName("BigLake Iceberg Example") \ .enableHiveSupport() \ .getOrCreate() spark.sql("CREATE NAMESPACE IF NOT EXISTS CATALOG;") spark.sql("CREATE DATABASE IF NOT EXISTS CATALOG.CATALOG_DB;") spark.sql("DROP TABLE IF EXISTS CATALOG.CATALOG_DB.CATALOG_TABLE;") /* Create a BigLake Metastore table and a BigQuery Iceberg table. */ spark.sql("CREATE TABLE IF NOT EXISTS CATALOG.CATALOG_DB.CATALOG_TABLE (id bigint, demo_name string) USING iceberg TBLPROPERTIES(bq_table='BQ_DATASET.BQ_TABLE', bq_connection='TABLE_CONNECTION_PROJECT_ID.TABLE_CONNECTION_REGION.TABLE_CONNECTION_ID'); ") /* Copy a Hive Metastore table to BigLake Metastore. Can be used together with TBLPROPERTIES `bq_table` to create a BigQuery Iceberg table. */ spark.sql("CREATE TABLE CATALOG.CATALOG_DB.CATALOG_TABLE (id bigint, demo_name string) USING iceberg TBLPROPERTIES(hms_table='HMS_DB.HMS_TABLE');") ''';
Replace the following:
PROCEDURE_CONNECTION_PROJECT_ID
: the project that contains the connection to run Spark procedures—for example,myproject
.PROCEDURE_CONNECTION_REGION
: the region that contains the connection to run Spark procedures—for example,us
.PROCEDURE_CONNECTION_ID
: the connection ID—for example,myconnection
.When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
.CATALOG
: the name of the Iceberg catalog to create for BigLake Metastore.The default value is
iceberg
.HMS_URI
: if you want to copy existing Hive Metastore tables to BigLake Metastore, specify a Hive Metastore URI.For example,
thrift://localhost:9083
.PROJECT_ID
: the project ID in which you want to create the BigLake Metastore instance.Iceberg BigLake tables are also created in the same project.
LOCATION
: the location where you want to create the BigLake Metastore instance.BigQuery can only access BigLake Metastore instances that are stored in the same location.
DATA_WAREHOUSE_URI
: the Cloud Storage bucket URI that you created to store the Iceberg metadata and data files.For example,
gs://mybucket/iceberg-warehouse
.CATALOG_DB
: the name of the database that you want to create in BigLake Metastore.This database is equivalent to the BigQuery dataset that will contain the Iceberg BigLake table.
CATALOG_TABLE
: the name of the table that you want to create in BigLake Metastore.This table is equivalent to the Iceberg BigLake table that you want to create.
BQ_DATASET
: the BigQuery dataset to contain the Iceberg BigLake table.BQ_TABLE
: the Iceberg BigLake table that you want to create.TABLE_CONNECTION_PROJECT_ID
: the project that contains the connection to create the BigLake table—for example,myproject
.TABLE_CONNECTION_REGION
: the region that contains the connection to create the BigLake table—for example,us
.TABLE_CONNECTION_ID
: the connection ID—for example,myconnection
.When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
.The service account associated with the connection must have
roles/biglake.viewer
to allow BigQuery users to query the table.HMS_DB
: if you want to copy existing Hive Metastore tables to BigLake Metastore, specify a Hive Metastore database.HMS_TABLE
: if you want to copy existing Hive Metastore tables to BigLake Metastore, specify a Hive Metastore table.
For information about Iceberg catalog configurations, see Spark Catalogs.
To run the stored procedure, click Run. For more information, see Call the Spark stored procedure. an Iceberg BigLake table is created in BigQuery.
Create tables with a metadata file
You can create Iceberg BigLake tables with a JSON metadata file. However, this is not the recommended method because you have to manually update the URI of the JSON metadata file to keep the BigLake table up to date. If the URI is not kept up to date, queries in BigQuery can either fail or provide different results from other query engines that directly use an Iceberg catalog. To avoid this, reference a BigLake Metastore instance when you create an Iceberg BigLake table.
Iceberg table metadata files are created in the Cloud Storage bucket that you specify when you create an Iceberg table using Spark.
Select one of the following options:
SQL
Use the CREATE EXTERNAL TABLE
statement. The following example creates a
BigLake table named myexternal-table
:
CREATE EXTERNAL TABLE myexternal-table WITH CONNECTION `myproject.us.myconnection` OPTIONS ( format = 'ICEBERG', uris = ["gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json"] )
Replace the uris
value with the latest JSON metadata file for a specific table snapshot.
You can enable the require partition filter by setting the require_partition_filter
flag.
bq
In a command-line environment, use the bq mk --table
command
with the @connection
decorator to specify the connection to use at the end
of the --external_table_definition
parameter.
To enable the require partition filter, use --require_partition_filter
.
bq mk
--table
--external_table_definition=TABLE_FORMAT=URI@projects/CONNECTION_PROJECT_ID/locations/CONNECTION_REGION/connections/CONNECTION_ID
PROJECT_ID:DATASET.EXTERNAL_TABLE
Replace the following:
TABLE_FORMAT
: the format of the table that you want to createIn this case,
ICEBERG
.URI
: the latest JSON metadata file for a specific table snapshot.For example,
gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json
.The URI can point to an external cloud location as well; such as Amazon S3 or Azure Blob Storage.
- Example for AWS:
s3://mybucket/iceberg/metadata/1234.metadata.json
. - Example for Azure:
azure://mystorageaccount.blob.core.windows.net/mycontainer/iceberg/metadata/1234.metadata.json
.
- Example for AWS:
CONNECTION_PROJECT_ID
: the project that contains the connection to create the BigLake table—for example,myproject
CONNECTION_REGION
: the region that contains the connection to create the BigLake table—for example,us
CONNECTION_ID
: the table connection ID—for example,myconnection
When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
DATASET
: the name of the BigQuery dataset that you want to create a table inFor example,
mydataset
.EXTERNAL_TABLE
: the name of the table that you want to createFor example,
mytable
.
Update table metadata
If you use a JSON metadata file to create Iceberg BigLake tables, then update the table definition to the latest table metadata. To update the schema or the metadata file, select one of the following options:
bq
Create a table definition file:
bq mkdef --source_format=ICEBERG \ "URI" > TABLE_DEFINITION_FILE
Use the
bq update
command with the--autodetect_schema
flag:bq update --autodetect_schema --external_table_definition=TABLE_DEFINITION_FILE PROJECT_ID:DATASET.TABLE
Replace the following:
URI
: your Cloud Storage URI with the latest JSON metadata fileFor example,
gs://mybucket/us/iceberg/mytable/metadata/1234.metadata.json
.TABLE_DEFINITION_FILE
: the name of the file containing the table schemaPROJECT_ID
: the project ID containing the table that you want to updateDATASET
: the dataset containing the table that you want to updateTABLE
: the table that you want to update
API
Use the tables.patch
method
with the autodetect_schema
property set to true
:
PATCH https://bigquery.googleapis.com/bigquery/v2/projects/PROJECT_ID/datasets/DATASET/tables/TABLE?autodetect_schema=true
Replace the following:
PROJECT_ID
: the project ID that contains the table that you want to updateDATASET
: the dataset containing the table that you want to updateTABLE
: the table that you want to update
In the body of the request, specify the updated values for the following fields:
{ "externalDataConfiguration": { "sourceFormat": "ICEBERG", "sourceUris": [ "URI" ] }, "schema": null }'
Replace URI
with the latest Iceberg
metadata file. For example,
gs://mybucket/us/iceberg/mytable/metadata/1234.metadata.json
.
Set up access control policies
You can use several methods to control access to BigLake tables:
For directions on setting up column-level security, see the column-level security guide.
For directions on setting up data masking, see the data masking guide.
For directions on setting up row-level security, see the row-level security guide.
For example, let's say you want to limit row access for the table mytable
in the dataset mydataset
:
+---------+---------+-------+ | country | product | price | +---------+---------+-------+ | US | phone | 100 | | JP | tablet | 300 | | UK | laptop | 200 | +---------+---------+-------+
You can create a row-level filter for Kim ([email protected]
) that restricts
their access to rows where country
is equal to US
.
CREATE ROW ACCESS POLICY only_us_filter ON mydataset.mytable GRANT TO ('user:[email protected]') FILTER USING (country = 'US');
Then, Kim runs the following query:
SELECT * FROM projectid.mydataset.mytable;
The output shows only the rows where country
is equal to US
:
+---------+---------+-------+ | country | product | price | +---------+---------+-------+ | US | phone | 100 | +---------+---------+-------+
Query BigLake tables
For more information, see Query Iceberg data.
Data mapping
BigQuery converts Iceberg data types to BigQuery data types as shown in the following table:
Iceberg data type | BigQuery data type |
---|---|
boolean |
BOOL |
int |
INT64 |
long |
INT64 |
float |
FLOAT64 |
double |
FLOAT64 |
Decimal(P/S) |
NUMERIC or BIG_NUMERIC depending on precision |
date |
DATE |
time |
TIME |
timestamp |
DATETIME |
timestamptz |
TIMESTAMP |
string |
STRING |
uuid |
BYTES |
fixed(L) |
BYTES |
binary |
BYTES |
list<Type> |
ARRAY<Type> |
struct |
STRUCT |
map<KeyType, ValueType> |
ARRAY<Struct<key KeyType, value ValueType>> |
Limitations
Iceberg BigLake tables have BigLake table limitations and also the following limitations:
The
copy-on-write
configuration is supported, but themerge-on-read
configuration is not supported. For more information, see Iceberg configuration.BigQuery supports manifest pruning using all Iceberg partition transformation functions except for
Bucket
. For information about how to prune partitions, see Query partitioned tables. Queries referencing Iceberg BigLake tables must contain literals in predicates compared to columns that are partitioned.Only Apache Parquet data files are supported.
If you are using BigLake Metastore, the following limitations apply:
- BigLake Metastore is not supported in BigQuery Omni regions.
- When renaming a table, the destination table must be in the same database as the source table. The database of the destination table must be specified explicitly.
- When inspecting an Iceberg metadata table,
you must use a fully qualified table name. For example,
prod.db.table.history
.
Costs
You will be charged for 1 TB at on-demand (per TB) query pricing for every 6,250,000 requests to BigLake Metastore and for every 625,000 objects stored in BigLake Metastore. On-demand query pricing rates vary by region. For smaller numbers of requests or objects, you will be charged the appropriate fraction of 1 TB.
For example, if you made 6,250,000 requests to BigLake Metastore and also stored 312,500 objects in it, you would be charged for 1.5 TB at the on-demand query pricing rate for the region in which you created the BigLake Metastore instance.
Require partition filter
You can require the use of predicate filters by enabling the
require partition filter option for your Iceberg table.
If you enable this option, attempts to query the table without specifying a
WHERE
clause that aligns with each manifest file will produce the following error:
Cannot query over table project_id.dataset.table without a filter that can be used for partition elimination.
Each manifest file requires at least one predicate suitable for partition elimination.
You can enable the require_partition_filter
in the following ways while
creating an Iceberg table :
SQL
Use the CREATE EXTERNAL TABLE
statement.The following example
creates a BigLake table named TABLE
with
require partition filter enabled:
CREATE EXTERNAL TABLE TABLE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID` OPTIONS ( format = 'ICEBERG', uris = [URI], require_partition_filter = true )
Replace the following:
TABLE
: the table name that you want to create.PROJECT_ID
: the project ID containing the table that you want to create.REGION
: the location where you want to create the Iceberg table.CONNECTION_ID
: the connection ID. For example,myconnection
.URI
: the Cloud Storage URI with the latest JSON metadata file.For example,
gs://mybucket/us/iceberg/mytable/metadata/1234.metadata.json
.The URI can point to an external cloud location as well; such as Amazon S3 or Azure Blob Storage.
- Example for AWS:
s3://mybucket/iceberg/metadata/1234.metadata.json
. - Example for Azure:
azure://mystorageaccount.blob.core.windows.net/mycontainer/iceberg/metadata/1234.metadata.json
.
- Example for AWS:
bq
Use the bq mk --table
command with the @connection
decorator to specify the connection to use at the end
of the --external_table_definition
parameter.
Use --require_partition_filter
to enable the require partition filter.
The following example creates a BigLake table named
TABLE
with require partition filter enabled:
bq mk \ --table \ --external_table_definition=ICEBERG=URI@projects/CONNECTION_PROJECT_ID/locations/CONNECTION_REGION/connections/CONNECTION_ID \ PROJECT_ID:DATASET.EXTERNAL_TABLE \ --require_partition_filter
Replace the following:
URI
: the latest JSON metadata file for a specific table snapshotFor example,
gs://mybucket/mydata/mytable/metadata/iceberg.metadata.json
.The URI can point to an external cloud location as well; such as Amazon S3 or Azure Blob Storage.
- Example for AWS:
s3://mybucket/iceberg/metadata/1234.metadata.json
. - Example for Azure:
azure://mystorageaccount.blob.core.windows.net/mycontainer/iceberg/metadata/1234.metadata.json
.
- Example for AWS:
CONNECTION_PROJECT_ID
: the project that contains the connection to create the BigLake table—for example,myproject
CONNECTION_REGION
: the region that contains the connection to create the BigLake table. For example,us
.CONNECTION_ID
: : the connection ID. For example,myconnection
.When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
DATASET
: the name of the BigQuerydataset that contains the table that you want to update. For example,
mydataset
.EXTERNAL_TABLE
: the name of the table that you want to createFor example,
mytable
.
You can also update your Iceberg table to enable the require partition filter.
If you don't enable the require partition filter option when you create the partitioned table, you can update the table to add the option.
bq
Use the bq update
command and supply the --require_partition_filter
flag.
For example:
To update mypartitionedtable
in mydataset
in your default project,
enter:
bq update --require_partition_filter PROJECT_ID:DATASET.TABLE
What's next
- Learn about stored procedure for Spark.
- Learn about BigLake tables.
- Learn how to set up access control policies.
- Learn about running queries in BigQuery.
- Learn about the supported statements and SQL dialects in BigQuery.