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

Part 2 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:

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:

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:

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:

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 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 puts the index and a model together to answer a customer's question from the help articles.