Friendly SQL과 매크로
DuckDB의 Friendly SQL, table macro, PIVOT/UNPIVOT을 분석 코드 품질로 연결합니다.
핵심 요약
- Friendly SQL은 짧게 쓰려는 문법이 아니라, 분석 SQL을 덜 깨지고 검토하기 쉽게 만드는 도구입니다.
- GROUP BY ALL/ORDER BY ALL, SELECT * EXCLUDE/REPLACE, UNION BY NAME으로 집계 누락·PII 제외·schema evolution을 다룹니다.
- PIVOT은 편리하지만 고 cardinality 컬럼에서 메모리·컬럼 폭이 폭발하므로 pivot 값을 제한하거나 long format을 유지해 BI에서 pivot합니다.
- MACRO는 SQL 문자열 조립보다 안전하지만 권한 경계가 아니므로 untrusted 입력을 table name·expression으로 넘기지 않습니다.
- 코드 리뷰에서는 publish mart의 SELECT * 잔존, GROUP BY ALL granularity, PIVOT cardinality, macro의 pure SQL 여부를 점검합니다.
DuckDB의 SQL dialect는 분석가가 반복해서 쓰는 boilerplate를 잘 줄여줍니다. DBA 입장에서 중요한 건 "짧게 쓰는 문법"이 아니라, 분석 SQL을 덜 깨지고 검토하기 쉬운 형태로 만든다는 점입니다.
자주 쓰는 Friendly SQL
| 기능 | 용도 | 예시 |
|---|---|---|
GROUP BY ALL | select list 기준 group by 자동화 | 집계 컬럼 누락 방지 |
ORDER BY ALL | deterministic sample/review | 회귀 비교 |
SELECT * EXCLUDE | 넓은 테이블에서 일부 제외 | PII 컬럼 제외 |
SELECT * REPLACE | 컬럼 유지하며 일부 변환 | timezone/cast 보정 |
UNION BY NAME | 컬럼 순서가 다른 결과 결합 | schema evolution 대응 |
PIVOT/UNPIVOT | wide/long 변환 | 리포트용 mart |
SELECT
date_trunc('day', event_time) AS day,
event_name,
count(*) AS events
FROM events
GROUP BY ALL
ORDER BY ALL;SELECT
* EXCLUDE (raw_payload, internal_note),
event_time AT TIME ZONE 'Asia/Seoul' AS event_time_kst
FROM raw_events;PIVOT
PIVOT daily_events
ON event_name
USING sum(events)
GROUP BY day
ORDER BY day;PIVOT은 편리하지만 고 cardinality 컬럼에 쓰면 메모리와 컬럼 폭이 폭발합니다. 운영 mart라면 pivot 대상 값을 제한하거나,
long format을 유지한 채 pivot은 BI에서 처리하는 편이 낫습니다.
Macro
DuckDB macro는 반복 분석 로직을 catalog에 넣는 방법입니다.
CREATE OR REPLACE MACRO is_paid_plan(plan) AS
plan IN ('pro', 'team', 'enterprise');
CREATE OR REPLACE MACRO revenue_by_day(source_table) AS TABLE
SELECT
date_trunc('day', paid_at) AS day,
sum(amount) AS revenue
FROM query_table(source_table)
GROUP BY ALL;
FROM revenue_by_day('payments');macro는 SQL 문자열 조립보다 안전하지만 권한 경계는 아닙니다. untrusted 입력을 table name이나 expression으로 넘기는 구조는 피합니다.
코드 리뷰 기준
| 체크 | 이유 |
|---|---|
SELECT *가 publish mart에 남아 있지 않은가 | schema drift가 downstream을 깨뜨림 |
GROUP BY ALL이 의도한 granularity와 맞는가 | select list 변경이 group key를 바꿈 |
PIVOT 대상 cardinality가 제한되어 있는가 | OOM 위험 |
| macro가 hidden side effect 없이 pure SQL인가 | 재현성 |
ORDER BY ALL이 테스트 output에 있는가 | diff 안정성 |
2.0 변수와 변경 CTE
$name은 설정한 세션 변수를 식에서 참조합니다. 값이 없으면 오류가 나며, 미설정 변수에 NULL을 반환하는 getvariable()과 차이가 있습니다. 외부 입력은 계속 클라이언트 parameter binding으로 전달합니다. SET VARIABLE.
SET VARIABLE minimum_amount = 100;
SELECT * FROM (VALUES (80), (120)) AS v(amount)
WHERE amount >= $minimum_amount;2.0은 CTE 안의 INSERT·UPDATE·DELETE·COPY를 지원합니다. 변경 결과를 다음 단계에서 사용하면 RETURNING으로 연결하고, 선행 변경 완료에 의존하는 CTE에는 MATERIALIZED를 명시합니다. 파일 출력까지 데이터베이스 트랜잭션처럼 rollback된다고 가정하지 않습니다. 변경 CTE.
CREATE TEMP TABLE queue (id INTEGER, done BOOLEAN);
CREATE TEMP TABLE completed (id INTEGER);
INSERT INTO queue VALUES (1, true), (2, false);
WITH moved AS MATERIALIZED (
DELETE FROM queue WHERE done RETURNING id
)
INSERT INTO completed SELECT id FROM moved;
SELECT * FROM completed ORDER BY id; -- 12.0 중첩 스키마
스키마를 계층적으로 나눌 수 있습니다. 부모를 먼저 만들고 객체는 전체 경로로 참조합니다. 이름의 계층화는 권한 격리를 대신하지 않습니다. CREATE SCHEMA.
CREATE SCHEMA IF NOT EXISTS analytics;
CREATE SCHEMA IF NOT EXISTS analytics.reporting;
CREATE TABLE analytics.reporting.daily (day DATE, revenue DECIMAL(18,2));기존 lambda는 lambda x: ...로 이전합니다. FETCH FIRST와 GROUP BY의 UNNEST도 2.0 문법에 포함되지만, 여러 엔진에서 공유하는 SQL에는 대상 엔진의 parser 검증을 추가합니다.