[분석] Warehouse Info 프로시저 생성

-- 1. 모니터링 메타 테이블 생성
CREATE OR REPLACE TABLE COMM.WAREHOUSE_INFO (
      NAME              VARCHAR(255)                              COMMENT '웨어하우스 이름'
    , SIZE              VARCHAR(50)                               COMMENT '웨어하우스 크기'
)
COMMENT = 'Snowflake 웨어하우스 상태 모니터링 테이블 - SHOW WAREHOUSES 결과를 주기적으로 저장'
;


-- 2. AIRFLOW 웨어하우스만 모니터링하는 프로시저
CREATE OR REPLACE PROCEDURE COMM.REFRESH_WAREHOUSE_INFO()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN

    TRUNCATE TABLE COMM.WAREHOUSE_INFO;
    
    -- AIRFLOW 웨어하우스만 조회
    SHOW WAREHOUSES;
    
    -- 결과를 모니터링 테이블에 삽입
    INSERT INTO COMM.WAREHOUSE_INFO (
          NAME
     , SIZE
    )   
    SELECT "name"
         , "size"
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    ;
    
    RETURN 'SUCCESS';
    
    
END;
$$;







-- 3. 프로시저 수행(테스트)
CALL COMM.REFRESH_WAREHOUSE_INFO();

SELECT * FROM COMM.WAREHOUSE_INFO;


-- 4. 모니터링용 TASK 수행하는 웨어하우스 생성
CREATE WAREHOUSE WAREHOUSE_MONITORING WAREHOUSE_SIZE = 'X-Small' AUTO_RESUME = true AUTO_SUSPEND = 60 WAREHOUSE_TYPE = 'STANDARD' MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1;

-- 5. AIRFLOW 웨어하우스만 모니터링하는 TASK
CREATE OR REPLACE TASK COMM.REFRESH_WAREHOUSE_INFO_TASK
    WAREHOUSE = 'WAREHOUSE_MONITORING'
    SCHEDULE = 'USING CRON 0 */1 * * * Asia/Seoul'
    COMMENT = 'Monitor warehouses every 60 minutes'
AS
    CALL COMM.REFRESH_WAREHOUSE_INFO();


-- 6. TASK RESUME
ALTER TASK COMM.REFRESH_WAREHOUSE_INFO_TASK RESUME;
SHOW TASKS;

USE SCHEMA DATASVC.PUBLIC;