Machine Learning (ML)

Kinetica provides support for AAW container registry & ML model creation and management in SQL. A Docker registry serves as a container for ML models, while an ML model represents a function from which an inference can be computed.

Container registry features accessible via SQL include:

ML model features accessible via SQL include:


CREATE CONTAINER REGISTRY

Creates a new reference to a Docker container registry for accessing ML models.

CREATE CONTAINER REGISTRY Syntax
1
2
3
CREATE [OR REPLACE] CONTAINER REGISTRY <registry name>
URI = '<registry URI>'
CREDENTIAL = <credential name>
ParametersDescription
OR REPLACEAny existing container registry reference with the same name will be dropped before creating this one
<registry name>Name of the reference to the container registry, which can be referenced in subsequent commands
URIThe URI to the container registry itself
CREDENTIALName of the credential to use to authenticate to the container registry

DROP CONTAINER REGISTRY

Removes an existing Docker container registry reference.

DROP CONTAINER REGISTRY Syntax
1
DROP CONTAINER REGISTRY <registry name>
ParametersDescription
<registry name>Name of the reference to the container registry to remove; has no effect on the container registry itself

SHOW CONTAINER REGISTRY

Outputs the DDL statement required to reconstruct the given Docker container registry reference.

SHOW CONTAINER REGISTRY Syntax
1
SHOW CONTAINER REGISTRY < <registry name> | * >
ParametersDescription
<registry name>Name of the existing reference to the container registry for which the DDL will be output. Use * instead to output the DDL of all references to container registries.

Note

The response to SHOW CONTAINER REGISTRY is a single-column result set with the DDL statement as the value in the DDL column.

IMPORT MODEL

Imports an ML model from a Docker container registry into the database.

IMPORT MODEL Syntax
1
2
3
4
IMPORT MODEL <model name>
REGISTRY = <registry name>
CONTAINER = '<path to container>'
RUN_FUNCTION = '<model run function>'
ParametersDescription
<model name>Name of the ML model to import via the given container registry reference
REGISTRYName of the reference to the container registry from which to import the ML model
CONTAINERThe full path to the ML model container within the container registry
RUN_FUNCTIONName of the function within the ML model to execute when running inferences against it
IMPORT MODEL Example
1
2
3
4
IMPORT MODEL temp_2c
REGISTRY = model_registry_docker
CONTAINER = 'kinetica/kinetica-blackbox-sdk:r7.1.0'
RUN_FUNCTION = 'module_temperature.convert_to_celsius'

EVALUATE_MODEL

Runs an inference against an ML model. There are two methods to evaluate a model:

  • By Query - An ML model can be queried, where the inference is returned as the result set.
  • By Execution - An ML model can be run with the EXECUTE FUNCTION command, where the inference is persisted to a specified table.
EVALUATE_MODEL Table Function Syntax
1
2
3
4
5
6
7
8
9
SELECT * FROM TABLE(
    EVALUATE_MODEL
    (
        MODEL => '<model name>',
        DEPLOYMENT_MODE => 'batch',
        REPLICATIONS => <number of replications>,
        SOURCE_TABLE => INPUT_TABLE(<source data set>)
    )
)
EVALUATE_MODEL EXECUTE FUNCTION Syntax
1
2
3
4
5
6
7
EXECUTE FUNCTION EVALUATE_MODEL(
    MODEL => '<model name>',
    DEPLOYMENT_MODE => 'batch',
    REPLICATIONS => <number of replications>,
    SOURCE_TABLE => INPUT_TABLE(<source data set>),
    DESTINATION_TABLE => '<destination table name>'
)
ParametersDescription
MODELName of the ML model on which to run an inference
DEPLOYMENT_MODEScheme used in making inferences on the ML model; batch mode (batch) is the only mode supported at this time
REPLICATIONSThe number of replicas to launch on Kubernetes
SOURCE_TABLE

Table or query to use as input to the ML model, specified with either of these two forms:

  • INPUT_TABLE(<table name>) - Use the given table or view as input; e.g.:

    INPUT_TABLE(price_history)
    
  • INPUT_TABLE(<query>) - Use the given query as input; e.g.:

    INPUT_TABLE(SELECT ts, item, price FROM price_history)
    
DESTINATION_TABLEOnly applicable when using EXECUTE FUNCTION syntax. Name of the table in which to store the results of the inference, in [schema_name.]table_name format, using standard name resolution rules and meeting table naming criteria

ALTER MODEL

Modifies the source of an ML model and re-imports it from that new location. Either the container registry or the container path can be modified.

CONTAINER REGISTRY
1
2
ALTER MODEL <model name>
SET REGISTRY = <registry name>
CONTAINER PATH
1
2
ALTER MODEL <model name>
SET CONTAINER = '<path to container>'

ParametersDescription
<model name>Name of the ML model to modify
REGISTRYName of the reference to the container registry from which to import the ML model
CONTAINERThe full path to the ML model container within the container registry

REFRESH MODEL

Refreshes an ML model from its source.

REFRESH MODEL Syntax
1
REFRESH MODEL <model name>

DROP MODEL

Removes an ML model.

DROP MODEL Syntax
1
DROP MODEL <model name>

SHOW MODEL

Displays the statement used to create an ML model.

SHOW MODEL Syntax
1
SHOW MODEL < <model name> | * >

DESCRIBE MODEL

Displays the configuration of an ML model.

DESCRIBE MODEL Syntax
1
DESCRIBE MODEL < <model name> | * >
ParametersDescription
<model name>Name of the existing model for which the configuration will be output. Use * instead to output the configuration of all models.

Note

The response to DESCRIBE MODEL is a four-column result set:

  • MODEL - name of the ML model
  • ENTITY_ID - internal unique ID for the ML model
  • INPUT_SCHEMA - list of columns types that valid input data is expected to match
  • OUTPUT_SCHEMA - list of column types that will either be returned by the model as a result set or used in creating a result table, depending on how the model's evaluation is invoked