AI Functions

Install AI Functions

To install AI Functions, navigate to AI > AI & ML Functions, select the deployment on which to install AI Functions. In the AI Functions tab, select Install, review the AI Functions Summary and then select Deploy.

Once the AI Functions are installed, query them in the SQL Editor or SingleStore notebooks. SingleStore provides the following AI Functions:

Category	Function
Text Processing Functions	SQL AI_COMPLETE(text, model)
	SQL AI_SENTIMENT(text, model)
	SQL AI_TRANSLATE(text, source_languages, target_languages, model)
	SQL AI_SUMMARIZE(text, model, max_lengths)
	SQL AI_CLASSIFY(text, categories, model)
	SQL AI_EXTRACT(text, questions, model)
Embedding Function
Embedding Function	SQL EMBED_TEXT(text, model)

Edit AI Functions

To edit AI Functions, navigate to Settings in the right navigation of the AI Functions tab and select Edit. Alternatively, select the ellipsis (vertical three dots) in the right and select Edit AI Functions. On the Edit AI Functions page, view or select the following:

Workspace Group	The workspace group in which the AI Function is installed and running.
Models	Default models used by AI Functions for inference and embedding. LLM Model: Uses Anthropic’s Claude model family for inference. Embedding Model: Uses an optimized embedding model for text vectorization. Supported models include the Qwen and Amazon Titan embedding families.
Settings	Container Region: Select the region of your container. Scaling: Select the minimum and maximum active replica. Note Auto-scale depends upon the container load.
Cost Estimation	View the cost estimation of LLM Model, Embedding Model, and Compute. In Compute, runtime cost scales with the replica count.
Region	View the region of the following: Workspace Group Container LLM Model Embedding Model

Select Next, review the changes, and select Save to save your settings.

Uninstall AI Functions

To uninstall AI Functions, select the ellipsis (vertical three dots) in the right and then select Uninstall AI Functions. Confirm the uninstallation and select Uninstall.

Text Processing Functions

AI_COMPLETE

Provides batched LLM powered completion of every input text. Used for general purpose text generation, completion, and complex reasoning.

Syntax

SQL

AI_COMPLETE(text, model)

Arguments

text: A prompt.
model: An LLM model.

Return Type

string

Usage

Basic usage with the default model

SQL

SELECT cluster.AI_COMPLETE('Life is like a box of') AS completion;

Basic usage with a specific model

SQL

SELECT cluster.AI_COMPLETE('Life is like a box of', model => 'anthropic-claude-3-5-haiku') as completion;

Input example on database

SQL

SELECT cluster.AI_COMPLETE(column_1) FROM table;

AI_SENTIMENT

Provides sentiment classification and score for all user-defined inputs.

Syntax

SQL

AI_SENTIMENT(text, model)

Arguments

text: A prompt.
model: An LLM model.

Return Type

string

Usage

Basic usage with default model	SQL SELECT cluster.AI_SENTIMENT('Wow, that food was rotten') AS sentiment;
Basic usage with selected model	SQL
Input example on database	SQL SELECT cluster.AI_SENTIMENT(column_1) FROM table;

AI_TRANSLATE

Provides translation of user-provided documents from source language to target language. Supports multi-language translation.

Syntax

SQL

AI_TRANSLATE(text, source_languages, target_languages, model)

Arguments

text: A prompt.
source_languages: The language in which the prompt is written.
target_languages: The language to which the prompt gets translated.
model: An LLM model.

Return Type

string

Usage

Basic usage with default model

SQL

SELECT cluster.AI_TRANSLATE('Hello, how are you?', 'English', 'Spanish') AS summary;

Basic usage with selected model

SQL

SELECT cluster.AI_TRANSLATE('Hello, how are you?', 'English', 'Spanish', 'claude-3-5-sonnet') AS translation;

Input example on database

SQL

SELECT cluster.AI_TRANSLATE(column_1, "source_language", "target_language") FROM table;

AI_SUMMARIZE

Provides summary of user-provided documents within the specified length.

Syntax

SQL

AI_SUMMARIZE(text, model, max_lengths)

Arguments

text: A prompt.
model: An LLM model.
max_lengths: Maximum length of the summary.

Return Type

string

Usage

Basic usage with default model and length

SQL

SELECT cluster.AI_SUMMARIZE('That movie was amazing ... I would definitely go again.') AS summary;

Basic usage with selected model

SQL

SELECT cluster.AI_SUMMARIZE("""Person A: 'Hey! Did you get a chance to see that new Hollywood sci-fi movie?'
    Person B: 'Yeah, I saw it last night. I was on the edge of my seat the entire time! What did you think?'
    Person A: 'I thought it was incredible. The plot was a bit complicated, but the special effects and action scenes were amazing.'""",
    model => 'anthropic-claude-3-5-haiku', max_length => 3) as summary;

Input example on database

SQL

SELECT cluster.AI_SUMMARIZE(column_1) FROM table;

AI_CLASSIFY

Provides classification of each input text into one of the given categories or labels.

Syntax

SQL

AI_CLASSIFY(text, categories, model)

Arguments

text: A prompt.
categories: Categories for classification.
model: An LLM model.

Return Type

string

Usage

Basic usage with default model

SQL

SELECT cluster.AI_CLASSIFY('Hello, how are you?', '[greeting, goodbye]') AS classification;

Basic usage with selected model

SQL

SELECT cluster.AI_CLASSIFY('Hello, Nice to meet you!', '[greeting, goodbye]', model => 'anthropic-claude-3-5-haiku') as classification;

Input example on database

SQL

SELECT cluster.AI_CLASSIFY(column_1, categories) FROM table;

AI_EXTRACT

Extracts information from a block or text based on the specified natural language question.

Syntax

SQL

AI_EXTRACT(text, questions, model)

Arguments

text: A prompt.
questions: Input natural language question on which the LLM model extracts information.
model: An LLM model.

Return Type

string

Usage

Basic usage with default model

SQL

SELECT cluster.AI_EXTRACT("Hello, how are you?', 'What is the first word' ) AS answer;

Basic usage with selected model

SQL

SELECT cluster.AI_EXTRACT("Hello, how are you?', 'What is the first word', model => 'anthropic-claude-3-5-haiku' ) AS answer;

Input example on database

SQL

SELECT cluster.AI_EXTRACT(column_1, question) FROM table;

Embedding Function

EMBED_TEXT

Provides batched embeddings of all input text. Converts text into high-dimensional vector embeddings for semantic search and RAG applications.

Syntax

SQL

EMBED_TEXT(text, model)

Arguments

text: A prompt.
model: An embedding model.

Return Type

bytes

Usage

Basic usage with default model

SQL

SELECT cluster.EMBED_TEXT('Hello, how are you?') AS embedding;

Basic usage with selected embedding model

SQL

SELECT cluster.EMBED_TEXT('Hello, how are you?', model => 'shared-qwen3-embed-0-6b') as embedding;

Input example on database

SQL

SELECT cluster.EMBED_TEXT(column_1) FROM table;

Examples

The following examples demonstrate how to use AI functions with the following customer_reviews table.

SQL

CREATE DATABASE reviews;

USE reviews;

CREATE TABLE customer_reviews (
   review_id INT PRIMARY KEY,
   product_id VARCHAR(50),
   product_name VARCHAR(255),
   customer_name VARCHAR(100),
   review_text TEXT,
   rating INT,
   review_date DATETIME,
   language VARCHAR(20) DEFAULT 'English',
   response_text TEXT,
   review_embedding BLOB
);

-- Insert data
INSERT INTO customer_reviews (
   review_id,
   product_id,
   product_name,
   customer_name,
   review_text,
   rating,
   review_date,
   language
) VALUES
(1, 'PROD-101', 'Wireless Headphones', 'John Smith',
'These headphones are amazing! The sound quality is crystal clear and the battery lasts for days. Highly recommend for anyone looking for quality audio.',
5, '2026-05-01', 'English'),

(2, 'PROD-101', 'Wireless Headphones', 'Maria Garcia',
'Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful.',
1, '2026-05-02', 'English'),

(3, 'PROD-102', 'Smart Watch', 'David Lee',
'Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.',
4, '2026-05-03', 'English'),

(4, 'PROD-101', 'Wireless Headphones', 'Sophie Martin',
'Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.',
5, '2026-05-04', 'French'),

(5, 'PROD-103', 'Laptop Stand', 'Ahmed Hassan',
'Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.',
5, '2026-05-05', 'English');

Generate Follow-up Questions for Negative Reviews

The following example uses the AI_COMPLETE function to generate follow-up questions for negative reviews:

SQL

SELECT
    review_id,
    customer_name,
    product_name,
    review_text,
    cluster.AI_COMPLETE(
        CONCAT(
            'Based on this negative review: "',
            review_text,
            '", generate three specific follow-up questions to better understand the issue.'
        )
    ) AS follow_up_questions
FROM customer_reviews
WHERE rating <= 2;

+-----------+----------------+----------------------+--------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| review_id | customer_name  | product_name         | review_text                                                  | follow_up_questions                                                                                                                                                                               |
+-----------+----------------+----------------------+--------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2         | Maria Garcia   | Wireless Headphones  | Disappointed with the build quality. They broke after just   | 1. Can you describe exactly how the product broke and in what specific way?                                                                                                                       |
|           |                |                      | two weeks of normal use. Customer service was unhelpful.     | 2. Did you contact customer service through phone, email, or another method, and what precisely did they say that made you feel they were unhelpful?                                              |
|           |                |                      |                                                              | 3. What type of normal use were you subjecting the product to when it broke?                                                                                                                      |
+-----------+----------------+----------------------+--------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Compare Customer Ratings with AI Sentiment

The following example uses the AI_SENTIMENT function to compare customer ratings with AI-generated sentiment analysis:

SQL

SELECT
    review_id,
    customer_name,
    product_name,
    rating AS star_rating,
    review_text,
    cluster.AI_SENTIMENT(review_text) AS ai_sentiment
FROM customer_reviews
ORDER BY review_date DESC;

+-----------+----------------+----------------------+-------------+---------------------------------------------------------------------------------------------------------------------+----------------------------------------------+
| review_id | customer_name  | product_name         | star_rating | review_text                                                                                                         | ai_sentiment                                 |
+-----------+----------------+----------------------+-------------+---------------------------------------------------------------------------------------------------------------------+----------------------------------------------+
| 3         | David Lee      | Smart Watch          | 4           | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.          | {'sentiment': 'neutral', 'score': '0.5'}     |
| 1         | John Smith     | Wireless Headphones  | 5           | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days.                    | {'sentiment': 'positive', 'score': '0.9'}    |
| 5         | Ahmed Hassan   | Laptop Stand         | 5           | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.        | {'sentiment': 'positive', 'score': '0.95'}   |
| 4         | Sophie Martin  | Wireless Headphones  | 5           | Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.                            | {'sentiment': 'positive', 'score': '0.90'}   |
| 2         | Maria Garcia   | Wireless Headphones  | 1           | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful. | {'sentiment': 'negative', 'score': '0.85'}   |
+-----------+----------------+----------------------+-------------+---------------------------------------------------------------------------------------------------------------------+----------------------------------------------+

Standardize Reviews in English

The following example uses the AI_TRANSLATE function to standardize the reviews in English language:

SQL

SELECT
    review_id,
    customer_name,
    product_name,
    language AS original_language,
    review_text AS original_review,
    CASE
        WHEN language = 'English' THEN review_text
        ELSE cluster.AI_TRANSLATE(
            review_text,
            language,
            'English'
        )
    END AS standardized_review_english
FROM customer_reviews
ORDER BY review_date DESC;

+-----------+----------------+----------------------+-------------------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
| review_id | customer_name  | product_name         | original_language | original_review                                                                                                     | standardized_review_english                                                                                         |
+-----------+----------------+----------------------+-------------------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+
| 3         | David Lee      | Smart Watch          | English           | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.          | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.          |
| 1         | John Smith     | Wireless Headphones  | English           | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days.                    | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days.                    |
| 5         | Ahmed Hassan   | Laptop Stand         | English           | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.        | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.        |
| 4         | Sophie Martin  | Wireless Headphones  | French            | Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.                            | Excellent sound quality! I use them every day for work and leisure.                                                 |
| 2         | Maria Garcia   | Wireless Headphones  | English           | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful. | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful. |
+-----------+----------------+----------------------+-------------------+---------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------+

Generate Product-Level Summaries

The following example uses the AI_SUMMARIZE function to generate product-level summaries from customer reviews:

SQL

SELECT
    product_id,
    product_name,
    COUNT(*) AS review_count,
    AVG(rating) AS avg_rating,
    cluster.AI_SUMMARIZE(
        GROUP_CONCAT(review_text SEPARATOR '. ')
    ) AS product_summary
FROM customer_reviews
GROUP BY product_id, product_name;

+------------+----------------------+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| product_id | product_name         | review_count | avg_rating | product_summary                                                                                                                                                   |
+------------+----------------------+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PROD-103   | Laptop Stand         | 1            | 5.0000     | Excellent ergonomic home office solution with sturdy construction and adjustable height, providing good value.                                                    |
| PROD-101   | Wireless Headphones  | 3            | 3.6667     | Mixed reviews for headphones: praised for excellent sound quality and long battery life, but criticized for poor durability and potential customer service issues.|
| PROD-102   | Smart Watch          | 1            | 4.0000     | Fitness tracker offers good value, though battery life is shorter than expected.                                                                                  |
+------------+----------------------+--------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Classify Review Intent

The following example uses the AI_CLASSIFY function to classify the review intent:

SQL

SELECT
    review_id,
    customer_name,
    product_name,
    rating,
    review_text,
    cluster.AI_CLASSIFY(
        review_text,
        '[praise, complaint, suggestion, question, comparison]'
    ) AS review_intent
FROM customer_reviews
ORDER BY review_date DESC;

+-----------+----------------+----------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------------+
| review_id | customer_name  | product_name         | rating | review_text                                                                                                         | review_intent |
+-----------+----------------+----------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------------+
| 3         | David Lee      | Smart Watch          | 4      | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.          | complaint     |
| 1         | John Smith     | Wireless Headphones  | 5      | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days.                    | praise        |
| 5         | Ahmed Hassan   | Laptop Stand         | 5      | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.        | praise        |
| 4         | Sophie Martin  | Wireless Headphones  | 5      | Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.                            | praise        |
| 2         | Maria Garcia   | Wireless Headphones  | 1      | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful. | complaint     |
+-----------+----------------+----------------------+--------+---------------------------------------------------------------------------------------------------------------------+---------------+

Extract Multiple Insights

The following example uses the AI_EXTRACT function to extract multiple insights from customer reviews:

SQL

SELECT
    review_id,
    product_name,
    review_text,
    cluster.AI_EXTRACT(
        review_text,
        'What product features are mentioned?'
    ) AS features_mentioned,
    cluster.AI_EXTRACT(
        review_text,
        'How long has the customer used this product?'
    ) AS usage_duration
FROM customer_reviews;

+-----------+----------------------+--------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------------------------+
| review_id | product_name         | review_text                                                                                                  | features_mentioned                                            | usage_duration              |
+-----------+----------------------+--------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------------------------+
| 5         | Laptop Stand         | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny. | Sturdy construction, adjustable height                        | Not specified in the review |
| 2         | Wireless Headphones  | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service         | Build quality issues, broke after two weeks was unhelpful.    | Two weeks                   |
| 4         | Wireless Headphones  | Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.                     | Sound quality                                                 | Daily (ongoing)             |
| 3         | Smart Watch          | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.   | Battery life (shorter than advertised), good value for price. | Not specified in the review |
| 1         | Wireless Headphones  | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days.             | Sound quality (crystal clear), long battery life              | Not specified in the review |
+-----------+----------------------+--------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------+-----------------------------+

Perform Semantic Search

The following example uses the EMBED_TEXT function to perform semantic search on customer reviews.

SQL

SET batch_external_functions = AUTO;

SELECT
   review_id,
   product_name,
   review_text,
   cluster.EMBED_TEXT(review_text) AS review_embedding
FROM customer_reviews;

UPDATE customer_reviews
SET review_embedding = cluster.EMBED_TEXT(review_text)
WHERE review_embedding IS NULL;

WITH search_query AS (
   SELECT cluster.EMBED_TEXT(
       'battery problems and short lifespan'
   ) AS query_embedding
)
SELECT
   cr.review_id,
   cr.product_name,
   cr.review_text,
   cr.rating,
   DOT_PRODUCT(
       cr.review_embedding,
       sq.query_embedding
   ) AS relevance_score
FROM customer_reviews cr,
    search_query sq
WHERE cr.review_embedding IS NOT NULL
ORDER BY relevance_score DESC;

+-----------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------------+
| review_id | product_name        | review_text                                                                                                                                            | rating | relevance_score |
+-----------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------------+
| 3         | Smart Watch         | Great fitness tracker, but the battery life is shorter than advertised. Overall, good value for the price.                                             | 4      | NULL            |
| 1         | Wireless Headphones | These headphones are amazing! The sound quality is crystal clear and the battery lasts for days. Highly recommend for anyone looking for quality audio.| 5      | NULL            |
| 2         | Wireless Headphones | Disappointed with the build quality. They broke after just two weeks of normal use. Customer service was unhelpful.                                    | 1      | NULL            |
| 4         | Wireless Headphones | Excellente qualité sonore! Je les utilise tous les jours pour le travail et les loisirs.                                                               | 5      | NULL            |
| 5         | Laptop Stand        | Perfect ergonomic solution for my home office. Sturdy construction and adjustable height. Worth every penny.                                           | 5      | NULL            |
+-----------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+--------+-----------------+

Complete Customer Insights

The following example uses the AI_SUMMARIZE and AI_EXTRACT functions to generate complete customer insights for each product:

SQL

SELECT
    product_id,
    product_name,
    COUNT(*) AS total_reviews,
    AVG(rating) AS avg_rating,
    cluster.AI_SUMMARIZE(
        GROUP_CONCAT(review_text SEPARATOR '. ')
    ) AS overview,
    cluster.AI_EXTRACT(
        GROUP_CONCAT(review_text SEPARATOR '. '),
        'What are the top three issues customers mention?'
    ) AS top_issues,
    cluster.AI_EXTRACT(
        GROUP_CONCAT(review_text SEPARATOR '. '),
        'What do customers love most about this product?'
    ) AS top_strengths
FROM customer_reviews
GROUP BY product_id, product_name;

+------------+----------------------+---------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+---------------------------------------------+
| product_id | product_name         | total_reviews | avg_rating | overview                                                                                                                                                            | top_issues                                                               | top_strengths                               |
+------------+----------------------+---------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+---------------------------------------------+
| PROD-103   | Laptop Stand         | 1             | 5.0000     | Excellent ergonomic home office solution with sturdy construction and adjustable height, providing good value.                                                      | 1. Sturdy construction 2. Adjustable height 3. Good value for money      | Sturdy construction and adjustable height   |
| PROD-101   | Wireless Headphones  | 3             | 3.6667     | Mixed reviews for headphones: praised for excellent sound quality and long battery life, but criticized for poor durability and potential customer service issues.  | 1. Sound quality 2. Battery life 3. Build quality (durability)           | Sound quality and battery life              |                                                                         |                                             |
| PROD-102   | Smart Watch          | 1             | 4.0000     | Fitness tracker offers good value, though battery life is shorter than expected.                                                                                    | 1. Battery life 2. Price 3. Functionality                                | Good value for the price                    |
+------------+----------------------+---------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------+---------------------------------------------+

Example Notebook

The following notebook demonstrates AI Functions:

demonstrate-some-common-ai-function-usecases.ipynb

SingleStore Notebooks

Demonstrate some common AI function usecases

Note

You can use your existing Standard or Premium workspace with this Notebook.

This feature is currently in Private Preview. Please reach out to support@singlestore.com to confirm if this feature can be enabled in your org.

This Jupyter notebook will help you:

Load the Amazon Fine Foods Reviews dataset from Kaggle
Store the data in SingleStore
Demonstrate powerful AI Functions for text processing and analysis

Prerequisites: Ensure AI Functions are installed on your deployment (AI Services > AI & ML Functions).

Create some simple tables

This setup establishes a basic relational structure to store some reviews for restaurants. Ensure you have selected a database and have CREATE permissions to create/delete tables.

SQL

%%sql
CREATE DATABASE IF NOT EXISTS temp;
USE temp;

SQL

%%sql
DROP TABLE IF EXISTS reviews;

CREATE TABLE IF NOT EXISTS reviews (
    Id INT PRIMARY KEY,
    ProductId VARCHAR(20),
    UserId VARCHAR(50),
    ProfileName VARCHAR(255),
    HelpfulnessNumerator INT,
    HelpfulnessDenominator INT,
    Score INT,
    Time BIGINT,
    Summary TEXT,
    Text TEXT
);

Install the required packages

Python

!pip install -q httplib2 kagglehub pandas

Download and Load Dataset

Python

import kagglehub
import pandas as pd

# Download the Amazon Fine Foods Reviews dataset from Kaggle
print("Downloading dataset from Kaggle...")
path = kagglehub.dataset_download("snap/amazon-fine-food-reviews")
print(f"Dataset downloaded to: {path}")

# Read the CSV file
df = pd.read_csv(f"{path}/Reviews.csv")

# Display dataset info
print(f"\nDataset shape: {df.shape}")
print(f"Columns: {list(df.columns)}")
print("\nFirst few rows:")
df.head()

Load Data into SingleStore

Python

import singlestoredb as s2

# Create SQLAlchemy engine instead of regular connection
engine = s2.create_engine(database='temp')

# Take a sample of 10,000 reviews for demo purposes
sample_df = df.head(10000).copy()

print(f"Loading {len(sample_df)} reviews into SingleStore...")

# Write dataframe to SingleStore table using SQLAlchemy engine
sample_df.to_sql(
    'reviews',
    con=engine,  # Use engine instead of connection
    if_exists='append',
    index=False,
    chunksize=1000
)

print("Data loaded successfully!")

Verify Data Load

SQL

%%sql
-- Check the number of reviews loaded
SELECT COUNT(*) as total_reviews FROM reviews;

Sample Data Preview

SQL

%%sql
-- View sample reviews
SELECT Id, ProductId, Score, Summary, LEFT(Text, 100) as Review_Preview
FROM reviews
LIMIT 10;

AI Functions Demonstrations

Now let's explore the power of SingleStore AI Functions for text analysis and processing. Ensure that AI functions are enabled for the org and you are able to list the available AI functions

SQL

%%sql
SHOW functions in cluster;

SQL

%%sql
-- AI_COMPLETE: Ask general questions and get LLM-powered completions
SELECT cluster.AI_COMPLETE(
    'What is SingleStore?'
) AS completion;

SQL

%%sql
-- AI_SENTIMENT: Analyze sentiment of customer reviews for a specific product
-- WHERE ProductId = <Your choice>
-- Remember to specify the datbase name. In this example 'temp' is the Database name
SELECT
    Id,
    ProductId,
    Score,
    LEFT(Text, 80) as Review_Snippet,
    cluster.AI_SENTIMENT(Text) AS sentiment
FROM temp.reviews
WHERE ProductId = 'B000NY8ODS'
LIMIT 10;

SQL

%%sql
-- Aggregate sentiment analysis across products
-- Using CTE to filter and prepare data first
WITH filtered_reviews AS (
    SELECT
        ProductId,
        Text
    FROM temp.reviews
    WHERE ProductId IN (
        SELECT ProductId
        FROM temp.reviews
        GROUP BY ProductId
        HAVING COUNT(*) >= 5
    )
    LIMIT 100
),
grouped_reviews AS (
    SELECT
        ProductId,
        COUNT(*) as review_count,
        GROUP_CONCAT(Text SEPARATOR '. ') as combined_text
    FROM filtered_reviews
    GROUP BY ProductId
    LIMIT 5
)
SELECT
    ProductId,
    review_count,
    cluster.AI_SENTIMENT(combined_text) as overall_sentiment
FROM grouped_reviews;

SQL

%%sql
-- AI_SUMMARIZE: Create concise summaries of lengthy reviews
-- Filter long reviews first using CTE
WITH long_reviews AS (
    SELECT
        Id,
        ProductId,
        Text,
        LEFT(Text, 150) as Original_Review
    FROM temp.reviews
    WHERE LENGTH(Text) > 200
    LIMIT 5
)
SELECT
    Id,
    ProductId,
    Original_Review,
    cluster.AI_SUMMARIZE(
        Text,
        'aifunctions_chat_default',
        15
    ) AS summary
FROM long_reviews;

SQL

%%sql
-- AI_CLASSIFY: Classify customer feedback into categories
-- Filter negative reviews first using CTE
WITH negative_reviews AS (
    SELECT
        Id,
        ProductId,
        Text,
        LEFT(Text, 100) as Review_Text
    FROM temp.reviews
    WHERE Score <= 3
    LIMIT 10
)
SELECT
    Id,
    ProductId,
    Review_Text,
    cluster.AI_CLASSIFY(
        Text,
        '[quality, price, shipping, taste]'
    ) AS classification
FROM negative_reviews;

SQL

%%sql
-- AI_EXTRACT: Extract specific information from reviews
-- Filter positive reviews first using CTE
WITH positive_reviews AS (
    SELECT
        Id,
        ProductId,
        Text,
        LEFT(Text, 100) as Review_Text
    FROM temp.reviews
    WHERE Score >= 4
    LIMIT 10
)
SELECT
    Id,
    ProductId,
    Review_Text,
    cluster.AI_EXTRACT(
        Text,
        'Does this customer indicate they will buy this product again? Answer with yes, no, or unclear only'
    ) AS repeat_purchase_intent
FROM positive_reviews;

SQL

%%sql
-- AI_EXTRACT: Identify reviews with high churn risk
-- Filter low-rated reviews first using CTE
WITH low_rated_reviews AS (
    SELECT
        Id,
        ProductId,
        Score,
        Text,
        LEFT(Text, 120) as Review_Text
    FROM temp.reviews
    WHERE Score <= 2
    LIMIT 10
)
SELECT
    Id,
    ProductId,
    Score,
    Review_Text,
    cluster.AI_EXTRACT(
        Text,
        'Is this customer at high risk of not purchasing again? Answer with high, medium, or low only'
    ) AS churn_risk
FROM low_rated_reviews;

SQL

%%sql
-- AI_TRANSLATE: Translate text between languages
-- Filter reviews with substantial summaries first using CTE
WITH translatable_reviews AS (
    SELECT
        Id,
        Summary as Original_English
    FROM temp.reviews
    WHERE Score = 5
    AND Summary IS NOT NULL
    AND LENGTH(Summary) > 20
    LIMIT 5
)
SELECT
    Id,
    Original_English,
    cluster.AI_TRANSLATE(
        Original_English,
        'english',
        'spanish'
    ) AS spanish_translation
FROM translatable_reviews;

SQL

%%sql
-- Combined AI Functions: Comprehensive product analysis
-- Filter to products with multiple reviews first
WITH popular_products AS (
    SELECT ProductId
    FROM temp.reviews
    GROUP BY ProductId
    HAVING COUNT(*) >= 10
    LIMIT 5
),
product_reviews AS (
    SELECT
        r.ProductId,
        r.Text,
        r.Score,
        LEFT(r.Text, 80) as Review_Sample
    FROM temp.reviews r
    INNER JOIN popular_products p ON r.ProductId = p.ProductId
    LIMIT 10
)
SELECT
    ProductId,
    Score,
    Review_Sample,
    cluster.AI_SENTIMENT(Text) as sentiment,
    cluster.AI_CLASSIFY(Text, '[quality, value, taste, packaging]') as category,
    cluster.AI_SUMMARIZE(Text, 'aifunctions_chat_default', 10) as brief_summary
FROM product_reviews;

Cleanup

SQL

%%sql
DROP TABLE IF EXISTS reviews;
DROP DATABASE IF EXISTS temp;

Usage Recommendations for AI Functions

To optimize performance and control costs when using AI Functions, SingleStore recommends the following:

Use Common Table Expressions (CTEs) to filter rows before making calls to large language models (LLMs). The query engine currently sends data to the LLM before applying LLM or WHERE filters.
LLM calls are expensive. Begin with a small dataset to evaluate response quality and verify the results meet the requirements before scaling up.
Enterprise plans support three model providers; Aura, Amazon Bedrock, and Azure AI Services. Data is processed according to each provider’s policies. If a row violates provider rules, the system fails the batch that includes the row and returns errors for these rows.
Strict usage quotas apply per model and per organization. These quotas are not configurable by end users. For higher usage limits, contact SingleStore Support. Self-service quota configuration will be available in the future.

On this page

Install AI Functions

Edit AI Functions

Uninstall AI Functions

Text Processing Functions

AI_COMPLETE

Syntax

Arguments

Return Type

Usage

AI_SENTIMENT

Syntax

Arguments

Return Type

Usage

AI_TRANSLATE

Syntax

Arguments

Return Type

Usage

AI_SUMMARIZE

Syntax

Arguments

Return Type

Usage

AI_CLASSIFY

Syntax

Arguments

Return Type

Usage

AI_EXTRACT

Syntax

Arguments

Return Type

Usage

Embedding Function

EMBED_TEXT

Syntax

Arguments

Return Type

Usage

Examples

Generate Follow-up Questions for Negative Reviews

Compare Customer Ratings with AI Sentiment

Standardize Reviews in English

Generate Product-Level Summaries

Classify Review Intent

Extract Multiple Insights

Perform Semantic Search

Complete Customer Insights

Example Notebook

Demonstrate some common AI function usecases

Create some simple tables

Install the required packages

Download and Load Dataset

Load Data into SingleStore

Verify Data Load

Sample Data Preview

AI Functions Demonstrations

Cleanup

Usage Recommendations for AI Functions

Was this article helpful?

On this page

Was this article helpful?