Creating Bar Graph In SQL Server 2017 Using R
l:Creating a bar graph with R in SQL Server 2017 is relatively easy. It’s just a matter of finding the correct functions, components, and properties of the package of your choice. For our example here, I am using ggplot2 which is probably the most popular plotting system for R.
Let’s create a quick colorful bar graph. This should demonstrate how easy it is to use ggplot2 in SQL Server 2017 with R. “Easy”, of course, is a relative term. Well, it’s not easy on the onset as you have to get yourself familiarized with all the components of the package. But once you’re doing this on regular basis, you’ll get the hang of it. Think of the time when you’re still learning how to write your first Select statement in SQL.
Cheesy Bar Graph
Let’s begin with the end in mind. Here’s our cheesy colorful bar graph:
Install Package
Let’s install the package if it hasn’t been installed yet. The easiest way to do that is to run RGUI.exe that came with your SQL Server 2017 In-Database Machine Learning installation. You can find it here:
C:\Program Files\MSSQL14.MSSQLSERVER\R_SERVICES\bin\x64
Take note that you need to run the executable as Administrator. Also, if you’ve installed the R engine prior to your SQL Server 2017 In-Database Machine Learning with R, you have to explicitly tell the R package installer where you want your package installed.
> install.packages("ggplot2", lib="C:\\Program Files\\Microsoft SQL Server\\MSSQL14.MSSQLSERVER\\R_SERVICES\\library", dep = TRUE)
dep = TRUE tells the installer to install dependencies. ggplot2 depends on a lot of other packages. You can check dependencies using MiniCRAN.
Dataset
Our dataset is based on the WorldWideImporters database. Here we’ve got the Sum of Line Profit per Stock Group:
USE WideWorldImporters;
GO
SELECT sg.StockGroupName,
SUM(il.LineProfit) AS LineProfit
FROM Sales.InvoiceLines il
JOIN Warehouse.StockItems si
ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
ON sis.StockGroupID = sg.StockGroupID
GROUP BY sg.StockGroupName;
Setup
Let’s create a directory for the graphics output -C:\Marlon_Test.
Here’s the script that we pass to @script parameter for sp_execute_external_script:
library(ggplot2); chartStockGroupProfit <- "C:\\Marlon_test\\StockGroupProfit_001.png" png(filename=chartStockGroupProfit, width=1000, height=800) StockGroupProfit <- InputDataSet StockGroupProfitChart <- ggplot(data=StockGroupProfit, aes(x=StockGroupName, y=LineProfit)) + geom_bar(stat="identity") print(StockGroupProfitChart)
Where:
chartStockGroupProfit - Filename
StockGroupProfit - Data Frame based on the InputDataSet (see Select Statement above)
StockGroupProfitChart - The actual chart
Create the Bar Graph
Let’s put that together. Note: Although you specified you wanted to install the dependencies for the ggplot2 package (dep = TRUE), the installer in some cases might not install all of them. If the ggplot2 fails with "missing package" error, just install the specified package using RGui.exe (see above).
EXEC sp_execute_external_script
@language = N'R',
@script = N' library(ggplot2); chartStockGroupProfit <- "C:\\Marlon_test\\StockGroupProfit_001.png" png(filename=chartStockGroupProfit, width=1000, height=800) StockGroupProfit <- InputDataSet StockGroupProfitChart <- ggplot(data=StockGroupProfit, aes(x=StockGroupName, y=LineProfit)) + geom_bar(stat="identity") print(StockGroupProfitChart)', @input_data_1 = N' SELECT sg.StockGroupName, SUM(il.LineProfit) AS LineProfit
FROM Sales.InvoiceLines il JOIN Warehouse.StockItems si ON il.StockItemID = si.StockItemID JOIN Warehouse.StockItemStockGroups sis ON si.StockItemID = sis.StockItemID JOIN Warehouse.StockGroups sg ON sis.StockGroupID = sg.StockGroupID
GROUP BY sg.StockGroupName;';
Bar Graph Format
You may argue that the above graph looks a lot better than our colorful graph at the top of this blog. But arguments aside, let’s put on some formatting.
You may notice that the Y-Axis (Line Profit) is not in Dollar ($) format. The Packaging Material group is more than $50,000,000.00.
Well, you can argue about the aesthetic of the graph, but you may agree that the exponential format of the Line Profit is not quite intuitive.
If we divide the LineProfit by the exponential value of 1e6 (1 times 10 to the power 6):
aes(x=StockGroupName, y=LineProfit/1e6)
To format that value in Dollars ($), we need to add another library for the script — library(scales).
Then we can do:
scale_y_continuous(labels = dollar)
Rename Labels
You may notice that the Y-Axis Label says “LineProfit/1e+06”. That can easily be renamed by using the lab() function:
labs(x="Product Groups", y="Line Profit (Millions)")
To format the data frame labels, use the theme() function:
theme(text = element_text(size=20, color="blue"), axis.text.y = element_text(size=18, face="bold"), axis.text.x = element_text(size=18, face="bold", angle=80, vjust = 0.5))
Then, we should have something like this:
Reorder Bar Graph
Just to have the graph easy to read, let’s reorder the bar in descending order:
aes(x=reorder(StockGroupName,-LineProfit / 1e6), y=LineProfit / 1e6))
Rainbow Magic
To have that rainbow effect on the bars, modify the geom_bar() like this:
geom_bar(stat="identity",fill = rainbow(n=length(StockGroupProfit$LineProfit)))
Putting it all together
Here’s the complete R code:
EXEC sp_execute_external_script
@language = N'R',
@script = N' # Use ggplot2/scales library(ggplot2); library(scales) # Create chart file (folder must be existing) chartfile <- "C:\\Marlon_test\\StockGroupProfit.png" png(filename=chartfile, width=1000, height=800) # Define data frame StockGroupProfit <- InputDataSet # Build chart StockGroupProfitChart <- ggplot(data=StockGroupProfit, aes(x=reorder(StockGroupName,-LineProfit / 1e6), y=LineProfit / 1e6)) + geom_bar(stat="identity",fill = rainbow(n=length(StockGroupProfit$LineProfit))) + theme(text = element_text(size=20, color="blue"), axis.text.y = element_text(size=18, face="bold"), axis.text.x = element_text(size=18, face="bold", angle=80, vjust = 0.5)) + labs(x="Product Groups", y="Line Profit (Millions)") + scale_y_continuous(labels = dollar) print(StockGroupProfitChart)', @input_data_1 = N' SELECT sg.StockGroupName, SUM(il.LineProfit) AS LineProfit FROM Sales.InvoiceLines il JOIN Warehouse.StockItems si ON il.StockItemID = si.StockItemID JOIN Warehouse.StockItemStockGroups sis ON si.StockItemID = sis.StockItemID JOIN Warehouse.StockGroups sg ON sis.StockGroupID = sg.StockGroupID GROUP BY sg.StockGroupName;';
There you go. Happy plotting!
Originally published at SQL, Code, Coffee, Etc..
