ConceptAI and semantics
SQL views and metric views as catalog objects
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.
In Unity Catalog, 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).
It stores the definition as sent. Spark sends the query text after AS with
its line breaks and indentation.
When Spark 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.
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 and Manage 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
and Change a metric view's 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.
- Create a view: the same privileges as creating a table. You need
USE CATALOGon the catalog, or to own it, and you need either ownership of the schema orUSE SCHEMAandCREATE TABLEon it (CREATE_TABLE). - Read a view's entry:
USE CATALOG,USE SCHEMA, and ownership,SELECT, orMODIFYon the view. The catalog's owner can read it too, and so can the schema's owner withUSE CATALOG(GET_TABLE,TableService). 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).
Reading the view's entry gives the engine the definition. Going by the server's
source
(TableService),
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.