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.
About the Author