exercises/070-RDT-AutoML.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6083ec24-4bf3-4ac2-8128-8546c4222d24",
   "metadata": {},
   "outputs": [],
   "source": [
    "import uuid\n",
    "\n",
    "from hana_ml.algorithms.pal.auto_ml import AutomaticClassification\n",
    "from hana_ml.dataframe import ConnectionContext\n",
    "from hana_ml.model_storage import ModelStorage\n",
    "from hana_ml.visualizers.automl_progress import PipelineProgressStatusMonitor\n",
    "from hana_ml.visualizers.automl_report import BestPipelineReport\n",
    "from hana_ml.visualizers.unified_report import UnifiedReport\n",
    "\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4bdc011e",
   "metadata": {},
   "source": [
    "## Check the setup and connect to the database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a8055be",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run \"010-check_setup.ipynb\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d2233fae-89f3-4b5d-8160-2c97d69034e3",
   "metadata": {},
   "source": [
    "# Tables from SAP HANA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "631711e0-1159-4452-8b69-3dc2de55332f",
   "metadata": {},
   "outputs": [],
   "source": [
    "column_id=\"PassengerId\"\n",
    "column_label=\"Survived\"\n",
    "features_subset=[\n",
    "            \"NameFirstLetter\",\n",
    "            \"FarePerPerson\",\n",
    "            \"SibSp\",\n",
    "            \"ParCh\",\n",
    "            \"Gender\",\n",
    "            \"PClass\",\n",
    "            \"Embarked\",\n",
    "            \"Title\",\n",
    "            \"Age\",\n",
    "            \"PersonsPerTicket\"\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9cd87d4e-cc36-4cbe-babe-96ea9c3d3481",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_train = myconn.table(\"DATA_LABELED_FE\").select(\n",
    "            [column_id]+features_subset+[column_label]\n",
    "        ).cast(column_label, 'NVARCHAR(1)')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36af59f4-03a4-476a-a36d-f1cdfc83aa2a",
   "metadata": {},
   "source": [
    "# Instantiate AutoML `auto_c` object"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d94efe4e-e125-469f-8777-966f1b3f70b7",
   "metadata": {},
   "source": [
    "Parametrization of `auto_c` instance: https://help.sap.com/doc/cd94b08fe2e041c2ba778374572ddba9/latest/en-US/pal/topics/genetic_optimization_automl.html#control-parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6076611e-9814-44f6-8bf7-5265b795de31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# AutomaticClassification init \n",
    "progress_id = \"automl_{}\".format(uuid.uuid1())\n",
    "auto_c = AutomaticClassification(\n",
    "    progress_indicator_id=progress_id\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51bc23fc-eab9-444b-9d8e-4ef37e69bc0d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# enable_workload_class\n",
    "auto_c.enable_workload_class(workload_class_name=\"PAL_AUTOML_WORKLOAD\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a548c69-f38f-455a-af68-466b70ef163d",
   "metadata": {},
   "source": [
    "Display the current configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d638f835-7a3f-4d18-9ec1-07a6b4e5d809",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.display_config_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68c4e1c5-dac8-46c4-994c-b40240b5f735",
   "metadata": {},
   "source": [
    "## Get an overview of pipeline operators and parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80312ea0-58d7-4e8d-be1c-34e18ab74b5f",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.algorithms.pal import auto_ml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a548e54-7a7c-4e99-93c8-c58c6cbbedf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_ml.get_pipeline_info(myconn).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39cdb878-4463-434f-99ce-7efca24289b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.display import HTML\n",
    "HTML(auto_ml.get_pipeline_info(myconn).filter(\"NAME in ('RDT_Classifier', 'HGBT_Classifier')\").collect().to_html())"
   ]
  },
  {
   "cell_type": "raw",
   "id": "32effb3d-e27d-425f-94fa-c400e82fc4f5",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "#Prior to HANA Cloud db 2024/Q3\n",
    "eval(auto_ml.get_pipeline_info(myconn).filter(\"NAME='RDT_Classifier'\").collect()[\"PARAMETERS\"][0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d44a006f",
   "metadata": {},
   "outputs": [],
   "source": [
    "eval(auto_ml.get_pipeline_info(myconn).filter(\"NAME in ('RDT_Classifier', 'HGBT_Classifier')\").collect()[\"SCHEMA\"][1])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e38edc35-9604-48d6-866a-19d8d2cbf731",
   "metadata": {},
   "source": [
    "## Experiment with [pipeline setting](https://help.sap.com/doc/cd94b08fe2e041c2ba778374572ddba9/latest/en-US/pal/algorithms/hana_ml.algorithms.pal.auto_ml.AutomaticClassification.html#hana_ml.algorithms.pal.auto_ml.AutomaticClassification.delete_config_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c47772e0-05d7-4ca9-80da-3adb6780288f",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.delete_config_dict(category=\"Resampler\")\n",
    "auto_c.delete_config_dict(category=\"Transformer\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "da632a96-6063-42d6-ab2b-53e954423de2",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.delete_config_dict(operator_name='MLP_Classifier')\n",
    "auto_c.delete_config_dict(operator_name='M_LOGR_Classifier')\n",
    "auto_c.delete_config_dict(operator_name='NB_Classifier')\n",
    "auto_c.delete_config_dict(operator_name='DT_Classifier')\n",
    "auto_c.delete_config_dict(operator_name='SVM_Classifier')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61693913-4f1a-4f9a-bbbb-9c65bb31e1be",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.update_config_dict(\"RDT_Classifier\", \"TREES_NUM\", [11, 49, 99])\n",
    "auto_c.update_config_dict(\"RDT_Classifier\", \"MAX_DEPTH\", {'range': [3, 2, 8]})\n",
    "auto_c.update_config_dict(\"RDT_Classifier\", \"NODE_SIZE\", [1, 100])\n",
    "auto_c.delete_config_dict(operator_name='RDT_Classifier', param_name='SAMPLE_FRACTION')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3604236",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.update_config_dict(\"HGBT_Classifier\", \"OBJ_FUNC\", [5, 6])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15f3b142-ea12-4667-a2e7-f9151d7b1fb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.display_config_dict()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e867fa2-ab98-433d-ab7e-255089f7d388",
   "metadata": {},
   "source": [
    "## Start Progress Status Monitor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f484501a-7fa9-4c33-8a2b-a0df7e6f96c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# invoke a PipelineProgressStatusMonitor\n",
    "progress_status_monitor = PipelineProgressStatusMonitor(\n",
    "    # need to create a connection context different from the one of AutoML\n",
    "    connection_context=myconn, \n",
    "    automatic_obj=auto_c)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca0f2311-ff32-465f-a90b-cdb21655be67",
   "metadata": {},
   "outputs": [],
   "source": [
    "progress_status_monitor.start()\n",
    "\n",
    "# training\n",
    "try:\n",
    "    auto_c.fit(data=hdf_titanic_train.set_index('PassengerId'))\n",
    "except Exception as e:\n",
    "    raise e"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f02eb0e0-a16a-4a61-a7c1-5ca290870839",
   "metadata": {},
   "source": [
    "## Get a report for the Best Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7e1f6fbb-753a-4d04-924d-d853608e1e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "BestPipelineReport(auto_c).generate_notebook_iframe()"
   ]
  },
  {
   "cell_type": "raw",
   "id": "601a9494-59fe-48be-882a-997ac600ca6b",
   "metadata": {
    "vscode": {
     "languageId": "raw"
    }
   },
   "source": [
    "# To generate an HTML page for the same:\n",
    "BestPipelineReport(auto_c).generate_html('auto-ml')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76f7faf8-a754-48a2-b5de-597002006971",
   "metadata": {},
   "outputs": [],
   "source": [
    "auto_c.model_[0].head(5).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5547cba-bbb5-43c4-8011-363c173f3a2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_colwidth', 200)\n",
    "auto_c.model_[1].collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "158a7495-7b45-4c2f-b675-40ce9efaca15",
   "metadata": {},
   "source": [
    "## Save the model in SAP HANA db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48b946f1-35a5-462b-9c18-65d483d0c412",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms = ModelStorage(myconn)\n",
    "auto_c.name = 'CodeJam-Titanic-AutoML'\n",
    "auto_c.version = 1\n",
    "ms.save_model(model=auto_c, if_exists='replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96edf201-a8e8-4021-991a-09ecddb1c9ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms.list_models()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41bdbad4-b39a-43d8-80f3-d3773ffac6fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "null = None\n",
    "false = False\n",
    "true = True\n",
    "eval(ms.list_models(name=auto_c.name, version=auto_c.version).at[0, 'JSON'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c6bf50b-3d57-499a-a647-af7f0daccb3c",
   "metadata": {},
   "source": [
    "# Call prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1402e2fd-3ec6-49be-a3be-eda1fb63e043",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_test=myconn.table('DATA_TO_PREDICT_FE').select(\n",
    "            [column_id]+features_subset\n",
    "        )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "03c7166f-a4f5-4c0a-be03-59ded25a4c55",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res = auto_c.predict(hdf_titanic_test.set_index('PassengerId'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6060ab9-2657-49d4-87fc-da4bdd26a9b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res.head(4).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "011df4d6-14e2-4509-b017-c8d4170be148",
   "metadata": {},
   "source": [
    "# Compare to the ground truth"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "591d755f-8957-41c0-b145-a8a8eb8883bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_titanic_complete=myconn.table('DATA_COMPLETE', schema='TITANIC')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a8fd5d5-510f-4942-98f2-5587f9c08e61",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res_ext=hdf_res.set_index('ID').join(myconn.table(\"DATA_TO_PREDICT_FE\").set_index('PassengerId'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89457191-53c8-4bfb-a01d-ee4f70960d24",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res_ext.head(3).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e4688cca-bcd9-4501-bb8f-c717fb319d5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res_incl_groundtruth=(hdf_res_ext.set_index(['Name', 'Ticket']).join(hdf_titanic_complete.set_index(['name', 'ticket']))\n",
    "                 .select('ID', 'Name', 'Ticket', 'SCORES','survived',('1-ABS(SCORES-\"survived\")', 'IS_CORRECT'))\n",
    "                 .cast('SCORES', 'INT')\n",
    "\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6eec4dd-59a9-48bf-a380-73efa97a98bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_res_incl_groundtruth.head(3).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c18af373-e2d0-4eec-a8c7-65fb9db7cc3d",
   "metadata": {},
   "source": [
    "## Using `metrics`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a6b2fa9-0ee7-46a2-a47b-2ced35ccbebf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import hana_ml.algorithms.pal.metrics as pal_metrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5d18fc1-42a3-4efe-8913-e6f32bbd7c70",
   "metadata": {},
   "outputs": [],
   "source": [
    "pal_metrics.accuracy_score(data=hdf_res_incl_groundtruth, label_true='survived', label_pred='SCORES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d455525a-28e1-47a6-a32f-c8cf17daf770",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_cm, hdf_cr = pal_metrics.confusion_matrix(data=hdf_res_incl_groundtruth, key='ID', label_true='survived', label_pred='SCORES')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7777a42e-da68-4e4b-a62d-4046c1fce5f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_cm.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1ae70de0-63ef-4bc3-9d62-e9188302ee16",
   "metadata": {},
   "outputs": [],
   "source": [
    "hdf_cr.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dca1c505-fb31-4c2f-8ab0-c9cd33ece610",
   "metadata": {},
   "outputs": [],
   "source": [
    "from hana_ml.visualizers.metrics import MetricsVisualizer\n",
    "MetricsVisualizer(enable_plotly=True).plot_confusion_matrix(hdf_cm, normalize=False);"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0b653f60-260e-4016-8783-2bb18473daff",
   "metadata": {},
   "source": [
    "🤓 **Let's discuss**:\n",
    "- Comparison of the last two models you trained"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "env",
   "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.13.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}