Install pre-trained machine learning models on SQL Server

APPLIES TO: yesSQL Server (Windows only) noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This article explains how to use Powershell to add free pre-trained machine learning models for sentiment analysis and image featurization to a SQL Server instance having R or Python integration. The pre-trained 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.

Once installed, the pre-trained 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 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 pre-trained 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.

MicrosoftML R package or microsoftml Python package contain the pre-trained models.

  • SQL Server 2017 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 pre-trained models to these libraries.

  • SQL Server 2016 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 pre-trained models, which makes running the PowerShell script unnecessary. However, if you already upgraded but missed adding the pre-trained 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 pre-trained 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 listed below:

  • 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

Click https://aka.ms/mlm4sql to download the file Install-MLModels.ps1.

Execute with elevated privileges

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

  2. 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 2017 Machine Learning 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 pre-trained models

The following link include 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 pre-trained models were trained by using Microsoft's Computation Network Toolkit, or 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:

See also