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属性的值。
该视图包括以下各列：
列 |
描述 |
|---|---|
|
具有该行属性的对象的类型。值可以是以下其中一项： |
|
具有此行属性的维度、事实、指标、逻辑表或关系的名称。 对于代表语义视图本身属性的行，此列中的值为 NULL。 |
|
维度、事实、指标或关系的父实体的名称。 对于代表逻辑表或语义视图本身的属性的行，此列中的值为 NULL。 |
|
逻辑表、关系、维度、事实、指标或语义视图的属性的名称。 此列中的值取决于对象的类型 ( 请参阅以下部分，根据
|
|
逻辑表、关系、维度、事实、指标或语义视图的属性的值。 |
逻辑表的属性¶
如果 object_kind 列包含 TABLE，则 property 列可能包含以下值：
属性名称 |
描述 |
|---|---|
|
包含逻辑表的数据库的名称。 |
|
包含逻辑表的架构的名称。 |
|
逻辑表的名称。 |
|
VARCHAR 值的 数组，代表逻辑表的同义词。 |
|
VARCHAR 值的 数组，指定构成逻辑表主键的列的名称。 |
关系的属性¶
如果 object_kind 列包含 RELATIONSHIP，则 property 列可能包含以下值：
属性名称 |
描述 |
|---|---|
|
关系中其中一个逻辑表的名称。 |
|
关系中使用的该逻辑表中列的名称。 |
|
关系中另一个逻辑表的名称。 |
|
关系中其他逻辑表中列的名称。 |
事实、维度和指标的属性¶
如果 object_kind 列包含 FACT、DIMENSION 或 METRIC，则 property 列可能包含以下值：
属性名称 |
描述 |
|---|---|
|
用于定义维度、事实或指标的逻辑表的名称。 |
|
维度、事实或指标的 SQL 表达式。 |
|
求值 SQL 表达式的 SQL 数据类型。 |
|
|
语义视图的属性¶
如果 object_kind 列是 NULL，则 property 列可能包含以下值：
属性名称 |
描述 |
|---|---|
|
关于语义视图的注释。 |
访问控制要求¶
用于执行此 SQL 命令的 角色 必须至少具有以下 一项 权限：
权限 |
对象 |
备注 |
|---|---|---|
任意 |
语义视图 |
The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema. Note that a role granted any privilege on a schema allows that role to resolve the schema. For example, a role granted CREATE privilege on a schema can create objects on that schema without also having USAGE granted on that schema.
有关创建具有指定权限集的自定义角色的说明，请参阅 创建自定义角色。
使用说明¶
To post-process the output of this command, you can use the pipe operator (
->>) or the RESULT_SCAN function. Both constructs treat the output as a result set that you can query.The output column names for this command are generated in lowercase. If you consume a result set from this command with the pipe operator or the RESULT_SCAN function, use double-quoted identifiers for the column names in the query to ensure that they match the column names in the output that was scanned. For example, if the name of an output column is
type, then specify"type"for the identifier.
示例¶
以下示例描述了名为 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 |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+