Introduction
ClickHouse’s recent addition of vector similarity search enables AI applications directly within your analytical infrastructure. Combined with its existing strengths in analytics, ClickHouse becomes a powerful platform for building AI-powered applications including retrieval-augmented generation (RAG) systems.
This guide explores how to leverage ClickHouse for AI applications, from embedding storage to production ML pipelines.
Vector Similarity Search
Getting Started
Before we can search vectors, we need somewhere to store them, and ClickHouse’s approach differs from dedicated vector databases in a way that matters for real deployments. Instead of introducing a brand-new storage engine, vectors live in ordinary MergeTree tables as Array(Float32) columns. This means vector data benefits from the same compression, replication, and partitioning machinery that powers ClickHouse’s analytics workloads, so you avoid running a separate vector database alongside your warehouse.
The schema below is deliberately minimal: a primary key, a document identifier, the original text, and the embedding array. Storing the raw text alongside the vector is a deliberate choice—it lets a single query return both the match and its contents without a join back to a document store. The dimensionality of the embedding column is dictated by whatever embedding model you choose; 384 dimensions (from models like all-MiniLM-L6-v2) is a common default that balances quality against storage cost.
-- Create table with vector column (25.x)
CREATE TABLE embeddings (
id UInt64,
document_id UInt64,
text String,
embedding Array(Float32) -- e.g., 384 dimensions
) ENGINE = MergeTree()
ORDER BY document_id;
-- Insert vector data
INSERT INTO embeddings VALUES
(1, 1, 'Python tutorial', [0.1, 0.2, 0.3, ...]),
(2, 1, 'Learn Python', [0.15, 0.25, 0.35, ...]),
(3, 2, 'JavaScript guide', [0.8, 0.1, 0.05, ...]);
Vector Index
Scanning every row and computing cosine distance in the query engine works for small datasets, but it becomes the bottleneck once a table holds millions of rows. ClickHouse’s vector index accelerates this by pre-computing approximate nearest neighbors over data granules, so the query engine only needs to inspect a small fraction of the table. The index is declared as a skip index: it is a hint that helps the engine prune granules, not an exact inverted index.
The first statement below attaches a vector_similarity index to the embedding column using cosine as the metric. Because it is an approximate index, results are returned in near-optimal order rather than exact order, and the trade-off between recall and speed is controlled at query time rather than index time. The second statement shows the fallback used when no index is present: a full scan that computes arrayCosineDistance for every row, sorts ascending, and returns the closest matches. Knowing both paths is important because the full scan remains the correctness baseline that approximate indexes are measured against.
-- Add vector similarity index (25.x)
ALTER TABLE embeddings
ADD INDEX vec_idx embedding
TYPE vector_similarity('metric=cosine')
GRANULARITY 1;
-- Or without index (full scan)
SELECT
id,
text,
arrayCosineDistance(embedding, [0.1, 0.2, ...]) as distance
FROM embeddings
ORDER BY distance
LIMIT 5;
Python Integration
SQL alone is not enough for a working system; embeddings have to be generated somewhere, and in practice that somewhere is Python. The library clickhouse-connect is the standard client, and SentenceTransformer provides a compact, locally runnable embedding model that avoids the latency and cost of a remote embedding API during development.
The ClickHouseVectorStore class below ties these pieces together into a small vector store with the same shape as the familiar FAISS or Pinecone interfaces: add documents, then search by query. Two design details are worth noting. First, the embedding model is instantiated on every call for simplicity, but in production you would load it once at startup to avoid repeated model-loading overhead. Second, the INSERT statements are executed row by row; for larger document collections you would batch them, because ClickHouse’s throughput is dramatically higher with batched inserts of hundreds or thousands of rows at a time.
import clickhouse_connect
import numpy as np
from sentence_transformers import SentenceTransformer
class ClickHouseVectorStore:
"""Vector store using ClickHouse."""
def __init__(self, host='localhost', port=8123):
self.client = clickhouse_connect.get_client(
host=host,
port=port
)
self._create_table()
def _create_table(self):
"""Create vector table."""
self.client.command("""
CREATE TABLE IF NOT EXISTS vectors (
id UInt64,
text String,
embedding Array(Float32)
) ENGINE = MergeTree()
ORDER BY id
""")
def add_documents(self, documents):
"""Add documents with embeddings."""
model = SentenceTransformer('all-MiniLM-L6-v2')
for i, text in enumerate(documents):
embedding = model.encode(text).tolist()
self.client.command(
"INSERT INTO vectors VALUES",
[[i, text, embedding]]
)
def search(self, query, top_k=5):
"""Search for similar documents."""
model = SentenceTransformer('all-MiniLM-L6-v2')
query_vector = model.encode(query).tolist()
results = self.client.query(f"""
SELECT id, text,
arrayCosineDistance(embedding, {query_vector}) as distance
FROM vectors
ORDER BY distance
LIMIT {top_k}
""")
return [
{'id': r[0], 'text': r[1], 'distance': r[2]}
for r in results.result_rows
]
# Usage
store = ClickHouseVectorStore()
store.add_documents([
"Python is a great programming language",
"Machine learning is fascinating",
"Data science combines programming and statistics"
])
results = store.search("What is programming?")
for r in results:
print(f"Text: {r['text']}, Distance: {r['distance']:.4f}")
Building RAG Pipelines
Complete RAG Implementation
A vector store is only one ingredient of retrieval-augmented generation. The full RAG pipeline also needs to split documents into manageable chunks, store document-level metadata, generate embeddings for each chunk, retrieve the most relevant chunks for a query, and finally feed that context to an LLM. Keeping the chunks and documents in the same database—as the schema below does—removes an entire class of consistency problems that appear when vector data and metadata live in separate systems.
The ClickHouseRAG class implements the whole loop. Chunking is done with a simple fixed-size window for clarity, although production systems typically use a sliding window with overlap so that semantically related text does not fall on either side of a chunk boundary. Retrieval orders chunks by cosine distance and returns the top-k with their document identifiers, which preserves provenance for citation. The answer method then assembles a context window with a hard character budget, guards against overrunning the model’s context limit, and submits the augmented prompt to the LLM for the final response.
import clickhouse_connect
from sentence_transformers import SentenceTransformer
import openai
class ClickHouseRAG:
"""RAG pipeline with ClickHouse."""
def __init__(self, config):
self.client = clickhouse_connect.get_client(**config)
self.model = SentenceTransformer('all-MiniLM-L6-v2')
self._init_schema()
def _init_schema(self):
"""Initialize database schema."""
# Chunks table
self.client.command("""
CREATE TABLE IF NOT EXISTS chunks (
id UInt64,
document_id UInt64,
chunk_text String,
chunk_index UInt32,
embedding Array(Float32)
) ENGINE = MergeTree()
ORDER BY (document_id, chunk_index)
""")
# Documents table
self.client.command("""
CREATE TABLE IF NOT EXISTS documents (
id UInt64,
title String,
source String,
created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY id
""")
def ingest_document(self, document_id, title, source, text, chunk_size=500):
"""Ingest document with chunking."""
# Split into chunks
chunks = [text[i:i+chunk_size]
for i in range(0, len(text), chunk_size)]
# Insert document metadata
self.client.command(
"INSERT INTO documents VALUES",
[[document_id, title, source]]
)
# Generate embeddings and insert chunks
for i, chunk in enumerate(chunks):
embedding = self.model.encode(chunk).tolist()
self.client.command(
"INSERT INTO chunks VALUES",
[[i, document_id, chunk, i, embedding]]
)
def retrieve(self, query, top_k=5):
"""Retrieve relevant context."""
query_vector = self.model.encode(query).tolist()
results = self.client.query(f"""
SELECT
chunk_text,
document_id,
arrayCosineDistance(embedding, {query_vector}) as distance
FROM chunks
ORDER BY distance
LIMIT {top_k}
""")
return [
{'text': r[0], 'doc_id': r[1], 'distance': r[2]}
for r in results.result_rows
]
def answer(self, question, max_context=3000):
"""Answer question using RAG."""
# Retrieve relevant chunks
chunks = self.retrieve(question, top_k=10)
# Build context
context = ""
for chunk in chunks:
if len(context) + len(chunk['text']) > max_context:
break
context += chunk['text'] + "\n\n"
# Generate answer
prompt = f"""Based on the following context, answer the question.
Context:
{context}
Question: {question}
Answer:"""
response = openai.ChatCompletion.create(
model="gpt-4",
messages=[{"role": "user", "content": prompt}],
temperature=0
)
return response.choices[0].message.content
def close(self):
"""Close connection."""
self.client.close()
# Usage
rag = ClickHouseRAG({'host': 'localhost', 'port': 8123})
# Ingest document
with open('article.txt') as f:
rag.ingest_document(1, 'Article', 'article.txt', f.read())
# Answer question
answer = rag.answer("What is the main topic?")
print(answer)
rag.close()
The critical insight in this pipeline is that the retrieval layer stays invisible to the LLM: the model only ever sees a well-formed prompt. All of the heavy lifting—embedding, chunking, ranking, and context assembly—happens inside ClickHouse and Python before generation begins. This separation of concerns is what makes RAG reliable enough for production: retrieval failures are caught and fixed by testing the SQL, never by debugging prompt behavior, and the same retrieval layer can be reused by any number of downstream applications.
ML Feature Engineering
Feature Creation
Beyond retrieval, ClickHouse earns its keep in AI systems as the feature store for machine learning. Because it is designed to aggregate billions of event rows in milliseconds, it can materialize the user- and time-level features that power ranking models, churn prediction, and personalization systems—without moving data into a separate feature engineering platform. The pattern is always the same: define a feature table with a CREATE TABLE … AS SELECT statement over the raw events, grouping by the entity you want to score.
The three examples below illustrate the range. The first computes user-level aggregates—event volume, session uniqueness, purchase counts, and amount statistics—in one pass over the events table. The second breaks activity down by hour, day of week, and month, which captures the cyclical behavior that many behavioral models rely on. The third is the most interesting: it combines conditional aggregations (sumIf, avgIf) with sequence functions such as sequenceCount, which detect ordered event patterns and let you compute a conversion funnel directly inside the query. Notice that all of this is set-based SQL; ClickHouse’s columnar engine computes these aggregates without ever materializing intermediate rows in Python.
import clickhouse_connect
class FeatureEngineering:
"""Feature engineering with ClickHouse."""
def __init__(self, config):
self.client = clickhouse_connect.get_client(**config)
def create_user_features(self):
"""Create user-level features from events."""
self.client.command("""
CREATE TABLE IF NOT EXISTS user_features AS
SELECT
user_id,
count() as total_events,
uniqExact(session_id) as unique_sessions,
avg(duration) as avg_duration,
min(event_time) as first_event,
max(event_time) as last_event,
sum(case when action = 'purchase' then 1 else 0 end) as purchase_count,
sum(amount) as total_amount,
stddevPop(amount) as amount_stddev
FROM events
GROUP BY user_id
""")
def create_time_features(self):
"""Create time-based features."""
self.client.command("""
CREATE TABLE IF NOT EXISTS time_features AS
SELECT
user_id,
toHour(event_time) as hour,
toDayOfWeek(event_time) as day_of_week,
toMonth(event_time) as month,
count() as event_count
FROM events
GROUP BY user_id, hour, day_of_week, month
""")
def create_aggregation_features(self):
"""Create aggregation features."""
self.client.command("""
CREATE TABLE IF NOT EXISTS agg_features AS
SELECT
user_id,
-- Rolling aggregations
anyLastIf(amount, action = 'view') as last_viewed_amount,
sumIf(amount, action = 'purchase') as total_purchases,
avgIf(amount, action = 'view') as avg_viewed_amount,
-- Sequence features
groupArray(1)(amount) as amounts,
sequenceCount('(?1)(?2)(?3)')(
action,
action = 'view',
action = 'add_to_cart',
action = 'purchase'
) as conversion_funnel
FROM events
GROUP BY user_id
""")
# Usage
features = FeatureEngineering({'host': 'localhost'})
features.create_user_features()
Export for ML
Features built inside ClickHouse are only useful once they reach a training pipeline, and the export step is where many teams discover that database-to-DataFrame plumbing can consume more engineering time than the modeling itself. The snippet below shows the shortest viable path: run a SELECT over the feature table, convert the result to a pandas DataFrame via result_set.to_pandas(), and split off the target column for supervised learning.
The convenience of this approach should not hide its scaling limits. For millions of rows, pulling everything into a single DataFrame becomes slow and memory-hungry, and production systems typically use batched streaming export or ClickHouse’s table function integrations with Spark. For model iteration and experimentation, though, the direct path is invaluable because it lets data scientists work in their familiar DataFrame workflow without leaving the warehouse.
def export_for_ml(self, table, target_column):
"""Export features for ML training."""
df = self.client.query(f"""
SELECT * FROM {table}
WHERE {target_column} IS NOT NULL
""").result_set.to_pandas()
return df
# Export
df = export_for_ml('user_features', 'purchase_count')
X = df.drop(columns=['purchase_count', 'user_id'])
y = df['purchase_count']
Recommendation Systems
Collaborative Filtering
Recommendation systems are a classic ClickHouse AI workload because the underlying data—who interacted with what, when, and how strongly—is naturally an event log. The first statement below creates the raw interaction table, a MergeTree ordered by user and timestamp so that per-user scans stay efficient. From this single table, both user-based and item-based collaborative filtering can be expressed as set-based SQL, which is notable: most recommendation stacks move this data into a graph or dedicated engine first.
The user-similarity query is the more intricate of the two. It computes, for a target user, the correlation between that user’s ratings and every other user’s ratings across the items they have in common, then expresses similarity as one minus the correlation so that smaller means closer. The construction uses a UNION between the trivial self-match and the correlation subquery, and the outer ORDER BY distance returns the ten nearest neighbors. The key point is that all of this runs as a single push-down query against ClickHouse’s aggregation engine, which scales far beyond what an in-memory pandas implementation could handle.
-- User-item interactions
CREATE TABLE user_items (
user_id UInt32,
item_id UInt32,
rating Float32,
timestamp DateTime
) ENGINE = MergeTree()
ORDER BY (user_id, timestamp);
-- Similar users
SELECT
user_id,
neighbor_user_id,
distance
FROM (
SELECT
user_id,
user_id as neighbor_user_id,
0 as distance
FROM user_items
WHERE user_id = 123
UNION ALL
SELECT
123 as user_id,
user_id as neighbor_user_id,
1 - correlation(other_ratings, my_ratings) as distance
FROM (
SELECT
user_id,
groupArray(rating) as other_ratings
FROM user_items
WHERE item_id IN (
SELECT item_id FROM user_items WHERE user_id = 123
)
GROUP BY user_id
)
)
ORDER BY distance
LIMIT 10;
Item-Based Recommendations
While user-based filtering finds neighbors by who rated what, item-based filtering discovers relationships between items directly. This variant is often more stable in practice: item affinities change more slowly than user tastes, and the item-similarity matrix can be precomputed once and reused for every request. The query below computes a cosine-style similarity between item pairs by counting the users who interacted with both and normalizing by the product of their interaction counts.
The self-join is the workhorse here. Each side of the join aggregates interactions into (item, user, count) rows, and the join pairs every co-occurring item combination. The WHERE a.item_id < b.item_id eliminates duplicate pairs so each similarity appears exactly once, and the result is ordered by similarity so the top-100 nearest item neighbors are returned directly. For large catalogs this precomputation should be scheduled as a batch job that materializes the similarity table, rather than run on every request, because the self-join cost grows with the square of the co-occurrence volume.
-- Item similarity
SELECT
a.item_id as item1,
b.item_id as item2,
count() / sqrt(a.count * b.count) as similarity
FROM (
SELECT item_id, user_id, count() as count
FROM user_items
GROUP BY item_id, user_id
) a
JOIN (
SELECT item_id, user_id, count() as count
FROM user_items
GROUP BY item_id, user_id
) b ON a.user_id = b.user_id
WHERE a.item_id < b.item_id
GROUP BY a.item_id, b.item_id
ORDER BY similarity DESC
LIMIT 100;
Anomaly Detection
Statistical Methods
Anomaly detection is a natural fit for ClickHouse because it operates over exactly the kind of high-volume, timestamped event streams that the engine ingests natively. The statistical approaches below avoid training a model altogether: they flag records whose behavior deviates from their own history by more than a threshold. This makes them interpretable and cheap to run continuously, which is why they remain the first line of defense in fraud monitoring and system health checks.
The first query computes a z-score per transaction. A window function over each user’s transactions derives the average and standard deviation, and the outer query keeps only records where the absolute z-score exceeds three—the standard rule for statistical outliers. The second query takes a different route: it uses a sliding window of the previous one hundred transactions per user to compute a rolling average, then flags any transaction that deviates from that local baseline by more than fifty percent. The distinction is worth internalizing: the z-score captures global volatility, while the moving-average check reacts to local drift, and combining both catches different failure modes.
-- Z-score based anomaly detection
SELECT
user_id,
amount,
avg_amount,
stddev_amount,
(amount - avg_amount) / stddev_amount as z_score
FROM (
SELECT
user_id,
amount,
avg(amount) OVER (PARTITION BY user_id) as avg_amount,
stddevPop(amount) OVER (PARTITION BY user_id) as stddev_amount
FROM transactions
)
WHERE abs(z_score) > 3;
-- Moving average deviation
SELECT
user_id,
timestamp,
amount,
avg_amount,
amount - avg_amount as deviation
FROM (
SELECT
user_id,
timestamp,
amount,
avg(amount) OVER (
PARTITION BY user_id
ORDER BY timestamp
ROWS BETWEEN 100 PRECEDING AND CURRENT ROW
) as avg_amount
FROM transactions
)
WHERE abs(amount - avg_amount) > avg_amount * 0.5;
Best Practices
Vector Search Optimization
The best practices in this section are the operational lessons that separate a demo from a production deployment. The first concerns embedding dimensionality. Higher-dimensional vectors store more information and can be more precise, but they consume more disk, slow down distance computation, and—critically for the approximate index—reduce its effectiveness, since each granule then holds fewer rows. There is no universal optimum; the right dimension is the smallest one that preserves downstream retrieval quality for your domain.
The second piece of advice is about insert patterns. ClickHouse is optimized for batched, append-only ingestion, and inserting vectors one row at a time through a driver wastes the engine’s strengths and multiplies round trips. Batching inserts—typically hundreds to a few thousand rows per statement, or using the native insert formats—keeps merge-tree parts large and keeps the table performant even as it grows to billions of rows.
-- Use appropriate vector dimensions
-- Smaller = faster, larger = more precise
-- Batch inserts
INSERT INTO embeddings VALUES
(1, 'text1', [0.1, ...]),
(2, 'text2', [0.2, ...]),
...; -- Batch 1000 at a time
Query Optimization
Query patterns have an outsized effect on vector search latency because an unconstrained similarity sort is effectively a full-table aggregation. The two rules below keep those queries fast and predictable. The first is simply to always apply LIMIT: without it, ClickHouse computes and sorts the distance for every row in the table even though only the nearest handful are ever used, and an unbounded sort at the edge of a billion-row table can stall an otherwise healthy cluster.
The second rule exploits ClickHouse’s columnar strength by pushing cheap filters before the expensive distance computation. Adding a WHERE clause on a low-cardinality column such as category or tenant_id lets the engine prune entire granules before any float arithmetic happens, shrinking the candidate set by orders of magnitude. Combined with the approximate vector index, filtered queries routinely cut end-to-end latency by more than an order of magnitude while barely changing the result set, because the filter is almost always semantically safe to apply before ranking.
-- Use LIMIT with vector search
SELECT * FROM embeddings
ORDER BY arrayCosineDistance(embedding, query)
LIMIT 10; -- Always limit
-- Filter before distance calculation
SELECT * FROM embeddings
WHERE category = 'tech'
ORDER BY arrayCosineDistance(embedding, query)
LIMIT 10;
Integration with ML Libraries
Scikit-learn
ClickHouse does not replace your modeling framework; it feeds it. The integration pattern that has become standard is to materialize feature tables in ClickHouse and then load them into whatever training stack you already use. This section shows the two most common cases, starting with the scikit-learn ecosystem, which remains the default for tabular models such as regression, random forests, and gradient boosting.
The example pulls the prepared feature set into a pandas DataFrame, drops the identifier and target columns to build the feature matrix, and splits it into training and test sets with the same random seed so experiments stay reproducible. From there the DataFrame is handed directly to a RandomForestRegressor, trained and evaluated with the standard fit/predict cycle. The important structural point is that the SQL stays constant across experiments: feature logic lives in ClickHouse, and the modeling code only ever sees a clean, stable DataFrame interface.
import clickhouse_connect
from sklearn.model_selection import train_test_split
# Get features
client = clickhouse_connect.get_client()
df = client.query("""
SELECT * FROM user_features
""").result_set.to_pandas()
X = df.drop(columns=['target', 'user_id'])
y = df['target']
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.2, random_state=42
)
# Train model
from sklearn.ensemble import RandomForestRegressor
model = RandomForestRegressor()
model.fit(X_train, y_train)
# Predict
predictions = model.predict(X_test)
TensorFlow/PyTorch
For deep learning workloads, the integration changes slightly but the principle is identical: extract vectors from ClickHouse and convert them to tensors. The example below shows the minimal path for pulling an embedding column into PyTorch. Querying only the embedding column, converting the result set to a DataFrame, and wrapping the values with torch.tensor produces a tensor ready for a neural network.
The commented line hints at the most common real-world use: seeding an embedding layer with the vectors computed in ClickHouse via torch.nn.Embedding.from_pretrained. This is how teams bootstrap recommendation and search models from precomputed embeddings without re-embedding the corpus inside the training job. For large corpora, prefer chunked fetching or ClickHouse’s Arrow/Native format support to stream rows into tensor memory in batches rather than materializing the full matrix at once.
import torch
import clickhouse_connect
# Load embeddings as tensors
client = clickhouse_connect.get_client()
embeddings = client.query("SELECT embedding FROM vectors").to_pandas()
embedding_matrix = torch.tensor(embeddings.values)
# Use in neural network
# embedding_layer = torch.nn.Embedding.from_pretrained(embedding_matrix)
Resources
Conclusion
ClickHouse provides a powerful platform for AI applications, combining analytical capabilities with vector search. From RAG pipelines to ML feature engineering, ClickHouse offers a unified solution for modern AI-powered applications.
In the next article, we’ll explore real-world ClickHouse use cases, including production patterns and implementation strategies.
Comments