Warehouse Dag별 모니터링

-- Dag별 Warehouse 부여 후 Warehouse 마다 큐와 런닝타임이 얼마나 걸리는지 통계를 내보자.
-- Show Warehouse -> 5분마다 웨어하우스 상태 적재
-- TASK 활용하면 될 듯

 

USE SCHEMA DATA.LAB;
SHOW WAREHOUSES LIKE 'AIRFLOW%';



-- 1. 모니터링 메타 테이블 생성
CREATE OR REPLACE TABLE LAB.WAREHOUSE_MONITORING (
      ID                INT AUTOINCREMENT PRIMARY KEY             COMMENT '모니터링 레코드 고유 식별자 (자동 증가)'
    , CAPTURED_AT       TIMESTAMP_LTZ DEFAULT CURRENT_TIMESTAMP() COMMENT '데이터 수집 시점 (로컬 타임존 기준)'
    , NAME              VARCHAR(255)                              COMMENT '웨어하우스 이름'
    , STATE             VARCHAR(50)                               COMMENT '웨어하우스 상태: STARTED(실행중), SUSPENDED(일시정지), RESIZING(크기조정중)'
    , TYPE              VARCHAR(50)                               COMMENT '웨어하우스 타입'
    , SIZE              VARCHAR(50)                               COMMENT '웨어하우스 크기'
    , MIN_CLUSTER_COUNT INT                                       COMMENT '멀티클러스터 웨어하우스의 최소 클러스터 수'
    , MAX_CLUSTER_COUNT INT                                       COMMENT '멀티클러스터 웨어하우스의 최대 클러스터 수'
    , STARTED_CLUSTERS  INT                                       COMMENT '현재 시작된 클러스터 수'
    , RUNNING           INT                                       COMMENT '웨어하우스에서 현재 실행 중인 SQL 문의 수'
    , QUEUED            INT                                       COMMENT '웨어하우스에서 대기 중인 SQL 문의 수'
    , SCALING_POLICY    VARCHAR(50)                               COMMENT '멀티클러스터 스케일링 정책: STANDARD(표준), ECONOMY(경제적)'
    , AVAILABLE         VARCHAR(50)                               COMMENT '웨어하우스 가용성 백분율 (0.0-100.0)'
    , PROVISIONING      VARCHAR(50)                               COMMENT '웨어하우스 프로비저닝 상태'
    , QUIESCING         VARCHAR(50)                               COMMENT '웨어하우스 정지 진행 상태'
)
COMMENT = 'Snowflake 웨어하우스 상태 모니터링 테이블 - SHOW WAREHOUSES 결과를 주기적으로 저장'
;

 

-- 2. AIRFLOW 웨어하우스만 모니터링하는 프로시저
CREATE OR REPLACE PROCEDURE LAB.MONITOR_AIRFLOW_WAREHOUSES()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    -- AIRFLOW 웨어하우스만 조회
    SHOW WAREHOUSES;
    
    -- 결과를 모니터링 테이블에 삽입
    INSERT INTO LAB.WAREHOUSE_MONITORING (
          NAME
     , STATE
     , TYPE
     , SIZE
     , MIN_CLUSTER_COUNT
     , MAX_CLUSTER_COUNT
     , STARTED_CLUSTERS
     , RUNNING
     , QUEUED
     , SCALING_POLICY
     , AVAILABLE
     , PROVISIONING
     , QUIESCING
    )   
    SELECT "name"
         , "state"
         , "type"
         , "size"
         , '1'
         , '1'
         , '1'
         , "running"
         , "queued"
         , 'scaling_policy'
         , "available"
         , "provisioning"
         , "quiescing"
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
   WHERE "name" LIKE 'AIRFLOW%'
      OR "name" = 'EMR_WH_XL'
      OR "name" = 'LOG_WH';
    
    RETURN 'SUCCESS';
    
    
END;
$$;

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

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

-- 5. AIRFLOW 웨어하우스만 모니터링하는 TASK
CREATE OR REPLACE TASK LAB.WAREHOUSE_MONITOR_AIRFLOW_TASK
    WAREHOUSE = 'PENTA_MONITORING'
    SCHEDULE = 'USING CRON */5 * * * * Asia/Seoul'
    COMMENT = 'Monitor AIRFLOW warehouses every 5 minutes'
AS
    CALL LAB.MONITOR_AIRFLOW_WAREHOUSES();

-- 6. TASK RESUME
ALTER TASK LAB.WAREHOUSE_MONITOR_AIRFLOW_TASK RESUME;
SHOW TASKS;

-- 7. 모니터링 메타 테이블 조회
SELECT *
  FROM LAB.WAREHOUSE_MONITORING
 WHERE NAME = 'AIRFLOW_DW_HOUR_WH'
   AND MAX_CLUSTER_COUNT = 2
;

-- 8. EMR_WH_XL 웨어하우스만 모니터링하는 프로시저(09/18 추가)
CREATE OR REPLACE PROCEDURE LAB.MONITOR_EMR_WH_XL_WAREHOUSES()
RETURNS STRING
LANGUAGE SQL
EXECUTE AS CALLER
AS
$$
BEGIN
    -- EMR_WH_XL 웨어하우스만 조회
    SHOW WAREHOUSES;
    
    -- 결과를 모니터링 테이블에 삽입
    INSERT INTO LAB.WAREHOUSE_MONITORING (
          NAME
     , STATE
     , TYPE
     , SIZE
     , MIN_CLUSTER_COUNT
     , MAX_CLUSTER_COUNT
     , STARTED_CLUSTERS
     , RUNNING
     , QUEUED
     , SCALING_POLICY
     , AVAILABLE
     , PROVISIONING
     , QUIESCING
    )   
    SELECT "name"
         , "state"
         , "type"
         , "size"
         , '1'
         , '1'
         , '1'
         , "running"
         , "queued"
         , 'scaling_policy'
         , "available"
         , "provisioning"
         , "quiescing"
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
;
    
    RETURN 'SUCCESS';
    
    
END;
$$;

-- 9. 프로시저 수행(테스트)
CALL LAB.MONITOR_EMR_WH_XL_WAREHOUSES();

 

-- 00:00 ~ RAW02 돌기 전까지 기존 AIRFLOW_WH가 SUSPENDED 되어 있는 것 확인해야 함
-- QUERY_HISTORY 조회하여 STARTED_CLUSTERS가 나오면 안됨
SELECT QUERY_TEXT
     , QUERY_TYPE
     , WAREHOUSE_NAME
     , CLUSTER_NUMBER
     , START_TIME
     , END_TIME
     , TOTAL_ELAPSED_TIME
     , TO_CHAR(DATEADD(MILLISECOND, TOTAL_ELAPSED_TIME, '1970-01-01 00:00:00'),'HH24:MI:SS') AS ELAPSED_HHMMSS
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
 WHERE 1=1
   AND TO_DATE(START_TIME) = '2025-09-15'
   AND QUERY_TYPE = 'CALL'
   AND WAREHOUSE_NAME = 'AIRFLOW_WH'
   AND START_TIME < TO_TIMESTAMP('2025-09-15 00:42:00')
   AND QUERY_TEXT NOT LIKE '%USP_GET_TRSF_TBL_CVT_SQL%'
   AND QUERY_TEXT NOT LIKE '%USP_ETL_DATE%'
   AND QUERY_TEXT NOT LIKE '%USP_GET_ETL_CMPL_FLAG%'
   AND QUERY_TEXT NOT LIKE '%USP_SET_ETL_CMPL_FLAG%'
 ORDER BY START_TIME
;