jupyter-samples/campaign-integratedml-jdbc.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Train an IntegratedML model on Marketing Campaign Dataset\n",
    "## Use JDBC to connect to InterSystems IRIS database\n",
    "This Notebook demonstrates:\n",
    "- Using the JayDeBeApi Python library to connect to InterSystems IRIS\n",
    "- Creating views to segment data into training and test sets\n",
    "- Defining and training an IntegratedML model to predict marketing campaign responses\n",
    "- Comparing the resulting model's predictions to data in the test set (that the model was not trained on)\n",
    "- Using the IntegratedML \"VALIDATE MODEL\" command to calculate accuracy metrics on the test set data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Set environment variables, if necessary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Get jdbc connection and cursor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import jaydebeapi\n",
    "url = \"jdbc:IRIS://irisimlsvr:1972/USER\"\n",
    "driver = \"com.intersystems.jdbc.IRISDriver\"\n",
    "user = \"SUPERUSER\"\n",
    "password = \"SYS\"\n",
    "#libx = \"C:/InterSystems/IRIS20194/dev/java/lib/JDK18\"\n",
    "#jarfile = \"C:/InterSystems/IRIS20194/dev/java/lib/JDK18/intersystems-jdbc-3.0.0.jar\"\n",
    "jarfile = \"./intersystems-jdbc-3.1.0.jar\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = jaydebeapi.connect(driver, url, [user, password], jarfile)\n",
    "curs = conn.cursor()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. specify the source data table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataTable = 'Marketing.Campaign'\n",
    "trainingTable = \"Marketing.CampaignTrainingSmall\"\n",
    "predictTable = \"Marketing.CampaignPredictSmall\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Execute a query and display results in Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>AGE</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <th>OCCUPATION</th>\n",
       "      <th>RELATIONSHIP</th>\n",
       "      <th>RACE</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>PROSPECT_ID</th>\n",
       "      <th>EMPLOYMENT</th>\n",
       "      <th>YEARS_EDUC</th>\n",
       "      <th>COUNTRY_OF_BIRTH</th>\n",
       "      <th>INV_EARNINGS</th>\n",
       "      <th>INV_LOSSES</th>\n",
       "      <th>WORKWEEK_LENGTH</th>\n",
       "      <th>RESPONSE</th>\n",
       "      <th>AMOUNT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001107</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>123.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>LOGISTICSTRANSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001140</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>97.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001143</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>135.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>43</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>CLEANINGHANDLING</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001187</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>93.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>LOGISTICSTRANSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001412</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>143.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SERVICEVARIOUS</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001429</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>232.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001443</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>103.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>29</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>ADMINOFFICE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001484</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>123.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>21</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001526</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>230.07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>36</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001634</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>48.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>52</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SPECIALISTPROFF</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001649</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>58.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>60</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001660</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>106.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>28</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001661</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>211.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>61</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001719</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>104.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>34</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001794</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>184.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>62</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001874</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>66.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>56</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001913</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>60.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>34</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001942</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>77.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>29</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SUPPORTTECHNIC</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77001977</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>122.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>33</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>MALE</td>\n",
       "      <td>77002003</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>9</td>\n",
       "      <td>USA</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "      <td>257.23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AGE   EDUCATION MARITAL_STATUS        OCCUPATION RELATIONSHIP   RACE  \\\n",
       "0    50  HIGHSCHOOL  MARRIEDCIVSPO       REPAIRCRAFT      HUSBAND  WHITE   \n",
       "1    49  HIGHSCHOOL  MARRIEDCIVSPO   LOGISTICSTRANSP      HUSBAND  WHITE   \n",
       "2    37  HIGHSCHOOL  MARRIEDCIVSPO       REPAIRCRAFT      HUSBAND  WHITE   \n",
       "3    43  HIGHSCHOOL  MARRIEDCIVSPO  CLEANINGHANDLING      HUSBAND  WHITE   \n",
       "4    54  HIGHSCHOOL  MARRIEDCIVSPO   LOGISTICSTRANSP      HUSBAND  WHITE   \n",
       "5    27  HIGHSCHOOL  MARRIEDCIVSPO    SERVICEVARIOUS      HUSBAND  WHITE   \n",
       "6    29  HIGHSCHOOL  MARRIEDCIVSPO       REPAIRCRAFT      HUSBAND  WHITE   \n",
       "7    29  HIGHSCHOOL  MARRIEDCIVSPO       ADMINOFFICE      HUSBAND  WHITE   \n",
       "8    21  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "9    36  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "10   52  HIGHSCHOOL  MARRIEDCIVSPO   SPECIALISTPROFF      HUSBAND  WHITE   \n",
       "11   60  HIGHSCHOOL  MARRIEDCIVSPO       REPAIRCRAFT      HUSBAND  WHITE   \n",
       "12   28  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "13   61  HIGHSCHOOL  MARRIEDCIVSPO       REPAIRCRAFT      HUSBAND  WHITE   \n",
       "14   34  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "15   62  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "16   56  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "17   34  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "18   29  HIGHSCHOOL  MARRIEDCIVSPO    SUPPORTTECHNIC      HUSBAND  WHITE   \n",
       "19   33  HIGHSCHOOL  MARRIEDCIVSPO   MACHINEOPERINSP      HUSBAND  WHITE   \n",
       "\n",
       "   GENDER  PROSPECT_ID EMPLOYMENT  YEARS_EDUC COUNTRY_OF_BIRTH  INV_EARNINGS  \\\n",
       "0    MALE     77001107   BUSINESS           9              USA             0   \n",
       "1    MALE     77001140   BUSINESS           9              USA             0   \n",
       "2    MALE     77001143   BUSINESS           9              USA             0   \n",
       "3    MALE     77001187   BUSINESS           9              USA             0   \n",
       "4    MALE     77001412   BUSINESS           9              USA             0   \n",
       "5    MALE     77001429   BUSINESS           9              USA             0   \n",
       "6    MALE     77001443   BUSINESS           9              USA             0   \n",
       "7    MALE     77001484   BUSINESS           9              USA             0   \n",
       "8    MALE     77001526   BUSINESS           9              USA             0   \n",
       "9    MALE     77001634   BUSINESS           9              USA             0   \n",
       "10   MALE     77001649   BUSINESS           9              USA             0   \n",
       "11   MALE     77001660   BUSINESS           9              USA             0   \n",
       "12   MALE     77001661   BUSINESS           9              USA             0   \n",
       "13   MALE     77001719   BUSINESS           9              USA             0   \n",
       "14   MALE     77001794   BUSINESS           9              USA             0   \n",
       "15   MALE     77001874   BUSINESS           9              USA             0   \n",
       "16   MALE     77001913   BUSINESS           9              USA             0   \n",
       "17   MALE     77001942   BUSINESS           9              USA             0   \n",
       "18   MALE     77001977   BUSINESS           9              USA             0   \n",
       "19   MALE     77002003   BUSINESS           9              USA             0   \n",
       "\n",
       "    INV_LOSSES  WORKWEEK_LENGTH  RESPONSE  AMOUNT  \n",
       "0            0               40         0  123.90  \n",
       "1            0               40         0   97.91  \n",
       "2            0               40         0  135.65  \n",
       "3            0               40         0   93.23  \n",
       "4            0               40         0  143.72  \n",
       "5            0               40         0  232.20  \n",
       "6            0               40         0  103.46  \n",
       "7            0               40         0  123.53  \n",
       "8            0               40         0  230.07  \n",
       "9            0               40         0   48.95  \n",
       "10           0               40         0   58.78  \n",
       "11           0               40         0  106.29  \n",
       "12           0               40         0  211.26  \n",
       "13           0               40         0  104.34  \n",
       "14           0               40         0  184.73  \n",
       "15           0               40         0   66.32  \n",
       "16           0               40         0   60.13  \n",
       "17           0               40         0   77.02  \n",
       "18           0               40         0  122.43  \n",
       "19           0               40         0  257.23  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import display\n",
    "\n",
    "df = pd.read_sql(\"select TOP 20 * from %s\" % dataTable, conn)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Aggregate_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>48842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Aggregate_1\n",
       "0        48842"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Show number rows\n",
    "df1 = pd.read_sql(\"SELECT COUNT(*) FROM %s\" % dataTable, conn)\n",
    "display(df1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Cleaning before retrying"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Before executing the notebook again, drop model, tables, views,... previously created\n",
    "#curs.execute(\"DROP VIEW %s\" % trainingTable)\n",
    "#curs.execute(\"DROP VIEW %s\" % predictTable)\n",
    "#curs.execute(\"DROP MODEL CampaignModel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Make some views to split training and testing datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Small training set view\n",
    "curs.execute(\"CREATE VIEW %s AS SELECT * FROM %s WHERE ID<3001\" % (trainingTable,dataTable))\n",
    "# Small prediction set\n",
    "curs.execute(\"CREATE VIEW %s AS SELECT * FROM %s WHERE ID>47000\" % (predictTable,dataTable))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 6. Create and Train an IntegratedML Model using default settings\n",
    "IntegratedML only needs a model name, the name of the column that is the target column to predict, and a table (or SELECT query to specify input columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"CREATE MODEL CampaignModel PREDICTING (RESPONSE) FROM %s\" % trainingTable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the model is defined, you can TRAIN it, which invokes the AutoML machine learning procedure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "curs.execute(\"TRAIN MODEL CampaignModel\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once that finishes, you can see some information about the model in the \"ML_TRAINED_MODELS\" table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>MODEL_NAME</th>\n",
       "      <th>TRAINED_MODEL_NAME</th>\n",
       "      <th>PROVIDER</th>\n",
       "      <th>TRAINED_TIMESTAMP</th>\n",
       "      <th>MODEL_TYPE</th>\n",
       "      <th>MODEL_INFO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CampaignModel</td>\n",
       "      <td>CampaignModel_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 08:01:09.859000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:TensorFlow Neural Network, Package:T...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bc</td>\n",
       "      <td>bc_t1</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2022-04-22 07:54:38.592000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:TensorFlow Neural Network, Package:T...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      MODEL_NAME TRAINED_MODEL_NAME PROVIDER           TRAINED_TIMESTAMP  \\\n",
       "0  CampaignModel   CampaignModel_t1   AutoML  2022-04-22 08:01:09.859000   \n",
       "1             bc              bc_t1   AutoML  2022-04-22 07:54:38.592000   \n",
       "\n",
       "       MODEL_TYPE                                         MODEL_INFO  \n",
       "0  classification  ModelType:TensorFlow Neural Network, Package:T...  \n",
       "1  classification  ModelType:TensorFlow Neural Network, Package:T...  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df3 = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_TRAINED_MODELS\", conn)\n",
    "display(df3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 7. Compare model output to data it has not seen yet\n",
    "Now you can use SQL to SELECT data from another table, run the IntegratedML model on this new data, and see how well the predictions match the data!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>PredictedResponse</th>\n",
       "      <th>ActualResponse</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1837</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1838</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1839</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1840</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1841</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1842 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      PredictedResponse  ActualResponse\n",
       "0                     0               0\n",
       "1                     0               0\n",
       "2                     0               1\n",
       "3                     0               0\n",
       "4                     0               1\n",
       "...                 ...             ...\n",
       "1837                  0               0\n",
       "1838                  0               0\n",
       "1839                  0               0\n",
       "1840                  0               0\n",
       "1841                  0               0\n",
       "\n",
       "[1842 rows x 2 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df4 = pd.read_sql(\"SELECT PREDICT(CampaignModel) AS PredictedResponse, \\\n",
    "                  response AS ActualResponse FROM %s\" % predictTable, conn)\n",
    "display(df4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 8. VALIDATE MODEL command calculates accuracy metrics\n",
    "You can certainly take that output above and calculate the accuracy using a standard formula, but IntegratedML has a built-in function to do that!\n",
    "\n",
    "Each time you run the command \"VALIDATE MODEL...\" it generates a set of metrics calculated on the data passed into the query. Since this table can be a bit difficult to read in its raw form we use a simple \"pivot\" call to arrange the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>METRIC_NAME</th>\n",
       "      <th>Accuracy</th>\n",
       "      <th>F-Measure</th>\n",
       "      <th>Precision</th>\n",
       "      <th>Recall</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>VALIDATION_RUN_NAME</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CampaignModel_t1_v1</th>\n",
       "      <td>0.6</td>\n",
       "      <td>0.2</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "METRIC_NAME          Accuracy  F-Measure  Precision  Recall\n",
       "VALIDATION_RUN_NAME                                        \n",
       "CampaignModel_t1_v1       0.6        0.2       0.81    0.11"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "curs.execute(\"VALIDATE MODEL CampaignModel FROM %s\" % predictTable)\n",
    "df5 = pd.read_sql(\"SELECT * FROM INFORMATION_SCHEMA.ML_VALIDATION_METRICS\", conn)\n",
    "df6 = df5.pivot(index='VALIDATION_RUN_NAME', columns='METRIC_NAME', values='METRIC_VALUE')\n",
    "display(df6)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}