使用 SQL 命令创建和管理语义视图¶

本主题介绍如何使用以下 SQL 命令创建和管理语义视图：

本主题还会解释如何调用以下存储过程和函数，以根据语义模型规范创建语义视图，以及获取语义视图的规范：

创建语义视图所需的权限¶

要创建语义视图，必须使用具有以下权限的角色：

对创建语义视图所在的架构具有 CREATE SEMANTIC VIEW 权限。
对创建语义视图所在的数据库和架构具有 USAGE 权限。
对语义视图中所用的表和视图具有 SELECT 权限。

有关查询语义视图所需权限的信息，请参阅查询语义视图所需的权限。

创建语义视图¶

要创建语义视图，您可以：

Run the CREATE SEMANTIC VIEW command.
如果您想根据语义模型的 YAML 规范创建语义视图，可以调用 SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML 存储过程。

语义视图必须是有效的。请参阅 Snowflake 如何验证语义视图。

接下来的各部分将介绍如何创建语义视图：

使用 CREATE SEMANTIC VIEW 命令
根据 YAML 规范创建语义视图

使用 CREATE SEMANTIC VIEW 命令¶

以下示例使用 CREATE SEMANTIC VIEW 命令创建语义视图。

该示例使用了 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';

Copy

接下来的章节将对此示例进行更详细的介绍：

定义逻辑表
识别逻辑表之间的关系
定义事实、维度和指标
将事实或指标标记为专用

备注

有关完整示例，请参阅使用 SQL 创建语义视图的示例。

定义逻辑表¶

在 CREATE SEMANTIC VIEW 命令中，使用 TABLES 子句定义视图中的逻辑表。在本子句中，您可以执行以下操作：

指定物理表名和可选别名。
识别逻辑表中的以下列：
- 用作主键的列。
- 包含唯一值的列（主键列除外）。
您可以使用这些列在此语义视图中定义关系。
为表添加同义词（以增强可发现性）。
包括描述性注释。

备注

如果两个表存在多种联接方式，则应为每种方式分别定义独立的逻辑表。有关信息，请参阅为联接两个表的不同路径定义不同的逻辑表。

在前面介绍的示例中，TABLES 子句定义了以下三个逻辑表：

一个 orders 表，包含来自 TPC-H orders 表的订单信息。
一个 customers 表，包含来自 TPC-H customers 表的客户信息。
一个 line_item 表，包含来自 TPC-H lineitem 表的订单明细项。

该示例确定了用作每个逻辑表主键的列，以便您可以识别表之间的关系。

该示例还提供了用于描述逻辑表的同义词和注释，使数据更易于发现。

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'

Copy

为联接两个表的不同路径定义不同的逻辑表¶

如果存在多条路径可用于联接两个物理表，则应为每条路径分别定义独立的逻辑表及关系。

例如，在 Snowflake 提供的 TPC-H 示例数据中，存在两种可能的方式来联接 region 和 lineitem 表：

region -> nation -> supplier -> partsupp -> lineitem
region -> nation -> customer -> orders -> lineitem

第一条路径表示供应商所属的区域，第二条路径表示客户所属的区域。

虽然可以为 region 和 nation 分别使用单一逻辑表，但应为供应商区域、客户区域、供应商国家/地区、客户国家/地区分别定义独立的逻辑表：

TABLES (
  supplier_region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey).
  customer_region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey),
  supplier_nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
  customer_nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
  ...
)

Copy

然后，定义代表不同路径的独立关系：

RELATIONSHIPS (
  supplier_nation (n_regionkey) REFERENCES supplier_region,
  customer_nation (n_regionkey) REFERENCES customer_region,
  ...
)

Copy

识别逻辑表之间的关系¶

在 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)
)

Copy

定义事实、维度和指标¶

在 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'
)

Copy

备注

有关定义使用窗口函数的指标的其他指南，请参阅定义和查询窗口函数指标。

将事实或指标标记为专用¶

如果您定义的数值或指标仅用于语义视图中的计算，并且不希望在查询中返回该事实或指标，则可以指定 PRIVATE 关键字将该事实或指标标记为专用。例如：

FACTS (
  PRIVATE my_private_fact AS ...
)

METRICS (
  PRIVATE my_private_metric AS ...
)

Copy

备注

您无法将维度标记为专用。维度始终是公开的。

查询包含专用事实或指标的语义视图时，不可在以下子句中指定专用事实或指标：

SELECT 列表
FACTS in the SEMANTIC_VIEW clause
METRICS in the SEMANTIC_VIEW clause
METRICS
WHERE in the SELECT statement or the SEMANTIC_VIEW clause

部分命令和函数包含专用事实与指标：

专用事实和指标会出现在 DESCRIBE SEMANTIC VIEW 命令的输出中。专用事实和指标对应的行会在 access_modifier 列中显示 PRIVATE。
如获取语义视图的 SQL 语句所述，专用事实和指标会列在 GET_DDL 函数调用的返回值中。

部分命令和函数仅在特定条件下包含专用事实与指标：

仅当使用已被授予语义视图 REFERENCES 或 OWNERSHIP 权限的角色时，专用事实和指标才会列在 INFORMATION_SCHEMA SEMANTIC_FACTS 和 SEMANTIC_METRICS 中。

否则，这些视图仅列出公共事实和指标。

其他命令和函数不包含专用事实与指标：

Private facts do not appear in the output of the SHOW SEMANTIC FACTS command.
Private metrics do not appear in the output of the SHOW SEMANTIC METRICS command.

根据 YAML 规范创建语义视图¶

要根据语义模型的 YAML 规范创建语义视图，可以调用 SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML 存储过程。

首先，将 TRUE 作为第三个实参传递，以验证您是否可以根据 YAML 规范创建语义视图。

以下示例验证了您是否可以在 YAML 中使用给定的语义模型规范，在数据库 my_db 和架构 tpch_analysis 中创建名为 my_schema 的语义视图：

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'my_db.my_schema',
  $$
  name: TPCH_REV_ANALYSIS
  description: Semantic view for revenue analysis
  tables:
    - name: CUSTOMERS
      description: Main table for customer data
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: CUSTOMER
      primary_key:
        columns:
          - C_CUSTKEY
      dimensions:
        - name: CUSTOMER_NAME
          synonyms:
            - customer name
          description: Name of the customer
          expr: customers.c_name
          data_type: VARCHAR(25)
        - name: C_CUSTKEY
          expr: C_CUSTKEY
          data_type: VARCHAR(134217728)
      metrics:
        - name: CUSTOMER_COUNT
          description: Count of number of customers
          expr: COUNT(c_custkey)
    - name: LINE_ITEMS
      description: Line items in orders
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: LINEITEM
      primary_key:
        columns:
          - L_ORDERKEY
          - L_LINENUMBER
      dimensions:
        - name: L_ORDERKEY
          expr: L_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: L_LINENUMBER
          expr: L_LINENUMBER
          data_type: VARCHAR(134217728)
      facts:
        - name: DISCOUNTED_PRICE
          description: Extended price after discount
          expr: l_extendedprice * (1 - l_discount)
          data_type: "NUMBER(25,4)"
        - name: LINE_ITEM_ID
          expr: "CONCAT(l_orderkey, '-', l_linenumber)"
          data_type: VARCHAR(134217728)
    - name: ORDERS
      synonyms:
        - sales orders
      description: All orders table for the sales domain
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: ORDERS
      primary_key:
        columns:
          - O_ORDERKEY
      dimensions:
        - name: ORDER_DATE
          description: Date when the order was placed
          expr: o_orderdate
          data_type: DATE
        - name: ORDER_YEAR
          description: Year when the order was placed
          expr: YEAR(o_orderdate)
          data_type: "NUMBER(4,0)"
        - name: O_ORDERKEY
          expr: O_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: O_CUSTKEY
          expr: O_CUSTKEY
          data_type: VARCHAR(134217728)
      facts:
        - name: COUNT_LINE_ITEMS
          expr: COUNT(line_items.line_item_id)
          data_type: "NUMBER(18,0)"
      metrics:
        - name: AVERAGE_LINE_ITEMS_PER_ORDER
          description: Average number of line items per order
          expr: AVG(orders.count_line_items)
        - name: ORDER_AVERAGE_VALUE
          description: Average order value across all orders
          expr: AVG(orders.o_totalprice)
  relationships:
    - name: LINE_ITEM_TO_ORDERS
      left_table: LINE_ITEMS
      right_table: ORDERS
      relationship_columns:
        - left_column: L_ORDERKEY
          right_column: O_ORDERKEY
      relationship_type: many_to_one
    - name: ORDERS_TO_CUSTOMERS
      left_table: ORDERS
      right_table: CUSTOMERS
      relationship_columns:
        - left_column: O_CUSTKEY
          right_column: C_CUSTKEY
      relationship_type: many_to_one
  $$,
TRUE);

Copy

如果规范有效，则存储过程返回以下消息：

+----------------------------------------------------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML                                            |
|----------------------------------------------------------------------------------|
| YAML file is valid for creating a semantic view. No object has been created yet. |
+----------------------------------------------------------------------------------+

如果 YAML 语法无效，则存储过程会引发异常。例如，如果缺少冒号：

relationships
  - name: LINE_ITEM_TO_ORDERS

Copy

存储过程会抛出异常，表示 YAML 语法无效：

392400 (22023): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  Invalid semantic model YAML: while scanning a simple key
   in 'reader', line 90, column 3:
        relationships
        ^
  could not find expected ':'
   in 'reader', line 91, column 11:
          - name: LINE_ITEM_TO_ORDERS
                ^

如果规范引用的物理表不存在，则存储过程会引发异常：

base_table:
  database: SNOWFLAKE_SAMPLE_DATA
  schema: TPCH_SF1
  table: NONEXISTENT

Copy

002003 (42S02): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  SQL compilation error:
  Table 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NONEXISTENT' does not exist or not authorized.

同样，如果规范引用的主键列不存在，则存储过程会引发异常：

primary_key:
  columns:
    - NONEXISTENT

Copy

000904 (42000): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  SQL compilation error: error line 0 at position -1
  invalid identifier 'NONEXISTENT'

然后，无需传入第三个实参即可调用存储过程来创建语义视图。

以下示例在数据库 my_db 和架构 tpch_analysis 中创建了一个名为 my_schema 的语义视图：

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'my_db.my_schema',
  $$
  name: TPCH_REV_ANALYSIS
  description: Semantic view for revenue analysis
  tables:
    - name: CUSTOMERS
      description: Main table for customer data
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: CUSTOMER
      primary_key:
        columns:
          - C_CUSTKEY
      dimensions:
        - name: CUSTOMER_NAME
          synonyms:
            - customer name
          description: Name of the customer
          expr: customers.c_name
          data_type: VARCHAR(25)
        - name: C_CUSTKEY
          expr: C_CUSTKEY
          data_type: VARCHAR(134217728)
      metrics:
        - name: CUSTOMER_COUNT
          description: Count of number of customers
          expr: COUNT(c_custkey)
    - name: LINE_ITEMS
      description: Line items in orders
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: LINEITEM
      primary_key:
        columns:
          - L_ORDERKEY
          - L_LINENUMBER
      dimensions:
        - name: L_ORDERKEY
          expr: L_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: L_LINENUMBER
          expr: L_LINENUMBER
          data_type: VARCHAR(134217728)
      facts:
        - name: DISCOUNTED_PRICE
          description: Extended price after discount
          expr: l_extendedprice * (1 - l_discount)
          data_type: "NUMBER(25,4)"
        - name: LINE_ITEM_ID
          expr: "CONCAT(l_orderkey, '-', l_linenumber)"
          data_type: VARCHAR(134217728)
    - name: ORDERS
      synonyms:
        - sales orders
      description: All orders table for the sales domain
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: ORDERS
      primary_key:
        columns:
          - O_ORDERKEY
      dimensions:
        - name: ORDER_DATE
          description: Date when the order was placed
          expr: o_orderdate
          data_type: DATE
        - name: ORDER_YEAR
          description: Year when the order was placed
          expr: YEAR(o_orderdate)
          data_type: "NUMBER(4,0)"
        - name: O_ORDERKEY
          expr: O_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: O_CUSTKEY
          expr: O_CUSTKEY
          data_type: VARCHAR(134217728)
      facts:
        - name: COUNT_LINE_ITEMS
          expr: COUNT(line_items.line_item_id)
          data_type: "NUMBER(18,0)"
      metrics:
        - name: AVERAGE_LINE_ITEMS_PER_ORDER
          description: Average number of line items per order
          expr: AVG(orders.count_line_items)
        - name: ORDER_AVERAGE_VALUE
          description: Average order value across all orders
          expr: AVG(orders.o_totalprice)
  relationships:
    - name: LINE_ITEM_TO_ORDERS
      left_table: LINE_ITEMS
      right_table: ORDERS
      relationship_columns:
        - left_column: L_ORDERKEY
          right_column: O_ORDERKEY
      relationship_type: many_to_one
    - name: ORDERS_TO_CUSTOMERS
      left_table: ORDERS
      right_table: CUSTOMERS
      relationship_columns:
        - left_column: O_CUSTKEY
          right_column: C_CUSTKEY
      relationship_type: many_to_one
  $$
);

Copy

+-----------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML   |
|-----------------------------------------|
| Semantic view was successfully created. |
+-----------------------------------------+

Modifying the comment for an existing semantic view¶

To modify the comment for an existing semantic view, run the ALTER SEMANTIC VIEW command. For example:

ALTER SEMANTIC VIEW my_semantic_view SET COMMENT = 'my comment';

Copy

备注

您不能使用 ALTER SEMANTIC VIEW 命令更改注释以外的属性。要更改该语义视图的其他属性，请替换该语义视图。请参阅替换现有的语义视图。

You can also use the COMMENT command to set a comment for a semantic view:

COMMENT ON SEMANTIC VIEW my_semantic_view IS 'my comment';

Copy

替换现有的语义视图¶

要替换现有的语义视图（例如更改视图的定义），请在执行 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;

Copy

列出语义视图¶

To list semantic views in the current schema or a specified schema, run the SHOW SEMANTIC VIEWS command. For example:

SHOW SEMANTIC VIEWS;

Copy

+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
| 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 架构中的视图来获取语义视图的信息。

Listing dimensions, facts, and metrics¶

To list the dimensions, facts, and metrics that are available in a view, schema, database, or account, you can run the following commands:

By default, the commands list the dimensions, facts, and metrics that are available in semantic views defined in the current schema:

SHOW SEMANTIC DIMENSIONS;

Copy

+---------------+-------------+--------------------+------------+---------------+--------------+-------------------+--------------------------------+
| database_name | schema_name | semantic_view_name | table_name | name          | data_type    | synonyms          | comment                        |
|---------------+-------------+--------------------+------------+---------------+--------------+-------------------+--------------------------------|
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | CUSTOMERS  | CUSTOMER_NAME | VARCHAR(25)  | ["customer name"] | Name of the customer           |
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | CUSTOMERS  | C_CUSTKEY     | NUMBER(38,0) | NULL              | NULL                           |
...

SHOW SEMANTIC FACTS;

Copy

+---------------+-------------+--------------------+------------+------------------+--------------------+----------+-------------------------------+
| database_name | schema_name | semantic_view_name | table_name | name             | data_type          | synonyms | comment                       |
|---------------+-------------+--------------------+------------+------------------+--------------------+----------+-------------------------------|
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | LINE_ITEMS | DISCOUNTED_PRICE | NUMBER(25,4)       | NULL     | Extended price after discount |
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | LINE_ITEMS | LINE_ITEM_ID     | VARCHAR(134217728) | NULL     | NULL                          |
...

SHOW SEMANTIC METRICS;

Copy

+---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------+
| database_name | schema_name | semantic_view_name | table_name | name                         | data_type    | synonyms | comment                                |
|---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------|
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | CUSTOMERS  | CUSTOMER_COUNT               | NUMBER(18,0) | NULL     | Count of number of customers           |
| MY_DB         | MY_SCHEMA   | TPCH_REV_ANALYSIS  | ORDERS     | AVERAGE_LINE_ITEMS_PER_ORDER | NUMBER(36,6) | NULL     | Average number of line items per order |
...

You can change the scope to list the dimensions, facts, and metrics defined in the following:

当前数据库中的语义视图：

SHOW SEMANTIC DIMENSIONS IN DATABASE;

SHOW SEMANTIC FACTS IN DATABASE;

SHOW SEMANTIC METRICS IN DATABASE;

Copy

Semantic views in specific schemas or databases:

SHOW SEMANTIC DIMENSIONS IN SCHEMA my_db.my_other_schema;

SHOW SEMANTIC DIMENSIONS IN DATABASE my_db;

SHOW SEMANTIC FACTS IN SCHEMA my_db.my_other_schema;

SHOW SEMANTIC FACTS IN DATABASE my_db;

SHOW SEMANTIC METRICS IN SCHEMA my_db.my_other_schema;

SHOW SEMANTIC METRICS IN DATABASE my_db;

Copy

Semantic views in the account:

SHOW SEMANTIC DIMENSIONS IN ACCOUNT;

SHOW SEMANTIC FACTS IN ACCOUNT;

SHOW SEMANTIC METRICS IN ACCOUNT;

Copy

特定的语义视图：

SHOW SEMANTIC DIMENSIONS IN my_semantic_view;

SHOW SEMANTIC FACTS IN my_semantic_view;

SHOW SEMANTIC METRICS IN my_semantic_view;

Copy

如果您要查询语义视图，可以使用 SHOW SEMANTIC DIMENSIONS FOR METRIC 命令来确定在指定给定指标时可以返回哪些维度。有关详细信息，请参阅为给定指标选择可以返回的维度。

查看有关语义视图的详细信息¶

To view the details of a semantic view, run the DESCRIBE SEMANTIC VIEW command. For example:

DESCRIBE SEMANTIC VIEW tpch_rev_analysis;

Copy

+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| 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 语句。

备注

要针对语义视图调用此函数，必须使用已被授予该语义视图 REFERENCES 或 OWNERSHIP 权限的角色。

调用 GET_DDL 时，将 'SEMANTIC_VIEW' 作为对象类型传入。例如：

SELECT GET_DDL('SEMANTIC_VIEW', 'tpch_rev_analysis', TRUE);

Copy

+-----------------------------------------------------------------------------------+
| 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'                                                                                                                                                                       |
|     );                                                                                                                                                                       |
+-----------------------------------------------------------------------------------+

返回值包含 :ref:`专用事实和指标 <label-semantic_views_private>`（即标记有 PRIVATE 关键字的事实和指标）。

获取语义视图的 YAML 规范¶

要获取用于语义视图的语义模型的 YAML 规范，请调用 SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW 函数。

以下示例返回数据库 my_db 和架构 my_schema 中名为 tpch_analysis 的语义视图的 YAML 规范：

SELECT SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW(
  'my_db.my_schema.tpch_rev_analysis'
);

Copy

+-------------------------------------------------------------+
| READ_YAML_FROM_SEMANTIC_VIEW                                |
|-------------------------------------------------------------|
| name: TPCH_REV_ANALYSIS                                     |
| description: Semantic view for revenue analysis             |
| tables:                                                     |
|   - name: CUSTOMERS                                         |
|     description: Main table for customer data               |
|     base_table:                                             |
|       database: SNOWFLAKE_SAMPLE_DATA                       |
|       schema: TPCH_SF1                                      |
|       table: CUSTOMER                                       |
|     primary_key:                                            |
|       columns:                                              |
|         - C_CUSTKEY                                         |
|     dimensions:                                             |
|       - name: CUSTOMER_NAME                                 |
|         synonyms:                                           |
|           - customer name                                   |
|         description: Name of the customer                   |
|         expr: customers.c_name                              |
|         data_type: VARCHAR(25)                              |
|       - name: C_CUSTKEY                                     |
|         expr: C_CUSTKEY                                     |
|         data_type: VARCHAR(134217728)                       |
|   - name: LINE_ITEMS                                        |
|     description: Line items in orders                       |
|     base_table:                                             |
|       database: SNOWFLAKE_SAMPLE_DATA                       |
|       schema: TPCH_SF1                                      |
|       table: LINEITEM                                       |
|     primary_key:                                            |
|       columns:                                              |
|         - L_ORDERKEY                                        |
|         - L_LINENUMBER                                      |
|     dimensions:                                             |
|       - name: L_ORDERKEY                                    |
|         expr: L_ORDERKEY                                    |
|         data_type: VARCHAR(134217728)                       |
|       - name: L_LINENUMBER                                  |
|         expr: L_LINENUMBER                                  |
|         data_type: VARCHAR(134217728)                       |
|     facts:                                                  |
|       - name: DISCOUNTED_PRICE                              |
|         description: Extended price after discount          |
|         expr: l_extendedprice * (1 - l_discount)            |
|         data_type: "NUMBER(25,4)"                           |
|       - name: LINE_ITEM_ID                                  |
|         expr: "CONCAT(l_orderkey, '-', l_linenumber)"       |
|         data_type: VARCHAR(134217728)                       |
|   - name: ORDERS                                            |
|     synonyms:                                               |
|       - sales orders                                        |
|     description: All orders table for the sales domain      |
|     base_table:                                             |
|       database: SNOWFLAKE_SAMPLE_DATA                       |
|       schema: TPCH_SF1                                      |
|       table: ORDERS                                         |
|     primary_key:                                            |
|       columns:                                              |
|         - O_ORDERKEY                                        |
|     dimensions:                                             |
|       - name: ORDER_DATE                                    |
|         description: Date when the order was placed         |
|         expr: o_orderdate                                   |
|         data_type: DATE                                     |
|       - name: ORDER_YEAR                                    |
|         description: Year when the order was placed         |
|         expr: YEAR(o_orderdate)                             |
|         data_type: "NUMBER(4,0)"                            |
|       - name: O_ORDERKEY                                    |
|         expr: O_ORDERKEY                                    |
|         data_type: VARCHAR(134217728)                       |
|       - name: O_CUSTKEY                                     |
|         expr: O_CUSTKEY                                     |
|         data_type: VARCHAR(134217728)                       |
|     facts:                                                  |
|       - name: COUNT_LINE_ITEMS                              |
|         expr: COUNT(line_items.line_item_id)                |
|         data_type: "NUMBER(18,0)"                           |
|     metrics:                                                |
|       - name: AVERAGE_LINE_ITEMS_PER_ORDER                  |
|         description: Average number of line items per order |
|         expr: AVG(orders.count_line_items)                  |
|       - name: ORDER_AVERAGE_VALUE                           |
|         description: Average order value across all orders  |
|         expr: AVG(orders.o_totalprice)                      |
| relationships:                                              |
|   - name: LINE_ITEM_TO_ORDERS                               |
|     left_table: LINE_ITEMS                                  |
|     right_table: ORDERS                                     |
|     relationship_columns:                                   |
|       - left_column: L_ORDERKEY                             |
|         right_column: O_ORDERKEY                            |
|   - name: ORDERS_TO_CUSTOMERS                               |
|     left_table: ORDERS                                      |
|     right_table: CUSTOMERS                                  |
|     relationship_columns:                                   |
|       - left_column: O_CUSTKEY                              |
|         right_column: C_CUSTKEY                             |
|                                                             |
+-------------------------------------------------------------+

Renaming a semantic view¶

To rename a semantic view, run ALTER SEMANTIC VIEW ... RENAME TO .... For example:

ALTER SEMANTIC VIEW sv RENAME TO sv_new_name;

Copy

移除语义视图¶

To remove a semantic view, run the DROP SEMANTIC VIEW command. For example:

DROP SEMANTIC VIEW tpch_rev_analysis;

Copy

授予语义视图的权限¶

语义视图权限列出了您可以授予的语义视图权限。

需要语义视图的以下权限才能使用该视图：

Any privilege (for example, REFERENCES OR SELECT) is required to run the DESCRIBE SEMANTIC VIEW command on the view.
SELECT is required to run SELECT ... FROM SEMANTIC_VIEW().
以上任一权限也用于让该视图出现在 SHOW SEMANTIC VIEWS 命令的输出结果中。

备注

要查询语义视图，您不需要对语义视图中使用的表具有 SELECT 权限。您只需要对语义视图本身具有 SELECT 权限。

此行为与查询标准视图所需的权限一致。

要在 Cortex Analyst 中使用并非自己拥有的语义视图，您必须使用一个拥有该视图的 REFERENCES 和 SELECT 权限的角色。

To grant the REFERENCES and SELECT privileges on a semantic view, use the GRANT <privileges> ... TO ROLE command. For example, to grant the REFERENCES and SELECT privileges on the semantic view named my_semantic_view to the role my_analyst_role, you can run the following statement:

GRANT REFERENCES, SELECT ON SEMANTIC VIEW my_semantic_view TO ROLE my_analyst_role;

Copy

如果您有一个包含语义视图的架构，并希望将其共享给 Cortex Analyst 用户，则您可以使用未来授权授予该架构中今后创建的所有语义视图的权限。例如：

GRANT REFERENCES, SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA my_schema TO ROLE my_analyst_role;

Copy