CREATE SEMANTIC VIEW

创建语义视图。语义视图将物理表、JOIN 关系和业务度量封装为一个统一的逻辑模型。

语法

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name> TABLES ( logicalTable [ , ... ] ) [ RELATIONSHIPS ( relationshipDef [ , ... ] ) ] [ VARIABLES ( variableDef [ , ... ] ) ] [ FACTS ( factExpression [ , ... ] ) ] [ FILTER <boolean_expression> ] [ DIMENSIONS ( dimensionExpression [ , ... ] ) ] [ METRICS ( { metricExpression | windowFunctionMetricExpression } [ , ... ] ) ] [ COMMENT = '<string_literal>' ] [ AI_VERIFIED_QUERIES ( verifiedQuery [ , ... ] ) ]

子句语法

logicalTable

logicalTable ::= [ <table_alias> AS ] <table_name> | [ <table_alias> AS ] ( <subquery> ) [ PRIMARY KEY ( <primary_key_column> [ , ... ] ) ] [ FOREIGN KEY ( <fk_column> [ , ... ] ) REFERENCES <ref_alias> [ ( <ref_column> [ , ... ] ) ] [ ASOF <ref_column> ] ] [ UNIQUE ( <unique_column> [ , ... ] ) [ ... ] ] [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ] [ COMMENT = '<string_literal>' ]

每个逻辑表至少需要一个约束（

PRIMARY KEY

PRIMARY KEY

、

FOREIGN KEY

FOREIGN KEY

或

UNIQUE

UNIQUE

）。

relationshipDef

relationshipDef ::= [ <relationship_name> AS ] <child_alias> ( <child_column> [ , ... ] ) REFERENCES <parent_alias> [ ( <parent_column> [ , ... ] ) ]

与 TABLES 中内联

FOREIGN KEY

FOREIGN KEY

等价。每个关系可指定名称，便于在

SHOW SEMANTIC RELATIONSHIPS

SHOW SEMANTIC RELATIONSHIPS

中识别。

variableDef

variableDef ::= <name> <data_type> [ DEFAULT <expr> ] [ COMMENT = '<string_literal>' ]

factExpression / dimensionExpression

factExpression ::= [ { PRIVATE | PUBLIC } ] <table_alias>.<fact_name> AS <sql_expr> [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ] [ COMMENT = '<string_literal>' ] dimensionExpression ::= [ PUBLIC ] <table_alias>.<dimension_name> AS <sql_expr> [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ] [ COMMENT = '<string_literal>' ]

metricExpression

metricExpression ::= [ { PRIVATE | PUBLIC } ] <table_alias>.<metric_name> [ USING ( <relationship_name> [ , ... ] ) ] [ NON ADDITIVE BY ( <dimension_alias> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ , ... ] ) ] AS <sql_expr> [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ] [ COMMENT = '<string_literal>' ]

windowFunctionMetricExpression

windowFunctionMetricExpression ::= [ { PRIVATE | PUBLIC } ] <table_alias>.<metric_name> AS <window_function>( <expr> ) OVER ( [ PARTITION BY { <exprs> | EXCLUDING <dimensions> } ] [ ORDER BY <exprs> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] ] [ <window_frame_clause> ] ) [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ] [ COMMENT = '<string_literal>' ]

verifiedQuery

verifiedQuery ::= <verified_query_name> AS ( QUESTION '<question>' [ VERIFIED_AT <timestamp> ] [ ONBOARDING_QUESTION <boolean> ] [ VERIFIED_BY '<contact>' ] SQL '<verified_query>' )

filterExpression

filterExpression ::= <boolean_expression>

FILTER 子句指定一个布尔表达式作为全局预过滤条件，自动应用于所有针对该语义视图的查询。支持：

比较运算：
```
=
```
=
、
```
<>
```
<>
、
```
>
```
>
、
```
>=
```
>=
、
```
<
```
<
、
```
<=
```
<=
逻辑运算：
```
AND
```
AND
、
```
OR
```
OR
、
```
NOT
```
NOT
IN 条件：
```
IN ( ... )
```
IN ( ... )
、
```
NOT IN ( ... )
```
NOT IN ( ... )
跨表引用：可以引用当前语义视图中任意逻辑表的列，使用全限定名（如
```
table_alias.column
```
table_alias.column
）

子句顺序：FILTER 位于

FACTS

FACTS

和

DIMENSIONS

DIMENSIONS

之间，不可调换。

参数说明

顶层参数

参数	描述
`<name>` <name>	语义视图名称，在同一 Schema 内唯一。支持多段标识符（如 `db.schema.view` db.schema.view ）。
`OR REPLACE` OR REPLACE	如果视图已存在，则原子替换。旧视图被删除，新视图在同一事务中创建。
`IF NOT EXISTS` IF NOT EXISTS	如果视图已存在，则不执行任何操作（不报错）。
`TABLES (...)` TABLES (...)	必填。定义逻辑表。
`RELATIONSHIPS (...)` RELATIONSHIPS (...)	可选。命名外键关系声明，与 TABLES 中内联 FOREIGN KEY 等价。
`VARIABLES (...)` VARIABLES (...)	可选。声明模板变量，可在其他子句中引用。
`FACTS (...)` FACTS (...)	可选。定义行级事实表达式，可为 METRICS 提供基础数据。
`FILTER <expr>` FILTER <expr>	可选。全局过滤条件，应用于所有查询。
`DIMENSIONS (...)` DIMENSIONS (...)	可选。定义分析维度，用于分组和过滤。须至少指定 DIMENSIONS 或 METRICS 之一。
`METRICS (...)` METRICS (...)	可选。定义可聚合的度量指标。须至少指定 DIMENSIONS 或 METRICS 之一。
`COMMENT = '<string_literal>'` COMMENT = '<string_literal>'	可选。视图的注释。
`AI_VERIFIED_QUERIES (...)` AI_VERIFIED_QUERIES (...)	可选。注册 AI 验证查询，用于 NL2SQL 验证。

逻辑表参数

参数	描述
`<alias> AS <table_name>` <alias> AS <table_name>	表别名。在关系、维度、指标、事实中使用该别名引用。省略别名时使用物理表名。
`<alias> AS ( <subquery> )` <alias> AS ( <subquery> )	内联子查询作为数据源。
`PRIMARY KEY ( <col> [,...] )` PRIMARY KEY ( <col> [,...] )	主键列。每张表必须至少定义一个约束。
`FOREIGN KEY ( <col> [,...] ) REFERENCES <alias>` FOREIGN KEY ( <col> [,...] ) REFERENCES <alias>	外键引用，定义表间关系（粒度层级）。被引用表为父表（粒度更粗）。
`REFERENCES <alias> ( ASOF <col> )` REFERENCES <alias> ( ASOF <col> )	ASOF JOIN 标记。ASOF 列类型必须与 FK 列兼容。
`UNIQUE ( <col> [,...] )` UNIQUE ( <col> [,...] )	唯一约束列。
`WITH SYNONYMS [ = ] ( '<alias>' [,...] )` WITH SYNONYMS [ = ] ( '<alias>' [,...] )	可选。同义词列表。
`COMMENT = '<string_literal>'` COMMENT = '<string_literal>'	可选。注释。

维度 / 事实 / 指标通用参数

参数	描述
`PRIVATE` PRIVATE / `PUBLIC` PUBLIC	访问级别。PRIVATE 指标/事实不能被查询直接引用。维度总是 PUBLIC。默认：PUBLIC。
`<table_alias>.<name> AS <sql_expr>` <table_alias>.<name> AS <sql_expr>	元素名称及对应的 SQL 表达式。
`WITH SYNONYMS [ = ] ( '<alias>' [,...] )` WITH SYNONYMS [ = ] ( '<alias>' [,...] )	可选。同义词列表。
`COMMENT = '<string_literal>'` COMMENT = '<string_literal>'	可选。注释。

维度特有属性

属性	描述
`is_time = true` is_time = true	标记该维度为时间维度。
`is_unique = true` is_unique = true	标记该维度包含唯一值。
`enum_values = [ <val>, ... ]` enum_values = [ <val>, ... ]	可选。枚举值列表，用于提示和建议。

指标特有参数

参数	描述
`USING ( <relationship> [,...] )` USING ( <relationship> [,...] )	可选。当两个逻辑表之间存在多条关系路径时，指定使用哪条关系。
`NON ADDITIVE BY ( <dim> [{ASC\|DESC}] [NULLS {FIRST\|LAST}] [,...] )` NON ADDITIVE BY ( <dim> [{ASC\|DESC}] [NULLS {FIRST\|LAST}] [,...] )	可选。使指标成为半可加指标。按指定维度排序后取聚合范围内的最后一条记录。默认为 `ASC` ASC （取最新值）。排序列和窗口函数 ORDER BY 必须引用已声明的维度别名，不能引用原始列。

使用说明

至少需要 DIMENSIONS 或 METRICS — 语义视图必须至少定义一个维度或指标。
子句顺序 — 必须按语法中列出的顺序书写子句。
原子替换 —
```
CREATE OR REPLACE
```
CREATE OR REPLACE
是原子操作，旧视图在新视图创建完成前不会被删除。
FOREIGN KEY 定义粒度 — 被引用表是父表（粒度更粗），引用方是子表（粒度更细）。
派生指标 — 指标表达式可以引用同一视图中其他已定义的指标（使用全限定名，不带表别名前缀），引擎自动按依赖顺序展开。

权限要求

权限	对象	说明
`CREATE SEMANTIC VIEW` CREATE SEMANTIC VIEW	Schema	在指定 Schema 中创建语义视图
`SELECT` SELECT	表 / 视图	对所有引用的物理表或视图

示例

最小示例

CREATE SEMANTIC VIEW sales_analysis TABLES ( orders AS db.orders PRIMARY KEY (o_orderkey) FOREIGN KEY (o_custkey) REFERENCES customers, customers AS db.customer PRIMARY KEY (c_custkey) ) DIMENSIONS ( orders.order_date AS o_orderdate, customers.cust_name AS c_name ) METRICS ( orders.total_revenue AS SUM(o_totalprice), orders.order_count AS COUNT(o_orderkey) );

多表 Star Schema

CREATE SEMANTIC VIEW tpch_rev_analysis TABLES ( line_items AS tpch.lineitem PRIMARY KEY (l_orderkey, l_linenumber) FOREIGN KEY (l_orderkey) REFERENCES orders COMMENT 'Line items in orders', orders AS tpch.orders PRIMARY KEY (o_orderkey) FOREIGN KEY (o_custkey) REFERENCES customers WITH SYNONYMS ('sales orders') COMMENT 'All orders', customers AS tpch.customer PRIMARY KEY (c_custkey) COMMENT 'Customer master' ) DIMENSIONS ( customers.customer_name AS customers.c_name WITH SYNONYMS = ('customer name') COMMENT 'Full customer name', orders.order_date AS o_orderdate is_time = true COMMENT 'Order date', orders.order_year AS YEAR(o_orderdate) COMMENT 'Year extracted from order date' ) METRICS ( customers.customer_count AS COUNT(c_custkey) COMMENT 'Total customer count', orders.avg_order_value AS AVG(o_totalprice) COMMENT 'Average order value' ) COMMENT 'Revenue analysis semantic view';

使用 RELATIONSHIPS 子句

CREATE SEMANTIC VIEW sales_with_rels TABLES ( ord AS orders PRIMARY KEY (o_orderkey), cust AS customer PRIMARY KEY (c_custkey) ) RELATIONSHIPS ( order_customer AS ord (o_custkey) REFERENCES cust (c_custkey) ) DIMENSIONS ( cust.name AS cust.c_name, ord.dt AS o_orderdate ) METRICS ( ord.revenue AS SUM(o_totalprice) );

效果等价于在

orders

orders

表定义中加入

FOREIGN KEY (o_custkey) REFERENCES cust

FOREIGN KEY (o_custkey) REFERENCES cust

。命名关系可通过

SHOW SEMANTIC RELATIONSHIPS IN sales_with_rels

SHOW SEMANTIC RELATIONSHIPS IN sales_with_rels

查看。

ASOF JOIN

单列 ASOF：

CREATE SEMANTIC VIEW sales_asof TABLES ( ticks AS tick_data PRIMARY KEY (symbol, ts) FOREIGN KEY (ts) REFERENCES dates (ASOF date), dates AS date_dim PRIMARY KEY (date) ) DIMENSIONS (tick_sym AS ticks.symbol) METRICS (avg_price AS AVG(ticks.price));

复合键等值匹配 + ASOF 时间近似匹配：

CREATE SEMANTIC VIEW sales_asof_composite TABLES ( t1 AS lineitem PRIMARY KEY (l_orderkey, l_linenumber) FOREIGN KEY (l_orderkey, l_shipdate) REFERENCES t2 (o_orderkey, ASOF o_orderdate), t2 AS orders PRIMARY KEY (o_orderkey) ) DIMENSIONS (qty AS t1.l_quantity) METRICS (cnt AS COUNT(t1.l_quantity));

SHOW SEMANTIC RELATIONSHIPS

SHOW SEMANTIC RELATIONSHIPS

中关系类型显示为

ASOF

ASOF

。

使用 USING 指定关系路径

当两个逻辑表之间存在多条关系路径时，使用

USING

USING

指定使用哪条关系：

CREATE SEMANTIC VIEW sales_using TABLES ( ord AS orders PRIMARY KEY (o_orderkey), cust AS customer PRIMARY KEY (c_custkey), nat AS nation PRIMARY KEY (n_nationkey) ) RELATIONSHIPS ( ord_cust AS ord (o_custkey) REFERENCES cust (c_custkey), cust_nat AS cust (c_nationkey) REFERENCES nat (n_nationkey) ) DIMENSIONS (dt AS ord.o_orderdate, cust_name AS cust.c_name) METRICS ( rev USING (ord_cust) AS SUM(ord.o_totalprice) );

半可加指标（NON ADDITIVE BY）

CREATE SEMANTIC VIEW inventory_snapshots TABLES (inv AS inventory PRIMARY KEY (product_id, snapshot_date)) DIMENSIONS ( product AS inv.product_id, snap_dt AS inv.snapshot_date, snap_yr AS YEAR(inv.snapshot_date) ) METRICS ( last_qty NON ADDITIVE BY (snap_dt ASC) AS MAX(inv.quantity) );

适用于库存快照、账户余额等场景。

窗口函数指标

CREATE SEMANTIC VIEW sales_running TABLES (d AS orders PRIMARY KEY (o_orderkey)) DIMENSIONS (dt AS d.o_orderdate) METRICS ( running_total AS SUM(d.o_totalprice) OVER (ORDER BY dt) );

窗口函数指标支持

PARTITION BY

PARTITION BY

、

ORDER BY

ORDER BY

和窗口帧子句。

PARTITION BY

PARTITION BY

ORDER BY

ORDER BY

中的列必须引用已声明的维度别名（如

dt

），不能直接引用原始列。

派生指标

CREATE SEMANTIC VIEW sales_derived TABLES ( ord AS orders PRIMARY KEY (o_orderkey), cust AS customer PRIMARY KEY (c_custkey) ) RELATIONSHIPS ( ord_cust AS ord (o_custkey) REFERENCES cust (c_custkey) ) DIMENSIONS (cust_name AS cust.c_name) METRICS ( total_revenue AS SUM(ord.o_totalprice), order_count AS COUNT(ord.o_orderkey), avg_revenue AS total_revenue / order_count );

派生指标引用同一视图中其他已定义的指标，使用全限定名（不带表别名前缀），引擎自动按依赖顺序展开。

内联子查询作为数据源

CREATE SEMANTIC VIEW sv_inline TABLES ( recent AS (SELECT * FROM orders WHERE o_orderdate >= '2024-01-01') PRIMARY KEY (o_orderkey) ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice) );

使用普通视图作为数据源

CREATE OR REPLACE VIEW v_recent_orders AS SELECT o_orderkey, o_totalprice, o_orderdate FROM orders WHERE o_orderdate >= '2024-01-01'; CREATE SEMANTIC VIEW sv_over_view TABLES ( v AS v_recent_orders PRIMARY KEY (o_orderkey) ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice) );

使用 VARIABLES

CREATE SEMANTIC VIEW sv_with_vars TABLES ( ord AS orders PRIMARY KEY (o_orderkey) ) VARIABLES ( min_date DATE DEFAULT '2024-01-01' COMMENT 'start of period' ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice) );

使用 AI_VERIFIED_QUERIES

CREATE SEMANTIC VIEW sv_with_queries TABLES ( ord AS orders PRIMARY KEY (o_orderkey) ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice) ) AI_VERIFIED_QUERIES ( q1 AS ( QUESTION 'total revenue by year' SQL 'SELECT o_orderdate, SUM(o_totalprice) FROM ord GROUP BY o_orderdate' ) );

使用 IF NOT EXISTS（幂等创建）

CREATE SEMANTIC VIEW IF NOT EXISTS sales_analysis TABLES ( orders AS orders PRIMARY KEY (o_orderkey) ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice) );

替换已有视图

CREATE OR REPLACE SEMANTIC VIEW sales_analysis TABLES ( orders AS orders PRIMARY KEY (o_orderkey) ) DIMENSIONS ( order_date AS o_orderdate ) METRICS ( revenue AS SUM(o_totalprice), avg_revenue AS AVG(o_totalprice), order_count AS COUNT(o_orderkey) );