Models in Vertica

reli

I need to analyze a model performance with data from a table in Vertica, I create the model file (pkl) in the python (pls2), and I want to check its performance inside the Vertica (add predict value to an existing table), do you know how to importing, and predicting in Vertica with an outside model file

SruthiA

@reli : we dont support it yet. if you can convert the pkl model to PMML format and load it into vertica. For more information, please visit the below link

https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AnalyzingData/MachineLearning/UsingExternalModels/UsingPMML/ImportingPredictingWithPMMLModels.htm

badrouali

Hi @reli,

The solution can be done by creating a UDF to do it. You have to be sure that the library used by your pickle are added in the dependencies when using the create library statement.

It can be something like:

import vertica_sdk

class my_model(vertica_sdk.ScalarFunction):

    def setup(self, server_interface, col_types):
                # unpickling the model and saving it as an attribute
        self.model = unpickle('...')

    def processBlock(self, server_interface, arg_reader, res_writer):
        while(True):
                        # scoring the row
            result = self.model([arg_reader.getFloat(0) ... arg_reader.getFloat(n)])
                        # writing the result
            res_writer.setFloat(result)
            res_writer.next()
            if not arg_reader.next():
                break

    def destroy(self, server_interface, col_types):
        pass

class my_model_factory(vertica_sdk.ScalarFunctionFactory):

    def createScalarFunction(self, srv):
        return my_model()

    def getPrototype(self, server_interface, arg_types, return_type):
        arg_types.addFloat()
                ...
        return_type.addFloat()

    def getReturnType(self, server_interface, arg_types, return_type):
        return_type.addFloat()

You can then install the function:

CREATE OR REPLACE LIBRARY my_model AS '/home/dbadmin/my_model.py' DEPENDS '/path/to/dependencies' LANGUAGE 'Python';
CREATE OR REPLACE FUNCTION my_model AS NAME 'my_model' LIBRARY python_math;

Hope it will help!

Badr

reli

thank you all !!!