Machine Learning (ML)

Kinetica provides support for Machine Learning (ML) container registry & 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.

Note

For statistical analysis functions that don't require a model, see ML Functions.

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>

Parameters	Description
`OR REPLACE`	Any 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
`URI`	The URI to the container registry itself
`CREDENTIAL`	Name of the credential to use to authenticate to the container registry

CREATE CONTAINER REGISTRY Example
1
2
3
CREATE CONTAINER REGISTRY model_registry_docker
URI = 'https://index.docker.io'
CREDENTIAL = reg_cred; 

DROP CONTAINER REGISTRY

Removes an existing Docker container registry reference.

DROP CONTAINER REGISTRY Syntax
1
DROP CONTAINER REGISTRY <registry name>

Parameters	Description
`<registry name>`	Name of the reference to the container registry to remove; has no effect on the container registry itself

DROP CONTAINER REGISTRY Example
1
DROP CONTAINER REGISTRY model_registry_docker

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> | * >

Note

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

Parameters	Description
`<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*.

SHOW CONTAINER REGISTRY Example
1
SHOW CONTAINER REGISTRY model_registry_docker

SHOW CONTAINER REGISTRY Example (All Registries)
1
SHOW CONTAINER REGISTRY *

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>'

Parameters	Description
`<model name>`	Name of the ML model to import via the given container registry reference
`REGISTRY`	Name of the reference to the container registry from which to import the ML model
`CONTAINER`	The full path to the ML model container within the container registry
`RUN_FUNCTION`	Name 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.2.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 | CONTINUOUS>',
    REPLICATIONS => <number of replications>,
    SOURCE_TABLE => INPUT_TABLE(<source data set>),
    DESTINATION_TABLE => '<destination table name>'
)

Parameters	Description
`MODEL`	Name of the ML model on which to run an inference
`DEPLOYMENT_MODE`	Scheme used in making inferences on the ML model: `BATCH` - inference tests are run against a batch of data in an existing table all at once `CONTINUOUS` - inference tests are run automatically against records being streamed into an input table; inference results are inserted into an output table, which will be updated upon each subsequent inference Note Continuous deployment mode is only available when using the `EXECUTE FUNCTION` syntax, as it creates a destination table with the continuously updated results.
`REPLICATIONS`	The 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_TABLE`	Only 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

EVALUATE_MODEL Table Function Example
1
2
3
4
5
6
7
8
9
SELECT * FROM TABLE(
    EVALUATE_MODEL
    (
        MODEL => 'raster_model',
        DEPLOYMENT_MODE => 'batch',
        REPLICATIONS =>1,
        SOURCE_TABLE => INPUT_TABLE(select raster_uri from ki_home.raster_input)
    )
)

EVALUATE_MODEL EXECUTE FUNCTION Example
1
2
3
4
5
6
7
EXECUTE FUNCTION EVALUATE_MODEL(
    MODEL => 'raster_model',
    DEPLOYMENT_MODE => 'batch',
    REPLICATIONS =>1,
    SOURCE_TABLE => INPUT_TABLE(select raster_uri from ki_home.raster_input),
    DESTINATION_TABLE => 'raster_output'
)

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
CONTAINER PATH

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>'

Parameters	Description
`<model name>`	Name of the ML model to modify
`REGISTRY`	Name of the reference to the container registry from which to import the ML model
`CONTAINER`	The full path to the ML model container within the container registry

ALTER MODEL Registry Example
1
2
ALTER MODEL temp_2c
SET REGISTRY = my_registry

ALTER MODEL Container Path Example
1
2
ALTER MODEL temp_2c
SET CONTAINER = 'kinetica/kinetica-blackbox-sdk:r7.2.0'

REFRESH MODEL

Refreshes an ML model from its source.

REFRESH MODEL Syntax
1
REFRESH MODEL <model name>

REFRESH MODEL Example
1
REFRESH MODEL temp_2c

DROP MODEL

Removes an ML model.

DROP MODEL Syntax
1
DROP MODEL <model name>

DROP MODEL Example
1
DROP MODEL temp_2c

SHOW MODEL

Displays the statement used to create an ML model.

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

Parameters	Description
`<model name>`	Name of the existing reference to the model for which the DDL will be output. Use `` instead to output the DDL of all references to models*.

SHOW MODEL Example
1
SHOW MODEL temp_2c

SHOW MODEL Example (All Models)
1
SHOW MODEL *

DESCRIBE MODEL

Displays the configuration of an ML model.

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

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

Parameters	Description
`<model name>`	Name of the existing model for which the configuration will be output. Use `` instead to output the configuration of all models*.

DESCRIBE MODEL Example
1
DESCRIBE MODEL temp_2c

DESCRIBE MODEL Example (All Models)
1
DESCRIBE MODEL *