Snowflakeアカウントのアカウント予算にカスタムアクションによる通知を追加する

-- アカウント予算のアクティベート
CALL SNOWFLAKE.LOCAL.ACCOUNT_ROOT_BUDGET!ACTIVATE();

-- 予算額の設定
CALL SNOWFLAKE.LOCAL.ACCOUNT_ROOT_BUDGET!SET_SPENDING_LIMIT(1);

-- 通知先の設定
CALL SNOWFLAKE.LOCAL.ACCOUNT_ROOT_BUDGET!SET_EMAIL_NOTIFICATIONS(
  'costadmin@example.com, budgetadmin@example.com'
);

USE ROLE ACCOUNTADMIN;
CREATE NOTIFICATION INTEGRATION IF NOT EXISTS budget_email_int
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('costadmin@example.com');

-- 通知を行うロールへUSAGEを付与する。今回はSYSADMIN。
GRANT USAGE ON INTEGRATION budget_email_int TO ROLE SYSADMIN;

USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS app_db;
CREATE SCHEMA IF NOT EXISTS app_db.ops;

CREATE OR REPLACE PROCEDURE app_db.ops.send_budget_alert(
    integration_name STRING,
    recipients      STRING,
    subject         STRING,
    body            STRING
)
RETURNS STRING
LANGUAGE PYTHON
RUNTIME_VERSION = '3.11'
PACKAGES = ('snowflake-snowpark-python')
HANDLER = 'send_budget_alert'
COMMENT = 'Budget の Custom Action から SYSTEM$SEND_EMAIL で通知する'
EXECUTE AS OWNER
AS
$$
def send_budget_alert(session, integration_name, recipients, subject, body):
    if not integration_name or not str(integration_name).strip():
        raise Exception("budget email failed: integration_name is required")
    if not recipients or not str(recipients).strip():
        raise Exception("budget email failed: recipients is required")
    if not subject or not str(subject).strip():
        raise Exception("budget email failed: subject is required")
    if not body or not str(body).strip():
        raise Exception("budget email failed: body is required")

    try:
        session.sql(
            """
            CALL SYSTEM$SEND_EMAIL(?, ?, ?, ?, 'text/plain')
            """,
            params=[integration_name, recipients, subject, body],
        ).collect()
    except Exception as err:
        raise Exception(f"budget email failed: {err}") from err

    return "sent"
$$;

GRANT USAGE ON DATABASE app_db
  TO APPLICATION SNOWFLAKE;

GRANT USAGE ON SCHEMA app_db.ops
  TO APPLICATION SNOWFLAKE;

GRANT USAGE ON PROCEDURE app_db.ops.send_budget_alert(STRING, STRING, STRING, STRING)
  TO APPLICATION SNOWFLAKE;

CALL app_db.ops.send_budget_alert(
  'budget_email_int',
  '<recipient@example.com>',
  '[Snowflake] Monthly budget alert test',
  'This is a manual test of the budget email procedure.'
);

-- 予測消費が予算上限の 75% に達したときにアラート通知する。
CALL snowflake.local.ACCOUNT_ROOT_BUDGET!ADD_CUSTOM_ACTION(
  SYSTEM$REFERENCE(
    'PROCEDURE',
    'app_db.ops.send_budget_alert(string, string, string, string)'
  ),
  ARRAY_CONSTRUCT(
    'budget_email_int',
    '<recipient@example.com>',
    '[Snowflake] Monthly budget alert 75%',
    'Projected credit consumption reached 75% of the monthly budget limit.'
  ),
  'ACTUAL',
  75
);