CREATE DATA METRIC FUNCTION¶

在当前或指定架构中创建新的数据指标函数 (DMF)，或替换现有的数据指标函数。

After creating a DMF, apply it to a table column using an ALTER TABLE … ALTER COLUMN command or a view column using the ALTER VIEW command.

该命令支持以下变体：

CREATE OR ALTER DATA METRIC FUNCTION: Creates a new data metric function if it doesn’t exist or alters an existing data metric function.

语法

CREATE [ OR REPLACE ] [ SECURE ] DATA METRIC FUNCTION [ IF NOT EXISTS ] <name>
  ( <table_arg> TABLE( <col_arg> <data_type> [ , ... ] )
    [ , <table_arg> TABLE( <col_arg> <data_type> [ , ... ] ) ] )
  RETURNS NUMBER [ [ NOT ] NULL ]
  [ LANGUAGE SQL ]
  [ COMMENT = '<string_literal>' ]
  AS
  '<expression>'

变体语法

CREATE OR ALTER DATA METRIC FUNCTION¶

Creates a new data metric function if it doesn’t already exist, or transforms an existing data metric function into the function defined in the statement. A CREATE OR ALTER DATA METRIC FUNCTION statement follows the syntax rules of a CREATE DATA METRIC FUNCTION statement and has the same limitations as an ALTER FUNCTION (DMF) statement.

与 CREATE OR REPLACE DATA METRIC FUNCTION 命令不同，CREATE OR ALTER 命令会更新对象，而无需删除和重新创建对象。

支持的功能更改包括对 COMMENT 属性的更改。

For more information, see CREATE OR ALTER DATA METRIC FUNCTION 使用说明.

CREATE [ OR ALTER ] DATA METRIC FUNCTION ...

必填参数

name

DMF 的标识符；对于架构必须是唯一的。

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.

( table_arg TABLE( col_arg data_type [ , ... ] ) [ , table_arg TABLE( col_arg data_type [ , ... ] ) ] )

DMF 的签名，用作表达式的输入。

您必须指定：

An argument name for each table (table_arg).
For each table, an argument name for at least one column, along with its data type (col_arg data_type).

您可以选择为其他列及其数据类型指定实参。列必须在同一个表中，不能引用其他表。

RETURNS NUMBER

函数输出的数据类型。

数据类型只能是 NUMBER。

AS expression

SQL expression that determines the output of the function. The expression must be deterministic and return a scalar value. The expression can reference other table objects, such as by using a WITH clause or a WHERE clause.

The delimiters around the expression can be either single quotes or a pair of dollar signs. Using $$ as the delimiter makes it easier to write expressions that contain single quotes.

If the delimiter for the expression is the single quote character, then any single quotes within expression (for example, string literals) must be escaped by single quotes.

The expression does not support the following:

Using nondeterministic functions (for example, CURRENT_TIME).
引用依赖于 UDF 或 UDTF 的对象。
返回非标量输出。

可选参数

SECURE

Specifies that the data metric function is secure. For more information, see Protecting Sensitive Information with Secure UDFs and Stored Procedures.

LANGUAGE SQL

指定用于编写表达式的语言。

SQL 是唯一支持的语言。

COMMENT = 'string_literal'

DMF 的注释。

访问控制要求

Privilege	Object	Notes
CREATE DATA METRIC FUNCTION	Schema	该权限仅允许在架构中创建数据指标函数。如果您要允许创建用户定义函数（如 SQL 或 Java UDFs），则角色必须具有 CREATE FUNCTION 权限。

Privilege

Object

Notes

CREATE DATA METRIC FUNCTION

Schema

该权限仅允许在架构中创建数据指标函数。

如果您要允许创建用户定义函数（如 SQL 或 Java UDFs），则角色必须具有 CREATE FUNCTION 权限。

一般使用说明

If you want to update an existing data metric function and need to see the current definition of the function, run the DESCRIBE FUNCTION (DMF) command or call the GET_DDL function.
关于元数据：

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.

The OR REPLACE and IF NOT EXISTS clauses are mutually exclusive. They can’t both be used in the same statement.
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.

CREATE OR ALTER DATA METRIC FUNCTION 使用说明¶

You can’t modify the DMF’s arguments. Specifying new arguments creates a new overloaded DMF.

示例：单表实参

Create a DMF that calls the COUNT function to return the total number of rows that have positive numbers in three columns of the table:

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;

示例：多表实参

返回一个表中某列的值在另一个表的相应列中没有对应值的记录数：

CREATE OR REPLACE DATA METRIC FUNCTION governance.dmfs.referential_check(
  arg_t1 TABLE (arg_c1 INT), arg_t2 TABLE (arg_c2 INT))
RETURNS NUMBER
AS
$$
  SELECT
    COUNT(*)
    FROM arg_t1
  WHERE
    arg_c1 NOT IN (SELECT arg_c2 FROM arg_t2)
$$;

For an example that uses this DMF to validate referential integrity, see Example: Using multiple table arguments to perform referential checks.

示例：使用 CREATE OR ALTER DATA METRIC FUNCTION 命令更改数据指标函数¶

更改上面示例中创建的单表数据指标函数，以设置安全性和注释。

CREATE OR ALTER SECURE DATA METRIC FUNCTION governance.dmfs.count_positive_numbers(
  arg_t TABLE(
    arg_c1 NUMBER,
    arg_c2 NUMBER,
    arg_c3 NUMBER
  )
)
RETURNS NUMBER
COMMENT = "count positive numbers"
AS
$$
  SELECT
    COUNT(*)
  FROM arg_t
  WHERE
    arg_c1>0
    AND arg_c2>0
    AND arg_c3>0
$$;