Hands On HANA Cloud PAL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# HANA Cloud - Predicitive Analysis Library Hands On\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Documentation\n",
    "- SAP HANA Python Client API for Machine Learning Algorithms: https://pypi.org/project/hana-ml/\n",
    "\n",
    "- SAP HANA Predictive Analysis Library (PAL): https://help.sap.com/viewer/2cfbc5cf2bc14f028cfbe2a2bba60a50/1.0.12/en-US\n",
    "\n",
    "SAP HANA ML Library\n",
    "You will be using the 'SAP HANA Python Client API for Machine Learning Algorithm'."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.\tHow can we directly access the data in our HANA? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "!pip install hana_ml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import hana_ml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hana_address = #your hostname as string\n",
    "hana_port = #your port as integer\n",
    "hana_user = #your user as string\n",
    "hana_password = #your password as string\n",
    "hana_encrypt = 'true' #for HANA Cloud"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import hana_ml.dataframe as dataframe\n",
    "\n",
    "# Establish connection\n",
    "conn = dataframe.ConnectionContext(address = hana_address,\n",
    "                                   port = hana_port, \n",
    "                                   user = hana_user, \n",
    "                                   password = hana_password, \n",
    "                                   encrypt = hana_encrypt,\n",
    "                                   sslValidateCertificate = 'false')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Through a HANA Key we are able to hide our login credentials."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import hana_ml.dataframe as dataframe\n",
    "\n",
    "# Establish connection\n",
    "conn = dataframe.ConnectionContext(userkey = 'MYHANACLOUD',\n",
    "                                   encrypt = 'true',\n",
    "                                   sslValidateCertificate = 'false')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "#load data, change path to your directory\n",
    "df = pd.read_csv(r\"C:\\<yourpath>\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#change columns to upper string\n",
    "df.columns = map(str.upper, df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#insert a product ID, which will later be used as key\n",
    "df.insert(0, 'PRODUCT_ID', df.reset_index().index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control a sample of the data\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a SAP HANA dataframe and point it to the table with the uploaded data.\n",
    "df_remote = dataframe.create_dataframe_from_pandas(connection_context = conn, \n",
    "                                                   pandas_df = df, \n",
    "                                                   table_name = 'PREDICTIVEQUALITY',\n",
    "                                                   force = True,\n",
    "                                                   replace = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.\tHow can we explore our data and react to data quality issues early? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control the size of the data\n",
    "df_remote.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control the variable types in SAP HANA\n",
    "df_remote.dtypes()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The variable QUALITY is binary and labels all products of bad quality with a 1. Since this is a categorical variable we transform it to type NVARCHAR with the following command. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#transform the variable QUALITY\n",
    "df_remote = df_remote.cast('QUALITY', 'NVARCHAR(20)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control the variable types\n",
    "df_remote.dtypes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#describe the data in SAP HANA\n",
    "df_remote.describe().collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create a Data Report for further exploration\n",
    "from hana_ml.visualizers.dataset_report import DatasetReportBuilder\n",
    "datasetReportBuilder = DatasetReportBuilder()\n",
    "datasetReportBuilder.build(df_remote, key=\"PRODUCT_ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#generate Data Report directly in Jupyter Notebook\n",
    "datasetReportBuilder.generate_notebook_iframe_report()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3.\tHow can we leverage the computing power of our HANA in our machine learning use case?  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create training and testing set\n",
    "from hana_ml.algorithms.pal import partition\n",
    "df_remote_train, df_remote_test, df_remote_ignore = partition.train_test_val_split(\n",
    "                                                                                   random_seed = 1017,\n",
    "                                                                                   data = df_remote, \n",
    "                                                                                   training_percentage = 0.8, \n",
    "                                                                                   testing_percentage = 0.2,\n",
    "                                                                                   validation_percentage = 0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control the size of the training and testing set\n",
    "print('Size of training subset: ' + str(df_remote_train.count()))\n",
    "print('Size of test subset: ' + str(df_remote_test.count()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us now train or random forest on the training set. First, we set the numbers of trees very high, to see where the Out of Bag error converges. After optimizing the numbers of trees we will take a closer look at the variables considered at each split. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#set parameters of random forest\n",
    "from hana_ml.algorithms.pal.trees import RandomForestClassifier\n",
    "rf = RandomForestClassifier(n_estimators=1501, \n",
    "                            calculate_oob=True, \n",
    "                            random_state=101,\n",
    "                            categorical_variable= ['SUPPLIER', 'MACHINE', 'QUALITY'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train random forest on the training set\n",
    "rf.fit(df_remote_train,\n",
    "       key='PRODUCT_ID', \n",
    "       features=['SUPPLIER','MACHINE','SENSOR1','SENSOR2','SENSOR3','SENSOR4','SENSOR5','SENSOR6','SENSOR7','SENSOR8','SENSOR9','SENSOR10'], \n",
    "       label='QUALITY')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#apply the trained model on the testing set\n",
    "result = rf.predict(df_remote_test,\n",
    "                    key='PRODUCT_ID', \n",
    "                    features=['SUPPLIER','MACHINE','SENSOR1','SENSOR2','SENSOR3','SENSOR4','SENSOR5','SENSOR6','SENSOR7','SENSOR8','SENSOR9','SENSOR10'])\n",
    "result.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#compute confuction matrix\n",
    "rf.confusion_matrix_.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#collect variable importance\n",
    "rf.feature_importances_.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#collect OOB error\n",
    "oob = rf.oob_error_.collect()\n",
    "oob"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#plot the OOB error\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pyplot import figure\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "plt.plot(oob[['TREE_INDEX']], oob[['ERROR']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the random forest converges after around 800 trees. Hence, let us now optimize the number of variables considered at each split. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#compute oob error at 801 trees for different numbers of variables considered at each split.\n",
    "l = []\n",
    "for var in range(0, 10):\n",
    "    rf = RandomForestClassifier(n_estimators=801, calculate_oob=True, max_features=var+1, random_state=101, categorical_variable= ['SUPPLIER', 'MACHINE', 'QUALITY'])\n",
    "    rf.fit(df_remote_train,key='PRODUCT_ID', features=['SUPPLIER','MACHINE','SENSOR1','SENSOR2','SENSOR3','SENSOR4','SENSOR5','SENSOR6','SENSOR7','SENSOR8','SENSOR9','SENSOR10'], label='QUALITY')\n",
    "    oob = pd.DataFrame(rf.oob_error_.collect()[['ERROR']])\n",
    "    l.append(oob[['ERROR']].iloc[-1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#show results\n",
    "l"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#plot the OOB error for each number of variables considered at each split. \n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.pyplot import figure\n",
    "figure(num=None, figsize=(8, 6), dpi=80, facecolor='w', edgecolor='k')\n",
    "plt.plot(l)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From our results we see that the optimal number of trees is 801 and the optimal numbers of variables considered at each split is 2. After 2 variables the OOB error increases again and we might overfitt the data. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#train the optimal random forest model\n",
    "from hana_ml.algorithms.pal.trees import RandomForestClassifier\n",
    "rf = RandomForestClassifier(n_estimators=801, max_features=2,calculate_oob=True, random_state=101,categorical_variable= ['SUPPLIER', 'MACHINE', 'QUALITY'])\n",
    "rf.fit(df_remote_train,key='PRODUCT_ID', features=['SUPPLIER','MACHINE','SENSOR1','SENSOR2','SENSOR3','SENSOR4','SENSOR5','SENSOR6','SENSOR7','SENSOR8','SENSOR9','SENSOR10'], label='QUALITY')\n",
    "result = rf.predict(df_remote_test,key='PRODUCT_ID', features=['SUPPLIER','MACHINE','SENSOR1','SENSOR2','SENSOR3','SENSOR4','SENSOR5','SENSOR6','SENSOR7','SENSOR8','SENSOR9','SENSOR10'])\n",
    "result.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#control confusion matrix\n",
    "rf.confusion_matrix_.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.\tHow can we save and create different versions of our results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#create Model storage\n",
    "from hana_ml.model_storage import ModelStorage \n",
    "MODEL_SCHEMA = 'YANNICK' # HANA schema in which models are to be saved \n",
    "model_storage = ModelStorage(connection_context=conn, schema = MODEL_SCHEMA) \n",
    "rf.name = 'Random Forest Model' \n",
    "model_storage.save_model(model=rf, if_exists = 'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Lists model\n",
    "list_models = model_storage.list_models()\n",
    "print(list_models)"
   ]
  }
 ],
 "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}