Adding Full‑Text Search to ClickHouse with Quickwit’s Search‑Stream Feature

Quickwit search‑stream endpoint

Quickwit exposes a REST endpoint that can stream up to 50 million matching IDs per second. The guide demonstrates adding full‑text search to the OLAP database ClickHouse using the public GitHub Archive dataset (PullRequestEvent, IssuesEvent, etc.).

Installation and Quickwit server start

curl -L https://install.quickwit.io | sh
cd quickwit-v*/
./quickwit run

Index configuration and creation

Download a ready‑made index configuration that defines the fields needed for the tutorial:

curl -o gh-archive-index-config.yaml https://raw.githubusercontent.com/quickwit-oss/quickwit/main/config/tutorials/gh-archive/index-config-for-clickhouse.yaml
./quickwit index create --index-config gh-archive-index-config.yaml

The YAML config defines: id – u64, fast field created_at –

datetime

event_type

– text with raw tokenizer title and body – text with default tokenizer

It also sets title and body as the default search fields.

Ingesting the dataset

Download the compressed NDJSON file that contains only the textual fields and ingest it into the Quickwit index:

wget https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2021-12-text-only.json.gz
gunzip -c gh-archive-2021-12-text-only.json.gz | ./quickwit index ingest --index gh-archive

Streaming IDs from Quickwit

Stream matching IDs in CSV format:

curl "http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=tantivy&output_format=csv&fast_field=id"

For high‑throughput consumption ClickHouse’s RowBinary format is used. The request returns 217 469 IDs in 0.068 s (≈3.19 M rows/s, 25.55 MB/s).

ClickHouse setup

Create a database and a table that stores the full GitHub event archive:

CREATE DATABASE "gh-archive";
USE "gh-archive";
CREATE TABLE github_events (
    id UInt64,
    event_type Enum('CommitCommentEvent' = 1, 'CreateEvent' = 2, 'DeleteEvent' = 3, 'ForkEvent' = 4,
                    'GollumEvent' = 5, 'IssueCommentEvent' = 6, 'IssuesEvent' = 7, 'MemberEvent' = 8,
                    'PublicEvent' = 9, 'PullRequestEvent' = 10, 'PullRequestReviewCommentEvent' = 11,
                    'PushEvent' = 12, 'ReleaseEvent' = 13, 'SponsorshipEvent' = 14, 'WatchEvent' = 15,
                    'GistEvent' = 16, 'FollowEvent' = 17, 'DownloadEvent' = 18, 'PullRequestReviewEvent' = 19,
                    'ForkApplyEvent' = 20, 'Event' = 21, 'TeamAddEvent' = 22),
    actor_login LowCardinality(String),
    repo_name LowCardinality(String),
    created_at Int64,
    action Enum('none' = 0, 'created' = 1, 'added' = 2, 'edited' = 3, 'deleted' = 4,
                'opened' = 5, 'closed' = 6, 'reopened' = 7, 'assigned' = 8, 'unassigned' = 9,
                'labeled' = 10, 'unlabeled' = 11, 'review_requested' = 12,
                'review_request_removed' = 13, 'synchronize' = 14, 'started' = 15,
                'published' = 16, 'update' = 17, 'create' = 18, 'fork' = 19, 'merged' = 20),
    comment_id UInt64,
    body String,
    ref LowCardinality(String),
    number UInt32,
    title String,
    labels Array(LowCardinality(String)),
    additions UInt32,
    deletions UInt32,
    commit_id String
) ENGINE = MergeTree ORDER BY (event_type, repo_name, created_at);

Import the full JSON archive (including events without text) into ClickHouse:

wget https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2021-12.json.gz
gunzip -c gh-archive-2021-12.json.gz | clickhouse-client -d gh-archive --query="INSERT INTO github_events FORMAT JSONEachRow"

Verify the import with a sample query that lists the top repositories by event count:

SELECT repo_name, count() AS stars
FROM github_events
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 5;

Using ClickHouse URL table engine to query Quickwit

The URL table engine can query a remote HTTP endpoint. Create a virtual table that points to the Quickwit search‑stream endpoint and join it with the ClickHouse data.

SELECT count(*)
FROM url('http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=log4j+OR+log4shell&fast_field=id&output_format=click_house_row_binary',
          RowBinary,
          'id UInt64');

Result:

┌─count()─┐
│ 217469 │
└─────────┘
Elapsed: 0.068 sec. Processed 217.47 k rows, 1.74 MB (3.19 M rows/s, 25.55 MB/s).

A more complex query joins the streamed IDs with the github_events table to count daily events for the terms log4j or log4shell:

SELECT count(*), toDate(fromUnixTimestamp64Milli(created_at)) AS date
FROM github_events
WHERE id IN (
    SELECT id
    FROM url('http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=log4j+OR+log4shell&fast_field=id&output_format=click_house_row_binary',
                RowBinary,
                'id UInt64')
)
GROUP BY date;

Result (excerpt):

┌─count()─┬───────date─┐
│      96 │ 2021-12-01 │
│      66 │ 2021-12-02 │
│      70 │ 2021-12-03 │
│      62 │ 2021-12-04 │
│      67 │ 2021-12-05 │
│     167 │ 2021-12-06 │
│     140 │ 2021-12-07 │
│     104 │ 2021-12-08 │
│     157 │ 2021-12-09 │
│   88110 │ 2021-12-10 │
│    2937 │ 2021-12-11 │
│    1533 │ 2021-12-12 │
│    5935 │ 2021-12-13 │
│  118025 │ 2021-12-14 │
└─────────┴────────────┘
Elapsed: 0.124 sec. Processed 8.35 M rows, 123.10 MB (67.42 M rows/s, 993.55 MB/s).

The daily counts show two peaks on 2021‑12‑10 and 2021‑12‑14.

Dataset availability

Full‑text and text‑only monthly JSON archives from 2015 to 2021 are stored in a public S3 bucket. Example links for January 2015:

Full JSON: https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01.json.gz

Text‑only JSON: https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01-text-only.json.gz

Conclusion

The Quickwit search‑stream endpoint can stream hundreds of millions of IDs in seconds, enabling practical full‑text search over multi‑TB ClickHouse datasets.