Semantic views as standard SQL views¶

In this preview, you can query a semantic view by using the standard clauses in a SELECT statement, as you would for a standard SQL view. You don’t need to specify the SEMANTIC_VIEW clause in your statement in order to query a semantic view.

For information, see Using standard clauses in a SELECT statement.

In addition, semantic views now appear in the output of the SHOW OBJECTS command, and dimensions, facts, and metrics appear in the output of the SHOW COLUMNS command.

For information, see Using SHOW OBJECTS and SHOW COLUMNS to list semantic view information.

Using standard clauses in a SELECT statement¶

For example, the following query specifies the SEMANTIC_VIEW clause:

SELECT * FROM SEMANTIC_VIEW(
    tpch_analysis
    DIMENSIONS customer.customer_market_segment
    METRICS orders.order_average_value
  )
  ORDER BY customer_market_segment;

Copy

The following statement demonstrates how to execute the same query without specifying the SEMANTIC_VIEW clause:

SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment
  ORDER BY customer_market_segment;

Copy

Internally, this statement is rewritten as a SELECT statement that uses the SEMANTIC_VIEW clause:

The expressions that you specify in the GROUP BY clause are rewritten into the DIMENSIONS clause in the SEMANTIC_VIEW clause.

In the SELECT statement, if you use an expression that is not in the GROUP BY clause (for example, a dimension expression in the SELECT list), the rewrite uses that expression in the FACTS clause in the SEMANTIC_VIEW clause.
When you refer to a metric in one of the clauses, you must pass the metric to the AGG, MIN, MAX, or ANY_VALUE function.
Any other calculated values that don’t fall into the first two categories are considered to be fact references.

The next sections explain these requirements in more detail:

Requirements for dimensions and metrics in a SELECT statement
Selecting metrics
Selecting dimensions
Specifying the WHERE clause
Specifying the HAVING clause
Limitations
Reference: SELECT for semantic views

Requirements for dimensions and metrics in a SELECT statement¶

In the SELECT statement, you can only refer to dimensions and metrics that have distinct names and that are not distinguished by their logical table name. For example, suppose that a semantic view has two dimensions that have the unqualified name name:

DIMENSIONS (
  nation.name AS nation.n_name,
  region.name AS region.r_name
);

Copy

In the SELECT statement, when you specify the qualified name of a dimension or metric, the qualifier is interpreted as the name of the semantic view, not the name of a logical table:

SELECT nation.name, region.name
  FROM duplicate_names
  GROUP BY nation.name, region.name;

Copy

000904 (42000): SQL compilation error: error line 1 at position 7
invalid identifier 'NATION.NAME'

Selecting metrics¶

If you want to select a metric, you must pass the metric to one of the following functions:

MIN
MAX
ANY_VALUE
AGG (a new special aggregate function for metrics in semantic views)

For example:

SELECT AGG(order_average_value) FROM tpch_analysis;

Copy

Note

The aggregate function has no effect on the metric because the function evaluates one value of the metric.

For example, passing the metric to the MIN function returns the minumum of one value of metric, which is the same as the value of that metric.

Because of this, there is no difference between passing the metric to MIN, MAX, ANY_VALUE, or AGG.

In the SELECT list, you can specify an expression that uses a metric. For example:

SELECT AGG(order_average_value) * 10 FROM tpch_analysis;

Copy

Selecting dimensions¶

If the SELECT list includes dimensions, you must specify those dimensions in the GROUP BY clause. For example:

SELECT customer_market_segment, customer_nation_name, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment, customer_nation_name;

Copy

In the SELECT list and in the GROUP BY clause, you can specify a dimension or a scalar expression that uses a dimension or fact. For example:

SELECT LOWER(customer_nation_name), AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_nation_name;

Copy

Specifying the WHERE clause¶

In the WHERE clause, you can only use conditional expressions that refer to dimensions or facts. For example:

SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  WHERE customer_market_segment = 'BUILDING'
  GROUP BY customer_market_segment;

Copy

The dimensions must be reachable by every metric used in the query.

Specifying the HAVING clause¶

In the HAVING clause, you can only specify metrics, and you must pass the metric to one of the aggregate functions listed in Selecting metrics. For example:

SELECT customer_market_segment, AGG(order_average_value)
  FROM tpch_analysis
  GROUP BY customer_market_segment
  HAVING AGG(order_average_value) > 142500;

Copy

Limitations¶

You cannot specify the following in the SELECT statement:

Extensions of the FROM clause, including:
- PIVOT
- UNPIVOT
- MATCH_RECOGNIZE
- LATERAL
Joins
Window function calls
QUALIFY
Subqueries

Reference: SELECT for semantic views¶

The following sections describe the syntax of the SELECT statement when you query a semantic view.

Syntax¶

SELECT [ DISTINCT ]
    {
      [<qualifiers>.]<dimension_or_fact> |
      { MIN | MAX | ANY_VALUE | AGG }( [<qualifiers>.]<metric> )
    }
    [ , ... ]
  FROM <semantic_view> [ AS <alias> ]
  [ WHERE <expr_using_dimensions_or_facts> ]
  [ GROUP BY <expr_using_dimensions_or_facts> [ , ... ] ]
  [ HAVING <expr_using_metrics> ]
  [ ORDER BY ... ]
  [ LIMIT ... ]

Copy

Parameters¶

DISTINCT

Eliminates duplicate values from the result set.

[qualifiers.]dimension

Specifies a dimension or a scalar expression that uses a dimension to return in the result set.

Note

qualifiers is interpreted as the name of the semantic view, not the name of a logical table.

If you specify a dimension here, you must also specify that dimension in the GROUP BY clause.

{ MIN | MAX | ANY_VALUE | AGG }( [qualifiers.]metric )

Specifies a metric to return in the result set. You must pass the metric to one of the following aggregate functions:

MIN
MAX
ANY_VALUE
AGG (a special aggregate function for semantic views)

These aggregate functions do not perform any additional aggregation. They simply evaluate the metric and return the metric value. There is no difference between calling MIN, MAX, ANY_VALUE, or AGG.

Note

qualifiers is interpreted as the name of the semantic view, not the name of a logical table.

FROM semantic_view [ AS alias ]

Specifies the semantic view to query and an optional alias for that semantic view.

WHERE expr_using_dimensions_or_facts

Specifies a conditional expression that refers to dimensions or facts in the semantic view.

GROUP BY expr_using_dimensions_or_facts [ , ... ]

Specifies an expression that refers to dimensions or facts in the semantic view to group the rows in the results.

HAVING expr_using_metrics

Specifies a conditional expression that refers to metrics in the semantic view.

As is the case with referring to metrics in the SELECT list, you must pass these metrics to the MIN, MAX, ANY_VALUE, or AGG aggregate functions.

ORDER BY ...

Specifies the order of the rows in the result set.

LIMIT ...

Constrains the maximum number of rows returned in the result set.

Using SHOW OBJECTS and SHOW COLUMNS to list semantic view information¶

When you run the SHOW OBJECTS command, the output includes semantic views. In the kind column, the type of object is listed as VIEW.

For example:

SHOW OBJECTS LIKE '%TPCH_ANALYSIS%' IN SCHEMA;

Copy

+-------------------------------+---------------+---------------+-------------+------+---------+------------+------+-------+---------+----------------+-----------------+-----------+------------+------------+
| created_on                    | name          | database_name | schema_name | kind | comment | cluster_by | rows | bytes | owner   | retention_time | owner_role_type | is_hybrid | is_dynamic | is_iceberg |
|-------------------------------+---------------+---------------+-------------+------+---------+------------+------+-------+---------+----------------+-----------------+-----------+------------+------------|
| 2025-10-03 16:28:01.505 -0700 | TPCH_ANALYSIS | MY_DB         | MY_SCHEMA   | VIEW |         |            |    0 |     0 | MY_ROLE | 1              | ROLE            | N         | N          | N          |
+-------------------------------+---------------+---------------+-------------+------+---------+------------+------+-------+---------+----------------+-----------------+-----------+------------+------------+

When you run the SHOW COLUMNS command for a semantic view, the output includes the dimensions, facts, and metrics in the semantic view. The kind column indicates if the row represents a DIMENSION, FACT, or METRIC.

For example:

SHOW COLUMNS IN VIEW my_db.my_schema.tpch_analysis;

Copy

+---------------+-------------+------------------------------+-----------------------------------------------------------------------------------------+----------+---------+-----------+------------+---------+---------------+---------------+-------------------------+
| table_name    | schema_name | column_name                  | data_type                                                                               | null?    | default | kind      | expression | comment | database_name | autoincrement | schema_evolution_record |
|---------------+-------------+------------------------------+-----------------------------------------------------------------------------------------+----------+---------+-----------+------------+---------+---------------+---------------+-------------------------|
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_COUNT               | {"type":"FIXED","precision":18,"scale":0,"nullable":false}                              | NOT_NULL |         | METRIC    |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_COUNTRY_CODE        | {"type":"TEXT","length":15,"byteLength":60,"nullable":true,"fixed":false}               | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_MARKET_SEGMENT      | {"type":"TEXT","length":10,"byteLength":40,"nullable":true,"fixed":false}               | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_NAME                | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_NATION_NAME         | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_ORDER_COUNT         | {"type":"FIXED","precision":30,"scale":0,"nullable":true}                               | true     |         | METRIC    |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | CUSTOMER_REGION_NAME         | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | C_CUSTOMER_ORDER_COUNT       | {"type":"FIXED","precision":18,"scale":0,"nullable":false}                              | NOT_NULL |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | LINE_ITEM_ID                 | {"type":"TEXT","length":134217728,"byteLength":134217728,"nullable":true,"fixed":false} | true     |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | NATION_NAME                  | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | N_NAME                       | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | AVERAGE_LINE_ITEMS_PER_ORDER | {"type":"FIXED","precision":36,"scale":6,"nullable":true}                               | true     |         | METRIC    |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | COUNT_LINE_ITEMS             | {"type":"FIXED","precision":18,"scale":0,"nullable":false}                              | NOT_NULL |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | ORDER_AVERAGE_VALUE          | {"type":"FIXED","precision":30,"scale":8,"nullable":true}                               | true     |         | METRIC    |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | ORDER_COUNT                  | {"type":"FIXED","precision":18,"scale":0,"nullable":false}                              | NOT_NULL |         | METRIC    |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | ORDER_DATE                   | {"type":"DATE","nullable":true}                                                         | true     |         | DIMENSION |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | O_ORDERKEY                   | {"type":"FIXED","precision":38,"scale":0,"nullable":true}                               | true     |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | R_NAME                       | {"type":"TEXT","length":25,"byteLength":100,"nullable":true,"fixed":false}              | true     |         | FACT      |            |         | MY_DB         |               | NULL                    |
| TPCH_ANALYSIS | MY_SCHEMA   | SUPPLIER_COUNT               | {"type":"FIXED","precision":18,"scale":0,"nullable":false}                              | NOT_NULL |         | METRIC    |            |         | MY_DB         |               | NULL                    |
+---------------+-------------+------------------------------+-----------------------------------------------------------------------------------------+----------+---------+-----------+------------+---------+---------------+---------------+-------------------------+