Rediger

Del via

Install pretrained machine learning models on SQL Server

  • Artikkel

Applies to: SQL Server 2016 (13.x), SQL Server 2017 (14.x), and SQL Server 2019 (15.x)

This article applies to SQL Server 2016 (13.x), SQL Server 2017 (14.x), and SQL Server 2019 (15.x).

This article explains how to use PowerShell to add free pretrained machine learning models for sentiment analysis and image featurization to a SQL Server instance having R or Python integration. The pretrained models are built by Microsoft and ready-to-use, added to an instance as a post-install task. For more information about these models, see the Resources section of this article.

Beginning with SQL Server 2022 (16.x), runtimes for R, Python, and Java, are no longer installed with SQL Setup. Instead, install your desired R and/or Python custom runtimes and packages. For more information, see Install SQL Server 2022 Machine Learning Services (Python and R) on Windows.

Once installed, the pretrained models are considered an implementation detail that power specific functions in the MicrosoftML (R) and microsoftml (Python) libraries. You should not (and cannot) view, customize, or retrain the models, nor can you treat them as an independent resource in custom code or paired other functions.

To use the pretrained models, call the functions listed in the following table.

R function (MicrosoftML) Python function (microsoftml) Usage
getSentiment get_sentiment Generates positive-negative sentiment score over text inputs.
featurizeImage featurize_image Extracts text information from image file inputs.

Prerequisites

Machine learning algorithms are computationally intensive. We recommend 16 GB of RAM for low-to-moderate workloads, including completion of the tutorial walkthroughs using all of the sample data.

You must have administrator rights on the computer and SQL Server to add pretrained models.

External scripts must be enabled and SQL Server LaunchPad service must be running. Installation instructions provide the steps for enabling and verifying these capabilities.

Download and install the latest cumulative update for your version of SQL Server. See the Latest updates for Microsoft SQL Server.

MicrosoftML R package or microsoftml Python package contain the pretrained models.

SQL Server Machine Learning Services includes both language versions of the machine learning library, so this prerequisite is met with no further action on your part. Because the libraries are present, you can use the PowerShell script described in this article to add the pretrained models to these libraries.

MicrosoftML R package contain the pretrained models.

SQL Server R Services, which is R only, does not include MicrosoftML package out of the box. To add MicrosoftML, you must do a component upgrade. One advantage of the component upgrade is that you can simultaneously add the pretrained models, which makes running the PowerShell script unnecessary. However, if you already upgraded but missed adding the pretrained models the first time around, you can run the PowerShell script as described in this article. It works for both versions of SQL Server. Before you do, confirm that the MicrosoftML library exists at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES\library.

Check whether pretrained models are installed

The install paths for R and Python models are as follows:

  • For R: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\library\MicrosoftML\mxLibs\x64

  • For Python: C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\Lib\site-packages\microsoftml\mxLibs

Model file names are in the following list:

  • AlexNet_Updated.model
  • ImageNet1K_mean.xml
  • pretrained.model
  • ResNet_101_Updated.model
  • ResNet_18_Updated.model
  • ResNet_50_Updated.model

If the models are already installed, skip ahead to the validation step to confirm availability.

Download the installation script

Visit https://aka.ms/mlm4sql to download the file Install-MLModels.ps1. On the GitHub page, select Download raw file.

Execute with elevated privileges

  1. Start PowerShell. On the task bar, right-click the PowerShell program icon and select Run as administrator.

  2. The recommended execution policy during installation is "RemoteSigned". For more information on setting the PowerShell execution policy, see Set-ExecutionPolicy. For example:

    Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

  3. Enter a fully qualified path to the installation script file and include the instance name. Assuming the Downloads folder and a default instance, the command might look like this:

    PS C:\WINDOWS\system32> C:\Users\<user-name>\Downloads\Install-MLModels.ps1 MSSQLSERVER

Output

On an internet-connected SQL Server Machine Learning Services default instance with R and Python, you should see messages similar to the following.

MSSQL14.MSSQLSERVER
     Verifying R models [9.2.0.24]
     Downloading R models [C:\Users\<user-name>\AppData\Local\Temp]
     Installing R models [C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\]
     Verifying Python models [9.2.0.24]
     Installing Python models [C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES\]
PS C:\WINDOWS\system32>

Verify installation

First, check for the new files in the mxlibs folder. Next, run demo code to confirm the models are installed and functional.

R verification steps

  1. Start RGUI.EXE at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64.

  2. Paste in the following R script at the command prompt.

    # Create the data
CustomerReviews <- data.frame(Review = c(
"I really did not like the taste of it",
"It was surprisingly quite good!",
"I will never ever ever go to that place again!!"),
stringsAsFactors = FALSE)

# Get the sentiment scores
sentimentScores <- rxFeaturize(data = CustomerReviews, 
                                mlTransforms = getSentiment(vars = list(SentimentScore = "Review")))

# Let's translate the score to something more meaningful
sentimentScores$PredictedRating <- ifelse(sentimentScores$SentimentScore > 0.6, 
                                        "AWESOMENESS", "BLAH")

# Let's look at the results
sentimentScores

  3. Press Enter to view the sentiment scores. Output should be as follows:

    > sentimentScores
                                        Review SentimentScore
1           I really did not like the taste of it      0.4617899
2                 It was surprisingly quite good!      0.9601924
3 I will never ever ever go to that place again!!      0.3103435
PredictedRating
1            BLAH
2     AWESOMENESS
3            BLAH

Python verification steps

  1. Start Python.exe at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\PYTHON_SERVICES.

  2. Paste in the following Python script at the command prompt.

    import numpy
import pandas
from microsoftml import rx_logistic_regression, rx_featurize, rx_predict, get_sentiment

# Create the data
customer_reviews = pandas.DataFrame(data=dict(review=[
            "I really did not like the taste of it",
            "It was surprisingly quite good!",
            "I will never ever ever go to that place again!!"]))

# Get the sentiment scores
sentiment_scores = rx_featurize(
    data=customer_reviews,
    ml_transforms=[get_sentiment(cols=dict(scores="review"))])

# Let's translate the score to something more meaningful
sentiment_scores["eval"] = sentiment_scores.scores.apply(
            lambda score: "AWESOMENESS" if score > 0.6 else "BLAH")
print(sentiment_scores)

  3. Press Enter to print the scores. Output should be as follows:

    >>> print(sentiment_scores)
                                            review    scores         eval
0            I really did not like the taste of it  0.461790         BLAH
1                  It was surprisingly quite good!  0.960192  AWESOMENESS
2  I will never ever ever go to that place again!!  0.310344         BLAH
>>>

Note

If demo scripts fail, check the file location first. On systems having multiple instances of SQL Server, or for instances that run side-by-side with standalone versions, it's possible for the installation script to mis-read the environment and place the files in the wrong location. Usually, manually copying the files to the correct mxlib folder fixes the problem.

Examples using pretrained models

The following link includes example code invoking the pretrained models.

Research and resources

Currently the models that are available are deep neural network (DNN) models for sentiment analysis and image classification. All pretrained models were trained by using Microsoft's Computation Network Toolkit (CNTK).

The configuration of each network was based on the following reference implementations:

  • ResNet-18
  • ResNet-50
  • ResNet-101
  • AlexNet

For more information about the algorithms used in these deep learning models, and how they are implemented and trained using CNTK, see these articles:

Related content