Generate Snowflake UDF Scoring Code¶

Scoring Code makes it easy for you to perform predictions on DataRobot models anywhere you want by exporting a model to a Java JAR file. Snowflake user-defined functions (UDFs) allow you to execute arbitrary Java code on Snowflake. DataRobot provides an API to Scoring Code for you to use Scoring Code as a UDF on Snowflake without writing any additional code.

To download and execute scoring code with Snowflake UDFs, you must meet the following prerequisites:

• Prepare a DataRobot model that supports Scoring Code for deployment and create a model package with it.

• Register your Snowflake prediction environment to use with the model.

Access Scoring Code¶

When you have your model package and prediction environment prepared in DataRobot, you can deploy the model in order to access the Scoring Code for use with Snowflake.

1. Navigate to the Model Registry and select your model package. On the Deployments tab, select Create new deployment.

   

2. Complete the fields and configure the deployment as desired. To generate Snowflake UDF Scoring Code, specify your Snowflake prediction environment under the Inference header.

   

3. Once fully configured, select Create deployment at the top of the screen.

4. After deploying the model, access the deployment from the inventory and navigate to the Predictions > Portable Predictions tab. This is where Datarobot hosts the Scoring Code for the model.

   

5. (Optional) Toggle on Include prediction explanations to include prediction explanations with the prediction results, then click Download. The Scoring Code JAR file appears in your browser bar.

   

6. When the Scoring Code download completes, copy the installation script and update it with your Snowflake warehouse, database, schema, and the path to the Scoring Code JAR file, then execute the script.

   

   Copy and paste (for regression)

   -- Replace with the warehouse to use
   USE WAREHOUSE my_warehouse;
   -- Replace with the database to use
   USE DATABASE my_database;
   
   -- Replace with the schema to use
   CREATE SCHEMA IF NOT EXISTS scoring_code_udf_schema;
   USE SCHEMA scoring_code_udf_schema;
   
   -- Update this path to match the Scoring Code JAR location
   PUT 'file:///path/to/downloaded_scoring_code.jar' '@~/jars/' AUTO_COMPRESS=FALSE;
   
   -- Create the UDF
   CREATE OR REPLACE FUNCTION datarobot_udf(RowValue OBJECT)
       RETURNS FLOAT
       LANGUAGE JAVA
       IMPORTS=('@~/jars/downloaded_scoring_code.jar')
       HANDLER='com.datarobot.prediction.simple.RegressionPredictor.score';
   

   Copy and paste (for classification)

   -- Replace with the warehouse to use
   USE WAREHOUSE my_warehouse;
   -- Replace with the database to use
   USE DATABASE my_database;
   
   -- Replace with the schema to use
   CREATE SCHEMA IF NOT EXISTS scoring_code_udf_schema;
   USE SCHEMA scoring_code_udf_schema;
   
   -- Update this path to match the Scoring Code JAR location
   PUT 'file:///path/to/downloaded_scoring_code.jar' '@~/jars/' AUTO_COMPRESS=FALSE;
   
   -- Create the UDF
   CREATE OR REPLACE FUNCTION datarobot_udf(RowValue OBJECT)
       RETURNS OBJECT
       LANGUAGE JAVA
       IMPORTS=('@~/jars/downloaded_scoring_code.jar')
       HANDLER='com.datarobot.prediction.simple.ClassificationPredictor.score';
   

   The script uploads the JAR file to a Snowflake stage and creates a UDF for making predictions with Scoring Code.

   Note

   To run these scripts, you must use the SnowSQL command provided in the next step (7). You can't execute these scripts in Snowflake's UI.

7. Execute the script in SnowSQL by providing your credentials and the script location:

   

   Copy and paste

   snowsql --accountname $ACCOUNT_NAME --username $USERNAME --filename $SCRIPT_PATH
   

8. With your UDF successfully created, you can now use Snowflake to score data. Use the UDF in any manner supported by SQL.

   

   Copy and paste

   /*
   Scoring your data
   
   The Scoring Code UDF accepts rows of data as objects. The OBJECT_CONSTRUCT_KEEP_NULL method can be used to turn a table row into an object.
   */
   
   -- Scoring without specifying columns. Data can contain nulls
   SELECT my_datarobot_model(OBJECT_CONSTRUCT_KEEP_NULL(*)) FROM source_table;
   

9. After scoring data, you can upload actuals via the Settings tab in order to enable accuracy monitoring and more.

   

Feature Considerations¶

Consider the following when using Scoring Code as a UDF on Snowflake.

• Keras models cannot be executed in Snowflake.

• Time series Scoring Code is not supported for Snowflake.

• Scoring Code JARs created prior to the release of the Snowflake Scoring Code feature cannot be run in Snowflake.