Vertica provides a number of tools to manage existing models. You can view model summaries and attributes, alter model characteristics like name and privileges, drop models, and version models.
This is the multi-page printable view of this section. Click here to print.
Model management
Vertica provides a number of tools to manage existing models.
- 1: Model versioning
- 2: Altering models
- 3: Dropping models
- 4: Managing model security
- 5: Viewing model attributes
- 6: Summarizing models
- 7: Viewing models
1 - Model versioning
Model versioning provides an infrastructure to track and manage the status of registered models in a database.
Model versioning provides an infrastructure to track and manage the status of registered models in a database. The versioning infrastructure supports a collaborative environment where multiple users can submit candidate models for individual applications, which are identified by their registered_name. Models in Vertica are by default unregistered, but any user with sufficient privileges can register a model to an application and add it to the versioning environment.
Register models
When a candidate model is ready to be submitted to an application, the model owner or any user with sufficient privileges, including any user with the DBADMIN or MLSUPERVISOR role, can register the model using the REGISTER_MODEL function. The registered model is assigned an initial status of 'under_review' and is visible in the REGISTERED_MODELS system table to users with USAGE privileges.
All registered models under a given registered_name are considered different versions of the same model, regardless of trainer, algorithm type, or production status. If a model is the first to be registered to a given registered_name, the model is assigned a registered_version of one. Otherwise, newly registered models are assigned an incremented registered_version of n + 1, where n is the number of models already registered to the given registered_name. Each registered model can be uniquely identified by the combination of registered_name and registered_version.
Change model status
After a model is registered, the model owner is automatically changed to superuser and the previous owner is given USAGE privileges. The MLSUPERVISOR role has full privileges over all registered models, as does dbadmin. Only users with these two roles can call the CHANGE_MODEL_STATUS function to change the status of registered models. There are six possible statuses for registered models:
-
under_review: Status assigned to newly registered models. -
staging: Model is targeted for A/B testing against the model currently in production. -
production: Model is in production for its specified application. Only one model can be in production for a givenregistered_nameat one time. -
archived: Status of models that were previously in production. Archived models can be returned to production at any time. -
declined: Model is no longer in consideration for production. -
unregistered: Model is removed from the versioning environment. The model does not appear in the REGISTERED_MODELS system table.
The following diagram depicts the valid status transitions:
Note
If you change the status of a model to 'production' and there is already a model in production under the givenregistered_name, the status of the new model is set to 'production' and that of the old model to 'archived'.
You can view the status history of registered models with the MODEL_STATUS_HISTORY system table, which includes models that have been unregistered or dropped. Only superusers or users to whom they have granted sufficient privileges can query the table. Vertica recommends granting access on the table to the MLSUPERVISOR role.
Managing registered models
Only users with the MLSUPERVISOR role, or those granted equivalent privileges, can drop or alter registered models. As with unregistered models, you can drop and alter registered models with the DROP MODEL and ALTER MODEL commands. Dropped models no longer appear in the REGISTERED_MODELS system table.
To make predictions with a registered model, you must use the [schema_name.]model_name and predict function for the appropriate model type. You can find all necessary information in the REGISTERED_MODELS system table.
Model registration does not effect the process of exporting models. However, model registration information, such as registered_version, is not captured in exported models. All imported models are initially unregistered, but each category of imported model—native Vertica, PMML, and TensorFlow—is compatible with model versioning.
Examples
The following example demonstrates a possible model versioning workflow. This workflow begins with registering multiple models to an application, continues with an MLSUPERVISOR managing and changing the status of those models, and concludes with one of the models in production.
First, the models must be registered to an application. In this case, the models, native_linear_reg and linear_reg_spark1, are registered as the linear_reg_app application:
=> SELECT REGISTER_MODEL('native_linear_reg', 'linear_reg_app');
REGISTER_MODEL
-------------------------------------------------------------------------
Model [native_linear_reg] is registered as [linear_reg_app], version [2]
(1 row)
=> SELECT REGISTER_MODEL('linear_reg_spark1', 'linear_reg_app');
REGISTER_MODEL
-------------------------------------------------------------------------
Model [linear_reg_spark1] is registered as [linear_reg_app], version [3]
(1 row)
You can query the REGISTERED_MODELS system table to view details about the newly registered models, such as the version number and model status:
=> SELECT * FROM REGISTERED_MODELS;
registered_name | registered_version | status | registered_time | model_id | schema_name | model_name | model_type | category
------------------+--------------------+--------------+-------------------------------+-------------------+-------------+-------------------+-----------------------+----------------
linear_reg_app | 3 | UNDER_REVIEW | 2023-01-26 09:52:00.082166-04 | 45035996273714020 | public | linear_reg_spark1 | PMML_REGRESSION_MODEL | PMML
linear_reg_app | 2 | UNDER_REVIEW | 2023-01-26 09:51:04.553102-05 | 45035996273850350 | public | native_linear_reg | LINEAR_REGRESSION | VERTICA_MODELS
linear_reg_app | 1 | PRODUCTION | 2023-01-24 05:29:25.990626-02 | 45035996273853740 | public | linear_reg_newton | LINEAR_REGRESSION | VERTICA_MODELS
logistic_reg_app | 1 | PRODUCTION | 2023-01-23 08:49:25.990626-02 | 45035996273853740 | public | log_reg_cgd | LOGISTIC_REGRESSION | VERTICA_MODELS
(4 rows)
If you query the MODELS system table, you can see that the model owner has automatically been changed to superuser, dbadmin in this case:
=> SELECT model_name, owner_name FROM MODELS WHERE model_name IN ('native_linear_reg', 'linear_reg_spark1');
model_name | owner_name
-------------------+------------
native_linear_reg | dbadmin
linear_reg_spark1 | dbadmin
(2 rows)
As a user with the MLSUPERVISOR role enabled, you can then change the status of both models to 'staging' with the CHANGE_MODEL_STATUS function, which accepts a registered_name and registered_version:
=> SELECT CHANGE_MODEL_STATUS('linear_reg_app', 2, 'staging');
CHANGE_MODEL_STATUS
-----------------------------------------------------------------------------
The status of model [linear_reg_app] - version [2] is changed to [staging]
(1 row)
=> SELECT CHANGE_MODEL_STATUS('linear_reg_app', 3, 'staging');
CHANGE_MODEL_STATUS
-----------------------------------------------------------------------------
The status of model [linear_reg_app] - version [3] is changed to [staging]
(1 row)
After comparing the evaluation metrics of the two staged models against the model currently in production, you can put the better performing model into production. In this case, the linear_reg_spark1 model is moved into production and the linear_reg_spark1 model is declined:
=> SELECT CHANGE_MODEL_STATUS('linear_reg_app', 2, 'declined');
CHANGE_MODEL_STATUS
-----------------------------------------------------------------------------
The status of model [linear_reg_app] - version [2] is changed to [declined]
(1 row)
=> SELECT CHANGE_MODEL_STATUS('linear_reg_app', 3, 'production');
CHANGE_MODEL_STATUS
-----------------------------------------------------------------------------
The status of model [linear_reg_app] - version [3] is changed to [production]
(1 row)
You can then query the REGISTERED_MODELS system table to confirm that the linear_reg_spark1 model is now in 'production', the native_linear_reg model has been set to 'declined', and the previously in production linear_reg_spark1 model has been automatically moved to 'archived':
=> SELECT * FROM REGISTERED_MODELS;
registered_name | registered_version | status | registered_time | model_id | schema_name | model_name | model_type | category
------------------+--------------------+--------------+-------------------------------+-------------------+-------------+-------------------+-----------------------+----------------
linear_reg_app | 3 | PRODUCTION | 2023-01-26 09:52:00.082166-04 | 45035996273714020 | public | linear_reg_spark1 | PMML_REGRESSION_MODEL | PMML
linear_reg_app | 2 | DECLINED | 2023-01-26 09:51:04.553102-05 | 45035996273850350 | public | native_linear_reg | LINEAR_REGRESSION | VERTICA_MODELS
linear_reg_app | 1 | ARCHIVED | 2023-01-24 05:29:25.990626-02 | 45035996273853740 | public | linear_reg_newton | LINEAR_REGRESSION | VERTICA_MODELS
logistic_reg_app | 1 | PRODUCTION | 2023-01-23 08:49:25.990626-02 | 45035996273853740 | public | log_reg_cgd | LOGISTIC_REGRESSION | VERTICA_MODELS
(4 rows)
To remove the declined native_linear_reg model from the versioning environment, you can set the status to 'unregistered':
=> SELECT CHANGE_MODEL_STATUS('linear_reg_app', 2, 'unregistered');
CHANGE_MODEL_STATUS
----------------------------------------------------------------------------------
The status of model [linear_reg_app] - version [2] is changed to [unregistered]
(1 row)
=> SELECT * FROM REGISTERED_MODELS;
registered_name | registered_version | status | registered_time | model_id | schema_name | model_name | model_type | category
------------------+--------------------+--------------+-------------------------------+-------------------+-------------+-------------------+-----------------------+----------------
linear_reg_app | 3 | PRODUCTION | 2023-01-26 09:52:00.082166-04 | 45035996273714020 | public | linear_reg_spark1 | PMML_REGRESSION_MODEL | PMML
linear_reg_app | 1 | ARCHIVED | 2023-01-24 05:29:25.990626-02 | 45035996273853740 | public | linear_reg_newton | LINEAR_REGRESSION | VERTICA_MODELS
logistic_reg_app | 1 | PRODUCTION | 2023-01-23 08:49:25.990626-02 | 45035996273853740 | public | log_reg_cgd | LOGISTIC_REGRESSION | VERTICA_MODELS
(3 rows)
You can see that the native_linear_reg model no longer appears in the REGISTERED_MODELS system table. However, you can still query the MODEL_STATUS_HISTORY system table to view the status history of native_linear_reg:
=> SELECT * FROM MODEL_STATUS_HISTORY WHERE model_id=45035996273850350;
registered_name | registered_version | new_status | old_status | status_change_time | operator_id | operator_name | model_id | schema_name | model_name
-----------------+--------------------+--------------+------------- +-------------------------------+-------------------+---------------+-------------------+-------------+-------------------
linear_reg_app | 2 | UNDER_REVIEW | UNREGISTERED | 2023-01-26 09:51:04.553102-05 | 45035996273964824 | user1 | 45035996273850350 | public | native_linear_reg
linear_reg_app | 2 | STAGING | UNDER_REVIEW | 2023-01-29 11:33:02.052464-05 | 45035996273704962 | supervisor1 | 45035996273850350 | public | native_linear_reg
linear_reg_app | 2 | DECLINED | STAGING | 2023-01-30 04:12:30.481136-05 | 45035996273704962 | supervisor1 | 45035996273850350 | public | native_linear_reg
linear_reg_app | 2 | UNREGISTERED | DECLINED | 2023-02-02 03:25:32.332132-05 | 45035996273704962 | supervisor1 | 45035996273850350 | public | native_linear_reg
(4 rows)
See also
2 - Altering models
You can modify a model using ALTER MODEL, in response to your model's needs.
You can modify a model using
ALTER MODEL, in response to your model's needs. You can alter a model by renaming the model, changing the owner, and changing the schema.
You can drop or alter any model that you create.
2.1 - Changing model ownership
As a superuser or model owner, you can reassign model ownership with ALTER MODEL as follows:.
As a superuser or model owner, you can reassign model ownership with
ALTER MODEL as follows:
ALTER MODEL model-nameOWNER TOowner-name
Changing model ownership is useful when a model owner leaves or changes responsibilities. Because you can change the owner, the models do not need to be rewritten.
Example
The following example shows how you can use ALTER_MODEL to change the model owner:
-
Find the model you want to alter. As the dbadmin, you own the model.
=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mykmeansmodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mykmeansmodel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964 -
Change the model owner from dbadmin to user1.
=> ALTER MODEL mykmeansmodel OWNER TO user1; ALTER MODEL -
Review
V_CATALOG.MODELSto verify that the owner was changed.=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mykmeansmodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mykmeansmodel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | user1 category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964
2.2 - Moving models to another schema
You can move a model from one schema to another with ALTER MODEL.
You can move a model from one schema to another with
ALTER MODEL. You can move the model as a superuser or user with USAGE privileges on the current schema and CREATE privileges on the destination schema.
Example
The following example shows how you can use ALTER MODEL to change the model schema:
-
Find the model you want to alter.
=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mykmeansmodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mykmeansmodel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964 -
Change the model schema.
=> ALTER MODEL mykmeansmodel SET SCHEMA test; ALTER MODEL -
Review
V_CATALOG.MODELSto verify that the owner was changed.=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mykmeansmodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mykmeansmodel schema_id | 45035996273704978 schema_name | test owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964
2.3 - Renaming a model
ALTER MODEL lets you rename models.
ALTER MODEL lets you rename models. For example:
-
Find the model you want to alter.
=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mymodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mymodel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964 -
Rename the model.
=> ALTER MODEL mymodel RENAME TO mykmeansmodel; ALTER MODEL -
Review
V_CATALOG.MODELSto verify that the model name was changed.=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mykmeansmodel'; -[ RECORD 1 ]--+------------------------------------------ model_id | 45035996273816618 model_name | mykmeansmodel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | kmeans is_complete | t create_time | 2017-03-02 11:16:04.990626-05 size | 964
3 - Dropping models
DROP MODEL removes one or more models from the database.
DROP MODEL removes one or more models from the database. For example:
-
Find the model you want to drop.
=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mySvmClassModel'; -[ RECORD 1 ]--+-------------------------------- model_id | 45035996273765414 model_name | mySvmClassModel schema_id | 45035996273704978 schema_name | public owner_id | 45035996273704962 owner_name | dbadmin category | VERTICA_MODELS model_type | SVM_CLASSIFIER is_complete | t create_time | 2017-02-14 10:30:44.903946-05 size | 525 -
Drop the model.
=> DROP MODEL mySvmClassModel; DROP MODEL -
Review
V_CATALOG.MODELSto verify that the model was dropped.=> SELECT * FROM V_CATALOG.MODELS WHERE model_name='mySvmClassModel'; (0 rows)
4 - Managing model security
You can manage the security privileges on your models by using the GRANT and REVOKE statements.
You can manage the security privileges on your models by using the GRANT and REVOKE statements. The following examples show how you can change privileges on user1 and user2 using the faithful table and the linearReg model.
-
In the following example, the dbadmin grants the SELECT privilege to user1:
=> GRANT SELECT ON TABLE faithful TO user1; GRANT PRIVILEGE -
Then, the dbadmin grants the CREATE privilege on the public schema to user1:
=> GRANT CREATE ON SCHEMA public TO user1; GRANT PRIVILEGE -
Connect to the database as user1:
=> \c - user1 -
As user1, build the linearReg model:
=> SELECT LINEAR_REG('linearReg', 'faithful', 'waiting', 'eruptions');
LINEAR_REG
---------------------------
Finished in 1 iterations
(1 row)
- As user1, grant USAGE privileges to user2:
=> GRANT USAGE ON MODEL linearReg TO user2;
GRANT PRIVILEGE
- Connect to the database as user2:
=> \c - user2
- To confirm privileges were granted to user2, run the GET_MODEL_SUMMARY function. A user with the USAGE privilege on a model can run GET_MODEL_SUMMARY on that model:
=> SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='linearReg');
=======
details
=======
predictor|coefficient|std_err |t_value |p_value
---------+-----------+--------+--------+--------
Intercept| 33.47440 | 1.15487|28.98533| 0.00000
eruptions| 10.72964 | 0.31475|34.08903| 0.00000
==============
regularization
==============
type| lambda
----+--------
none| 1.00000
===========
call_string
===========
linear_reg('public.linearReg', 'faithful', '"waiting"', 'eruptions'
USING PARAMETERS optimizer='newton', epsilon=1e-06, max_iterations=100, regularization='none', lambda=1)
===============
Additional Info
===============
Name |Value
------------------+-----
iteration_count | 1
rejected_row_count| 0
accepted_row_count| 272
(1 row)
- Connect to the database as user1:
=> \c - user1
- Then, you can use the REVOKE statement to revoke privileges from user2:
=> REVOKE USAGE ON MODEL linearReg FROM user2;
REVOKE PRIVILEGE
- To confirm the privileges were revoked, connect as user 2 and run the GET_MODEL_SUMMARY function:
=> \c - user2
=>SELECT GET_MODEL_SUMMARY('linearReg');
ERROR 7523: Problem in get_model_summary.
Detail: Permission denied for model linearReg
See also
5 - Viewing model attributes
The following topics explain the model attributes for the Vertica machine learning algorithms.
The following topics explain the model attributes for the Vertica machine learning algorithms. These attributes describe the internal structure of a particular model:
6 - Summarizing models
-
Find the model you want to summarize.
=> SELECT * FROM v_catalog.models WHERE model_name='svm_class'; model_id | model_name | schema_id | schema_name | owner_id | owner_name | category | model_type | is_complete | create_time | size -------------------+------------+-------------------+-------------+-------------------+------------+-------- --------+----------------+-------------+-------------------------------+------ 45035996273715226 | svm_class | 45035996273704980 | public | 45035996273704962 | dbadmin | VERTICA_MODELS | SVM_CLASSIFIER | t | 2017-08-28 09:49:00.082166-04 | 1427 (1 row) -
View the model summary.
=> SELECT GET_MODEL_SUMMARY(USING PARAMETERS model_name='svm_class'); ------------------------------------------------------------------------ ======= details ======= predictor|coefficient ---------+----------- Intercept| -0.02006 cyl | 0.15367 mpg | 0.15698 wt | -1.78157 hp | 0.00957 =========== call_string =========== SELECT svm_classifier('public.svm_class', 'mtcars_train', '"am"', 'cyl, mpg, wt, hp, gear' USING PARAMETERS exclude_columns='gear', C=1, max_iterations=100, epsilon=0.001); =============== Additional Info =============== Name |Value ------------------+----- accepted_row_count| 20 rejected_row_count| 0 iteration_count | 12 (1 row)
See also
7 - Viewing models
Vertica stores the models you create in the V_CATALOG.MODELS system table.
Vertica stores the models you create in the V_CATALOG.MODELS system table.
You can query V_CATALOG.MODELS to view information about the models you have created:
=> SELECT * FROM V_CATALOG.MODELS;
-[ RECORD 1 ]--+------------------------------------------
model_id | 45035996273765414
model_name | mySvmClassModel
schema_id | 45035996273704978
schema_name | public
owner_id | 45035996273704962
owner_name | dbadmin
category | VERTICA_MODELS
model_type | SVM_CLASSIFIER
is_complete | t
create_time | 2017-02-14 10:30:44.903946-05
size | 525
-[ RECORD 2 ]--+------------------------------------------
model_id | 45035996273711466
model_name | mtcars_normfit
schema_id | 45035996273704978
schema_name | public
owner_id | 45035996273704962
owner_name | dbadmin
category | VERTICA_MODELS
model_type | SVM_CLASSIFIER
is_complete | t
create_time | 2017-02-06 15:03:05.651941-05
size | 288