类别：

数据指标函数

FRESHNESS（系统数据指标函数）

Returns how much time in seconds has elapsed since a table was last modified.

When a column argument is specified, the time period is calculated by comparing the current run of the function with the maximum value of a timestamp column. If the scheduled time to run the function is different than the time it actually ran, then the scheduled time is used for the comparison.

When no column is specified, the time period is calculated by comparing the current run of the function with the last time a DML command acted on the table. If the scheduled time to run the function is different than the time it actually ran, then the scheduled time is used for the comparison.

本主题提供直接调用该函数的语法。要了解如何将该函数与表或视图关联以使其定期运行，请参阅 关联 DMF 以自动执行数据质量检查。

语法

SNOWFLAKE.CORE.FRESHNESS( [ <query> ] )
Copy

实参

query

If specified, the query must project a single timestamp column.

If you don't want to specify a column, you must associate the function with a table rather than call it directly.

允许的数据类型

query 投射的列必须具有以下数据类型之一：

  • DATE

  • TIMESTAMP_LTZ

  • TIMESTAMP_TZ

返回

该函数返回一个数据类型为 NUMBER 的标量值。

访问控制要求

Associating and running a system DMF requires the USAGE privilege on the system DMF. You can grant the SNOWFLAKE.DATA_METRIC_USER database role to give users the USAGE privilege on all system DMFs. For more information, see Grant the USAGE privilege on system DMFs.

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

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

Usage notes

  • You must specify a column argument if you want to associate this function with a view or external table.

  • This function can be called directly only if you specify a query that projects a timestamp column. If you want to associate the function with a table or view so it runs at regular intervals with or without a column argument, see 关联 DMF 以自动执行数据质量检查.

示例

Associate the function with the table t1 to determine how long it's been since the last DML operation on the table:

ALTER TABLE t1
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.FRESHNESS on ();
Copy

Call the function directly to determine the freshness of the data, 300 seconds or 5 minutes, in the table by measuring the TIMESTAMP column:

SELECT SNOWFLAKE.CORE.FRESHNESS(
  SELECT
    timestamp
  FROM hr.tables.empl_info
) < 300;
Copy
+---------------------------------------------------------------------+
| SNOWFLAKE.CORE.FRESHNESS(SELECT timestamp FROM hr.tables.empl_info) |
+---------------------------------------------------------------------+
| True                                                                |
+---------------------------------------------------------------------+
语言: 中文