Query BigQuery tables through the Apache Iceberg REST catalog endpoint

Lakehouse provides interoperability with BigQuery by routing requests through the Apache Iceberg REST catalog endpoint to the BigQuery catalog. This capability lets you expose tables managed by BigQuery, for example Iceberg managed tables, to external open source (OSS) engines such as Apache Spark and Trino.

Instead of creating a dedicated Lakehouse catalog container to store metadata, the Apache Iceberg REST catalog endpoint acts purely as a proxy gateway. When external engines connect using the bq:// warehouse prefix, the gateway routes their catalog requests directly to BigQuery's internal catalog. This lets you create and manage tables directly within BigQuery using standard BigQuery DDL or APIs, while giving external OSS engines read-only access to query those tables through the REST catalog endpoint.

How querying BigQuery tables through the REST catalog endpoint works

Routing requests to the BigQuery catalog lets you expose BigQuery tables—such as Iceberg managed tables and BigQuery metastore tables—through the Lakehouse runtime catalog Apache Iceberg REST catalog endpoint.

The workflow operates as follows:

  1. Create a table in the BigQuery catalog: You create an Iceberg managed table in BigQuery using DDL statements. This table exists in the BigQuery catalog, is governed by BigQuery access control lists (ACLs), and functions as a BigQuery REST resource.
  2. Federate into the BigQuery warehouse from the Lakehouse runtime catalog: Using the Lakehouse runtime catalog Apache Iceberg REST API, you federate into a warehouse specified by the bq://projects/PROJECT_ID warehouse path format (or the regional version, bq://projects/PROJECT_ID/locations/LOCATION). This lets you access the BigQuery table from compute engines like Apache Spark through the Apache Iceberg REST Catalog API. In this configuration, you get a read-only experience from Spark, but a read-write experience from BigQuery.

Considerations

  • Because routing requests to the BigQuery catalog does not create a dedicated Lakehouse catalog resource, other catalog-level concepts and configurations—such as the resource hierarchy, bucket and catalog regions, and the four-part P.C.N.T naming structure—do not apply to this method. In the underlying API, this corresponds to the CATALOG_TYPE_BIGQUERY configuration. Storage access delegation (credential vending) isn't supported when routing requests to the BigQuery catalog.

  • Exposing BigQuery tables through the REST catalog endpoint is distinct from accessing cross-cloud data. While this workflow exposes Google Cloud tables to external OSS engines, Lakehouse connects Google Cloud to remote external catalogs, such as Databricks Unity Catalog, to query data stored in other cloud providers directly from BigQuery and OSS engines without migrating data. For more information about that workflow, see About cross-cloud data access.

Comparison with tables managed by the Apache Iceberg REST catalog endpoint

Exposing BigQuery tables through the REST catalog endpoint differs from workflows where the Lakehouse runtime catalog uses the Apache Iceberg REST catalog endpoint in the following ways:

  • Resource management and catalog storage: Tables in the BigQuery catalog reside there as BigQuery REST resources, and the Lakehouse runtime catalog acts as a proxy gateway. When the Lakehouse runtime catalog uses the Apache Iceberg REST catalog endpoint to manage tables directly, those tables are stored as Lakehouse REST resources.
  • Access control: Tables in the BigQuery catalog use BigQuery IAM permissions and access control lists (ACLs). Tables managed directly in the Lakehouse runtime catalog use Lakehouse ACLs.
  • Engine read and write capabilities: Iceberg managed tables provide read-write access through BigQuery, and read-only access from external engines such as Spark. Tables managed directly in the Lakehouse runtime catalog through the Apache Iceberg REST catalog endpoint support read-write operations from both BigQuery and external engines such as Spark.

Before you begin

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

  2. Enable the BigLake API, if it is not already enabled.

    Roles required to enable APIs

    To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

    Enable the API

Required roles

To get the permissions that you need to query BigQuery tables through the Apache Iceberg REST catalog endpoint, ask your administrator to grant you the following IAM roles:

  • Read catalog resources and query table data:
  • Create namespaces or modify external-engine-created tables:

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.

If you use external query engines such as Managed Service for Apache Spark to create namespaces or modify tables created from external engines, grant these roles to the service account that runs jobs in that engine (Iceberg managed tables are read-only from external engines).

Configure your client for the BigQuery warehouse

To connect to the BigQuery warehouse, configure your client (such as Apache Spark or Trino) with the bq://projects/PROJECT_ID warehouse format in the WAREHOUSE_PATH field in the client configuration examples in Configure client application.

You can also choose to include a BigQuery location to restrict future requests to a single location using the bq://projects/PROJECT_ID/locations/LOCATION format.

Because these resources are managed by BigQuery, you must have the applicable required permissions.

Create namespaces in the BigQuery warehouse

After you configure your client, you can create a namespace for your tables.

Spark

To create a namespace in the BigQuery warehouse, include the LOCATION and DBPROPERTIES clauses:

spark.sql("CREATE NAMESPACE IF NOT EXISTS NAMESPACE_NAME LOCATION 'gs://BUCKET_NAME/NAMESPACE_NAME' WITH DBPROPERTIES ('gcp-region' = 'LOCATION');")
spark.sql("USE NAMESPACE_NAME;")

Replace the following:

  • NAMESPACE_NAME: a name for your namespace.
  • BUCKET_NAME: the Cloud Storage bucket that you're using with your catalog.
  • LOCATION: a BigQuery location. The default value is the US multi-region.

Trino

To create a schema in the BigQuery warehouse, include the LOCATION and gcp-region properties:

CREATE SCHEMA IF NOT EXISTS  CATALOG_NAME.SCHEMA_NAME WITH ( LOCATION = 'gs://BUCKET_NAME/SCHEMA_NAME', "gcp-region" = 'LOCATION');
USE CATALOG_NAME.SCHEMA_NAME;

Replace the following:

  • CATALOG_NAME: the name of your Trino catalog using the Apache Iceberg REST catalog endpoint.
  • SCHEMA_NAME: a name for your schema.
  • BUCKET_NAME: the Cloud Storage bucket that you're using with your catalog.
  • LOCATION: a BigQuery location. The default value is the US multi-region.

Query tables in BigQuery

Tables that you create in the BigQuery warehouse are visible in BigQuery and can be queried directly using standard BigQuery SQL (without needing a four-part P.C.N.T name):

SELECT * FROM `NAMESPACE_NAME.TABLE_NAME`;

Replace the following:

  • NAMESPACE_NAME: the name of your namespace.
  • TABLE_NAME: the name of your table.

What's next