fbpx

Microsoft SQL Server 2017 enabled execution of Python scripts within T-SQL via Machine Learning Services (R in-database is available from SQL Server 2016). The functionality of Python in-database is not only limited to machine learning, because Python has vast amount of libraries which can be installed in MS SQL Server using pip. In this article we demonstrate an example of using Python in the Advanced Analytics Extension.

Advantages of using Python in-database:

  • Adding Python execution to your SQL queries greatly reduce project complexity by closing all functionality in one tool (SQL Server).

  • Communication between Python and MSSQL Server is easier rather than relying on middleware.

  • Possibility to use local platform resources such as CPUs and GPUs.

  • Run different AI algorithms such as XGBoost, Random Forest, SVM, Neural Networks etc.

  • Integrate easily your Machine Learning solutions with Enterprise Data Warehouse.

Drawbacks of using Python in-database:

  • Python code in SQL query is passed as a string so you loose syntax highlighting which makes the code less readable.

  • A security risk is exposed because you need to allow external scripts execution.

  • Python calculations can be resource-intensive and affect performance of all database processes.

Installation of Python in-database

To enable Python in SQL Server 2017 first you need to install ‘Machine Learning Services’.

SQL Server installation

Source: Microsoft official website [1]

On the Feature Selection page select Python (and SQL Server R Services if you need it) which is inside Database Engine Services -> Machine Learning Services (In-Database) tab.

Machine Learning services

Source: Microsoft official website. [1]

Do not select options in Shared Feature which are intended for on a separate computer.

Python is installed in a folder called PYTHON_SERVICES (typically located at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\) where you should find a file python.exe.

To enable external scripts execution run the following query:

EXEC sp_configure ‘external scripts enabled’, 1 RECONFIGURE WITH OVERRIDE

Restart services

After installation and enabling external scripts execution you need to restart SQL Server in SQL Server Configuration Manager:

Enable external script execution

Please check if script are enabled by running:

EXEC sp_configure ‘external scripts enabled’

The run_value should be set to 1.

Verify installation

Now you can execute simple python or R code with below query:

EXEC sp_execute_external_script @language =N’Python’, @script=N’ OutputDataSet = InputDataSet; ‘, @input_data_1 = N’SELECT 1 AS hello’ WITH RESULT SETS (([hello] int not null)); GO

You should acquire single value: hello 1.

As you can read input data are transferred to Python script in @input_data_1 parameter and can accessed in Python as a Pandas DataFrame InputDataSet. Analogically output Pandas DataFrame should be named OutputDataSet, but SQL Server would know only values of the output data. You should define column names and types in “WITH RESULT SETS” statement.

If you are planning to start Machine Learning project check for the Machine Learning consulting or plan ML project properly.

Data sources:

  1. https://docs.microsoft.com/en-us/sql/advanced-analytics/install/sql-machine-learning-services-windows-install?view=sql-server-2017

Related articles

Planning AI or BI project? Get an Estimate

Get a quick estimate of your AI or BI project within 1 business day. Delivered straight to your inbox.