On the 19th of April 2017, Microsoft held an online conference called Microsoft Data Amp to showcase how Microsoft’s latest innovations put data, analytics and artificial intelligence at the heart of business transformation. Microsoft has, over the last few years, made great strides in accelerating the pace of innovation to enable businesses to meet the demands of a dynamic marketplace and harness the incredible power of data—more securely and faster than ever before.
After the conference, there were a few questions some of us had, though. Is Microsoft SQL Server 2017 emerging as an enterprise solution for data science? Does it provide the required capabilities—is the engine capable of handling huge data? It seems the answer is “Yes”, as starting with the CTP 2.0 release of SQL Server 2017, Microsoft has brought Python-based intelligence to data in SQL Server.
Python has gathered a lot of interest recently as a language of choice for data analysis. This language has the right set of libraries for data analysis and predictive modeling, not to mention a simpler learning curve.
The growing trends of data science and modeling predict a massive growth in data in the upcoming years. The propulsion towards innovation and adaptation to leading trends in the data technology might intrigue us enough to make us take a look at the current release of SQL Server 2017.
Data science is a combination of Data Mining, Machine Learning, Analytics and Big Data. The integration of SQL 2016 with data science language, R, into database the engine provides an interface that can efficiently run models and generate predictions using SQL R services. Python builds on the foundation laid for R Services in SQL Server 2016, and extends that mechanism to include Python support for in-database analytics and machine learning.
Moreover, the R–Python integration in SQL Server, apart from emphasizing productivity and code readability, also can take advantages of parallel query processing, security and better resource governance.
Now, Microsoft has renamed R Services as Machine Learning Services, bringing R and Python under the umbrella. The renamed Microsoft Machine Learning Services component enables Python to run directly on the database server, or along with embedded T-SQL scripts.
Developers have used stored procedure called sp_execute_external_script to run R code, whose first parameter is @language. Microsoft had designed this stored procedure to be open-ended.
The engine inherits the features of R, in order to adopt Python. To run Python code in SQL Server, we have to install SQL Server 2017 CTP 2.0, with the Machine Learning Services with Python feature. It’s noteworthy that other versions of SQL Server do not support Python integration.
The article explores
- The installation of the database engine and the configuration of Machine Learning Services
- Configuration of Python
- Configuration of the instance to allow execution of scripts that use an external executable
- Data-type support
- Samples to understand the use of Python in SQL
Installation
- Download SQL Server 2017 CTP 2.0. During Feature Selection at the time of installation, Python is listed as a part of Machine Learning Services.
- Run the setup wizard for SQL Server 2017.
- On the Installation tab, click New SQL Server stand-alone installation or add features to an existing installation.
-
On the Feature Selection page, select both of the options as you see in the screenshot.
- Database Engine Services: To use Python with SQL Server, you must install an instance of the database engine.
- Machine Services (In-Database): This option installs the database services that support Python script execution.
- Python: Check this option to get the Python 3.5 executable and select libraries from the Anaconda distribution
On the below page, Consent to Install Python, click Accept.
On the Ready to Install page, verify that selected components are included
After a successful installation, the instance is ready for enabling an external script execution parameter.
To enable SQL Instance to run Python scripts:
- Open the SQL Server Management Studio.
-
Connect to the instance where Machine Learning Services is installed, and run the following command:
sp_configure
-
To enable the external scripting feature that supports Python, run the following statement.
EXEC sp_configure ‘external scripts enabled’, 1
RECONFIGURE WITH OVERRIDE - Restart the SQL Instance.
sp_execute_external_script
The sp_execute_external_script is a system stored procedure that executes with a provided R/Python script as an argument. To enable normal functioning of this external stored procedure, you must have administrator access to your SQL Server instance, so that you can run the sp_configure command. The procedure will invoke the launchpad service to the respective library for its execution.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
sp_execute_external_script @language = N'language' , @script = N'script', @input_data_1 = ] 'input_data_1' [ , @input_data_1_name = ] N'input_data_1_name' ] [ , @output_data_1_name = 'output_data_1_name' ] [ , @parallel = 0 | 1 ] [ , @params = ] N'@parameter_name data_type [ OUT | OUTPUT ] [ ,...n ]' [ , @parameter1 = ] 'value1' [ OUT | OUTPUT ] [ ,...n ] [ WITH <execute_option> ] [;] <execute_option>::= { { RESULT SETS UNDEFINED } | { RESULT SETS NONE } | { RESULT SETS ( <result_sets_definition> ) } } |
| Parameter | Purpose |
| @language = N’Python’ | Scripting language parameter, In this case it’s Python |
| @script = N’ ‘ | Python script body |
| @input_data_1 = N’ T-SQL Statement’ | The T-SQL statement reads data from SQL Table |
| @output_data_1_name = N’ Data Frame Name’ | Holds the data frame generated inside the Python Script. |
| WITH RESULT SETS ((Col1 DataType,Col2 DataType )) | Specifies the Output column and Datatype of the dataframe columns. This is optional. |
Execute Python Code in SQL Server
Microsoft has made it possible to embed Python code directly in SQL Server databases by including the code as a T-SQL stored procedure.
Datatype Support
Python supports a limited number of data types in comparison to SQL Server. As a result, whenever you use data from SQL Server in Python scripts, the data might be implicitly converted to a type compatible with Python. However, often an exact conversion cannot be performed automatically, and an error is returned. This table lists the implicit conversions that are provided. Other data types are not supported.
| SQLTYPE | PYTHON TYPE |
| bigint | numeric |
| binary | raw |
| bit | bool |
| char | str |
| float | float64 |
| int | int32 |
| nchar | str |
| nvarchar | str |
| nvarchar(max) | str |
| real | float32 |
| smallint | int16 |
| tinyint | uint8 |
| varbinary | bytes |
| varbinary(max) | bytes |
| varchar(n) | str |
| varchar(max) | str |
Examples
Print the input value
|
1 2 3 4 5 6 |
exec sp_execute_external_script @language =N'Python', @script=N'OutputDataSet = InputDataSet print("Input data is {0}".format(InputDataSet)) ', @input_data_1 = N'SELECT 1 as col' |
Find Mean of a given list
|
1 2 3 4 5 6 |
execute sp_execute_external_script @language = N'Python', @script = N' l = [15, 18, 2, 36, 12, 78, 5, 6, 9] print(sum(l) / float(len(l))) ' |
Format operator
|
1 2 3 4 5 6 7 |
exec sp_execute_external_script @language = N'Python' ,@script= N' print("""jan:{2} feb:{0} mar:{2} Apr:{1} May:{2} Jun:{1} Jul:{2} Aug:{2} Sep:{1} Oct:{2} Nov:{1} Dec:{2}""".format(InputDataSet.A, InputDataSet.B, InputDataSet.C)) ', @input_data_1 = N'select 28 as A ,30 as B,31 as C' |
Using loops and branches
|
1 2 3 4 5 6 |
execute sp_execute_external_script @language = N'Python', @script = N' for i in range(5): if i<3 : print("i is now:", i*2)' |
Passing a table as input and generating a computed column called bonus
- Create the EMP table
- Inserting dummy values
- Execute the Python script to generate the compute column
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
DROP TABLE IF EXISTS dbo.EMP GO CREATE TABLE [dbo].[EMP]( [empno] [int] NOT NULL, [ename] [varchar](10) NULL, [job] [varchar](9) NULL, [mgr] [int] NULL, [hiredate] [datetime] NULL, [sal] float NULL, [comm] [numeric](7, 2) NULL, [dept] [int] NULL, PRIMARY KEY CLUSTERED ( [empno] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO EMP VALUES (1,'Prashanth','ADMIN',6,'12-17-1990',18000,NULL,4) INSERT INTO EMP VALUES (2,'Jayaram','MANAGER',9,'02-02-1998',52000,300,3) INSERT INTO EMP VALUES (3,'thanVitha','SALES I',2,'01-02-1996',25000,500,3) SELECT EMPNO,ENAME,SAL from EMP EXECUTE sp_execute_external_script @language = N'Python', @script=N'OutputDataSet = InputDataSet for i in OutputDataSet["sal"]: OutputDataSet["Bonus"]=OutputDataSet["sal"]*0.05', @input_data_1 = N'SELECT [empno],[ename],sal,0 as Bonus from EMP' WITH RESULT SETS ((EMPNO int, ENAME varchar(10), SAL float, Bonus float)) |
Read content from a text file using the DictReader function.
|
1 2 3 4 5 6 7 8 9 |
EXECUTE sp_execute_external_script @language = N'Python' , @script = N' import csv with open(''e:\python.txt'') as f: reader = csv.DictReader(f, delimiter = ''\t'') for row in reader: print (row) ' |
The following sample shows how to import the Pandas library to get access to dataframes and do the following tasks
- Read CSV file using Pandas library
- Find the number of Rows using Pandas object pyo
- Fetch the first row of the csv file
- Calculate the mean of every statistical column
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
EXECUTE sp_execute_external_script @language = N'Python' , @script = N' import pandas pyo = pandas.read_csv("e:\InputServer_1.csv") print (pyo) #Finding the number of rows print (pyo.shape) #Looking at the first row of the data print (pyo.head(1)) #Find the average of each statistic print (pyo.mean()) ' |
Find the mean over the statistical column
The data is fed from the table and the computation happens using the Pandas library
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DROP TABLE IF EXISTS MyData; CREATE TABLE MyData([Col1] INT NOT NULL) ON [PRIMARY]; INSERT INTO MyData VALUES(1); INSERT INTO MyData VALUES(10); INSERT INTO MyData VALUES(100); GO -- print all rows of MyData table SELECT * FROM MyData; --Find mean of Col1 EXECUTE sp_execute_external_script @language = N'Python' , @script = N' import pandas print("*******************************") OutputDataSet = InputDataSet print (OutputDataSet) print (OutputDataSet.mean()) print("*******************************") ' , @input_data_1 = N'SELECT * from Mydata' |
Summary
This article covered how SQL Server 2017 introduces support for data analytics, and the use of Python in addition to R scripts. I’ve detailed the installation procedures, configuration details and execution of a sample Python SQL script.
The close integration of R/Python to SQL Server machine eliminates unnecessary movement of data across machines; think of moving millions/billions of rows to the client for modeling or scoring over the network—it is cumbersome, and a tedious job. This is one of the reasons why data scientists rely on sampling (Test set v/s Train Set). It’s a useful approach, especially where there are issues of data sovereignty and compliance. Your code runs inside the SQL Server security boundaries, triggered by a single call from the T-SQL stored procedures.
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021