AI Functions in ClickHouse: Upgrade your SQL to the AI age

ClickHouse released a family of built-in AI Functions that call an LLM or embedding provider directly from its SQL engine, with the full set available in version 26.8 after rolling out across releases 26.4, 26.6, and 26.8. The eight functions in beta include aiGenerate(), aiClassify(), aiExtract(), aiTranslate(), aiEmbed(), aiFilter(), aiRedact(), and aiSimilarity(), and are in private preview in ClickHouse Cloud. ClickHouse said the functions move the model to the data rather than the data to the model, letting the full RAG cycle run in one system instead of across a vector database, an orchestration framework, and a separate LLM API.

ClickHouse now has a family of built-in AI Functions https://clickhouse.com/docs/reference/functions/regular-functions/ai-functions that call an LLM or an embedding provider directly from the SQL engine. The model becomes something you call from SQL, like lower or sum , and it runs where your data already lives. The functions are currently in Beta and functionality is constantly being added and improved. They arrived over the course of three releases: aiGenerate , aiClassify , aiExtract , and aiTranslate in 26.4, aiEmbed in 26.6, then aiFilter , aiRedact , and aiSimilarity in 26.8. Try AI Functions in ClickHouse Cloud In ClickHouse Cloud, no extra configuration mentioned below is required—the functions are plug-and-play. Currently, AI Functions are in private preview in ClickHouse Cloud. Join the private preview https://clickhouse.com/cloud/ai-functions-and-inference-services-waitlist?loc=blog-cta-1960-try-ai-functions-in-clickhouse-cloud-join-the-private-preview&utm blogctaid=1960 Why we developed this All your data already lives in ClickHouse: logs, product reviews, descriptions and support tickets. Traditional LLM workflows like RAG pull data out of database into a separate stack to run classification or embedding tasks, then push the results back in. This approach is slow, error prone, and adds operational complexity. The core idea behind AI Functions is to move the model to the data instead of the data to the model. Because ClickHouse already stores and can search vectors https://clickhouse.com/docs/guides/use-cases/ai-ml/vector-search , the full RAG cycle can run in one system rather than being stitched together across a vector database, an orchestration framework, and a separate LLM API. As a simple example: 1SELECT aiClassify 'I love this product ', 'positive', 'negative', 'neutral' ; Response: 1positive AI Functions in one query In short, AI Functions turn intricate workflows into a straightforward SELECT query. Explore AI Functions https://clickhouse.com/docs/reference/functions/regular-functions/ai-functions?loc=blog-cta-1961-ai-functions-in-one-query-explore-ai-functions&utm blogctaid=1961 What is in the beta The following AI Functions https://clickhouse.com/docs/reference/functions/regular-functions/ai-functions are available for you in 26.8. Text functions: - aiClassify - Classifies the given text into one of the provided categories using an LLM provider. - aiExtract - Extracts structured information from unstructured text using an LLM provider. - aiGenerate - Generates free-form text content from a prompt using an LLM provider. - aiTranslate - Translates the given text into the specified target language using an LLM provider. - aiFilter - Evaluates a natural-language condition against the given text using an LLM provider and returns a boolean UInt8 suitable for WHERE , PREWHERE , and JOIN ... ON . - aiRedact - Detects and redacts personally identifiable information PII in the given text using an LLM provider. Embedding functions: - aiEmbed - Generates an embedding vector for the given text using the configured AI provider. - aiSimilarity - Computes the semantic similarity of two texts using the configured embedding provider. AI Functions work by making a remote HTTP call to the configured provider's API for each input, or a batch of inputs for the embedding functions, and returning the parsed response as a native ClickHouse value. Preparing the setup OSS only To begin using AI Functions, you must configure two named collections https://clickhouse.com/docs/concepts/features/configuration/server-config/named-collections for text and embedding, respectively to store your provider credentials and configuration. We recommend configuring settings ai function text default credentials and ai function embedding default credentials with the names of the named collections. Then, all AI Functions will pick up the right endpoint automatically. It is also possible to override the settings during execution. Example statement to create a named collection with OpenAI provider credentials: you'll need an API key from OpenAI, one with a chat endpoint and another with an embedding endpoint: 1CREATE NAMED COLLECTION ai text credentials AS 2 provider = 'openai', 3 endpoint = 'https://api.openai.com/v1/chat/completions', 4 model = 'gpt-5.6-terra', 5 api key = 'sk-...'; 6 7-- The embedding functions aiEmbed , aiSimilarity do not read model from the named collection. 8CREATE NAMED COLLECTION ai embedding credentials AS 9 provider = 'openai', 10 endpoint = 'https://api.openai.com/v1/embeddings', 11 api key = 'sk-...'; Note: Any OpenAI-compatible API e.g. Ollama, LiteLLM can be used by setting provider = 'openai' and pointing the endpoint to your service. Therefore, this works with a local model too. Next, configure the default-credentials setting: 1SET ai function text default credentials = 'ai text credentials'; 2SET ai function embedding default credentials = 'ai embedding credentials'; Now you are ready to run the functions. Let’s review some examples. Understanding your data: classify, filter The examples below use the Hacker News dataset https://clickhouse.com/docs/get-started/sample-datasets/hacker-news , 28 million rows of stories and comments, loaded with the Parquet schema from that guide. The columns we care about are title , comment , author , score , type , and timestamp . Make sure you follow the setup steps above. aiClassify takes a string and a constant list of labels, and returns exactly one of those labels. The model is asked to pick a bucket, so it has limited creativity here. Front page titles are a good starting point because they are short, which keeps token usage low: 1SELECT 2 title, 3 aiClassify title, 'space', 'security', 'databases', 'startups', 'programming', 'other' AS topic 4FROM hackernews 5WHERE type = 'story' AND score < 3000 AND title = '' 6ORDER BY score DESC 7LIMIT 3 FORMAT Vertical; Response: 1Row 1: 2────── 3title: SpaceX’s Falcon Heavy successfully launches 4topic: space 5 6Row 2: 7────── 8title: Twitter Will Allow Employees to Work at Home Forever 9topic: other 10 11Row 3: 12────── 13title: No Cookie for You 14topic: security Since the output is a String , it integrates seamlessly with standard SQL. You can use aiClassify in a subquery, then aggregate over the result. For anything you plan to query more than once, classify into a column instead of recomputing. Analyze with SQL, not Python That is a topic breakdown of high-scoring Hacker News stories, computed without a single line of Python. In the old workflow, this would be an export, a classification job, and a load back into ClickHouse. Explore the dataset https://clickhouse.com/docs/get-started/sample-datasets/hacker-news?loc=blog-cta-1962-analyze-with-sql-not-python-explore-the-dataset&utm blogctaid=1962 aiFilter returns UInt8 , which means it can go straight into WHERE and behave like any other boolean condition. This is the function that most clearly does something SQL could not do before. Token search finds rows containing the word "database". aiFilter finds rows where someone is complaining about one: 1SELECT author, substring comment, 1, 100 AS snippet 2FROM 3 SELECT author, comment 4 FROM hackernews 5 WHERE type = 'comment' AND ilike comment, '%database%' 6 LIMIT 500 7 8WHERE aiFilter comment, 'the author is describing a production incident or outage they experienced' LIMIT 3 FORMAT Vertical; Response: 1Row 1: 2────── 3author: 0x0 4snippet: Wow, the database connection ip and dbname were taken from http cookies 5 6Row 2: 7────── 8author: AccountCreated 9snippet: &gt; The primary MCP database is comprised of 9 MongoDB shards 10enough said. 11 12Row 3: 13────── 14author: tomazzi 15snippet: The link goes to "Database Error - Error establishing a database connection" which is kind of intere Note: Since AI Function calls can be slow to process, it’s best to apply cheap predicates first in a subquery, then call the LLM predicate in the outer query. Reshaping your data: generate, translate Previous classification examples put text into buckets; the next two functions produce new text. We use the same dataset and same setup as above. aiGenerate takes a prompt and returns whatever the model writes back. In a SQL context the prompt is usually built with concat from a column. Hacker News comments are a good target because many of them are long, and this is a great candidate for summarization. In this example we also use params map with a system prompt and temperature parameters that keep the output shaped consistently across rows: 1SELECT 2 author, 3 length comment AS original chars, 4 aiGenerate 5 concat 'Summarize this Hacker News comment in one sentence: ', comment , 6 map 'system prompt', 'You are terse. Reply with one sentence and no preamble.', 7 'temperature', '0.3', 8 'max tokens', '2000' 9 10 AS summary 11FROM hackernews 12WHERE type = 'comment' AND length comment 1000 13LIMIT 3 FORMAT Vertical; Response: 1Row 1: 2────── 3author: 0- -0 4original chars: 1076 5summary: Some VPN providers have been court-verified to keep no logs, and given their financial incentive to protect their reputation plus mandatory data retention laws in many countries, using a reputable VPN is likely more private than relying on your local ISP. 6 7Row 2: 8────── 9author: 0- -0 10original chars: 1428 11summary: The comment shares Our World in Data links comparing COVID-19 confirmed cases, deaths, and case fatality rates across several countries using 7-day rolling averages. 12 13Row 3: 14────── 15author: 0- -0 16original chars: 1337 17summary: The commenter is drawing a parallel to Wim Hof, a man famous for extreme cold endurance feats and the ability to consciously control his immune system through a method combining cold exposure, breathing, and meditation. You can also do interesting patterns with generating once per group rather than once per row, something like this: 1SELECT aiGenerate 2 concat 3 'Write a three-bullet digest of what Hacker News was discussing. Titles:\n', 4 arrayStringConcat groupArray title , '\n' 5 6 AS digest; aiTranslate takes the text and a target language, either a name or a BCP-47 code. The parameter worth knowing about is instructions , which passes style or dialect guidance to the model: 1SELECT title, aiTranslate title, 'Spanish', map 'instructions', 'Use polite form. Keep technical terms and product names in English.' AS title es 2FROM hackernews 3WHERE type = 'story' AND score < 3000 AND title = '' 4ORDER BY score DESC LIMIT 3 FORMAT Vertical; Response: 1Row 1: 2────── 3title: SpaceX’s Falcon Heavy successfully launches 4title es: El Falcon Heavy de SpaceX se lanza con éxito 5 6Row 2: 7────── 8title: Twitter Will Allow Employees to Work at Home Forever 9title es: Twitter Permitirá a sus Empleados Trabajar desde Casa para Siempre 10 11Row 3: 12────── 13title: No Cookie for You 14title es: Sin Cookie para Ti Because both functions return String , they can be nested. For example, summarize a long comment, then translate the summary: 1aiTranslate 2 aiGenerate concat 'Summarize in one sentence: ', comment , 3 map 'system prompt', 'Reply with one sentence, no preamble.' , 4 'es-MX' AS resumen; The full RAG cycle, in the database Retrieval augmented generation powered by aiEmbed and vector search Every stage can now be performed in SQL: 1. Embed at write time with aiEmbed in a materialized view, so vectors are populated as data lands. 2. Store data and Embeddings next to each other 3. Index with a vector similarity index 4. Retrieve with cosineDistance against the embedded data. 5. Generate the answer with aiGenerate over the retrieved context Note that aiSimilarity is the convenience path for ad hoc work: it embeds both sides and returns cosine similarity in one call, which is ideal for semantic dedup or ranking a few thousand rows. Putting a ceiling on spend Unlike most ClickHouse functions, AI functions have a per-call cost in tokens and dollars, as opposed to just CPU cycles and memory usage. When deploying such functions, limiting per-query token cost and usage is likely to be front of mind. To help avoid runaway queries and hair-raising AI usage bills, we have implemented a set of quota settings which can be used to limit per-query AI function usage. The following session settings are used to control these quotas: - ai function max input tokens per query default 1000000 https://clickhouse.com/docs/reference/settings/session-settings/ai-function ai function max input tokens per query - ai function max output tokens per query default 500000 https://clickhouse.com/docs/reference/settings/session-settings/ai-function ai function max output tokens per query - ai function max api calls per query default 1000 https://clickhouse.com/docs/reference/settings/session-settings/ai-function ai function max api calls per query As well as a setting that controls error behavior upon reaching the quota limit: ai function throw on quota exceeded https://clickhouse.com/docs/reference/settings/session-settings/ai-function ai function throw on quota exceeded default 1 - throw . The settings above can be used like so: 1SELECT 2 title, 3 aiClassify title, 'space', 'security', 'databases', 'startups', 'programming', 'other' AS topic 4FROM hackernews 5WHERE type = 'story' AND score 100 AND title = '' 6LIMIT 5000 7SETTINGS ai function max api calls per query = 100; The text functions issue one request per row, so a call budget is effectively a row budget. This query wants 5000 rows but is only allowed 100 requests, so it stops: 1Code: 290. DB::Exception: AI API call limit reached: 100 calls made, maximum: 100. 2This is controlled by the 'ai function max api calls per query' setting. LIMIT EXCEEDED The count is exact, since the quota is checked before every request is dispatched, so a query never overshoots its call budget. Setting it to 0 disables the limit. Token quotas work the same way, but track what the provider actually reports, which is much closer to what you get billed for. Summarizing long Hacker News comments is the expensive case, since the whole comment goes into the prompt: 1SELECT 2 author, 3 aiGenerate 4 concat 'Summarize this Hacker News comment in one sentence: ', comment , 5 map 'system prompt', 'You are terse. Reply with one sentence and no preamble.', 6 'temperature', '0.3' 7 AS summary 8FROM hackernews 9WHERE type = 'comment' AND length comment 1000 10LIMIT 2000 11SETTINGS 12 ai function max input tokens per query = 500000, 13 ai function max output tokens per query = 50000; 14 15Code: 290. DB::Exception: AI input token limit reached or exceeded: 500642 tokens consumed, 16maximum: 500000. This is controlled by the 'ai function max input tokens per query' setting. Note "reached or exceeded". A call's token cost is not known until its response comes back, so the total can overshoot by up to one in-flight request's worth per thread. Leave headroom rather than setting the limit to the exact number you can afford. Aborting is the right default, but not always what you want. Stopping a long classification run at 99% and returning nothing is worse than returning most of it. ai function throw on quota exceeded = 0 turns the quota into a soft stop: 1SELECT 2 topic, 3 count AS stories 4FROM 5 SELECT aiClassify title, 'space', 'security', 'databases', 'startups', 'programming', 'other' AS topic 6 FROM hackernews 7 WHERE type = 'story' AND score 100 AND title = '' 8 LIMIT 5000 9 10WHERE topic = '' 11GROUP BY topic 12ORDER BY stories DESC 13SETTINGS 14 ai function max api calls per query = 1000, 15 ai function throw on quota exceeded = 0; Rows past the quota receive the column's default, an empty string for String , and the query succeeds. The WHERE topic = '' filters them back out, so you get a partial but honest aggregate instead of an exception. To see what a query actually spent, read the profile events from system.query log : 1SELECT 2 ProfileEvents 'AIAPICalls' AS api calls, 3 ProfileEvents 'AIInputTokens' AS input tokens, 4 ProfileEvents 'AIOutputTokens' AS output tokens, 5 ProfileEvents 'AIRowsProcessed' AS rows processed, 6 ProfileEvents 'AIRowsSkipped' AS rows skipped 7FROM system.query log 8WHERE query id = 'hn classify' AND type = 'QueryFinish' 9ORDER BY event time DESC 10LIMIT 1 FORMAT Vertical; 11 12Row 1: 13────── 14api calls: 1000 15input tokens: 24310 16output tokens: 3122 17rows processed: 1000 18rows skipped: 4000 AIRowsSkipped is the one to watch whenever ai function throw on quota exceeded = 0 , since it counts rows that quietly got a default value, from either a quota cut or an error. The cheapest way to size a quota is to run this over a LIMIT 100 sample first and multiply. Some things to keep in mind while configuring the quotas: - Set them in the top-level query. A SETTINGS clause on a sub-query is ignored for the quota settings. - They are per server, per query fragment. Within one execution context the cap is exact and shared across every AI function, block and thread. A distributed query can dispatch up to the limit on each shard , so divide by your shard count. - Token quotas need a provider that reports usage. OpenAI, Anthropic and vLLM do. Providers that omit the usage object leave the token counters at 0 , so those limits never fire, and you should bound them with ai function max api calls per query instead. - Embedding functions never produce completion tokens , so the output-token limit does not apply to aiEmbed or aiSimilarity . - Retries count against the call quota . ai function max retries defaults to 1 , so a budget of 1000 covers 1000 attempts , not 1000 rows, if the provider returns transient errors. Things to know before you ship Please keep in mind those concerns and limitations while shipping this to production: - Prompt injection : input text steers the model, so treat output as untrusted and never feed it into generated SQL or shell commands. - Non-determinism : same rows fed into the LLM will give different answers, so use temperature = 0 and consider materializing results rather than recomputing them. - Cost: Cost and latency scale with row count, so always test with LIMIT first, and make use of the quota settings described above - Security : restrict remote url allow hosts to your providers, keep endpoints on HTTPS, and remember the provider sees your data in cleartext after TLS termination. Conclusion AI Functions put classification, translation, embedding, and generation on SQL map in ClickHouse World. The full RAG loop now runs where the data already is. Looking forward to hearing from you on how you can build your apps based on this functionality. Try AI Functions in ClickHouse Cloud AI Functions are in private preview in ClickHouse Cloud. Join the private preview https://clickhouse.com/cloud/ai-functions-and-inference-services-waitlist?loc=blog-cta-1963-try-ai-functions-in-clickhouse-cloud-join-the-private-preview&utm blogctaid=1963