Snowflake で監視や定期的なモニタリングを実施するとよい観点と方法をまとめてみた

SELECT
    QUERY_ID,
    USER_NAME,
    START_TIME,
    END_TIME,
    ERROR_CODE,
    ERROR_MESSAGE
FROM 
    TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        end_time_range_start=>dateadd('hours',-1,current_timestamp()),
        end_time_range_end=>current_timestamp(), -- 直近 1 時間
        result_limit=>10000
    ))
WHERE 
    ERROR_CODE IS NOT NULL
    -- AND WAREHOUSE_NAME = '<warehouse_name>'
    -- AND DATABASE_NAME = '<database_name>'
    -- AND SCHEMA_NAME = '<schema_name>'
ORDER BY 
    START_TIME DESC;

SELECT
    EXECUTION_TIME / 1000 AS EXECUTION_TIME_SECONDS,
    QUERY_ID,
    START_TIME,
    END_TIME
FROM 
    TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(
        end_time_range_start=>dateadd('hours',-1,current_timestamp()),
        end_time_range_end=>current_timestamp(), -- 直近 1 時間
        result_limit=>10000
    ))
-- WHERE 
--     EXECUTION_TIME > 60000 -- 実行に 60 秒以上かかっている
ORDER BY 
    EXECUTION_TIME DESC;

ALTER SESSION SET QUERY_TAG = 'クエリ検証用';

-- アカウント全体のステージのストレージ使用量
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.STAGE_STORAGE_USAGE_HISTORY
WHERE USAGE_DATE BETWEEN dateadd('days',-10,current_date()) AND current_date()
ORDER BY USAGE_DATE DESC;

-- データベース単位
SELECT *
FROM table(INFORMATION_SCHEMA.DATABASE_STORAGE_USAGE_HISTORY(
    dateadd('days',-10,current_date()),current_date() -- 直近 10 日分
    )
)
ORDER BY USAGE_DATE DESC;

-- テーブル単位のストレージ使用量
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.TABLE_STORAGE_METRICS;

SELECT 
    WAREHOUSE_NAME,
    SUM(CREDITS_USED) AS TOTAL_CREDITS,
    DATE_TRUNC('DAY', START_TIME) AS USAGE_DATE
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE 
    START_TIME >= CURRENT_DATE - 7
GROUP BY 
    WAREHOUSE_NAME, USAGE_DATE
ORDER BY 
    USAGE_DATE DESC, TOTAL_CREDITS DESC;

SELECT 
    WAREHOUSE_NAME,
    AVG(AVG_RUNNING) AS AVG_CONCURRENT_QUERIES,
    AVG(AVG_QUEUED_LOAD) AS AVG_QUEUED_QUERIES,
    AVG(AVG_BLOCKED) AS AVG_BLOCKED_QUERIES,
    DATE_TRUNC('HOUR', START_TIME) AS HOUR
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE 
    START_TIME >= CURRENT_DATE - 1
GROUP BY 
    WAREHOUSE_NAME, HOUR
ORDER BY 
    HOUR DESC;

SELECT 
    *
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY
WHERE 
    EVENT_TIMESTAMP >= CURRENT_DATE - 7
ORDER BY 
    EVENT_TIMESTAMP DESC;

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    CREATED,
    LAST_ALTERED
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    LAST_ALTERED >= CURRENT_DATE - 7
ORDER BY 
    LAST_ALTERED DESC;

SELECT 
    *
FROM 
    SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE 
    END_TIME >= CURRENT_DATE - 7
ORDER BY 
    END_TIME DESC;