Snowflake JavaScript存储过程SQL格式化的更优实现方式

问题描述 #

我在Snowflake中创建了JavaScript存储过程FIND_DUPLICATE_ROWS，用于识别指定表中的重复行并终止后续代码执行。该过程接收数据库名、模式名、表名和多个自然键（逗号分隔）作为参数，当前通过字符串拼接组装SQL语句，想寻求更简洁的SQL格式化方式。

原代码如下：

CREATE OR REPLACE PROCEDURE FIND_DUPLICATE_ROWS(DB_NAME STRING, SCHEMA_NAME STRING, TABLE_NAME STRING, NATURAL_KEYS STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // Split the Natural keys into an array and trim whitespaces
    var keysArray = NATURAL_KEYS.split(',').map(function(item) { return item.trim(); });

    // Format the Natural keys into comma separated string for the query
    var NaturalkeyString = keysArray.join(', ');

    // Assemble the SQL query to identify duplicate rows
    var sql_query = 
        'WITH duplicates AS (' +
            'SELECT ' + NaturalkeyString +
            ', ROW_NUMBER() OVER (PARTITION BY ' + NaturalkeyString + ' ORDER BY ' + NaturalkeyString + ') AS RowNumber ' +
            'FROM "' + DB_NAME + '"."' + SCHEMA_NAME + '"."' + TABLE_NAME + '"' +
        ') ' +
        'SELECT * FROM duplicates WHERE RowNumber > 1';

    var stmt = snowflake.createStatement({sqlText: sql_query});

    var result = stmt.execute();

    // Check if there are duplicate rows
    if(result.next()){
        throw 'ERROR: Duplicate rows found in the table ' + TABLE_NAME;
    }
    else{
        return 'No duplicate rows found in the table ' + TABLE_NAME;
    }
$$;

-- Test the Stored Procedure:
CALL FIND_DUPLICATE_ROWS('SUPA_DB', 'RAW', 'DIM_EMP_DETAILS', 'ID,EMP_NAME,ORG');

--If the "FIND_DUPLICATE_ROWS" stored procedure works the following code should NOT execute after calling "FIND_DUPLICATE_ROWS" stored procedure:
SELECT 7887;

优化方案 #

1. 用JavaScript模板字符串简化拼接 #

JavaScript的模板字符串（反引号`包裹）支持直接嵌入变量，无需反复拼接字符串和加号，代码可读性大幅提升：

CREATE OR REPLACE PROCEDURE FIND_DUPLICATE_ROWS(DB_NAME STRING, SCHEMA_NAME STRING, TABLE_NAME STRING, NATURAL_KEYS STRING)
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
    // 拆分并清洗自然键
    const keysArray = NATURAL_KEYS.split(',').map(item => item.trim());
    const naturalKeyString = keysArray.join(', ');

    // 模板字符串组装SQL，格式更清晰
    const sqlQuery = `
        WITH duplicates AS (
            SELECT ${naturalKeyString},
                   ROW_NUMBER() OVER (PARTITION BY ${naturalKeyString} ORDER BY ${naturalKeyString}) AS RowNumber
            FROM "${DB_NAME}"."${SCHEMA_NAME}"."${TABLE_NAME}"
        )
        SELECT * FROM duplicates WHERE RowNumber > 1
    `;

    const stmt = snowflake.createStatement({sqlText: sqlQuery});
    const result = stmt.execute();

    if(result.next()){
        throw `ERROR: Duplicate rows found in the table ${TABLE_NAME}`;
    } else {
        return `No duplicate rows found in the table ${TABLE_NAME}`;
    }
$$;

2. 用IDENTIFIER函数安全处理对象名 #

若数据库/模式/表名包含特殊字符，或想避免手动加引号的麻烦，可使用Snowflake的IDENTIFIER()函数动态引用对象，结合模板字符串更安全：

const sqlQuery = `
    WITH duplicates AS (
        SELECT ${naturalKeyString},
               ROW_NUMBER() OVER (PARTITION BY ${naturalKeyString} ORDER BY ${naturalKeyString}) AS RowNumber
        FROM IDENTIFIER('${DB_NAME}.${SCHEMA_NAME}.${TABLE_NAME}')
    )
    SELECT * FROM duplicates WHERE RowNumber > 1
`;

该方式会自动处理对象名的引号转义，减少手动拼接的错误风险。

3. 优化重复检测逻辑提升性能 #

原逻辑会查询所有重复行，但我们仅需判断是否存在重复，无需返回所有重复数据。改用EXISTS子查询，找到第一条重复即停止查询，性能更优：

const sqlQuery = `
    SELECT 1
    FROM "${DB_NAME}"."${SCHEMA_NAME}"."${TABLE_NAME}" t1
    WHERE EXISTS (
        SELECT 1
        FROM "${DB_NAME}"."${SCHEMA_NAME}"."${TABLE_NAME}" t2
        WHERE ${keysArray.map(key => `t2.${key} = t1.${key}`).join(' AND ')}
        AND t2.ROWID() <> t1.ROWID()
    )
    LIMIT 1
`;

通过ROWID()区分同一键值下的不同行，避免全表扫描生成ROW_NUMBER数据集。

总结 #

• 模板字符串是最直接的优化方式，让SQL拼接代码更易读易维护；
• IDENTIFIER()函数可安全处理特殊对象名，减少手动引号的错误；
• 改用EXISTS逻辑能大幅提升大表下的检测性能，无需生成完整重复行数据集。

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