使用 SQL 命令创建和管理语义视图¶
本主题介绍如何使用以下 SQL 命令创建和管理 语义视图。
创建语义视图¶
要创建语义视图，请执行 CREATE SEMANTIC VIEW 命令。您必须使用具有以下权限的角色：
对创建语义视图所在的架构具有 CREATE SEMANTIC VIEW 权限。
对创建语义视图所在的数据库和架构具有 USAGE 权限。
对语义视图中所用的表和视图具有 SELECT 权限。
语义视图必须是有效的。请参阅 Snowflake 如何验证语义视图。
该示例使用了 Snowflake 中提供的 TPC-H 示例数据。该数据集包含多个表，代表了一个简化的商业场景，其中包括客户、订单和订单明细项等内容。
该示例使用 TPC-H 数据集中的表创建了一个名为 tpch_rev_analysis 的语义视图。该语义视图定义了以下内容：
三个逻辑表（
orders、customers和line_items）。orders和customers表之间的关系。line_items和orders表之间的关系。将用于计算指标的事实。
展示：包括客户名称、订单日期，以及下单年份。
指标：每个订单的平均金额和每个订单中明细项的平均数量。
CREATE SEMANTIC VIEW tpch_rev_analysis
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
)
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers,
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders
)
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
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',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
COMMENT = 'Semantic view for revenue analysis';
接下来的章节将对此示例进行更详细的介绍：
备注
有关完整示例，请参阅 使用 SQL 创建语义视图的示例。
定义逻辑表¶
在 CREATE SEMANTIC VIEW 命令中，使用 TABLES 子句定义视图中的逻辑表。在本子句中，您可以执行以下操作：
指定物理表名和可选别名。
识别逻辑表中的以下列：
用作主键的列。
包含唯一值的列（主键列除外）。
您可以使用这些列在此语义视图中定义关系。
为表添加同义词（以增强可发现性）。
包括描述性注释。
在 前面介绍的示例 中，TABLES 子句定义了以下三个逻辑表：
一个
orders表，包含来自 TPC-Horders表的订单信息。一个
customers表，包含来自 TPC-Hcustomers表的客户信息。一个
line_item表，包含来自 TPC-Hlineitem表的订单明细项。
该示例确定了用作每个逻辑表主键的列，以便您可以 识别表之间的关系。
该示例还提供了用于描述逻辑表的同义词和注释，使数据更易于发现。
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
识别逻辑表之间的关系¶
在 CREATE SEMANTIC VIEW 命令中，使用 RELATIONSHIPS 子句来标识视图中各表之间的关系。对于每种关系，您可以指定以下内容：
关系的可选名称。
包含外键的逻辑表的名称。
表中用于定义外键的列。
包含主键或具有唯一值的列的逻辑表的名称。
表中定义主键或包含唯一值的列。
如果您已经在 TABLES 子句中为逻辑表指定 PRIMARY KEY，则无需在关系中指定主键列。
如果 TABLES 子句中逻辑表只有一个 UNIQUE 关键字，则无需在关系中指定相应的列。
在 前面介绍的示例 中，RELATIONSHIPS 子句指定了以下两种关系：
orders和customers表之间的关系。在orders表中，o_custkey是一个外键，指向customers表中的主键c_custkey。line_items和orders表之间的关系。在line_items表中，l_orderkey是一个外键，指向orders表中的主键o_orderkey。
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers (c_custkey),
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders (o_orderkey)
)
定义事实、维度和指标¶
在 CREATE SEMANTIC VIEW 命令中，使用 FACTS、DIMENSIONS 和 METRICS 子句定义语义视图中的事实、维度和指标。
您必须在语义视图中定义至少一个维度或指标。
对于每个事实、维度或指标，您可以指定以下内容：
它所属的逻辑表。
事实、维度或指标的名称。
用于计算它的 SQL 表达式。
可选的同义词和注释。
前面介绍的示例 定义了以下多个事实、维度和指标：
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
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',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
替换现有的语义视图¶
要替换现有的语义视图（例如更改视图的定义），请在执行 CREATE SEMANTIC VIEW 时指定 OR REPLACE。如果要保留对现有语义视图授予的任何权限，请指定 COPY GRANTS。例如：
CREATE OR REPLACE SEMANTIC VIEW tpch_rev_analysis
TABLES (
orders AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
PRIMARY KEY (o_orderkey)
WITH SYNONYMS ('sales orders')
COMMENT = 'All orders table for the sales domain',
customers AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
PRIMARY KEY (c_custkey)
COMMENT = 'Main table for customer data',
line_items AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM
PRIMARY KEY (l_orderkey, l_linenumber)
COMMENT = 'Line items in orders'
)
RELATIONSHIPS (
orders_to_customers AS
orders (o_custkey) REFERENCES customers,
line_item_to_orders AS
line_items (l_orderkey) REFERENCES orders
)
FACTS (
line_items.line_item_id AS CONCAT(l_orderkey, '-', l_linenumber),
orders.count_line_items AS COUNT(line_items.line_item_id),
line_items.discounted_price AS l_extendedprice * (1 - l_discount)
COMMENT = 'Extended price after discount'
)
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',
orders.average_line_items_per_order AS AVG(orders.count_line_items)
COMMENT = 'Average number of line items per order'
)
COMMENT = 'Semantic view for revenue analysis and different comment'
COPY GRANTS;
列出语义视图¶
要列出当前架构或指定架构中的语义视图，请执行 SHOW SEMANTIC VIEWS 命令。例如：
SHOW SEMANTIC VIEWS;
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
| created_on | name | database_name | schema_name | comment | owner | owner_role_type | extension |
|-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------|
| 2025-03-20 15:06:34.039 -0700 | MY_NEW_SEMANTIC_MODEL | MY_DB | MY_SCHEMA | A semantic model created through the wizard. | MY_ROLE | ROLE | ["CA"] |
| 2025-02-28 16:16:04.002 -0800 | O_TPCH_SEMANTIC_VIEW | MY_DB | MY_SCHEMA | NULL | MY_ROLE | ROLE | NULL |
| 2025-03-21 07:03:54.120 -0700 | TPCH_REV_ANALYSIS | MY_DB | MY_SCHEMA | Semantic view for revenue analysis | MY_ROLE | ROLE | NULL |
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
您还可以通过 查询 ACCOUNT_USAGE 和 INFORMATION_SCHEMA 架构中的视图来获取语义视图的信息。
查看有关语义视图的详细信息¶
要查看语义视图的详细信息，请执行 DESCRIBE SEMANTIC VIEW 命令。例如：
DESCRIBE SEMANTIC VIEW tpch_rev_analysis;
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| object_kind | object_name | parent_entity | property | property_value |
|--------------+------------------------------+---------------+--------------------------+----------------------------------------|
| NULL | NULL | NULL | COMMENT | Semantic view for revenue analysis |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | CUSTOMERS | NULL | BASE_TABLE_NAME | CUSTOMER |
| TABLE | CUSTOMERS | NULL | PRIMARY_KEY | ["C_CUSTKEY"] |
| TABLE | CUSTOMERS | NULL | COMMENT | Main table for customer data |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | TABLE | CUSTOMERS |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | EXPRESSION | customers.c_name |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | DATA_TYPE | VARCHAR(25) |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | SYNONYMS | ["customer name"] |
| DIMENSION | CUSTOMER_NAME | CUSTOMERS | COMMENT | Name of the customer |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | LINE_ITEMS | NULL | BASE_TABLE_NAME | LINEITEM |
| TABLE | LINE_ITEMS | NULL | PRIMARY_KEY | ["L_ORDERKEY","L_LINENUMBER"] |
| TABLE | LINE_ITEMS | NULL | COMMENT | Line items in orders |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | TABLE | LINE_ITEMS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_TABLE | ORDERS |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | FOREIGN_KEY | ["L_ORDERKEY"] |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS | LINE_ITEMS | REF_KEY | ["O_ORDERKEY"] |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | EXPRESSION | l_extendedprice * (1 - l_discount) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | DATA_TYPE | NUMBER(25,4) |
| FACT | DISCOUNTED_PRICE | LINE_ITEMS | COMMENT | Extended price after discount |
| FACT | LINE_ITEM_ID | LINE_ITEMS | TABLE | LINE_ITEMS |
| FACT | LINE_ITEM_ID | LINE_ITEMS | EXPRESSION | CONCAT(l_orderkey, '-', l_linenumber) |
| FACT | LINE_ITEM_ID | LINE_ITEMS | DATA_TYPE | VARCHAR(134217728) |
| TABLE | ORDERS | NULL | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA |
| TABLE | ORDERS | NULL | BASE_TABLE_SCHEMA_NAME | TPCH_SF1 |
| TABLE | ORDERS | NULL | BASE_TABLE_NAME | ORDERS |
| TABLE | ORDERS | NULL | SYNONYMS | ["sales orders"] |
| TABLE | ORDERS | NULL | PRIMARY_KEY | ["O_ORDERKEY"] |
| TABLE | ORDERS | NULL | COMMENT | All orders table for the sales domain |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | TABLE | ORDERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_TABLE | CUSTOMERS |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | FOREIGN_KEY | ["O_CUSTKEY"] |
| RELATIONSHIP | ORDERS_TO_CUSTOMERS | ORDERS | REF_KEY | ["C_CUSTKEY"] |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | TABLE | ORDERS |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | EXPRESSION | AVG(orders.count_line_items) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | DATA_TYPE | NUMBER(36,6) |
| METRIC | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS | COMMENT | Average number of line items per order |
| FACT | COUNT_LINE_ITEMS | ORDERS | TABLE | ORDERS |
| FACT | COUNT_LINE_ITEMS | ORDERS | EXPRESSION | COUNT(line_items.line_item_id) |
| FACT | COUNT_LINE_ITEMS | ORDERS | DATA_TYPE | NUMBER(18,0) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | TABLE | ORDERS |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | EXPRESSION | AVG(orders.o_totalprice) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | DATA_TYPE | NUMBER(30,8) |
| METRIC | ORDER_AVERAGE_VALUE | ORDERS | COMMENT | Average order value across all orders |
| DIMENSION | ORDER_DATE | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_DATE | ORDERS | EXPRESSION | o_orderdate |
| DIMENSION | ORDER_DATE | ORDERS | DATA_TYPE | DATE |
| DIMENSION | ORDER_DATE | ORDERS | COMMENT | Date when the order was placed |
| DIMENSION | ORDER_YEAR | ORDERS | TABLE | ORDERS |
| DIMENSION | ORDER_YEAR | ORDERS | EXPRESSION | YEAR(o_orderdate) |
| DIMENSION | ORDER_YEAR | ORDERS | DATA_TYPE | NUMBER(4,0) |
| DIMENSION | ORDER_YEAR | ORDERS | COMMENT | Year when the order was placed |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
获取语义视图的 SQL 语句¶
您可以调用 GET_DDL 函数来检索用于创建语义视图的 DDL 语句。
调用 GET_DDL 时，将 'SEMANTIC_VIEW' 作为对象类型传入。例如：
SELECT GET_DDL('SEMANTIC_VIEW', 'tpch_rev_analysis', TRUE);
+-----------------------------------------------------------------------------------+
| GET_DDL('SEMANTIC_VIEW', 'TPCH_REV_ANALYSIS', TRUE) |
|-----------------------------------------------------------------------------------|
| create or replace semantic view DYOSHINAGA_DB.DYOSHINAGA_SCHEMA.TPCH_REV_ANALYSIS |
| tables ( |
| ORDERS primary key (O_ORDERKEY) with synonyms=('sales orders') comment='All orders table for the sales domain', |
| CUSTOMERS as CUSTOMER primary key (C_CUSTKEY) comment='Main table for customer data', |
| LINE_ITEMS as LINEITEM primary key (L_ORDERKEY,L_LINENUMBER) comment='Line items in orders' |
| ) |
| relationships ( |
| ORDERS_TO_CUSTOMERS as ORDERS(O_CUSTKEY) references CUSTOMERS(C_CUSTKEY), |
| LINE_ITEM_TO_ORDERS as LINE_ITEMS(L_ORDERKEY) references ORDERS(O_ORDERKEY) |
| ) |
| facts ( |
| ORDERS.COUNT_LINE_ITEMS as COUNT(line_items.line_item_id), |
| LINE_ITEMS.DISCOUNTED_PRICE as l_extendedprice * (1 - l_discount) comment='Extended price after discount', |
| LINE_ITEMS.LINE_ITEM_ID as CONCAT(l_orderkey, '-', l_linenumber) |
| ) |
| dimensions ( |
| 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', |
| CUSTOMERS.CUSTOMER_NAME as customers.c_name with synonyms=('customer name') comment='Name of the customer' |
| ) |
| metrics ( |
| ORDERS.AVERAGE_LINE_ITEMS_PER_ORDER as AVG(orders.count_line_items) comment='Average number of line items per order', |
| ORDERS.ORDER_AVERAGE_VALUE as AVG(orders.o_totalprice) comment='Average order value across all orders' |
| ); |
+-----------------------------------------------------------------------------------+
移除语义视图¶
要移除语义视图，请执行 DROP SEMANTIC VIEW 命令。例如：
DROP SEMANTIC VIEW tpch_rev_analysis;
授予语义视图的权限¶
语义视图权限 列出了您可以授予的语义视图权限。
需要语义视图的以下权限才能使用该视图：
任意权限（例如 REFERENCE OR SELECT）都可用于对视图执行 DESCRIBE SEMANTIC VIEW 命令。
SELECT 是执行
SELECT ... FROM SEMANTIC_VIEW()所必需的。以上任一权限也用于让该视图出现在 SHOW SEMANTIC VIEWS 命令的输出结果中。
备注
目前，REFERENCES 权限允许您查询语义视图，但以后，查询语义视图的能力将仅限于具有 SELECT 权限的角色。
要在 Cortex Analyst 中使用并非自己拥有的语义视图，您必须使用一个拥有该视图的 REFERENCES 和 SELECT 权限的角色。
要授予语义视图的 REFERENCES 和 SELECT 权限，请使用 GRANT <privileges> ... TO ROLE 命令。例如，若要将名为 my_semantic_view 的语义视图的 REFERENCES 和 SELECT 权限授予角色 my_analyst_role，可以执行以下语句：
GRANT REFERENCES, SELECT ON SEMANTIC VIEW my_semantic_view TO ROLE my_analyst_role;
如果您有一个包含语义视图的架构，并希望将其共享给 Cortex Analyst 用户，则您可以使用 未来授权 授予该架构中今后创建的所有语义视图的权限。例如：
GRANT REFERENCES, SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA my_schema TO ROLE my_analyst_role;