DESCRIBE SEMANTIC VIEW¶
Describes the properties of the logical tables, dimensions, facts, and metrics that make up a semantic view.
- See also:
CREATE SEMANTIC VIEW , DROP SEMANTIC VIEW , SHOW SEMANTIC VIEWS
Syntax¶
{ DESCRIBE | DESC } SEMANTIC VIEW <name>
Parameters¶
nameSpecifies the identifier for the semantic view to describe.
If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.
For more information, see Identifier requirements.
Output¶
The command output provides the properties and metadata about the logical tables, relationships, facts, dimensions, metrics, and the semantic view itself.
Each row in the view represents a property of:
A logical table
A relationship
A fact
A dimension
A metric
The semantic view itself
The following is an example of the output of the command:
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| 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 |
| ... | ... | ... | ... | ... |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
As shown above, each row represents a property of a logical table, dimension, relationship, metric, or fact. For example:
The first row is the value of the
commentproperty of the semantic view itself.The second row is the value of the
base_table_database_nameproperty of the logical table namedcustomers.
The view includes the following columns:
Column |
Description |
|---|---|
|
Type of the object that has the property for this row. The value can be one of the following:
|
|
Name of the dimension, fact, metric, logical table, or relationship that has the property for this row. For rows that represent properties of the semantic view itself, the value in this column is NULL. |
|
Name of the parent entity of the dimension, fact, metric, or relationship. For rows that represent properties of logical tables or the semantic view itself, the value in this column is NULL. |
|
Name of the property of the logical table, relationship, dimension, fact, metric, or semantic view. The value in this column depends on the type of the object ( See the following sections for details about the properties and their possible values, based on the value in the
|
|
Value of the property of the logical table, relationship, dimension, fact, metric, or semantic view. |
Properties for logical tables¶
If the object_kind column contains TABLE, the property column can contain the following values:
Property name |
Description |
|---|---|
|
Name of the database containing the logical table. |
|
Name of the schema containing the logical table. |
|
Name of the logical table. |
|
Array of VARCHAR values, representing the synonyms for the logical table. |
|
Array of VARCHAR values, specifying the names of the columns that make up the primary key for the logical table. |
Properties for relationships¶
If the object_kind column contains RELATIONSHIP, the property column can contain the following values:
Property name |
Description |
|---|---|
|
Name of one of the logical tables in the relationship. |
|
Name of the column in that logical table used in the relationship. |
|
Name of the other logical table in the relationship. |
|
Name of the column in the other logical table in the relationship. |
Properties for facts, dimensions, and metrics¶
If the object_kind column contains FACT, DIMENSION, or METRIC, the property column can contain the
following values:
Property name |
Description |
|---|---|
|
Name of the logical table used to define the dimension, fact, or metric. |
|
The SQL expression for the dimension, fact, or metric. |
|
The SQL data type of the evaluated SQL expression. |
Properties for semantic views¶
If the object_kind column is NULL, the property column can contain the following values:
Property name |
Description |
|---|---|
|
Comment about the semantic view. |
Access control requirements¶
A role used to execute this SQL command must have at least one of the following privileges at a minimum:
Privilege |
Object |
Notes |
|---|---|---|
REFERENCES |
Semantic view |
|
OWNERSHIP |
Semantic view |
OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege). |
The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.
For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.
For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.
Usage notes¶
To post-process the output of this command, you can use the RESULT_SCAN function, which treats the output as a table that can be queried.
Examples¶
The following example describes the semantic view named 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 |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+