시스템 테이블 개요와 핵심 테이블

system (카탈로그)
├── access           ← 보안 감사 로그
│   ├── audit
│   └── table_lineage (access 스키마의 lineage 뷰)
├── billing          ← 비용 / DBU 사용량
│   ├── usage
│   └── list_prices
├── compute          ← 클러스터 / 웨어하우스
│   ├── clusters
│   └── warehouse_events
├── lakeflow         ← Job / 워크플로우 실행
│   ├── jobs
│   ├── job_tasks
│   └── job_run_timeline
├── lineage          ← 데이터 리니지
│   ├── table_lineage
│   └── column_lineage
└── storage          ← 스토리지 최적화
    └── predictive_optimization_operations_history

# REST API로 특정 스키마 활성화 (예: billing)
curl -X PUT "https://accounts.azuredatabricks.net/api/2.0/accounts/{account_id}/metastores/{metastore_id}/systemschemas/billing" \
  -H "Authorization: Bearer <token>"

-- 지난 30일간 워크로드 유형별 DBU 사용량 및 비용 추정
SELECT
    billing_origin_product,
    sku_name,
    SUM(u.usage_quantity)                              AS total_dbus,
    ROUND(SUM(u.usage_quantity * p.pricing.default), 2) AS estimated_cost_usd
FROM system.billing.usage u
LEFT JOIN system.billing.list_prices p
    ON u.sku_name = p.sku_name
    AND u.usage_date BETWEEN p.price_start_time::DATE AND COALESCE(p.price_end_time::DATE, CURRENT_DATE())
WHERE u.usage_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY estimated_cost_usd DESC;

-- 일별 비용 추이 (급증 감지용)
SELECT
    usage_date,
    billing_origin_product,
    SUM(usage_quantity) AS daily_dbus
FROM system.billing.usage
WHERE usage_date >= CURRENT_DATE() - INTERVAL 90 DAYS
GROUP BY 1, 2
ORDER BY usage_date, billing_origin_product;

-- 사용자별 DBU 소비 TOP 10
SELECT
    identity_metadata.run_as   AS user_email,
    billing_origin_product     AS product,
    SUM(usage_quantity)        AS total_dbus
FROM system.billing.usage
WHERE usage_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY total_dbus DESC
LIMIT 10;

-- 최근 7일간 누가 어떤 테이블에 접근했는지 확인
SELECT
    event_date,
    user_identity.email     AS user_email,
    action_name,
    request_params.full_name_arg AS table_name,
    source_ip_address
FROM system.access.audit
WHERE action_name IN ('getTable', 'commandSubmit')
    AND event_date >= CURRENT_DATE() - INTERVAL 7 DAYS
ORDER BY event_date DESC
LIMIT 100;

-- 비정상 접근 패턴 감지: 야간 시간대(22시~06시) 활동
SELECT
    user_identity.email,
    COUNT(*)                    AS late_night_actions,
    COLLECT_SET(action_name)    AS action_types
FROM system.access.audit
WHERE HOUR(event_time) NOT BETWEEN 6 AND 22
    AND event_date >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY user_identity.email
HAVING COUNT(*) > 10
ORDER BY late_night_actions DESC;

-- 권한 변경 이력 (중요 보안 이벤트)
SELECT
    event_time,
    user_identity.email     AS actor,
    action_name,
    request_params
FROM system.access.audit
WHERE action_name IN (
    'updatePermissions', 'grantPrivileges', 'revokePrivileges',
    'createCredential', 'deleteCredential'
)
    AND event_date >= CURRENT_DATE() - INTERVAL 30 DAYS
ORDER BY event_time DESC;

-- 자동 종료 설정이 없거나 너무 긴 클러스터 (비용 누수 리스크)
SELECT
    cluster_id,
    cluster_name,
    owned_by,
    autotermination_minutes,
    state,
    TIMESTAMPDIFF(HOUR, state_start_time, CURRENT_TIMESTAMP()) AS running_hours
FROM system.compute.clusters
WHERE state = 'RUNNING'
    AND (autotermination_minutes IS NULL OR autotermination_minutes > 120)
ORDER BY running_hours DESC;

-- 클러스터 유형별 분포 파악
SELECT
    cluster_source,
    node_type_id,
    COUNT(DISTINCT cluster_id)  AS cluster_count,
    AVG(autotermination_minutes) AS avg_autotermination_min
FROM system.compute.clusters
WHERE state_start_time >= CURRENT_DATE() - INTERVAL 30 DAYS
GROUP BY 1, 2
ORDER BY cluster_count DESC;