SEMANTIC_VIEW¶

Syntax¶

SEMANTIC_VIEW(
  [<namespace>.]<semantic_view_name>
  [ METRICS <metric> [ , ... ] ]
  [ DIMENSIONS <dimension_expr>  [ , ... ] ]
  [ WHERE <predicate> ]
)

Parameters¶

[namespace.]semantic_view_name

Specifies the identifier for the semantic view to query.

If the identifier contains spaces or special characters, the entire string must be enclosed in double quotes. Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

METRICS metric [ , ... ]

Specifies the metrics that you want to return in the results.

Note

You can’t specify an expression that uses a metric. For example, you can’t specify t1.metric_1 + 1.

For the names of the metrics:

• You can qualify the name of the metric (for example, my_logical_table.my_metric).

  Using the unqualified name works only if there are no other identifiers with the same unqualified name in the semantic view. For example, if a metric and a dimension use the same unqualified name, you must qualify the name of the metric in the query.

• To specify all metrics in a logical table, use an asterisk as a wildcard, qualified by the logical table name (for example, my_logical_table.*).

  You can’t specify an asterisk without qualifying it with a table name.

Specify the metrics in the order in which they should appear in the results.

DIMENSIONS dimension_expr [ , ... ]

Specifies the dimensions that you want to return in the results. You can also specify scalar expressions that refer to dimensions in the semantic view.

For the names of the dimensions:

• You can qualify the name of the dimension (for example, my_logical_table.my_dimension). Using the unqualified name works only if there are no other identifiers with the same unqualified name in the semantic view. For example, if a metric and a dimension use the same unqualified name, you must qualify the name of the dimension in the query.

• To specify all dimensions in a logical table, use an asterisk as a wildcard, qualified by the logical table name (for example, my_logical_table.*).

  You can’t specify an asterisk without qualifying it with a table name.

If you specify a scalar expression, you can’t refer to dimensions in other semantic views, facts, or metrics in that expression.

Specify the dimensions in the order in which they should appear in the results.

Note

If you are returning a window function metric, you must also return the dimensions that are specified in PARTITION BY dimension, PARTITION BY EXCLUDING dimension, and ORDER BY dimension clauses in the CREATE SEMANTIC VIEW statement for that semantic view.

See Defining and querying window function metrics.

WHERE predicate

A boolean expression. The expression can include logical operators, built-in functions, and user-defined functions (UDFs).

In the condition, you can only refer to dimensions or expressions that use dimensions.

This filter condition is applied before the metrics are computed.

Usage notes¶

• 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.

• If you specify a dimension and a metric, 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.

  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.

• 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;
  

  Copy

  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;
  

  Copy

  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¶

See Querying semantic views.