如何在Snowflake中筛选时间戳列数据小于30天的指定前缀表列表

要筛选出指定前缀基表中timestamp列存在早于30天数据的表，需要通过动态SQL批量验证每个表的满足条件的记录，以下是两种可行实现方式：

方式1：生成验证语句手动执行 #

先生成针对每个目标表的验证SQL，再统一执行获取结果：

-- 生成所有目标表的验证语句
WITH target_tables AS (
    -- 先筛选存在timestamp列的指定前缀基表
    SELECT t.table_name
    FROM information_schema.tables t
    JOIN information_schema.columns c 
        ON t.table_name = c.table_name 
        AND t.table_schema = c.table_schema 
        AND t.table_catalog = c.table_catalog
    WHERE t.table_type = 'BASE TABLE'
      AND t.table_name LIKE UPPER('DIM_STUDENT_NAME%')
      AND c.column_name = 'TIMESTAMP'
    GROUP BY t.table_name
)
SELECT 'SELECT ''' || table_name || ''' AS qualifying_table WHERE EXISTS (SELECT 1 FROM ' || table_name || ' WHERE timestamp < DATEADD(DAY, -30, CURRENT_TIMESTAMP))'
FROM target_tables;

执行上述语句后，会得到一系列单个表的验证SQL，将这些SQL拼接后加上UNION ALL执行，即可得到所有符合条件的表名。

方式2：用存储过程自动批量验证 #

通过Snowflake存储过程自动遍历所有目标表，执行验证并返回结果：

-- 创建存储过程
CREATE OR REPLACE PROCEDURE find_tables_with_old_timestamp()
RETURNS TABLE(qualifying_table VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
    -- 游标遍历所有符合条件的表
    table_cursor CURSOR FOR
        SELECT t.table_name
        FROM information_schema.tables t
        JOIN information_schema.columns c 
            ON t.table_name = c.table_name 
            AND t.table_schema = c.table_schema 
            AND t.table_catalog = c.table_catalog
        WHERE t.table_type = 'BASE TABLE'
          AND t.table_name LIKE UPPER('DIM_STUDENT_NAME%')
          AND c.column_name = 'TIMESTAMP'
        GROUP BY t.table_name;
    v_table_name VARCHAR;
    v_sql VARCHAR;
    v_result RESULTSET;
    v_output TABLE(qualifying_table VARCHAR);
BEGIN
    -- 逐个表验证
    FOR table_rec IN table_cursor DO
        v_table_name := table_rec.table_name;
        -- 生成当前表的验证SQL
        v_sql := 'SELECT ''' || v_table_name || ''' AS qualifying_table WHERE EXISTS (SELECT 1 FROM ' || v_table_name || ' WHERE timestamp < DATEADD(DAY, -30, CURRENT_TIMESTAMP))';
        v_result := (EXECUTE IMMEDIATE v_sql);
        -- 收集符合条件的表名
        v_output := v_output UNION ALL (SELECT * FROM TABLE(v_result));
    END FOR;
    RETURN TABLE(v_output);
END;
$$;

-- 调用存储过程获取结果
CALL find_tables_with_old_timestamp();

注意事项 #

• 确保执行账号对所有目标表有SELECT权限，否则会出现权限错误
• DATEADD(DAY, -30, CURRENT_TIMESTAMP)用于计算30天前的时间，可根据需求调整时间单位（如HOUR、MONTH）或偏移量

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