Run Predictions Inside the Database

It parses a fitted 'R' model object, and returns a formula in 'Tidy Eval' code that calculates the predictions. It works with several databases back-ends because it leverages 'dplyr' and 'dbplyr' for the final 'SQL' translation of the algorithm. Dozens of model classes are supported; see the "Supported models" article at < https://tidypredict.tidymodels.org/articles/models.html> for the current list.

tidypredict

The main goal of tidypredict is to enable running predictions inside databases. It reads the model, extracts the components needed to calculate the prediction, and then creates an R formula that can be translated into SQL. In other words, it is able to parse a model such as this one:

model <- lm(mpg ~ wt + cyl, data = mtcars)

tidypredict can return a SQL statement that is ready to run inside the database. Because it uses dplyr’s database interface, it works with several databases back-ends, such as MS SQL:

tidypredict_sql(model, dbplyr::simulate_mssql())

## <SQL> (39.686261480253 + ([wt] * -3.19097213898374)) + ([cyl] * -1.5077949682598)

Installation

Install tidypredict from CRAN using:

install.packages("tidypredict")

Or install the development version using devtools as follows:

install.packages("remotes")
remotes::install_github("tidymodels/tidypredict")

Functions

tidypredict has only a few functions, and it is not expected that number to grow much. The main focus at this time is to add more models to support.

How it works

Instead of translating directly to a SQL statement, tidypredict creates an R formula. That formula can then be used inside dplyr. The overall workflow would be as illustrated in the image above, and described here:

1. Fit the model using a base R model, or one from the packages listed in Supported Models
2. tidypredict reads model, and creates a list object with the necessary components to run predictions
3. tidypredict builds an R formula based on the list object
4. dplyr evaluates the formula created by tidypredict
5. dplyr translates the formula into a SQL statement, or any other interfaces.
6. The database executes the SQL statement(s) created by dplyr

Parsed model spec

tidypredict writes and reads a spec based on a model. Instead of simply writing the R formula directly, splitting the spec from the formula adds the following capabilities:

1. No more saving models as .rds - Specifically for cases when the model needs to be used for predictions in a Shiny app.
2. Beyond R models - Technically, anything that can write a proper spec, can be read into tidypredict. It also means, that the parsed model spec can become a good alternative to using PMML.

Supported models

tidypredict parses 43 fitted model classes from 30 modeling packages. Supported models has the full list, with the parsnip spec and engine for each and a link to a worked example. In brief:

• Regression: lm(), glm(), glmnet::glmnet(), LiblineaR::LiblineaR(), quantreg::rq(), nnet::multinom(), kernlab::ksvm(), nnet::nnet(), earth::earth(), mixOmics PLS, parsnip::nullmodel()
• Classification and discriminant analysis: naivebayes::naive_bayes(), klaR::NaiveBayes(), MASS::lda(), MASS::qda(), mda::fda(), sda::sda(), sparsediscrim
• Trees and forests: rpart::rpart(), C50::C5.0(), partykit::ctree() and cforest(), randomForest::randomForest(), ranger::ranger(), aorsf::orsf(), baguette::bagger(), dbarts::bart()
• Boosting and rules: xgboost, lightgbm, catboost, mboost::blackboost(), Cubist::cubist(), xrf::xrf(), H2O GBM and RuleFit

tidypredict dispatches on the class of the fitted model, so models fitted through parsnip work for any engine whose underlying model appears above: pass the parsnip fit object to tidypredict_fit() just as you would the engine’s own fit.

tidypredict_interval() and tidypredict_sql_interval() are narrower, and only support lm() and glm() models.

broom

The tidy() function from broom works with linear models parsed via tidypredict

pm <- parse_model(lm(wt ~ ., mtcars))
tidy(pm)

## # A tibble: 11 × 2
##    term        estimate
##    <chr>          <dbl>
##  1 (Intercept) -0.231  
##  2 mpg         -0.0417 
##  3 cyl         -0.0573 
##  4 disp         0.00669
##  5 hp          -0.00323
##  6 drat        -0.0901 
##  7 qsec         0.200  
##  8 vs          -0.0664 
##  9 am           0.0184 
## 10 gear        -0.0935 
## 11 carb         0.249

Contributing

This project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

• For questions and discussions about tidymodels packages, modeling, and machine learning, please post on Posit Community.

• If you think you have encountered a bug, please submit an issue.

• Either way, learn how to create and share a reprex (a minimal, reproducible example), to clearly communicate about your code.

• Check out further details on contributing guidelines for tidymodels packages and how to get help.