Back to Details

databricks-metric-views

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Unity Catalog Metric Views

Unity Catalog 指标视图

Define reusable, governed business metrics in YAML that separate measure definitions from dimension groupings for flexible querying.
使用YAML定义可复用、受管控的业务指标，将度量定义与维度分组分离，实现灵活查询。

When to Use

适用场景

Use this skill when:
  • Defining standardized business metrics (revenue, order counts, conversion rates)
  • Building KPI layers shared across dashboards, Genie, and SQL queries
  • Creating metrics with complex aggregations (ratios, distinct counts, filtered measures)
  • Defining window measures (moving averages, running totals, period-over-period, YTD)
  • Modeling star or snowflake schemas with joins in metric definitions
  • Enabling materialization for pre-computed metric aggregations
在以下场景中使用该技能：
  • 定义标准化业务指标（收入、订单数量、转化率）
  • 构建可在仪表板、Genie和SQL查询间共享的KPI层
  • 创建带有复杂聚合的指标（比率、去重计数、过滤度量）
  • 定义窗口度量（移动平均值、累计总和、同期对比、年初至今）
  • 在指标定义中通过关联建模星型或雪花型模式
  • 启用物化以预计算指标聚合结果

Prerequisites

前提条件

  • Databricks Runtime 17.2+ (for YAML version 1.1); 17.3+ for semantic metadata (
    synonyms
    / 
    display_name
    / 
    format
    )
  • SQL warehouse with 
    CAN USE
    permissions
  • SELECT
    on source tables, 
    CREATE TABLE
    + 
    USE SCHEMA
    in the target schema
  • Databricks Runtime 17.2+（适用于YAML 1.1版本）；**17.3+**支持语义元数据（
    synonyms
    / 
    display_name
    / 
    format
    ）
  • 拥有
    CAN USE
    权限的SQL仓库
  • 源表的
    SELECT
    权限，目标Schema的
    CREATE TABLE
    + 
    USE SCHEMA
    权限

Quick Start

快速开始

Inspect Source Table Schema

检查源表结构

Before authoring a metric view, inspect the source tables. Use 
discover-schema
as the default — one call returns columns, types, sample rows, null counts, and row count. If you only know the schema, list tables first with 
query "SHOW TABLES IN ..."
.
databricks experimental aitools tools discover-schema catalog.schema.orders catalog.schema.customers
For dimensions and measures, probe distribution beyond sampling — cardinality of candidate dimensions, min/max/percentiles for measures, top categorical values. Write aggregate SQL through 
databricks experimental aitools tools query --warehouse <WH> "..."
. Both commands auto-pick the default warehouse; set 
DATABRICKS_WAREHOUSE_ID
or pass 
--warehouse <ID>
to override.
The 
databricks experimental aitools tools
subcommands are experimental — subject to change between CLI versions. Confirm availability with 
databricks experimental aitools tools --help
before relying on them; see CLI Execution for the stable Statement Execution API fallback.
在编写指标视图之前，先检查源表。默认使用
discover-schema
命令——一次调用即可返回列、类型、示例行、空值计数和行数。如果仅知道Schema，先使用
query "SHOW TABLES IN ..."
列出表。
databricks experimental aitools tools discover-schema catalog.schema.orders catalog.schema.customers
对于维度和度量，需探查抽样之外的分布情况——候选维度的基数、度量的最小/最大/百分位数、分类值的前几名。通过
databricks experimental aitools tools query --warehouse <WH> "..."
编写聚合SQL。这两个命令都会自动选择默认仓库；可设置
DATABRICKS_WAREHOUSE_ID
或传入
--warehouse <ID>
来覆盖默认设置。
databricks experimental aitools tools
子命令为实验性命令——不同CLI版本间可能会有变更。在依赖这些命令之前，请通过
databricks experimental aitools tools --help
确认其可用性；如需稳定方案，请参考CLI执行中的Statement Execution API替代方案。

Create a Metric View

创建指标视图

sql
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  comment: "Orders KPIs for sales analysis"
  filter: order_date > '2020-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
      comment: "Month of order"
    - name: Order Status
      expr: CASE
        WHEN status = 'O' THEN 'Open'
        WHEN status = 'P' THEN 'Processing'
        WHEN status = 'F' THEN 'Fulfilled'
        END
      comment: "Human-readable order status"
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(total_price)
      comment: "Sum of total price"
    - name: Revenue per Customer
      expr: SUM(total_price) / COUNT(DISTINCT customer_id)
      comment: "Average revenue per unique customer"
$$
sql
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  comment: "Orders KPIs for sales analysis"
  filter: order_date > '2020-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
      comment: "Month of order"
    - name: Order Status
      expr: CASE
        WHEN status = 'O' THEN 'Open'
        WHEN status = 'P' THEN 'Processing'
        WHEN status = 'F' THEN 'Fulfilled'
        END
      comment: "Human-readable order status"
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(total_price)
      comment: "Sum of total price"
    - name: Revenue per Customer
      expr: SUM(total_price) / COUNT(DISTINCT customer_id)
      comment: "Average revenue per unique customer"
$$

Query a Metric View

查询指标视图

All measures must use the 
MEASURE()
function. 
SELECT *
is NOT supported.
sql
SELECT
  `Order Month`,
  `Order Status`,
  MEASURE(`Total Revenue`) AS total_revenue,
  MEASURE(`Order Count`) AS order_count
FROM catalog.schema.orders_metrics
WHERE extract(year FROM `Order Month`) = 2024
GROUP BY ALL
ORDER BY ALL
所有度量必须使用
MEASURE()
函数。不支持
SELECT *
。
sql
SELECT
  `Order Month`,
  `Order Status`,
  MEASURE(`Total Revenue`) AS total_revenue,
  MEASURE(`Order Count`) AS order_count
FROM catalog.schema.orders_metrics
WHERE extract(year FROM `Order Month`) = 2024
GROUP BY ALL
ORDER BY ALL

Reference Files

参考文件

TopicFileDescription
YAML Syntaxreferences/yaml-reference.mdComplete YAML spec: dimensions, measures, joins, materialization
Patterns & Examplesreferences/patterns.mdCommon patterns: star schema, snowflake, filtered measures, window measures, ratios
Multi-source build (Advisor)references/metric-view-advisor.mdGuided workflow to build metric views from gold schemas, dashboards, SQL queries, Genie spaces, or KPI files — analysis, overlap detection, deploy
主题文件描述
YAML语法references/yaml-reference.md完整YAML规范：维度、度量、关联、物化
模式与示例references/patterns.md常见模式：星型模式、雪花型模式、过滤度量、窗口度量、比率
多源构建（Advisor）references/metric-view-advisor.md从黄金Schema、仪表板、SQL查询、Genie空间或KPI文件构建指标视图的引导式工作流——分析、重叠检测、部署

Guided, multi-source build (Metric View Advisor)

引导式多源构建（Metric View Advisor）

For the single-table create/query patterns above, use this skill directly. When the user wants to build metric views from existing assets — gold/fact schemas, AI/BI dashboards, SQL query files, Genie spaces, or KPI spreadsheets — combine multiple sources, deduplicate against views that already exist, and walk deployment end to end, use the Metric View Advisor in 
references/metric-view-advisor.md
. It builds on this skill's baseline spec and adds the multi-source analysis, overlap detection, and an interactive build/deploy flow. Load it when the user asks to "formalize our KPIs," "build a metric/semantic layer from our tables/dashboards/queries," or otherwise wants a guided build rather than authoring one view by hand.
对于上述单表创建/查询模式，可直接使用本技能。当用户希望从现有资产构建指标视图——黄金/事实Schema、AI/BI仪表板、SQL查询文件、Genie空间或KPI电子表格——合并多个源、与已存在的视图去重，并完成端到端部署时，请使用
references/metric-view-advisor.md
中的Metric View Advisor。它基于本技能的基线规范，添加了多源分析、重叠检测以及交互式构建/部署流程。当用户要求“规范化我们的KPI”、“从我们的表/仪表板/查询构建指标/语义层”，或希望通过引导式构建而非手动编写单个视图时，加载该工具。

SQL Operations

SQL操作

Create Metric View

创建指标视图

sql
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  comment: "Orders KPIs for sales analysis"
  source: catalog.schema.orders
  filter: order_date > '2020-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
      comment: "Month of order"
    - name: Order Status
      expr: status
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(total_price)
      comment: "Sum of total price"
$$;
sql
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  comment: "Orders KPIs for sales analysis"
  source: catalog.schema.orders
  filter: order_date > '2020-01-01'
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
      comment: "Month of order"
    - name: Order Status
      expr: status
  measures:
    - name: Order Count
      expr: COUNT(1)
    - name: Total Revenue
      expr: SUM(total_price)
      comment: "Sum of total price"
$$;

Query Metric View

查询指标视图

sql
SELECT
  `Order Month`,
  MEASURE(`Total Revenue`) AS total_revenue,
  MEASURE(`Order Count`) AS order_count
FROM catalog.schema.orders_metrics
WHERE extract(year FROM `Order Month`) = 2024
GROUP BY ALL
ORDER BY ALL
LIMIT 100;
sql
SELECT
  `Order Month`,
  MEASURE(`Total Revenue`) AS total_revenue,
  MEASURE(`Order Count`) AS order_count
FROM catalog.schema.orders_metrics
WHERE extract(year FROM `Order Month`) = 2024
GROUP BY ALL
ORDER BY ALL
LIMIT 100;

Describe Metric View

描述指标视图

sql
DESCRIBE TABLE EXTENDED catalog.schema.orders_metrics;

-- Or get YAML definition
SHOW CREATE TABLE catalog.schema.orders_metrics;
sql
DESCRIBE TABLE EXTENDED catalog.schema.orders_metrics;

-- 或获取YAML定义
SHOW CREATE TABLE catalog.schema.orders_metrics;

Grant Access

授予权限

sql
GRANT SELECT ON VIEW catalog.schema.orders_metrics TO `data-consumers`;
sql
GRANT SELECT ON VIEW catalog.schema.orders_metrics TO `data-consumers`;

Drop Metric View

删除指标视图

sql
DROP VIEW IF EXISTS catalog.schema.orders_metrics;
sql
DROP VIEW IF EXISTS catalog.schema.orders_metrics;

CLI Execution

CLI执行

The 
databricks experimental aitools tools
commands are experimental and their surface can change between CLI versions. Before relying on a subcommand (
query
, 
statement submit
/
get
, 
discover-schema
, 
get-default-warehouse
), confirm it exists with 
databricks experimental aitools tools --help
, and fall back to the stable Statement Execution API below if it isn't available. There is no stable 
databricks sql execute
/ 
execute-statement
verb.
For short statements (
SHOW
/
DESCRIBE
/
SELECT
), run the SQL inline:
bash
databricks experimental aitools tools query --warehouse WAREHOUSE_ID "SHOW TABLES IN catalog.schema"
For long DDL (
CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS $$...$$
), write the SQL to a 
.sql
file and submit the file — this avoids the 
$$
-heredoc escaping traps (bash variable expansion, 
sed
, JSON encoding) entirely:
bash
undefined
databricks experimental aitools tools
命令为实验性命令，其界面在不同CLI版本间可能会变更。 在依赖子命令（
query
、
statement submit
/
get
、
discover-schema
、
get-default-warehouse
）之前，请通过
databricks experimental aitools tools --help
确认其存在；如果不可用，请使用下方的稳定[Statement Execution API](#稳定替代方案：Statement Execution API)。目前没有稳定的
databricks sql execute
/ 
execute-statement
命令。
对于短语句（
SHOW
/
DESCRIBE
/
SELECT
），直接内联运行SQL：
bash
databricks experimental aitools tools query --warehouse WAREHOUSE_ID "SHOW TABLES IN catalog.schema"
对于长DDL（
CREATE OR REPLACE VIEW ... WITH METRICS LANGUAGE YAML AS $$...$$
），将SQL写入
.sql
文件并提交该文件——这样可以完全避免
$$
heredoc转义陷阱（bash变量展开、
sed
、JSON编码）：
bash
undefined

orders_metrics.sql holds the full CREATE OR REPLACE VIEW ... $$ ... $$ statement

orders_metrics.sql包含完整的CREATE OR REPLACE VIEW ... $$ ... $$语句

databricks experimental aitools tools statement submit --file orders_metrics.sql --warehouse WAREHOUSE_ID databricks experimental aitools tools statement get <statement_id> # blocks until terminal

This is the same file-based path the [Metric View Advisor](references/metric-view-advisor.md) uses for deployment — keep to one method so the two don't drift.
databricks experimental aitools tools statement submit --file orders_metrics.sql --warehouse WAREHOUSE_ID databricks experimental aitools tools statement get <statement_id> # 阻塞直到完成

这与[Metric View Advisor](references/metric-view-advisor.md)部署时使用的基于文件的路径相同——请保持使用同一种方法，避免两者出现差异。

Statement Execution API (stable alternative)

稳定替代方案：Statement Execution API

If the experimental 
aitools
commands aren't available, use the stable Statement Execution REST API, which takes the SQL as a JSON string:
bash
databricks api post /api/2.0/sql/statements/execute --json '{
  "warehouse_id": "WAREHOUSE_ID",
  "statement": "CREATE OR REPLACE VIEW catalog.schema.orders_metrics WITH METRICS LANGUAGE YAML AS $$\nversion: 1.1\nsource: catalog.schema.orders\ndimensions:\n  - name: Order Month\n    expr: DATE_TRUNC(MONTH, order_date)\nmeasures:\n  - name: Total Revenue\n    expr: SUM(total_price)\n$$"
}'
For long statements, template the JSON from a 
.sql
file rather than hand-escaping newlines.
如果实验性
aitools
命令不可用，请使用稳定的Statement Execution REST API，该API接受JSON格式的SQL字符串：
bash
databricks api post /api/2.0/sql/statements/execute --json '{
  "warehouse_id": "WAREHOUSE_ID",
  "statement": "CREATE OR REPLACE VIEW catalog.schema.orders_metrics WITH METRICS LANGUAGE YAML AS $$\nversion: 1.1\nsource: catalog.schema.orders\ndimensions:\n  - name: Order Month\n    expr: DATE_TRUNC(MONTH, order_date)\nmeasures:\n  - name: Total Revenue\n    expr: SUM(total_price)\n$$"
}'
对于长语句，请从
.sql
文件生成JSON模板，而非手动转义换行符。

Convert an Existing View to a Metric View

将现有视图转换为指标视图

To migrate a regular view to a metric view, treat its 
SELECT
source as the metric view's 
source
, then promote 
GROUP BY
columns to 
dimensions
and aggregations to 
measures
. The new metric view does not replace the original — it sits alongside it as a governed metric layer.
sql
-- Existing regular view (keep as-is or drop later)
-- CREATE VIEW catalog.schema.orders_summary AS
-- SELECT DATE_TRUNC('MONTH', order_date) AS month,
--        SUM(total_price) AS revenue,
--        COUNT(*) AS order_count
-- FROM catalog.schema.orders
-- GROUP BY 1;

-- Equivalent metric view (new artifact, governed)
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
  measures:
    - name: Revenue
      expr: SUM(total_price)
    - name: Order Count
      expr: COUNT(1)
$$
After verifying parity (
SELECT ... FROM <orders_metrics>
returns the same numbers as the original view), update downstream consumers and drop the original view.
要将常规视图迁移为指标视图，可将其
SELECT
源作为指标视图的
source
，然后将
GROUP BY
列升级为
dimensions
，聚合操作升级为
measures
。新的指标视图不会替换原视图——它作为受管控的指标层与原视图并存。
sql
-- 现有常规视图（可保留或稍后删除）
-- CREATE VIEW catalog.schema.orders_summary AS
-- SELECT DATE_TRUNC('MONTH', order_date) AS month,
--        SUM(total_price) AS revenue,
--        COUNT(*) AS order_count
-- FROM catalog.schema.orders
-- GROUP BY 1;

-- 等效的指标视图（新资产，受管控）
CREATE OR REPLACE VIEW catalog.schema.orders_metrics
WITH METRICS
LANGUAGE YAML
AS $$
  version: 1.1
  source: catalog.schema.orders
  dimensions:
    - name: Order Month
      expr: DATE_TRUNC('MONTH', order_date)
  measures:
    - name: Revenue
      expr: SUM(total_price)
    - name: Order Count
      expr: COUNT(1)
$$
验证一致性后（
SELECT ... FROM <orders_metrics>
返回与原视图相同的结果），更新下游消费者并删除原视图。

YAML Spec Quick Reference

YAML规范快速参考

yaml
version: 1.1                    # Required: "1.1" for DBR 17.2+ (semantic metadata needs 17.3+)
source: catalog.schema.table    # Required: source table/view
comment: "Description"          # Optional: metric view description
filter: column > value          # Optional: global WHERE filter

dimensions:                     # Required: at least one
  - name: Display Name          # Backtick-quoted in queries
    expr: sql_expression        # Column ref or SQL transformation
    comment: "Description"      # Optional (v1.1+)

measures:                       # Required: at least one
  - name: Display Name          # Queried via MEASURE(`name`)
    expr: AGG_FUNC(column)      # Must be an aggregate expression
    comment: "Description"      # Optional (v1.1+)

joins:                          # Optional: star/snowflake schema
  - name: dim_table
    source: catalog.schema.dim_table
    on: source.fk = dim_table.pk

materialization:                # Optional (experimental)
  schedule: every 6 hours
  mode: relaxed
yaml
version: 1.1                    # 必填：DBR 17.2+使用"1.1"（语义元数据需要17.3+）
source: catalog.schema.table    # 必填：源表/视图
comment: "Description"          # 可选：指标视图描述
filter: column > value          # 可选：全局WHERE过滤条件

dimensions:                     # 必填：至少一个
  - name: Display Name          # 查询时需加反引号引用
    expr: sql_expression        # 列引用或SQL转换
    comment: "Description"      # 可选（v1.1+）

measures:                       # 必填：至少一个
  - name: Display Name          # 通过MEASURE(`name`)查询
    expr: AGG_FUNC(column)      # 必须是聚合表达式
    comment: "Description"      # 可选（v1.1+）

joins:                          # 可选：星型/雪花型模式
  - name: dim_table
    source: catalog.schema.dim_table
    on: source.fk = dim_table.pk

materialization:                # 可选（实验性）
  schedule: every 6 hours
  mode: relaxed

Key Concepts

核心概念

Dimensions vs Measures

维度 vs 度量

DimensionsMeasures
PurposeCategorize and group dataAggregate numeric values
ExamplesRegion, Date, StatusSUM(revenue), COUNT(orders)
In queriesUsed in SELECT and GROUP BYWrapped in 
MEASURE()
SQL expressionsAny SQL expressionMust use aggregate functions
维度度量
用途对数据进行分类和分组聚合数值型数据
示例区域、日期、状态SUM(revenue)、COUNT(orders)
查询中使用方式用于SELECT和GROUP BY包裹在
MEASURE()
中
SQL表达式任意SQL表达式必须使用聚合函数

Why Metric Views vs Standard Views?

指标视图 vs 标准视图

FeatureStandard ViewsMetric Views
Aggregation locked at creationYesNo - flexible at query time
Safe re-aggregation of ratiosNoYes
Star/snowflake schema joinsManualDeclarative in YAML
MaterializationSeparate MV neededBuilt-in
AI/BI Genie integrationLimitedNative
特性标准视图指标视图
聚合在创建时锁定是否 - 查询时可灵活调整
支持安全地重新聚合比率否是
星型/雪花型模式关联手动实现在YAML中声明式定义
物化需要单独的物化视图内置支持
AI/BI Genie集成有限原生支持

Common Issues

常见问题

IssueSolution
SELECT * not supportedMust explicitly list dimensions and use MEASURE() for measures
"Cannot resolve column"Dimension/measure names with spaces need backtick quoting
JOIN at query time failsJoins must be in the YAML definition, not in the SELECT query
MEASURE() requiredAll measure references must be wrapped: 
MEASURE(\
name`)`
DBR version errorRequires Runtime 17.2+ for YAML v1.1, or 16.4+ for v0.1. Semantic metadata (
synonyms
/
display_name
/
format
) needs 17.3+
Materialization not workingRequires serverless compute enabled; currently experimental
问题解决方案
**不支持SELECT ***必须显式列出维度，并对度量使用MEASURE()
"无法解析列"包含空格的维度/度量名称需要加反引号引用
查询时关联失败关联必须在YAML定义中配置，而非在SELECT查询中
必须使用MEASURE()所有度量引用必须包裹在
MEASURE(\
name`)`中
DBR版本错误YAML v1.1需要Runtime 17.2+，v0.1需要16.4+。语义元数据（
synonyms
/
display_name
/
format
）需要17.3+
物化不生效需要启用无服务器计算；目前为实验性功能

Integrations

集成

Metric views work natively with:
  • AI/BI Dashboards - Use as datasets for visualizations
  • AI/BI Genie - Natural language querying of metrics
  • Alerts - Set threshold-based alerts on measures
  • SQL Editor - Direct SQL querying with MEASURE()
  • Catalog Explorer UI - Visual creation and browsing
指标视图原生支持以下集成：
  • AI/BI仪表板 - 用作可视化的数据集
  • AI/BI Genie - 对指标进行自然语言查询
  • 告警 - 基于度量设置阈值告警
  • SQL编辑器 - 使用MEASURE()直接进行SQL查询
  • Catalog Explorer UI - 可视化创建和浏览

Resources

资源