SQL Language Guide > SQL Language Guide > SQL Statements > CREATE MODEL > CREATE GENAI MODEL
Was this helpful?
CREATE GENAI MODEL
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
A Generative AI (GenAI) model prompts a language model (LM) endpoint as part of a SQL query. This eliminates the need to export data into external infrastructure and allows further processing of the LM response. Prompting LM endpoints can be used to generate, classify, summarize, and create Retrieval Augmented Generation (RAG) pipelines.
Note:  Before defining and using GenAI models, run iisuremotefs Command--Set up and Configure Remote File Systems Support.
Note:  GenAI models support language model endpoints that either do not require any authentication or use the standard 'Authorization' HTTP header. Custom header keys for authentication are not supported.
The CREATE MODEL statement is used to create or replace a GenAI model.
This statement has the following format:
CREATE [OR REPLACE] MODEL model_name
(INPUT SET OF
[(col_name col_type)])
[RESULT ROW (col_name col_type)]
AS TYPE model_family,
URL = 'model_url',
INPUT_STRUCTURE = 'input_structure',
CONTENT_TYPE = 'content_type'[,] |
[OUTPUT_STRUCTURE = 'output_structure'[,]] |
[CREDENTIAL = 'credential_object'];
model_name
Specifies the name of the model.
INPUT SET OF ‘col_name col_type’
Specifies the input column name and data type for the model.
RESULT ROW ‘col_name col_type’
Specifies the result column name and data type for the model.
TYPE model_family
Specifies the model family, which must be ‘genai’.
URL = ‘model_url’
Specifies the model API endpoint (in the form of HTTP URL).
INPUT_STRUCTURE = ‘input_structure’
Specifies the payload expected by the API provider. The (mandatory) placeholder '<prompt>' marks the prompt insertion during runtime.
CONTENT_TYPE = 'content_type'
Specifies the media type of the payload, which is ‘application/json’.
OUTPUT_STRUCTURE = 'output_structure'
(Optional) Specifies the name of the JSON key whose value is extracted and returned. The default behavior is to return the full response from the HTTP request.
CREDENTIAL = ‘credential_object”
(Optional) Specifies the credential object of type TOKEN, if required to access the URL. For more information on credentials, see CREATE CREDENTIAL.
Note:  Using named credentials is a more secure and preferred option, as opposed to direct provisioning of credentials, which may be deprecated in the future.
INVOKE GENAI MODEL
The following is the syntax for invoking a GenAI Model:
SELECT [ALL|DISTINCT] target_list
FROM <model_name>(TABLE(<table-reference>))
[WHERE search_cond]
[GROUP BY col(s)]
[HAVING search_cond]
[WINDOW window_defn]
[UNION subselect]
[ORDER BY col(s)]
CREATE GENAI MODEL Example
Example query for creating a GenAI Model:
CREATE MODEL mymodel (input set of(prompt varchar(50)))
RESULT ROW (response varchar(200))
AS TYPE genai
URL = 'https://api.example.com/chat/completions',
INPUT_STRUCTURE = '{"model": "mymodel", "messages": [{"role": "user", "content": "<prompt>"}]}',
CONTENT_TYPE = 'application/json',
CREDENTIAL = 'mytoken';
Example query for model invocation (compatible to ‘mymodel’ created above):
DROP TABLE IF EXISTS tab;
CREATE TABLE tab (roles VARCHAR(50));
INSERT INTO tab VALUES ("lead engineer");
SELECT * FROM mymodel (TABLE(SELECT 'Generate a list of : ' || roles as prompt FROM tab) t);
Last modified date: 09/11/2026