labs/korean-log-search

Search ClickStack logs for a Korean word and you can get nothing back, with no error. ClickHouse has no Korean morphological analyser, and the body index of ClickStack's log table splits text into words, so 결제 never matches 결제가 or 결제를 — the word with a particle attached. This lab shows that on Managed ClickStack, the two settings that fix it, and what the fix costs, measured on the same data.

Verified on ClickHouse Cloud 26.8.1.605 (2 replicas, ap-northeast-2) and Managed ClickStack HyperDX 2.41.0-sha38acb5a, 2026-11-02: bin/run.sh end to end (checks K1–K9 PASS), then the searches below in the ClickStack UI. Synthetic data, one run.

What it shows

	Body index	ClickStack source setting Use Text Index	Search 결제
ClickStack default	lower(Body), text(tokenizer = 'splitByNonAlpha')	Auto	0 results — the index has 결제가, not 결제
Index changed only	lower(Body), text(tokenizer = ngrams(2))	Auto	0 results — Auto falls back to hasToken, which uses no index
Both changed	lower(Body), text(tokenizer = ngrams(2))	Force enable	every row with 결제 in it, through the index

A wildcard search (*결제*) returns the right rows on either index, as a LIKE that the default word index cannot narrow.

The two settings

1. Table: replace the body index with ngrams(2). One expression can hold only one text index, so the word index is dropped, not kept beside it.

sql ALTER TABLE otel_logs DROP INDEX idx_lower_body; ALTER TABLE otel_logs ADD INDEX idx_lower_body_ngram lower(Body) TYPE text(tokenizer = ngrams(2)); ALTER TABLE otel_logs MATERIALIZE INDEX idx_lower_body_ngram; -- existing parts; rewrites them

2. ClickStack source: Use Text Index → Force enable. Team Settings → Data → the log source → Configure Optional Fields → Use Text Index. In Auto, HyperDX uses a text index for a plain word only when its tokenizer is splitByNonAlpha; for any other tokenizer it renders hasToken, which no text index serves.

How HyperDX chooses (read in packages/common-utils/src/queryParser.ts, hyperdxio/hyperdx main, 2026-11-01, and confirmed in system.query_log from the UI): it takes the first text index in system.data_skipping_indices whose expression names the searched column, and builds hasAllTokens(lower(Body), lower('<term>')) when that index is on lower(Body). A wildcard becomes lower(Body) LIKE lower('%<term>%').

In the UI

The default word index, 결제: no results.

The same index, *결제*: the rows are there (40,522 in this 15-minute range).

The ngram index with Use Text Index still on Auto: still no results.

The ngram index with Force enable: the same 40,522 rows.

The cost of 2-grams, in one screen: 환불요청 returns the row that has it, and a row that only has its pieces apart (환불 처리 불요 재요청).

Results

Checks (sql/03_checks.sql, every search in WHERE; ground truth from plain string functions on the unindexed table):

#	Shows	Expected	Actual
K1	word index: 결제 finds nothing	0	0
K1+	control: the rows exist	yes	yes (599,838)
K2	ngram + hasAllTokens (Force enable): 결제 finds every row	599,838	599,838
K3	ngram + hasToken (Auto): 결제 finds nothing	0	0
K4	word index: a standalone word 환불요청 is exact	3	3
K5	ngram: 환불요청 also matches the 3 decoy rows	6	6
K6	wildcard *환불요청* is exact on both tables	3 / 3	3 / 3
K7	word index: error matches the word only	300,844	300,844
K8	ngram: error matches as a substring (errors too)	599,863	599,863
K9	ngram: a one-character term 환 finds nothing	0	0
K9+	control: *환* (LIKE) finds the rows	6	6

Granules read for the UI-shaped searches over the last 15 minutes (sql/04_results.sql, from system.query_log; the range alone covers 37–38 granules):

Search	Index	Granules	Rows
결제, Auto	word	0	0
결제, Auto	ngram	38	0
결제, Force enable	ngram	38	rows (common term: the index cannot narrow it)
환불요청, Auto	word	1	1
환불요청, Auto	ngram	38	1
환불요청, Force enable	ngram	2	2 (one false)
*환불요청*	word	37	1
*환불요청*	ngram	2	1

Size and insert cost (3,000,000 rows, one part per partition after OPTIMIZE FINAL):

	word index	ngram index
Body index, compressed	42.47 MiB	70.71 MiB (1.67×)
CPU to copy the rows in (no index: 1.59 s)	3.08 s	5.72 s
Index build CPU above the plain copy	1.49 s	4.13 s (2.8×)

The data itself compresses to 11.6 MiB because the messages come from ten templates, so the index-to-data ratio here (3.6× and 6.1×) says nothing about real logs. Compare the two indexes with each other.

Trade-offs of the change

• Korean words are found, particles attached or not, and rare ones through the index.
• English becomes substring search. error also matches errors; very short terms (id, ok) match almost every row and the index cannot narrow them.
• False matches: a term of three or more characters matches any row that holds all its 2-grams, even apart (K5). Phrases with a space are safe — HyperDX adds a LIKE on the whole phrase.
• One-character terms find nothing through the index (K9); use the wildcard.
• Bigger index, more insert CPU (table above); MATERIALIZE INDEX rewrites existing parts.
• hasAllTokens outside WHERE is not stable. On the ngram table, SELECT countIf(hasAllTokens(lower(Body), lower('결제'))) returned 599,838 with no WHERE and 0 once WHERE Timestamp > now() - INTERVAL 1 HOUR was added (same session, same build). The search page puts the term in WHERE; charts, alerts and dashboards were not checked.

Running it

Needs clickhouse client and a ClickHouse Cloud service with Managed ClickStack. Credentials come from the environment (.env.example); nothing outside the database korean_log_search is touched.

cp .env.example .env    # fill in CH_HOST, CH_USER, CH_PASSWORD
set -a; . ./.env; set +a
bin/run.sh              # install, checks, the UI-shaped searches, results

Then, for the UI: in ClickStack add two log sources, korean_log_search.logs_word and korean_log_search.logs_ngram (Timestamp column Timestamp, apply the detected Service, Level and Body fields), search 결제, *결제* and 환불요청 in the last 15 minutes, switch the ngram source to Force enable and search again. The rows end at load time; re-run bin/run.sh install to move them to now. Remove everything with bin/run.sh drop and delete the two sources.

Honest limits

• Synthetic data from ten templates; index sizes and CPU on real logs will differ — measure on a sample.
• One run on one service; CPU comes from one INSERT … SELECT per table.
• Charts, alerts and dashboards with these indexes were not checked.

Layout

labs/korean-log-search/
├── README.md
├── .env.example
├── bin/run.sh            install · check · searches · results · drop
├── sql/
│   ├── 01_schema.sql     four tables shaped like otel_logs (no index, copy, word, ngram)
│   ├── 02_load.sql       3,000,000 rows, ten templates, two rare markers
│   ├── 03_checks.sql     K1–K9 with ground truth
│   ├── 04_results.sql    index size, copy CPU, granules per search
│   └── 99_drop.sql
└── images/               ClickStack UI, 2026-11-02

cp .env.example .env    # CH_HOST, CH_USER, CH_PASSWORD 채우기
set -a; . ./.env; set +a
bin/run.sh              # 설치, 검사, 화면 형태 검색, 결과

labs/korean-log-search/
├── README.md
├── .env.example
├── bin/run.sh            install · check · searches · results · drop
├── sql/
│   ├── 01_schema.sql     otel_logs 형태 표 넷(색인 없음, 복사, 단어, ngram)
│   ├── 02_load.sql       3,000,000행, 템플릿 10개, 드문 표식 둘
│   ├── 03_checks.sql     K1–K9, 정답과 비교
│   ├── 04_results.sql    색인 크기, 복사 CPU, 검색별 granule
│   └── 99_drop.sql
└── images/               ClickStack 화면, 2026-11-02