ClickHouse의 딕셔너리: JOIN 없이 빠른 조회
1. 딕셔너리가 필요한 이유 — 참조 데이터의 JOIN 문제
온라인 카지노로 돌아가 봅시다. bets 테이블에는 1부터 20까지의 숫자인 sport_id가 저장되어 있습니다. 하지만 보고서에서는 스포츠 이름인 "축구", "하키", "테니스"를 보여줘야 합니다. 이 정보는 보통 별도의 참조 테이블 sports에 있습니다.
-- JOIN을 사용한 느린 쿼리
SELECT
b.user_id,
s.name AS sport_name,
sum(b.amount) AS total
FROM bets b
JOIN sports s ON b.sport_id = s.id
GROUP BY b.user_id, s.name;bets에 10억 개의 행이 있고 sports에 20개의 행이 있는 경우, 이 JOIN은 데이터 청크마다 참조 테이블을 복사합니다. ClickHouse는 브로드캐스트 조인(작은 테이블을 모든 샤드에 전송)을 수행하는데, 이는 빠르지만 여전히 메모리와 CPU를 소모합니다.
딕셔너리는 이 문제를 다르게 해결합니다. 딕셔너리는 ClickHouse 내부에 상주하는 인메모리 참조 테이블입니다. JOIN을 실행하지 않고도 키를 조회하여 값을 마이크로초 단위로 얻을 수 있습니다.
실생활 비유: 딕셔너리는 시험을 위한 컨닝 페이퍼와 같습니다. 20개의 행 목록이 있습니다: "1 = 축구, 2 = 하키...". ID로 스포츠 이름을 찾아야 할 때, 두꺼운 참고서(디스크)를 보러 도서관에 가는 대신 컨닝 페이퍼(메모리)를 한눈에 보기만 하면 됩니다. 수천 배 더 빠릅니다.
ClickHouse에서 이것이 중요한 이유: ClickHouse는 데이터를 디스크에 저장하며, JOIN을 통해 작은 테이블을 읽어도 디스크 작업이 필요합니다. 딕셔너리는 메모리(압축 및 최적화된 형태)에 상주하며, 접근은 단순히 RAM에서 읽는 것입니다.
2. 딕셔너리 유형 — 구조 선택 방법
ClickHouse는 다음에 따라 여러 딕셔너리 유형(LAYOUT)을 제공합니다:
- 데이터 크기(키 개수),
- 키 유형(단일 또는 복합),
- 범위 조회 필요 여부(예: 날짜별 환율).
| 유형 | 사용 시기 | 최대 키 | 특징 |
|---|---|---|---|
flat |
매우 작은 딕셔너리(최대 500k 키) | 500,000 | 가장 빠름, 배열에 저장. 키는 정수(UInt*)여야 함. |
hashed |
중간 크기 딕셔너리(수백만 키) | 무제한 | 해시 테이블. 모든 키 유형에 적합. flat보다 약간 느림. |
sparse_hashed |
매우 큰 딕셔너리(수천만 개) | 매우 많음 | 메모리 절약(null 값 미저장), 약간 느림. |
range_hashed |
날짜 범위(날짜별 환율) | 무제한 | 키 + 범위(시작, 끝). get(key, date) 조회 가능. |
complex_key_hashed |
복합 키(예: market_id, selection_id) |
무제한 | 키는 여러 필드의 튜플. |
ip_trie |
IP 주소(접두사 조회) | 최대 500k | GeoIP용: IP로 국가/도시 찾기. |
선택 방법:
- 키가 500k 미만이고 정수형 →
flat(최대 속도). - 키가 500k 이상이거나 정수가 아님 →
hashed. - 키가 매우 많고 null 값이 많음 →
sparse_hashed. - 날짜 기반 조회 필요 →
range_hashed. - 복합 키(여러 필드) →
complex_key_hashed.
비유: flat은 번호가 매겨진 서랍장(인덱스 = 번호)과 같습니다. 17번 서랍으로 바로 갑니다. hashed는 저자의 성을 해싱하여 선반을 먼저 계산하는 도서관 목록과 같습니다. range_hashed는 날짜를 알고 문서를 검색하는 아카이브와 같습니다.
3. 데이터 소스 — 딕셔너리가 데이터를 가져오는 곳
딕셔너리는 다양한 소스(SOURCE)에서 데이터를 채울 수 있습니다. ClickHouse는 지정된 간격(LIFETIME)으로 소스에서 딕셔너리를 주기적으로 업데이트합니다.
지원되는 소스:
CLICKHOUSE— 다른 ClickHouse 테이블MYSQL— MySQL 테이블POSTGRESQL— PostgreSQL 테이블HTTP— REST API (JSON 또는 XML)FILE— 로컬 파일 (CSV, TSV)REDIS— Redis (키-값)MONGODB— MongoDB 컬렉션
MySQL 예시:
CREATE DICTIONARY currencies_dict
(
code String,
name String,
rate Decimal(10,4)
)
PRIMARY KEY code
SOURCE(MYSQL(
host 'mysql-host'
port 3306
user 'reader'
password 'secret'
db 'reference'
table 'currencies'
))
LIFETIME(MIN 3600 MAX 7200) -- 1-2시간마다 업데이트
LAYOUT(HASHED());편리한 이유: 통화 참조 데이터는 재무 부서에서 유지 관리하는 외부 MySQL 데이터베이스에서 한 시간에 한 번 업데이트할 수 있습니다. ClickHouse가 자동으로 변경 사항을 가져오므로 ETL 스크립트를 작성할 필요가 없습니다.
4. ClickHouse 테이블에서 딕셔너리 생성 — 단계별
가장 일반적인 시나리오: ClickHouse에 이미 참조 테이블이 있고 빠른 조회를 위해 딕셔너리로 전환하려는 경우입니다.
1단계: 참조 테이블 생성 (없는 경우)
CREATE TABLE sports
(
id UInt32, -- 스포츠 ID (1, 2, 3...)
name String, -- '축구', '하키', '테니스'
category String -- '팀', '개인', 'e스포츠'
)
ENGINE = MergeTree()
ORDER BY id;
-- 데이터 삽입
INSERT INTO sports VALUES (1, '축구', '팀'), (2, '하키', '팀'), (3, '테니스', '개인');2단계: 이 테이블을 기반으로 딕셔너리 생성
CREATE DICTIONARY sports_dict
(
id UInt32, -- 키 컬럼
name String, -- 조회할 값
category String -- 또 다른 값
)
PRIMARY KEY id -- 조회 키
SOURCE(CLICKHOUSE(
host 'localhost'
port 9000
user 'default'
password ''
db 'default'
table 'sports'
))
LIFETIME(MIN 300 MAX 600) -- 5-10분마다 업데이트
LAYOUT(HASHED()); -- 20개 레코드의 경우 flat도 괜찮지만, hashed도 작동매개변수 설명:
PRIMARY KEY id— 조회에 사용되는 컬럼. 고유해야 함.SOURCE(CLICKHOUSE(...))— 데이터 소스. localhost뿐만 아니라 모든 호스트를 지정할 수 있음.LIFETIME(MIN 300 MAX 600)— 딕셔너리는 5~10분마다 완전히 다시 로드됨. MIN과 MAX는 모든 서버의 모든 딕셔너리가 동시에 업데이트되는 것을 방지하기 위해 무작위화에 사용됨.LAYOUT(HASHED())— 인메모리 구조. 20개 레코드의 경우flat이 더 좋지만, 예시로hashed를 유지함.
생성 후 발생하는 일: ClickHouse는 전체 sports 테이블을 읽어 해시 테이블로 메모리에 로드합니다. 이제 dictGet을 사용하여 빠르게 접근할 수 있습니다.
5. 쿼리에서 딕셔너리 사용 — dictGet과 그 외 함수
실제 마법은 SELECT에서 시작됩니다. JOIN sports 대신 딕셔너리 함수를 사용합니다.
dictGet — 주요 함수
-- sport_id로 스포츠 이름 가져오기
SELECT
user_id,
sport_id,
dictGet('sports_dict', 'name', sport_id) AS sport_name,
amount
FROM bets
LIMIT 10;구문: dictGet('딕셔너리_이름', '값_컬럼', 키)
dictGetOrDefault — 기본값 포함
-- sport_id를 찾을 수 없으면 '알 수 없음' 반환
SELECT
user_id,
sport_id,
dictGetOrDefault('sports_dict', 'name', sport_id, '알 수 없음') AS sport_name
FROM bets;dictHas — 키 존재 여부 확인
-- 유효하지 않은 sport_id를 가진 베팅 찾기
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0; -- 딕셔너리에 없는 sport_id 반환집계와 함께 사용하는 전체 예시
-- JOIN 없이 총 베팅 금액 기준 상위 5개 스포츠!
SELECT
dictGet('sports_dict', 'name', sport_id) AS sport_name,
sum(amount) AS total_amount,
count() AS bet_count
FROM bets
WHERE created_at >= today() - 7
GROUP BY sport_id
ORDER BY total_amount DESC
LIMIT 5;JOIN보다 빠른 이유: 디스크 읽기 없음, 참조 테이블을 샤드에 분산할 필요 없음, 쿼리 시 해싱 없음. 딕셔너리는 각 ClickHouse 노드의 메모리에 이미 있습니다.
6. 복합 키 — Tuple과 함께 사용하는 dictGet
키가 여러 필드로 구성된 경우(예: market_id + selection_id), LAYOUT(COMPLEX_KEY_HASHED())를 사용하고 키를 튜플로 전달합니다.
복합 키를 가진 딕셔너리 생성:
-- 배당률 딕셔너리: (market_id, selection_id) → 배당률 값
CREATE DICTIONARY odds_dict
(
market_id UInt32,
selection_id UInt32,
odds_value Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id) -- 복합 키!
SOURCE(CLICKHOUSE(
table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED()); -- complex_key여야 함!쿼리에서 사용:
-- 특정 마켓과 결과의 배당률 가져오기
SELECT
bet_id,
market_id,
selection_id,
dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;튜플이란? 튜플은 괄호로 묶인 값들의 그룹입니다. tuple(market_id, selection_id)는 (100, 5)와 같은 키를 만듭니다.
7. 범위 딕셔너리 — 과거 데이터용 (날짜별 환율)
매일 변하는 과거 환율 데이터가 있다고 가정해 봅시다. 유로화로 된 각 베팅에 대해 베팅 날짜의 환율이 필요합니다.
소스 테이블 (예: MySQL):
| currency | start_date | end_date | rate |
|---|---|---|---|
| EUR | 2025-01-01 | 2025-01-31 | 1.05 |
| EUR | 2025-02-01 | 2025-02-28 | 1.08 |
| EUR | 2025-03-01 | 2099-12-31 | 1.10 |
범위 딕셔너리 생성:
CREATE DICTIONARY eur_rates_dict
(
currency String,
start_date Date,
end_date Date,
rate Decimal(10,4)
)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'eur_rates'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(RANGE_HASHED()) -- 특수 유형
RANGE(MIN start_date MAX end_date); -- 범위 컬럼 지정사용:
-- 각 EUR 베팅에 대해 베팅 날짜의 환율 가져오기
SELECT
bet_id,
amount_eur,
created_at,
dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';ClickHouse는 주어진 통화에 대해 created_at이 start_date와 end_date 사이에 있는 레코드를 자동으로 찾습니다.
비유: 가격 변동의 달력과 같습니다. "3월 15일의 환율을 알려줘"라고 말하면 딕셔너리는 달력을 확인합니다. 3월 15일은 3월 1일~3월 31일 구간에 속하며, 환율은 1.10입니다.
8. 딕셔너리 모니터링 — system.dictionaries
딕셔너리의 상태를 이해하려면 시스템 테이블 system.dictionaries가 있습니다.
SELECT *
FROM system.dictionaries
WHERE name = 'sports_dict';유용한 컬럼:
| 컬럼 | 설명 |
|---|---|
status |
LOADED — 로드됨, LOADING — 로딩 중, FAILED — 오류 |
origin |
소스 (ClickHouse, MySQL...) |
type |
유형 (flat, hashed, range_hashed...) |
key |
키 유형 |
attribute.names |
사용 가능한 컬럼 |
bytes_allocated |
메모리 사용량 (바이트) |
query_count |
조회 수 |
hit_rate |
적중률 (높을수록 좋음) |
load_factor |
딕셔너리 채움 정도 (hashed의 경우) |
creation_time |
로드된 시간 |
last_exception |
상태가 FAILED인 경우 오류 내용 |
메모리 모니터링:
SELECT
name,
formatReadableSize(bytes_allocated) AS memory,
query_count,
hit_rate
FROM system.dictionaries
WHERE status = 'LOADED'
ORDER BY bytes_allocated DESC;딕셔너리가 기가바이트를 차지한다면 잘못된 LAYOUT(예: sparse_hashed 대신 hashed)을 선택했을 수 있습니다.
9. 핫 리로드 — SYSTEM RELOAD DICTIONARY
딕셔너리는 LIFETIME에 따라 자동으로 업데이트됩니다. 하지만 때로는 강제로 업데이트해야 할 때가 있습니다:
- 소스의 데이터를 수정했는데 10분을 기다리기 싫을 때.
- 딕셔너리가 실패했는데(예: 소스에 접근 불가) 문제를 해결했을 때.
-- 특정 딕셔너리 리로드
SYSTEM RELOAD DICTIONARY sports_dict;
-- 모든 딕셔너리 리로드
SYSTEM RELOAD DICTIONARIES;발생하는 일: ClickHouse가 소스(예: sports 테이블)를 다시 읽고 메모리의 딕셔너리 내용을 교체합니다. 리로드 중에는 dictGet을 사용하는 쿼리가 대기하거나(또는 버전에 따라 이전 데이터를 반환할 수 있음)합니다. 중요 시스템의 경우 야간에 리로드를 수행하세요.
딕셔너리가 올바르게 로드되었는지 확인:
SELECT status, last_exception
FROM system.dictionaries
WHERE name = 'sports_dict';상태가 LOADED이면 정상입니다. FAILED이면 last_exception을 확인하세요.
10. 예제 아키텍처: 베팅 플랫폼의 모든 참조 딕셔너리
완전한 베팅 플랫폼 아키텍처를 상상해 보세요. 수십 개의 참조 딕셔너리가 쿼리에서 데이터를 보강하는 데 지속적으로 사용됩니다.
생성할 딕셔너리:
-- 1. 스포츠 (20개 레코드, FLAT)
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());
-- 2. 리그/챔피언십 (1만 개 레코드, HASHED)
CREATE DICTIONARY leagues_dict (id UInt32, name String, sport_id UInt32, country_id UInt32)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'leagues'))
LIFETIME(3600) LAYOUT(HASHED());
-- 3. 국가 (200개 레코드, FLAT)
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT()); -- 거의 변경되지 않음, 하루에 한 번 업데이트
-- 4. 과거 환율을 포함한 통화 (RANGE)
CREATE DICTIONARY exchange_rates_dict (currency String, start_date Date, end_date Date, rate Decimal(10,4))
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'exchange_rates'))
LIFETIME(3600) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);
-- 5. 국가 및 베팅 유형별 수수료 (COMPLEX_KEY)
CREATE DICTIONARY commission_dict (country_id UInt32, bet_type String, commission Decimal(5,2))
PRIMARY KEY (country_id, bet_type)
SOURCE(CLICKHOUSE(table 'commissions'))
LIFETIME(7200) LAYOUT(COMPLEX_KEY_HASHED());단일 쿼리에서 사용:
SELECT
b.user_id,
dictGet('sports_dict', 'name', b.sport_id) AS sport_name,
dictGet('leagues_dict', 'name', b.league_id) AS league_name,
dictGet('countries_dict', 'name', dictGet('leagues_dict', 'country_id', b.league_id)) AS country_name,
b.amount_eur * dictGet('exchange_rates_dict', 'rate', tuple('EUR', toDate(b.created_at))) AS amount_usd,
dictGet('commission_dict', 'commission', tuple(dictGet('leagues_dict', 'country_id', b.league_id), 'prematch')) AS commission
FROM bets b
WHERE b.created_at >= today() - 7;이 접근 방식의 장점:
- 속도: JOIN 없음, 직접 메모리 조회만.
- 가독성: 코드가 더 명확해짐 — 어떤 딕셔너리가 사용되는지 즉시 알 수 있음.
- 관리 용이성: 참조 데이터 업데이트(예: 스페인 수수료)가 ETL 스크립트가 아닌 한 곳에서 이루어짐.
- 메모리 효율성: 딕셔너리는 압축되어 저장되므로, 종종 비정규화된 테이블 컬럼보다 공간을 덜 차지함.
딕셔너리를 사용하지 않으면 어떻게 될까요? 데이터를 비정규화하거나(모든 베팅 행에 스포츠 이름을 반복 — 데이터 양이 10배 이상 증가) 모든 집계에서 JOIN을 수행해야 합니다(수십억 행에서 느리고 고통스러움).
다음 단계
이제 딕셔너리에 대해 모든 것을 알게 되었습니다. 다음 주제:
- HTTP를 통한 딕셔너리 업데이트 — 외부 API에서 데이터를 가져오는 방법.
- 구체화된 뷰에서 딕셔너리 사용 — 데이터 사전 보강용.
- 딕셔너리 클러스터링 — ClickHouse 클러스터(Distributed)에서 딕셔너리가 동작하는 방식.
결론: 딕셔너리는 ClickHouse에서 참조 데이터를 다루는 데 필수적인 도구입니다. 작은 테이블과의 느린 JOIN을 번개처럼 빠른 메모리 조회로 바꿔줍니다. 규칙은 간단합니다: 참조 데이터가 1분에 한 번 이상 변경되지 않고 크기가 RAM에 저장할 수 있는 정도라면 딕셔너리로 만드세요. 그러면 쿼리가 감사할 것입니다.
← 이전 글: ClickHouse의 TTL: 자동 데이터 수명 주기 관리
→ 다음 글: 특별한 ClickHouse 엔진: MergeTree가 적합하지 않을 때
— Editorial Team
아직 댓글이 없습니다.