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>
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
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>
ParametersDescription
<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.

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.
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>'
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 | CONTINUOUS>',
    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_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.

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