AI Functions (ai_query, ai_classify...)
Databricks · Machine Learning & AI
Theory
AI Functions are built-in SQL functions that call large language models directly from a query, so analysts can use AI on table data without Python or API code. Task-specific functions handle common jobs: ai_classify (pick labels), ai_extract (pull fields such as names or amounts), ai_analyze_sentiment, ai_summarize, ai_translate, ai_mask (hide PII), ai_fix_grammar, ai_parse_document (turn PDFs and images into text and structure) and ai_forecast. ai_query is the general-purpose function: you pass a model serving endpoint name (a Databricks-hosted foundation model, an external model or your own custom model) and a prompt or input, and get the response back as a column. They run on Databricks SQL (not classic warehouses), serverless and recent runtimes, and work over whole tables as batch inference.
Example: tag 10,000 customer reviews from Jaipur stores as delivery, quality or price with ai_classify in one SELECT.
Why we use it
Turning free text into structured data (labels, entities, summaries) used to need an ML team. AI Functions bring that into SQL, inside pipelines and dashboards, with Unity Catalog governance on the data and endpoints.
When to use it
Use task-specific functions first; they are simpler and tuned for the job. Use ai_query when you need a custom prompt, a specific model or your own endpoint. Test on a small LIMIT before running on millions of rows, and store results in a table instead of recomputing.
How it works
- Free-text column in a table (reviews, tickets, PDFs)
- Is there a task-specific function for the job? Yes: Use ai_classify / ai_extract / ai_summarize.... No: Use ai_query with a prompt and endpoint.
- Run on a small LIMIT and check outputs
- Are outputs good enough? Yes: Run on the full table and save results. No: Refine labels, instructions or prompt.
- Use the structured columns in dashboards and pipelines
Syntax
ai_classify(content, labels_json [, options]) -- returns VARIANT (response, metadata, error_message)
ai_extract(content, ...) ai_analyze_sentiment(text) ai_summarize(text [, max_words])
ai_translate(text, to_lang) ai_mask(text, labels_array) ai_parse_document(content)
ai_query(endpoint, request [, returnType => ..., responseFormat => ..., failOnError => ...])Example
-- Label reviews (task-specific function)
SELECT review_id, city,
ai_classify(review_text, '["delivery", "quality", "price"]',
MAP('version', '2.1')):response[0].value::string AS topic
FROM main.sales.reviews
LIMIT 20;
SELECT review_id, ai_analyze_sentiment(review_text) AS sentiment
FROM main.sales.reviews;
-- Custom prompt with a model endpoint (name as listed under Serving)
SELECT customer_id,
ai_query('system.ai.meta-llama-3-3-70b-instruct',
CONCAT('Write a one-line thank-you note for a ', segment,
' customer from ', city, '.')) AS note
FROM main.sales.customers
LIMIT 10;
-- Save results once instead of re-calling the model
CREATE OR REPLACE TABLE main.sales.review_topics AS
SELECT review_id, ai_classify(review_text, '["delivery","quality","price"]') AS topic_result
FROM main.sales.reviews;Key points
- Task-specific functions (ai_classify, ai_extract...) need no endpoint choice
- ai_query works with foundation, external and custom serving endpoints
- Not available on classic SQL warehouses; use serverless or pro / recent runtimes
- Newer function versions return VARIANT; read fields with :response
- Test with LIMIT, then persist results to a table to control cost
- Model outputs can vary; review samples before trusting them in reports