Query a table

Querying a table reads data from the underlying Cloud Storage files using the metadata managed by the Lakehouse runtime catalog.

You can execute select queries from open-source engines like Spark and Trino, or directly from BigQuery using four-part table naming.

Before you begin

See the table overview to understand the different types of tables and the implications of using them.

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

2. Enable the BigLake API.

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

   Enable the API

Required roles

To get the permissions that you need to query a table, ask your administrator to grant you the following IAM roles on your project and storage bucket:

• Read table data in credential vending mode: BigLake Viewer (roles/biglake.viewer) - the project
• Read table data in non-credential vending mode:
  • BigLake Viewer (roles/biglake.viewer) - the project
  • Storage Object Viewer (roles/storage.objectViewer) - the Cloud Storage bucket

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.

Query a table

Select all data from the table:

Spark

spark.sql("SELECT * FROM TABLE_NAME;").show()

Trino

SELECT * FROM TABLE_NAME;

BigQuery

To query Lakehouse Iceberg REST catalog tables from BigQuery, use the four-part table name in your query with the following format: PROJECT_NAME.CATALOG_ID.NAMESPACE_OR_SCHEMA_NAME.TABLE_NAME.

SELECT * FROM `PROJECT_NAME.BIGLAKE_CATALOG_ID.NAMESPACE_OR_SCHEMA_NAME.TABLE_NAME`;

Replace the following values:

• PROJECT_NAME: the Google Cloud project that owns the catalog in the Lakehouse runtime catalog. The selected Google Cloud project in the Google Cloud console is billed for the query.

• CATALOG_ID: the ID of the Lakehouse runtime catalog specified when the catalog was created. This identifier is used as the catalog name in BigQuery queries.

  This identifier is also the name of your Cloud Storage bucket.

  For example, if you created your bucket to store your catalog and named it iceberg-bucket, both your catalog name and bucket name are iceberg-bucket. This is used later when you query your catalog in BigQuery, using the P.C.N.T syntax. For example my-project.biglake_catalog_id.quickstart_namespace.quickstart_table.

• NAMESPACE_OR_SCHEMA_NAME: the table namespace if using Spark or table schema name if using Trino.

• TABLE_NAME: the name of your table.

What's next

• Learn how to alter a table.