Models in Vertica

relireli Vertica Customer
edited July 2021 in General Discussion

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

Answers

  • SruthiASruthiA Vertica Employee Administrator

    @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

  • badroualibadrouali Vertica Employee

    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

  • relireli Vertica Customer

    thank you all !!!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file