Docs / vector & ml

Vector & ML

A native VECTOR type, embeddings, and a full train → predict → score → explain model lifecycle. No data movement, all in SQL.

ShannonBase ships an ML runtime inside the server, next to the MySQL kernel and the Rapid column store. Embeddings and models run in-process on ONNX Runtime and LightGBM, so the data never leaves the database. What you get is a set of sys.* routines that look like ordinary SQL.

1 · A native VECTOR type

A VECTOR(n) column is a fixed-width array of n float32 values, written and read as a bracketed list. Vector columns live in ordinary tables and can be loaded into Rapid like any other column. 

CREATE TABLE items (
  id      INT PRIMARY KEY,
  title   VARCHAR(255),
  title_vec VECTOR(384)
);

Vector helper functions, all usable in SQL:

  • TO_VECTOR('[0.1, 0.2, ...]') — parse a string into a vector
  • FROM_VECTOR(vec) — render a vector back to its string form
  • VECTOR_DIM(vec) — number of dimensions
  • DISTANCE(v1, v2, 'metric') — similarity, with COSINE, DOT, or EUCLIDEAN
mysql> SELECT id, title
       FROM items
       ORDER BY DISTANCE(title_vec, @query_vec, 'COSINE')
       LIMIT 5;

2 · Embeddings

sys.ML_EMBED_ROW encodes one text value into a vector using an in-process sentence-transformers model (ONNX Runtime). The result is a BLOB you can store in a VECTOR column. 

mysql> SELECT sys.ML_EMBED_ROW(
         'What is artificial intelligence?',
         JSON_OBJECT('model_id', 'multilingual-e5-small'))
       INTO @query_vec;

sys.ML_EMBED_TABLE runs the same model over an entire column in batches. Input and output are given as database.table.column triples. 

mysql> CALL sys.ML_EMBED_TABLE(
         'demo_db.input_table.Input',
         'demo_db.output_table.Output',
         JSON_OBJECT('model_id', 'multilingual-e5-small',
                     'batch_size', 500,
                     'truncate',    true));
  • model_id — embedding model to load
  • batch_size — rows per batch (1–1000, default 1000)
  • truncate — truncate inputs longer than the model window (default true)
  • details_column — column that receives per-row errors

3 · Training

sys.ML_TRAIN fits a model on a labeled table. The model is stored in your private catalog (ML_SCHEMA_<user>.MODEL_CATALOG), and the handle is returned through the session variable you pass in. 

mysql> SET @iris_model = 'iris_manual';
mysql> CALL sys.ML_TRAIN('ml_data.iris_train', 'class',
         JSON_OBJECT('task', 'classification'),
         @iris_model);

Supported task values:

  • classification — binary and multi-class (default)
  • regression
  • forecasting
  • anomaly_detection and log_anomaly_detection
  • recommendation
  • topic_modeling

The learner is LightGBM, compiled in. Training tables are capped at 10 GB, 100 million rows, and 1017 columns.

4 · Prediction

sys.ML_PREDICT_ROW scores one row and returns a JSON object. For classification the output includes the predicted class and the per-class probabilities. 

mysql> SELECT sys.ML_PREDICT_ROW(
         JSON_OBJECT('sepal_length', 5.1, 'sepal_width', 3.5,
                     'petal_length', 1.4, 'petal_width',  0.2),
         @iris_model, NULL);

sys.ML_PREDICT_TABLE runs the model over an entire table and writes the predictions to a new output table. 

mysql> CALL sys.ML_PREDICT_TABLE(
         'census_data.census_predictions_in',
         @census_model,
         'census_data.census_predictions_out',
         NULL);

5 · Scoring

sys.ML_SCORE evaluates a trained model against ground truth and writes the metric into an output session variable. 

mysql> CALL sys.ML_SCORE('ml_data.iris_validate', 'class',
         @iris_model, 'balanced_accuracy', @score, NULL);
mysql> SELECT @score;

Metrics are task-specific. Classification supports accuracy, balanced_accuracy, f1 (and f1_macro, f1_micro, f1_weighted), precision, recall, neg_log_loss, and roc_auc. Recommendation supports hit_ratio_at_k, ndcg_at_k, precision_at_k, and recall_at_k.

6 · Explaining

sys.ML_EXPLAIN computes feature importance for the model and per-row explanations for predictions, and stores the explanation in the model catalog. 

mysql> CALL sys.ML_EXPLAIN('ml_data.iris_train', 'class',
         @iris_model,
         JSON_OBJECT('model_explainer', 'fast_shap',
                     'prediction_explainer', 'shap'));
  • model_explainer — permutation_importance (default), shap, fast_shap, partial_dependence
  • prediction_explainer — per-row explainer for sys.ML_EXPLAIN_ROW

7 · Model management

Models are persisted in your private catalog. A set of routines manages them over their lifetime:

  • sys.ML_MODEL_LOAD / sys.ML_MODEL_UNLOAD — move a model in and out of memory
  • sys.ML_MODEL_IMPORT / sys.ML_MODEL_EXPORT — bring a model in from (or out to) ONNX
  • sys.ML_MODEL_ACTIVE — list models currently loaded in memory

8 · LLM generation

sys.ML_GENERATE runs a prompt through a large language model and returns the generated text. Generation tasks are generation and summarization. 

mysql> SELECT sys.ML_GENERATE(
         'Explain what a vector database is, in one paragraph.',
         JSON_OBJECT('task', 'generation',
                     'model_id', 'Qwen2.5-0.5B-Instruct',
                     'language', 'en',
                     'max_tokens', 256));

Backends, selected with provider:

  • onnx — local ONNX Runtime inference (default), models under llm-models/
  • ollama — a local Ollama service (default http://localhost:11434)
  • openai — OpenAI or any OpenAI-compatible endpoint
  • dashscope — Alibaba Cloud Tongyi Qianwen
  • qianfan — Baidu Wenxin
  • deepseek — DeepSeek Cloud API

Generation options include temperature, max_tokens, top_k, top_p, repeat_penalty, frequency_penalty, presence_penalty, stop_sequences, speculative_decoding, and context. Cloud credentials can be referenced by name with api_config (resolved from mysql.shannon_api_configs, keys stored encrypted) instead of passing an api_key inline.

sys.ML_GENERATE_TABLE runs the same generation in parallel over a column, writing results to an output column.

9 · Retrieval-augmented generation

sys.ML_RAG combines semantic search over stored vectors with LLM generation: it retrieves the most relevant segments from your vector store tables, then generates an answer grounded in them. The output JSON contains the generated text plus citations. 

mysql> CALL sys.ML_RAG('What does the EULA say about data retention?',
         @answer,
         JSON_OBJECT('vector_store',   JSON_ARRAY('kb.documents'),
                     'n_citations',    3,
                     'distance_metric','COSINE'));
  • vector_store / exclude_vector_store — which vector tables to search
  • n_citations — segments to retrieve (default 3, 0–100)
  • distance_metric — COSINE | DOT | EUCLIDEAN
  • embed_model_id — embedding model for the query (default multilingual-e5-small)
  • skip_generate — retrieve only, no generation
  • model_options — forwarded to ML_GENERATE

10 · Natural language to SQL

sys.NL_SQL turns a natural-language question into a SELECT statement, validates it with PREPARE, retries up to three times on syntax errors, and can execute it for you. 

mysql> CALL sys.NL_SQL(
         'Which customer spent the most last quarter?',
         @result,
         JSON_OBJECT('execute', true,
                     'schemas', JSON_ARRAY('sales'),
                     'verbose', 1));
Back to HTAP & Rapid Back to docs