Teradata Package for R Function Reference | 17.20 - td_h2o_predict - Teradata Package for R - Look here for syntax, methods and examples for the functions included in the Teradata Package for R.

Teradata® Package for R Function Reference
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Teradata Package for R
Release Number
17.20
Published
March 2024
ft:locale
en-US
ft:lastEdition
2024-05-03
dita:id
TeradataR_FxRef_Enterprise_1720
lifecycle
latest
Product Category
Teradata Vantage

H2OPredict

Description

The td_h2o_predict_sqle() function performs a prediction on each row of the input table using a model previously trained in H2O and then loaded into the database. The model uses an interchange format called MOJO and it is loaded to Teradata database in a table by the user as a blob. The model data prepared by user should have a model id for each model (residing as a MOJO object) created by the user.
td_h2o_predict_sqle() supports Driverless AI and H2O-3 MOJO models.
H2O Driverless AI (DAI) provides a number of transformations.
The following transformers are available for regression and classification (multiclass and binary) experiments:

  • Numeric

  • Categorical

  • Time and Date

  • Time Series

  • NLP (test)

  • Image

Usage

  td_h2o_predict_sqle (
      modeldata = NULL,
      newdata = NULL,
      accumulate = NULL,
      model.output.fields = NULL,
      overwrite.cached.models = NULL,
      model.type = "OpenSource",
      enable.options = NULL,
      is.debug = FALSE,
      ...
  )

Arguments

modeldata

Required Argument.
Specifies the model tbl_teradata to be used for scoring.
Types: tbl_teradata

newdata

Required Argument.
Specifies the input tbl_teradata that contains the data to be scored.
Types: tbl_teradata

accumulate

Required Argument.
Specifies the name(s) of input tbl_teradata column(s)
to copy to the output.
Types: character OR vector of Strings (character)

model.output.fields

Optional Argument.
Specifies the output fields to add as individual columns instead of the entire JSON output. Specify fields with a comma-separated list.
Types: character OR vector of Strings (character)

overwrite.cached.models

Optional Argument.
Specifies the model name that needs to be removed from the cache.
When a model loaded into the memory of the node fits in the cache, it stays in the cache until being evicted to make space for another model that needs to be loaded. Therefore, a model can remain in the cache even after the completion of function execution. Other functions that use the same model can use it, saving the cost of reloading it into memory. User should overwrite a cached model only when it is updated, to make sure that the Predict function uses the updated model instead of the cached model.
Note:
Do not use the "overwrite.cached.models" argument except when user is trying to replace a previously cached model. Using the argument in other cases, including in concurrent queries or multiple times within a short period of time lead to an OOM error.
Default behavior: The function does not overwrite cached models.
Permitted Values: "true", "t", "yes", "y", "1", "false", "f", "no", "n", "0", "*", "current_cached_model"
Types: character OR vector of Strings (character)

model.type

Optional Argument.
Specifies the model type for H2O model prediction.
Default Value: "OpenSource"
Permitted Values: "DAI", "OpenSource"
Types: character

enable.options

Optional Argument.
Specifies feature option values to have them appear in the JSON output:

  • contributions: The contribution of each input feature towards the prediction.

  • stageProbabilities: Prediction probabilities of trees in each stage or iteration.

  • leafNodeAssignments: The leaf placements of the row in all the trees in the tree-based model.

When the feature options are not specified, the features are considered false and the following values are not populated in the output JSON:

  • contributions (applies only to binomial or regression models)

  • leafNodeAssignments and stageProbabilities (applies to binomial, regression, multinomial, and AnomalyDetection models)

Permitted Values: "contributions", "stageProbabilities", "leafNodeAssignments"
Types: character OR vector of Strings (character)

is.debug

Optional Argument.
Specifies whether debug statements are added to a trace table or not.
When set to TRUE, debug statements are added to a trace table that must be created beforehand.
Notes:

  • Only available with BYOM version 3.00.00.02 and later.

  • To save logs for debugging, user can create an error log by using the is.debug=TRUE parameter in the predict functions.
    A database trace table is used to collect this information which does impact performance of the function, so using small data input sizes is recommended.

  • To generate this log, user must do the following: 1. Create a global trace table with columns vproc_ID BYTE(2), Sequence INTEGER, Trace_Output VARCHAR(31000) 2. Turn on session function tracing: SET SESSION FUNCTION TRACE USING ” FOR TABLE <trace_table_name_created_in_step_1>; 3. Execute function with "is.debug" set to TRUE. 4. Debug information is logged to the table created in step 1. 5. To turn off the logging, either disconnect from the session or run following SQL: SET SESSION FUNCTION TRACE OFF; The trace table is temporary and the information is deleted if user logs off from the session. If long term persistence is necessary, user can copy the table to a permanent table before leaving the session.

Default Value: FALSE
Types: logical

...

Specifies the generic keyword arguments SQLE functions accept. Below are the generic keyword arguments:

persist:
Optional Argument.
Specifies whether to persist the results of the function in a table or not. When set to TRUE, results are persisted in a table; otherwise, results are garbage collected at the end of the session.
Default Value: FALSE
Types: logical

volatile:
Optional Argument.
Specifies whether to put the results of the
function in a volatile table or not. When set to TRUE, results are stored in a volatile table, otherwise not.
Default Value: FALSE
Types: logical

Function allows the user to partition, hash, order or local order the input data. These generic arguments are available for each argument that accepts tbl_teradata as input and can be accessed as:

  • "<input.data.arg.name>.partition.column" accepts character or vector of character (Strings)

  • "<input.data.arg.name>.hash.column" accepts character or vector of character (Strings)

  • "<input.data.arg.name>.order.column" accepts character or vector of character (Strings)

  • "local.order.<input.data.arg.name>" accepts logical

Note:
These generic arguments are supported by tdplyr if the underlying SQL Engine function supports, else an exception is raised.

Value

Function returns an object of class "td_h2o_predict_sqle" which is a named list containing object of class "tbl_teradata".
Named list member(s) can be referenced directly with the "$" operator using the name(s):result

Examples

  
    
    # Get the current context/connection..
    con <- td_get_context()$connection
    
    # Load example data.
    loadExampleData("pmmlpredict_example", "iris_test")
    
    # Create tbl_teradata object.
    iris_test <- tbl(con, "iris_test")
    
    # Set install location of BYOM functions.
    options(byom.install.location = "mldb")
    
    # Check the list of available analytic functions.
    display_analytic_functions(type="BYOM")
    
    # Example 1: This example scores the data on Vantage using a GLM model generated
    #            outside of Vantage. The example performs prediction with td_h2o_predict_sqle
    #            function using this GLM model in mojo format generated by H2O.
    #            Corresponding values are specified for the "model.type", "enable.options",
    #            "model.output.fields" and "overwrite.cached.models". This will erase
    #            entire cache.
    
    # Load model file into Vantage.
    # Create following table on vantage if it does not exist.
    crt_tbl <- "CREATE SET TABLE byom_models(model_id VARCHAR(40), model BLOB)
                PRIMARY INDEX (model_id);"
    DBI::dbExecute(con, sql(crt_tbl))

    # Run the following query through BTEQ or Teradata Studio to load the
    # models. 'load_byom_model.txt' and byom files can be found under
    # 'inst/scripts' in tdplyr installation directory. This file and the byom
    # models to be loaded should be in the same directory.

    # .import vartext file load_byom_model.txt
    # .repeat *
    # USING (c1 VARCHAR(40), c2 BLOB AS DEFERRED BY NAME) INSERT INTO byom_models(:c1, :c2);
    
    # Retrieve model.
    modeldata <- tbl(con, "byom_models") 
    
    result <- td_h2o_predict_sqle(
                        newdata=iris_test,
                        newdata.partition.column='id',
                        newdata.order.column='id',
                        modeldata=modeldata,
                        modeldata.order.column='model_id',
                        model.output.fields=c('label', 'classProbabilities'),
                        accumulate=c('id', 'sepal_length', 'petal_length'),
                        overwrite.cached.models='*',
                        enable.options='stageProbabilities',
                        model.type='OpenSource'
                        )
    
    # Print the results.
    print(result$result)
    
    # Example 2: This example scores the data on Vantage using a XGBoost model generated
    #            outside of Vantage. The example performs prediction with td_h2o_predict_sqle
    #            function using this XGBoost model in mojo format generated by H2O.
    #            Corresponding values are specified for the "model.type", "enable.options",
    #            "model.output.fields" and "overwrite.cached.models". This will erase
    #            entire cache.
    
    # Retrieve model.
    modeldata <- tbl(con, "byom_models") 
    
    result <- td_h2o_predict_sqle(
                        newdata=iris_test,
                        newdata.partition.column='id',
                        newdata.order.column='id',
                        modeldata=modeldata,
                        modeldata.order.column='model_id',
                        model.output.fields=c('label', 'classProbabilities'),
                        accumulate=c('id', 'sepal_length', 'petal_length'),
                        overwrite.cached.models='*',
                        enable.options='stageProbabilities',
                        model.type='OpenSource'
                        )
    
    # Print the results.
    print(result$result)
    
    # Example 3: Example to show case the trace table usage using
    #            is.debug=TRUE.
    
    # Create the trace table if not present.
    crt_tbl_query <- 'CREATE GLOBAL TEMPORARY TRACE TABLE BYOM_Trace \
                    (vproc_ID	BYTE(2) \
                    ,Sequence	INTEGER \
                    ,Trace_Output VARCHAR(31000) CHARACTER SET LATIN NOT CASESPECIFIC) \
                    ON COMMIT PRESERVE ROWS;'
    DBI::dbExecute(con, sql(crt_tbl_query))
    
    # Turn on tracing for the session.
    DBI::dbExecute(con, "SET SESSION FUNCTION TRACE USING '' FOR TABLE BYOM_Trace;")

    modeldata <- tbl(con, "byom_models") 
    
    # Execute the td_h2o_predict_sqle() function using is.debug=TRUE.
    result <- td_h2o_predict_sqle(
                        newdata=iris_test,
                        newdata.partition.column='id',
                        newdata.order.column='id',
                        modeldata=modeldata,
                        modeldata.order.column='model_id',
                        model.output.fields=c('label', 'classProbabilities'),
                        accumulate=c('id', 'sepal_length', 'petal_length'),
                        overwrite.cached.models='*',
                        enable.options='stageProbabilities',
                        model.type='OpenSource',
                        is.debug=TRUE
                        )
    
    # Print the results.
    print(result$result)
    
    # View the trace table information.
    trace_df <- dbGetQuery(con, "select * from BYOM_Trace")
    print(trace_df)
    
    # Turn off tracing for the session.
    DBI::dbExecute(con, "SET SESSION FUNCTION TRACE OFF;")