딕셔너리

딕셔너리 (Dictionary)

ClickHouse의 딕셔너리는 내부 및 외부 소스의 데이터를 인메모리 키-값 형태로 제공하며, 초저지연 룩업 쿼리에 최적화되어 있어요. 여기서는 딕셔너리로 JOIN을 가속화하고, 쿼리/인덱스 시점에 데이터를 보강하는 방법을 설명해 드릴게요.

출처: 문서

본문

ClickHouse의 딕셔너리는 내부 및 외부 소스의 데이터를 인메모리 키-값 형태로 제공하며, 초저지연 룩업 쿼리에 최적화되어 있어요.

딕셔너리는 다음에 유용해요:

  • 특히 JOIN과 함께 쓸 때 쿼리 성능을 개선
  • 인제스트(ingest) 과정을 느리게 하지 않으면서 유입되는 데이터를 그 자리에서 보강

딕셔너리를 사용한 JOIN 가속화 (Speeding up joins using a Dictionary)

딕셔너리를 사용해 특정 유형의 JOIN을 가속화할 수 있어요. 바로 조인 키가 기반 키-값 저장소의 키 속성과 일치해야 하는 LEFT ANY 유형이에요.

이 경우 ClickHouse는 딕셔너리를 활용해 Direct Join을 수행할 수 있어요. 이는 ClickHouse의 가장 빠른 조인 알고리즘이며, 오른쪽 테이블의 기반 테이블 엔진이 저지연 키-값 요청을 지원할 때 적용할 수 있어요. ClickHouse에는 이를 제공하는 세 가지 테이블 엔진이 있어요: Join(기본적으로 미리 계산된 해시 테이블), EmbeddedRocksDB, Dictionary. 여기서는 딕셔너리 기반 접근 방식을 설명하지만, 메커니즘은 세 엔진 모두 동일해요.

direct join 알고리즘은 오른쪽 테이블이 딕셔너리에 의해 뒷받침되어, 조인할 데이터가 저지연 키-값 데이터 구조 형태로 이미 메모리에 존재해야 해요.

예시 (Example)

Stack Overflow 데이터 세트를 사용해 질문 하나를 답해 볼게요: Hacker News에서 SQL에 관한 가장 논란(controversial)이 많은 글은 무엇인가?

우리는 논란을 up/down 투표 수가 비슷한 글로 정의할게요. 이 절대 차이를 계산하는데, 0에 가까울수록 더 논란적이에요. 글은 최소한 up 10개, down 10개가 있어야 한다고 가정할게요. 투표하지 않는 글은 별로 논란적이지 않으니까요.

데이터가 정규화된 상태에서, 이 쿼리는 현재 posts와 votes 테이블을 사용한 JOIN이 필요해요:

WITH PostIds AS
(
         SELECT Id
         FROM posts
         WHERE Title ILIKE '%SQL%'
)
SELECT
    Id,
    Title,
    UpVotes,
    DownVotes,
    abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
INNER JOIN
(
    SELECT
         PostId,
         countIf(VoteTypeId = 2) AS UpVotes,
         countIf(VoteTypeId = 3) AS DownVotes
    FROM votes
    WHERE PostId IN (PostIds)
    GROUP BY PostId
    HAVING (UpVotes > 10) AND (DownVotes > 10)
) AS votes ON posts.Id = votes.PostId
WHERE Id IN (PostIds)
ORDER BY Controversial_ratio ASC
LIMIT 1

Row 1:
──────
Id:                     25372161
Title:                  How to add exception handling to SqlDataSource.UpdateCommand
UpVotes:                13
DownVotes:              13
Controversial_ratio: 0

1 rows in set. Elapsed: 1.283 sec. Processed 418.44 million rows, 7.23 GB (326.07 million rows/s., 5.63 GB/s.)
Peak memory usage: 3.18 GiB.

JOIN의 오른쪽에는 더 작은 데이터 세트를 사용하세요: 이 쿼리는 PostId 필터링이 외부 쿼리와 하위 쿼리 모두에 있어서 필요한 것보다 더 장황해 보일 수 있어요. 이것은 쿼리 응답 시간을 빠르게 보장하는 성능 최적화예요. 최적의 성능을 위해 항상 JOIN의 오른쪽이 더 작은 세트이고 가능한 한 작게 유지하세요. JOIN 성능 최적화와 사용 가능한 알고리즘에 대한 팁은 이 블로그 글 시리즈를 권장해요.

이 쿼리는 빠르지만, 좋은 성능을 얻으려면 JOIN을 신중히 작성해야 해요. 이상적으로는 메트릭을 계산하기 위해 블로그 하위 집합에 대한 UpVote와 DownVote 수를 보기 전에 글을 “SQL”을 포함하는 것으로 단순히 필터링하고 싶을 거예요.

딕셔너리 적용 (Applying a dictionary)

이 개념을 보여주기 위해 투표 데이터에 딕셔너리를 사용할게요. 딕셔너리는 보통 메모리에 보관되므로(ssd_cache가 예외) 데이터 크기에 주의해야 해요. votes 테이블 크기를 확인해 보면:

SELECT table,
        formatReadableSize(sum(data_compressed_bytes)) AS compressed_size,
        formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed_size,
        round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table IN ('votes')
GROUP BY table

┌─table───────────┬─compressed_size─┬─uncompressed_size─┬─ratio─┐
│ votes           │ 1.25 GiB        │ 3.79 GiB          │  3.04 │
└─────────────────┴─────────────────┴───────────────────┴───────┘

딕셔너리에는 데이터가 압축되지 않은 상태로 저장되므로, 모든 열을(우리는 그러지 않을 거지만) 딕셔너리에 저장하려면 최소 4GB의 메모리가 필요해요. 딕셔너리는 클러스터 전체에 복제되므로 이 메모리 양은 노드당 예약해야 해요.

아래 예시에서 딕셔너리의 데이터는 ClickHouse 테이블에서 비롯돼요. 이것이 딕셔너리의 가장 흔한 소스이지만, 파일, http, Postgres를 포함한 데이터베이스 등 여러 소스가 지원돼요. 아래에서 보여주듯 딕셔너리는 자동으로 새로고침될 수 있어, 자주 바뀌는 작은 데이터 세트가 direct join에 사용 가능하도록 보장하는 이상적인 방법이에요.

딕셔너리에는 룩업이 수행될 기본 키(primary key)가 필요해요. 이는 개념적으로 트랜잭션 데이터베이스의 기본 키와 동일하며 고유해야 해요. 위 쿼리는 조인 키인 PostId에 대한 룩업이 필요해요. 딕셔너리는 votes 테이블에서 각 PostId에 대한 up/down 투표 합계로 채워져야 해요. 딕셔너리 데이터를 얻는 쿼리는 다음과 같아요:

SELECT PostId,
   countIf(VoteTypeId = 2) AS UpVotes,
   countIf(VoteTypeId = 3) AS DownVotes
FROM votes
GROUP BY PostId

딕셔너리를 만들려면 다음 DDL이 필요해요. 위 쿼리를 사용하는 것에 주목하세요:

CREATE DICTIONARY votes_dict
(
  `PostId` UInt64,
  `UpVotes` UInt32,
  `DownVotes` UInt32
)
PRIMARY KEY PostId
SOURCE(CLICKHOUSE(QUERY 'SELECT PostId, countIf(VoteTypeId = 2) AS UpVotes, countIf(VoteTypeId = 3) AS DownVotes FROM votes GROUP BY PostId'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

0 rows in set. Elapsed: 36.063 sec.

자체 관리 OSS에서는 위 명령을 모든 노드에서 실행해야 해요. ClickHouse Cloud에서는 딕셔너리가 자동으로 모든 노드에 복제돼요. 위는 64GB RAM을 가진 ClickHouse Cloud 노드에서 실행됐으며 로드에 36초가 걸렸어요.

딕셔너리가 소비하는 메모리를 확인하려면:

SELECT formatReadableSize(bytes_allocated) AS size
FROM system.dictionaries
WHERE name = 'votes_dict'

┌─size─────┐
│ 4.00 GiB │
└──────────┘

특정 PostId에 대한 up/down 투표를 가져오는 것은 이제 간단한 dictGet 함수로 할 수 있어요. 아래에서 글 11227902의 값을 가져와 볼게요:

SELECT dictGet('votes_dict', ('UpVotes', 'DownVotes'), '11227902') AS votes

┌─votes──────┐
│ (34999,32) │
└────────────┘

이것을 앞선 쿼리에 활용하면 JOIN을 제거할 수 있어요:

WITH PostIds AS
(
        SELECT Id
        FROM posts
        WHERE Title ILIKE '%SQL%'
)
SELECT Id, Title,
        dictGet('votes_dict', 'UpVotes', Id) AS UpVotes,
        dictGet('votes_dict', 'DownVotes', Id) AS DownVotes,
        abs(UpVotes - DownVotes) AS Controversial_ratio
FROM posts
WHERE (Id IN (PostIds)) AND (UpVotes > 10) AND (DownVotes > 10)
ORDER BY Controversial_ratio ASC
LIMIT 3

3 rows in set. Elapsed: 0.551 sec. Processed 119.64 million rows, 3.29 GB (216.96 million rows/s., 5.97 GB/s.)
Peak memory usage: 552.26 MiB.

이 쿼리는 훨씬 단순할 뿐만 아니라 두 배 이상 빠르기도 해요! up/down 투표가 10개 이상인 글만 딕셔너리에 로드하고 미리 계산된 논란 값을만 저장하면 더 최적화할 수 있어요.

쿼리 시점 보강 (Query time enrichment)

딕셔너리를 사용해 쿼리 시점에 값을 룩업할 수 있어요. 이 값들은 결과로 반환되거나 집계에 사용될 수 있어요. 사용자 ID를 위치에 매핑하는 딕셔너리를 만들어 보자면:

CREATE DICTIONARY users_dict
(
  `Id` Int32,
  `Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM stackoverflow.users'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

이 딕셔너리를 사용해 글 결과를 보강할 수 있어요:

SELECT
        Id,
        Title,
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location
FROM posts
WHERE Title ILIKE '%clickhouse%'
LIMIT 5
FORMAT PrettyCompactMonoBlock

┌───────Id─┬─Title─────────────────────────────────────────────────────────┬─Location──────────────┐
│ 52296928 │ Comparison between two Strings in ClickHouse                  │ Spain                 │
│ 52345137 │ How to use a file to migrate data from mysql to a clickhouse? │ 中国江苏省Nanjing Shi   │
│ 61452077 │ How to change PARTITION in clickhouse                         │ Guangzhou, 广东省中国   │
│ 55608325 │ Clickhouse select last record without max() on all table      │ Moscow, Russia        │
│ 55758594 │ ClickHouse create temporary table                             │ Perm', Russia         │
└──────────┴───────────────────────────────────────────────────────────────┴───────────────────────┘

5 rows in set. Elapsed: 0.033 sec. Processed 4.25 million rows, 82.84 MB (130.62 million rows/s., 2.55 GB/s.)
Peak memory usage: 249.32 MiB.

앞선 조인 예시와 비슷하게, 같은 딕셔너리를 사용해 대부분의 글이 어디에서 오는지 효율적으로 알아낼 수 있어요:

SELECT
        dictGet('users_dict', 'Location', CAST(OwnerUserId, 'UInt64')) AS location,
        count() AS c
FROM posts
WHERE location != ''
GROUP BY location
ORDER BY c DESC
LIMIT 5

┌─location───────────────┬──────c─┐
│ India                  │ 787814 │
│ Germany                │ 685347 │
│ United States          │ 595818 │
│ London, United Kingdom │ 538738 │
│ United Kingdom         │ 537699 │
└────────────────────────┴────────┘

5 rows in set. Elapsed: 0.763 sec. Processed 59.82 million rows, 239.28 MB (78.40 million rows/s., 313.60 MB/s.)
Peak memory usage: 248.84 MiB.

인덱스 시점 보강 (Index time enrichment)

위 예시에서는 쿼리 시점에 딕셔너리를 사용해 조인을 제거했어요. 딕셔너리는 insert 시점에 행을 보강하는 데도 사용할 수 있어요. 보강 값이 변하지 않고 딕셔너리를 채울 수 있는 외부 소스에 있을 때 보통 적합해요. 이 경우 insert 시점에 행을 보강하면 쿼리 시점의 딕셔너리 룩업을 피할 수 있어요.

Stack Overflow에서 사용자의 Location이 절대 변하지 않는다고 가정해 볼게요(실제로는 변하지만) — 구체적으로 users 테이블의 Location 열이요. UserId를 포함하는 posts 테이블을 위치별로 분석 쿼리를 하고 싶다고 가정해 봐요.

딕셔너리는 users 테이블에 의해 뒷받침되는 사용자 ID에서 위치로의 매핑을 제공해요:

CREATE DICTIONARY users_dict
(
    `Id` UInt64,
    `Location` String
)
PRIMARY KEY Id
SOURCE(CLICKHOUSE(QUERY 'SELECT Id, Location FROM users WHERE Id >= 0'))
LIFETIME(MIN 600 MAX 900)
LAYOUT(HASHED())

Id < 0인 사용자는 시스템 사용자이므로 빼고, Hashed 딕셔너리 유형을 사용할 수 있게 해요.

posts 테이블에 대해 insert 시점에 이 딕셔너리를 활용하려면 스키마를 수정해야 해요:

CREATE TABLE posts_with_location
(
    `Id` UInt32,
    `PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
     ...
    `Location` MATERIALIZED dictGet(users_dict, 'Location', OwnerUserId::'UInt64')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CommentCount)

위 예시에서 Location은 MATERIALIZED 열로 선언돼 있어요. 이는 그 값이 INSERT 쿼리의 일부로 제공될 수 있고 항상 계산된다는 뜻이에요.

ClickHouse는 DEFAULT 열도 지원해요(값을 넣거나, 제공되지 않으면 계산). 자세한 내용은 여기를 참고해요.

테이블을 채우려면 S3에서 일반적인 INSERT INTO SELECT를 사용할 수 있어요:

INSERT INTO posts_with_location SELECT Id, PostTypeId::UInt8, AcceptedAnswerId, CreationDate, Score, ViewCount, Body, OwnerUserId, OwnerDisplayName, LastEditorUserId, LastEditorDisplayName, LastEditDate, LastActivityDate, Title, Tags, AnswerCount, CommentCount, FavoriteCount, ContentLicense, ParentId, CommunityOwnedDate, ClosedDate FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/stackoverflow/parquet/posts/*.parquet')

0 rows in set. Elapsed: 36.830 sec. Processed 238.98 million rows, 2.64 GB (6.49 million rows/s., 71.79 MB/s.)

이제 대부분의 글이 어디에서 오는지 위치 이름을 얻을 수 있어요:

SELECT Location, count() AS c
FROM posts_with_location
WHERE Location != ''
GROUP BY Location
ORDER BY c DESC
LIMIT 4

┌─Location───────────────┬──────c─┐
│ India                  │ 787814 │
│ Germany                │ 685347 │
│ United States          │ 595818 │
│ London, United Kingdom │ 538738 │
└────────────────────────┴────────┘

4 rows in set. Elapsed: 0.142 sec. Processed 59.82 million rows, 1.08 GB (420.73 million rows/s., 7.60 GB/s.)
Peak memory usage: 666.82 MiB.

고급 딕셔너리 주제 (Advanced dictionary topics)

딕셔너리 레이아웃 고르기, 딕셔너리 vs JOIN을 언제 쓸지, 딕셔너리 사용량 모니터링에 대한 지침은 딕셔너리 모범 사례를 참고해요.

딕셔너리 새로고침 (Refreshing dictionaries)

딕셔너리의 LIFETIME을 MIN 600 MAX 900으로 지정했어요. LIFETIME은 딕셔너리의 갱신 간격이며, 여기서 값은 600~900초 사이의 무작위 간격으로 주기적 재로드가 일어나게 해요. 많은 서버에서 갱신할 때 딕셔너리 소스에 부하를 분산시키기 위해 이 무작위 간격이 필요해요. 갱신 중에도 딕셔너리의 이전 버전을 쿼리할 수 있으며, 초기 로드만 쿼리를 차단해요. (LIFETIME(0))으로 설정하면 딕셔너리가 갱신되지 않는다는 점에 주의하세요. SYSTEM RELOAD DICTIONARY 명령으로 딕셔너리를 강제로 다시 로드할 수 있어요.

ClickHouse와 Postgres 같은 데이터베이스 소스의 경우, 주기적 간격 대신 딕셔너리가 실제로 변경되었을 때만 업데이트하는 쿼리를 설정할 수 있어요(쿼리 응답이 이를 결정). 자세한 내용은 여기에서 확인할 수 있어요.

기타 딕셔너리 유형 (Other dictionary types)

ClickHouse는 계층형(Hierarchical), 폴리곤(Polygon), 정규 표현식(Regular Expression) 딕셔너리도 지원해요.

더 알아보기 (Learn more)