SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML¶

基于 YAML 格式的语义模型规范创建语义视图，或验证该规范能否用于创建语义视图。

存储过程使用 YAML 规范中的名称作为语义视图的名称。

如果已经存在同名的语义视图，则存储过程会尝试替换该语义视图并复制该语义视图中的授权。这与运行 CREATE OR REPLACE SEMANTIC VIEW ... COPY GRANTS 的效果相同。

另请参阅：: SYSTEM$READ_YAML_FROM_SEMANTIC_VIEW、CREATE SEMANTIC VIEW

语法¶

SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  '<fully_qualified_schema_name>' ,
  '<yaml_specification>' ,
  [ <verify_only> ]
)

Copy

实参¶

必填：

'fully_qualified_schema_name'

要在其中创建语义视图的架构的完全限定名称。

必须使用数据库名称（例如 my_db.my_schema）限定架构名称。否则，会发生错误。

'yaml_specification'

YAML 格式的语义模型规范。

若规范中包含引号、反斜杠或换行符，可为该实参使用美元引号字符串常量。。

可选：

verify_only

如果为 TRUE，则验证是否可使用 :samp:`'{yaml_specification}'` 指定的语义模型创建语义视图。

在尝试创建语义视图之前，您可以指定此值来验证是否可以从模型中创建语义视图。

默认：FALSE

返回¶

返回一个 VARCHAR类型的值，其中包含创建语义视图或验证该视图能否被创建的操作状态。

如果存储过程未能成功创建语义视图或验证该视图能否被创建，则会抛出异常。

访问控制要求¶

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

权限	对象	备注
CREATE SEMANTIC VIEW	架构	创建新的语义视图时需要。
SELECT	表、视图	语义视图定义中使用的任何表和/或视图都需要。
OWNERSHIP	同名的现有语义视图。	如果已经存在同名的语义视图，则存储过程会尝试替换该语义视图。要替换现有的语义视图，必须使用已被授予 OWNERSHIP 权限的角色。 OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

权限

对象

备注

CREATE SEMANTIC VIEW

架构

创建新的语义视图时需要。

SELECT

表、视图

语义视图定义中使用的任何表和/或视图都需要。

OWNERSHIP

同名的现有语义视图。

如果已经存在同名的语义视图，则存储过程会尝试替换该语义视图。要替换现有的语义视图，必须使用已被授予 OWNERSHIP 权限的角色。

OWNERSHIP is a special privilege on an object that is automatically granted to the role that created the object, but can also be transferred using the GRANT OWNERSHIP command to a different role by the owning role (or any role with the MANAGE GRANTS privilege).

要对架构中的任何对象执行操作，需要对父数据库和架构的 USAGE 权限。请注意，如果某个角色获授某个架构的任意权限，该角色便能够解析该架构。例如，若某角色被授予 CREATE 权限，则可以在该架构上创建对象，而无需同时被授予该架构的 USAGE 权限。

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

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

使用说明¶

如果数据库或模式的名称是 :ref:`双引号标识符 <label-delimited_identifier>`（例如名称中包含空格），则必须在名称外保留双引号。例如：

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  '"my database"."my schema"',
  ...
);

Copy

示例¶

以下示例验证了您是否可以在 YAML 中使用给定的语义模型规范，在数据库 my_db 和架构 tpch_analysis 中创建名为 my_schema 的语义视图：

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'my_db.my_schema',
  $$
  name: TPCH_REV_ANALYSIS
  description: Semantic view for revenue analysis
  tables:
    - name: CUSTOMERS
      description: Main table for customer data
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: CUSTOMER
      primary_key:
        columns:
          - C_CUSTKEY
      dimensions:
        - name: CUSTOMER_NAME
          synonyms:
            - customer name
          description: Name of the customer
          expr: customers.c_name
          data_type: VARCHAR(25)
        - name: C_CUSTKEY
          expr: C_CUSTKEY
          data_type: VARCHAR(134217728)
      metrics:
        - name: CUSTOMER_COUNT
          description: Count of number of customers
          expr: COUNT(c_custkey)
    - name: LINE_ITEMS
      description: Line items in orders
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: LINEITEM
      primary_key:
        columns:
          - L_ORDERKEY
          - L_LINENUMBER
      dimensions:
        - name: L_ORDERKEY
          expr: L_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: L_LINENUMBER
          expr: L_LINENUMBER
          data_type: VARCHAR(134217728)
      facts:
        - name: DISCOUNTED_PRICE
          description: Extended price after discount
          expr: l_extendedprice * (1 - l_discount)
          data_type: "NUMBER(25,4)"
        - name: LINE_ITEM_ID
          expr: "CONCAT(l_orderkey, '-', l_linenumber)"
          data_type: VARCHAR(134217728)
    - name: ORDERS
      synonyms:
        - sales orders
      description: All orders table for the sales domain
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: ORDERS
      primary_key:
        columns:
          - O_ORDERKEY
      dimensions:
        - name: ORDER_DATE
          description: Date when the order was placed
          expr: o_orderdate
          data_type: DATE
        - name: ORDER_YEAR
          description: Year when the order was placed
          expr: YEAR(o_orderdate)
          data_type: "NUMBER(4,0)"
        - name: O_ORDERKEY
          expr: O_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: O_CUSTKEY
          expr: O_CUSTKEY
          data_type: VARCHAR(134217728)
      facts:
        - name: COUNT_LINE_ITEMS
          expr: COUNT(line_items.line_item_id)
          data_type: "NUMBER(18,0)"
      metrics:
        - name: AVERAGE_LINE_ITEMS_PER_ORDER
          description: Average number of line items per order
          expr: AVG(orders.count_line_items)
        - name: ORDER_AVERAGE_VALUE
          description: Average order value across all orders
          expr: AVG(orders.o_totalprice)
  relationships:
    - name: LINE_ITEM_TO_ORDERS
      left_table: LINE_ITEMS
      right_table: ORDERS
      relationship_columns:
        - left_column: L_ORDERKEY
          right_column: O_ORDERKEY
      relationship_type: many_to_one
    - name: ORDERS_TO_CUSTOMERS
      left_table: ORDERS
      right_table: CUSTOMERS
      relationship_columns:
        - left_column: O_CUSTKEY
          right_column: C_CUSTKEY
      relationship_type: many_to_one
  $$,
TRUE);

Copy

如果规范有效，则存储过程返回以下消息：

+----------------------------------------------------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML                                            |
|----------------------------------------------------------------------------------|
| YAML file is valid for creating a semantic view. No object has been created yet. |
+----------------------------------------------------------------------------------+

如果 YAML 语法无效，则存储过程会引发异常。例如，如果缺少冒号：

relationships
  - name: LINE_ITEM_TO_ORDERS

Copy

存储过程会抛出异常，表示 YAML 语法无效：

392400 (22023): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  Invalid semantic model YAML: while scanning a simple key
   in 'reader', line 90, column 3:
        relationships
        ^
  could not find expected ':'
   in 'reader', line 91, column 11:
          - name: LINE_ITEM_TO_ORDERS
                ^

如果规范引用的物理表不存在，则存储过程会引发异常：

base_table:
  database: SNOWFLAKE_SAMPLE_DATA
  schema: TPCH_SF1
  table: NONEXISTENT

Copy

002003 (42S02): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  SQL compilation error:
  Table 'SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NONEXISTENT' does not exist or not authorized.

同样，如果规范引用的主键列不存在，则存储过程会引发异常：

primary_key:
  columns:
    - NONEXISTENT

Copy

000904 (42000): Uncaught exception of type 'EXPRESSION_ERROR' on line 3 at position 23 :
  SQL compilation error: error line 0 at position -1
  invalid identifier 'NONEXISTENT'

以下示例在数据库 my_db 和架构 tpch_analysis 中创建了一个名为 my_schema 的语义视图：

CALL SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML(
  'my_db.my_schema',
  $$
  name: TPCH_REV_ANALYSIS
  description: Semantic view for revenue analysis
  tables:
    - name: CUSTOMERS
      description: Main table for customer data
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: CUSTOMER
      primary_key:
        columns:
          - C_CUSTKEY
      dimensions:
        - name: CUSTOMER_NAME
          synonyms:
            - customer name
          description: Name of the customer
          expr: customers.c_name
          data_type: VARCHAR(25)
        - name: C_CUSTKEY
          expr: C_CUSTKEY
          data_type: VARCHAR(134217728)
      metrics:
        - name: CUSTOMER_COUNT
          description: Count of number of customers
          expr: COUNT(c_custkey)
    - name: LINE_ITEMS
      description: Line items in orders
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: LINEITEM
      primary_key:
        columns:
          - L_ORDERKEY
          - L_LINENUMBER
      dimensions:
        - name: L_ORDERKEY
          expr: L_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: L_LINENUMBER
          expr: L_LINENUMBER
          data_type: VARCHAR(134217728)
      facts:
        - name: DISCOUNTED_PRICE
          description: Extended price after discount
          expr: l_extendedprice * (1 - l_discount)
          data_type: "NUMBER(25,4)"
        - name: LINE_ITEM_ID
          expr: "CONCAT(l_orderkey, '-', l_linenumber)"
          data_type: VARCHAR(134217728)
    - name: ORDERS
      synonyms:
        - sales orders
      description: All orders table for the sales domain
      base_table:
        database: SNOWFLAKE_SAMPLE_DATA
        schema: TPCH_SF1
        table: ORDERS
      primary_key:
        columns:
          - O_ORDERKEY
      dimensions:
        - name: ORDER_DATE
          description: Date when the order was placed
          expr: o_orderdate
          data_type: DATE
        - name: ORDER_YEAR
          description: Year when the order was placed
          expr: YEAR(o_orderdate)
          data_type: "NUMBER(4,0)"
        - name: O_ORDERKEY
          expr: O_ORDERKEY
          data_type: VARCHAR(134217728)
        - name: O_CUSTKEY
          expr: O_CUSTKEY
          data_type: VARCHAR(134217728)
      facts:
        - name: COUNT_LINE_ITEMS
          expr: COUNT(line_items.line_item_id)
          data_type: "NUMBER(18,0)"
      metrics:
        - name: AVERAGE_LINE_ITEMS_PER_ORDER
          description: Average number of line items per order
          expr: AVG(orders.count_line_items)
        - name: ORDER_AVERAGE_VALUE
          description: Average order value across all orders
          expr: AVG(orders.o_totalprice)
  relationships:
    - name: LINE_ITEM_TO_ORDERS
      left_table: LINE_ITEMS
      right_table: ORDERS
      relationship_columns:
        - left_column: L_ORDERKEY
          right_column: O_ORDERKEY
      relationship_type: many_to_one
    - name: ORDERS_TO_CUSTOMERS
      left_table: ORDERS
      right_table: CUSTOMERS
      relationship_columns:
        - left_column: O_CUSTKEY
          right_column: C_CUSTKEY
      relationship_type: many_to_one
  $$
);

Copy

+-----------------------------------------+
| SYSTEM$CREATE_SEMANTIC_VIEW_FROM_YAML   |
|-----------------------------------------|
| Semantic view was successfully created. |
+-----------------------------------------+