{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# BentoML Demo: Iris Classifier with SQL Server Machine Learning Services"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**BentoML makes moving trained ML models to production easy:**\n",
    "\n",
    "* Package models trained with **any ML framework** and reproduce them for model serving in production\n",
    "* **Deploy anywhere** for online API serving or offline batch serving\n",
    "* High-Performance API model server with *adaptive micro-batching* support\n",
    "* Central hub for managing models and deployment process via Web UI and APIs\n",
    "* Modular and flexible design making it *adaptable to your infrastrcuture*\n",
    "\n",
    "BentoML is a framework for serving, managing, and deploying machine learning models. It is aiming to bridge the gap between Data Science and DevOps, and enable teams to deliver prediction services in a fast, repeatable, and scalable way.\n",
    "\n",
    "[Microsoft's Machine Learning Services](https://docs.microsoft.com/en-us/sql/machine-learning/sql-server-machine-learning-services?view=sql-server-ver15) is a feature in SQL Server that gives the ability to run Python and R scripts with relational data. We can use BentoML and other open-source packages, along with the Microsoft Python packages, for predictive analytics and machine learning. The scripts are executed in-database without moving data outside SQL Server or over the network.\n",
    "\n",
    "![Impression](https://www.google-analytics.com/collect?v=1&tid=UA-112879361-3&cid=555&t=event&ec=end-to-end&ea=end-to-end-sql&dt=end-to-end-sql)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's start by creating the Iris Classifier and packaging that as a bento bundle."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload 2\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "!pip install -q bentoml scikit-learn>=0.23.0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating BentoService for model serving. \n",
    "\n",
    "BentoService is the base for creating the classs. It should not be present in the `__main__` function so we are going to create a separate cell for it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%%writefile iris_classifier.py\n",
    "from bentoml import env, artifacts, api, BentoService\n",
    "from bentoml.adapters import DataframeInput\n",
    "from bentoml.frameworks.sklearn import SklearnModelArtifact\n",
    "\n",
    "@env(infer_pip_packages=True)\n",
    "@artifacts([SklearnModelArtifact('model')])\n",
    "\n",
    "class IrisClassifier(BentoService):\n",
    "    @api(input=DataframeInput(), batch=True)\n",
    "    def predict(Self,df):\n",
    "        #optional pre-procesing, post-processing code goes here\n",
    "        return self.artifacts.model.predict(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "\n",
    "from sklearn import svm\n",
    "from sklearn import datasets\n",
    "\n",
    "from iris_classifier import IrisClassifier\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "  # Load training data\n",
    "  iris = datasets.load_iris()\n",
    "  X, y = iris.data, iris.target\n",
    "\n",
    "  # Model Training\n",
    "  clf = svm.SVC(gamma='scale')\n",
    "  clf.fit(X, y)\n",
    "\n",
    "  # Create a iris classifier service instance\n",
    "  iris_classifier_service = IrisClassifier()\n",
    "\n",
    "  # Pack the newly trained model artifact\n",
    "  iris_classifier_service.pack('model', clf)\n",
    "\n",
    "  # Save the prediction service to disk for model serving\n",
    "  saved_path = iris_classifier_service.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Get the path of the saved bundle\n",
    "\n",
    "!bentoml get --print-location IrisClassifier:latest "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now you can serve the bentoml packed model via REST API by running the following command.\n",
    "\n",
    "`!bentoml serve IrisClassifier:latest`\n",
    "\n",
    "However, the focus of this tutorial is to serve the saved bundle using SQL server.\n",
    "\n",
    "We will first install the required software and packages.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For running the saved bundle in sql server follow these steps:\n",
    "\n",
    "- Install sql server machine learning services from [here](https://www.microsoft.com/en-in/sql-server/sql-server-downloads)\n",
    "\n",
    "- Follow the installation steps from [here](https://docs.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install). Don't forget to select python from the feature selection\n",
    "\n",
    "![](images/python-sqlserver.png)\n",
    "\n",
    "- Once the setup is ready, create a new server and after server is connected download [SSMS](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017). It is an integrated environment for managing SQL queries. We will use it to run our saved bento bundle and make predictions. Alternatively we can also use [Azure Data Studio ](https://docs.microsoft.com/en-us/sql/azure-data-studio/what-is?view=sql-server-ver15)\n",
    "\n",
    "- Install bentoml in the sql server. Open Command Prompt or terminal and change directory to `SQL Server/PYTHON_SERVICES/SCRIPTS`.\n",
    "Run `pip.exe install bentoml`\n",
    "\n",
    "\n",
    "If you get SSL related errors:\n",
    "- cd..\n",
    "- `condabin\\activate.bat` \n",
    "\n",
    "![](images/conda.png)\n",
    "\n",
    "- `pip.exe install bentoml`\n",
    "\n",
    "Additionally upgrade scikit-learn to prevent errors during deployment:\n",
    "- `pip install --upgrade scikit-learn`\n",
    "\n",
    "\n",
    "Finally copy the bento bundle from the location you got from `!bentoml get --print-location IrisClassifier:latest` to the directory where SQL Server is installed to avoid permission denied errors.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's switch to SSMS and write query for deploying bento saved bundle."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect to Database Engine with the server name you used to create new server. \n",
    "\n",
    "![](images/server-connect.png)\n",
    "\n",
    "Start a new query and write the following command. This will enable running external scripts.\n",
    "\n",
    "`sp_configure`\n",
    "\n",
    "`EXEC sp_configure  'external scripts enabled', 1`\n",
    "\n",
    "`RECONFIGURE WITH OVERRIDE`\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create a new database to store the dataset for prediction.\n",
    "\n",
    "`CREATE DATABASE irissql`\n",
    "\n",
    "`GO`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now create new table in the database we just created and put columns similar to the iris dataset.\n",
    "We will later save the data here.\n",
    "\n",
    "\n",
    "`USE irissql`\n",
    "\n",
    "`GO`\n",
    "\n",
    "`DROP TABLE IF EXISTS iris_data;`\n",
    "\n",
    "`GO`\n",
    "\n",
    "\n",
    "`CREATE TABLE iris_data (`\n",
    "\n",
    "  `id INT NOT NULL IDENTITY PRIMARY KEY`\n",
    "\n",
    "  `, \"Sepal.Length\" FLOAT NOT NULL, \"Sepal.Width\" FLOAT NOT NULL`\n",
    "\n",
    "  `, \"Petal.Length\" FLOAT NOT NULL, \"Petal.Width\" FLOAT NOT NULL`\n",
    "\n",
    "  `, \"Species\" VARCHAR(100) NOT NULL, \"SpeciesId\" INT NOT NULL`\n",
    "\n",
    "`);`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will create a procedure which works similar to a method(function) in python. We list a set of steps that will get executed when we'll use this procedure. We are going to use procedure `get_iris_dataset` to insert values in the table. \n",
    "Find more in the documentation [here](https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-execute-external-script-transact-sql?view=sql-server-ver15).\n",
    "\n",
    "\n",
    "`CREATE PROCEDURE get_iris_dataset`\n",
    "\n",
    "`AS`\n",
    "\n",
    "`BEGIN`\n",
    "\n",
    "`EXEC sp_execute_external_script @language = N'Python',` \n",
    "\n",
    "`@script = N'`\n",
    "\n",
    "`from sklearn import datasets`\n",
    "\n",
    "`iris = datasets.load_iris()`\n",
    "\n",
    "`iris_data = pandas.DataFrame(iris.data)`\n",
    "\n",
    "`iris_data[\"Species\"] = pandas.Categorical.from_codes(iris.target, iris.target_names)`\n",
    "\n",
    "`iris_data[\"SpeciesId\"] = iris.target`\n",
    "\n",
    "`',`\n",
    "\n",
    "`@input_data_1 = N'',`\n",
    "\n",
    "`@output_data_1_name = N'iris_data'`\n",
    "\n",
    "`WITH RESULT SETS ((\"Sepal.Length\" float not null, \"Sepal.Width\" float not null, \"Petal.Length\" float not null, \"Petal.Width\" float not null,`\n",
    "\n",
    "`\"Species\" varchar(100) not null, \"SpeciesId\" int not null));`\n",
    "\n",
    "`END;`\n",
    "\n",
    "`GO`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally insert data into the table `iris_data` and execute the procedure `get_iris_dataset`.\n",
    "\n",
    "`INSERT INTO iris_data (\"Sepal.Length\", \"Sepal.Width\", \"Petal.Length\", \"Petal.Width\", \"Species\", \"SpeciesId\")`\n",
    "\n",
    "`EXEC dbo.get_iris_dataset;`\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The last step is to create a procedure for model deployment and prediction.\n",
    "We will create a procedure `predict_species` and as an external script we will run the bento saved bundle.\n",
    "\n",
    "`import bentoml` as usual and set the `saved_path` to the location where the bento bundle is saved.\n",
    "\n",
    "Load the bundle using `bentoml.load()`. Now we can use this model loaded from the saved bundle to make predictions and deploy the model. List all the input and output features.\n",
    "\n",
    "\n",
    "Here is the complete script\n",
    "\n",
    "\n",
    "`CREATE PROCEDURE predict_species (@model VARCHAR(100))`\n",
    "\n",
    "`AS`\n",
    "\n",
    "`BEGIN`\n",
    " \n",
    "    `DECLARE @svm_model VARBINARY(max)`\n",
    "\n",
    "    `EXECUTE sp_execute_external_script @language = N'Python'`\n",
    "\n",
    "        `, @script = N'`\n",
    "\n",
    "`import bentoml`\n",
    "\n",
    "`saved_path=r\"C:\\Program Files\\Microsoft SQL Server\\MSSQL15.NEWSERVER\\bento_bundle\"`\n",
    "\n",
    "`irismodel = bentoml.load(saved_path)`\n",
    "\n",
    "`species_pred = irismodel.predict(iris_data[[\"Sepal.Length\", \"Sepal.Width\", \"Petal.Length\", \"Petal.Width\"]])`\n",
    "\n",
    "`iris_data[\"PredictedSpecies\"] = species_pred`\n",
    "\n",
    "`OutputDataSet = iris_data[[\"id\",\"SpeciesId\",\"PredictedSpecies\"]]`\n",
    "\n",
    "`print(OutputDataSet)`\n",
    "\n",
    "`'\n",
    "        , @input_data_1 = N'select id, \"Sepal.Length\", \"Sepal.Width\", \"Petal.Length\", \"Petal.Width\", \"SpeciesId\" from iris_data'`\n",
    "\n",
    "        `, @input_data_1_name = N'iris_data'`\n",
    "        \n",
    "        `, @params = N'@svm_model varbinary(max)'`\n",
    "        \n",
    "        `, @nb_model = @svm_model`\n",
    "    \n",
    "    `WITH RESULT SETS((`\n",
    "     \n",
    "                `\"id\" INT`\n",
    "              \n",
    "              `, \"SpeciesId\" INT`\n",
    "\n",
    "              `, \"SpeciesId.Predicted\" INT`\n",
    "               \n",
    "               ` ));`\n",
    "\n",
    "`END;`\n",
    "\n",
    "`GO`\n",
    "\n",
    "The procedure is ready now. We can deploy it using `Execute predict_species 'SVM';` where SVM is the name given to the model.\n",
    "\n",
    "\n",
    "\n",
    "`EXECUTE predict_species 'SVM';`\n",
    "\n",
    "`GO`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After running the final query you can see the predictions in form of a table. Our model is served with SQL server easily with the help of BentoML."
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}