AI SQL Function

Works with v1.8+

This recipe demonstrates how to use the ai() SQL function to invoke large language models (LLMs) directly within SQL queries for AI-powered text generation.

The ai() function enables you to integrate AI capabilities into your data workflows without external APIs or complex integrations—simply call a function in your SQL query!

What You'll Learn

How to configure LLM models in Spice
How to use the ai() function in SQL queries
How to process data with AI in parallel for better performance
Real-world examples like sentiment analysis and text categorization

Prerequisites

Spice CLI installed. Follow the Getting Started guide if needed.
An OpenAI API key (or another supported LLM provider like Anthropic, xAI, etc.)

Quick Start

Step 1: Clone and Navigate

git clone https://github.com/spiceai/cookbook.git  # Skip if already cloned
cd cookbook/ai

Step 2: Configure Your API Key

Create a .env file with your OpenAI API key:

echo "SPICE_OPENAI_API_KEY=your_openai_api_key_here" > .env

Or manually create .env:

SPICE_OPENAI_API_KEY=your_openai_api_key_here

Step 3: Start Spice

spice run

You should see output indicating the model is ready:

2025-10-06T10:30:00.123456Z  INFO runtime::init::model: Loading model [gpt-4o-mini] from openai:gpt-4o-mini...
2025-10-06T10:30:01.234567Z  INFO runtime::init::model: Model [gpt-4o-mini] deployed, ready for inferencing
2025-10-06T10:30:02.345678Z  INFO runtime::init::dataset: Dataset taxi_zones registered...

Step 4: Try Your First AI Query

Open the Spice SQL REPL in a new terminal:

spice sql

Try this simple example:

SELECT ai('Say hello in a creative way!') as greeting;

Result:

+--------------------------------------------------+
| greeting                                         |
+--------------------------------------------------+
| Greetings, cosmic wanderer! 🌟 How do you do?  |
+--------------------------------------------------+

Example Queries

Example 1: Simple Text Generation

Ask the AI a question:

SELECT ai('What is the capital of France?') as answer;

Example 2: Categorize Data

Categorize NYC taxi zones using AI:

SELECT
  LocationID,
  Zone,
  ai(concat('Categorize this location in one word: ', Zone), 'gpt-4o-mini') as category
FROM taxi_zones
LIMIT 5;

Result:

+------------+-------------------------+-------------+
| LocationID | Zone                    | category    |
+------------+-------------------------+-------------+
| 1          | Newark Airport          | Transport   |
| 2          | Jamaica Bay             | Nature      |
| 3          | Allerton/Pelham Gardens | Residential |
| 4          | Alphabet City           | Urban       |
| 5          | Arden Heights           | Suburban    |
+------------+-------------------------+-------------+

Example 3: Sentiment Analysis

Analyze customer feedback sentiment:

SELECT
  feedback,
  ai('Classify this feedback as positive, negative, or neutral: ' || feedback, 'gpt-4o-mini') as sentiment
FROM customer_feedback
LIMIT 3;

Result:

+------------------------------------------+-----------+
| feedback                                 | sentiment |
+------------------------------------------+-----------+
| Great service, very helpful!             | positive  |
| The product broke after one day          | negative  |
| It's okay, nothing special               | neutral   |
+------------------------------------------+-----------+

Example 4: Data Enrichment

Generate descriptions for locations:

SELECT
  Zone,
  Borough,
  ai('Write a one-sentence description of ' || Zone || ' in ' || Borough, 'gpt-4o-mini') as description
FROM taxi_zones
WHERE Borough = 'Manhattan'
LIMIT 3;

Example 5: Using Multiple Models

Compare responses from different models (requires multiple models configured):

SELECT
  left(ai('Explain quantum computing in 10 words', 'gpt-4o-mini'), 50) as gpt4,
  left(ai('Explain quantum computing in 10 words', 'sonnet-4-5'), 50) as claude
FROM (SELECT 1);  -- Dummy table for single row;

Example 6: Translation

Translate text to different languages:

SELECT
  Zone as original,
  ai(concat_ws(' ', 'Translate to Spanish:', Zone), 'gpt-4o-mini') as spanish,
  ai(concat_ws(' ', 'Translate to French:', Zone), 'gpt-4o-mini') as french
FROM taxi_zones
WHERE Borough = 'Manhattan'
LIMIT 5;

Result:

+----------------+------------------+-----------------+
| original       | spanish          | french          |
+----------------+------------------+-----------------+
| Central Park   | Parque Central   | Parc Central    |
| Times Square   | Times Square     | Times Square    |
| Battery Park   | Parque Battery   | Battery Park    |
+----------------+------------------+-----------------+

Understanding the Configuration

The spicepod.yaml file configures the LLM model:

models:
  - name: gpt-4o-mini
    from: openai:gpt-4o-mini
    params:
      openai_api_key: ${secrets:SPICE_OPENAI_API_KEY}

Key Points:

name: The identifier you use in ai(message, 'model_name')
from: The model provider and model name
params: Configuration like API keys (loaded from .env)

How It Works

Function Signatures

The ai() function has two forms:

Default Model (when only one model configured):
```
ai('your message here')
```
Specific Model:
```
ai('your message here', 'model_name')
```

Parallel Processing

The ai() function executes asynchronously, meaning when you query multiple rows, Spice processes the AI calls in parallel for better performance:

-- This processes 10 AI calls in parallel!
SELECT Zone, ai('Categorize: ' || Zone) as category
FROM taxi_zones
LIMIT 10;

Limits

Maximum batch size: 100 rows per query
Maximum message size: 1 MB per message

Error Handling

If an AI call fails, the function returns NULL and logs the error. You can check the logs or task history for details.

Advanced Usage

Task History

Every ai() call is tracked in the runtime.task_history table:

SELECT
  trace_id,
  task,
  execution_duration_ms,
  captured_output
FROM runtime.task_history
WHERE task = 'ai'
ORDER BY start_time DESC
LIMIT 5;

Combining with Other Functions

The ai() function works seamlessly with SQL:

-- Uppercase the AI response
SELECT upper(ai('say hello')) as loud_greeting;

-- Get first 20 characters of response
SELECT left(ai('Write a long story'), 20) as preview;

-- Use in WHERE clauses
SELECT * FROM products
WHERE ai('Is this a tech product? Answer yes or no: ' || description) = 'yes';

Using Different Model Providers

Spice supports multiple LLM providers! Here's how to configure different ones:

Anthropic Claude

Add to spicepod.yaml:

models:
  - name: sonnet-4-5
    from: anthropic:claude-4-5-sonnet
    params:
      anthropic_api_key: ${secrets:ANTHROPIC_API_KEY}

xAI Grok

models:
  - name: grok-4-1-fast
    from: xai:grok-4-1-fast-non-reasoning
    params:
      xai_api_key: ${secrets:XAI_API_KEY}

Then use in queries:

SELECT
  ai('Hello!', 'gpt-4o-mini') as openai_response,
  ai('Hello!', 'sonnet-4-5') as claude_response,
  ai('Hello!', 'grok-4-1-fast-non-reasoning') as grok_response;

Real-World Use Cases

Content Moderation: Classify user-generated content
Data Cleaning: Standardize messy text data
Entity Extraction: Extract structured info from unstructured text
Translation: Translate text in your database
Summarization: Generate summaries of long text fields
Classification: Categorize products, tickets, or documents
Search Enhancement: Generate better search terms

Tips and Best Practices

Be Specific: Clear, specific prompts get better results

-- Good
ai('Classify as positive/negative/neutral: ' || text)

-- Less effective
ai('What about: ' || text)

Limit Results: Use LIMIT for testing to avoid long waits and costs

SELECT ai('...') FROM large_table LIMIT 10;  -- Test first!

Use Cheaper Models: For simple tasks, use gpt-4o-mini or similar

Handle NULL: AI calls can fail, so handle NULL responses

SELECT coalesce(ai('...'), 'Error or no response') as result;

Troubleshooting

"No model configured" error

Make sure your spicepod.yaml has a model defined and Spice has restarted.

API key errors

Check that your .env file is in the correct directory and properly formatted.

Slow queries

Reduce the number of rows with LIMIT
Consider using a faster/cheaper model for simple tasks
Check your internet connection and API rate limits

NULL results

Check the Spice logs for error messages:

# In the terminal where `spice run` is running
# Look for errors related to the AI model

Learn More

Next Steps

Explore the text-to-sql cookbook for natural language to SQL
Check out vector search for semantic search capabilities
Try embeddings for similarity search
Learn about LLM tools for more advanced AI integration

FilesExpand file tree

README.md

Latest commit

History

README.md

File metadata and controls

AI SQL Function

What You'll Learn

Prerequisites

Quick Start

Step 1: Clone and Navigate

Step 2: Configure Your API Key

Step 3: Start Spice

Step 4: Try Your First AI Query

Example Queries

Example 1: Simple Text Generation

Example 2: Categorize Data

Example 3: Sentiment Analysis

Example 4: Data Enrichment

Example 5: Using Multiple Models

Example 6: Translation

Understanding the Configuration

How It Works

Function Signatures

Parallel Processing

Limits

Error Handling

Advanced Usage

Task History

Combining with Other Functions

Using Different Model Providers

Anthropic Claude

xAI Grok

Real-World Use Cases

Tips and Best Practices

Troubleshooting

"No model configured" error

API key errors

Slow queries

NULL results

Learn More

Next Steps