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

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

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

创建语义视图所需的权限

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

  • 对创建语义视图所在的架构具有 CREATE SEMANTIC VIEW 权限。

  • 对创建语义视图所在的数据库和架构具有 USAGE 权限。

  • SELECT on the tables and views used in the semantic view.

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

创建语义视图

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

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

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

使用 CREATE SEMANTIC VIEW 命令

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

该示例使用了 Snowflake 中提供的 TPC-H 示例数据。该数据集包含多个表，代表了一个简化的商业场景，其中包括客户、订单和订单明细项等内容。

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 子句定义视图中的逻辑表。在本子句中，您可以执行以下操作：

  • 指定物理表名和可选别名。

  • 识别逻辑表中的以下列：

    • 用作主键的列。

    • 包含唯一值的列（主键列除外）。

    您可以使用这些列在此语义视图中定义关系。

  • 为表添加同义词（以增强可发现性）。

  • 包括描述性注释。

备注

If there are multiple ways in which two tables can be joined, you should define a separate logical table for each of these ways. For information, see Defining different logical tables for different paths that join two 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

Defining different logical tables for different paths that join two tables

If there are multiple paths that you can use to join two physical tables, you should define separate logical tables and relationships for each path.

For example, in the TPC-H sample data available in Snowflake, there are two possible ways to join the region and lineitem tables:

  • region -> nation -> supplier -> partsupp -> lineitem

  • region -> nation -> customer -> orders -> lineitem

The first path represents the region of the supplier, and the second path represents the region of the customer.

Although you can use a single logical table for region and a single logical table for nation, you should define separate logical tables for the region of the supplier, the region of the customer, the nation of the supplier, and the nation of the customer:

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

Then, define separate relationships that represent the different paths:

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 子句定义语义视图中的事实、维度和指标。

您必须在语义视图中定义至少一个维度或指标。

对于每个事实、维度或指标，您可以指定以下内容：

  • 它所属的逻辑表。

    备注

    If you want to define a derived metric (a metric that is not specific to one logical table), you must omit the logical table name. See Defining derived 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

备注

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

Defining derived metrics

When you define a metric, you specify the name of the logical table that the metric belongs to. This is the logical table on which the metric is aggregated.

If you want to define a metric based on metrics from different logical tables, you can define a derived metric. A derived metric is a metric that is scoped to the semantic view (rather than to a specific logical table). A derived metric can combine metrics from multiple logical tables.

In the definition of a derived metric, omit the logical table name.

For example, suppose that you want to define a metric my_derived_metric_1 that is the sum of the metrics table_1.metric_1 and table_2.metric_2. When you define my_derived_metric_1, don't qualify the name with any logical table name:

CREATE SEMANTIC VIEW sv_with_derived_metrics
  TABLES (
    table_1 PRIMARY KEY (column_1),
    table_2 PRIMARY KEY (column_2)
  )
  ...
  METRICS (
    table_1.metric_1 AS SUM(...),
    table_2.metric_2 AS SUM(...),
    my_derived_metric_1 AS table_1.metric_1 + table_2.metric_2
  )
 ...
Copy

You can use other derived metrics in the expression. For example:

METRICS (
  ...
  my_derived_metric_1 AS table_1.metric_1 + table_2.metric_2,
  my_view_metric_2 AS my_derived_metric_1 + table_3.metric_3
)
Copy

Note the following restrictions when you define a derived metric:

  • You cannot use the same name for a derived metric and a regular metric.

  • The expression for a derived metric must be a scalar expression that uses one or more metrics. You don't need to qualify the name of a metric in the expression if the name is not ambiguous. For example:

    METRICS (
  table_1.metric_1 AS ...,
  table_1.my_unique_metric_name AS ...,
  table_2.metric_1 AS ...,
  my_derived_metric_1 AS table_1.metric_1 + my_unique_metric_name
)
    Copy

    Note that metric_1 needs to be qualified by table_1 because there are two metrics named metric_1, but my_unique_metric_name does not need to be qualified because the name is unique.

  • In the expression for a derived metric, you cannot use the following:

    • Aggregate functions.

    • Window functions.

    • References to physical columns, facts, and dimensions.

  • You cannot use a derived metric in the expression for a regular metric, dimension, or fact. Only another derived metric can use a derived metric in its expression.

Marking a fact or metric as private

If you are defining a fact or metric only for use in calculations in the semantic view and you don't want the fact or metric to be returned in a query, you can specify the PRIVATE keyword to mark the fact or metric as private. For example:

FACTS (
  PRIVATE my_private_fact AS ...
)

METRICS (
  PRIVATE my_private_metric AS ...
)
Copy

备注

You cannot mark a dimension as private. Dimensions are always public.

When you query a semantic view that has private facts or metrics, you cannot specify a private fact or metric in the following clauses:

Some commands and functions include private facts and metrics:

Some commands and functions include private facts and metrics only under specific conditions:

Other commands and functions do not include private facts and metrics:

根据 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

备注

You can't use the ALTER SEMANTIC VIEW command to change properties other than the comment. To change other properties of the semantic view, replace the semantic view. See 替换现有的语义视图.

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

备注

To call this function for a semantic view, you must use a role that has been granted the REFERENCES or OWNERSHIP privilege on the semantic view.

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

The return value includes private facts and metrics (facts and metrics that are marked with the PRIVATE keyword).

获取语义视图的 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
语言: 中文