Model DDLs

This topic describes the data definition language (DDL) statements for registering, viewing, modifying, and deleting AI models.

Usage Notes

• The system supports various large model services with OpenAI compatible interfaces.

CREATE MODEL

Register a Model

Run a CREATE MODEL command in the SQL editor to register a model.

Syntax

CREATE [TEMPORARY] MODEL [catalog_name.][db_name.]model_name
INPUT ( { <physical_column_definition> [, ...n] )
OUTPUT ( { <physical_column_definition> [, ...n] )
WITH (key1=val1, key2=val2, ...)

<physical_column_definition>:
  column_name column_type [COMMENT column_comment]

Clause	Description	Key parameters	Schema requirements	Example
INPUT	Defines the fields, field types, and field order of input data.	column_name, column_type, COMMENT	Exactly one STRING field is required.	INPUT (input_text STRING COMMENT 'User comment')
OUTPUT	Defines the fields, field types, and field order of output data.	column_name, column_type, COMMENT	Constraints for different task types: - chat/completions: Exactly one STRING field is required. - embeddings: Exactly one ARRAY<FLOAT> field is required.	OUTPUT (sentiment_label STRING COMMENT 'Sentiment label')
WITH	See Parameters.	provider, endpoint, apiKey, model	None.	WITH ('provider'='openai', 'endpoint'='${ENDPOINT}', 'model'='gpt-4.1', 'apiKey'='${KEY}')

Examples

Completion Model

The following example registers a model for sentiment analysis using an OpenAI-compatible completion service. You can use a proxy to access the API if needed.

CREATE MODEL sentiment_analysis_model
INPUT (input STRING)
OUTPUT (output STRING)
WITH (
  'provider' = 'openai',
  'task' = 'completions',
  'endpoint' = 'https://proxy.example.com/v1/chat/completions',
  'apiKey' = '<your-api-key>',
  'system_prompt' = 'Analyze the sentiment of the text and return only POSITIVE, NEGATIVE, or NEUTRAL.',
  'model' = 'gpt-4.1'
);

When you call this model, the following happens:

1. You pass a string of text via the input column.
2. The model sends this to the API with the system prompt.
3. The model analyzes the text and responds with POSITIVE, NEGATIVE, or NEUTRAL.
4. The result is returned in the output field.

Embedding Model

The following example registers an embedding model to convert text into numerical vectors.

CREATE MODEL text_embedding_model
INPUT (input STRING)
OUTPUT (embedding ARRAY<FLOAT>)
WITH (
  'provider' = 'openai',
  'endpoint' = 'https://api.example.com/v1/chat/embeddings',
  'apiKey' = '<your-api-key>',
  'model' = 'text-embedding-v3'
);

These embeddings are useful for:

• Semantic search: Find relevant documents based on meaning rather than keyword matches.
• Duplicate detection: Identify semantically identical entries.
• Text clustering: Automatically group similar documents.
• Recommendation systems: Suggest items based on content similarity.
• Anomaly detection: Find unusual or unexpected text entries.

Parameters

General

Parameter	Description	Data type	Required	Default value	Note
provider	The model service provider.	String	Yes	None	Set to openai for OpenAI-compatible services.
endpoint	The API endpoint for chat, completions, or embeddings.	String	Yes	None	Refer to your service's API documentation for the correct endpoint.
api-key	The API key used to access the model service.	String	Yes	None	In VERA Engine 4.1, use apiKey.
max-context-size	The maximum context length in a single request.	Integer	No	None	Supported in VERA Engine 4.3 and later.
context-overflow-action	Specifies how to handle a context exceeding the limit.	String	No	truncated-tail	Supported in VERA Engine 4.3 and later. See Context Overflow Handling for more information.

chat/completions

Parameter	Description	Data type	Required	Default value
model	The model to call (e.g., gpt-4.1).	String	Yes	None
system-prompt	The system prompt for the request.	String	Yes	"You are a helpful assistant."
temperature	Controls the randomness of the output (0 to 2).	Float	No	None
top-p	Specifies cumulative probability for sampling.	Float	No	None
stop	The stop sequence string.	String	No	None
max-tokens	Maximum length of generated texts.	Integer	No	None

embeddings

Parameter	Description	Data type	Required	Default value	Note
model	The model to call.	String	Yes	None
dimension	Controls the output vector dimension.	Integer	No	1024	Valid values: 1024, 768, 512.

View Models

Use the following commands to view information about registered models.

• Show registered models:

  SHOW MODELS [ ( FROM | IN ) [catalog_name.]database_name ];

• Show model registration statement:

  SHOW CREATE MODEL [catalog_name.][db_name.]model_name;

• Show model schema:

  DESCRIBE MODEL [catalog_name.][db_name.]model_name;

Modify Models

Use the ALTER MODEL command to modify an existing model.

ALTER MODEL [IF EXISTS] [catalog_name.][db_name.]model_name {
  RENAME TO new_model_name
  SET (key1=val1, ...)
  RESET (key1, ...)
}

Examples

• Rename a model:

  ALTER MODEL m RENAME TO m1;

• Modify a parameter:

  ALTER MODEL m SET ('endpoint' = 'https://new-endpoint.example.com');

• Reset a parameter:

  ALTER MODEL m RESET ('endpoint');

Context Overflow Handling

VERA Engine 4.3 introduces configurable strategies to handle inputs that exceed an AI model's maximum context window. You can define these strategies using the max-context-size and context-overflow-action parameters in the WITH clause of your CREATE MODEL statement.

Supported Overflow Actions

The following table describes the supported actions for handling context overflow.

Action	Description
truncated-tail	Default. Truncates the input from the end to fit the max-context-size.
truncated-tail-log	Truncates the input from the end and logs the action.
truncated-head	Truncates the input from the beginning to fit the max-context-size.
truncated-head-log	Truncates the input from the beginning and logs the action.
skipped	Skips the input entirely without processing it.
skipped-log	Skips the input and logs the action.

SQL Examples

The following examples demonstrate how to use context overflow parameters with ML_PREDICT.

Chat and Completions

This example uses a completion model with a small context size and truncated-tail action.

CREATE TEMPORARY MODEL `gpt-model`
INPUT (`input` STRING)
OUTPUT (`output` STRING)
WITH (
    'provider' = 'openai-compat',
    'endpoint' = 'https://api.openai.com/v1/chat/completions',
    'max-context-size' = '4',
    'context-overflow-action' = 'truncated-tail',
    'api-key' = '${secret_values.openai-key}',
    'model' = 'gpt-4o'
);

-- Use ML_PREDICT to call the model
SELECT `value`, `output`
FROM ML_PREDICT(
  INPUT => TABLE `source_table`,
  MODEL => MODEL `gpt-model`,
  ARGS => DESCRIPTOR(`value`)
);

Embeddings

This example uses an embedding model with a max-context-size of 2 and truncated-tail action. If the input contains more than two words, it is truncated before generating the embedding.

CREATE TEMPORARY MODEL `embedding-model`
INPUT (`input` STRING)
OUTPUT (`output` ARRAY<FLOAT>)
WITH (
    'provider' = 'openai-compat',
    'endpoint' = 'https://api.openai.com/v1/embeddings',
    'max-context-size' = '2',
    'context-overflow-action' = 'truncated-tail',
    'api-key' = '${secret_values.openai-key}',
    'model' = 'text-embedding-3-small',
    'dimension' = '1536'
);

-- Use ML_PREDICT to generate embeddings
SELECT `value`, `output`
FROM ML_PREDICT(
  INPUT => TABLE `source_table`,
  MODEL => MODEL `embedding-model`,
  ARGS => DESCRIPTOR(`value`)
);

Delete Models

Use the DROP MODEL command to delete a registered model.

DROP [TEMPORARY] MODEL [IF EXISTS] [catalog_name.][db_name.]model_name

Example

DROP MODEL sentiment_analysis_model;