Deploy model to SQL Server Machine Learning Services

[yashika51]

Hi,
I am working on issue #766 . This is the first issue I have picked to work on for Bento. I had a few doubts which @parano promptly helped me with. Starting a discussion here to keep the community updated :)

Based on our discussion this is what he suggested

the model training and packaging does not need to happen within Azure
The idea is that I can create the BentoML saved bundle locally or anywhere and deploy them to SQL server

I have created the python script so far that does the basic iris classification. The setup for the SQL server has been done and I am working in the SSMS.

Now I want to load the saved bento bundle as the script in SQL server and I am figuring out ways to do that.

[yashika51]

So to import bentoml in sql server we have to install it via cmd. This is done and when I run this to load the saved bundle, I get yml file not found error

This is something I faced when I was using Jupyter notebooks but that problem was solved by removing %%writefile. This time the bundle is created with the right Python script. What could be the reason for the error here?

An external script error occurred: bentoml.exceptions.BentoMLException: BentoML can't locate config file 'bentoml.yml' in saved bundle in path

[parano]

I'm not familiar with how SQL server setup its python runtime but could you make sure the file is available in that python session? For example, SQL Server might be running the code in a container or on a remote server, which does not have access to this local directory.

[yashika51]

Thanks for the reply :).
Albeit by writing a script that prints the directories shows that the files are accessible inside the SQL server it still gives yml file not found error. The problem is solved by moving the saved bundle to the directory where the SQL server is installed. Also, the scikit-learn package is needed to get upgraded in the SQL server.
Eventually, the saved_bundle is loaded successfully!

[yashika51]

Hi @parano, I came up with this SQL query for loading the bento bundle and making the predictions. Could you please take a look at this gist and suggest any changes?

Briefly speaking this is what I have done:

• Created table for storing the data
• Created procedure to get the data from iris dataset and save it to the database
• Created another procedure for prediction.

Some of the questions here are:

• The bento bundle is capable of making predictions but when we see the SQL server documentation they have created a procedure and saved the model by dumping the pickle file. IMO we don't need to do that when we have bento :)
  Please correct me if I am wrong

• I am not sure if we can use simple list as an input for predictions. That is why I stored it in the database. Am I right?

[parano]

Hi @yashika51, apologize for the delayed response,

The bento bundle is capable of making predictions but when we see the SQL server documentation they have created a procedure and saved the model by dumping the pickle file. IMO we don't need to do that when we have bento :)
Please correct me if I am wrong

That is correct.

I am not sure if we can use simple list as an input for predictions. That is why I stored it in the database. Am I right?

I'm not sure I understand your question. Do you mean using a python list as input for prediction? That will depend on the BentoService class' implementation.

The gist looks good. Would be great if you could add documentation on detailed setup, how to install all dependencies specified in the BentoService saved bundle, how to use tables as inference input, etc. If you are not sure about the input for prediction, try deploying another model(e.g. xgboost or pytorch) with other input adapters(JSON, Image) and see if you could get them to work.

[yashika51]

Hi @parano, no problem.
I get your point. I created this gist to just confirm if I am doing it right. I'll add details now.
Another question(probably last 😅) is does this structure of this tutorial sound right?

-sql-server-deployment folder
--Iris Classifier.ipynb
--deploybentobundle.sql
-- images(directory containing some screenshots from terminal)

Because I was also wondering if we want to continue explaining SQL server deployment in the Jupyter notebook and just add the screenshots of the query running along instead of putting a SQL file.

[yubozhao]

@yashika51 Great job with the Gist

I think for the tutorial on the documentation site, it would be great just to explain how did you set things up and anything the reader should watch out to avoid potential problems. If the tutorial involves with any operations on the Azure web ui, screenshots would be great. You can find an example like the Heroku deployment guide.

You could also make a PR to the bentoml/gallery repo with the Jupyter notebook and the sql file as an reference in your tutorial

[yashika51]

Thanks for the clarification @yubozhao :). Based on your suggestions I have made a PR bentoml/gallery#45.

Please let me know if there are any changes.

[yubozhao]

Awesome. I comment on the gallery PR. Looking forward the tutorial PR to the documentation in this repo.