Skip to main content

​
SQL 스크립팅이란?

SQL 스크립팅(SQL Scripting) 은 Databricks SQL에서 변수, 조건문, 반복문, 예외 처리 를 포함한 절차적 프로그래밍을 가능하게 하는 기능입니다. 기존에는 Python이나 Scala로 작성해야 했던 복잡한 데이터 처리 로직을 SQL만으로 구현 할 수 있습니다.
💡 비유: 일반 SQL이 “단일 명령”이라면, SQL 스크립팅은 “여러 명령을 순서대로 실행하는 프로그램”입니다. 마치 저장 프로시저(Stored Procedure)를 작성하는 것과 비슷합니다.

​
DECLARE 변수 선언

​
기본 변수 선언

-- 타입과 기본값을 지정한 변수 선언
DECLARE target_date DATE DEFAULT CURRENT_DATE() - INTERVAL 1 DAY;
DECLARE batch_size INT DEFAULT 1000;
DECLARE status_msg STRING DEFAULT '';
DECLARE total_amount DECIMAL(12,2);

-- 변수에 값 할당
SET total_amount = 0.00;

​
쿼리 결과로 변수 할당

DECLARE row_count INT;
DECLARE max_date DATE;

-- 단일 값 쿼리 결과를 변수에 할당
SET row_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = target_date);
SET max_date = (SELECT MAX(order_date) FROM silver.orders);

​
변수 타입

타입예시설명
INT / BIGINTDECLARE count INT DEFAULT 0정수형
DECIMAL(p,s)DECLARE amount DECIMAL(12,2)고정 소수점
DOUBLEDECLARE ratio DOUBLE부동 소수점
STRINGDECLARE name STRING DEFAULT ''문자열
DATEDECLARE d DATE DEFAULT CURRENT_DATE()날짜
TIMESTAMPDECLARE ts TIMESTAMP타임스탬프
BOOLEANDECLARE flag BOOLEAN DEFAULT TRUE불리언

​
SET 문

변수에 값을 할당하거나 Spark 설정을 변경합니다. 
-- 변수 값 할당
SET row_count = 100;
SET status_msg = '처리 완료';

-- 표현식으로 할당
SET batch_id = CONCAT('batch_', DATE_FORMAT(CURRENT_TIMESTAMP(), 'yyyyMMdd_HHmmss'));

-- 쿼리 결과로 할당
SET total_revenue = (
    SELECT SUM(amount) FROM gold.daily_revenue
    WHERE sale_date = target_date
);

​
IF / ELSEIF / ELSE

​
기본 조건문

DECLARE row_count INT;
DECLARE status STRING;

SET row_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = CURRENT_DATE() - INTERVAL 1 DAY);

IF row_count = 0 THEN
    SET status = 'NO_DATA';
    INSERT INTO audit.alerts (message, severity, created_at)
    VALUES ('어제 주문 데이터가 없습니다', 'CRITICAL', CURRENT_TIMESTAMP());

ELSEIF row_count < 100 THEN
    SET status = 'LOW_VOLUME';
    INSERT INTO audit.alerts (message, severity, created_at)
    VALUES ('어제 주문이 예상보다 적습니다: ' || CAST(row_count AS STRING), 'WARNING', CURRENT_TIMESTAMP());

ELSE
    SET status = 'NORMAL';
    -- 정상: Gold 테이블 갱신
    INSERT OVERWRITE gold.daily_summary
    SELECT
        CURRENT_DATE() - INTERVAL 1 DAY AS summary_date,
        COUNT(*) AS order_count,
        SUM(amount) AS total_revenue
    FROM silver.orders
    WHERE order_date = CURRENT_DATE() - INTERVAL 1 DAY;
END IF;

SELECT status AS processing_result, row_count AS processed_rows;

​
WHILE 루프

​
기본 반복문

-- 최근 12개월에 대해 월별 집계 생성
DECLARE month_offset INT DEFAULT 0;

WHILE month_offset < 12 DO
    DECLARE target_month DATE;
    SET target_month = DATE_TRUNC('MONTH', CURRENT_DATE()) - MAKE_INTERVAL(0, month_offset);

    MERGE INTO gold.monthly_summary AS target
    USING (
        SELECT
            target_month AS month,
            COUNT(*) AS order_count,
            SUM(amount) AS total_revenue,
            AVG(amount) AS avg_order_value
        FROM silver.orders
        WHERE DATE_TRUNC('MONTH', order_date) = target_month
    ) AS source
    ON target.month = source.month
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *;

    SET month_offset = month_offset + 1;
END WHILE;

SELECT '12개월 집계 완료' AS result;

​
동적 테이블 처리

-- 여러 테이블에 대해 동일한 처리 반복
DECLARE table_index INT DEFAULT 0;
DECLARE table_count INT;
DECLARE current_table STRING;

-- 처리할 테이블 목록을 임시 뷰로 생성
CREATE TEMPORARY VIEW tables_to_process AS
SELECT table_name FROM catalog.information_schema.tables
WHERE table_schema = 'bronze' AND table_name LIKE '%_raw';

SET table_count = (SELECT COUNT(*) FROM tables_to_process);

WHILE table_index < table_count DO
    SET current_table = (
        SELECT table_name FROM (
            SELECT table_name, ROW_NUMBER() OVER (ORDER BY table_name) AS rn
            FROM tables_to_process
        ) WHERE rn = table_index + 1
    );

    -- 각 테이블에 OPTIMIZE 실행
    OPTIMIZE IDENTIFIER('catalog.bronze.' || current_table);

    SET table_index = table_index + 1;
END WHILE;

​
예외 처리 (SIGNAL)

​
SIGNAL로 사용자 정의 오류 발생

DECLARE data_count INT;
SET data_count = (SELECT COUNT(*) FROM silver.orders WHERE order_date = CURRENT_DATE());

IF data_count = 0 THEN
    -- 사용자 정의 오류 발생
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '오늘 날짜의 주문 데이터가 없습니다. 데이터 수집 파이프라인을 확인하세요.';
END IF;

-- 데이터가 있으면 이후 로직 계속
SELECT '데이터 검증 통과. 행 수: ' || CAST(data_count AS STRING) AS result;

​
SQLSTATE 코드

SQLSTATE의미사용 시나리오
'45000'사용자 정의 일반 오류비즈니스 규칙 위반
'45001'데이터 품질 오류데이터 검증 실패
'45002'환경 설정 오류필수 설정 누락

​
BEGIN ATOMIC 트랜잭션

여러 테이블에 걸친 원자적 트랜잭션 을 수행합니다. 모든 작업이 성공하거나, 하나라도 실패하면 전체가 롤백됩니다. 
-- 계좌 이체: 원자적 트랜잭션
BEGIN ATOMIC
    -- 출금
    UPDATE accounts
    SET balance = balance - 50000
    WHERE account_id = 'A001';

    -- 입금
    UPDATE accounts
    SET balance = balance + 50000
    WHERE account_id = 'A002';

    -- 이체 기록
    INSERT INTO transactions (from_acc, to_acc, amount, txn_time)
    VALUES ('A001', 'A002', 50000, CURRENT_TIMESTAMP());
END;

​
트랜잭션 규칙

규칙설명
원자성모든 문이 성공하거나, 전부 롤백됩니다
지원 대상Unity Catalog 관리형 테이블만 지원합니다
중첩 불가트랜잭션 내에 트랜잭션을 중첩할 수 없습니다
DDL 제한CREATE/ALTER/DROP 등 DDL 문은 트랜잭션 내에서 사용할 수 없습니다

​
저장 프로시저 패턴

SQL 스크립팅의 모든 기능을 조합하여 재사용 가능한 프로시저 패턴을 구현할 수 있습니다.

​
데이터 품질 검증 프로시저

-- 범용 데이터 품질 검증 스크립트
DECLARE target_table STRING DEFAULT 'catalog.silver.orders';
DECLARE target_date DATE DEFAULT CURRENT_DATE() - INTERVAL 1 DAY;
DECLARE null_count INT;
DECLARE dup_count INT;
DECLARE negative_count INT;
DECLARE total_issues INT DEFAULT 0;

-- 1. NULL 체크
SET null_count = (
    SELECT COUNT(*)
    FROM IDENTIFIER(target_table)
    WHERE order_id IS NULL AND order_date = target_date
);
SET total_issues = total_issues + null_count;

-- 2. 중복 체크
SET dup_count = (
    SELECT COUNT(*) - COUNT(DISTINCT order_id)
    FROM IDENTIFIER(target_table)
    WHERE order_date = target_date
);
SET total_issues = total_issues + dup_count;

-- 3. 음수 금액 체크
SET negative_count = (
    SELECT COUNT(*)
    FROM IDENTIFIER(target_table)
    WHERE amount < 0 AND order_date = target_date
);
SET total_issues = total_issues + negative_count;

-- 4. 결과 기록
INSERT INTO audit.quality_checks (table_name, check_date, null_count, dup_count, negative_count, total_issues, checked_at)
VALUES (target_table, target_date, null_count, dup_count, negative_count, total_issues, CURRENT_TIMESTAMP());

-- 5. 임계치 초과 시 오류 발생
IF total_issues > 0 THEN
    SIGNAL SQLSTATE '45001'
    SET MESSAGE_TEXT = CONCAT(
        '데이터 품질 이슈 발견: NULL=', null_count,
        ', 중복=', dup_count,
        ', 음수금액=', negative_count
    );
END IF;

SELECT 'Quality check passed' AS result;

​
정리

기능구문핵심 포인트
변수 선언DECLARE ... DEFAULT ...타입과 기본값을 지정하여 변수를 선언합니다
값 할당SET var = expr리터럴, 표현식, 쿼리 결과로 할당합니다
조건문IF / ELSEIF / ELSE / END IF조건에 따라 다른 SQL을 실행합니다
반복문WHILE ... DO ... END WHILE조건이 만족하는 동안 반복 실행합니다
예외 처리SIGNAL SQLSTATE사용자 정의 오류를 발생시킵니다
트랜잭션BEGIN ATOMIC ... END여러 DML을 원자적으로 실행합니다

​
참고 링크