DROP SEMANTIC VIEW IF EXISTS tpch_rev_analysis;
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
customers AS TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
orders AS TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
FOREIGN KEY (o_custkey) REFERENCES customers
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
line_items AS TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
FOREIGN KEY (l_orderkey) REFERENCES orders
COMMENT = 'Line items in orders'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name')
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders'
)
COMMENT = 'Semantic view for revenue analysis';
DROP SEMANTIC VIEW IF EXISTS tpch_rev_analysis;
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
customers AS TPCH_AI.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
orders AS TPCH_AI.ORDERS
PRIMARY KEY (o_orderkey)
FOREIGN KEY (o_custkey) REFERENCES customers
WITH SYNONYMS ('orders_synonyms')
COMMENT = 'All orders table for the sales domain',
line_items AS TPCH_AI.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
FOREIGN KEY (l_orderkey) REFERENCES orders
COMMENT = 'Line items in orders'
)
FILTERS (
customers.is_ny AS customers.c_city = 'New York'
)
DIMENSIONS (
customers.customer_name AS customers.c_name
WITH SYNONYMS = ('customer name', 'dimensions_synonyms')
is_unique = true
COMMENT = 'Name of the customer',
orders.order_date AS o_orderdate
is_time = true
enum_values = [date'2025-01-01', date'2025-06-01', date'2025-12-01']
COMMENT = 'Date when the order was placed',
orders.order_year AS YEAR(o_orderdate)
COMMENT = 'Year when the order was placed'
)
METRICS (
customers.customer_count AS COUNT(c_custkey)
COMMENT = 'Count of number of customers',
orders.order_average_value AS AVG(orders.o_totalprice)
COMMENT = 'Average order value across all orders'
)
COMMENT = 'Semantic view for revenue analysis';
SELECT * FROM semantic_view(
tpch_rev_analysis,
DIMENSIONS customers.customer_city,
DIMENSIONS customers.customer_name,
METRICS orders.order_average_value
) WHERE customer_city = 'New York';
传统 SQL 查询对比
为了说明语义视图如何简化查询，以下对比展示了同一分析需求分别使用传统 SQL 和语义视图的写法。
传统 SQL 查询：
使用传统方式，您需要手动编写 JOIN 逻辑、指定连接条件，并显式声明 GROUP BY 子句：
SELECT
o.o_orderdate,
c.c_name,
AVG(o.o_totalprice) AS avg_value
FROM TPCH_SF1.ORDERS o
JOIN TPCH_SF1.CUSTOMER c ON o.o_custkey = c.c_custkey
GROUP BY o.o_orderdate, c.c_name;
Semantic View: tpch_rev_analysis
DIMENSIONS:
- customers.customer_name [is_unique=true] Name of the customer
- customers.customer_city City of the customer
- orders.order_date [is_time=true] Date when the order was placed
- orders.order_year Year when the order was placed
METRICS:
- customers.customer_count Count of number of customers
- orders.order_average_value Average order value across all orders
LH-get_semantic_view_dims
LH-get_semantic_view_dims
— 获取维度列表
获取指定语义视图的所有维度定义，返回结构化的维度信息，包含维度名、物理列映射、元数据属性（
is_unique
is_unique
、
is_time
is_time
、
enum_values
enum_values
）及注释。
主要参数：
参数
类型
必填
说明
semantic_view
semantic_view
string
✅
语义视图名称（如
tpch_rev_analysis
tpch_rev_analysis
）
schema_name
schema_name
string
—
所在 Schema 名称
LH-semantic-view-dim-add
LH-semantic-view-dim-add
— 动态添加维度
向已存在的语义视图中追加一个或多个维度，无需重建视图，支持指定同义词、注释及元数据属性。
主要参数：
参数
类型
必填
说明
semantic_view_name
semantic_view_name
string
✅
目标语义视图名称
dimensions
dimensions
array
✅
待添加的维度列表
`` 数组元素结构：
字段
类型
必填
说明
logical_table
logical_table
string
✅
所属逻辑表别名
dimension_name
dimension_name
string
✅
维度名称
column_name
column_name
string
✅
对应物理列名
synonyms
synonyms
array
✅
同义词列表（可为空数组）
comment
comment
string
✅
维度描述
示例：
mcp.call("LH-semantic-view-dim-add",
semantic_view_name="tpch_rev_analysis",
dimensions=[{
"logical_table": "customers",
"dimension_name": "customer_city",
"column_name": "c_city",
"synonyms": ["city", "customer city"],
"comment": "City of the customer"
}]
)
