Querying a semantic view¶
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.
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 |
| ... | ... |
+----------------+-------------+