Train Locally and Predict in Exasol

This section describes how you can train your data locally and predict in Exasol. Start by loading the Boston Housing dataset in R. The dataset is available through the mlbench package.

# load libraries
library(mlbench)         # Load data
library(RODBC)           # ODBC database connectivity
library(exasol)          # R interface for Exasol database
library(randomForest)    # Random forests for regression and classification
library(RCurl)           # HTTP communication

After loading the data in your environment, make a split for training and testing sets. There are many methods to it, one of them is to randomly generate indices that will subset rows from the main set.

The ratio of the split is arbitrary and depends on available data and application. In this case, keep around 75% of the data for training and the remaining 25% for testing purpose.

# Load data
data("BostonHousing")

# Generate random indices (75% of the sample size)
set.seed(42)
indices <- sample(nrow(BostonHousing),size=floor(nrow(BostonHousing) * 0.75))

# Add a dummy variable with 2 levels "Train" and "Test"
BostonHousing$split = factor(NA, levels = c("Train", "Test"))

# Assign predefined rows respectively to "Train" and "Test" subsets
BostonHousing$split[indices]  = "Train"
BostonHousing$split[-indices] = "Test"

# Add id variable
BostonHousing$id = seq(1:nrow(BostonHousing))

Now you can train a basic random forest model on our data and see which variables contribute the most to predicting the median house value.

set.seed(42)
# Run model (note that we filter only for the training data (split=="Train"))
rf_model = randomForest(medv ~ .-split -id,
                        data = BostonHousing[BostonHousing$split=="Train",],
                        importance = TRUE)

# See which variables are important in predicting house prices
varImpPlot(rf_model)
# rm    - number of rooms per house
# lstat - % lower status of the population

Send model to BucketFS

UDF scripts are executed in parallel on the Exasol cluster and in this case for the prediction such script needs to have access to the model we just trained. It's possible to use any file service, when considering performance it is more efficient to have a storage space in the local cluster nodes.

Keep in mind that the Exasol database cluster stores only tables and you cannot upload our model in there.

Exasol BucketFS file system has been developed for such use cases, where data should be stored synchronously and replicated across the cluster. This concept can be used to extend script languages and even to install completely new script languages on the Exasol cluster. For more information, see Scripting.

Files can be sent and retrieved from BucketFS in the following ways:

  • Using packages that allow HTTP communication in R (httr or RCurl)
  • BucketFS Explorer (drag and drop tool provided by Exasol)
  • Using curl commands (not covered here)

With the first method we will use the RCurl package. It is important to note that the model which lives in our R environment now has to be serialized (converted into raw text). When we want to retrieve it we can unserialize it and return it to its original file type. See ?serialize for more information.

# Define options for the authentication
curl_opts = curlOptions(userpwd  = "w:exasol",
                        verbose  = FALSE,
                        httpauth = AUTH_BASIC)

# Transfer model to the bucket
httpPUT(
  # EXABucket URL
  url = "http://192.168.56.103:8080/bucket1/rf_model",
  # It is important to serialize the model
  content = serialize(rf_model, ascii = FALSE, connection = NULL),
  # EXABucket: authenticate
  curl = getCurlHandle(.opts = curl_opts)
)

If the size of you model increases, you can use the Strip package that allows you to reduce the memory footprint of your models.

You can open BucketFS Explorer and see that the file is transferred to bucket1.

You can also save the model locally and then just drag and drop it into the bucket. BucketFS and BucketFS Explorer features are covered in BucketFS.

Send data to Exasol

To run the prediction in Exasol you need to transfer the data into the database. To know how to write and read data into Exasol, see Write Data and Read Data.

# Create connection with the Exasol database
exaconn <- dbConnect(
    drv     = "exa",                  # EXAdriver object
    exahost = "192.168.56.103:8563",  # IP of database cluster
    uid     = "sys",                  # Username
    pwd     = "exasol")               # Password

# Create database schema (if it does not yet exist) with the name r_demo
# (This also opens it, i.e. makes it the default container for all subsequent steps below)
odbcQuery(exaconn, "CREATE SCHEMA IF NOT EXISTS r_demo")

# If the schema already existed, we need to open it to make it the default container for all subsequent steps below
odbcQuery(exaconn, "OPEN SCHEMA r_demo")

# Create an empty table in Exasol with the name boston_housing
odbcQuery(exaconn,
    "CREATE OR REPLACE TABLE boston_housing(
         crim    DOUBLE,
         zn      DOUBLE,
         indus   DOUBLE,
         chas    VARCHAR(10),
         nox     DOUBLE,
         rm      DOUBLE,
         age     DOUBLE,
         dis     DOUBLE,
         rad     DOUBLE,
         tax     DOUBLE,
         ptratio DOUBLE,
         b       DOUBLE,
         lstat   DOUBLE,
         medv    DOUBLE,
         split   VARCHAR(10),
         id      INT
     )")

# Write the train data into Exasol
exa.writeData(exaconn, data = BostonHousing, tableName = "boston_housing")

Through an SQL client, you can see the data that is transferred to the database with the corresponding schema.

Make prediction using UDF

You are now ready to use the algorithm which is in BucketFS to run a prediction on the data that's in Exasol. For this, you can use a UDF function exa.createScript.

PredictInExasol1 <- exa.createScript(
  exaconn,
  "r_demo.dt_predict1",
  function(data) {

    # Load the required packages
    require(RCurl)
    require(randomForest)

    # Load data in chuks of 1000 rows at a time (very useful knob with big data!)
    repeat {
      if (!data$next_row(1000))
        break

      # put data in a data.frame
      df <- data.frame(
              id       = data$id,
              crim     = data$crim,
              zn       = data$zn,
              indus    = data$indus,
              chas     = data$chas,
              nox      = data$nox,
              rm       = data$rm,
              age      = data$age,
              dis      = data$dis,
              rad      = data$rad,
              tax      = data$tax,
              ptratio  = data$ptratio,
              b        = data$b,
              lstat    = data$lstat,
              medv     = data$medv,
              split    = data$split
            )
    }

    # Set options for retrieving model from bucket
    curl_opts = curlOptions(userpwd = "w:exasol",
                            verbose = TRUE,
                            httpauth=AUTH_BASIC)

    # Loading the model from the bucket (note that is unserialized)
    rf_model = unserialize(httpGET(url  = "http://192.168.56.103:8080/bucket1/rf_model",
                                   curl = getCurlHandle(.opts = curl_opts)))

    # Use the loaded model to make the prediction
    prediction <- predict(rf_model, newdata = df)

    # Return of the forecast
    data$emit(df$id, df$medv, prediction)
  },

  # Input arguments
  inArgs  = c("id         INT",
              "crim       DOUBLE",
              "zn         DOUBLE",
              "indus      DOUBLE",
              "chas       VARCHAR(10)",
              "nox        DOUBLE",
              "rm         DOUBLE",
              "age        DOUBLE",
              "dis        DOUBLE",
              "rad        DOUBLE",
              "tax        DOUBLE",
              "ptratio    DOUBLE",
              "b          DOUBLE",
              "lstat      DOUBLE",
              "medv       DOUBLE",
              "split      VARCHAR(10)"),

  # Output arguments
  outArgs = c("id         INT",
              "RealValue  DOUBLE",
              "Prediction DOUBLE")
)

# Create a table with the real values and predicted ones
# Note that the prediction is done in test data using 'where' argument
prediction_output = PredictInExasol1("id","crim", "zn", "indus", "chas", "nox", "rm", "age", "dis",
                                     "rad", "tax", "ptratio", "b", "lstat", "medv", "split",
                                     table = "r_demo.boston_housing",
                                     groupBy = "iproc(),mod(iproc(),5)",
                                     where = "split = 'Test'")

# Check the root mean squared error (RMSE)
RMSE = sqrt(mean((prediction_output$PREDICTION - prediction_output$REALVALUE)^2))
# Should be 3.35

You can also plot your predicted values and see the output:

# Plot the predictions against real values
library(ggplot2)

plot_predictions = ggplot(prediction_output, aes(ID)) +
                    geom_line(aes(y = PREDICTION, colour = "Prediction")) +
                    geom_line(aes(y = REALVALUE, colour = "True Value")) +
                    xlab("") +
                    ylab("Results") +
                    ggtitle("Predicted vs Real values")
                    theme(legend.title=element_blank())

plot(plot_predictions)