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
As is the case with standard views, you do not need the SELECT privilege on the tables used in the semantic view. You just need the SELECT privilege on the semantic view itself.
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:
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 |
+-------------------------+---------------------+
Note the following:
In the SEMANTIC_VIEWS clause, you must specify the METRICS clause, the DIMENSIONS clause, or both.
You cannot omit both of these clauses from a SEMANTIC_VIEWS clause.
Specify the METRICS and DIMENSIONS 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.
Basic examples¶
The following examples use the tpch_analysis view defined in Example of using SQL to create a semantic view:
Basic example of 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 |
+------------+------------------------------+---------------------+
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:
CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
TABLES (
store PRIMARY KEY (s_store_sk),
store_sales AS store_sales_small,
date AS date_dim PRIMARY KEY (d_date_sk)
)
RELATIONSHIPS (
sales_to_date AS store_sales(ss_sold_date_sk) REFERENCES date(cd_demo_sk)
)
DIMENSIONS (
store_sales.total_sales_quantity AS SUM(ss_quantity)
WITH SYNONYMS = ('Total sales quantity'),
date.date AS d_date,
date.d_date_sk AS d_date_sk,
data.year AS d_year
)
METRICS (
store_sales.avg_7_days_sales_quantity as AVG(total_sales_quantity)
OVER (PARTITION BY EXCLUDING date.date, data.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_dim.day dimension in the PARTITION BY EXCLUDING and ORDER BY clauses in the
definition of the sales.sales_7_day_avg metric:
CREATE OR REPLACE SEMANTIC VIEW sv_window_function_example
...
DIMENSIONS (
...
date_dim.day AS EXTRACT('day', d_date),
...
)
METRICS (
...
sales.sales_7_day_avg AS avg(sales.sale_amount)
OVER (PARTIION BY EXCLUDING date_dim.day ORDER BY date_dim.day
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW),
...
);
If you return the sales.sales_7_day_avg metric in a query, you must also return the date_dim.day dimension:
SELECT * FROM SEMANTIC_VIEW (
sv
DIMENSIONS date_dim.day
METRICS metrics sales.sales_7_day_avg
);
If you omit the date_dim.day dimension, an error occurs.
The following 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 running 7-day average of the total sales quantity:
SELECT * FROM SEMANTIC_VIEW (
sv_window_function_example
DIMENSIONS date.date, date.year
METRICS store_sales.avg_7_days_sales_quantity
);
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
);