Author:
CSO & Co-Founder
Reading time:
Microsoft SQL Server 2017 enabled the 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 a vast amount of libraries that 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 the performance of all database processes.
To enable Python in SQL Server 2017 first you need to install ‘Machine Learning Services’.
On the Feature Selection page select Python (and SQL Server R Services if you need it) which is inside Database Engine Services -> Machine Learning Consulting (In-Database) tab.
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
After installation and enabling external scripts execution you need to restart SQL Server in SQL Server Configuration Manager:
Please check if script are enabled by running:
EXEC sp_configure ‘external scripts enabled’
The run_value should be set to 1.
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 a single value: hello 1.
As you can read input data are transferred to Python script in @input_data_1 parameter and can access 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 the “WITH RESULT SETS” statement.
See our machine learning services to find out more.
[1] Microsoft. Install SQL Server Machine Learning Services (Python and R) on Windows. URL: https://docs.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install?view=sql-server-2017. Accessed Jan 13, 2019.
Category: