BLOGS

R Plots in SQL Server 2016

As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.

It makes the R integration with SQL Server 2016 is easy.

As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.

Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.

I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.

Solution 1:

Step 1: Create a sample database and table. Insert some values into the table.

Step 2: Do the modification on the table name and column name and run it.

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df$Values);

dev.off();

OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

‘

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 3: Make sure that you are getting an varbinary result as a output.

Step 4: Wrap the above code within stored procedure, like below and check the result again.

CREATE PROCEDURE Sp_Test1

As

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df$Values); #column name

dev.off();

OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

‘

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 5: Use below bcp command and convert the Varbinay into jpeg format.

bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name>  -U <user name> -P <password>

Step 6: Verify the plot in a above image.

 

Solution 2:

Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.

Step 2: Verify the plot.

 

Solution 3:

We can export the result as an image file without using bcp tool itself.

Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image.

Step 2: Run the script and check the above specified location. Image file will be available with plot.