Docs / reference

Reference

SQL syntax extensions, system variables, and the sys.* routines for vectors, ML, and the agent.

1 · SQL syntax

VECTOR columns and functions

A VECTOR(n) column stores a fixed-width array of n float32 values. Values are written and read as bracketed lists, e.g. [0.1, 0.2, 0.3].

  • TO_VECTOR(str) / STRING_TO_VECTOR(str) — parse a string into a vector
  • FROM_VECTOR(vec) / VECTOR_TO_STRING(vec) — render a vector to a string
  • VECTOR_DIM(vec) — the number of dimensions
  • DISTANCE(v1, v2, 'metric') — similarity metric; COSINE, DOT, or EUCLIDEAN

Secondary engine (Rapid)

Rapid is a secondary engine. A table is mirrored into the in-memory column store with two statements: 

ALTER TABLE lineitem SECONDARY_ENGINE = RAPID;
ALTER TABLE lineitem SECONDARY_LOAD;

From then on the optimizer routes each query by cost. The session variable use_secondary_engine can force or disable the secondary engine for the connection.

Native ML functions

The sys.* routines below are thin wrappers over native functions. You can call the native functions directly:

  • ML_MODEL_TRAIN, ML_MODEL_PREDICT_ROW, ML_MODEL_PREDICT_TABLE
  • ML_MODEL_SCORE, ML_MODEL_EXPLAIN, ML_MODEL_EXPLAIN_ROW, ML_MODEL_EXPLAIN_TABLE
  • ML_MODEL_EMBED_ROW, ML_MODEL_EMBED_TABLE
  • ML_MODEL_GENERATE, ML_MODEL_GENERATE_TABLE
  • ML_MODEL_LOAD, ML_MODEL_UNLOAD, ML_MODEL_IMPORT, ML_MODEL_LIST, ML_MODEL_ACTIVE

2 · System variables

Secondary engine

  • use_secondary_engine — OFF | ON | FORCED (default ON)
  • secondary_engine_cost_threshold — cost ratio below which queries stay on the row store
  • show_create_table_skip_secondary_engine — omit secondary-engine clauses from SHOW CREATE TABLE

Rapid engine

  • rapid_memory_size_max — memory budget for the column store
  • rapid_pop_buffer_size_max — per-thread populate buffer cap
  • rapid_parallel_load_max — parallel load threads
  • rapid_parallel_part_load_threshold — partition count before parallel load
  • rapid_propagation_mode — how committed changes reach the column store
  • rapid_async_column_threshold — column size that triggers async load
  • rapid_use_dynamic_offload — enable dynamic query offloading
  • rapid_self_load_enabled — automatic load of candidate tables
  • rapid_self_load_interval_seconds — self-load scan interval
  • rapid_self_load_skip_quiet_check — bypass the quiet-period check
  • rapid_self_load_base_relation_fill_percentage — fill threshold for base relations
  • rapid_max_purger_timeout — max purge timeout
  • rapid_purge_batch_size — rows purged per batch
  • rapid_min_versions_for_purge — minimum versions before purge
  • rapid_purge_efficiency_threshold — efficiency target for purging
  • rapid_gc_interval_scn — garbage-collection interval
  • rapid_reload_on_restart — reload the column store on restart
  • rapid_schema_embedding — use schema embeddings in the optimizer

Session variables

  • @chat_options — JSON configuration for sys.shannon_chat
  • @shannon_agent_plugin — dispatcher level-1 plugin override (format schema.func)
  • @_shannon_ml_handle — last ML model handle used by a procedure
  • @_shannon_last_conv_id — last agent conversation id (auto-continue)

3 · ML lifecycle

Train, predict, score, and explain a model. All models live in your private catalog ML_SCHEMA_<user>.MODEL_CATALOG.

  • sys.ML_TRAIN('schema.table', 'target', options, @handle) — fit a model; task selects classification, regression, forecasting, anomaly_detection, log_anomaly_detection, recommendation, or topic_modeling
  • sys.ML_PREDICT_ROW(JSON_OBJECT(...), @handle, options) — score one row, returns JSON
  • sys.ML_PREDICT_TABLE('in.table', @handle, 'out.table', options) — score a whole table into a new table
  • sys.ML_SCORE('schema.table', 'target', @handle, 'metric', @score, options) — evaluate against ground truth
  • sys.ML_EXPLAIN('schema.table', 'target', @handle, options) — feature importance for the model
  • sys.ML_EXPLAIN_ROW(input_data, @handle, options) — per-row explanation (function)
  • sys.ML_EXPLAIN_TABLE('in.table', @handle, 'out.table', options) — per-row explanations for a whole table

4 · Embeddings, generation & RAG

  • sys.ML_EMBED_ROW(text, options) — encode text into a vector embedding (function)
  • sys.ML_EMBED_TABLE('db.table.column', 'db.table.column', options) — batch-embed a column
  • sys.ML_GENERATE(text, options) — LLM text generation or summarization (function)
  • sys.ML_GENERATE_TABLE('db.table.column', 'db.table.column', options) — batch generation in parallel
  • sys.ML_RAG(query, @out, options) — retrieve from vector stores and generate a grounded answer
  • sys.ML_RAG_TABLE('db.table.column', 'db.table.column', options) — batch RAG over a column
  • sys.ML_RETRIEVE_SCHEMA_METADATA(query, @out, options) — rank the tables most relevant to a natural-language query
  • sys.NL_SQL(query, @out, options) — convert a question into a validated SELECT, optionally execute it

5 · Model management

  • sys.ML_MODEL_LOAD(@handle, 'user') — load a trained model into memory
  • sys.ML_MODEL_UNLOAD(@handle) — unload a model from memory
  • sys.ML_MODEL_IMPORT(content, metadata, @handle) — import a model (e.g. ONNX)
  • sys.ML_MODEL_EXPORT(@handle, 'schema.table') — export a model to a table for backup or sharing
  • sys.ML_MODEL_ACTIVE('user', @out) — report which models are loaded in memory

6 · Agent

  • sys.shannon_chat(message) — the agent entry point (procedure); returns a response result set
  • sys.shannon_agent_default(message, conversation_id) — the built-in dispatcher-level-4 agent (function)
  • sys.shannon_agent_register_plugin(name, schema, func, priority, desc, @result) — register a plugin agent
  • sys.shannon_agent_unregister_plugin(name, @result) — unregister a plugin agent
  • sys.shannon_agent_toggle_plugin(name, enabled, @result) — enable or disable a plugin agent
  • sys.shannon_agent_list_plugins() — list registered plugins with status

7 · System tables

  • ML_SCHEMA_<user>.MODEL_CATALOG — model metadata (owner, task, target, timestamps)
  • ML_SCHEMA_<user>.MODEL_OBJECT_CATALOG — large model binaries, stored in 16 MB chunks
  • mysql.shannon_api_configs — pre-configured LLM provider credentials (API keys stored encrypted), referenced by api_config
  • mysql.agent_sql_trace — per-turn SQL execution audit log
  • mysql.agent_review_plan / mysql.agent_review_history — approval plans and decisions
  • mysql.agent_memory — conversation memory with vector embeddings
  • mysql.agent_tx_lease — agent transaction leases
  • mysql.shannon_agent_plugins — the agent plugin registry
Back to Agent Back to docs