llm_complete

The llm_complete function generates text completions using specified models and prompts for dynamic data generation.

Modalities

Text

Pass tabular columns in context_columns (default type: 'tabular').

SELECT
  product_name,
  llm_complete(
    {'model_name': 'gpt-4o'},
    {
      'prompt': 'Write a one-sentence product blurb.',
      'context_columns': [{'data': product_name}]
    }
  ) AS blurb
FROM UNNEST(['Wireless Headphones', 'Gaming Laptop', 'Smart Watch']) AS t(product_name);

Image

Use a vision-capable model and set type: 'image' (URL, path, or base64). Optional OpenAI detail: 'low' or 'high'.

SELECT
  product_name,
  llm_complete(
    {'model_name': 'gpt-4o'},
    {
      'prompt': 'Describe this product image, focusing on key features.',
      'context_columns': [
        {'data': product_name},
        {'data': image_url, 'type': 'image'}
      ]
    }
  ) AS image_analysis
FROM VALUES
  ('Wireless Headphones', 'https://images.unsplash.com/photo-1505740420928-5e560c06d30e?w=400'),
  ('Gaming Laptop', 'https://images.unsplash.com/photo-1496181133206-80ce9b88a853?w=400')
AS t(product_name, image_url);

Voice

Pass audio with type: 'audio' and a required transcription_model (OpenAI or Azure Whisper, e.g. whisper-1). Anthropic and Ollama do not support audio.

SELECT
  audio_id,
  llm_complete(
    {'model_name': 'gpt-4o'},
    {
      'prompt': 'Transcribe this audio verbatim.',
      'context_columns': [{
        'data': file_path,
        'type': 'audio',
        'transcription_model': 'whisper-1'
      }]
    }
  ) AS transcript
FROM VALUES
  (1, '/data/audio/meeting_01.mp3'),
  (2, '/data/audio/meeting_02.mp3')
AS t(audio_id, file_path);

1. Simple Usage (without data)

1.1 Inline Prompt

SELECT llm_complete(
           {'model_name': 'gpt-4o'},
    {'prompt': 'Explain the purpose of Flock.'}
) AS flock_purpose;

Description: This example uses an inline prompt to generate a text completion with the gpt-4 model. The prompt asks the model to explain the purpose of Flock. The function returns a completion for each row based on the provided prompt.

1.2 Named Prompt

SELECT llm_complete(
           {'model_name': 'summarizer', 'secret_name': 'summarizer_secret'},
    {'prompt_name': 'description-generation', 'version': 1, 'context_columns': [{'data': product_name}]}
) AS product_description
FROM UNNEST(['Wireless Headphones', 'Gaming Laptop', 'Smart Watch']) AS t(product_name);

Description: In this example, a named prompt description-generation is used with the summarizer model. The function generates product descriptions using data from the product_name column for each row in the sample data.

2. Actual Usage (with data)

WITH sample_products AS (
    SELECT *
    FROM (VALUES
        (1, 'MacBook Pro', 'High-performance laptop'),
        (2, 'AirPods Pro', 'Wireless noise-cancelling earbuds'),
        (3, 'iPad Air', 'Lightweight tablet for creativity')
    ) AS t(product_id, product_name, description)
),
enhanced_products AS (
    SELECT product_id,
           product_name,
           llm_complete(
               {'model_name': 'reduce-model'},
               {'prompt_name': 'summarize-content', 'version': 2, 'context_columns': [{'data': product_name}]}
           ) AS enhanced_description
    FROM sample_products
)
SELECT product_id, product_name, enhanced_description
FROM enhanced_products
WHERE LENGTH(enhanced_description) > 50;

Description: This actual example demonstrates the use of a pre-configured prompt summarize-content with version 2 and the reduce-model. It processes the product_name column and generates a summarized description. The query then filters out rows where the generated description is shorter than 50 characters.

3. Input Parameters

The llm_complete function accepts three structured inputs: model configuration, prompt configuration, and input data columns.

3.1 Model Configuration

Parameter: model_name and secret_name

3.1.1 Model Selection

Description: Specifies the model used for text generation.
Example:
{ 'model_name': 'gpt-4o' }

3.1.2 Model Selection with Secret

Description: Specifies the model along with the secret name to be used for authentication when accessing the model.

Example:

{ 'model_name': 'gpt-4o', 'secret_name': 'your_secret_name' }

3.2 Prompt Configuration

Parameter: prompt or prompt_name with context_columns

3.2.1 Inline Prompt

Directly provides the prompt with context columns.

Example:

{ 'prompt': 'Summarize the content: {article}', 'context_columns': [{'data': article_content, 'name': 'article'}] }

3.2.2 Named Prompt

References a pre-configured prompt with context columns.

Example:

{ 'prompt_name': 'summarize-content', 'context_columns': [{'data': article_content}] }

3.2.3 Named Prompt with Version

References a specific version of a prompt with context columns.

Example:

{ 'prompt_name': 'summarize-content', 'version': 2, 'context_columns': [{'data': article_content}] }

3.3 Context Columns Configuration

Parameter: context_columns array
Description: Specifies the columns from the table to be passed to the model as input. Each column can have three properties:
- data: The SQL column data (required)
- name: Custom name for the column to be referenced in the prompt (optional)
- type: Data type — "tabular" (default), "image", or "audio" (audio requires transcription_model)

Example:

'context_columns': [
  {'data': product_name, 'name': 'product'},
  {'data': image_url, 'type': 'image'},
  {'data': audio_path, 'type': 'audio', 'transcription_model': 'whisper-1'}
]

4. Output

The function generates a completion for each row based on the provided prompt and input data.

Column Type: JSON
Behavior: Maps over each row and generates a response per tuple. By default the JSON holds free-form model text. Pass a JSON schema in model_parameters to constrain the response shape (OpenAI response_format, Ollama format, Anthropic output_format / tool-use — see Anthropic and Model parameters). Use LOAD JSON and dot notation to extract fields.

LOAD JSON;

SELECT
  product_name,
  response.category::VARCHAR AS category,
  response.confidence::DOUBLE AS confidence
FROM (
  SELECT
    product_name,
    llm_complete(
      {
        'model_name': 'gpt-4o',
        'model_parameters': '{
          "response_format": {
            "type": "json_schema",
            "json_schema": {
              "name": "product_categorization",
              "schema": {
                "type": "object",
                "properties": {
                  "category": { "type": "string" },
                  "confidence": { "type": "number" }
                },
                "required": ["category", "confidence"],
                "additionalProperties": false
              }
            },
            "strict": true
          }
        }'
      },
      {
        'prompt': 'Categorize this product and provide a confidence score.',
        'context_columns': [{'data': product_name}]
      }
    ) AS response
  FROM UNNEST(['Wireless Bluetooth Headphones', 'Organic Cotton T-Shirt']) AS t(product_name)
);

Home

Getting Started

Resource Management

Scalar Functions

Aggregate Functions

Hybrid Search

Operations

Contributing

llm_complete

Modalities

Text

Image

Voice

1. Simple Usage (without data)

1.1 Inline Prompt

1.2 Named Prompt

2. Actual Usage (with data)

3. Input Parameters

3.1 Model Configuration

3.1.1 Model Selection

3.1.2 Model Selection with Secret

3.2 Prompt Configuration

3.2.1 Inline Prompt

3.2.2 Named Prompt

3.2.3 Named Prompt with Version

3.3 Context Columns Configuration

4. Output

​Modalities

​Text

​Image

​Voice

​1. Simple Usage (without data)

​1.1 Inline Prompt

​1.2 Named Prompt

​2. Actual Usage (with data)

​3. Input Parameters

​3.1 Model Configuration

​3.1.1 Model Selection

​3.1.2 Model Selection with Secret

​3.2 Prompt Configuration

​3.2.1 Inline Prompt

​3.2.2 Named Prompt

​3.2.3 Named Prompt with Version

​3.3 Context Columns Configuration

​4. Output

Modalities

Text

Image

Voice

1. Simple Usage (without data)

1.1 Inline Prompt

1.2 Named Prompt

2. Actual Usage (with data)

3. Input Parameters

3.1 Model Configuration

3.1.1 Model Selection

3.1.2 Model Selection with Secret

3.2 Prompt Configuration

3.2.1 Inline Prompt

3.2.2 Named Prompt

3.2.3 Named Prompt with Version

3.3 Context Columns Configuration

4. Output