DESCRIBE SEMANTIC VIEW

描述构成 语义视图 的逻辑表、维度、事实和指标的属性。

另请参阅:

CREATE SEMANTIC VIEW , ALTER SEMANTIC VIEW , DROP SEMANTIC VIEW , SHOW SEMANTIC VIEWS , SHOW SEMANTIC DIMENSIONS , SHOW SEMANTIC DIMENSIONS FOR METRIC , SHOW SEMANTIC FACTS , SHOW SEMANTIC METRICS

语法

{ DESCRIBE | DESC } SEMANTIC VIEW <name>

参数

name

指定要描述的语义视图的标识符。

如果标识符包含空格或特殊字符，则整个字符串必须放在双引号内。放在双引号内的标识符也区分大小写。

有关更多信息，请参阅 标识符要求。

输出

命令输出提供有关逻辑表、关系、事实、维度、指标和语义视图本身的属性和元数据。

视图中的每行代表以下内容的属性：

  • 逻辑表

  • 关系

  • 事实

  • 维度

  • 指标

  • 语义视图本身

以下是该命令的输出示例：

+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| object_kind  | object_name                  | parent_entity | property                 | property_value                         |
|--------------+------------------------------+---------------+--------------------------+----------------------------------------|
| NULL         | NULL                         | NULL          | COMMENT                  | Comment about the semantic view        |
| TABLE        | CUSTOMERS                    | NULL          | BASE_TABLE_DATABASE_NAME | SNOWFLAKE_SAMPLE_DATA                  |
| ...          | ...                          | ...           | ...                      | ...                                    |
| DIMENSION    | CUSTOMER_NAME                | CUSTOMERS     | TABLE                    | CUSTOMERS                              |
| ...          | ...                          | ...           | ...                      | ...                                    |
| RELATIONSHIP | LINE_ITEM_TO_ORDERS          | LINE_ITEMS    | TABLE                    | LINE_ITEMS                             |
| ...          | ...                          | ...           | ...                      | ...                                    |
| FACT         | DISCOUNTED_PRICE             | LINE_ITEMS    | TABLE                    | LINE_ITEMS                             |
| ...          | ...                          | ...           | ...                      | ...                                    |
| METRIC       | AVERAGE_LINE_ITEMS_PER_ORDER | ORDERS        | TABLE                    | ORDERS                                 |
| ...          | ...                          | ...           | ...                      | ...                                    |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+

如上所示，每行代表逻辑表、维度、关系、指标或事实的属性。例如：

  • 第一行是语义视图本身 comment 属性的值。

  • 第二行是名为 customers 的逻辑表 base_table_database_name 属性的值。

该视图包括以下各列：

列

描述

object_kind

具有该行属性的对象的类型。值可以是以下其中一项：

  • ``TABLE``（视图的逻辑表）

  • RELATIONSHIP

  • DIMENSION

  • FACT

  • METRIC

  • DERIVED_METRIC (for derived metrics)

  • CUSTOM_INSTRUCTIONS (for custom instructions)

  • ``NULL``（适用于语义视图本身的属性，例如注释）

object_name

具有此行属性的维度、事实、指标、逻辑表或关系的名称。

For rows that represent properties of the semantic view itself and rows that represent custom instructions, the value in this column is NULL.

parent_entity

维度、事实、指标或关系的父实体的名称。

The value of this column is NULL for rows that represent:

property

Name of the property of the logical table, constraint, relationship, dimension, fact, metric, custom instruction, or semantic view.

此列中的值取决于对象的类型 (object_kind)。

请参阅以下部分，根据 object_kind 列中的值，了解有关属性及其可能值的详细信息：

property_value

Value of the property of the logical table, relationship, dimension, fact, metric, custom instruction, or semantic view.

逻辑表的属性

如果 object_kind 列包含 TABLE，则 property 列可能包含以下值：

属性名称

描述

BASE_TABLE_DATABASE_NAME

包含逻辑表的数据库的名称。

BASE_TABLE_SCHEMA_NAME

包含逻辑表的架构的名称。

BASE_TABLE_NAME

逻辑表的名称。

SYNONYMS

VARCHAR 值的 数组，代表逻辑表的同义词。

PRIMARY_KEY

VARCHAR 值的 数组，指定构成逻辑表主键的列的名称。

Properties for constraints

If the object_kind column contains CONSTRAINT, the row represents a constraint that is used for a range join. The property column can contain the following values:

属性名称

描述

CONSTRAINT_TYPE

The value is DISTINCT_RANGE.

START_COLUMN

Specifies the name of the column that represents the start of the range.

END_COLUMN

Specifies the name of the column that represents the end of the range.

关系的属性

如果 object_kind 列包含 RELATIONSHIP，则 property 列可能包含以下值：

属性名称

描述

TABLE

关系中其中一个逻辑表的名称。

FOREIGN_KEY

关系中使用的该逻辑表中列的名称。

REF_TABLE

关系中另一个逻辑表的名称。

REF_KEY

One of the following values:

  • For relationships that represent range joins, an array that contains JSON-formatted strings for objects with the following keys:

    • The start_column key specifies the name of the column that represents the start of the range.

    • The end_column key specifies the name of the column that represents the end of the range.

    • The type key is RANGE.

  • For relationships that represent ASOF joins, an array that contains the following elements:

    • The name of the column in the first table.

    • A JSON object with the following fields:

      • column: Name of the column in the second table.

      • type: ASOF.

  • For other types of relationships, the name of the column in the other logical table in the relationship.

事实、维度和指标的属性

如果 object_kind 列包含 FACT、DIMENSION 或 METRIC，则 property 列可能包含以下值：

属性名称

描述

TABLE

用于定义维度、事实或指标的逻辑表的名称。

EXPRESSION

维度、事实或指标的 SQL 表达式。

DATA_TYPE

求值 SQL 表达式的 SQL 数据类型。

ACCESS_MODIFIER

PRIVATE for private facts and metrics. PUBLIC for everything else.

备注

For derived metrics, the TABLE property is not present.

In addition, if the row represents a dimension that uses a Cortex Search Service, the property column can contain the following values:

属性名称

描述

CORTEX_SEARCH_SERVICE_COLUMN_NAME

The name of the column that the Cortex Search Service allows you to search on.

CORTEX_SEARCH_SERVICE_DATABASE_NAME

The name of the database that contains the Cortex Search Service.

CORTEX_SEARCH_SERVICE_SCHEMA_NAME

The name of the schema that contains the Cortex Search Service.

CORTEX_SEARCH_SERVICE_NAME

The name of the Cortex Search Service.

Properties for custom instructions

If the object_kind column contains CUSTOM_INSTRUCTIONS, the property column can contain the following values:

属性名称

描述

AI_QUESTION_CATEGORIZATION

Custom instructions for Cortex Analyst that explain how to classify questions.

AI_SQL_GENERATION

Custom instructions for Cortex Analyst that explain how to generate the SQL statement.

语义视图的属性

如果 object_kind 列是 NULL，则 property 列可能包含以下值：

属性名称

描述

COMMENT

关于语义视图的注释。

访问控制要求

A role used to execute this operation must have the following privileges at a minimum:

权限

对象

备注

任意

语义视图

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

有关创建具有指定权限集的自定义角色的说明，请参阅 创建自定义角色。

有关对 安全对象 执行 SQL 操作的相应角色和权限授予的一般信息，请参阅 访问控制概述。

使用说明

  • 要对该命令的输出进行后处理，可以使用 管道运算符 或 RESULT_SCAN 函数。这两种构造都将输出视为可以查询的结果集。

    For example, you can use the pipe operator or RESULT_SCAN function to select specific columns from the SHOW command output or filter the rows.

    When you refer to the output columns, use double-quoted identifiers for the column names. For example, to select the output column type, specify SELECT "type".

    You must use double-quoted identifiers because the output column names for SHOW commands are in lowercase. The double quotes ensure that the column names in the SELECT list or WHERE clause match the column names in the SHOW command output that was scanned.

示例

以下示例描述了名为 tpch_rev_analysis 的语义视图：

DESC SEMANTIC VIEW tpch_rev_analysis;

+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| object_kind  | object_name                  | parent_entity | property                 | property_value                         |
|--------------+------------------------------+---------------+--------------------------+----------------------------------------|
| NULL         | NULL                         | NULL          | COMMENT                  | Comment about the semantic view        |
| 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         |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+