ModelOps/10_ModelOps_GIT_PIMA_R_GBM.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "baa939bd",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       ModelOps demo: R GBM using Git\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79044f7d-d2c1-4f96-b0c0-4b23997f468b",
   "metadata": {
    "tags": []
   },
   "source": [
    "![image](images/git_meth.png) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "33e72017",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Introduction</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This notebook will cover the Operationalization of the PIMA diabetes use case with R GBM algorithm. The <strong>gbm R</strong> package is an implementation of extensions to Freund and Schapire's AdaBoost algorithm and Friedman's gradient boosting machine.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this example, we will use the GBM algorithm to generate both R model formats and operationalize them through ModelOps in the same Model Catalog than other trained models based on other libraries and languages.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b4dc0ba6",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Prerequisites</b></p>\n",
    "\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Access to Teradata Vantage</li>\n",
    "    <li>Access to VAL</li>\n",
    "    <li>Access to BYOM</li>\n",
    "    <li>Have already gone through Notebook 1 - ModelOps Setup </li>\n",
    "    <li>Have already gone through Notebook 7 - ModelOps CLI and GIT Setup </li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "321957fb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Steps in this Notebook</b></p>\n",
    "\n",
    "<ol style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>Configure the Environment </li>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Define Training function </li>\n",
    "    <li>Define Evaluate function </li>\n",
    "    <li>Define Scoring function</li>\n",
    "    <li>Define Model Metadata</li>\n",
    "    <li>Commit and Push to Git to let ModelOps manage</li>\n",
    "</ol>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fce684bd-5726-4088-91d9-2117759add40",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Configure the Environment</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, we import the required libraries, set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41bc6349",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.1 Libraries installation</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Ensure you have the following libraries installed in order to be able to run this notebook.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Run this in a terminal (File -> New -> Terminal)</p>\n",
    "\n",
    "```r\n",
    "$ R\n",
    "\n",
    "$ install.packages(c(\"gbm\", \"tdplyr\", \"getPass\", \"caret\", \"e1071\", \"ids\"))\n",
    "```\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You’ll be prompted to create a local user R installation profile as you cannot install to the base system, type Yes.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>When prompted to select a CRAN mirror, choose “USA (OR)”. To choose this you would type the number to the left of “USA (OR)”</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>A restart of the Kernel is needed to confirm changes.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8732dd18-a6e4-458b-99c7-3b90cb971ba0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Hint:</b><i>The easy way to restart the kernel to bring the above installed software into memory is to type zero zero (<b> 0 0 </b>). </i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a32c662-4e24-4e61-9a0f-b96f81ccc0f8",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>1.2 Libraries import</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d50ae5dc",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "LoadPackages <- function() {\n",
    "    if(!require('gbm')){install.packages('gbm')}\n",
    "    if(!require('tdplyr')){install.packages('tdplyr')}\n",
    "    if(!require('getPass')){install.packages('getPass')}\n",
    "    if(!require('caret')){install.packages('caret', dependencies = TRUE)}\n",
    "    if(!require('e1071')){install.packages('e1071')}\n",
    "    if(!require('ids')){install.packages('ids')}\n",
    "}\n",
    "suppressPackageStartupMessages(LoadPackages())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6f67261b-66e3-48e7-841f-f4b269d82958",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Connect to Vantage</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "361ad025",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Create Vantage connection using tdplyr\n",
    "\n",
    "con = NULL\n",
    "aoa_create_context <- function(connection = con) {\n",
    "    if (is.null(connection)) {\n",
    "        # host = readline(\"Host: \");\n",
    "        host = 'host.docker.internal'\n",
    "        # username = readline(\"Username: \");\n",
    "        username = 'demo_user'\n",
    "        db_name <- username;\n",
    "        password = getPass::getPass(\"Password: \");\n",
    "        connection <- td_create_context(host=host, uid=username, pwd=password, dType=\"native\", logmech=\"TDNEGO\");\n",
    "\n",
    "        # Set connection context\n",
    "        td_set_context(connection);\n",
    "\n",
    "        DBI::dbExecute(connection, \"SET QUERY_BAND = 'appVersion=7.0;appName=VMO;appFunc=R;org=teradata-internal-telem;' FOR SESSION VOLATILE\")\n",
    "        DBI::dbExecute(connection, paste(\"DATABASE\", db_name))\n",
    "        message(paste(\"Using this database for table/views lookup and temp objects:\", db_name))\n",
    "    }\n",
    "    return(connection)\n",
    "}\n",
    "\n",
    "con <- aoa_create_context()\n",
    "\n",
    "# set the path to the local project repository for this model demo\n",
    "model_local_path <- '~/modelops-demo-models/model_definitions/pima_r_gbm'\n",
    "system(sprintf(\"mkdir -p %s/model_modules\", model_local_path))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edb36f37",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Define Training Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The training function takes the following shape</p>\n",
    "\n",
    "```R\n",
    "train <- function(data_conf, model_conf, ...) {\n",
    "    # Connect to Vantage\n",
    "    con <- aoa_create_context()\n",
    "    \n",
    "    # your training code\n",
    "    \n",
    "    # save your model\n",
    "    saveRDS(model, \"artifacts/output/model.rds\")\n",
    "}\n",
    "```\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You can execute this from the CLI or directly within the notebook as shown.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ecf0596",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/model_modules/training.R\n",
    "LoadPackages <- function() {\n",
    "    library(\"gbm\")\n",
    "    library(\"DBI\")\n",
    "    library(\"dplyr\")\n",
    "    library(\"tdplyr\")\n",
    "\n",
    "}\n",
    "\n",
    "suppressPackageStartupMessages(LoadPackages())\n",
    "\n",
    "train <- function(data_conf, model_conf, ...) {\n",
    "    # Connect to Vantage\n",
    "    con <- aoa_create_context()\n",
    "\n",
    "    table <- tbl(con, sql(data_conf$sql))\n",
    "\n",
    "    # Create dataframe from tibble, selecting the necessary columns and mutating integer64 to integers\n",
    "    # select both the feature and target columns (ignorning e.g. entity key)\n",
    "    columns <- unlist(c(data_conf$featureNames, data_conf$targetNames), use.name = TRUE)\n",
    "    data <- table %>% select(all_of(columns)) %>% mutate(\n",
    "                       NumTimesPrg = as.integer(NumTimesPrg),\n",
    "                       PlGlcConc = as.integer(PlGlcConc),\n",
    "                       BloodP = as.integer(BloodP),\n",
    "                       SkinThick = as.integer(SkinThick),\n",
    "                       TwoHourSerIns = as.integer(TwoHourSerIns),\n",
    "                       HasDiabetes = as.integer(HasDiabetes)) %>% as.data.frame()\n",
    "\n",
    "    # Load hyperparameters from model configuration\n",
    "    hyperparams <- model_conf[[\"hyperParameters\"]]\n",
    "\n",
    "    print(\"Training model...\")\n",
    "\n",
    "    # Train model\n",
    "    model <- gbm(HasDiabetes~.,\n",
    "                 data=data,\n",
    "                 shrinkage=hyperparams$shrinkage,\n",
    "                 distribution = 'bernoulli',\n",
    "                 cv.folds=hyperparams$cv.folds,\n",
    "                 n.trees=hyperparams$n.trees,\n",
    "                 verbose=FALSE)\n",
    "\n",
    "    print(\"Model Trained!\")\n",
    "\n",
    "    # Get optimal number of iterations\n",
    "    if (hyperparams$cv.folds > 1) {\n",
    "        best.iter <- gbm.perf(model, plot.it=FALSE, method=\"cv\")\n",
    "    }\n",
    "\n",
    "    # clean the model (R stores the dataset on the model..\n",
    "    model$data <- NULL\n",
    "\n",
    "    # how to save only best.iter tree?\n",
    "    # model$best.iter <- best.iter\n",
    "    # model$trees <- light$trees[best.iter]\n",
    "\n",
    "    # Save trained model\n",
    "    print(\"Saving trained model...\")   \n",
    "    saveRDS(model, paste(ifelse(model_conf$outputPath != \"\" && !is.null(model_conf$outputPath), model_conf$outputPath, \"artifacts/output/\"), \"model.rds\", sep=\"\"))\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90817af3",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Define the ModelContext to test with. The ModelContext (dataconf and model_conf) is created and managed automatically by ModelOps \n",
    "# when it executes your code via CLI / UI. However, for testing in the notebook, you can define as follows\n",
    "\n",
    "# define the training dataset\n",
    "sql <- \"\n",
    "SELECT \n",
    "    F.*, D.hasdiabetes\n",
    "FROM DEMO_ModelOps.PIMA_PATIENT_FEATURES F \n",
    "JOIN DEMO_ModelOps.PIMA_PATIENT_DIAGNOSES D\n",
    "ON F.patientid = D.patientid\n",
    "    WHERE D.patientid MOD 5 <> 0\n",
    "\";\n",
    "\n",
    "feature_metadata <- list(\n",
    "    # \"database\" = td_get_context()$default.database,\n",
    "    \"database\" = 'DEMO_ModelOps',\n",
    "    \"table\" = \"aoa_feature_metadata\"\n",
    ");\n",
    "\n",
    "hyperParameters <- list(\n",
    "    \"shrinkage\" = 0.01,\n",
    "    \"cv.folds\"=1,  # cv.folds value has been reduced to minimun to avoid perfomance issues in Jupyter (should be 5)\n",
    "    \"n.trees\"=3000\n",
    ");\n",
    "\n",
    "entityKey = \"PatientId\"\n",
    "\n",
    "targetNames <- list(\"HasDiabetes\");\n",
    "\n",
    "featureNames <- list(\"NumTimesPrg\", \"PlGlcConc\", \"BloodP\", \"SkinThick\", \"TwoHourSerIns\", \"BMI\", \"DiPedFunc\", \"Age\");\n",
    "\n",
    "data_conf <- list(\n",
    "    \"sql\" = sql,\n",
    "    \"featureNames\" = featureNames,\n",
    "    \"targetNames\" = targetNames\n",
    ")\n",
    "\n",
    "model_conf = list(\n",
    "    \"hyperParameters\" = hyperParameters,\n",
    "    \"outputPath\" = \"artifacts/\"\n",
    ")\n",
    "\n",
    "# Execute training\n",
    "train(\n",
    "    data_conf = data_conf,\n",
    "    model_conf = model_conf,\n",
    "    model_version = \"rgbm_v1\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f42d20f1",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Check the generated files\n",
    "res <- system(\"ls -lh artifacts\", intern=TRUE)\n",
    "print(paste(res, sep = \"\\n\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8d9d8b5",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Define Evaluation Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The evaluation function takes the following shape</p>\n",
    "\n",
    "```R\n",
    "evaluate <- function(data_conf, model_conf, ...) {\n",
    "    # Connect to Vantage\n",
    "    con <- aoa_create_context()\n",
    "    \n",
    "    # Load model\n",
    "    model <- readRDS(\"artifacts/input/model.rds\")\n",
    "    \n",
    "    # your evaluation logic here\n",
    "    \n",
    "    # Save metrics\n",
    "    write(jsonlite::toJSON(metrics, auto_unbox = TRUE, null = \"null\", keep_vec_names=TRUE), \"artifacts/output/metrics.json\")\n",
    "}\n",
    "```\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You can execute this from the CLI or directly within the notebook as shown.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f05a582",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/model_modules/evaluation.R\n",
    "LoadPackages <- function() {\n",
    "    library(\"methods\")\n",
    "    library(\"jsonlite\")\n",
    "    library(\"caret\")\n",
    "    library(\"gbm\")\n",
    "    library(\"DBI\")\n",
    "    library(\"dplyr\")\n",
    "    library(\"tdplyr\")\n",
    "}\n",
    "\n",
    "evaluate <- function(data_conf, model_conf, ...) {\n",
    "    model <- readRDS(paste(ifelse(model_conf$inputPath != \"\" && !is.null(model_conf$inputPath), model_conf$inputPath, \"artifacts/input/\"), \"model.rds\", sep=\"\"))\n",
    "    print(\"Evaluating model...\")\n",
    "\n",
    "    suppressPackageStartupMessages(LoadPackages())\n",
    "\n",
    "    # Connect to Vantage\n",
    "    con <- aoa_create_context()\n",
    "\n",
    "    table <- tbl(con, sql(data_conf$sql))\n",
    "\n",
    "    # Create dataframe from tibble, selecting the necessary columns and mutating integer64 to integers\n",
    "    data <- table %>% mutate(NumTimesPrg = as.integer(NumTimesPrg),\n",
    "                                PlGlcConc = as.integer(PlGlcConc),\n",
    "                                BloodP = as.integer(BloodP),\n",
    "                                SkinThick = as.integer(SkinThick),\n",
    "                                TwoHourSerIns = as.integer(TwoHourSerIns),\n",
    "                                HasDiabetes = as.integer(HasDiabetes)) %>% as.data.frame()\n",
    "\n",
    "    probs <- predict(model, data, na.action = na.pass, type = \"response\")\n",
    "    preds <- as.integer(ifelse(probs > 0.5, 1, 0))\n",
    "\n",
    "    cm <- confusionMatrix(table(preds, data$HasDiabetes))\n",
    "\n",
    "    png(paste(ifelse(model_conf$outputPath != \"\" && !is.null(model_conf$outputPath), model_conf$outputPath, \"artifacts/output/\"), \"confusion_matrix.png\", sep=\"\"), width = 860, height = 860)\n",
    "    fourfoldplot(cm$table)\n",
    "    dev.off()\n",
    "\n",
    "    preds$pred <- preds\n",
    "    metrics <- cm$overall\n",
    "\n",
    "    # Save metrics\n",
    "    write(jsonlite::toJSON(metrics, auto_unbox = TRUE, null = \"null\", keep_vec_names=TRUE), paste(ifelse(model_conf$outputPath != \"\" && !is.null(model_conf$outputPath), model_conf$outputPath, \"artifacts/output/\"), \"metrics.json\", sep=\"\"))\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e81f5f70",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Define the ModelContext to test with. The ModelContext (dataconf and model_conf) is created and managed automatically by ModelOps \n",
    "# when it executes your code via CLI / UI. However, for testing in the notebook, you can define as follows\n",
    "\n",
    "# define the training dataset\n",
    "sql <- \"\n",
    "SELECT \n",
    "    F.*, D.hasdiabetes \n",
    "FROM DEMO_ModelOps.PIMA_PATIENT_FEATURES F \n",
    "JOIN DEMO_ModelOps.PIMA_PATIENT_DIAGNOSES D\n",
    "ON F.patientid = D.patientid\n",
    "    WHERE D.patientid MOD 5 = 0\n",
    "\";\n",
    "\n",
    "data_conf <- list(\n",
    "    \"sql\" = sql,\n",
    "    \"featureNames\" = featureNames,\n",
    "    \"targetNames\" = targetNames\n",
    ")\n",
    "\n",
    "model_conf <- list(\n",
    "    \"outputPath\" = \"artifacts/\",\n",
    "    \"inputPath\" = \"artifacts/\"\n",
    ")\n",
    "\n",
    "# Execute evaluation\n",
    "evaluate(\n",
    "    data_conf = data_conf,\n",
    "    model_conf = model_conf,\n",
    "    model_version = \"rgbm_v1\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e30749a7",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Check the generated files\n",
    "res <- system(\"ls -lh artifacts\", intern=TRUE)\n",
    "print(paste(res, sep = \"\\n\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4007896",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Define Scoring Function</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The scoring function takes the following shape</p>\n",
    "\n",
    "```R\n",
    "evaluate <- function(data_conf, model_conf, ...) {\n",
    "    # Connect to Vantage\n",
    "    con <- aoa_create_context()\n",
    "    \n",
    "    # Load model\n",
    "    model <- readRDS(\"artifacts/input/model.rds\")\n",
    "    \n",
    "    # your scoring logic here\n",
    "    \n",
    "    # your scoring result saving logic here\n",
    "}\n",
    "```\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You can execute this from the CLI or directly within the notebook as shown.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ceb0d975",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/model_modules/scoring.R\n",
    "library(methods)\n",
    "library(gbm)\n",
    "library(jsonlite)\n",
    "library(caret)\n",
    "\n",
    "LoadBatchScoringPackages <- function() {\n",
    "    library(\"gbm\")\n",
    "    library(\"DBI\")\n",
    "    library(\"dplyr\")\n",
    "    library(\"tdplyr\")\n",
    "}\n",
    "\n",
    "score.batch <- function(data_conf, model_conf, model_version, job_id, ...) {\n",
    "    model <- initialise_model()\n",
    "    print(\"Batch scoring model...\")\n",
    "\n",
    "    suppressPackageStartupMessages(LoadBatchScoringPackages())\n",
    "\n",
    "    # Connect to Teradata Vantage\n",
    "    con <- aoa_create_context()\n",
    "\n",
    "    table <- tbl(con, sql(data_conf$sql))\n",
    "\n",
    "    # Create dataframe from tibble, selecting the necessary columns and mutating integer64 to integers\n",
    "    data <- table %>% mutate(PatientId = as.integer(PatientId),\n",
    "                             NumTimesPrg = as.integer(NumTimesPrg),\n",
    "                             PlGlcConc = as.integer(PlGlcConc),\n",
    "                             BloodP = as.integer(BloodP),\n",
    "                             SkinThick = as.integer(SkinThick),\n",
    "                             TwoHourSerIns = as.integer(TwoHourSerIns)) %>% as.data.frame()\n",
    "\n",
    "    # The model object will be obtain from the environment as it has already been initialised using 'initialise_model'\n",
    "    probs <- predict(model, data, na.action = na.pass, type = \"response\")\n",
    "    score <- as.integer(ifelse(probs > 0.5, 1, 0))\n",
    "    print(\"Finished batch scoring model...\")\n",
    "\n",
    "    # create result dataframe and store in Teradata Vantage\n",
    "    pred_df <- as.data.frame(unlist(score))\n",
    "    colnames(pred_df) <- c(\"HasDiabetes\")\n",
    "    pred_df$PatientId <- data$PatientId\n",
    "    pred_df$job_id <- job_id\n",
    "\n",
    "    # tdplyr doesn't match column names on append.. and so to match / use same table schema as for byom predict\n",
    "    # example (see README.md), we must add empty json_report column and change column order manually (v17.0.0.4)\n",
    "    # CREATE MULTISET TABLE pima_patient_predictions\n",
    "    # (\n",
    "    #     job_id VARCHAR(255), -- comes from airflow on job execution\n",
    "    #     PatientId BIGINT,    -- entity key as it is in the source data\n",
    "    #     HasDiabetes BIGINT,   -- if model automatically extracts target\n",
    "    #     json_report CLOB(1048544000) CHARACTER SET UNICODE  -- output of\n",
    "    # )\n",
    "    # PRIMARY INDEX ( job_id );\n",
    "    pred_df$json_report <- \"\"\n",
    "    pred_df <- pred_df[, c(\"job_id\", \"PatientId\", \"HasDiabetes\", \"json_report\")]\n",
    "\n",
    "    copy_to(con, pred_df,\n",
    "            name=dbplyr::in_schema(data_conf$predictions$database, data_conf$predictions$table),\n",
    "            types = c(\"varchar(255)\", \"bigint\", \"bigint\", \"clob\"),\n",
    "            append=TRUE)\n",
    "    print(\"Saved batch predictions...\")\n",
    "}\n",
    "\n",
    "initialise_model <- function() {\n",
    "    print(\"Loading model...\")\n",
    "    model <- readRDS(paste(ifelse(model_conf$inputPath != \"\" && !is.null(model_conf$inputPath), model_conf$inputPath, \"artifacts/input/\"), \"model.rds\", sep=\"\"))\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aba2a5c7",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Define the ModelContext to test with. The ModelContext (dataconf and model_conf) is created and managed automatically by ModelOps \n",
    "# when it executes your code via CLI / UI. However, for testing in the notebook, you can define as follows\n",
    "\n",
    "# define the scoring dataset\n",
    "sql <- \"\n",
    "SELECT \n",
    "    F.*\n",
    "FROM DEMO_ModelOps.PIMA_PATIENT_FEATURES F \n",
    "    WHERE F.patientid MOD 5 = 0\n",
    "\";\n",
    "\n",
    "# where to store predictions\n",
    "predictions <- list(\n",
    "    \"database\" = td_get_context()$default.database,\n",
    "    \"table\" = \"pima_patient_predictions_tmp\"\n",
    ")\n",
    "\n",
    "data_conf <- list(\n",
    "    \"sql\" = sql,\n",
    "    \"featureNames\" = featureNames,\n",
    "    \"targetNames\" = targetNames,\n",
    "    \"predictions\" = predictions\n",
    ")\n",
    "\n",
    "model_conf <- list(\n",
    "    \"inputPath\" = \"artifacts/\"\n",
    ")\n",
    "\n",
    "job_id <- uuid::UUIDgenerate(1)\n",
    "\n",
    "# Execute batch scoring\n",
    "score.batch(\n",
    "    data_conf = data_conf,\n",
    "    model_conf = model_conf,\n",
    "    model_version = \"rgbm_v1\",\n",
    "    job_id = job_id\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "592e495b",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Using tibble\n",
    "tbl(con, sql(sprintf(\"SELECT * FROM %s.pima_patient_predictions_tmp WHERE job_id = '%s'\", td_get_context()$default.database, job_id)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2d05e8b",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Using DBI\n",
    "DBI::dbGetQuery(con, sprintf(\"SELECT * FROM %s.pima_patient_predictions_tmp WHERE job_id = '%s'\", td_get_context()$default.database, job_id))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "063d3d0f",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Clean up\n",
    "\n",
    "system('rm -f artifacts/*')\n",
    "tryCatch(DBI::dbSendQuery(con, sprintf(\"DROP TABLE %s.rgbm_v1\", td_get_context()$default.database)), error=function(cond) return(NA))\n",
    "tryCatch(DBI::dbSendQuery(con, sprintf(\"DROP TABLE %s.pima_patient_predictions_tmp\", td_get_context()$default.database)), error=function(cond) return(NA))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae3a1bec-08e0-4170-8d39-70e8bf5e3c30",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Define Model Metadata</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now let's create the configuration files.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Requirements file with the dependencies and versions:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e823d152",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/model_modules/requirements.R\n",
    "message('Installing packages')\n",
    "if(!require('gbm')){install.packages('gbm')}\n",
    "if(!require('devtools')){install.packages('devtools')}\n",
    "if(!require('caret')){install.packages('caret')}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b07c1f53",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The hyper parameter configuration (default values):</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6bb623c7",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/config.json\n",
    "{\n",
    "    \"hyperParameters\": {\n",
    "        \"shrinkage\": 0.01,\n",
    "        \"cv.folds\": 5,\n",
    "        \"n.trees\": 3000\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "972362b7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The model configuration:</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c1170e30",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "# Save as ~/modelops-demo-models/model_definitions/pima_r_gbm/model.json\n",
    "{\n",
    "    \"id\": \"d0d58b07-15f1-4421-8e56-3f30cc7c679c\",\n",
    "    \"name\": \"R PIMA GBM\",\n",
    "    \"description\": \"R PIMA GBM for Diabetes Prediction\",\n",
    "    \"language\": \"R\",\n",
    "    \"automation\": {\n",
    "        \"training\": {\n",
    "            \"resources\": {\n",
    "                \"cpu\": \"1\",\n",
    "                \"memory\": \"1Gi\"\n",
    "            }\n",
    "        },\n",
    "        \"evaluation\": {\n",
    "            \"resources\": {\n",
    "                \"cpu\": \"1\",\n",
    "                \"memory\": \"1Gi\"\n",
    "            }\n",
    "        },\n",
    "        \"deployment\": {\n",
    "            \"resources\": {\n",
    "                \"cpu\": \"1\",\n",
    "                \"memory\": \"1Gi\"\n",
    "            }\n",
    "        }\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed35e360",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>7. Commit and push changes</b>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Run the command below to commit and push changes to our forked repository, so ModelOps can fetch the changes to the model.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a0c1ea0",
   "metadata": {
    "vscode": {
     "languageId": "r"
    }
   },
   "outputs": [],
   "source": [
    "res <- system(sprintf('cd %s/../.. && git add . && git commit -m \"Added R PIMA GBM demo model 🎢\" && git push', model_local_path), intern=TRUE)\n",
    "print(paste(res, sep = \"\\n\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60b44d10-a9ee-4609-be4e-ea90e96d4653",
   "metadata": {},
   "source": [
    "[<< Back to Git PIMA Python XGBoost](./08_ModelOps_GIT_PIMA_Python_H2OAutoML.ipynb) | [Continue to Git PIMA Python In database XGBoost >>](./11_ModelOps_GIT_PIMA_Python_indb_XGboost.ipynb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "57ecc749",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.1.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}