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": null,

   "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": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import jaydebeapi\n",
    "url = \"jdbc:IRIS://irisimlsvr:51773/USER\" #\"jdbc:IRIS://172.17.0.1:8091/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": 5,
   "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": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataTable = 'SQLUser.Campaign'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Execute a query and display results in Pandas DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>AMOUNT</th>\n",
       "      <th>COUNTRY_OF_BIRTH</th>\n",
       "      <th>EDUCATION</th>\n",
       "      <th>EMPLOYMENT</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>INV_EARNINGS</th>\n",
       "      <th>INV_LOSSES</th>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <th>OCCUPATION</th>\n",
       "      <th>PROSPECT_ID</th>\n",
       "      <th>RACE</th>\n",
       "      <th>RELATIONSHIP</th>\n",
       "      <th>RESPONSE</th>\n",
       "      <th>WORKWEEK_LENGTH</th>\n",
       "      <th>YEARS_EDUC</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>50</td>\n",
       "      <td>123.90</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>77001107</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49</td>\n",
       "      <td>97.91</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>LOGISTICSTRANSP</td>\n",
       "      <td>77001140</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>37</td>\n",
       "      <td>135.65</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>77001143</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>43</td>\n",
       "      <td>93.23</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>CLEANINGHANDLING</td>\n",
       "      <td>77001187</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>54</td>\n",
       "      <td>143.72</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>LOGISTICSTRANSP</td>\n",
       "      <td>77001412</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>27</td>\n",
       "      <td>232.20</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SERVICEVARIOUS</td>\n",
       "      <td>77001429</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>29</td>\n",
       "      <td>103.46</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>77001443</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>29</td>\n",
       "      <td>123.53</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>ADMINOFFICE</td>\n",
       "      <td>77001484</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>21</td>\n",
       "      <td>230.07</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001526</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>36</td>\n",
       "      <td>48.95</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001634</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>52</td>\n",
       "      <td>58.78</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SPECIALISTPROFF</td>\n",
       "      <td>77001649</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>60</td>\n",
       "      <td>106.29</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>77001660</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>28</td>\n",
       "      <td>211.26</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001661</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>61</td>\n",
       "      <td>104.34</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>REPAIRCRAFT</td>\n",
       "      <td>77001719</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>34</td>\n",
       "      <td>184.73</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001794</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>62</td>\n",
       "      <td>66.32</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001874</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>56</td>\n",
       "      <td>60.13</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001913</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>34</td>\n",
       "      <td>77.02</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77001942</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>29</td>\n",
       "      <td>122.43</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>SUPPORTTECHNIC</td>\n",
       "      <td>77001977</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>33</td>\n",
       "      <td>257.23</td>\n",
       "      <td>USA</td>\n",
       "      <td>HIGHSCHOOL</td>\n",
       "      <td>BUSINESS</td>\n",
       "      <td>MALE</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>MARRIEDCIVSPO</td>\n",
       "      <td>MACHINEOPERINSP</td>\n",
       "      <td>77002003</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>HUSBAND</td>\n",
       "      <td>0</td>\n",
       "      <td>40</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    AGE  AMOUNT COUNTRY_OF_BIRTH   EDUCATION EMPLOYMENT GENDER  INV_EARNINGS  \\\n",
       "0    50  123.90              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "1    49   97.91              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "2    37  135.65              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "3    43   93.23              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "4    54  143.72              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "5    27  232.20              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "6    29  103.46              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "7    29  123.53              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "8    21  230.07              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "9    36   48.95              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "10   52   58.78              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "11   60  106.29              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "12   28  211.26              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "13   61  104.34              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "14   34  184.73              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "15   62   66.32              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "16   56   60.13              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "17   34   77.02              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "18   29  122.43              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "19   33  257.23              USA  HIGHSCHOOL   BUSINESS   MALE             0   \n",
       "\n",
       "    INV_LOSSES MARITAL_STATUS        OCCUPATION  PROSPECT_ID   RACE  \\\n",
       "0            0  MARRIEDCIVSPO       REPAIRCRAFT     77001107  WHITE   \n",
       "1            0  MARRIEDCIVSPO   LOGISTICSTRANSP     77001140  WHITE   \n",
       "2            0  MARRIEDCIVSPO       REPAIRCRAFT     77001143  WHITE   \n",
       "3            0  MARRIEDCIVSPO  CLEANINGHANDLING     77001187  WHITE   \n",
       "4            0  MARRIEDCIVSPO   LOGISTICSTRANSP     77001412  WHITE   \n",
       "5            0  MARRIEDCIVSPO    SERVICEVARIOUS     77001429  WHITE   \n",
       "6            0  MARRIEDCIVSPO       REPAIRCRAFT     77001443  WHITE   \n",
       "7            0  MARRIEDCIVSPO       ADMINOFFICE     77001484  WHITE   \n",
       "8            0  MARRIEDCIVSPO   MACHINEOPERINSP     77001526  WHITE   \n",
       "9            0  MARRIEDCIVSPO   MACHINEOPERINSP     77001634  WHITE   \n",
       "10           0  MARRIEDCIVSPO   SPECIALISTPROFF     77001649  WHITE   \n",
       "11           0  MARRIEDCIVSPO       REPAIRCRAFT     77001660  WHITE   \n",
       "12           0  MARRIEDCIVSPO   MACHINEOPERINSP     77001661  WHITE   \n",
       "13           0  MARRIEDCIVSPO       REPAIRCRAFT     77001719  WHITE   \n",
       "14           0  MARRIEDCIVSPO   MACHINEOPERINSP     77001794  WHITE   \n",
       "15           0  MARRIEDCIVSPO   MACHINEOPERINSP     77001874  WHITE   \n",
       "16           0  MARRIEDCIVSPO   MACHINEOPERINSP     77001913  WHITE   \n",
       "17           0  MARRIEDCIVSPO   MACHINEOPERINSP     77001942  WHITE   \n",
       "18           0  MARRIEDCIVSPO    SUPPORTTECHNIC     77001977  WHITE   \n",
       "19           0  MARRIEDCIVSPO   MACHINEOPERINSP     77002003  WHITE   \n",
       "\n",
       "   RELATIONSHIP  RESPONSE  WORKWEEK_LENGTH  YEARS_EDUC  \n",
       "0       HUSBAND         0               40           9  \n",
       "1       HUSBAND         0               40           9  \n",
       "2       HUSBAND         0               40           9  \n",
       "3       HUSBAND         0               40           9  \n",
       "4       HUSBAND         0               40           9  \n",
       "5       HUSBAND         0               40           9  \n",
       "6       HUSBAND         0               40           9  \n",
       "7       HUSBAND         0               40           9  \n",
       "8       HUSBAND         0               40           9  \n",
       "9       HUSBAND         0               40           9  \n",
       "10      HUSBAND         0               40           9  \n",
       "11      HUSBAND         0               40           9  \n",
       "12      HUSBAND         0               40           9  \n",
       "13      HUSBAND         0               40           9  \n",
       "14      HUSBAND         0               40           9  \n",
       "15      HUSBAND         0               40           9  \n",
       "16      HUSBAND         0               40           9  \n",
       "17      HUSBAND         0               40           9  \n",
       "18      HUSBAND         0               40           9  \n",
       "19      HUSBAND         0               40           9  "
      ]
     },
     "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": 8,
   "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": [
    "### 5. Make some views to split training and testing datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.Update\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.processError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.getServerError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mException\u001b[0m: Java Exception",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m                     Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m: java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-9-7bc24195bac9>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# Small training set view\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mcurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE VIEW SQLUser.CampaignTrainingSmall AS SELECT * FROM SQLUser.Campaign WHERE ID<3001\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      3\u001b[0m \u001b[0;31m# Small prediction set\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mcurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE VIEW SQLUser.CampaignPredictSmall AS SELECT * FROM SQLUser.Campaign WHERE ID>47000\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    534\u001b[0m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 536\u001b[0;31m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    537\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mis_rs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    538\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetResultSet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36m_handle_sql_exception_jpype\u001b[0;34m()\u001b[0m\n\u001b[1;32m    163\u001b[0m         \u001b[0mexc_type\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInterfaceError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 165\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    166\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    167\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_jdbc_connect_jpype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdriver_args\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjars\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlibs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb)\u001b[0m\n\u001b[1;32m     55\u001b[0m             \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     56\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 57\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     58\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    532\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_stmt_parms\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    536\u001b[0m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: java.sql.SQLException: [SQLCODE: <-201>:<Table or view name not unique>]\r\n[Location: <ServerLoop>]"
     ]
    }
   ],
   "source": [
    "# Small training set view\n",
    "curs.execute(\"CREATE VIEW SQLUser.CampaignTrainingSmall AS SELECT * FROM SQLUser.Campaign WHERE ID<3001\")\n",
    "# Small prediction set\n",
    "curs.execute(\"CREATE VIEW SQLUser.CampaignPredictSmall AS SELECT * FROM SQLUser.Campaign WHERE ID>47000\")"
   ]
  },
  {
   "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": 10,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "java.sql.SQLException: [SQLCODE: <-180>:<Model name not unique>]\r\n[Location: <ServerLoop>]\r\n[%msg: <Model name 'CampaignModel' is not unique>]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mException\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.execute\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISPreparedStatement.Update\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.InStream.readMessage\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.processError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/_jpype.cpython-36m-x86_64-linux-gnu.so\u001b[0m in \u001b[0;36mcom.intersystems.jdbc.IRISConnection.getServerError\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mException\u001b[0m: Java Exception",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m                     Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mjava.sql.SQLException\u001b[0m: java.sql.SQLException: [SQLCODE: <-180>:<Model name not unique>]\r\n[Location: <ServerLoop>]\r\n[%msg: <Model name 'CampaignModel' is not unique>]",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-10-2ab15a30635b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcurs\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"CREATE MODEL CampaignModel PREDICTING (RESPONSE) FROM CampaignTrainingSmall\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    534\u001b[0m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 536\u001b[0;31m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    537\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mis_rs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    538\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mgetResultSet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36m_handle_sql_exception_jpype\u001b[0;34m()\u001b[0m\n\u001b[1;32m    163\u001b[0m         \u001b[0mexc_type\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mInterfaceError\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    164\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 165\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    166\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    167\u001b[0m \u001b[0;32mdef\u001b[0m \u001b[0m_jdbc_connect_jpype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mjclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0murl\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdriver_args\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjars\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlibs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb)\u001b[0m\n\u001b[1;32m     55\u001b[0m             \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtp\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     56\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 57\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     58\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/lib/python3.6/dist-packages/jaydebeapi/__init__.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, operation, parameters)\u001b[0m\n\u001b[1;32m    532\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_set_stmt_parms\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    533\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 534\u001b[0;31m             \u001b[0mis_rs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_prep\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    535\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    536\u001b[0m             \u001b[0m_handle_sql_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: java.sql.SQLException: [SQLCODE: <-180>:<Model name not unique>]\r\n[Location: <ServerLoop>]\r\n[%msg: <Model name 'CampaignModel' is not unique>]"
     ]
    }
   ],
   "source": [
    "curs.execute(\"CREATE MODEL CampaignModel PREDICTING (RESPONSE) FROM CampaignTrainingSmall\")"
   ]
  },
  {
   "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": 11,
   "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": 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>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>CampaignModel2</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2020-06-29 13:44:07.324000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Logistic Regression, Package:sklearn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CampaignModel</td>\n",
       "      <td>CampaignModel4</td>\n",
       "      <td>AutoML</td>\n",
       "      <td>2020-06-29 15:25:48.832000</td>\n",
       "      <td>classification</td>\n",
       "      <td>ModelType:Logistic Regression, Package:sklearn...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      MODEL_NAME TRAINED_MODEL_NAME PROVIDER           TRAINED_TIMESTAMP  \\\n",
       "0  CampaignModel     CampaignModel2   AutoML  2020-06-29 13:44:07.324000   \n",
       "1  CampaignModel     CampaignModel4   AutoML  2020-06-29 15:25:48.832000   \n",
       "\n",
       "       MODEL_TYPE                                         MODEL_INFO  \n",
       "0  classification  ModelType:Logistic Regression, Package:sklearn...  \n",
       "1  classification  ModelType:Logistic Regression, Package:sklearn...  "
      ]
     },
     "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": 17,
   "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>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</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>1</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                    1               0\n",
       "1                    1               0\n",
       "2                    1               1\n",
       "3                    1               0\n",
       "4                    1               1\n",
       "...                ...             ...\n",
       "1837                 1               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 CampaignPredictSmall\", 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": 18,
   "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>CampaignModel21</th>\n",
       "      <td>0.75</td>\n",
       "      <td>0.73</td>\n",
       "      <td>0.68</td>\n",
       "      <td>0.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "METRIC_NAME          Accuracy  F-Measure  Precision  Recall\n",
       "VALIDATION_RUN_NAME                                        \n",
       "CampaignModel21          0.75       0.73       0.68     0.8"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "curs.execute(\"VALIDATE MODEL CampaignModel FROM SQLUser.CampaignPredictSmall\")\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
}