# Call an LLM from SQL on Databricks: ai_classify and ai_query on a table, run once per row

Databricks AI Functions call models from SQL. ai_classify puts text into 1 of the labels you give, with a description for each; ai_query sends any prompt to a model serving endpoint, such as a hosted foundation model, and returns its answer. Run them as a batch over a table, set failOnError => false so a failed row returns an error instead of failing the query, and write the results to a table, processing only rows that are not there yet, because every call is billed.

- Page: https://tecyfy.com/data-engineering/course/data-for-ai/ai-functions
- Course: Data engineering course (https://tecyfy.com/data-engineering/course), Module 13: Data for AI, AI Functions, a model called from SQL
- Parts of this module: Chunk documents for retrieval (https://tecyfy.com/data-engineering/course/data-for-ai); A vector index in sync with its table (https://tecyfy.com/data-engineering/course/data-for-ai/vector-index); AI Functions, a model called from SQL (https://tecyfy.com/data-engineering/course/data-for-ai/ai-functions); Build it: answer a question from the help articles (https://tecyfy.com/data-engineering/course/data-for-ai/build-it)

At the end of this part you can:

- Classify text in a table with ai_classify and labels that carry descriptions
- Call a hosted model on every row with ai_query, and keep a failed row from stopping the batch
- Run AI Functions incrementally so each row is processed and paid for once

[Part 2](/data-engineering/course/data-for-ai/vector-index) kept the help centre's index in step with its table. Models are useful on the data side too, before any assistant: sorting, summarising and extracting from text that arrives in a table every day. On Databricks, that is a SQL function. This part runs on Databricks, in a SQL editor or a notebook, with its own small table; it needs nothing from earlier parts.

## The messages

The shop's contact form writes each customer message to a table. Create it with a few messages:

```sql
CREATE TABLE IF NOT EXISTS workspace.shop.messages (message_id INT, received_at TIMESTAMP, text STRING);

INSERT INTO workspace.shop.messages VALUES
  (1, '2026-10-01 09:12:00', 'My parcel was due on Monday and has still not arrived. Order 1042.'),
  (2, '2026-10-01 10:40:00', 'I sent back a jacket 2 weeks ago and have not had my money yet.'),
  (3, '2026-10-01 11:05:00', 'Can I pay with a gift card and my debit card together?'),
  (4, '2026-10-01 13:30:00', 'The mug in order 1051 arrived broken. What do I do?'),
  (5, '2026-10-01 15:02:00', 'Do you deliver to Ireland?'),
  (6, '2026-10-01 16:48:00', 'Love the new autumn range, well done!');
```

The support team wants each message sorted into a queue, with 1 line saying what it is about.

## Sort them with ai_classify

`ai_classify` puts text into 1 of the labels you give. A label works better with a description of what belongs in it, written as a JSON object:

```sql
SELECT message_id, text,
       variant_get(
         ai_classify(
           text,
           '{"delivery": "Late, lost or missing parcels, delivery times and places",
             "refund": "Returns, refunds and damaged items",
             "payment": "Ways to pay, charges and gift cards",
             "other": "Anything else, including praise"}',
           map('version', '2.1')
         ),
         '$.response[0].value', 'string'
       ) AS topic
FROM workspace.shop.messages
```

Version 2.1 of `ai_classify`, the one Databricks recommends, returns a VARIANT: the chosen label sits in `response`, as an object with a `value`, beside any error. `variant_get` reads the label out as a string. Pinning the version in `map('version', '2.1')` keeps the output's shape fixed if the default changes. The options also take `instructions`, a description of the task, and `multilabel`, for a message that belongs in 2 queues.

Read the labels before trusting them. Message 4, a broken mug, could fairly be `refund` or `delivery`, and the description is what decides; a label that keeps landing in the wrong queue usually needs a clearer description, not a different model.

## Summarise them with ai_query

`ai_query` sends any prompt to a model serving endpoint and returns the answer. Databricks hosts foundation models with endpoints ready to call:

```sql
SELECT message_id,
       ai_query(
         'system.ai.meta-llama-3-3-70b-instruct',
         'Summarise this customer message in 1 short sentence for a support agent. '
           || 'Include the order number if there is one. Message: ' || text,
         failOnError => false
       ) AS summary
FROM workspace.shop.messages
```

The prompt is built by joining fixed instructions to each row's text with `||`. The model's temperature defaults to 0, which keeps answers steady from run to run; `modelParameters` changes it and the maximum length.

`failOnError => false` changes what comes back. By default, 1 row whose call fails fails the whole query, and a batch of 100,000 messages stops at the first bad one. With `false`, each row returns a struct of the `response` and an `errorMessage`, which is null when the call worked, so the batch finishes and the failed rows can be retried.

The hosted models change over time, and each has its own price and limits. The list of Foundation Model APIs in Databricks' documentation has the current endpoints; choose the smallest model that does the job well on your own messages.

## Run it once per message

Every call costs money. Databricks bills the compute that runs the query as usual, and the model inference on top: `ai_classify` under the `AI_FUNCTIONS` product in `system.billing.usage`, and `ai_query` for the endpoint it calls. A query that classifies all messages every night pays again for every old message. So write the results to a table, and process only what is not there yet:

```sql
CREATE TABLE IF NOT EXISTS workspace.shop.messages_routed (
  message_id INT, received_at TIMESTAMP, topic STRING, summary STRING, error STRING
);

INSERT INTO workspace.shop.messages_routed
SELECT message_id, received_at, topic, summary.response, summary.errorMessage
FROM (
  SELECT m.message_id, m.received_at,
         variant_get(
           ai_classify(m.text,
             '{"delivery": "Late, lost or missing parcels, delivery times and places",
               "refund": "Returns, refunds and damaged items",
               "payment": "Ways to pay, charges and gift cards",
               "other": "Anything else, including praise"}',
             map('version', '2.1')),
           '$.response[0].value', 'string') AS topic,
         ai_query('system.ai.meta-llama-3-3-70b-instruct',
           'Summarise this customer message in 1 short sentence for a support agent. '
             || 'Include the order number if there is one. Message: ' || m.text,
           failOnError => false) AS summary
  FROM workspace.shop.messages AS m
  LEFT ANTI JOIN workspace.shop.messages_routed AS r ON r.message_id = m.message_id
)
```

`LEFT ANTI JOIN` keeps the messages with no row in `messages_routed`, so a second run over the same messages calls no model at all. This is [module 10's](/data-engineering/course/orchestration) rule again: a step that is safe to run twice. A row whose summary failed keeps its `error`; to retry those, delete them from `messages_routed` and run the insert again.

As a task in the job, this query runs after the messages arrive, on serverless compute or a SQL warehouse. [Part 4](/data-engineering/course/data-for-ai/build-it) puts the index and a model together to answer a customer's question from the help articles.

## Check yourself

1. The routing query runs every night over all of the shop's messages, old and new. What does that cost?

   A. Nothing extra, because the results are cached
   B. Only the SQL warehouse time
   C. The model calls for every message, every night, though only the new ones changed
   D. Only the new messages, because AI Functions skip rows they have seen

### Answers

1. C. AI Functions do not remember earlier calls, and each call's inference is billed on top of the compute that runs the query. Processing only the messages that are not in the results table yet pays for each message once.

## Questions

### What are AI Functions in Databricks?

Built-in SQL functions that apply AI models to data on Databricks. Task-specific ones, such as ai_classify, ai_extract, ai_parse_document, ai_summarize and ai_translate, do 1 job with models Databricks manages. ai_query is general: it sends a prompt to any supported model serving endpoint, including hosted foundation models, and returns the answer. They run from SQL warehouses (not classic ones), notebooks, jobs and pipelines.

### How is ai_query billed?

The compute that runs the query is billed as usual, and the model inference is billed in addition. For a Databricks-hosted foundation model, ai_query is billed like the task-specific functions; a custom model or provisioned throughput endpoint is billed for its own serving compute. The costs appear in system.billing.usage, under the MODEL_SERVING or AI_FUNCTIONS product depending on the function.

## Sources

- [Transform unstructured data using AI Functions](https://docs.databricks.com/aws/en/large-language-models/ai-functions)
- [ai_classify function](https://docs.databricks.com/aws/en/sql/language-manual/functions/ai_classify)
- [ai_query function](https://docs.databricks.com/aws/en/sql/language-manual/functions/ai_query)
- [variant_get function](https://docs.databricks.com/aws/en/sql/language-manual/functions/variant_get)