SQL‑Driven Text Classification with Hologres AI Function: Prompt Design to KV‑Cache Tuning

Traditional machine‑learning pipelines for text classification involve data cleaning, tokenization, feature engineering, model selection, training, deployment, and inference service maintenance, often taking days or weeks. Hologres AI Function embeds large‑model inference into the database engine, allowing users to perform classification with a single SQL function ( ai_gen_structured) without Python scripts, external services, or data export.

Step 1 – Data preparation : A table movie_reviews_200 is created in Hologres and populated with 200 Douban movie reviews (including a manually labeled sentiment_label) via COPY from a CSV file.

CREATE TABLE movie_reviews_200(
    cn_name text,
    review text,
    stars bigint,
    sentiment_label text
);

Step 2 – Prompt table : Prompt templates are stored in a table ai_prompts. The prompt() function assembles variables, enabling versioned prompt management without code changes.

CREATE TABLE ai_prompts(
    name text PRIMARY KEY,
    prompt_text text
);
INSERT INTO ai_prompts VALUES(
    'classify_prompt1',
    $$
    你是一名专业的电影评论情感分析专家。你的任务是根据输入的**电影名称、用户影评和 stars 评分**，判断该影评对电影整体的情感倾向，并输出唯一结果。
    ## 输出要求
    - 正向
    - 负向
    - 中性
    $$
);

Step 3 – Batch classification : Using a CTE, each review is paired with the prompt and sent to the model qwen37-plus via ai_gen_structured. The JSON‑schema response is parsed to extract the predicted label, which is inserted into movie_reviews_predict.

WITH prompts AS (
    SELECT cn_name, review, stars,
           prompt(prompt_text, cn_name, review, stars)::text AS prompt,
           sentiment_label
    FROM movie_reviews_200
    LEFT JOIN ai_prompts ON ai_prompts.name = 'classify_prompt1'
),
gen_data AS (
    SELECT cn_name, review, stars, sentiment_label,
           ai_gen_structured(
               model_name => 'qwen37-plus',
               message => prompt,
               response_format => $$ {"type":"json_schema","schema":{"type":"object","properties":{"label":{"type":"string"}}}} $$,
               params => '{"show_details": true}'
           ) AS result
    FROM prompts
),
per_row AS (
    SELECT cn_name, review, stars, sentiment_label,
           result->'output'->>'label' AS predict_label,
           result->'usage' AS usage
    FROM gen_data
)
INSERT INTO movie_reviews_predict SELECT * FROM per_row;

The initial run achieved an accuracy of 83.50 % . No model retraining was required; only prompt refinement was needed.

Step 4 – Prompt tuning : A more detailed prompt ( classify_prompt_opt) adds explicit rules for handling sarcasm, contrast, and ambiguous cases, and prioritises the review text over the stars rating. After replacing the prompt name and re‑running the batch, accuracy rose to 95.00 % , an improvement of 11.5 percentage points.

Step 5 – Cost analysis : Token usage is queried from the usage JSON column. For 200 reviews, total input tokens were ~258 k, with ~234 k cached tokens, yielding a cache‑hit rate of 90 %. Hologres applies a 1–2× discount on cached tokens, resulting in a total inference cost of only 0.11 CNY (≈0.055 CNY for cached tokens and 0.057 CNY for uncached tokens).

Key advantages :

SQL‑centric AI: data analysts can run large‑model inference without leaving the database.

Prompt‑as‑data: version control and A/B testing are as simple as updating a table row.

Intelligent KV‑Cache: high cache‑hit rates dramatically lower token‑based pricing.

Typical use cases include sentiment monitoring, content moderation, customer‑service quality inspection, large‑scale data labeling, and industry‑specific document classification.