Skip to main content

Use SQL to create and manage metric views

This page explains how to create and manage metric views using SQL.

Prerequisites​

  • You must have SELECT privileges on the source data objects.
  • You must have the CREATE TABLE privilege and the USE SCHEMA privilege in the schema where you want to create the metric view.
  • You must also have the USE CATALOG privilege on the schema's parent catalog.
  • CAN USE permissions on a SQL warehouse or other compute resource running Databricks Runtime 17.2 or above.

A metastore admin or the catalog owner can grant you all of these privileges. A schema owner or user with the MANAGE privilege can grant you USE SCHEMA and CREATE TABLE privileges on the schema.

Create a metric view​

Use CREATE VIEW with the WITH METRICS clause to create a metric view. The metric view must be defined with a valid YAML specification in the body. Source data for a metric view can be a table, view, or SQL query.

The source data for the following metric view is the samples.tpch.orders table available in the samples catalog for most Databricks deployments. The following SQL DDL creates a metric view named orders_metric_view in the current catalog and schema. To specify a different catalog and schema, use the Unity Catalog three-level namespace.

You can add table-level and column-level comments to the metric view definition.

SQL
CREATE OR REPLACE VIEW orders_metric_view
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  comment: "Orders KPIs for sales and financial analysis"
  source: samples.tpch.orders
  filter: o_orderdate > '1990-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', o_orderdate)
    - name: Order Status
      expr: CASE
        WHEN o_orderstatus = 'O' then 'Open'
        WHEN o_orderstatus = 'P' then 'Processing'
        WHEN o_orderstatus = 'F' then 'Fulfilled'
        END
    - name: Order Priority
      expr: SPLIT(o_orderpriority, '-')[1]
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(o_totalprice)
    - name: Total Revenue per Customer
      expr: SUM(o_totalprice) / COUNT(DISTINCT o_custkey)
    - name: Total Revenue for Open Orders
      expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus='O')
$$

Alter a metric view​

To make changes to the definition associated with a metric view, use ALTER VIEW. The following example adds comments to dimensions and measures in the orders_metric_view metric view.

SQL
ALTER VIEW orders_metric_view
AS $$
  version: 1.1
  comment: "Orders KPIs for sales and financial analysis"
  source: samples.tpch.orders
  filter: o_orderdate > '1990-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', o_orderdate)
      comment: "Month of order"
    - name: Order Status
      expr: CASE
        WHEN o_orderstatus = 'O' then 'Open'
        WHEN o_orderstatus = 'P' then 'Processing'
        WHEN o_orderstatus = 'F' then 'Fulfilled'
        END
      comment: "Status of order: open, processing, or fulfilled"
    - name: Order Priority
      expr: SPLIT(o_orderpriority, '-')[1]
      comment: "Numeric priority 1 through 5; 1 is highest"
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(o_totalprice)
      comment: "Sum of total price"
    - name: Total Revenue per Customer
      expr: SUM(o_totalprice) / COUNT(DISTINCT o_custkey)
      comment: "Sum of total price by customer"
    - name: Total Revenue for Open Orders
      expr: SUM(o_totalprice) FILTER (WHERE o_orderstatus='O')
      comment: "Potential revenue from open orders"
$$

Grant privileges on a metric view​

A metric view is a Unity Catalog securable object and follows the same permission model as other views. Privileges are hierarchical, so privileges on a metastore, catalog, or schema cascade to the objects contained within. The following example grants minimum privileges necessary for users in the data_consumers group to query a metric view.

SQL
GRANT SELECT ON orders_metric_view to `data-consumers`;

To learn more about privileges in Unity Catalog, see Manage privileges in Unity Catalog. To learn more about creating and managing groups, see Groups.

Get metric view definition​

Use DESCRIBE TABLE EXTENDED with the optional AS JSON parameter to view the definition for a metric view. The AS JSON parameter is optional. Omitting it provides output that is better for human readers, while including it is better for machine consumers. The following example returns a JSON string that describes the metric view and its components.

SQL
DESCRIBE TABLE EXTENDED orders_metric_view AS JSON

Drop a metric view​

Use DROP TABLE syntax to delete a metric view.

SQL
DROP TABLE orders_metric_view;

Next steps​

Now that you've created a metric view using SQL, explore these related topics:

Query and consume metric views​

Advanced metric view features​

Alternative creation methods​

Governance and security​

Last updated on