Back to Details

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)
  • 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版本）
  • 拥有
    CAN USE
    权限的SQL仓库
  • 对源表拥有
    SELECT
    权限，对目标Schema拥有
    CREATE TABLE
    和
    USE SCHEMA
    权限

Quick Start

快速入门

Create a 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: 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
  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: 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 Syntaxyaml-reference.mdComplete YAML spec: dimensions, measures, joins, materialization
Patterns & Examplespatterns.mdCommon patterns: star schema, snowflake, filtered measures, window measures, ratios
主题文件描述
YAML语法yaml-reference.md完整YAML规范：维度、指标、关联、物化
模式与示例patterns.md常见模式：星型模式、雪花型模式、过滤指标、窗口指标、比率

MCP Tools

MCP工具

Use the 
manage_metric_views
tool for all metric view operations:
ActionDescription
create
Create a metric view with dimensions and measures
alter
Update a metric view's YAML definition
describe
Get the full definition and metadata
query
Query measures grouped by dimensions
drop
Drop a metric view
grant
Grant SELECT privileges to users/groups
使用
manage_metric_views
工具执行所有指标视图操作：
操作描述
create
创建包含维度和指标的指标视图
alter
更新指标视图的YAML定义
describe
获取完整定义和元数据
query
按维度分组查询指标
drop
删除指标视图
grant
向用户/组授予SELECT权限

Create via MCP

通过MCP创建

python
manage_metric_views(
    action="create",
    full_name="catalog.schema.orders_metrics",
    source="catalog.schema.orders",
    or_replace=True,
    comment="Orders KPIs for sales analysis",
    filter_expr="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"},
    ],
)
python
manage_metric_views(
    action="create",
    full_name="catalog.schema.orders_metrics",
    source="catalog.schema.orders",
    or_replace=True,
    comment="Orders KPIs for sales analysis",
    filter_expr="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 via MCP

通过MCP查询

python
manage_metric_views(
    action="query",
    full_name="catalog.schema.orders_metrics",
    query_measures=["Total Revenue", "Order Count"],
    query_dimensions=["Order Month"],
    where="extract(year FROM `Order Month`) = 2024",
    order_by="ALL",
    limit=100,
)
python
manage_metric_views(
    action="query",
    full_name="catalog.schema.orders_metrics",
    query_measures=["Total Revenue", "Order Count"],
    query_dimensions=["Order Month"],
    where="extract(year FROM `Order Month`) = 2024",
    order_by="ALL",
    limit=100,
)

Describe via MCP

通过MCP查看详情

python
manage_metric_views(
    action="describe",
    full_name="catalog.schema.orders_metrics",
)
python
manage_metric_views(
    action="describe",
    full_name="catalog.schema.orders_metrics",
)

Grant Access

授予访问权限

python
manage_metric_views(
    action="grant",
    full_name="catalog.schema.orders_metrics",
    principal="data-consumers",
    privileges=["SELECT"],
)
python
manage_metric_views(
    action="grant",
    full_name="catalog.schema.orders_metrics",
    principal="data-consumers",
    privileges=["SELECT"],
)

YAML Spec Quick Reference

YAML规范快速参考

yaml
version: 1.1                    # Required: "1.1" for DBR 17.2+
comment: "Description"          # Optional: metric view description
source: catalog.schema.table    # Required: source table/view
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                    # Required: "1.1" for DBR 17.2+
comment: "Description"          # Optional: metric view description
source: catalog.schema.table    # Required: source table/view
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

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(收入)、COUNT(订单)
查询中使用方式用于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
Materialization not workingRequires serverless compute enabled; currently experimental
问题解决方案
**不支持SELECT ***必须显式列出维度，指标需使用MEASURE()包裹
"无法解析列"包含空格的维度/指标名称需要使用反引号包裹
查询时关联失败关联必须在YAML定义中配置，而不是在SELECT查询中
必须使用MEASURE()所有指标引用都必须包裹在
MEASURE(\
名称`)`中
DBR版本错误YAML v1.1需要Runtime 17.2+，v0.1需要16.4+
物化功能不工作需要启用无服务器计算；当前为实验性功能

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

资源