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

  1. Free-text column in a table (reviews, tickets, PDFs)
  2. 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.
  3. Run on a small LIMIT and check outputs
  4. Are outputs good enough? Yes: Run on the full table and save results. No: Refine labels, instructions or prompt.
  5. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic