CREATE TENSORFLOW/ONNX MODEL
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE MODEL statement is used to create or replace a TensorFlow or ONNX machine learning model.
This statement has the following format:
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 = 'credential_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',
AZURE_CLIENT_SECRET = 'secret'[,]];
CREATE OR REPLACE MODEL
Creates or recreates the model if it already exists.
model_name
Defines the name of the model.
INPUT SET OF ‘col_name col_type’
Specifies the input column name and data type of each column for the model. Each column specification must be separated with a comma.
RESULT ROW ‘col_name col_type’
Specifies the result column name and data type of each column for the model. Each column specification must be separated with a comma.
TYPE ‘model_family’
Specifies the model family, which can be ‘TensorFlow’ or ‘ONNX’.
PATH
Specifies the absolute path to the model. It can be local storage, HDFS, or cloud storage (AWS S3, Azure, GCS).
Note: In a client-server connection, the model path must be accessible from the server. Ensure the server has read access to the model location, and any required cache or temporary directories are writable by the server.
MEMORY
Specifies the maximum memory per container. It can be increased for large models in case of out-of-memory errors.
• Default: 512MB
• Minimum 70MB
SIGNATURE
(Valid only for TensorFlow model) Specifies 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
CREDENTIAL = ‘credential_name’
Specifies the name of the credential that is either created by the user or for which the user has VIEW or USAGE privileges. It is a more secure way to access cloud storage and other external services, instead of passing sensitive credential information in the SQL statement. For more information on credentials, see
CREATE CREDENTIAL.
Note: If you use a credential name to access remote objects, then you cannot use other options such as AWS_SECRET_KEY, GCS_PRIVATE_KEY, and similar. This direct mode of passing sensitive credential information in SQL statements may be deprecated in the future.
CREDENTIAL_TYPE
Specifies the type of credential.
For information on configuring TensorFlow or ONNX model, see
Configuring Machine Learning Models (MLM).
INVOKE TENSORFLOW/ONNX MODEL
The following is the syntax for invoking a TensorFlow or ONNX 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 TENSORFLOW/ONNX MODEL Example
Example query for creating a TensorFlow 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'
CREDENTIAL = 'credential1';\p\g
Example query for creating an ONNX Model:
CREATE MODEL mymodel (input set of(a float4, b float4))
RESULT ROW (x float4)
AS TYPE onnx
PATH = '/path/to/model.onnx',
MEMORY = 256000000,
CREDENTIAL = 'credential1';\p\g
Example query for model invocation (compatible to ‘mymodel’ created above):
DROP TABLE IF EXISTS tab;
CREATE TABLE tab (roles VARCHAR);
INSERT INTO tab VALUES ("lead engineer");
SELECT * FROM mymodel (TABLE(SELECT 'Generate a list of : ' || roles FROM tab) t);