Using R and Python in Microsoft SQL Server 2022

This article is originally published at https://tomaztsql.wordpress.com

In the late November 2022 Microsoft announced, that Microsoft SQL Server 2022 is generally available. New version brings many great features, and upgrades the existing ones. For the purpose of this blog post, I am using a Developer Edition of SQL Server 2022.

So what changes bring MSSQL2022 to R and Python?

The first bigger change is the removal or R, Python and Java from the installation process, as we were used to in SQL server 2016, 2017, and 2019.

Machine Learning Services and language extensions is available under Database Engine Services, and if you want to use any of these languages, check this feature. During the installation process, the R, Python or Java will not be installed (nor asked for permissions), but you will install your own runtime after the installation. This will bring you more convenience with the installation of different R/Python/Java runtimes.

After the selection of the desired features, you will see, that SQL Server 2022 still uses SQL Server Launchpad Service and creates a dedicated service account. Denoting that the underlying concept of communicating with R, Python or Java engine uses the same service as in previous versions (2016-2019).

After the completion of the installation, you will receive information (recapitulation) on installed features and the Machine learning Services and Language extensions are successfully installed.

Before we go any further, the configuration is still mandatory!

Mandatory configuration

USE [master];
GO

sp_configure 'show advanced options',1;
GO

RECONFIGURE;
GO

sp_configure 'external scripts enabled', 1;
GO

RECONFIGURE;
GO

And if we try to run a simple R command using sp_execute_external_script

 EXEC   sp_execute_external_script  
       @language = N'R'  
     , @script = N'iris_data <- iris;'
     , @input_data_1 = N''  
     , @output_data_1_name = N'iris_data'
     WITH RESULT SETS ((
        "Sepal.Length" float not null,
       "Sepal.Width" float not null,  
       "Petal.Length" float not null,
      "Petal.Width" float not null, 
      "Species" varchar(100)
));  

we clearly get an error message (assuming similar message would appear running Python or Java).

So, we need to do some additional installation in order for the machine learning services to work!

Additional configuration with SQL server 2022

Installing R

1. Assuming you are doing a clean installation, you would want to download the R engine. Head to CRAN and download the latest version of R for Windows. At the time of writing this blog post, I installed a fresh R version 4.2.2.

2. Run R.exe (I have installed my R engine to the location: “C:\Program Files\R\R-4.2.2\bin“) and install the following R packages:

# install these packages
install.packages("iterators")
install.packages("foreach")
install.packages("R6")
install.packages("jsonlite")
install.packages("https://aka.ms/sqlml/r4.2/windows/CompatibilityAPI_1.1.0.zip", repos=NULL)
install.packages("https://aka.ms/sqlml/r4.2/windows/RevoScaleR_10.0.1.zip", repos=NULL)

With the packages being successfully installed.

Please note, that you can also semi-skip this part and use your already installed R version, as long as you have all the packages above installed.

• 3. Configure your R engine with SQL Server 2022 by running the RegisterRext.exe command. You will find this installed in the RevoScaleR package folder in library folder for your installed (selected) R engine.

Mine is available in “C:\Program Files\R\R-4.2.2\library\RevoScaleR\rxLibs\x64“. Open the cmd from this location and run the following command:

.\RegisterRext.exe /configure /rhome:"%ProgramFiles%\R\R-4.2.2" /instance:"MSSQLSERVER"

Please note (!!) that prior to running this command you will need to change this command to your environment settings:

• Rhome path: ProgramFiles%\R\R-4.2.2
• SQL Server Instance name: MSSQLSERVER

The whole configuration in CMD looks like:

After successful configuration, make sure to restart the SQL Server Service:

After the installation and configuration, you can re-run the T-SQL sp_execute_external_script. And you will get the results back

Installing Python

1. Assuming you are doing a clean installation, you would want to download the Python interpreter. Head to Python.org and download the latest version of Python for Windows. At the time of writing this blog post, I installed a fresh Python version 3.11.0.

2. Run Python.exe (I have installed my Python interpreter to the location: “C:\Users\tomazkastrun\AppData\Local\Programs\Python\Python311“) and pip install the revoscalepy package.

Head to your python folder and run the following pip command:

python -m pip install https://aka.ms/sqlml/python3.10/windows/revoscalepy-10.0.1-py3-none-any.whl

3. Configure your R engine with SQL Server 2022 by running the RegisterRext.exe command. You will find this installed in the RevoScaleR site-package folder in Libs folder for your installed (selected) Python interpreter (my full path: “C:\Users\tomazkastrun\AppData\Local\Programs\Python\Python311\Lib\site-packages\revoscalepy\rxLibs”).

Run the following command:

.\RegisterRext.exe /configure /pythonhome:"C:\Users\tomazkastrun\AppData\Local\Programs\Python\Python311" /instance:"MSSQLSERVER"

Please note (!!) that prior to running this command you will need to change this command to your environment settings:

• Python home path: C:\Users\tomazkastrun\AppData\Local\Programs\Python\Python311
• SQL Server Instance name: MSSQLSERVER

And the configuration is completed. One last thing you need to do, is to restart the MSSQLSERVER service!

Now run some Python code:

EXECUTE sp_execute_external_script 
  @language = N'Python'
, @script = N'
a = 1
b = 2
c = a/b
print(c)'

Happy scripting with R and Python in SQL Server 2022 and stay healthy!

Thanks for visiting r-craft.org
This article is originally published at https://tomaztsql.wordpress.com
Please visit source website for post related comments.