Part 2 MSSQL Machine Learning – Step by Step

By | June 4, 2020

Now that we have Machine learning services installed on SQL Server the next thing will be to install additional packages. Packages are precompiled code that we can use similar to a function to perform certain tasks. The most common examples are numpy, matplotlib, sklearn etc. Each of these modules contains easy to use functions that perform common operations like arithmetic operations, plot graphs or even machine learning. Using these modules allow us to get started without having to reinvent the wheel.

Some context

When using Python outside of SQL Server we normally are able to install modules easily using an application called Pip. However since Pip is used within Python and not SQL Server we need to install additional components from within SQLMLUTILS. We later add modules into SQL Server machine learning services using SQLMLUtils. In short SQL Server Machine Learning Services uses SQLMLUTILS which in turn uses PIP to get and install the Modules we require.

Download and Install SQLMLUTILS

The first step is to download SQLMUTILS from the link here . Do not extract the zip file. These steps are required to be performed on a DEV environment where you have permissions to install the utils and python , pip etc.

Once downloaded, open command prompt and run it in administrator mode followed by the common below

pip install –upgrade –upgrade-strategy only-if-needed c:\temp\sqlmlutils-1.0.1.zip

Where the path will be modified to wherever you saved file.

Make sure you check the version of the zip file in the path when you copy paste the command. When run it should look similar to the screen below.

Next we need to call SQLMLUtils from within python to start installing our modules

Installing Modules

To install modules you simply need to run the below commands from within Python after changing the database connection string.

import sqlmlutils

connection = sqlmlutils.ConnectionInfo(server=”server”, database=”database”, uid=”username”, pwd=”password”)

sqlmlutils.SQLPackageManager(connection).install(“text-tools”)

 

Once done it should look similar to the screen below, I am installing module sklearn which is used for Machine Learning.

With that we have installed the module, you will need to follow the above step once per module.

In the next post we explore how to run a basic linear regression machine learning algorithm on SQL Server Machine learning services.