SQL Language Guide > SQL Language Guide > Elements of SQL Statements > Machine Learning Model (MLM) SQL Syntax
Was this helpful?
Machine Learning Model (MLM) SQL Syntax
Note:  This MLM SQL syntax is only valid on Unix installations.
The syntax of a CREATE MODEL statement is:
CREATE [OR REPLACE] MODEL model_name
(INPUT SET OF
[(col_name [=] col_type {,col_name[=] col_type})])
[RESULT ROW(col_name col_type {,col_name col_type})]
AS|=
TYPE model_family,
PATH = '/path/to/model/filename'[,] |
[MEMORY = integer[,]] |
[SIGNATURE = 'signature_name'[,]] |
[CREDENTIAL_TYPE =
GCS_SERVICE_1 |
AWS_SIMPLE |
AWS_SESSION |
AZURE_OAUTH2_CLIENT |
AZURE_SHARED_KEY[,]] |
[GCS_EMAIL =
'email',
GCS_PRIVATE_KEY_ID = 'private key',
GCS_PRIVATE_KEY = 'secret key'[,]] |
[AWS_ACCESS_KEY =
'access key',
AWS_SECRET_KEY = 'secret key',
AWS_ENDPOINT = 'endpoint',
AWS_REGION = 'region'[,]
[AWS_SESSION_TOKEN = 'session id'[,]] ] |
[AZURE_CLIENT_ENDPOINT =
'endpoint',
AZURE_CLIENT_ID = 'id',
 
SQL Syntax Parameter
Description
Path
Path to the model. Can be local storage, HDFS, cloud storage (AWS S3, Azure, GCS).
Memory
Maximum memory per container. Can be increased for large models if there are out-of-memory errors.
• Default: 512MB
• Minimum 70MB
Signature
The Tensorflow signature definition, the default is “serving_default”.
This is similar to running from the command line:
saved_model_cli show --dir /path/to/model/ --tag_set serve --signature_def serving_default
Credentials
If necessary, can be specified to access the model storage.
Example query for CREATE MODEL:
CREATE MODEL mymodel(input set of(a float4, b float4))
RESULT ROW (x float4)
AS TYPE tensorflow
PATH = '/path/to/model',
MEMORY = 256000000,
SIGNATURE = 'signature1';\p\g
MLM invocation syntax:
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)]
Example queries for model invocation (compatible to created mymodel above):
Drop table if exists tab; \p\g
Create Table tab (a float4, b float4); \p\g
Insert into tab values(1.0, 2.0); \p\g
Select * from mymodel(Table tab);\p\g
Select * from mymodel(Table(Select * from tab) t);\p\g
Select * from mymodel(Table(Select 1.0 as a, 2.0 as b) t); \p\g
Model deletion syntax:
DROP MODEL [IF EXISTS] model_name
Last modified date: 04/15/2025