Querying semantic views¶
Privileges required to query a semantic view¶
If you are using a role that does not own the semantic view, you must be granted the SELECT privilege on that semantic view to query that semantic view.
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.
For information about granting privileges on semantic views, see Granting privileges on semantic views.
Querying a semantic view¶
To query a semantic view, use the SEMANTIC_VIEW clause in the FROM clause. The
following example selects the customer_market_segment dimension and the order_average_value metric from the
tpch_analysis semantic view, which you defined earlier:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS customer.customer_market_segment
METRICS orders.order_average_value
);
+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE | 142570.25947219 |
| FURNITURE | 142563.63314267 |
| MACHINERY | 142655.91550608 |
| HOUSEHOLD | 141659.94753445 |
| BUILDING | 142425.37987558 |
+-------------------------+---------------------+
The following example selects the customer_name dimension and the c_customer_order_count fact from the
tpch_analysis semantic view:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS customer.customer_name
FACTS customer.c_customer_order_count
)
ORDER BY customer_name
LIMIT 5;
+--------------------+------------------------+
| CUSTOMER_NAME | C_CUSTOMER_ORDER_COUNT |
|--------------------+------------------------|
| Customer#000000001 | 9 |
| Customer#000000002 | 11 |
| Customer#000000003 | 0 |
| Customer#000000004 | 20 |
| Customer#000000005 | 10 |
+--------------------+------------------------+
Note the following:
In the SEMANTIC_VIEW clause, you must specify at least one of the following clauses:
METRICS
DIMENSIONS
FACTS
You cannot omit all of these clauses from the SEMANTIC_VIEW clause.
When specifying a combination of these clauses, note the following:
You cannot specify FACTS and METRICS in the same SEMANTIC_VIEW clause.
Although you can specify both FACTS and DIMENSIONS in a query, you should do so only if the dimensions can uniquely determine the facts.
The query groups the results by dimensions. if the facts do not depend on the dimensions, the results can be non-deterministic.
If you specify both FACTS and DIMENSIONS, all facts and dimensions used in the query (including those specified in the WHERE clause) must be defined in the same logical table.
If you specify a dimension and a metric, the logical table for the dimension must be related to the logical table for the metric.
In addition, the logical table for the dimension must have an equal or lower level of granularity than the logical table for the metric.
To determine which dimensions meet this criteria, you can run the SHOW SEMANTIC DIMENSIONS FOR METRIC command.
For details, see Choosing the dimensions that you can return for a given metric.
In the DIMENSIONS clause, you can specify an expression that refers to a fact. Similarly, in the FACTS clause, you can specify an expression that refers to a dimension. For example:
-- Dimension expression that refers to a fact DIMENSIONS my_table.my_fact -- Fact expression that refers to a dimension FACTS my_table.my_dimension
One of the main differences between using DIMENSIONS and FACTS is that the query groups the results by the dimensions and expressions specified in the DIMENSIONS clause.
Specify the METRICS, DIMENSIONS, and FACTS clauses in the order in which you want them to appear in the results.
If you want the dimensions to appear first in the results, specify DIMENSIONS before METRICS. Otherwise, specify METRICS first.
For example, suppose that you specify the METRICS clause first:
SELECT * FROM SEMANTIC_VIEW( tpch_analysis METRICS customer.customer_order_count DIMENSIONS customer.customer_name ) ORDER BY customer_name LIMIT 5;
In the output, the first column is the metric column (
customer_order_count) and the second column is the dimension column (customer_name):+----------------------+--------------------+ | CUSTOMER_ORDER_COUNT | CUSTOMER_NAME | |----------------------+--------------------| | 6 | Customer#000000001 | | 7 | Customer#000000002 | | 0 | Customer#000000003 | | 20 | Customer#000000004 | | 4 | Customer#000000005 | +----------------------+--------------------+
If you instead specify the DIMENSIONS clause first:
SELECT * FROM SEMANTIC_VIEW( tpch_analysis DIMENSIONS customer.customer_name METRICS customer.customer_order_count ) ORDER BY customer_name LIMIT 5;
In the output, the first column is the dimension column (
customer_name) and the second column is the metric column (customer_order_count):+--------------------+----------------------+ | CUSTOMER_NAME | CUSTOMER_ORDER_COUNT | |--------------------+----------------------| | Customer#000000001 | 6 | | Customer#000000002 | 7 | | Customer#000000003 | 0 | | Customer#000000004 | 20 | | Customer#000000005 | 4 | +--------------------+----------------------+
You can use the relation defined by a SEMANTIC_VIEW clause in other SQL constructs, including JOIN, PIVOT, UNPIVOT, GROUP BY, and common table expressions (CTEs).
The output column headers use the unqualified names of the metrics and dimensions.
If you have multiple metrics and dimensions with the same names, use a table alias to assign different names to the column headers. See Handling duplicate column names in the output.
To return all metrics or dimensions in a given logical table, use an asterisk as a wildcard, qualified by the name of the logical
table. For example, to return all metrics and dimensions defined in the customer logical table:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS customer.*
METRICS customer.*
);
+-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------+
| CUSTOMER_COUNTRY_CODE | CUSTOMER_MARKET_SEGMENT | CUSTOMER_NAME | CUSTOMER_NATION_NAME | CUSTOMER_REGION_NAME | CUSTOMER_COUNT | CUSTOMER_ORDER_COUNT |
|-----------------------+-------------------------+--------------------+----------------------+----------------------+----------------+----------------------|
| 18 | BUILDING | Customer#000034857 | INDIA | ASIA | 1 | 0 |
| 14 | AUTOMOBILE | Customer#000145116 | EGYPT | MIDDLE EAST | 1 | 0 |
...
Choosing the dimensions that you can return for a given metric¶
When you specify a dimension and a metric to return, the base table for the dimension must be related to the base table for the metric. In addition, the base table for the dimension must have an equal or lower level of granularity than the base table for the metric.
For example, suppose that you query the tpch_analysis semantic view that you created in Example of using SQL to create a semantic view. Suppose that you want
to return the customer.customer_count dimension and the orders.order_date metric:
SELECT * FROM SEMANTIC_VIEW (
tpch_analysis
DIMENSIONS orders.order_date
METRICS customer.customer_order_count
);
This query fails because the orders table for the order_date dimension has a higher level of granularity than the
customer table for the customer_order_count metric:
010234 (42601): SQL compilation error:
Invalid dimension specified: The dimension entity 'ORDERS' must be related to and
have an equal or lower level of granularity compared to the base metric or dimension entity 'CUSTOMER'.
To list the dimensions that you can return with a specific metric, run the SHOW SEMANTIC DIMENSIONS FOR METRIC command. For example:
SHOW SEMANTIC DIMENSIONS IN tpch_analysis FOR METRIC customer_order_count;
+------------+-------------------------+-------------+----------+----------+---------+
| table_name | name | data_type | required | synonyms | comment |
|------------+-------------------------+-------------+----------+----------+---------|
| CUSTOMER | CUSTOMER_COUNTRY_CODE | VARCHAR(15) | false | NULL | NULL |
| CUSTOMER | CUSTOMER_MARKET_SEGMENT | VARCHAR(10) | false | NULL | NULL |
| CUSTOMER | CUSTOMER_NAME | VARCHAR(25) | false | NULL | NULL |
| CUSTOMER | CUSTOMER_NATION_NAME | VARCHAR(25) | false | NULL | NULL |
| CUSTOMER | CUSTOMER_REGION_NAME | VARCHAR(25) | false | NULL | NULL |
| NATION | NATION_NAME | VARCHAR(25) | false | NULL | NULL |
+------------+-------------------------+-------------+----------+----------+---------+
Basic examples¶
The following examples use the tpch_analysis view defined in Example of using SQL to create a semantic view:
Retrieving a metric¶
The following statement retrieves the total count of customers by querying a metric:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
METRICS customer.customer_count
);
+----------------+
| CUSTOMER_COUNT |
+----------------+
| 15000 |
+----------------+
Grouping metric data by a dimension¶
The following statement groups metric data (order_average_value) by a dimension (customer_market_segment):
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS customer.customer_market_segment
METRICS orders.order_average_value
);
+-------------------------+---------------------+
| CUSTOMER_MARKET_SEGMENT | ORDER_AVERAGE_VALUE |
+-------------------------+---------------------+
| AUTOMOBILE | 142570.25947219 |
| FURNITURE | 142563.63314267 |
| MACHINERY | 142655.91550608 |
| HOUSEHOLD | 141659.94753445 |
| BUILDING | 142425.37987558 |
+-------------------------+---------------------+
Using the SEMANTIC_VIEW subclause with other constructs¶
The following example demonstrates how you can use dimensions and metrics in the SEMANTIC_VIEW subclause with other SQL constructs to filter, sort, and limit results:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS customer.customer_name
METRICS orders.average_line_items_per_order,
orders.order_average_value
)
WHERE average_line_items_per_order > 4
ORDER BY average_line_items_per_order DESC
LIMIT 5;
+--------------------+------------------------------+---------------------+
| CUSTOMER_NAME | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
+--------------------+------------------------------+---------------------+
| Customer#000045678 | 6.87 | 175432.21 |
| Customer#000067890 | 6.42 | 182376.58 |
| Customer#000012345 | 5.93 | 169847.42 |
| Customer#000034567 | 5.76 | 178952.36 |
| Customer#000056789 | 5.64 | 171248.75 |
+--------------------+------------------------------+---------------------+
Specifying scalar expressions that use dimensions¶
The following example uses a scalar expression that refers to a dimension in the DIMENSIONS clause:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS DATE_PART('year', orders.order_date)
);
+--------------------------------------+
| DATE_PART('YEAR', ORDERS.ORDER_DATE) |
|--------------------------------------|
| 1992 |
| 1997 |
| 1998 |
| 1993 |
| 1996 |
| 1994 |
| 1995 |
+--------------------------------------+
Specifying the WHERE clause¶
The following example specifies a WHERE clause that refers to a dimension in the DIMENSIONS clause:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
DIMENSIONS orders.order_date
METRICS orders.average_line_items_per_order,
orders.order_average_value
WHERE orders.order_date > '1995-01-01'
)
ORDER BY order_date ASC
LIMIT 5;
+------------+------------------------------+---------------------+
| ORDER_DATE | AVERAGE_LINE_ITEMS_PER_ORDER | ORDER_AVERAGE_VALUE |
|------------+------------------------------+---------------------|
| 1995-01-02 | 3.884547 | 151237.54900533 |
| 1995-01-03 | 3.894819 | 145751.84384615 |
| 1995-01-04 | 3.838863 | 145331.39167457 |
| 1995-01-05 | 4.040689 | 150723.67353678 |
| 1995-01-06 | 3.990755 | 152786.54109399 |
+------------+------------------------------+---------------------+
Specifying facts in the WHERE clause¶
The following example uses the region.r_name fact in a condition in the WHERE clause:
SELECT * FROM SEMANTIC_VIEW(
tpch_analysis
FACTS customer.c_customer_order_count
WHERE orders.order_date < '2021-01-01' AND region.r_name = 'AMERICA'
);
Handling duplicate column names in the output¶
The output columns use the unqualified names of the metrics and dimensions. If you have multiple metrics and dimensions with the same names, multiple columns will use the same name.
To work around this, use a table alias to assign different names to the columns.
For example, suppose that you define the following semantic view, which defines the dimensions nation.name and
region.name:
CREATE OR REPLACE SEMANTIC VIEW duplicate_names
TABLES (
nation AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION PRIMARY KEY (n_nationkey),
region AS SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION PRIMARY KEY (r_regionkey)
)
RELATIONSHIPS (
nation (n_regionkey) REFERENCES region
)
DIMENSIONS (
nation.name AS nation.n_name,
region.name AS region.r_name
);
If you query this view and select these two dimensions, the output includes two columns named name without any qualifiers:
SELECT * FROM SEMANTIC_VIEW(
duplicate_names
DIMENSIONS nation.name, region.name
);
+----------------+-------------+
| NAME | NAME |
+----------------+-------------+
| BRAZIL | AMERICA |
| MOROCCO | AFRICA |
| UNITED KINGDOM | EUROPE |
| IRAN | MIDDLE EAST |
| FRANCE | EUROPE |
| ... | ... |
+----------------+-------------+
To disambiguate the columns, use a table alias to assign different column names (for example, nation_name and
region_name):
SELECT * FROM SEMANTIC_VIEW(
duplicate_names
DIMENSIONS nation.name, region.name
) AS table_alias(nation_name, region_name);
+----------------+-------------+
| NATION_NAME | REGION_NAME |
+----------------+-------------+
| BRAZIL | AMERICA |
| MOROCCO | AFRICA |
| UNITED KINGDOM | EUROPE |
| IRAN | MIDDLE EAST |
| FRANCE | EUROPE |
| ... | ... |
+----------------+-------------+
Defining and querying window function metrics¶
You can define metrics that call window functions and pass in aggregated values. These metrics are called window function metrics.
The following examples illustrate the difference between a window function metric and a metric that passes a row-level expression to a window function:
The following metric is a window function metric:
METRICS ( table_1.metric_1 AS SUM(table_1.metric_3) OVER( ... ) )
In this example, the SUM window function takes another metric (
table_1.metric_3) as an argument.The following metric is also a window function metric:
METRICS ( table_1.metric_2 AS SUM( SUM(table_1.column_1) ) OVER( ... ) )
In this example, the SUM window function takes a valid metric expression (
SUM(table_1.column_1)) as an argument.The following metric is not a window function metric:
METRICS ( table_1.metric_1 AS SUM( SUM(table_1.column_1) OVER( ... ) ) )
In this example, the SUM window function takes a column (
table_1.column_1) as an argument, and the result of that window function call is passed to a separate SUM aggregate function call.
The following sections explain how to define and query window function metrics:
Defining window function metrics¶
When specifying a window function call, use this syntax, which is described in Parameters for window function metrics.
The following example creates a semantic view that includes the definitions of several window function metrics. The example uses tables from the TPC-DS sample database. For information on accessing this database, see How Do I Add the TPC-DS Data Set to My Account?.
CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
TABLES (
store_sales AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.store_sales,
date AS SNOWFLAKE_SAMPLE_DATA.TPCDS_SF10TCL.date_dim PRIMARY KEY (d_date_sk)
)
RELATIONSHIPS (
sales_to_date AS store_sales(ss_sold_date_sk) REFERENCES date(d_date_sk)
)
DIMENSIONS (
date.date AS d_date,
date.d_date_sk AS d_date_sk,
date.year AS d_year
)
METRICS (
store_sales.total_sales_quantity AS SUM(ss_quantity)
WITH SYNONYMS = ('Total sales quantity'),
store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
WITH SYNONYMS = ('Running 7-day average of total sales quantity'),
store_sales.total_sales_quantity_30_days_ago AS LAG(total_sales_quantity, 30)
OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date)
WITH SYNONYMS = ('Sales quantity 30 days ago'),
store_sales.avg_7_days_sales_quantity_30_days_ago AS AVG(total_sales_quantity)
OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
RANGE BETWEEN INTERVAL '36 days' PRECEDING AND INTERVAL '30 days' PRECEDING)
WITH SYNONYMS = ('Running 7-day average of total sales quantity 30 days ago')
);
You can also use other metrics from the same logical table in the metric definition. For example:
METRICS (
orders.m3 AS SUM(m2) OVER (PARTITION BY m1 ORDER BY m2),
orders.m4 AS ((SUM(m2) OVER (..)) / m1) + 1
)
Note
You can’t use window function metrics in row-level calculations (facts and dimensions) or in the definitions of other metrics.
Querying window function metrics¶
When you query a semantic view and the query returns a window function metric, you must also return the dimensions specified in
PARTITION BY dimension, PARTITION BY EXCLUDING dimension, and ORDER BY dimension in the
CREATE SEMANTIC VIEW statement for the semantic view.
For example, suppose that you specify the date.date and date.year dimensions in the PARTITION BY EXCLUDING and ORDER BY
clauses in the definition of the store_sales.avg_7_days_sales_quantity metric:
CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
...
DIMENSIONS (
...
date.date AS d_date,
...
date.year AS d_year
...
)
METRICS (
...
store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
OVER (PARTITION BY EXCLUDING date.date, date.year ORDER BY date.date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW)
WITH SYNONYMS = ('Running 7-day average of total sales quantity'),
...
);
If you return the store_sales.avg_7_days_sales_quantity metric in a query, you must also return the date.date and
date.year dimensions:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.avg_7_days_sales_quantity
);
If you omit the date.date and date.year dimensions, an error occurs.
010260 (42601): SQL compilation error:
Invalid semantic view query: Dimension 'DATE.DATE' used in a
window function metric must be requested in the query.
To determine which dimensions you must specify in the query, execute the
SHOW SEMANTIC DIMENSIONS FOR METRIC command. For example, to determine the dimensions that you must
specify when retrieving the store_sales.avg_7_days_sales_quantity metric, run this command:
SHOW SEMANTIC DIMENSIONS IN sv_window_function_example FOR METRIC avg_7_days_sales_quantity;
In the output of the command, the required column contains true for the dimensions that you must specify in the query.
+------------+-----------+--------------+----------+----------+---------+
| table_name | name | data_type | required | synonyms | comment |
|------------+-----------+--------------+----------+----------+---------|
| DATE | DATE | DATE | true | NULL | NULL |
| DATE | D_DATE_SK | NUMBER(38,0) | false | NULL | NULL |
| DATE | YEAR | NUMBER(38,0) | true | NULL | NULL |
+------------+-----------+--------------+----------+----------+---------+
The following additional examples query the window function metrics defined in Defining window function metrics. Note that the DIMENSIONS clause includes the dimensions specified in the PARTITION BY EXCLUDING and ORDER BY clauses of the metric definitions.
The following example returns the sales quantity 30 days ago:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.total_sales_quantity_30_days_ago
);
The following example returns the running 7-day average of the total sales quantity 30 days ago:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.avg_7_days_sales_quantity_30_days_ago
);