CREATE SEMANTIC VIEW¶

在当前/指定的架构中创建新的语义视图。

语义视图必须符合这些验证规则。

另请参阅:: ALTER SEMANTIC VIEW , DESCRIBE SEMANTIC VIEW , DROP SEMANTIC VIEW , SHOW SEMANTIC VIEWS , SHOW SEMANTIC DIMENSIONS , SHOW SEMANTIC DIMENSIONS FOR METRIC , SHOW SEMANTIC FACTS , SHOW SEMANTIC METRICS , SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML

语法¶

CREATE [ OR REPLACE ] SEMANTIC VIEW [ IF NOT EXISTS ] <name>
  TABLES ( logicalTable [ , ... ] )
  [ RELATIONSHIPS ( relationshipDef [ , ... ] ) ]
  [ FACTS ( factExpression [ , ... ] ) ]
  [ DIMENSIONS ( dimensionExpression [ , ... ] ) ]
  [ METRICS ( { metricExpression | windowFunctionMetricExpression } [ , ... ] ) ]
  [ COMMENT = '<comment_about_semantic_view>' ]
  [ AI_SQL_GENERATION '<instructions_for_sql_generation>' ]
  [ AI_QUESTION_CATEGORIZATION '<instructions_for_question_categorization>' ]
  [ COPY GRANTS ]

其中：

逻辑表的参数为：

logicalTable ::=
  [ <table_alias> AS ] <table_name>
  [ PRIMARY KEY ( <primary_key_column_name> [ , ... ] ) ]
  [
    UNIQUE ( <unique_column_name> [ , ... ] )
    [ ... ]
  ]
  [
    CONSTRAINT [ <constraint_name> ]
      DISTINCT RANGE BETWEEN <start_column> AND <end_column> EXCLUSIVE
  ]
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_table>' ]

关系的参数为：

relationshipDef ::=
  [ <relationship_identifier> AS ]
  <table_alias> ( <column_name> [ , ... ] )
  REFERENCES
  <ref_table_alias> [ (
    [ ASOF ] <ref_column_name> [ , ... ] |
    BETWEEN <start_column> AND <end_column> EXCLUSIVE
  ) ]

The parameters for expressions in the definitions of facts are:

factExpression ::=
  [ { PRIVATE | PUBLIC } ] <table_alias>.<fact> AS <sql_expr>
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_the_fact>' ]

事实、维度和指标定义中表达式的参数为：

dimensionExpression ::=
  [ PUBLIC ] <table_alias>.<dimension> AS <sql_expr>
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_the_dimension>' ]
  [ WITH CORTEX SEARCH SERVICE <search_service_name> [ USING <search_service_column_name> ] ]

The parameters for expressions in the definitions of metrics are:

metricExpression ::=
  [ { PRIVATE | PUBLIC } ] <table_alias>.<metric>
    [ USING ( <relationship_name> [ , ... ] ) ]
    [
      NON ADDITIVE BY (
        <dimension> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ]
        [ , ... ]
      )
    ]
    AS <sql_expr>
  [ WITH SYNONYMS [ = ] ( '<synonym>' [ , ... ] ) ]
  [ COMMENT = '<comment_about_the_metric>' ]

您可以使用以下语法定义使用窗口函数的指标（窗口函数指标）：

windowFunctionMetricExpression ::=
  [ { PRIVATE | PUBLIC } ] <table_alias>.<metric> AS
    <window_function>( <metric> ) OVER (
      [ PARTITION BY { <exprs_using_dimensions_or_metrics> | EXCLUDING <dimensions> } ]
      [ ORDER BY <exprs_using_dimensions_or_metrics> [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
      [ <windowFrameClause> ]
    )

有关此语法的信息，请参阅窗口函数指标的参数。

备注

子句的顺序很重要。例如，您必须在 FACTS 子句之前指定 DIMENSIONS 子句。

您可以引用后面子句中定义的语义表达式。例如，即使 fact_2 是在 fact_1 之后定义，您仍然可以在 fact_1 的定义中使用 fact_2。

必填参数¶

name

指定语义视图的名称；该名称对于创建表的架构必须唯一。

此外，标识符必须以字母字符开头，且不能包含空格或特殊字符，除非整个标识符字符串放在双引号内（例如，"My object"）。放在双引号内的标识符也区分大小写。

有关更多信息，请参阅标识符要求。

可选参数¶

COMMENT = 'comment_about_semantic_view'

指定有关语义视图的注释。

AI_SQL_GENERATION 'instructions_for_sql_generation'

Specifies instructions for Cortex Analyst that explain how to generate the SQL statement.

For more information, see Providing custom instructions for Cortex Analyst.

AI_QUESTION_CATEGORIZATION 'instructions_for_question_categorization'

Specifies instructions for Cortex Analyst that explain how to classify questions.

For more information, see Providing custom instructions for Cortex Analyst.

COPY GRANTS

当您指定 OR REPLACE 用新的语义视图替换现有语义视图时，您可以设置此参数，将授予的现有语义视图的任何权限复制到新的语义视图。

该命令将现有语义视图之外 OWNERSHIP 的所有权限授权复制到新的语义视图。默认情况下，执行 CREATE SEMANTIC VIEW 语句的角色拥有新视图。

新语义视图不会继承为架构中对象类型定义的任何未来授权。

复制授权的操作在 CREATE SEMANTIC VIEW 语句中（换句话说，在同一事务中）自动发生。

如果您省略 COPYGRANTS，则新的语义视图不会继承对现有语义视图授予的任何显式访问权限，但会继承为架构中对象类型定义的任何未来授权。

逻辑表的参数¶

这些参数是逻辑表语法的一部分：

table_alias AS

为逻辑表指定可选别名。

如果您指定别名，则在关系、事实、维度和指标中引用逻辑表时必须使用此别名。
如果未指定别名，则使用非限定逻辑表名来引用该表。

table_name

指定逻辑表的名称。

PRIMARY KEY ( primary_key_column_name [ , ... ] )

指定逻辑表中用作表主键的一列或多列的名称。

UNIQUE ( unique_column_name [ , ... ] )

指定包含唯一值的列的名称或包含唯一值组合的列的名称。

例如，如果列 service_id 包含唯一值，请指定：

TABLES(
  ...
  product_table UNIQUE (service_id)

如果 product_area_id 和 product_id 列中的值组合唯一，请指定：

TABLES(
  ...
  product_table UNIQUE (product_area_id, product_id)
  ...

您可以在给定逻辑表中将多个列和多个列组合标识为唯一：

TABLES(
  ...
  product_table UNIQUE (product_area_id, product_id) UNIQUE (service_id)
  ...

备注

如果您已经将某列标识为主键列（使用 PRIMARYKEY），请不要为该列添加 UNIQUE 子句。

CONSTRAINT [ constraint_name ] . DISTINCT RANGE BETWEEN start_column AND end_column EXCLUSIVE

Specifies a constraint for a range join.

constraint_name

Specifies an optional name for the constraint.

If you omit this name, the command uses a system-generated name for the constraint.

DISTINCT RANGE BETWEEN start_column AND end_column EXCLUSIVE specifies that in each row, the range between start_column and end_column is a distinct range:

The range is a half-open interval (link removed), where the range is closed on the left side (start_column) and open on the right (end_column).

In other words, the time on the left is included in the range, but the time on the right is excluded from the range.

For example, for a row in this table, if the value in start_column is 2024-01-15 00:00:00.000 and the value in end_column is 2024-02-01 00:00:00.000, the range is:

2024-01-15 00:00:00.000 <= timestamp_from_other_table < 2024-02-01 00:00:00.000

The timestamp 2024-01-15 00:00:00.000 is included in this range, but the timestamp 2024-02-01 00:00:00.000 is not.
start_column and end_column must be physical columns from the same table or facts or dimensions from the same table.

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

为逻辑表指定一个或多个同义词。与别名不同，同义词仅用于提供信息。不要使用同义词来引用关系、维度、指标和事实中的逻辑表。

COMMENT = 'comment_about_table'

指定有关逻辑表的注释。

关系的参数¶

这些参数是关系语法的一部分：

relationship_identifier AS

为关系指定可选标识符。

table_alias ( column_name [ , ... ] )

指定引用另一个逻辑表中列的一个逻辑表及其一个或多个列。

ref_table_alias [ ( ... ) ]

Specifies the other logical table referred to by the first logical table.

You can specify one of the following in parentheses, depending on how you want to join the tables:

ref_column_name [ , ... ]

Specifies a column identified with the PRIMARY KEY or UNIQUE constraint in the logical table definition.

ASOF ref_column_name [ , ... ] )

For an ASOF join, specifies a column of one of the supported types.

备注

You can specify at most one ASOF keyword in the definition of a given relationship. You can specify this keyword before any column in the list.

BETWEEN start_column AND end_column EXCLUSIVE

For a range join, specifies the range of possible values in the first table.

start_column . end_column

Specifies the columns that define the start and end of the range.

You must define a constraint for these columns.
You cannot use the same column for both start_column and end_column.

If you want to use the same column, use an ASOF relationship.

备注

column_name must have a data type that can be coerced to the data types for start_column and end_column.

事实、维度和指标的参数¶

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

These parameters are part of the syntax for defining a fact, dimension, or metric:

{ PRIVATE | PUBLIC }

Specifies whether a fact or metric is private or public. Facts and metrics that are marked as private cannot be queried or used in a query condition.

备注

You cannot mark a dimension as private. Dimensions are always public. For a dimension, the effect is the same whether you specify or omit PUBLIC.

If you omit PRIVATE and PUBLIC, the dimension, fact, or metric is public by default.

table_alias.semantic_expression_name

Specifies a name for a dimension, fact, or metric.

USING relationship_name [ , ... ]

For metric definitions, specifies the relationship that should be used to join the tables and calculate the metric, when multiple relationship paths exist between two logical tables.

To define a derived metric (a metric that combines multiple metrics from different logical tables), omit table_alias. from the name.

有关定义有效语义视图的规则，请参阅 Snowflake 如何验证语义视图。

NON ADDITIVE BY ( dimension [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ , ... ] )

Specifies a list of dimensions that should not be used when summing the metric.

Instead, during query processing, the rows are sorted by the non-additive dimensions, and the values from the last rows (the latest snapshots of values) are aggregated to compute the metric.

{ ASC | DESC }

Optionally sorts the values of the non-additive dimensions in ascending (lowest to highest) or descending (highest to lowest) order, which determines what the last snapshot is.

Default: ASC

NULLS { FIRST | LAST }

Optionally specifies whether NULL values are sorted before/after non-NULL values, based on the sort order (ASC or DESC). The sort order determines what the last snapshot is.

Default: Depends on the sort order (ASC or DESC); see the usage notes in the ORDER BY documentation.

Specifying the NON ADDITIVE BY clause makes the metric a semi-additive metric.

For information, see Identifying the dimensions that should be non-additive for a metric.

AS sql_expr

Specifies the SQL expression for computing the dimension, fact, or metric.

See 定义事实、维度和指标. For the validation rules for these expressions, see Snowflake 如何验证语义视图.

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

为维度、事实或指标指定一个或多个可选同义词。请注意，同义词仅用于提供信息。您不能使用同义词来引用另一个维度、事实或指标中的维度、事实或指标。

COMMENT = 'comment_about_dim_fact_or_metric'

指定有关维度、事实或指标的可选注释。

WITH CORTEX SEARCH SERVICE search_service_name [ USING search_service_column_name ]

Specifies the Cortex Search Service to use for this dimension.

You can only specify this parameter for dimensions (and not for facts or metrics).

If the Cortex Search Service is in a different database or schema, qualify the name of the service (for example, my_db.my_schema.my_service).

You can set the optional USING clause to the name of the column in the Cortex Search Service.

窗口函数指标的参数¶

这些参数是定义窗口函数指标的语法的一部分：

metric

为该窗口函数指定指标表达式。您可以指定指标或任何有效的指标表达式，用于在该实体中定义指标。

PARTITION BY ...

将行分组为分区。您可以按一组指定的表达式进行分区，也可以按查询中指定的所有维度（选定维度除外）进行分区：

PARTITION BY exprs_using_dimensions_or_metrics

按 SQL 表达式将行分组。在 SQL 表达式中：

表达式中的任何维度都必须可以通过定义窗口函数指标的同一个实体来访问。
任何指标都必须属于定义该指标的同一个表。
您无法指定汇总、窗口函数或子查询。

PARTITION BY EXCLUDING dimensions

按查询的 SEMANTIC_VIEW 子句中指定的所有维度将行分成分区，由 dimensions 指定的维度除外。

dimensions 必须仅引用可从定义窗口函数指标的实体访问的维度。

例如，假设您将维度 table_1.dimension_1 排除在分区之外：

CREATE SEMANTIC VIEW sv
  ...
  METRICS (
    table_1.metric_2 AS SUM(table_1.metric_1) OVER
      (PARTITION BY EXCLUDING table_l.dimension_1 ORDER BY table_1.dimension_2)
  )
  ...

假设您运行了指定维度 table_1.dimension_1 的查询：

SELECT * FROM SEMANTIC VIEW(
  sv
  METRICS (
    table_1.metric_2
  )
  DIMENSIONS (
    table_1.dimension_1,
    table_1.dimension_2,
    table_1.dimension_3
  );

在查询中，指标 table_1.metric_2 的评估结果为：

SUM(table_1.metric_1) OVER (
  PARTITION BY table_1.dimension_2, table_1.dimension_3
  ORDER BY table_1.dimension_2
)

注意如何将 table_1.dimension_1 从 PARTITION BY 子句中排除。

备注

在语义视图中，您不能在指标定义之外使用 EXCLUDING。EXCLUDING 在任何其他上下文的窗口函数调用中都不受支持。

ORDER BY exprs_using_dimensions_or_metrics [ ASC | DESC ] [ NULLS FIRST | LAST ] [, ... ]

对每个分区内的行进行排序。在 SQL 表达式中：

表达式中的任何维度都必须可以通过定义窗口函数指标的同一个实体来访问。
任何指标都必须属于定义该指标的同一个表。
您无法指定汇总、窗口函数或子查询。

windowFrameClause

请参阅窗口函数的语法和用法。

有关窗口函数参数和示例的更多信息，请参阅定义和查询窗口函数指标。

访问控制要求¶

用于执行此操作的角色必须至少具有以下权限：


权限	对象	备注
CREATE SEMANTIC VIEW	架构	创建新的语义视图时需要。
SELECT	表、视图	语义视图定义中使用的任何表和/或视图都需要。

Operating on an object in a schema requires at least one privilege on the parent database and at least one privilege on the parent schema.

有关创建具有指定权限集的自定义角色的说明，请参阅创建自定义角色。

有关对安全对象执行 SQL 操作的相应角色和权限授予的一般信息，请参阅访问控制概述。

使用说明¶

语义视图必须有效且必须遵循 Snowflake 如何验证语义视图中描述的规则。
关于元数据：

注意

客户应确保在使用 Snowflake 服务时，不会将个人数据（用户对象除外）、敏感数据、出口管制数据或其他受监管数据作为元数据输入。有关更多信息，请参阅 Snowflake 中的元数据字段。
CREATE OR REPLACE <object> 语句是原子的。也就是说，当对象被替换时，旧对象将被删除，新对象将在单个事务中创建。

示例¶

请参阅 Creating a semantic view by using the CREATE SEMANTIC VIEW command。