{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Inference for CIFAR-10 dataset using predict BYOM\n",
    "The predict BYOM function allows you to do inference using models that have not been trained with MADlib, but rather imported or created elsewhere. It was added in MADlib 1.17.\n",
    "\n",
    "In this workbook we train a model in Python using\n",
    "https://keras.io/examples/cifar10_cnn/\n",
    "and run inference on the validation set.\n",
    "\n",
    "## Table of contents\n",
    "\n",
    "<a href=\"#setup\">1. Setup</a>\n",
    "\n",
    "<a href=\"#train_model\">2. Train model in Python</a>\n",
    "\n",
    "<a href=\"#load_model\">3. Load model into table</a>\n",
    "\n",
    "<a href=\"#load_images\">4. Get validation data set and load into table</a>\n",
    "\n",
    "<a href=\"#inference\">5. Inference</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"setup\"></a>\n",
    "# 1. Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Greenplum Database 5.x on GCP - via tunnel\n",
    "%sql postgresql://gpadmin@localhost:8000/madlib\n",
    "        \n",
    "# PostgreSQL local\n",
    "#%sql postgresql://fmcquillan@localhost:5432/madlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>version</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>MADlib version: 1.18.0-dev, git revision: rel/v1.17.0-89-g9d9f756, cmake configuration time: Thu Mar  4 23:11:53 UTC 2021, build type: release, build system: Linux-3.10.0-1160.11.1.el7.x86_64, C compiler: gcc 4.8.5, C++ compiler: g++ 4.8.5</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(u'MADlib version: 1.18.0-dev, git revision: rel/v1.17.0-89-g9d9f756, cmake configuration time: Thu Mar  4 23:11:53 UTC 2021, build type: release, build system: Linux-3.10.0-1160.11.1.el7.x86_64, C compiler: gcc 4.8.5, C++ compiler: g++ 4.8.5',)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%sql select madlib.version();\n",
    "#%sql select version();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"train_model\"></a>\n",
    "# 2. Train model in Python\n",
    "\n",
    "Train a model in Python using https://keras.io/examples/cifar10_cnn/\n",
    "\n",
    "Define model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "x_train shape: (50000, 32, 32, 3)\n",
      "50000 train samples\n",
      "10000 test samples\n",
      "WARNING:tensorflow:From /Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/tensorflow/python/ops/init_ops.py:1251: calling __init__ (from tensorflow.python.ops.init_ops) with dtype is deprecated and will be removed in a future version.\n",
      "Instructions for updating:\n",
      "Call initializer instance with the dtype argument instead of passing it to the constructor\n"
     ]
    }
   ],
   "source": [
    "from __future__ import print_function\n",
    "from tensorflow import keras\n",
    "from tensorflow.keras.datasets import cifar10\n",
    "from tensorflow.keras.preprocessing.image import ImageDataGenerator\n",
    "from tensorflow.keras.models import Sequential\n",
    "from tensorflow.keras.layers import Dense, Dropout, Activation, Flatten\n",
    "from tensorflow.keras.layers import Conv2D, MaxPooling2D\n",
    "import os\n",
    "\n",
    "batch_size = 32\n",
    "num_classes = 10\n",
    "epochs = 2\n",
    "data_augmentation = True\n",
    "num_predictions = 20\n",
    "#save_dir = os.path.join(os.getcwd(), 'saved_models')\n",
    "#model_name = 'keras_cifar10_trained_model.h5'\n",
    "\n",
    "# The data, split between train and test sets:\n",
    "(x_train, y_train), (x_test, y_test) = cifar10.load_data()\n",
    "print('x_train shape:', x_train.shape)\n",
    "print(x_train.shape[0], 'train samples')\n",
    "print(x_test.shape[0], 'test samples')\n",
    "\n",
    "# Convert class vectors to binary class matrices.\n",
    "y_train = keras.utils.to_categorical(y_train, num_classes)\n",
    "y_test = keras.utils.to_categorical(y_test, num_classes)\n",
    "\n",
    "model = Sequential()\n",
    "model.add(Conv2D(32, (3, 3), padding='same',\n",
    "                 input_shape=x_train.shape[1:]))\n",
    "model.add(Activation('relu'))\n",
    "model.add(Conv2D(32, (3, 3)))\n",
    "model.add(Activation('relu'))\n",
    "model.add(MaxPooling2D(pool_size=(2, 2)))\n",
    "model.add(Dropout(0.25))\n",
    "\n",
    "model.add(Conv2D(64, (3, 3), padding='same'))\n",
    "model.add(Activation('relu'))\n",
    "model.add(Conv2D(64, (3, 3)))\n",
    "model.add(Activation('relu'))\n",
    "model.add(MaxPooling2D(pool_size=(2, 2)))\n",
    "model.add(Dropout(0.25))\n",
    "\n",
    "model.add(Flatten())\n",
    "model.add(Dense(512))\n",
    "model.add(Activation('relu'))\n",
    "model.add(Dropout(0.5))\n",
    "model.add(Dense(num_classes))\n",
    "model.add(Activation('softmax'))\n",
    "\n",
    "# initiate RMSprop optimizer\n",
    "opt = keras.optimizers.RMSprop(lr=0.0001, decay=1e-6)\n",
    "\n",
    "# Let's train the model using RMSprop\n",
    "model.compile(loss='categorical_crossentropy',\n",
    "              optimizer=opt,\n",
    "              metrics=['accuracy']);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"class_name\": \"Sequential\", \"keras_version\": \"2.2.4-tf\", \"config\": {\"layers\": [{\"class_name\": \"Conv2D\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"conv2d\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"data_format\": \"channels_last\", \"padding\": \"same\", \"strides\": [1, 1], \"dilation_rate\": [1, 1], \"kernel_regularizer\": null, \"filters\": 32, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"batch_input_shape\": [null, 32, 32, 3], \"use_bias\": true, \"activity_regularizer\": null, \"kernel_size\": [3, 3]}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"relu\", \"trainable\": true, \"name\": \"activation\"}}, {\"class_name\": \"Conv2D\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"conv2d_1\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"data_format\": \"channels_last\", \"padding\": \"valid\", \"strides\": [1, 1], \"dilation_rate\": [1, 1], \"kernel_regularizer\": null, \"filters\": 32, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"use_bias\": true, \"activity_regularizer\": null, \"kernel_size\": [3, 3]}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"relu\", \"trainable\": true, \"name\": \"activation_1\"}}, {\"class_name\": \"MaxPooling2D\", \"config\": {\"name\": \"max_pooling2d\", \"dtype\": \"float32\", \"trainable\": true, \"data_format\": \"channels_last\", \"pool_size\": [2, 2], \"padding\": \"valid\", \"strides\": [2, 2]}}, {\"class_name\": \"Dropout\", \"config\": {\"name\": \"dropout\", \"dtype\": \"float32\", \"trainable\": true, \"rate\": 0.25, \"seed\": null, \"noise_shape\": null}}, {\"class_name\": \"Conv2D\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"conv2d_2\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"data_format\": \"channels_last\", \"padding\": \"same\", \"strides\": [1, 1], \"dilation_rate\": [1, 1], \"kernel_regularizer\": null, \"filters\": 64, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"use_bias\": true, \"activity_regularizer\": null, \"kernel_size\": [3, 3]}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"relu\", \"trainable\": true, \"name\": \"activation_2\"}}, {\"class_name\": \"Conv2D\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"conv2d_3\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"data_format\": \"channels_last\", \"padding\": \"valid\", \"strides\": [1, 1], \"dilation_rate\": [1, 1], \"kernel_regularizer\": null, \"filters\": 64, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"use_bias\": true, \"activity_regularizer\": null, \"kernel_size\": [3, 3]}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"relu\", \"trainable\": true, \"name\": \"activation_3\"}}, {\"class_name\": \"MaxPooling2D\", \"config\": {\"name\": \"max_pooling2d_1\", \"dtype\": \"float32\", \"trainable\": true, \"data_format\": \"channels_last\", \"pool_size\": [2, 2], \"padding\": \"valid\", \"strides\": [2, 2]}}, {\"class_name\": \"Dropout\", \"config\": {\"name\": \"dropout_1\", \"dtype\": \"float32\", \"trainable\": true, \"rate\": 0.25, \"seed\": null, \"noise_shape\": null}}, {\"class_name\": \"Flatten\", \"config\": {\"dtype\": \"float32\", \"trainable\": true, \"name\": \"flatten\", \"data_format\": \"channels_last\"}}, {\"class_name\": \"Dense\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"dense\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"kernel_regularizer\": null, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"units\": 512, \"use_bias\": true, \"activity_regularizer\": null}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"relu\", \"trainable\": true, \"name\": \"activation_4\"}}, {\"class_name\": \"Dropout\", \"config\": {\"name\": \"dropout_2\", \"dtype\": \"float32\", \"trainable\": true, \"rate\": 0.5, \"seed\": null, \"noise_shape\": null}}, {\"class_name\": \"Dense\", \"config\": {\"kernel_initializer\": {\"class_name\": \"GlorotUniform\", \"config\": {\"dtype\": \"float32\", \"seed\": null}}, \"name\": \"dense_1\", \"kernel_constraint\": null, \"bias_regularizer\": null, \"bias_constraint\": null, \"dtype\": \"float32\", \"activation\": \"linear\", \"trainable\": true, \"kernel_regularizer\": null, \"bias_initializer\": {\"class_name\": \"Zeros\", \"config\": {\"dtype\": \"float32\"}}, \"units\": 10, \"use_bias\": true, \"activity_regularizer\": null}}, {\"class_name\": \"Activation\", \"config\": {\"dtype\": \"float32\", \"activation\": \"softmax\", \"trainable\": true, \"name\": \"activation_5\"}}], \"name\": \"sequential\"}, \"backend\": \"tensorflow\"}'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model.to_json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Using real-time data augmentation.\n",
      "Epoch 1/2\n",
      "1563/1563 [==============================] - 107s 69ms/step - loss: 1.8637 - acc: 0.3142 - val_loss: 1.6037 - val_acc: 0.4154\n",
      "Epoch 2/2\n",
      "1563/1563 [==============================] - 116s 74ms/step - loss: 1.5880 - acc: 0.4174 - val_loss: 1.4362 - val_acc: 0.4754\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<tensorflow.python.keras.callbacks.History at 0x14dfc98d0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10000/10000 [==============================] - 7s 698us/sample - loss: 1.4365 - acc: 0.4754\n",
      "Test loss: 1.4364811393737793\n",
      "Test accuracy: 0.4754\n"
     ]
    }
   ],
   "source": [
    "x_train = x_train.astype('float32')\n",
    "x_test = x_test.astype('float32')\n",
    "x_train /= 255\n",
    "x_test /= 255\n",
    "\n",
    "if not data_augmentation:\n",
    "    print('Not using data augmentation.')\n",
    "    model.fit(x_train, y_train,\n",
    "              batch_size=batch_size,\n",
    "              epochs=epochs,\n",
    "              validation_data=(x_test, y_test),\n",
    "              shuffle=True)\n",
    "else:\n",
    "    print('Using real-time data augmentation.')\n",
    "    # This will do preprocessing and realtime data augmentation:\n",
    "    datagen = ImageDataGenerator(\n",
    "        featurewise_center=False,  # set input mean to 0 over the dataset\n",
    "        samplewise_center=False,  # set each sample mean to 0\n",
    "        featurewise_std_normalization=False,  # divide inputs by std of the dataset\n",
    "        samplewise_std_normalization=False,  # divide each input by its std\n",
    "        zca_whitening=False,  # apply ZCA whitening\n",
    "        zca_epsilon=1e-06,  # epsilon for ZCA whitening\n",
    "        rotation_range=0,  # randomly rotate images in the range (degrees, 0 to 180)\n",
    "        # randomly shift images horizontally (fraction of total width)\n",
    "        width_shift_range=0.1,\n",
    "        # randomly shift images vertically (fraction of total height)\n",
    "        height_shift_range=0.1,\n",
    "        shear_range=0.,  # set range for random shear\n",
    "        zoom_range=0.,  # set range for random zoom\n",
    "        channel_shift_range=0.,  # set range for random channel shifts\n",
    "        # set mode for filling points outside the input boundaries\n",
    "        fill_mode='nearest',\n",
    "        cval=0.,  # value used for fill_mode = \"constant\"\n",
    "        horizontal_flip=True,  # randomly flip images\n",
    "        vertical_flip=False,  # randomly flip images\n",
    "        # set rescaling factor (applied before any other transformation)\n",
    "        rescale=None,\n",
    "        # set function that will be applied on each input\n",
    "        preprocessing_function=None,\n",
    "        # image data format, either \"channels_first\" or \"channels_last\"\n",
    "        data_format=None,\n",
    "        # fraction of images reserved for validation (strictly between 0 and 1)\n",
    "        validation_split=0.0)\n",
    "\n",
    "    # Compute quantities required for feature-wise normalization\n",
    "    # (std, mean, and principal components if ZCA whitening is applied).\n",
    "    datagen.fit(x_train)\n",
    "\n",
    "    # Fit the model on the batches generated by datagen.flow().\n",
    "    model.fit_generator(datagen.flow(x_train, y_train,\n",
    "                                     batch_size=batch_size),\n",
    "                        epochs=epochs,\n",
    "                        validation_data=(x_test, y_test),\n",
    "                        workers=1)\n",
    "\n",
    "# Save model and weights\n",
    "#if not os.path.isdir(save_dir):\n",
    "#    os.makedirs(save_dir)\n",
    "#model_path = os.path.join(save_dir, model_name)\n",
    "#model.save(model_path)\n",
    "#print('Saved trained model at %s ' % model_path)\n",
    "\n",
    "# Score trained model.\n",
    "scores = model.evaluate(x_test, y_test, verbose=1)\n",
    "print('Test loss:', scores[0])\n",
    "print('Test accuracy:', scores[1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"load_model\"></a>\n",
    "# 3.  Load model into table\n",
    "\n",
    "Load the model architecture and weights into the model architecture table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>model_id</th>\n",
       "        <th>name</th>\n",
       "        <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>1</td>\n",
       "        <td>CIFAR10 model</td>\n",
       "        <td>CNN model with weights trained on CIFAR10.</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(1, u'CIFAR10 model', u'CNN model with weights trained on CIFAR10.')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import psycopg2 as p2\n",
    "conn = p2.connect('postgresql://gpadmin@localhost:8000/madlib')\n",
    "#conn = p2.connect('postgresql://fmcquillan@localhost:5432/madlib')\n",
    "cur = conn.cursor()\n",
    "\n",
    "from keras.layers import *\n",
    "from keras import Sequential\n",
    "import numpy as np\n",
    "\n",
    "# get weights, flatten and serialize\n",
    "weights = model.get_weights()\n",
    "weights_flat = [w.flatten() for w in weights]\n",
    "weights1d =  np.concatenate(weights_flat).ravel()\n",
    "weights_bytea = p2.Binary(weights1d.tostring())\n",
    "\n",
    "%sql DROP TABLE IF EXISTS model_arch_library_cifar10;\n",
    "query = \"SELECT madlib.load_keras_model('model_arch_library_cifar10', %s,%s,%s,%s)\"\n",
    "cur.execute(query,[model.to_json(), weights_bytea, \"CIFAR10 model\", \"CNN model with weights trained on CIFAR10.\"])\n",
    "conn.commit()\n",
    "\n",
    "# check weights loaded OK\n",
    "%sql SELECT model_id, name, description FROM model_arch_library_cifar10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"load_images\"></a>\n",
    "# 4. Get validation data set and load into table\n",
    "\n",
    "First set up image loader using the script called <em>madlib_image_loader.py</em> located at https://github.com/apache/madlib-site/tree/asf-site/community-artifacts/Deep-learning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "madlib_site_dir = '/Users/fmcquillan/Documents/Product/MADlib/Demos/data'\n",
    "sys.path.append(madlib_site_dir)\n",
    "\n",
    "# Import image loader module\n",
    "from madlib_image_loader import ImageLoader, DbCredentials\n",
    "\n",
    "# Specify database credentials, for connecting to db\n",
    "#db_creds = DbCredentials(user='fmcquillan',\n",
    "#                         host='localhost',\n",
    "#                         port='5432',\n",
    "#                         password='')\n",
    "\n",
    "# Specify database credentials, for connecting to db\n",
    "db_creds = DbCredentials(user='gpadmin', \n",
    "                         db_name='madlib',\n",
    "                         host='localhost',\n",
    "                         port='8000',\n",
    "                         password='')\n",
    "\n",
    "# Initialize ImageLoader (increase num_workers to run faster)\n",
    "iloader = ImageLoader(num_workers=5, db_creds=db_creds)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next load CIFAR-10 data from Keras consisting of 50,000 32x32 color training images, labeled over 10 categories, and 10,000 test images."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MainProcess: Connected to madlib db.\n",
      "Executing: CREATE TABLE cifar_10_test_data (id SERIAL, x REAL[], y TEXT)\n",
      "CREATE TABLE\n",
      "Created table cifar_10_test_data in madlib db\n",
      "Spawning 5 workers...\n",
      "Initializing PoolWorker-1 [pid 95042]\n",
      "PoolWorker-1: Created temporary directory /tmp/madlib_dTZhEGBDFE\n",
      "Initializing PoolWorker-2 [pid 95043]\n",
      "PoolWorker-2: Created temporary directory /tmp/madlib_ctWjbhcjwz\n",
      "Initializing PoolWorker-3 [pid 95044]\n",
      "PoolWorker-3: Created temporary directory /tmp/madlib_nx9VuMScrX\n",
      "Initializing PoolWorker-4 [pid 95045]\n",
      "PoolWorker-4: Created temporary directory /tmp/madlib_thkphNCw4r\n",
      "Initializing PoolWorker-5 [pid 95046]\n",
      "PoolWorker-5: Created temporary directory /tmp/madlib_037luEXgEL\n",
      "PoolWorker-2: Connected to madlib db.\n",
      "PoolWorker-3: Connected to madlib db.\n",
      "PoolWorker-1: Connected to madlib db.\n",
      "PoolWorker-5: Connected to madlib db.\n",
      "PoolWorker-4: Connected to madlib db.\n",
      "PoolWorker-3: Wrote 1000 images to /tmp/madlib_nx9VuMScrX/cifar_10_test_data0000.tmp\n",
      "PoolWorker-1: Wrote 1000 images to /tmp/madlib_dTZhEGBDFE/cifar_10_test_data0000.tmp\n",
      "PoolWorker-2: Wrote 1000 images to /tmp/madlib_ctWjbhcjwz/cifar_10_test_data0000.tmp\n",
      "PoolWorker-5: Wrote 1000 images to /tmp/madlib_037luEXgEL/cifar_10_test_data0000.tmp\n",
      "PoolWorker-4: Wrote 1000 images to /tmp/madlib_thkphNCw4r/cifar_10_test_data0000.tmp\n",
      "PoolWorker-3: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-3: Wrote 1000 images to /tmp/madlib_nx9VuMScrX/cifar_10_test_data0001.tmp\n",
      "PoolWorker-1: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-1: Wrote 1000 images to /tmp/madlib_dTZhEGBDFE/cifar_10_test_data0001.tmp\n",
      "PoolWorker-2: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-4: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-5: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-2: Wrote 1000 images to /tmp/madlib_ctWjbhcjwz/cifar_10_test_data0001.tmp\n",
      "PoolWorker-4: Wrote 1000 images to /tmp/madlib_thkphNCw4r/cifar_10_test_data0001.tmp\n",
      "PoolWorker-5: Wrote 1000 images to /tmp/madlib_037luEXgEL/cifar_10_test_data0001.tmp\n",
      "PoolWorker-3: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-1: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-2: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-4: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-5: Loaded 1000 images into cifar_10_test_data\n",
      "PoolWorker-1: Removed temporary directory /tmp/madlib_dTZhEGBDFE\n",
      "PoolWorker-2: Removed temporary directory /tmp/madlib_ctWjbhcjwz\n",
      "PoolWorker-3: Removed temporary directory /tmp/madlib_nx9VuMScrX\n",
      "PoolWorker-5: Removed temporary directory /tmp/madlib_037luEXgEL\n",
      "PoolWorker-4: Removed temporary directory /tmp/madlib_thkphNCw4r\n",
      "Done!  Loaded 10000 images in 108.267487049s\n",
      "5 workers terminated.\n"
     ]
    }
   ],
   "source": [
    "from keras.datasets import cifar10\n",
    "\n",
    "# Load dataset into np array\n",
    "(x_train, y_train), (x_test, y_test) = cifar10.load_data()\n",
    "\n",
    "%sql DROP TABLE IF EXISTS cifar_10_test_data;\n",
    "\n",
    "# Save images to temporary directories and load into database\n",
    "#iloader.load_dataset_from_np(x_train, y_train, 'cifar_10_train_data', append=False)\n",
    "iloader.load_dataset_from_np(x_test, y_test, 'cifar_10_test_data', append=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"inference\"></a>\n",
    "# 5. Inference"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Done.\n"
     ]
    },
    {
     "ename": "InternalError",
     "evalue": "(psycopg2.errors.InternalError_) plpy.Error: Unable to get number of classes from model architecture. (plpython.c:5038)\nCONTEXT:  Traceback (most recent call last):\n  PL/Python function \"madlib_keras_predict_byom\", line 23, in <module>\n    madlib_keras_predict.PredictBYOM(**globals())\n  PL/Python function \"madlib_keras_predict_byom\", line 42, in wrapper\n  PL/Python function \"madlib_keras_predict_byom\", line 314, in __init__\n  PL/Python function \"madlib_keras_predict_byom\", line 326, in validate_and_set_defaults\n  PL/Python function \"madlib_keras_predict_byom\", line 207, in set_default_class_values\n  PL/Python function \"madlib_keras_predict_byom\", line 75, in get_num_classes\nPL/Python function \"madlib_keras_predict_byom\"\n\n[SQL: SELECT madlib.madlib_keras_predict_byom('model_arch_library_cifar10',  -- model arch table\n                                         1,                            -- model arch id\n                                        'cifar_10_test_data',          -- test_table\n                                        'id',                          -- id column\n                                        'x',                           -- independent var\n                                        'cifar10_predict_byom',        -- output table\n                                        'response',                    -- prediction type\n                                         FALSE,                        -- use gpus\n                                         NULL,                         -- class values\n                                         255.0                         -- normalizing const\n                                   );]\n(Background on this error at: http://sqlalche.me/e/2j85)",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInternalError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-11-d7da0ccca3f1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mu'sql'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu''\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34mu\"DROP TABLE IF EXISTS cifar10_predict_byom;\\n\\nSELECT madlib.madlib_keras_predict_byom('model_arch_library_cifar10',  -- model arch table\\n                                         1,                            -- model arch id\\n                                        'cifar_10_test_data',          -- test_table\\n                                        'id',                          -- id column\\n                                        'x',                           -- independent var\\n                                        'cifar10_predict_byom',        -- output table\\n                                        'response',                    -- prediction type\\n                                         FALSE,                        -- use gpus\\n                                         NULL,                         -- class values\\n                                         255.0                         -- normalizing const\\n                                   );\\nSELECT * FROM cifar10_predict_byom ORDER BY id LIMIT 10;\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/IPython/core/interactiveshell.pyc\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[0;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[1;32m   2115\u001b[0m             \u001b[0mmagic_arg_s\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvar_expand\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mline\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstack_depth\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2116\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2117\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2118\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2119\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m</Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/decorator.pyc:decorator-gen-124>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    187\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    189\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    190\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m</Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/decorator.pyc:decorator-gen-123>\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/IPython/core/magic.pyc\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    187\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 188\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    189\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    190\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sql/magic.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m    135\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    136\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 137\u001b[0;31m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparsed\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'sql'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_ns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    138\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    139\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mNone\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstr\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcolumn_local_vars\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sql/run.pyc\u001b[0m in \u001b[0;36mrun\u001b[0;34m(conn, sql, config, user_namespace)\u001b[0m\n\u001b[1;32m    361\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    362\u001b[0m                 \u001b[0mtxt\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msqlalchemy\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtext\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 363\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtxt\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser_namespace\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    364\u001b[0m             \u001b[0m_commit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconfig\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mconfig\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    365\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mresult\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mconfig\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfeedback\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object_, *multiparams, **params)\u001b[0m\n\u001b[1;32m    980\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobject_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    981\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 982\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    983\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    984\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/sql/elements.pyc\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    285\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    286\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msupports_execution\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 287\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    288\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    289\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mObjectNotExecutableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1099\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1100\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1101\u001b[0;31m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1102\u001b[0m         )\n\u001b[1;32m   1103\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1249\u001b[0m             self._handle_dbapi_exception(\n\u001b[0;32m-> 1250\u001b[0;31m                 \u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1251\u001b[0m             )\n\u001b[1;32m   1252\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1474\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mnewraise\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1475\u001b[0m             \u001b[0;32melif\u001b[0m \u001b[0mshould_wrap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1476\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1477\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1478\u001b[0m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    396\u001b[0m     \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m     \u001b[0mcause\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mexc_value\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mexception\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 398\u001b[0;31m     \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcause\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    399\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    400\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1244\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mevt_handled\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1245\u001b[0m                     self.dialect.do_execute(\n\u001b[0;32m-> 1246\u001b[0;31m                         \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1247\u001b[0m                     )\n\u001b[1;32m   1248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mBaseException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/fmcquillan/Library/Python/2.7/lib/python/site-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    579\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    580\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 581\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    582\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    583\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInternalError\u001b[0m: (psycopg2.errors.InternalError_) plpy.Error: Unable to get number of classes from model architecture. (plpython.c:5038)\nCONTEXT:  Traceback (most recent call last):\n  PL/Python function \"madlib_keras_predict_byom\", line 23, in <module>\n    madlib_keras_predict.PredictBYOM(**globals())\n  PL/Python function \"madlib_keras_predict_byom\", line 42, in wrapper\n  PL/Python function \"madlib_keras_predict_byom\", line 314, in __init__\n  PL/Python function \"madlib_keras_predict_byom\", line 326, in validate_and_set_defaults\n  PL/Python function \"madlib_keras_predict_byom\", line 207, in set_default_class_values\n  PL/Python function \"madlib_keras_predict_byom\", line 75, in get_num_classes\nPL/Python function \"madlib_keras_predict_byom\"\n\n[SQL: SELECT madlib.madlib_keras_predict_byom('model_arch_library_cifar10',  -- model arch table\n                                         1,                            -- model arch id\n                                        'cifar_10_test_data',          -- test_table\n                                        'id',                          -- id column\n                                        'x',                           -- independent var\n                                        'cifar10_predict_byom',        -- output table\n                                        'response',                    -- prediction type\n                                         FALSE,                        -- use gpus\n                                         NULL,                         -- class values\n                                         255.0                         -- normalizing const\n                                   );]\n(Background on this error at: http://sqlalche.me/e/2j85)"
     ]
    }
   ],
   "source": [
    "%%sql\n",
    "DROP TABLE IF EXISTS cifar10_predict_byom;\n",
    "\n",
    "SELECT madlib.madlib_keras_predict_byom('model_arch_library_cifar10',  -- model arch table\n",
    "                                         1,                            -- model arch id\n",
    "                                        'cifar_10_test_data',          -- test_table\n",
    "                                        'id',                          -- id column\n",
    "                                        'x',                           -- independent var\n",
    "                                        'cifar10_predict_byom',        -- output table\n",
    "                                        'response',                    -- prediction type\n",
    "                                         FALSE,                        -- use gpus\n",
    "                                         NULL,                         -- class values\n",
    "                                         255.0                         -- normalizing const\n",
    "                                   );\n",
    "SELECT * FROM cifar10_predict_byom ORDER BY id LIMIT 10;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Number of missclassifications:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>count</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>2551</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(2551L,)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(*) FROM cifar10_predict_byom JOIN cifar_10_test_data USING (id)\n",
    "WHERE cifar10_predict_byom.estimated_dependent_var != cifar_10_test_data.y;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Predict accuracy. From https://keras.io/examples/cifar10_cnn/ accuracy claim is 75% on validation set after 25 epochs.  From run above test accuracy: 0.7449.  MADlib predict BYOM accuracy matches:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>test_accuracy_percent</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>74.49</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(Decimal('74.49'),)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT round(count(*)*100.0/10000.0, 2) as test_accuracy_percent from\n",
    "    (select cifar_10_test_data.y as actual, cifar10_predict_byom.estimated_dependent_var as estimated\n",
    "     from cifar10_predict_byom inner join cifar_10_test_data\n",
    "     on cifar_10_test_data.id=cifar10_predict_byom.id) q\n",
    "WHERE q.actual=q.estimated;"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}