llm_last Function

The llm_last function is used to extract the least relevant result from a set of rows based on a model's prompt and input columns. It operates over a set of rows, generally combined with a GROUP BY clause, to return the least relevant row for each group.

• 1. Usage Examples
  • 1.1. Example without GROUP BY
  • 1.2. Example with GROUP BY
  • 1.3. Using a Named Prompt with GROUP BY
  • 1.4. Advanced Example with Multiple Columns and GROUP BY
• 2. Input Parameters
  • 2.1 Model Configuration
  • 2.2. Prompt Configuration
  • 2.3. Context Columns Configuration
• 3. Output

1. Usage Examples

1.1. Example without GROUP BY

Retrieve the least relevant product feature across all rows:

SELECT llm_last(
    {'model_name': 'gpt-4o'},
    {'prompt': 'professional work productivity', 'context_columns': [{'data': product_name}, {'data': product_description}]}
) AS last_product_feature
FROM VALUES
    ('MacBook Pro', 'High-performance laptop with M2 chip and Retina display'),
    ('AirPods Pro', 'Wireless earbuds with active noise cancellation'),
    ('iPad Air', 'Lightweight tablet perfect for creativity and productivity')
AS t(product_name, product_description);

Description: This query returns the product least relevant to the given sentence from all product descriptions and product names.

1.2. Example with GROUP BY

Retrieve the least relevant product feature for each product category:

SELECT category,
       llm_last(
           {'model_name': 'gpt-4o'},
           {'prompt': 'high-quality audio equipment for entertainment', 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM VALUES
    ('Electronics', 'Premium Headphones', 'High-quality wireless headphones with superior sound', 89.99),
    ('Electronics', 'Gaming Mouse', 'Precision gaming mouse with RGB lighting', 45.99),
    ('Electronics', 'Wireless Keyboard', 'Ergonomic wireless keyboard with backlight', 79.99),
    ('Books', 'Python Programming', 'Complete guide to Python programming language', 39.99),
    ('Books', 'Data Science Guide', 'Comprehensive data science methodology book', 49.99),
    ('Books', 'Machine Learning', 'Introduction to machine learning algorithms', 59.99)
AS products(category, product_name, product_description, price)
GROUP BY category;

Description: The query groups the products by category and returns the product least relevant to the given sentence for each group.

1.3. Using a Named Prompt with GROUP BY

Use a reusable prompt, such as "least-relevant-detail", to extract the least relevant feature for each product category:

SELECT category,
       llm_last(
           {'model_name': 'gpt-4o', 'secret_name': 'product_key'},
           {'prompt_name': 'least-relevant-detail', 'version': 1, 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM (
    SELECT * FROM (
        VALUES
            ('Electronics', 'MacBook Pro', 'High-performance laptop with M2 chip'),
            ('Electronics', 'iPhone 15', 'Latest smartphone with advanced camera system'),
            ('Accessories', 'Magic Mouse', 'Wireless mouse with multi-touch surface'),
            ('Accessories', 'USB-C Cable', 'Fast charging cable for modern devices')
    ) AS t(category, product_name, product_description)
)
GROUP BY category;

Description: This example leverages a named prompt (least-relevant-detail) to extract the least relevant feature for each product category. The query groups the results by category.

1.4. Advanced Example with Multiple Columns and GROUP BY

Retrieve the least relevant feature for products grouped by category, using both the product name and description:

WITH sample_products AS (
    SELECT * FROM (
        VALUES
            ('Electronics', 'MacBook Pro', 'High-performance laptop with M2 chip'),
            ('Electronics', 'iPhone 15', 'Latest smartphone with advanced camera'),
            ('Electronics', 'iPad Air', 'Lightweight tablet for creativity')
    ) AS t(category, product_name, product_description)
)
SELECT category,
       llm_last(
           {'model_name': 'gpt-4o'},
           {'prompt': 'mobile communication and calls', 'context_columns': [{'data': product_name}, {'data': product_description}]}
       ) AS last_product_feature
FROM sample_products
GROUP BY category;

Description: This query extracts the product least relevant to the given sentence from both the product_name and product_description columns, grouped by product category.

2. Input Parameters

2.1 Model Configuration

• Parameter: model_name and secret_name

2.1.1 Model Selection

• Description: Specifies the model used for text generation.
• Example:

  { 'model_name': 'gpt-4o' }

2.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' }

2.2. Prompt Configuration

Two types of prompts can be used:

1. Inline Prompt

   • Directly provides the prompt in the query with context columns.
   • Example:

     {'prompt': 'professional audio equipment', 'context_columns': [{'data': product_name}, {'data': product_description}]}

2. Named Prompt

   • Refers to a pre-configured prompt by name.
   • Example:

     {'prompt_name': 'least-relevant-detail'}

3. Named Prompt with Version

   • Refers to a specific version of a pre-configured prompt.
   • Example:

     {'prompt_name': 'least-relevant-detail', 'version': 1}

2.3. Context Columns Configuration

• Key: context_columns array.
• Purpose: Maps table columns to provide input data for the model. 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) or "image" (optional)
• Example:

  'context_columns': [
    {'data': product_name, 'name': 'product'},
    {'data': product_description},
    {'data': image_url, 'type': 'image'}
  ]

3. Output

• Type: JSON object.
• Behavior: The function returns a JSON object containing the values of the columns you provided in the input. The structure of the returned JSON will mirror the input columns' values.

Output Example:
For a query that extracts the least relevant feature, the result could look like:

• Input Rows:

  • product_name: "Wireless Headphones"
  • product_description: "High-quality wireless headphones with noise cancellation."

• Output JSON:

  {
    "product_name": "Wireless Headphones",
    "product_description": "High-quality wireless headphones with noise cancellation."
  }

The output contains the values for product_name and product_description from the least relevant row based on the prompt.