Build a predictive model using Python and SQL Server ML Services
Predictive modeling is a powerful way to add intelligence to your application. It enables applications to predict outcomes against new data. The act of incorporating predictive analytics into your applications involves two major phases: model training and model deployment
In this tutorial, you will learn how to create a predictive model in Python and deploy it with SQL Server 2017 Machine Learning Services, RC1 and above.
You can copy code as you follow this tutorial. All code is also available on github.
Step 1.1 Install SQL Server with in-database Machine Learning Services
- If you don’t have SQL Server 2016 Developer (or above) installed:
*Click here to download the preview of SQL Server 2017
*Click here here to download the SQL Server 2016 exe (This version only supports R for Machine Learning)
- Run it to start the SQL installer
- Click Accept> after you have read the license terms
- On the Feature Selection page, select: R Services (In-Database) for SQL Server 2016 or Machine Learning Services (In-Database) for SQL Server 2017
- Don’t forget to choose R/Python or both
- If you chose R: On the page, Consent to Install Microsoft R Open>, click Accept.
- If you chose Python: On the page, Consent to Python>, click Accept.
- Click Install to proceed with the installation
You now have SQL Server installed with in-database ML services, running locally on your Windows computer! Check out the next section to continue installing prerequisites.
Step 1.2 Install SQL Server Management Studio (SSMS)
Download and install SQL Server Management studio: SSMS
Now you have installed a tool you can use to easily manage your database objects and scripts.
Step 1.3 Enable external script execution
Run SSMS and open a new query window. Then execute the script below to enable your instance to run Python scripts in SQL Server.
EXEC sp_configure 'external scripts enabled', 1; RECONFIGURE WITH OVERRIDE
You can read more about configuring Machine Learning Services here. Don’t forget to restart your SQL Server Instance after the configuration! You can restart in SSMS by right clicking on the instance name in the Object Explorer and choose Restart.
Now you have enabled external script execution so that you can run Python code inside SQL Server!
Step 1.4 Install and configure your Python development environment
1.You need to install a Python IDE. Here are some suggestions:
*Python Tools for Visual Studio (PTVS) Download
2.Note: To be able to use some of the functions in this tutorial, you need to point your Python environment to use the Python that comes with SQL Server 2017. Point your Python environment to this path: C:\Program Files\Microsoft SQL Server\YOURSQLSERVER\PYTHON_SERVICES This is an example path to illustrate where the Python interpreter files are saved: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES
For instructions on how to point to the python interpreter that comes with SQL Server Machine Learning Services, here are some useful links:
*PTVS: Here are instructions on how to manually point to a python interpreter using PTVS
*VS Code: Here are instructions on how to manually point to a python interpreter using VS Code.
*PyCharm: Here are instructions on how to configure a python interpreter using Pycharm
Terrific, now your SQL Server instance is able to host and run Python code and you have the necessary development tools installed and configured! The next section will walk you through creating a predictive model using Python.