適用於：
Databricks SQL Databricks Runtime
建構虛擬數據表，此虛擬數據表沒有根據 SQL 查詢的結果集或以 yaml 規格為基礎的計量檢視。 ALTER VIEW 和 DROP VIEW 只會變更元數據。
若要執行此語句，您必須是中繼存放區系統管理員，或具有 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 ] } [, ...] )
參數
或取代
如果已有相同名稱的檢視存在，則會加以取代。 若要取代現有的檢視，您必須是其擁有者。
取代現有的檢視並不會保留在原始檢視或
table_id上所授予的權限。 使用 ALTER VIEW 來保留許可權。CREATE OR REPLACE VIEW view_name相當於DROP VIEW IF EXISTS view_name，後面接著CREATE VIEW view_name。暫時的
TEMPORARY檢視只會顯示在建立這些檢視的會話中，而且會在會話結束時卸除。全域暫存
適用於：
Databricks RuntimeGLOBAL TEMPORARY檢視會繫結至系統保留的暫存架構global_temp。如果不存在
只有在檢視不存在時，才會建立檢視。
CREATE VIEW語句將被忽略，如果同名的視圖已經存在。您最多可以指定
IF NOT EXISTS或OR REPLACE中的一個。-
新建立的檢視的名稱。 暫存檢視的名稱不得限定。 完整檢視名稱必須唯一。
在
hive_metastore中建立的檢視名稱只能包含英文字母、數字和下劃線（INVALID_SCHEMA_OR_RELATION_NAME）。 指標
適用於：
Databricks SQL Databricks Runtime 16.4 和更高版本 Unity Catalog 仅适用將檢視識別為計量檢視。 檢視必須以
LANGUAGE YAML定義，而且檢視主體必須是有效的 yaml 規格。在 Databricks Runtime 17.2 之前，暫時檢視不支援此子句。
計量檢視不支援
DEFAULT COLLATION和schema_binding子句。計量檢視的 YAML 規格定義了
dimensions和measures。dimensions是檢視的數據行，叫用者可以匯總量值，而measures定義檢視的匯總。計量檢視的叫用者會使用 量值 表達式來存取已定義的量值，而不是指定聚合函數。
schema_binding
適用於：
Databricks Runtime 15.3 和更新版本選擇性地指定檢視如何因基礎物件定義中的變更而適應查詢架構的變更。
暫存檢視、計量檢視或具體化檢視不支持這個子句。
SCHEMA 捆綁
如果查詢資料列清單變更，但下列條件除外，檢視會變成無效：
- 欄位清單包含星號子句，此外還有其他欄位。 會忽略這些額外的欄。
- 一或多個數據行的類型會以允許它們使用隱含轉換規則安全地轉換成原始數據行類型的方式變更。
SCHEMA 補償
如果查詢資料列清單變更，但下列條件除外，檢視會變成無效：
- 欄位清單包含星號子句，此外還有其他欄位。 會忽略這些額外的欄。
- 一個或多個數據行的類型會以允許它們使用明確的 ANSI 轉換規則轉換成原始數據行類型的方式變更。
此為預設行為。
SCHEMA 類型演進
當 SQL 編譯程式偵測到這類變更以響應檢視的參考時，檢視會將查詢資料行清單中類型的任何變更採用到自己的定義中。
SCHEMA 演化
- 這種模式的行為就像
SCHEMA TYPE EVOLUTION，如果檢視不包含明確的column_list，也會採用欄位名稱的變更，或新增和刪除的欄位。 - 只有當查詢無法再解析，或可選檢視
column_list不再匹配queryselect-list 中的運算式數目時，檢視才會變成無效。
- 這種模式的行為就像
column_list
選擇性地在檢視的查詢結果中標記數據行。 如果您提供一個欄位清單，欄位別名的數目必須與查詢中的表達式數目一致，或在度量檢視的情況下，必須符合 YAML 規格。 如果未指定欄位清單，別名將從檢視的主體中衍生。
-
欄位別名必須是唯一的。
column_comment
描述欄位別名的可選擇的
STRING常值。
-
view_comment
提供檢視層級註解的可選
STRING常數。預設定序 collation_name
適用於：
Databricks SQL Databricks Runtime 16.3 和以上版本定義在
query內使用的預設排序規則。 如果未指定，預設的排序規則會衍生自建立檢視定義的模式。度量檢視不支持此子句。
-
選擇性地設定一個或多個使用者定義的屬性。
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_per_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_per_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