Page Contents
- This blog is an extension of PIVOT, Multi aggregate Pivot & Dynamic Pivot in SQL Server
Summary
In SQL Server 2016, Microsoft introduced a new system stored procedure sys.sp_execute_external_script. The idea was to extend the capabilities of SQL Server engine to be able to execute external code i.e code written in R, Java, or Python. SQL 2017 supports R and Python. The new functionality is a part of Sql Server’s Machine Learning Services. The purpose of this blog is to “tickle devs imagination” on how to use Python for Pivoting and more..
From a super high-level point of view, the process goes like this: we call sys.sp_execute_external_script indicating that we want to use e.g Python language, and pass in our python code. We also define a data set(an Sql query) that the code will use as an input data source. The code performs analytical tasks over the input data source and returns a result-set in the form of a pandas DataFrame. We use python’s methods to “tweak” the data frame to match the final shape of the output sql dataset. Optionally, we describe the output(define column names and their data types) by using WITH RESULT SET stored procedure option.
So, I thought it would be cool to try to do pivoting/multi-pivoting using Python code. What I discovered are the amazing things you can do with Python in SQL Server.
NOTE: More information about how Sql Server engine executes external code can be found here.
Prepare the environment for Python
First thing, we need to install Sql Server Machine Learning Services and Language Extensions.
Figure 8, Sql Server ML Services
Make sure that the SQL Server Launchpad service is up and running.
The next step is to allow Sql Server to execute the external scripts and we are good to go.
sp_configure
@configname = 'show advanced options'
,@configvalue = 1
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure
@configname = 'external scripts enabled'
,@configvalue = 1
GO
RECONFIGURE WITH OVERRIDE;
GO
Python’s Pivot
Let us present the sum of freight(Shipping cost) values per order year for each country that ordered our products, but this time using Python instead tSQL’s PIVOT operator – you can find the tSQL example here.
Set up dbo.Orders_TestPivot test table and run the python script.
Figure 1, Python’s pivot result
Note: During the testing, I found it difficult to use only SSMS to write Python code (similar to working with dynamic sequel) with no debugger, IntelliSense, etc. I used the Visual Studio Code tool with Python 3.8. Here is the code I used for testing.
The system stored procedure sp_execute_external_script is similar to sp_executesql, but along with the code to be executed, parameter definitions, and parameter values, we also pass the following values(from our pivot script):
@input_data_1 – There are a couple of interesting things with the query used as a base for Python Pivoting.
- Python does define Pivot grouping element, therefore, we don’t need a table expression that implicitly defines Pivot elements where the grouping element is everything else but spreading and aggregate element – see pivot operation directly on a table.
- The query result-set(in our case named df) is internally transformed to DataFrame object – a table-like structure defined within pandas. Pandas is an open-source data analysis library build on top of the Python language. DataFrame does not support all Sql Server data types e.g MONEY and DECIMAL are not supported and that’s why the two columns Freight and OrderValue need to be converted to FLOAT.
Supported types : Supported types: bit, tinyint, smallint, int, bigint, uniqueidentifier, real, float, char, varchar, nchar, nvarchar, varbinary, date, datetime, smalldatetime.
How it works
As mentioned before, after passing the input query, the query gets executed and the resultset, natively in a form of a table expression, gets transformed into a DataFrame object named df. The code below runs the pivot_table method(far more powerful than tSQL’s PIVOT operator 🙂 ) on the DataFrame object. The final result is then stored in the dfpivot_out variable of type DataFrame, previously defined as an output dataset name.
....
,@script = N'
dfpivot_out = df.pivot_table(index = ["Shipcountry"], \
columns =["OrderYear"], \
values = ["Freight"], \
aggfunc={"Freight":sum}, \
fill_value=None).reset_index(level="Shipcountry")
## dfpivot_out =dfpivot_out.reset_index(level="Shipcountry") ##we can reshape the data frame in a separate statement.
print(dfpivot_out)
'
....
Note: Python code above starts with no indentation 🙂
Pivot_table
In our example, we are passing four parameters to the pivot_table method.
index – This parameter explicitly defines a list of grouping element(s). Due to the difference between DataFrame and sequel’s table expression structures, the Index column will not be visible in the final output (see reset_index method)
columns – defines a list of spreading elements.
values – defines a list of columns whose values will be aggregated.
aggfunc – defines a list of pairs (value column: aggregate function). Basically, we can apply different aggregate functions on different aggregate columns defined in the values list.
Before explaining the reset_index() method, remove the method from the code and comment out WITH RESULT SET option.
...
values = ["Freight"], \
aggfunc={"Freight":sum}) ##.reset_index(level="Shipcountry")
...
,@output_data_1_name =N'dfpivot_out'
WITH --RESULT SETS ((Shipcountry NVARCHAR(15),[2018] MONEY, [2019] MONEY,[2020] MONEY));
After running the code, have a look at the result of the print statement under the Messages pane in SSMS. This is how DataFrame graphically looks like
Figure 2, panda’s DataFrame shape
The index values are not presented as a DataFrame column. There are many ways to manipulate the DataFrame output to match the sql result-set shape. One way is to use reset_index(level=”Shipcountry”) method on the DataFrame. This will “convert” the index into a column. The new, default index will be created with the unique, ever-increasing integer values, starting from 0.
Run the code in its original form and compare the print output.
Multi aggregate Pivot with Python
This time we want to calculate the total Freight and the average order value in different countries per year. Again, compare the tSQL approach with the Python code.
Compare tSQL example with the Python code. (Just a few “tweaks” to the code above and there you go 🙂 )
...
,@script = N'
import numpy as np
dfpivot_out = df.pivot_table(index = ["Shipcountry"], \
columns =["OrderYear"], \
values = ["Freight","OrderValue"], \
aggfunc={"Freight":sum,"OrderValue":np.average}).reset_index(level="Shipcountry")
print(dfpivot_out)
'
...
WITH RESULT SETS ((Shipcountry varchar(2000),[2018] FLOAT, [2019] FLOAT,[2020] FLOAT
,[Avg Order Value(2018)] FLOAT
,[Avg Order Value(2019)] FLOAT
,[Avg Order Value(2020)] FLOAT))
Note: For this example, I’ve imported another python library, – numpy, to be able to use its average aggregate function.
… and here is another one.
Find total Freight and average order value in different countries and different regions per year. The code can be found here.
Conclusion
Playing with pivot operations is just a tip of the iceberg. There are many different functions available in Python that we can use in SQL Server for all sorts of data analysis. The good thing is that the data does not need to be moved away from SQL Server. However, It is still important to completely understand how python code executes in SQL environment i.e performance impact on the existing workload etc. Nevertheless, I found Python very intuitive and easy to work with, so, sorry c#, but I seem to have found a new second-best friend 🙂
Thanks for reading.
Dean Mincic