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 cannot specify an expression that uses a metric. For example, you cannot 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, use an asterisk, qualified by the logical table name (for example, my_logical_table.*). You cannot 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, use an asterisk, qualified by the logical table name (for example, my_logical_table.*). You cannot specify an asterisk without qualifying it with a table name.

If you specify a scalar expression, you cannot 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.

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.

• 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 a semantic view.