Foundation models (OpenAI, Anthropic, Google)

Learn about how to use different foundation models with Exasol.

You can call foundation model APIs (OpenAI, Anthropic, Google) from Exasol using Python UDFs, external scripts, or MCP-mediated workflows. While Exasol’s packaged extensions focus on in-database inference and managed cloud training, the UDF and scripting architecture gives you the flexibility to connect to any HTTP API.

This article describes the available patterns and the practical considerations for each model.

Pattern 1: Python UDFs that call external APIs

You can write a Python UDF that uses the requests library (or a provider SDK) to call a foundation model API. The UDF receives row data, sends it to the API, and returns the model's response as a result column.

Copy 
-- Conceptual example: calling an external model API from a Python UDF
CREATE OR REPLACE PYTHON3 SCALAR SCRIPT my_schema.call_llm_api(
    prompt VARCHAR(20000)
)
RETURNS VARCHAR(20000) AS

import requests
import json

def run(ctx):
    # Retrieve API key from an Exasol connection object
    api_key = exa.get_connection("LLM_API_CREDENTIALS").password

    response = requests.post(
        "https://api.openai.com/v1/chat/completions",
        headers={
            "Authorization": f"Bearer {api_key}",
            "Content-Type": "application/json"
        },
        json={
            "model": "gpt-4o",
            "messages": [{"role": "user", "content": ctx.prompt}]
        }
    )

    result = response.json()
    return result["choices"][0]["message"]["content"]
/

The requests library may or may not be available in the default script language container (SLC). Check the SLC documentation or your installed SLC's package list. If requests is not included, you need to build a custom SLC with it installed. For general guidance on building custom SLCs, see GPU acceleration for UDFs. For more details, refer to the EXASOL Script Languages GitHub repository.

Network access

Exasol UDFs run inside script language containers, which may have restricted network access depending on your deployment configuration. Before writing UDFs that call external APIs, confirm that your Exasol instance allows outbound HTTPS connections from the UDF execution environment. On-premises deployments often require firewall rules to be updated. SaaS deployments may have additional restrictions.

Store API keys in connection objects

Use Exasol’s CREATE CONNECTION statement to store credentials securely rather than hardcoding them in UDF source code:

Copy 
CREATE OR REPLACE CONNECTION "LLM_API_CREDENTIALS"
    TO 'https://api.openai.com'
    IDENTIFIED BY 'sk-your-api-key-here';

The UDF can then retrieve the key at runtime using exa.get_connection("LLM_API_CREDENTIALS").password

Other providers

The example connection object uses the OpenAI API. If you are calling Anthropic or Google models, adjust the endpoint URL, headers, and request body to match that provider’s API format. For example, Anthropic uses https://api.anthropic.com/v1/messages with an x-api-key header, while Google’s Gemini API uses a different authentication scheme. The UDF pattern is the same, only the HTTP request details change.

Pattern 2: External scripts and middleware

Instead of calling APIs directly from UDFs, you can run a Python script or service outside the database that:

  1. Queries Exasol for the data you want to process.
  2. Sends that data to a foundation model API.
  3. Writes the results back to Exasol.

This approach avoids the constraints of the UDF execution environment (network access, library availability, timeout limits) and gives you full control over retries, batching, and error handling. Use the Exasol Notebook Connector (GitHub repository) or Exasol AI Lab as your external processing environment.

This is the most practical pattern for batch workloads where you need to process a large number of rows through a foundation model.

Pattern 3: MCP-mediated access

The Exasol MCP Server lets AI assistants query your database through natural language. In this pattern, the foundation model (through an MCP client like Claude Desktop) reads from and writes to Exasol, but the model orchestrates the workflow rather than Exasol calling the model. This is useful for interactive exploration, ad-hoc analysis, and prototyping, but not for batch processing at scale.

For more information, see Connect AI assistants (MCP Server).

Practical considerations

Latency

Foundation model API calls add network round-trip time and model inference time to each row processed. A single API call typically takes 200ms to several seconds depending on the model and prompt length. If you are processing thousands of rows, this adds up quickly. Consider batching rows into fewer API calls where the model supports it, and use the external script pattern (Pattern 2) for large-scale workloads.

Cost

Foundation model APIs charge per token (input and output). Processing database columns through these APIs can become expensive at scale. Estimate your token usage before running batch jobs. For workloads where cost is a concern, consider whether an open-source model using the Transformers Extension can meet your accuracy requirements at lower cost.

Rate limits

Most foundation model APIs enforce rate limits (requests per minute, tokens per minute). UDFs processing many rows in parallel may exceed these limits. If you use the UDF pattern, add retry logic with backoff, or limit parallelism by processing data in smaller batches.

Data governance

Sending database contents to an external API means your data leaves the Exasol environment. Review your organization's data governance policies before sending sensitive data to third-party model APIs. For data that must stay in-database, use the Transformers Extension or Text AI Extension instead.

Next steps