Databricks SQL 개요

-- 매개변수를 사용한 쿼리 (SQL Editor에서 실행)
SELECT
    order_date,
    COUNT(*) AS order_count,
    SUM(amount) AS total_revenue
FROM catalog.schema.orders
WHERE order_date BETWEEN '{{start_date}}' AND '{{end_date}}'
  AND status = '{{order_status}}'
GROUP BY order_date
ORDER BY order_date;

Server Hostname: <workspace-url>.databricks.com
Port:           443
HTTP Path:      /sql/1.0/warehouses/<warehouse-id>
Auth:           OAuth (M2M) 또는 Personal Access Token

-- 1. 결과 캐시 활용: 동일 쿼리는 캐시에서 즉시 반환 (추가 비용 없음)
-- SQL Warehouse는 결과 캐시를 기본 활성화합니다

-- 2. 필요한 컬럼만 SELECT (SELECT * 지양)
-- ❌ 비용이 높은
SELECT * FROM catalog.schema.large_table;

-- ✅ 효율적
SELECT order_id, amount, order_date
FROM catalog.schema.large_table
WHERE order_date >= '2025-01-01';

-- 3. EXPLAIN으로 쿼리 계획 확인
EXPLAIN SELECT ... FROM ...;

-- 4. 쿼리 태그로 비용 추적
SET statement_tag = 'marketing_dashboard';
SELECT ... FROM ...;

-- 카탈로그와 스키마 설정
USE CATALOG my_catalog;
USE SCHEMA my_schema;

-- 샘플 테이블 생성
CREATE TABLE IF NOT EXISTS sales (
    sale_id     BIGINT GENERATED ALWAYS AS IDENTITY,
    product     STRING,
    region      STRING,
    amount      DECIMAL(10,2),
    sale_date   DATE
) USING DELTA;

-- 데이터 삽입
INSERT INTO sales (product, region, amount, sale_date) VALUES
    ('Laptop', 'Seoul', 1500000, '2025-01-15'),
    ('Mouse', 'Busan', 25000, '2025-01-16'),
    ('Keyboard', 'Seoul', 89000, '2025-01-16'),
    ('Monitor', 'Daegu', 450000, '2025-01-17'),
    ('Laptop', 'Seoul', 1800000, '2025-01-17');

-- 지역별 매출 분석
SELECT
    region,
    COUNT(*)    AS sale_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_revenue
FROM sales
GROUP BY region
ORDER BY total_revenue DESC;