【Snowflake】監視実装例（失敗したTASKを見つけて管理者にメールで発報）

はじめに

Snowflakeではタスクを使用したJOBの定期実行やEメール送信なども可能なため、Snowflakeで完結するシンプルな定常監視の仕組みの例を作成したので掲載してみます

概要

task_historyを確認して24時間以内にSTATEが'FAILED'のレコードが存在した場合、指定したメールアドレスに通知する

事前準備

Snowflakeから送信可能なメールアドレスは該当アカウント内のユーザに紐づいており、
かつ承認済のアドレスである必要があります。
例えば以下のサポートの入り口やプロファイルからメールを有効にしておく必要があります。

よくある質問として、グループメールには送れないの？と聞かれるのですが、グループメール用のSnowflakeユーザを作成し、メールアドレスを承認後にユーザを無効化しておけば大丈夫。

作成するもの

1. E-mail送信用通知統合の作成
   通知統合名：admin_mail_integration
2. プロシージャとTASK配置用スキーマの作成
   スキーマ名：kansi
3. 通知用ストアドプロシージャの作成
   プロシージャ名：kansi.check_task_history()
4. 通知用TASKの作成
   TASK名：kansi.alert_failed_task

実装例詳細

1. E-mail送信用通知統合の作成

通知統合とは・・メールを送信するためのインターフェース的なObjectだと私は捉えています

CREATE OR REPLACE NOTIFICATION INTEGRATION admin_mail_integration
  TYPE=EMAIL
  ENABLED=TRUE
  -- 送信先メールアドレスを通知統合で限定することも可能
  -- ALLOWED_RECIPIENTS=('[e-mail]','[e-mail]')
;

メールを送るロールが通知統合のownerでない場合は権限の付与

GRANT USAGE ON INTEGRATION admin_mail_integration TO ROLE email_send_role;

メール送信テスト

CALL SYSTEM$SEND_EMAIL(
    'admin_mail_integration',     -- INTEGRATIONを指定
    '[e-mail]',       -- 宛先のアドレスを指定
    'TEST MAIL',                  -- タイトルを指定
    'メールのテストです'            --　本文を指定。\nで改行も可能
    );

指定したアドレスにこんなメールが飛んでくるはず。。

注意！この時点でメールが飛んでいない場合、上記事前準備の項を確認してください！

2. スキーマの作成

-- プロシージャとTASKを配置するDBを選択
USE DATABASE <DB名>;

-- kansiスキーマが存在しない場合は作成
CREATE SCHEMA IF NOT EXISTS kansi;

3. 通知用ストアドプロシージャの作成

参考：ストアドプロシージャ化せずに直接TASKに記述してもよいですが、
　　　内容修正する際にプロシージャのほうが編集やテスト等が個人的にはしやすいので。

-- task_historyを確認すしてFAILED STATUSが存在したらエラーで返すプロシージャ
CREATE OR REPLACE PROCEDURE kansi.check_task_history()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS CALLER  -- 実行したROLEの権限で実行（⇔ EXECUTE AS OWNER）
AS 
$$
DECLARE
  error_count INT;
BEGIN
  -- TASK_HISTORYから一日以内にステータスFAILEDの行が存在するか確認
  SELECT COUNT(1)
    INTO :error_count
    FROM TABLE(information_schema.task_history())
   WHERE STATE = 'FAILED'
     AND COMPLETED_TIME > DATEADD(DAY,-1,CURRENT_TIMESTAMP());
 
  -- FAILEDの行が存在する場合はメール発報
  IF (error_count > 0) THEN
    CALL SYSTEM$SEND_EMAIL(
        'admin_mail_integration',      -- INTEGRATIONを指定
        '[e-mail]',        -- 宛先のアドレスを指定
        'Alert: EXISTS FAILD TASK',    -- タイトルを指定　↓本文を指定
        '24時間以内にFAILEDステータスで終了したTASKが ' || :error_count || ' 件 存在します。\n\n' ||
        '以下のSQLのSQLを実行して詳細を確認してください\n\n' ||
        'SELECT * FROM TABLE(information_schema.task_history()) WHERE STATE = ''FAILED'' ORDER BY completed_time DESC;'
        );
    RETURN 'FAILED COUNT:' || :error_count || ' エラーあり。メールを送信しました';  
  END IF;
 
  RETURN 'FAILED COUNT:' || :error_count;
END;
$$;

プロシージャのテスト実行

TASKのエラーがないとメールの確認まではできないですが・・
どうしてもメール確認までしたい場合はIF (error_count > 0) THENの条件をいじれば飛ばせます。（戻すのを忘れないように！！）

CALL kansi.check_task_history()

4. 通知用TASKの作成

朝9時に上記プロシージャが回るように設定

CREATE OR REPLACE TASK kansi.alert_failed_task
  USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL' -- ウェアハウスをSnowflake管理のものに指定できる。次回以降、適切なサイズに自動変更してくれるのと、1秒単位課金となり安い
  --WAREHOUSE = 'WH_OKADATA_XS'  -- ウェアハウス指定も可能だが最低60秒分課金されてしまう
  SCHEDULE = 'USING CRON 00 09 * * * Asia/Tokyo'
AS
  CALL kansi.check_task_history();

TASKの有効化

TASKは作成時点では有効になっておらず、明示的に有効化する必要がある

ALTER TASK kansi.alert_failed_task RESUME;

TASKが有効になったことを確認

以下を実行してステータスがstartedになっていればOK

SHOW TASKS;

参考：TASKの実行結果確認

SELECT * FROM TABLE(information_schema.task_history());

参考URL

通知統合の作成
https://docs.snowflake.com/ja/sql-reference/sql/create-notification-integration

TASKの作成
https://docs.snowflake.com/ja/sql-reference/sql/create-task

おわりに

かなりベーシックな作りにしたと思いますのでこれをベースに改造できるかと思います。
誰かのお役に立てば幸いです。