Skip to main content

AISQL cookbook

Classify, summarize, extract, translate, and aggregate six synthetic product reviews with SQL.

The practical problem and model​

A product team wants to turn review text into a small set of actionable issues. The fixture contains praise, a short battery-life complaint, confusing documentation, startup crashes, and a Spanish-language positive review.

AISQL sends text to a language model through SQL functions. Classification chooses from supplied labels; extraction answers a specific question; translation preserves meaning; aggregate functions combine a group of reviews into one response. These functions solve different tasks and should be evaluated with different criteria.

The same input may produce different wording across provider versions or runs. Judge whether each response is supported by its input rather than requiring an exact sentence.

Before you run​

Complete the shared setup. This walkthrough uses synthetic data and recreates its tutorial objects. Use a sandbox tenant or schema, and run the steps in order.

Studio: Tutorial: AISQL Cookbook. Download the complete SQL.

The AI_* calls use Gnok's AI provider under your organization's AI Governance. Before the first run, an organization administrator opens Operations → AI Governance in Studio, saves a monthly token budget as Available, and then enables a rollout policy. The policy must allow provider anthropic, model claude-haiku-4-5-20251001, and the operations used here (ai_classify_text, ai_summarize, ai_extract_answer, ai_translate, ai_complete), with egress and retention approved. Without them each call is refused. See AI Governance. Re-running the text queries uses more of the token budget.

Step 1: Create the review fixture​

The six reviews span two products. Ratings and language tags let later SQL limit the rows sent for processing.

CREATE CATALOG IF NOT EXISTS tutorial;

CREATE SCHEMA IF NOT EXISTS tutorial.aisql;

CREATE OR REPLACE TABLE tutorial.aisql.reviews (
    review_id BIGINT,
    product_id BIGINT,
    body VARCHAR,
    rating INT,
    language VARCHAR,
    created_at TIMESTAMP
);

INSERT INTO tutorial.aisql.reviews VALUES
    (1, 100, 'The build quality is fantastic and shipping was fast.', 5, 'en', TIMESTAMP '2026-04-01 10:00'),
    (2, 100, 'Battery dies after 2 hours. Very disappointed.',          1, 'en', TIMESTAMP '2026-04-02 11:30'),
    (3, 100, 'Excelente calidad y entrega rápida.',                     5, 'es', TIMESTAMP '2026-04-02 14:15'),
    (4, 200, 'The UI is confusing and the support docs are wrong.',     2, 'en', TIMESTAMP '2026-04-03 09:00'),
    (5, 200, 'Buggy. Crashes on startup half the time.',                1, 'en', TIMESTAMP '2026-04-03 16:45'),
    (6, 200, 'Works as advertised. Five stars.',                        5, 'en', TIMESTAMP '2026-04-04 08:20');

Step 2: Classify, summarize, extract, and translate​

Classification covers all six reviews. Summarization and issue extraction cover the three low-rated reviews; translation handles the one Spanish review. Extraction is performed once per selected row in this example.

SELECT
    review_id,
    body,
    AI_CLASSIFY_TEXT(body, ARRAY['positive', 'negative', 'neutral', 'mixed']) AS sentiment
FROM tutorial.aisql.reviews;

SELECT review_id, AI_SUMMARIZE(body) AS summary
FROM tutorial.aisql.reviews
WHERE rating <= 2;

SELECT
    review_id,
    body,
    AI_EXTRACT_ANSWER(body, 'What product issue is the customer describing?') AS issue
FROM tutorial.aisql.reviews
WHERE rating <= 2;

SELECT
    review_id,
    language,
    body,
    AI_TRANSLATE(body, 'English') AS body_en
FROM tutorial.aisql.reviews
WHERE language != 'en';

Step 3: Aggregate product feedback and draft replies​

The two aggregate queries group low-rated feedback by product. A reply is a draft for review; this SQL does not send a message to a customer.

SELECT
    product_id,
    COUNT(*) AS review_count,
    AI_SUMMARIZE_AGG(body) AS summary_of_reviews
FROM tutorial.aisql.reviews
WHERE rating <= 3
GROUP BY product_id;

SELECT
    product_id,
    AI_AGG(
        'Read these customer reviews and produce three concrete product improvements, one per line.',
        body
    ) AS top_three_improvements
FROM tutorial.aisql.reviews
WHERE rating <= 3
GROUP BY product_id;

SELECT
    review_id,
    AI_COMPLETE(
        'In one sentence, suggest a polite reply to this customer review: ' || body
    ) AS suggested_reply
FROM tutorial.aisql.reviews
WHERE rating <= 2;

What to check in the results​

OperationExpected coverageReview criterion
Classification6 rowsPraise is positive; the battery, documentation, and crash complaints are negative.
Summary and extraction3 rows eachRetain the two-hour battery issue, confusing UI/documentation, and startup crashes without adding facts.
Translation1 rowPreserve the praise for quality and fast delivery.
Grouped feedback2 product groupsProduct 100 concerns the battery; product 200 concerns usability, docs, and crashes.
Suggested replies3 rowsAddress the actual complaint without promising an unauthorized refund, fix, or delivery date.

Aggregate semantics​

The aggregate functions combine the selected text for each group before requesting a response. Bound the input size and review the complete response; a returned string alone is not evidence that the task was performed correctly.

Adapt it to real data​

Create a human-reviewed sample with the labels and facts you expect. Track omissions and invented details as well as parse success. Filter and deduplicate appropriate inputs before paying for large runs, and store reviewed outputs when repeated generation adds no value.

Separate model-generated suggestions from actions. A proposed reply or product improvement should not automatically become a customer message or a committed product promise.

Natural-language and AI function reference

All tutorials · Setup and troubleshooting

Last updated on