Snowflake SQL表值UDF转等效JavaScript UDF的代码修改方法

核心调整项 #

• 调整函数声明的语言属性：将原定义中的LANGUAGE SQL修改为LANGUAGE JAVASCRIPT，可额外指定JS运行时版本保证兼容性，比如加RUNTIME_VERSION = '18'。
• 替换函数体逻辑：不能直接在AS块中写SQL语句，需要调用Snowflake JS UDF内置的snowflake.createStatement()API执行原有聚合查询，通过execute()方法获取结果集。
• 适配表值输出逻辑：遍历查询返回的结果集，将每一行的字段按原RETURNS TABLE定义的字段名映射为JS对象返回，保证输出字段顺序、类型和原UDF完全一致。
• 保留原有聚合逻辑：原有SELECT语句的聚合计算、精度处理（比如ROUND保留8位小数）不需要修改，直接作为查询字符串传入JS执行即可，避免在JS中二次计算产生精度误差。
• 冗余逻辑兼容：原SQL中的ORDER BY total_CUSTOMERS desc在单聚合结果行的场景下无实际作用，可保留或删除，不影响最终输出。

转换后完整代码示例 #

CREATE OR REPLACE FUNCTION "TEST_UDF"()
RETURNS TABLE ("TOTAL_CUSTOMERS" NUMBER(38,0), "COUNTRIES" NUMBER(38,0), "US_STATES" NUMBER(38,0), "CITIES" NUMBER(38,0), "MIN_AGE" VARCHAR(16777216), "MAX_AGE" VARCHAR(16777216), "WOMEN" NUMBER(38,0), "MEN" NUMBER(38,0), "OTHER" NUMBER(38,0))
LANGUAGE JAVASCRIPT
RUNTIME_VERSION = '18'
AS $$
// 执行原有聚合SQL
const query = `
    select 
    COUNT(DISTINCT(HUX_ID)) AS TOTAL_CUSTOMERS,
    COUNT(DISTINCT(COUNTRY)) AS COUNTRIES,
    COUNT(DISTINCT(STATE)) AS US_STATES,
    COUNT(DISTINCT(CITY)) AS CITIES,
    MIN(AGE) AS MIN_AGE,
    MAX(AGE) AS MAX_AGE,
    ROUND(COUNT(CASE WHEN GENDER = 'F' THEN 1 END)* 100 / COUNT(GENDER),8) AS WOMEN,
    ROUND(COUNT(CASE WHEN GENDER = 'M' THEN 1 END)* 100 / COUNT(GENDER),8) AS MEN,
    ROUND(COUNT(CASE WHEN GENDER = 'U' THEN 1 END)* 100 / COUNT(GENDER),8) AS OTHER
    from DETAIL_CUSTOMER
`;
const stmt = snowflake.createStatement({sqlText: query});
const rs = stmt.execute();

// 遍历结果集返回匹配结构的行
while (rs.next()) {
    return {
        TOTAL_CUSTOMERS: rs.getColumnValue('TOTAL_CUSTOMERS'),
        COUNTRIES: rs.getColumnValue('COUNTRIES'),
        US_STATES: rs.getColumnValue('US_STATES'),
        CITIES: rs.getColumnValue('CITIES'),
        MIN_AGE: rs.getColumnValue('MIN_AGE'),
        MAX_AGE: rs.getColumnValue('MAX_AGE'),
        WOMEN: rs.getColumnValue('WOMEN'),
        MEN: rs.getColumnValue('MEN'),
        OTHER: rs.getColumnValue('OTHER')
    };
}
$$;

内容的提问来源于stack exchange，提问作者rita mishra