Model management

• 1: Altering models
• 1.1: Changing model ownership
• 1.2: Moving models to another schema
• 1.3: Renaming a model
• 2: Dropping models
• 3: Managing model security
• 4: Viewing model attributes
• 5: Summarizing models
• 6: Viewing models

1 - Altering models

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.

1.1 - Changing model ownership

As a superuser or model owner, you can reassign model ownership with ALTER MODEL as follows:

ALTER MODEL model-name OWNER TO owner-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:

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

2. Change the model owner from dbadmin to user1.

   => ALTER MODEL mykmeansmodel OWNER TO user1;
        ALTER MODEL
   

3. Review V_CATALOG.MODELS to 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
   

1.2 - Moving models to another schema

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:

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

2. Change the model schema.

   => ALTER MODEL mykmeansmodel SET SCHEMA test;
        ALTER MODEL
   

3. Review V_CATALOG.MODELS to 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
   

1.3 - Renaming a model

ALTER MODEL lets you rename models. For example:

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

2. Rename the model.

   => ALTER MODEL mymodel RENAME TO mykmeansmodel;
        ALTER MODEL
   

3. Review V_CATALOG.MODELS to 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
   

2 - Dropping models

DROP MODEL removes one or more models from the database. For example:

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

2. Drop the model.

   => DROP MODEL mySvmClassModel;
        DROP MODEL
   

3. Review V_CATALOG.MODELS to verify that the model was dropped.

   => SELECT * FROM V_CATALOG.MODELS WHERE model_name='mySvmClassModel';
   (0 rows)
   

3 - Managing model security

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

• GRANT (model)
• REVOKE (model)

4 - Viewing model attributes

The following topics explain the model attributes for the Vertica machine learning algorithms. These attributes describe the internal structure of a particular model:

• Cross validation model attributes

• K-means model attributes

• Naive Bayes model attributes

• Normalization model attributes

• One Hot Encoder model attributes

• Random forest model attributes

• Regression model attributes

• SVM model attributes

5 - Summarizing models

1. 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)
   

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

• GET_MODEL_SUMMARY

6 - Viewing models

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

See also

• MODELS
• V_CATALOG schema