如何在Snowflake连接中检测表列名相等并触发警告？

问题场景 #

用户提交的Snowflake查询中偶尔会出现连接条件里同一表的同一列自相等的情况（如table_a.col_1 = table_a.col_1），这种无意义的连接会引发数据扇出，大幅降低查询性能。示例问题查询如下：

select
    table_a.*
from table_a
left outer join table_b
    on table_a.col_1 = table_a.col_1

程序化触发警告的方法 #

结合Snowflake Scripting的异常处理能力，可通过以下几种方式实现程序化检测与告警：

1. 查询文本预校验脚本

编写Snowflake Scripting脚本，接收待执行的查询文本，通过正则匹配检测自相等的连接条件，匹配到则抛出警告：

DECLARE
    -- 待校验的查询文本
    target_query STRING := 'select table_a.* from table_a left outer join table_b on table_a.col_1 = table_a.col_1';
    -- 匹配自相等连接条件的正则（忽略大小写、支持换行）
    fanout_pattern STRING := 'on\\s+(\\w+\\.\\w+)\\s*=\\s*\\1';
BEGIN
    -- 检查查询文本中是否存在目标模式
    IF REGEXP_INSTR(target_query, fanout_pattern, 1, 1, 0, 'i') > 0 THEN
        RAISE WARNING '检测到无效连接条件：同一表列自相等，可能引发数据扇出，请检查查询逻辑';
    END IF;
    -- 若校验通过，执行查询
    -- EXECUTE IMMEDIATE target_query;
END;

注：可根据实际查询格式优化正则表达式，比如添加s修饰符处理换行，或扩展匹配带引号的列名、复杂表别名等场景。

2. 封装存储过程做前置校验

将查询校验逻辑封装成存储过程，在提交查询前调用存储过程传入查询文本，返回校验结果并触发警告：

CREATE OR REPLACE PROCEDURE CHECK_FANOUT_CONDITION(query_str STRING)
RETURNS BOOLEAN
LANGUAGE SQL
AS
$$
DECLARE
    pattern STRING := 'on\\s+(\\w+\\.\\w+)\\s*=\\s*\\1';
BEGIN
    IF REGEXP_INSTR(query_str, pattern, 1, 1, 0, 'is') > 0 THEN
        RAISE WARNING '风险查询：存在自相等连接条件，可能引发数据扇出';
        RETURN FALSE;
    ELSE
        RETURN TRUE;
    END IF;
END;
$$;

-- 调用示例
CALL CHECK_FANOUT_CONDITION('select table_a.* from table_a left outer join table_b on table_a.col_1 = table_a.col_1');
-- 若返回TRUE，再执行查询

3. 定期扫描历史查询告警

通过Snowflake的QUERY_HISTORY视图定期扫描近期执行的查询，检测是否存在此类问题，再通过ALERT对象触发通知：

CREATE OR REPLACE ALERT CHECK_FANOUT_QUERIES
WAREHOUSE = YOUR_WH
SCHEDULE = 'USING CRON 0 * * * * UTC'
IF EXISTS (
    SELECT 1
    FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        DATEADD('hour', -1, CURRENT_TIMESTAMP()), CURRENT_TIMESTAMP()
    ))
    WHERE REGEXP_INSTR(QUERY_TEXT, 'on\\s+(\\w+\\.\\w+)\\s*=\\s*\\1', 1, 1, 0, 'is') > 0
)
THEN
    EXECUTE IMMEDIATE $$
        RAISE WARNING '最近1小时内检测到存在自相等连接条件的查询，可能引发数据扇出';
        -- 可扩展发送邮件、团队协作工具通知等逻辑（需结合Snowflake集成功能）
    $$;
END IF;

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