알림, 대시보드, 베스트 프랙티스

-- 에러율이 5%를 초과하면 알림 트리거
-- (이 쿼리를 Databricks SQL Alert로 등록합니다)
SELECT
    endpoint_name,
    COUNT(*) AS total_requests,
    SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) AS errors,
    ROUND(
        SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
    ) AS error_rate_pct
FROM system.serving.served_model_requests
WHERE request_time >= CURRENT_TIMESTAMP() - INTERVAL 1 HOUR
GROUP BY endpoint_name
HAVING error_rate_pct > 5.0;

# Slack Webhook을 통한 알림 예시 (Lakeflow Jobs의 Task로 실행)
import requests
import json

slack_webhook_url = "https://hooks.slack.com/services/T.../B.../..."

def send_alert(endpoint_name, error_rate, p99_latency):
    payload = {
        "text": f":rotating_light: *서빙 엔드포인트 알림*\n"
                f"- 엔드포인트: `{endpoint_name}`\n"
                f"- 에러율: {error_rate}%\n"
                f"- P99 지연시간: {p99_latency}ms"
    }
    requests.post(slack_webhook_url, json=payload)

-- 1. 일별 요청량 및 Percentile 지연 시간 (시계열 차트)
SELECT
    DATE(timestamp_ms / 1000) AS day,
    COUNT(*) AS total_requests,
    ROUND(PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY execution_time_ms), 1) AS p50_ms,
    ROUND(PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY execution_time_ms), 1) AS p95_ms,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY execution_time_ms), 1) AS p99_ms,
    SUM(CASE WHEN status_code != 200 THEN 1 ELSE 0 END) AS error_count,
    ROUND(SUM(CASE WHEN status_code != 200 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS error_rate_pct
FROM ml_prod.monitoring.fraud_model_payload
WHERE date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY DATE(timestamp_ms / 1000)
ORDER BY day DESC;

-- 2. LLM 엔드포인트: 토큰 사용량 추적 (시계열 차트)
SELECT
    DATE(timestamp_ms / 1000) AS day,
    SUM(request:usage.prompt_tokens) AS total_input_tokens,
    SUM(request:usage.completion_tokens) AS total_output_tokens,
    SUM(request:usage.total_tokens) AS total_tokens,
    ROUND(AVG(execution_time_ms), 0) AS avg_latency
FROM ml_prod.monitoring.agent_payload
WHERE date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY DATE(timestamp_ms / 1000)
ORDER BY day DESC;

-- 3. 에러 분석 (테이블 위젯)
SELECT
    status_code,
    COUNT(*) AS count,
    FIRST(response) AS sample_error
FROM ml_prod.monitoring.fraud_model_payload
WHERE status_code != 200
    AND date = CURRENT_DATE()
GROUP BY status_code;

-- 4. 시간대별 트래픽 패턴 (히트맵)
SELECT
    DAYOFWEEK(timestamp_ms / 1000) AS day_of_week,
    HOUR(timestamp_ms / 1000) AS hour_of_day,
    COUNT(*) AS request_count
FROM ml_prod.monitoring.fraud_model_payload
WHERE date >= CURRENT_DATE() - INTERVAL 7 DAYS
GROUP BY day_of_week, hour_of_day;

-- 5. 엔드포인트별 SLA 준수율 비교 (가로 막대 차트)
-- SLA 기준: P99 < 1000ms, 에러율 < 1%
SELECT
    endpoint_name,
    DATE(request_time) AS day,
    COUNT(*) AS total_requests,
    ROUND(PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY execution_time_ms), 0) AS p99_ms,
    ROUND(SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS error_rate_pct,
    -- SLA 준수 여부
    CASE
        WHEN PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY execution_time_ms) <= 1000
             AND SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) <= 1.0
        THEN 'SLA 준수'
        ELSE 'SLA 위반'
    END AS sla_status
FROM system.serving.served_model_requests
WHERE DATE(request_time) >= CURRENT_DATE() - INTERVAL 7 DAYS
GROUP BY endpoint_name, DATE(request_time)
ORDER BY day DESC, endpoint_name;

-- 6. 비용 추이 (일별, 엔드포인트별)
SELECT
    usage_date,
    usage_metadata.endpoint_name,
    SUM(usage_quantity) AS total_dbus,
    ROUND(SUM(usage_quantity * list_price), 2) AS daily_cost_usd
FROM system.billing.usage
WHERE sku_name LIKE '%SERVING%'
    AND usage_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY usage_date, usage_metadata.endpoint_name
ORDER BY usage_date DESC;

# 올바른 순서: 엔드포인트 생성과 Inference Table 활성화를 동시에
w.serving_endpoints.create(
    name="my-model",
    config=EndpointCoreConfigInput(
        served_entities=[...],
        auto_capture_config=AutoCaptureConfigInput(enabled=True, ...)  # 처음부터 활성화
    )
)

-- 배포 시점 기준 분포 저장
CREATE TABLE ml_prod.monitoring.fraud_model_baseline AS
SELECT * FROM ml_prod.monitoring.fraud_model_payload
WHERE date BETWEEN '2024-01-01' AND '2024-01-31';  -- 첫 달 데이터

# 클라이언트에서 버전 정보를 메타데이터로 전달
import requests

response = requests.post(
    endpoint_url,
    headers={
        "Authorization": f"Bearer {token}",
        "X-Model-Version": "v3",        # 커스텀 메타데이터
        "X-Client-Id": "recommendation-service"
    },
    json={"inputs": features}
)

-- 일별 비용이 임계값 초과 시 알림 (Databricks SQL Alert로 등록)
SELECT
    usage_date,
    SUM(usage_quantity * list_price) AS daily_cost_usd
FROM system.billing.usage
WHERE sku_name LIKE '%SERVING%'
    AND usage_date = CURRENT_DATE() - INTERVAL 1 DAYS
GROUP BY usage_date
HAVING daily_cost_usd > 100.0;  -- $100 초과 시 알림

-- 잘못된 예시: 전체 테이블 스캔으로 매우 느림
SELECT * FROM ml_prod.monitoring.fraud_model_payload
WHERE timestamp_ms > UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 1 DAYS) * 1000;

-- 올바른 예시: date 파티션 필터 추가
SELECT * FROM ml_prod.monitoring.fraud_model_payload
WHERE date >= CURRENT_DATE() - INTERVAL 1 DAYS  -- 파티션 프루닝
    AND timestamp_ms > UNIX_TIMESTAMP(CURRENT_TIMESTAMP() - INTERVAL 1 DAYS) * 1000;

# 올바른 예시: 콜드 스타트를 고려한 충분한 타임아웃
response = requests.post(
    endpoint_url,
    json=payload,
    timeout=60  # 60초 타임아웃 (콜드 스타트 포함)
)