基于Explain Plan动态调整Snowflake Warehouse的存储过程求助

以下是实现需求的完整存储过程示例，包含执行计划解析、仓库大小判断、自动调整及目标查询执行的完整逻辑：

CREATE OR REPLACE PROCEDURE ADJUST_WH_AND_RUN_QUERY(p_target_query VARCHAR, p_warehouse_name VARCHAR)
RETURNS TABLE()
LANGUAGE SQL
AS
$$
DECLARE
    v_explain_sql VARCHAR;
    v_explain_result RESULTSET;
    v_assigned_bytes NUMBER;
    v_current_wh_size VARCHAR;
    v_target_wh_size VARCHAR;
    v_run_query_result RESULTSET;
BEGIN
    -- 构建EXPLAIN语句，获取目标查询的执行计划
    v_explain_sql := 'EXPLAIN USING JSON ' || p_target_query;
    
    -- 执行EXPLAIN并存储结果集
    v_explain_result := EXECUTE IMMEDIATE :v_explain_sql;
    
    -- 从执行计划JSON中提取所有节点的assigned_bytes并求和
    SELECT SUM(TO_NUMBER(GET_PATH(PARSE_JSON("PLAN"), '$.assigned_bytes')))
    INTO v_assigned_bytes
    FROM TABLE(v_explain_result);
    
    -- 根据总assigned_bytes设定目标仓库大小（可根据业务需求调整阈值）
    IF v_assigned_bytes < 1000000000 THEN -- 小于1GB
        v_target_wh_size := 'X_SMALL';
    ELSIF v_assigned_bytes < 5000000000 THEN -- 1GB到5GB之间
        v_target_wh_size := 'SMALL';
    ELSIF v_assigned_bytes < 20000000000 THEN -- 5GB到20GB之间
        v_target_wh_size := 'MEDIUM';
    ELSE -- 大于20GB
        v_target_wh_size := 'LARGE';
    END IF;
    
    -- 查询当前仓库的大小
    SELECT "SIZE" INTO v_current_wh_size
    FROM INFORMATION_SCHEMA.WAREHOUSES
    WHERE WAREHOUSE_NAME = :p_warehouse_name;
    
    -- 仅当当前大小与目标不一致时调整仓库
    IF v_current_wh_size != v_target_wh_size THEN
        EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || p_warehouse_name || ' SET SIZE = ' || v_target_wh_size;
        -- 可选：确保仓库处于可用状态（若之前是暂停状态）
        EXECUTE IMMEDIATE 'ALTER WAREHOUSE ' || p_warehouse_name || ' RESUME';
    END IF;
    
    -- 执行目标查询并返回结果
    v_run_query_result := EXECUTE IMMEDIATE :p_target_query;
    RETURN TABLE(v_run_query_result);
END;
$$;

关键说明 #

1. 参数定义

   • p_target_query：需要执行的目标SQL语句（无需包含EXPLAIN）
   • p_warehouse_name：需要调整大小的Snowflake仓库名称

2. 执行计划解析
   通过PARSE_JSON解析EXPLAIN返回的JSON格式执行计划，使用GET_PATH提取每个节点的assigned_bytes，再求和得到预估处理的总数据量。

3. 仓库大小判断逻辑
   示例中设置了4档阈值，你可以根据自身的仓库性能、成本预算调整阈值和对应的仓库规格（如加入XLARGE、XXLARGE等）。

4. 仓库调整优化
   先查询当前仓库大小，仅当需要调整时才执行ALTER WAREHOUSE操作，避免无意义的状态变更。

使用示例 #

-- 调用存储过程，传入目标查询和仓库名称
CALL ADJUST_WH_AND_RUN_QUERY(
    'SELECT * FROM YOUR_SCHEMA.YOUR_TABLE WHERE CREATED_DATE >= DATEADD(day, -7, CURRENT_DATE())',
    'YOUR_WH_NAME'
);

扩展说明 #

• 若要基于分区数调整仓库，可将assigned_bytes的提取逻辑替换为提取$.partitions_total字段，再根据分区数量设定阈值。
• 存储过程执行需要具备ALTER WAREHOUSE权限以及目标查询的执行权限。
• assigned_bytes是Snowflake的预估数据量，实际执行时可能因数据分布等因素有差异，可根据实际运行情况优化阈值。

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