--- title: SQL views and metric views as catalog objects summary: Unity Catalog stores a view's definition, columns, and dependencies, and the engine runs it. A metric view's definition is YAML with measures and dimensions. diataxis: explanation project: unitycatalog canonical: https://docs.unitycatalog.io/explanation/views-and-metric-views companions: - url: "https://docs.unitycatalog.io/explanation/views-and-metric-views/snippets/stored_definitions.py" kind: python purpose: "What Unity Catalog stores for a SQL view and a metric view, and what it" run: "UNITY_CATALOG_URL=http://localhost:8080/api/2.1/unity-catalog uv run https://docs.unitycatalog.io/explanation/views-and-metric-views/snippets/stored_definitions.py" requires-python: ">=3.11" services: ["unitycatalog"] environment: title: "Unity Catalog 0.7.0" dir: "uc-docs-env/unitycatalog" start: ["curl -fsSL https://docs.unitycatalog.io/env/unitycatalog.tar.gz | tar -xz", "cd uc-docs-env/unitycatalog && docker compose up -d --wait", "export UNITY_CATALOG_URL=http://localhost:8080/api/2.1/unity-catalog"] stop: "docker compose down" elements: ["unityCatalogServer"] index: "https://docs.unitycatalog.io/env/environments.json" --- ## Companion files CI-tested scripts that run this page's examples end to end. A script that exits 0 has passed its checks. They need Unity Catalog 0.7.0 running. To start it (also `environment` in this file's frontmatter): ```bash curl -fsSL https://docs.unitycatalog.io/env/unitycatalog.tar.gz | tar -xz cd uc-docs-env/unitycatalog && docker compose up -d --wait export UNITY_CATALOG_URL=http://localhost:8080/api/2.1/unity-catalog ``` - [`stored_definitions.py`](https://docs.unitycatalog.io/explanation/views-and-metric-views/snippets/stored_definitions.py) (Python): What Unity Catalog stores for a SQL view and a metric view, and what it - run: `UNITY_CATALOG_URL=http://localhost:8080/api/2.1/unity-catalog uv run https://docs.unitycatalog.io/explanation/views-and-metric-views/snippets/stored_definitions.py` In [Unity Catalog](https://docs.unitycatalog.io/explanation/what-is-unity-catalog "The open-source reference server implementing the Unity Catalog specification."), a view is a catalog entry with a table type and a stored definition. It has no storage location and holds no data. When you query it, a query engine reads the definition from the catalog, expands it in its own dialect, and runs it against the source tables. A metric view is the same kind of entry, but its definition declares measures and dimensions in YAML instead of a query. ## What the catalog stores for a view A view is stored as a table entry, so it's listed with the schema's tables and uses the same API. These fields set it apart: | Field | SQL view | Metric view | | ------------------- | ------------------------------------- | --------------------------------------- | | `table_type` | `VIEW` | `METRIC_VIEW` | | `view_definition` | The query text after `AS`, as written | The YAML, as written | | `columns` | The output columns and their types | The dimensions and measures, as columns | | `view_dependencies` | The tables the query reads, if known | The tables the YAML reads, at least one | | `storage_location` | Empty | Empty | When it creates a view, the server checks the request's shape: there's a definition, at least one column, and every listed dependency exists ([`TableRepository`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/persist/TableRepository.java#L972-L1000)). It stores the definition as sent. Spark sends the query text after `AS` with its line breaks and indentation. > **Note** > > The server never parses a view's definition. A `VIEW` whose definition is the text `this is not a query` registers without error and is stored unchanged. The error comes later, from the engine that tries to run it. When [Spark](https://spark.apache.org "Unified analytics engine for large-scale data processing.") creates a view, it also stores `properties` on the entry: the session's current catalog and namespace, the schema mode, the output column names, and the session's SQL settings, so it can read the view back the same way. Registering a view through the REST API generates none of these, so the entry holds only the properties the client sends. To register a view without Spark, see [Manage SQL views](https://docs.unitycatalog.io/how-to/sql-views#register-a-view-through-the-rest-api). ## Where a view runs The engine that reads a view runs it. It fetches the stored definition, parses it in its own SQL dialect, and plans the expanded query as if you had typed it. So a view's definition only works in engines that understand its dialect. A client that only talks to the REST API can store and return a definition, but it can't run one, and the Iceberg REST catalog serves no views. For a SQL view, Spark works out the dependencies from the query as best it can. A view over `samples.views_explained.orders` records that table. A view with no source table, such as `SELECT 1 AS one`, records an empty list. ## SQL views versus metric views Both kinds are stored definitions that an engine expands at query time. They differ in what the definition holds and how you query it: | | SQL view | Metric view | | ---------------- | --------------------------------- | ----------------------------------------------------------------------- | | Definition | A SQL query | YAML version `0.1`: a source, optional filter, dimensions, and measures | | A query returns | The query's rows | Measures aggregated over the dimensions you group by | | How you query it | `SELECT` its columns like a table | `SELECT` dimensions, wrap each measure in `MEASURE()`, and `GROUP BY` | | Dependencies | Optional | At least one, or the server rejects it | Use a SQL view to name a query: a join, a filter, or a renamed set of columns that several readers share. Use a metric view when the thing to share is a calculation, such as revenue or order count. Its readers then pick the grouping at query time, and every query computes the measure the same way. A metric view over the orders table with a `Revenue` measure of `SUM(amount)` returns revenue per status when you group by `Status`, and the grand total when you don't. To work with each kind, see [Manage SQL views](https://docs.unitycatalog.io/how-to/sql-views) and [Manage metric views](https://docs.unitycatalog.io/how-to/metric-views). ## Changing a stored definition The server checks a view once, when it's created. Dropping a source table later leaves the view listed, with `view_dependencies` still naming that table. Only a query against the view fails. The stored columns are checked only at read time too: they're a contract the engine plans against, and the server doesn't compare them with the query. Because the catalog can't update a view, a change means dropping it and creating it again, and the new entry gets the same checks. For the steps, see [Change or rename a view](https://docs.unitycatalog.io/how-to/sql-views#change-or-rename-a-view) and [Change a metric view's definition](https://docs.unitycatalog.io/how-to/metric-views#change-a-definition). ## Permissions Because a view is a table entry, the server applies the table rules to it. These rules come from the server's source, linked at v0.7.0. The server has no row filters or column masks to attach to a view. For the privilege model itself, see [Ownership and privileges](https://docs.unitycatalog.io/explanation/uc-basics#ownership-and-privileges). - **Create a view:** the same privileges as creating a table. You need `USE CATALOG` on the catalog, or to own it, and you need either ownership of the schema or `USE SCHEMA` and `CREATE TABLE` on it ([`CREATE_TABLE`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/auth/AuthorizeExpressions.java#L132-L141)). - **Read a view's entry:** `USE CATALOG`, `USE SCHEMA`, and ownership, `SELECT`, or `MODIFY` on the view. The catalog's owner can read it too, and so can the schema's owner with `USE CATALOG` ([`GET_TABLE`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/auth/AuthorizeExpressions.java#L30-L38), [`TableService`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/service/TableService.java#L99-L117)). Listing a schema's tables returns only the views and tables you can read. When a view is created, the server checks that its dependencies exist, not that the creator can read them ([`TableRepository`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/persist/TableRepository.java#L972-L1000)). Reading the view's entry gives the engine the definition. Going by the server's source ([`TableService`](https://github.com/unitycatalog/unitycatalog/blob/2b17f3b5a1d06c0adbf22f4c11ea888c01a36415/server/src/main/java/io/unitycatalog/server/service/TableService.java#L99-L117)), the engine then reads each source table as the caller, so a view doesn't hide its source tables: a reader needs access to them as well. ## Related - [Manage SQL views](https://docs.unitycatalog.io/how-to/sql-views) - [Manage metric views](https://docs.unitycatalog.io/how-to/metric-views) - [Work with metric views](https://docs.unitycatalog.io/tutorials/first-metric-view) - [Learn how ownership and privileges work](https://docs.unitycatalog.io/explanation/uc-basics#ownership-and-privileges) - [Learn how external and managed tables differ](https://docs.unitycatalog.io/explanation/external-and-managed-tables)