Snowflake の通知統合を使ったら、秒でメール送信できてびっくりした話。

create notification integration my_notify_int
    type = EMAIL
    ENABLED = TRUE
    ALLOWED_RECIPIENTS = ('****@classmethod.jp')

call system$send_email(
    'my_notify_int',
    '****@classmethod.jp',
    'Email Alert from Snowflake: Hello!',
    'This is Test for Alert from Snowflake...'
)

call system$send_email(
    'my_notify_int',
--    '****@classmethod.jp',
    'aaa@test.co.jp',
    'Email Alert from Snowflake: Hello!',
    'This is Test for Alert from Snowflake...'
)

import boto3
from botocore.exceptions import ClientError
import json

def lambda_handler(event, context):
    secret_name = "cm-mikami-secret"
    region_name = "ap-northeast-1"

    status_code = 200
    array_of_rows_to_return = []

    try:
        if "body" in event:
            # API Gateway経由
            event_body = event["body"]
            payload = json.loads(event_body)
        else:
            # テスト実行
            payload = event

        rows = payload["data"]

        # AWS Secrets Manager client setup
        session = boto3.session.Session()
        client = session.client(
            service_name='secretsmanager',
            region_name=region_name
        )

        # Get secret
        get_secret_value_response = client.get_secret_value(
            SecretId=secret_name
        )
        secret_string = get_secret_value_response['SecretString']
        secret = json.loads(secret_string)
        emails = secret['emails'].split(',')

        # For each input row
        for row in rows:
            row_number = row[0]
            row_to_return = [row_number, emails]
            array_of_rows_to_return.append(row_to_return)

        json_compatible_string_to_return = json.dumps({"data": array_of_rows_to_return})

    except ClientError as e:
        status_code = 400
        json_compatible_string_to_return = json.dumps({
            "data": [[0, f"AWS error: {str(e)}"]]
        })
    except Exception as e:
        status_code = 400
        json_compatible_string_to_return = json.dumps({
            "data": [[0, f"Error: {str(e)}"]]
        })

    return {
        'statusCode': status_code,
        'body': json_compatible_string_to_return
    }

create or replace api integration aws_api_get_secret
  api_provider = aws_api_gateway
  api_aws_role_arn = 'arn:aws:iam::************:role/cm-mikami-sf-integration'
  api_allowed_prefixes = ('https://dihukvxk90.execute-api.ap-northeast-1.amazonaws.com/dev/get-emails')
  enabled = true

desc integration aws_api_get_secret

create or replace external function get_email_from_secrets()
    returns array
    API_INTEGRATION = aws_api_get_secret
    as 'https://dihukvxk90.execute-api.ap-northeast-1.amazonaws.com/dev/get-emails'

select get_email_from_secrets()

CREATE OR REPLACE PROCEDURE send_email_with_dynamic_integration(
    subject STRING DEFAULT 'Email Alert from Snowflake: Hello!',
    message STRING DEFAULT 'This is Test for Alert from Snowflake...'
)
RETURNS STRING
AS
$$
DECLARE
    email_array ARRAY;
    email_list STRING;
    integration_name STRING := 'my_notify_int';
    sent_count INTEGER := 0;
    result_msg STRING;
    current_email STRING;
    i INTEGER;
BEGIN
    -- メールアドレス取得
    email_array := (SELECT get_email_from_secrets());
    email_list := '';
    FOR i IN 0 TO ARRAY_SIZE(:email_array) - 1 DO
        IF (:i > 0) THEN
            email_list := :email_list || ',';
        END IF;
        email_list := :email_list || '''' || TRIM(:email_array[:i]::string) || '''';
    END FOR;

    -- 通知統合を作成/更新
    EXECUTE IMMEDIATE 
        'CREATE OR REPLACE NOTIFICATION INTEGRATION ' || :integration_name || 
        ' TYPE = EMAIL ENABLED = TRUE ALLOWED_RECIPIENTS = (' || :email_list || ')';

    result_msg := 'Integration created/updated. ';

    -- メール送信
    FOR i IN 0 TO ARRAY_SIZE(:email_array) - 1 DO
        current_email := TRIM(:email_array[:i]::string);
        CALL SYSTEM$SEND_EMAIL(
            :integration_name,
            :current_email,
            :subject,
            :message
        );
        sent_count := :sent_count + 1;
    END FOR;

    RETURN :result_msg || 'Sent ' || :sent_count || ' emails.';

EXCEPTION
    WHEN OTHER THEN
        RETURN 'Error: ' || SQLERRM;
END;
$$;

call send_email_with_dynamic_integration()