如何创建IS_MEMOIZABLE为'Y'的Snowflake用户自定义函数？

背景回顾 #

在Snowflake中，可通过查询INFORMATION_SCHEMA.FUNCTIONS或执行SHOW FUNCTIONS语句查看函数的IS_MEMOIZABLE属性，该属性用于标识函数是否支持记忆化（缓存重复输入的计算结果以提升性能）。所有内置函数的该属性均为'N'，可通过以下语句验证：

SHOW BUILTIN FUNCTIONS;

SELECT "is_memoizable", *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "is_memoizable" <> 'N';
-- 执行后返回0行，说明无内置函数支持记忆化

创建可记忆化自定义函数的要求 #

要让用户自定义函数的IS_MEMOIZABLE属性为'Y'，需满足以下条件：

• 必须定义为IMMUTABLE类型：记忆化的核心前提是函数相同输入永远返回相同输出，且不依赖任何外部可变状态（比如当前时间、随机数、会话变量等）。只有标记为IMMUTABLE的函数才符合这个要求。
• 无需额外关键字：只要函数符合IMMUTABLE的特性，Snowflake会自动将IS_MEMOIZABLE设为'Y'，不需要额外添加专门的记忆化相关关键字。
• 适用的函数类型：
  • 原生SQL标量/表函数，只要声明为IMMUTABLE
  • Python、Java等外部UDF，同样需要满足IMMUTABLE的特性（函数逻辑不依赖外部可变资源，相同输入返回相同结果）

示例：创建并验证可记忆化UDF #

-- 创建一个不可变的SQL标量函数
CREATE OR REPLACE FUNCTION SQUARE_INT(input_num INT)
RETURNS INT
IMMUTABLE
AS $$
  input_num * input_num
$$;

-- 查询该函数的IS_MEMOIZABLE属性
SELECT IS_MEMOIZABLE, FUNCTION_NAME
FROM INFORMATION_SCHEMA.FUNCTIONS
WHERE FUNCTION_NAME = 'SQUARE_INT';
-- 结果中IS_MEMOIZABLE列会显示为'Y'

注意事项 #

如果函数被定义为VOLATILE（结果随每次调用可能变化）或STABLE（结果在单个会话内不变，跨会话可能变化），则IS_MEMOIZABLE会默认是'N'，因为这两类函数的结果不具备可缓存的可靠性。

内容的提问来源于stack exchange，提问作者Lukasz Szozda