适用于：
Databricks SQL Databricks Runtime
基于 SQL 查询的结果集或基于 yaml 规范的指标视图构造没有物理数据的虚拟表。 ALTER VIEW 和 DROP VIEW 仅更改元数据。
若要执行此语句，您必须是 metastore 管理员，或者在目录和架构上具有 USE CATALOG 和 USE SCHEMA 特权，并且在目标架构中具有 CREATE TABLE 特权。
执行此命令的用户将成为视图的所有者。
语法
CREATE [ OR REPLACE ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name
[ column_list ]
[ with_clause |
COMMENT view_comment |
DEFAULT COLLATION collation_name |
TBLPROPERTIES clause |
LANGUAGE YAML ] [...]
AS { query | $$ yaml_string $$ }
with_clause
WITH { { schema_binding | METRICS } |
( { schema_binding | METRICS } [, ...] } )
schema_binding
WITH SCHEMA { BINDING | COMPENSATION | [ TYPE ] EVOLUTION }
column_list
( { column_alias [ COMMENT column_comment ] } [, ...] )
参数
或替换
如果已存在具有相同名称的视图，则会替换该视图。 若要替换现有视图，你必须是其所有者。
替换现有视图不会保留对原始视图授予的特权。 使用 ALTER VIEW 来保留特权。
CREATE OR REPLACE VIEW view_name等同于DROP VIEW IF EXISTS view_name后跟CREATE VIEW view_name。临时
TEMPORARY视图仅对创建视图的会话可见，并在会话结束时删除。GLOBAL TEMPORARY
适用于：
Databricks RuntimeGLOBAL TEMPORARY视图绑定到系统保留的临时模式global_temp。如果不存在
仅在该视图不存在时创建它。 如果已存在具有此名称的视图，则忽略
CREATE VIEW语句。最多可以指定
IF NOT EXISTS或OR REPLACE中的一项。-
新创建的视图的名称。 临时视图的名称不能带限定词。 完全限定的视图名称必须是独一无二的。
在
hive_metastore中创建的视图名称只能包含字母数字 ASCII 字符和下划线（INVALID_SCHEMA_OR_RELATION_NAME）。 指标
适用于：
Databricks SQL， Databricks Runtime 16.4 及以上版本，将视图标识为指标视图。 必须定义
LANGUAGE YAML视图，并且视图正文必须是有效的 yaml 规范。临时视图不支持此子句。
指标视图不支持
DEFAULT COLLATION和schema_binding条款。指标视图的 YAML 规范定义
dimensions和measures。dimensions是调用者通过其可以聚合度量值的视图列，而measures则用于定义视图的聚合。调用指标视图的使用者使用 度量值 表达式访问视图中的定义度量值，而不是使用聚合函数来定义度量值。
schema_binding
适用于：
Databricks Runtime 15.3 及更高版本（可选）指定视图如何适应由于基础对象定义的更改而导致的查询架构更改。
临时视图、指标视图或物化视图不支持此子句。
SCHEMA 捆绑
除以下情况外，如果查询的列列表发生更改，则视图将失效：
- 列列表包含一个 star 子句，并且还包含附加的列。 将忽略这些附加列。
- 一个或多个列的类型发生了更改，导致可以使用隐式强制转换规则将其安全地强制转换为原始列类型。
这是默认行为。
SCHEMA 补偿
除以下情况外，如果查询的列列表发生更改，则视图将失效：
- 列列表包含一个 star 子句，并且还包含附加的列。 将忽略这些附加列。
- 一个或多个列的类型发生了更改，导致可以使用显式 ANSI 强制转换规则将其强制转换为原始列类型。
SCHEMA 类型演变
当 SQL 编译器因检测到对视图的引用而发生这种更改时，视图会将查询列列表类型的任何更改纳入其自己的定义中。
SCHEMA 演化
- 此模式的行为类似于
SCHEMA TYPE EVOLUTION，如果视图不包含显式column_list，则还会采用列名更改或添加和删除的列。 - 仅当查询不再可分析，或可选视图
column_list与query选择列表中的表达式数量不再匹配时，视图才会失效。
- 此模式的行为类似于
column_list
（可选）在视图的查询结果中标记列。 如果提供列列表，则列别名数必须与查询中的表达式数匹配，或者对于指标视图，则必须与 YAML 规格中的表达式数匹配。 如果未指定列列表，则别名派生自视图主体。
-
列别名必须是唯一的。
column_comment
描述列别名的可选
STRING文本。
-
view_comment
提供视图级别注释的可选
STRING字面量。默认排序规则 collation_name
适用于：
Databricks SQL Databricks Runtime 16.3 及更高版本定义在
query中使用的默认排序规则。 如果未指定，则默认排序规则为UTF8_BINARYa。此子句不适用于指标视图。
-
可以选择设置一个或多个用户定义的属性。
AS 查询
从基表或其他视图中构造视图的查询。
该条款不适用于指标视图。
AS $$ yaml_string $$
定义指标视图的 yaml 规范。
示例
-- Create or replace view for `experienced_employee` with comments.
> CREATE OR REPLACE VIEW experienced_employee
(id COMMENT 'Unique identification number', Name)
COMMENT 'View for experienced employees'
AS SELECT id, name
FROM all_employee
WHERE working_years > 5;
-- Create a temporary view `subscribed_movies`.
> CREATE TEMPORARY VIEW subscribed_movies
AS SELECT mo.member_id, mb.full_name, mo.movie_title
FROM movies AS mo
INNER JOIN members AS mb
ON mo.member_id = mb.id;
-- Create a view with schema binding (default)
> CREATE TABLE emp(name STRING, income INT);
> CREATE VIEW emp_v WITH SCHEMA BINDING AS SELECT * FROM emp;
– The view ignores adding a column to the base table
> ALTER TABLE emp ADD COLUMN bonus SMALLINT;
> SELECT * FROM emp_v;
name income
---- ------
-- The view tolerates narrowing the underlying type
> CREATE OR REPLACE TABLE emp(name STRING, income SMALLINT, bonus SMALLINT);
> SELECT typeof(income) FROM emp_v;
INTEGER
– The view does not tolerate widening the underlying type
CREATE OR REPLACE TABLE emp(name STRING, income BIGINT, bonus SMALLINT);
> SELECT typeof(income) FROM emp_v;
Error
– Create a view with SCHEMA COMPENSATION
> CREATE TABLE emp(name STRING, income SMALLINT, bonus SMALLINT);
> CREATE VIEW emp_v WITH SCHEMA COMPENSATION AS SELECT * FROM emp;
-- The view tolerates widening the underlying type but keeps its own signature fixed
CREATE OR REPLACE TABLE emp(name STRING, income INTEGER, bonus INTEGER);
> SELECT typeof(income) FROM emp_v;
INTEGER
-- The view does not tolerate dropping a needed column
ALTER TABLE emp DROP COLUMN bonus;
> SELECT * FROM emp_v;
Error
– Create a view with SCHEMA EVOLUTION
> CREATE TABLE emp(name STRING, income SMALLINT);
> CREATE VIEW emp_v WITH SCHEMA EVOLUTION AS SELECT * FROM emp;
-- The view picks up additional columns
> ALTER TABLE emp ADD COLUMN bonus SMALLINT
> SELECT * FROM emp_v;
name income bonus
---- ------ -----
-- The view picks up renamed columns as well
> ALTER TABLE emp RENAME COLUMN income TO salary SMALLINT;
> SELECT * FROM emp_v;
name salary bonus
---- ------ -----
-- The view picks up changes to column types and dropped columns
> CREATE OR REPLACE TABLE emp(name STRING, salary BIGINT);
> SELECT *, typeof(salary)AS salary_type FROM emp_v;
name salary
---- ------
-- Create a view using a default collation of UTF8_BINARY
> CREATE VIEW v DEFAULT COLLATION UTF8_BINARY
AS SELECT 5::STRING AS text;
-- Creates a Metric View as specified in the YAML definition, with three dimensions and four measures representing the count of orders.
> CREATE OR REPLACE VIEW region_sales_metrics
(month COMMENT 'Month order was made',
status,
order_priority,
count_orders COMMENT 'Count of orders',
total_Revenue,
total_Revenue_p_Customer,
total_revenue_for_open_orders)
WITH METRICS
LANGUAGE YAML
COMMENT 'A Metric View for regional sales metrics.'
AS $$
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > '1990-01-01'
dimensions:
- name: month
expr: date_trunc('MONTH', o_orderdate)
- name: status
expr: case
when o_orderstatus = 'O' then 'Open'
when o_orderstatus = 'P' then 'Processing'
when o_orderstatus = 'F' then 'Fulfilled'
end
- name: prder_priority
expr: split(o_orderpriority, '-')[1]
measures:
- name: count_orders
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')
$$;
> DESCRIBE EXTENDED region_sales_metrics;
col_name data_type
------------------------------ --------------------------
month timestamp
status string
order_priority string
count_orders bigint measure
total_revenue decimal(28,2) measure
total_revenue_p_customer decimal(38,12) measure
total_revenue_for_open_orders decimal(28,2) measure
# Detailed Table Information
Catalog main
Database default
Table region_sales_metrics
Owner alf@melmak.et
Created Time Thu May 15 13:03:01 UTC 2025
Last Access UNKNOWN
Created By Spark
Type METRIC_VIEW
Comment A Metric View for regional sales metrics.
Use Remote Filtering false
View Text "
version: 0.1
source: samples.tpch.orders
filter: o_orderdate > '1990-01-01'
dimensions:
- name: month
expr: date_trunc('MONTH', o_orderdate)
- name: 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: count_orders
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')
"
Language YAML
Table Properties [metric_view.from.name=samples.tpch.orders, metric_view.from.type=ASSET, metric_view.where=o_orderdate > '1990-01-01']
-- Tracking total_revenue_per_customer by month in 1995
> SELECT extract(month from month) as month,
measure(total_revenue_per_customer)::bigint AS total_revenue_per_customer
FROM region_sales_metrics
WHERE extract(year FROM month) = 1995
GROUP BY ALL
ORDER BY ALL;
month total_revenue_per_customer
----- --------------------------
1 167727
2 166237
3 167349
4 167604
5 166483
6 167402
7 167272
8 167435
9 166633
10 167441
11 167286
12 167542
-- Tracking total_revenue_per_customer by month and status in 1995
> SELECT extract(month from month) as month,
status,
measure(total_revenue_per_customer)::bigint AS total_revenue_per_customer
FROM region_sales_metrics
WHERE extract(year FROM month) = 1995
GROUP BY ALL
ORDER BY ALL;
month status total_revenue_per_customer
----- --------- --------------------------
1 Fulfilled 167727
2 Fulfilled 161720
2 Open 40203
2 Processing 193412
3 Fulfilled 121816
3 Open 52424
3 Processing 196304
4 Fulfilled 80405
4 Open 75630
4 Processing 196136
5 Fulfilled 53460
5 Open 115344
5 Processing 196147
6 Fulfilled 42479
6 Open 160390
6 Processing 193461
7 Open 167272
8 Open 167435
9 Open 166633
10 Open 167441
11 Open 167286
12 Open 167542