Using SQL commands to create and manage semantic views¶
This topic explains how to use the following SQL commands to create and manage semantic views:
This topic also explains how to call the following stored procedure and function to create a semantic view from a semantic model specification and get the specification for a semantic view:
Privileges required to create a semantic view¶
To create a semantic view, you must use a role with the following privileges:
CREATE SEMANTIC VIEW on the schema where you are creating the semantic view.
USAGE on the database and schema where you are creating the semantic view.
SELECT on the tables and views used in the semantc view.
For information about the privileges required to query a semantic view, see Privileges required to query a semantic view.
Creating a semantic view¶
To create a semantic view, you can either:
Execute the CREATE SEMANTIC VIEW command.
Call the SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML stored procedure, if you want to create a semantic view from the YAML specification for a semantic model.
The semantic view must be valid. See How Snowflake validates semantic views.
The next sections explain how to create a semantic view:
Using the CREATE SEMANTIC VIEW command¶
The following example uses the CREATE SEMANTIC VIEW command to create a semantic view.
The example uses the TPC-H sample data available in Snowflake. This data set contains tables that represent a simplified business scenario with customers, orders, and line items.
The example creates a semantic view named tpch_rev_analysis, using the tables in the TPC-H data set. The semantic view
defines:
Three logical tables (
orders,customers, andline_items).A relationship between the
ordersandcustomerstables.A relationship between the
line_itemsandorderstables.Facts that will be used to calculate metrics.
Dimensions for the customer name, the order date, and the year in which the order was placed.
Metrics for the average value of an order and the average number of line items in an order.
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';
The next sections explain this example in more detail:
Note
For a full example, see Example of using SQL to create a semantic view.
Defining the logical tables¶
In the CREATE SEMANTIC VIEW command, use the TABLES clause to define the logical tables in the view. In this clause, you can:
Specify the physical table name and an optional alias.
Identify the following columns in the logical table:
Columns that serve as primary keys.
Columns that contain unique values (other than the primary key columns).
You can use these columns to define relationships in this semantic view.
Add synonyms for the table (for enhanced discoverability).
Include a descriptive comment.
In the example presented earlier, the TABLES clause defines three logical tables:
An
orderstable containing the order information from the TPC-Horderstable.A
customerstable containing the customer information from the TPC-Hcustomerstable.A
line_itemtable containing the line items in orders from the TPC-Hlineitemtable.
The example identifies the columns to be used as primary keys for each logical table so that you can identify the relationships between the tables.
The example also provides synonyms and comments that describe the logical tables and make the data easier to discover.
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'
Identifying the relationships between logical tables¶
In the CREATE SEMANTIC VIEW command, use the RELATIONSHIPS clause to identify the relationships between the tables in view. For each relationship, you specify:
An optional name for the relationship.
The name of the logical table containing the foreign key.
The columns in that table that define the foreign key.
The name of the logical table containing the primary key or columns with unique values.
The columns in that table that define the primary key or that contain unique values.
If you already specified PRIMARY KEY for the logical table in the TABLES clause, you don’t need to specify the primary key column in the relationship.
If there is a single UNIQUE keyword for the logical table in the TABLES clause, you don’t need to specify the corresponding columns in the relationship.
In the example presented earlier, the RELATIONSHIPS clause specifies two relationships:
A relationship between the
ordersandcustomerstables. In theorderstable,o_custkeyis the foreign key that refers to the primary key in thecustomerstable (c_custkey).A relationship between the
line_itemsandorderstables. In theline_itemstable,l_orderkeyis the foreign key that refers to the primary key in theorderstable (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)
)
Defining facts, dimensions, and metrics¶
In the CREATE SEMANTIC VIEW command, use the FACTS, DIMENSIONS, and METRICS clauses to define the facts, dimensions, and metrics in the semantic view.
You must define at least one dimension or metric in the semantic view.
For each fact, dimension, or metric, you specify:
The logical table it belongs to.
A name for the fact, dimension, or metric.
The SQL expression to calculate it.
Optional synonyms and comments.
The example presented earlier defines several facts, dimensions, and metrics:
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'
)
Note
For additional guidelines on defining metrics that use window functions, see Defining and querying window function metrics.
Creating a semantic view from a YAML specification¶
To create a semantic view from a YAML specification for a semantic model, you can call the SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML stored procedure.
First, pass TRUE as the third argument to verify that you can create the semantic view from the YAML specification.
The following example verifies that you can use a given semantic model specification in YAML to create a semantic view named
tpch_analysis in the database my_db and schema 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);
If the specification is valid, the stored procedure returns the following message:
+----------------------------------------------------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML |
|----------------------------------------------------------------------------------|
| YAML file is valid for creating a semantic view. No object has been created yet. |
+----------------------------------------------------------------------------------+
If the YAML syntax is invalid, the stored procedure throw an exception. For example, if a colon is missing:
relationships
- name: LINE_ITEM_TO_ORDERS
the stored procedure throws an exception, indicating that the YAML syntax is invalid:
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
^
If the specification refers to a physical table that does not exist, the stored procedure throws an exception:
base_table:
database: SNOWFLAKE_SAMPLE_DATA
schema: TPCH_SF1
table: NONEXISTENT
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.
Similarly, if the specification refers to a primary key column that does not exist, the stored procedure throws an exception:
primary_key:
columns:
- NONEXISTENT
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'
You can then call the stored procedure without passing in the third argument to create the semantic view.
The following example creates a semantic view named tpch_analysis in the database my_db and schema 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
$$
);
+-----------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML |
|-----------------------------------------|
| Semantic view was successfully created. |
+-----------------------------------------+
Replacing an existing semantic view¶
To replace an existing semantic view (for example, to change the definition of the view), specify OR REPLACE when executing CREATE SEMANTIC VIEW. If you want to preserve any privileges granted on the existing semantic view, specify COPY GRANTS. For example:
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;
Listing semantic views¶
To list semantic views in the current schema or a specified schema, execute the SHOW SEMANTIC VIEWS command. For example:
SHOW SEMANTIC VIEWS;
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
| 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 |
+-------------------------------+-----------------------+---------------+-------------------+----------------------------------------------+-----------------+-----------------+-----------+
You can also query the views for semantic views in the ACCOUNT_USAGE and INFORMATION_SCHEMA schemas.
Listing dimensions and metrics¶
To list the dimensions and metrics that are available in a view, schema, database, or account, you can run the SHOW SEMANTIC DIMENSIONS and SHOW SEMANTIC METRICS commands.
By default, the commands list the dimensions and metrics that are available in semantic views defined in the current schema:
SHOW SEMANTIC DIMENSIONS;
+---------------+-------------+--------------------+------------+---------------+--------------+-------------------+--------------------------------+
| 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 METRICS;
+---------------+-------------+--------------------+------------+------------------------------+--------------+----------+----------------------------------------+
| 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 and metrics defined in the following:
Semantic views in the current database:
SHOW SEMANTIC DIMENSIONS IN DATABASE; SHOW SEMANTIC METRICS IN DATABASE;
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 METRICS IN SCHEMA my_db.my_other_schema; SHOW SEMANTIC METRICS IN DATABASE my_db;
Semantic views in the account.
SHOW SEMANTIC DIMENSIONS IN ACCOUNT; SHOW SEMANTIC METRICS IN ACCOUNT;
A specific semantic view:
SHOW SEMANTIC DIMENSIONS IN my_semantic_view;
If you are querying a semantic view, you can use the SHOW SEMANTIC DIMENSIONS FOR METRIC command to determine which dimensions you can return when specifying a given metric. For details, see Choosing the dimensions that you can return for a given metric.
Viewing the details about a semantic view¶
To view the details of a semantic view, execute the DESCRIBE SEMANTIC VIEW command. For example:
DESCRIBE SEMANTIC VIEW tpch_rev_analysis;
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
| 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 |
+--------------+------------------------------+---------------+--------------------------+----------------------------------------+
Getting the SQL statement for a semantic view¶
You can call the GET_DDL function to retrieve the DDL statement that created a semantic view.
When calling GET_DDL, pass in 'SEMANTIC_VIEW' as the object type. For example:
SELECT GET_DDL('SEMANTIC_VIEW', 'tpch_rev_analysis', TRUE);
+-----------------------------------------------------------------------------------+
| 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' |
| ); |
+-----------------------------------------------------------------------------------+
Getting the YAML specification for a semantic view¶
To get the YAML specification for a semantic model for a semantic view, call the SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW function.
The following example returns the YAML specification for the semantic view named tpch_analysis in the database my_db and
schema my_schema:
SELECT SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW(
'my_db.my_schema.tpch_rev_analysis'
);
+-------------------------------------------------------------+
| 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 |
| |
+-------------------------------------------------------------+
Removing a semantic view¶
To remove a semantic view, execute the DROP SEMANTIC VIEW command. For example:
DROP SEMANTIC VIEW tpch_rev_analysis;
Granting privileges on semantic views¶
Semantic view privileges lists the privileges that you can grant on a semantic view.
The following privileges on a semantic view are required to work with the view:
Any privilege (for example, REFERENCE OR SELECT) is required to execute the DESCRIBE SEMANTIC VIEW command on the view.
SELECT is required to execute
SELECT ... FROM SEMANTIC_VIEW().Either of these privileges is required to display the view in the output of the SHOW SEMANTIC VIEWS command.
Note
To query a semantic view, you don’t need the SELECT privilege on the tables used in the semantic view. You only need the SELECT privilege on the semantic view itself.
This behavior is consistent with the privileges required to query standard views.
To use a semantic view that you do not own in Cortex Analyst, you must use a role that has the REFERENCES and SELECT privileges on that view.
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 execute the following statement:
GRANT REFERENCES, SELECT ON SEMANTIC VIEW my_semantic_view TO ROLE my_analyst_role;
If you have a schema containing semantic views that you want to share with Cortex Analyst users, you can use future grants to grant the privileges on any semantic view that you create in that schema. For example:
GRANT REFERENCES, SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA my_schema TO ROLE my_analyst_role;