Create a view

Creating an Apache Iceberg view lets you register a SQL-defined, non-materialized logical table in a namespace of the Lakehouse runtime catalog so that Iceberg-compatible engines such as Spark and Trino can query it. The catalog writes the view definition to a versioned metadata.json file in Cloud Storage under the view's location.

Because the catalog validates the schemas of referenced tables when you create a view, the namespace and every table that the view references must already exist. For more information, see Overview of Apache Iceberg views.

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

  3. Set up the Lakehouse runtime catalog with a multiple-bucket catalog or a single-bucket catalog. Iceberg views aren't supported in BigQuery catalogs or federated catalogs.
  4. Review the limitations of Iceberg views.
  5. Connect your open source engine to the Apache Iceberg REST catalog endpoint.

    • Spark: Use Spark 3.5 or later with the Iceberg runtime 1.6.0 or later. Use Iceberg runtime 1.10.0 or later so that Spark discovers the supported view endpoints through the catalog's GET /v1/config response. Set spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions so that Spark delegates view DDL and resolution to the Apache Iceberg REST catalog endpoint.
    • Trino: Use Trino 482 or later, and configure your Trino Iceberg connector to use the Apache Iceberg REST catalog endpoint. For more information, see Trino support.

Required roles

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

  • Create a view: BigLake Editor (roles/biglake.editor) - your project
  • Write view metadata files in end-user credentials mode: Storage Object User (roles/storage.objectUser) - the Cloud Storage bucket

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

These predefined roles contain the permissions required to create a view. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to create a view:

  • Create a view:
    • biglake.views.create on the namespace
    • biglake.tables.get on each Iceberg table that the view references
  • Write view metadata files in end-user credentials mode: storage.objects.create on the Cloud Storage bucket

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

For a complete list of view permissions, see Required roles and permissions.

Create base tables

The examples in this document reference the following namespace and tables. If they don't already exist, create them:

CREATE NAMESPACE IF NOT EXISTS my_ns;
USE my_ns;

CREATE TABLE IF NOT EXISTS employees (
  id INT,
  name STRING,
  dept STRING,
  salary DOUBLE
) USING iceberg;

CREATE TABLE IF NOT EXISTS departments (
  dept_code STRING,
  dept_name STRING,
  building STRING
) USING iceberg;

Optional: insert sample data so that you can query the views that you create:

INSERT INTO my_ns.employees VALUES
  (1, 'Alice', 'Engineering', 150000.0),
  (2, 'Bob', 'Marketing', 120000.0),
  (3, 'Charlie', 'Engineering', 135000.0);

INSERT INTO my_ns.departments VALUES
  ('Engineering', 'Eng Org', 'Building A'),
  ('Marketing', 'Growth Org', 'Building B');

Create a view

Create an Iceberg view over one or more Iceberg tables.

Spark

Create a view with explicit column aliases, column-level comments, a view-level comment, and custom properties. The engine resolves unqualified table names in the view definition by using the view's default-catalog and default-namespace:

CREATE VIEW eng_employees (
  emp_id COMMENT 'Unique employee ID',
  emp_name COMMENT 'Full name of employee',
  salary COMMENT 'Annual compensation'
)
COMMENT 'Curated view of Engineering department employees'
TBLPROPERTIES ('tier' = 'silver')
AS SELECT id, name, salary
FROM employees
WHERE dept = 'Engineering';

To create a view only if it doesn't already exist, use CREATE VIEW IF NOT EXISTS. If the view exists, the statement succeeds without modifying it:

CREATE VIEW IF NOT EXISTS eng_employees AS
SELECT id, name, salary FROM employees;

By default, the catalog writes gzip-compressed metadata files (.gz.metadata.json). To write uncompressed metadata files (.metadata.json), set the write.metadata.compression-codec property to none:

CREATE VIEW uncompressed_eng_view
TBLPROPERTIES ('write.metadata.compression-codec' = 'none')
AS SELECT id, name FROM employees WHERE dept = 'Engineering';

REST

To create an Iceberg view using the REST API, make a POST request to the CreateIcebergView endpoint:

POST /iceberg/v1/restcatalog/v1/projects/PROJECT_ID/catalogs/CATALOG_ID/namespaces/NAMESPACE_NAME/views

The request body must contain a valid Iceberg CreateViewRequest JSON payload that defines the view name, schema, view version, and initial properties. If you set a custom location, it must follow the view storage location rules for your catalog type. The location is immutable after creation.

Replace the following:

  • PROJECT_ID: your Google Cloud project ID.
  • CATALOG_ID: the ID of your catalog.
  • NAMESPACE_NAME: the name of your catalog namespace.

Create views with joins, aggregations, and subqueries

A view definition can include joins, aggregations, CASE expressions, common table expressions (CTEs), UNION ALL, and subqueries.

Create a view that joins two tables:

CREATE VIEW employee_directory AS
SELECT e.id, e.name, d.dept_name, d.building
FROM my_ns.employees e
JOIN my_ns.departments d
  ON e.dept = d.dept_code;

Create a view that aggregates data and uses a CASE expression:

CREATE VIEW dept_summary AS
SELECT
  dept,
  CASE WHEN dept = 'Engineering' THEN 'TECH' ELSE 'NON-TECH' END AS category,
  COUNT(*) AS headcount,
  AVG(salary) AS avg_salary
FROM my_ns.employees
GROUP BY dept;

Create a view that uses a CTE, UNION ALL, and a subquery:

CREATE VIEW high_earners_cte AS
WITH eng AS (
  SELECT id, name, salary FROM my_ns.employees WHERE dept = 'Engineering'
)
SELECT id, name FROM eng WHERE salary >= 140000.0
UNION ALL
SELECT id, name FROM my_ns.employees
WHERE id IN (SELECT id FROM my_ns.employees WHERE dept = 'Marketing');

Create a nested view

A view can query another view. Querying the outer view requires biglake.views.get on both views and read access to every table that either view references.

CREATE VIEW top_eng_employees AS
SELECT emp_id, emp_name
FROM my_ns.eng_employees
WHERE salary >= 140000.0;

Create a view with time travel on underlying tables

Views don't maintain their own data snapshots, but a view definition can query an underlying Iceberg table at a specific timestamp or snapshot ID. Use this pattern to create reproducible historical baselines for compliance, reporting, or model training.

  1. Insert a record to create a new version of the underlying table:

    INSERT INTO my_ns.employees VALUES (4, 'Diana', 'Engineering', 160000.0);
    
  2. Create a view that is pinned to the table state before the insert. Replace the timestamp with a time before your insert:

    CREATE VIEW eng_employees_baseline AS
    SELECT id, name, salary
    FROM my_ns.employees TIMESTAMP AS OF 'TIMESTAMP'
    WHERE dept = 'Engineering';
    

    Alternatively, pin the view to a specific table snapshot ID:

    CREATE VIEW eng_employees_snapshot_audit AS
    SELECT id, name, salary
    FROM my_ns.employees VERSION AS OF SNAPSHOT_ID
    WHERE dept = 'Engineering';
    
  3. Query the historical view. The result excludes the newly inserted record:

    SELECT * FROM eng_employees_baseline;
    

You can't apply TIMESTAMP AS OF or VERSION AS OF directly to a view identifier. Express time travel inside the view definition, against the underlying table. For more information, see Troubleshoot Iceberg views.

What's next