CREATE SEMANTIC VIEW

Creates a new semantic view in the current/specified schema.

See also:

DESCRIBE SEMANTIC VIEW , DROP SEMANTIC VIEW , SHOW SEMANTIC VIEWS

Syntax

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name>
  TABLES ( logicalTable [ , ... ] )
  [ RELATIONSHIPS ( relationshipDef [ , ... ] ) ]
  [ FACTS ( semanticExpression [ , ... ] ) ]
  [ DIMENSIONS ( semanticExpression [ , ... ] ) ]
  [ METRICS ( semanticExpression [ , ... ] ) ]
  [ COMMENT = '<comment_about_semantic_view>' ]
Copy

where:

logicalTable ::=
  [ <table_alias> AS ] <table_name>
  [ PRIMARY KEY ( <primary_key_column_name> [ , ... ] ) ]
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_table>' ]
Copy
relationshipDef ::=
  [ <relationship_identifier> AS ]
  <table_alias> ( <column_name> [ , ... ] )
  REFERENCES
  <ref_table_alias> [ ( <ref_column_name> [ , ... ] ) ]
Copy
semanticExpression ::=
  <table_alias>.<dim_fact_or_metric> AS <sql_expr>
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_dim_fact_or_metric>' ]
Copy

Note

The order of the clauses is important. For example, you must specify the FACTS clause before the DIMENSIONS clause.

You can refer to semantic expressions that are defined in later clauses. For example, even if fact_2 is defined after fact_1, you can still use fact_2 in the definition of fact_1.

Required parameters

name

Specifies the name of the semantic view; the name must be unique for the schema in which the table is created.

In addition, the identifier must start with an alphabetic character and cannot contain spaces or special characters unless the entire identifier string is enclosed in double quotes (for example, "My object"). Identifiers enclosed in double quotes are also case-sensitive.

For more information, see Identifier requirements.

Optional parameters

COMMENT = 'comment_about_semantic_view'

Specifies a comment about the semantic view.

Parameters for logical tables (logicalTable)

table_alias AS

Specifies an optional alias for the logical table.

  • If you specify an alias, you must use this alias when referring to the logical table in relationships, facts, dimensions, and metrics.

  • If you do not specify an alias, you use the unqualified logical table name to refer to the table.

table_name

Specifies the name of the logical table.

PRIMARY KEY ( primary_key_column_name [ , ... ] )

Specifies the names of one or more columns in the logical table that serve as the primary key of the table.

WITH SYNONYMS [ = ] ( 'synonym' [ , ... ] )

Specifies one or more synonyms for the logical table. Unlike aliases, synonyms are used for informational purposes only. You do not use synonyms to refer to the logical table in relationships, dimensions, metrics, and facts.

COMMENT = 'comment_about_table'

Specifies a comment about the logical table.

Parameters for relationships (relationshipDef)

relationship_identifier AS

Specifies an optional identifier for the relationship.

table_alias ( column_name [ , ... ] )

Specifies one of the logical tables and one or more of its columns that refers to columns in another logical table.

ref_table_alias [ ( ref_column_name [ , ... ] ) ]

Specifies the other logical table and one or more of its columns that are referred to by the first logical table.

Parameters for facts, dimensions, and metrics (semanticExpression)

In a semantic view, you must define at least one dimension or metric, which means that you must specify at least one DIMENSIONS or METRICS clause.

Use the following parameters to define the fact, dimension, or metric:

table_alias.semantic_expression_name AS sql_expr

Specifies a name for a dimension, fact, or metric and the SQL expression for computing that dimension, fact, or metric.

WITH SYNONYMS [ = ] ( 'synonym' [ , ... ] )

Specifies one or more optional synonyms for the dimension, fact, or metric. Note that synonyms are used for informational purposes only. You cannot use a synonym to refer to a dimension, fact, or metric in another dimension, fact, or metric.

COMMENT = 'comment_about_dim_fact_or_metric'

Specifies an optional comment about the dimension, fact, or metric.

Access control requirements

A role used to execute this operation must have the following privileges at a minimum:

Privilege

Object

Notes

CREATE SEMANTIC VIEW

Schema

Required to create a new semantic view.

SELECT

Table, view

Required on any tables and/or views used in the semantic view definition.

The USAGE privilege on the parent database and schema are required to perform operations on any object in a schema.

For instructions on creating a custom role with a specified set of privileges, see Creating custom roles.

For general information about roles and privilege grants for performing SQL actions on securable objects, see Overview of Access Control.

Usage notes

  • Regarding metadata:

    Attention

    Customers should ensure that no personal data (other than for a User object), sensitive data, export-controlled data, or other regulated data is entered as metadata when using the Snowflake service. For more information, see Metadata fields in Snowflake.

  • CREATE OR REPLACE <object> statements are atomic. That is, when an object is replaced, the old object is deleted and the new object is created in a single transaction.

Examples

See Creating a semantic view.

Language: English