Using Python scripts natively in SQL Server 2017
In a recent blog post I covered how to get started with C# common language runtimes. I mentioned that the flavors available are C# and Visual basic, this technology began shipping with SQL Server 2005.
However a more recent development in the SQL server world began shipping with SQL Server 2017 and this is availability for Python and R scripts to run natively in your SQL instance.
This is extremly interesting because now you can develop stored procedures that utalize all the machine learning models that you have developed natively. And your OLTP data can be ran through the models natively on your server.
To get things started I will just show a few high level steps of how to get your SQL instance primed for this.
Firstly your going to need a SQL server 2017 instance, I learned that SQL express editions do not carry the functionaility for Python and R scripting. So you can download a 180 day eval edition here.
The main thing to be concerned with here under feature selection is to click the check for Python under database engine services.
Additionally when your instance has finished installing enable external scripts.
sp_configure 'external scripts enabled', 1;
RECONFIGURE WITH OVERRIDE;
Once that is complete, a restart for the instance will need to be performed.
Here is an example to check the location of the install for specifically the SQL Server 2017 Python install.
A quick note here that if you run anaconda or Python on the client machine already, the dependencies that you have will not be carried over to this new SQL Server 2017 Python. It is completly different and you will need to manage the libraries seperately!
EXEC sp_execute_external_script
@language =N'Python',
@script=N'import sys; print("\n".join(sys.path))'
Now you have the install location you can open a CMD prompt and cd the directory and then issue a pip.exe command to install a new module. Here is a example:
cd C:\Program Files\Microsoft SQL Server\MSSQL14.FOGELDEV2017\PYTHON_SERVICES\Scripts
pip.exe install text-tools
And thats it congratulations, you are all setup to run Python on your SQL instance!
It is possible to output data straight to the query grid like you would a normal procedure in SQL Server. This opens up the flood gates in terms of data warehousing, model performance and so much more interesting functionality that is available in Python.
Passing data to a Python CLR Proc, here I wrote a function to count the frequency of characters in the text passed to the proc. The results are then translated to a pandas dataframe and returned in the format specified of WITH RESULT SETS:
CREATE PROCEDURE CountChars (
@param1 VARCHAR(MAX)
)
AS
EXECUTE sp_execute_external_script @language = N'Python'
, @script = N'
import pandas as pd
freq_count = [(c, text.count(c)) for c in text if c.isalpha()]
OutputDataSet = sorted(list(set(freq_count)), key=lambda x: (-x[1], x[0]))
df = pd.DataFrame(OutputDataSet)
OutputDataSet = df
'
, @input_data_1 = N' ;'
, @params = N' @text VARCHAR(MAX)'
, @text = @param1
WITH RESULT SETS(([Letter] CHAR(20) NOT NULL,[Count] CHAR(20) NOT NULL));
exec CountChars 'super fun sql'
