<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/"></div></div><div><a data-url="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/" data-counturl="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/" data-count="horizontal" data-text="FileTable and storing graphs from Microsoft R Server" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2016/09/25/filetable-and-storing-graphs-from-microsoft-r-server/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>FileTable has been around now for quite some time and and it is useful  for storing files, documents, pictures and and binary files in a designated SQL Server table – FileTable. The best part of FileTable is the fact one can access it from windows or other application as if it were stored on file system (because they are) and not making any other changes on the client.</p>
<p>And this feature is absolutely handy for using and storing outputs from Microsoft R Server. In this blog post I will focus mainly on persistently storing charts from statistical analysis.</p>
<p>First we need to secure that FileStream is enabled. Open <a target="_blank" href="https://msdn.microsoft.com/en-us/library/ms174212.aspx">SQL Server Configuration Manager</a> and navigate to your running SQL Server. Right click and select FILESTREAM and enable Filestream for T-SQL access and I/O access. In addition, allow remote clients access to Filestream data as well.</p>
<p><img alt="2016-09-23-21_53_34-sqlquery9-sql-sicn-kastrun-master-spar_si01017988-63_-microsoft-sql-serv" class="wp-image-2501" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-23-21_53_34-sqlquery9-sql-sicn-kastrun-master-spar_si01017988-63_-microsoft-sql-serv.png?resize=456%2C378&ssl=1" data-recalc-dims="1"></p>
<p>Next step is to enable the configurations in Management Studio.</p>
<pre>EXEC sp_configure 'filestream_access_level' , 2;
GO
RECONFIGURE;
GO</pre>
<p>For this purpose I have decided to have a dedicated database for storing charts created in R. And this database will have FileTable enabled.</p>
<pre>USE master;
GO

CREATE DATABASE FileTableRChart 
ON PRIMARY  (NAME = N'FileTableRChart', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileTableRChart.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = ChartsFG, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\RCharts')
LOG ON (NAME = N'FileTableRChart_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\FileTableRChart_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO

ALTER DATABASE FileTableRChart
    SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'RCharts' )</pre>
<p>So I will have folder RCharts available as a BLOB storage to my FileTableRChart SQL server database. Adding a table to get all the needed information on my charts.</p>
<pre>USE FileTableRChart;
GO

CREATE TABLE ChartsR AS FILETABLE
WITH (
 FileTable_Directory = 'DocumentTable'
,FileTable_Collate_Filename = database_default  
);
GO</pre>
<p>Setting the BLOB, we can focus now on R code within T-SQL. Following R Code will be used to generated histograms with normal curve for quick data overview (note, this is just a sample):</p>
<pre>x &lt;- data.frame(val = c(1,2,3,6,3,2,3,4,5,6,7,7,6,6,6,5,5,4,8))
y &lt;- data.frame(val = c(1,2,5,8,5,4,2,4,5,6,3,2,3,5,5,6,7,7,8))
x$class &lt;- 'XX'
y$class &lt;- 'YY'
d &lt;- rbind(x,y)

#normal function with counts
gghist &lt;- ggplot(d, aes(x=val)) + geom_histogram(binwidth=2, 
                  aes(y=..density.., fill=..count..))
gghist &lt;- gghist + stat_function(fun=dnorm, args=list(mean=mean(d$val), 
                   sd=sd(d$val)), colour=&quot;red&quot;)
gghist &lt;- gghist + ggtitle(&quot;Histogram of val with normal curve&quot;)  + 
                   xlab(&quot;Variable Val&quot;) + ylab(&quot;Density of Val&quot;)</pre>
<p>Returning diagram that will be further parametrized when inserted into T-SQL code.</p>
<p><img alt="hist_norm_curv" class="wp-image-2527" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/09/hist_norm_curv.png?resize=456%2C449&ssl=1" data-recalc-dims="1"></p>
<p>Besides parametrization, I will add a function to loop through all the input variables and generated diagrams for each of the given variable/column in SQL Server query passed through sp_execute_external_script stored procedure.</p>
<p>Final code:</p>
<pre>DECLARE @SQLStat NVARCHAR(4000)
SET @SQLStat = 'SELECT
                     fs.[Sale Key] AS SalesID
                    ,c.[City] AS City
                    ,c.[State Province] AS StateProvince
                    ,c.[Sales Territory] AS SalesTerritory
                    ,fs.[Customer Key] AS CustomerKey
                    ,fs.[Stock Item Key] AS StockItem
                    ,fs.[Quantity] AS Quantity
                    ,fs.[Total Including Tax] AS Total
                    ,fs.[Profit] AS Profit

                    FROM [Fact].[Sale] AS  fs
                    JOIN dimension.city AS c
                    ON c.[City Key] = fs.[City Key]
                    WHERE
                        fs.[customer key] &lt;> 0'

DECLARE @RStat NVARCHAR(4000)
SET @RStat = 'library(ggplot2)
              library(stringr)
              #library(jpeg)
              cust_data &lt;- Sales
              n &lt;- ncol(cust_data)
              for (i in 1:n) 
                        {
                          path &lt;- 
''\\\\SICN-KASTRUN\\mssqlserver\\RCharts\\DocumentTable\\Plot_''
                          colid   &lt;- data.frame(val=(cust_data)[i])
                          colname &lt;- names(cust_data)[i]
                          #print(colname)
                          #print(colid)
                          gghist &lt;- ggplot(colid, aes(x=val)) + 
geom_histogram(binwidth=2, aes(y=..density.., fill=..count..))
                          gghist &lt;- gghist + stat_function(fun=dnorm, 
args=list(mean=mean(colid$val), sd=sd(colid$val)), colour=&quot;red&quot;)
                          gghist &lt;- gghist + ggtitle(&quot;Histogram of val with 
normal curve&quot;)  + xlab(&quot;Variable Val&quot;) + ylab(&quot;Density of Val&quot;)
                          path &lt;- paste(path,colname,''.jpg'')
                          path &lt;- str_replace_all(path,&quot; &quot;,&quot;&quot;)
                          #jpeg(file=path)
                          ggsave(path, width = 4, height = 4)
                          plot(gghist)
                          dev.off()
                        }';

EXECUTE sp_execute_external_script
     @language = N'R'
    ,@script = @RStat
    ,@input_data_1 = @SQLStat
    ,@input_data_1_name = N'Sales'</pre>
<p>I am using ggsave function, but jpeg function from package jpeg is also an option. Matter of a flavour. And variable path should be pointing to your local FileTable directory.</p>
<p>Now I can have graphs and charts stored persistently in filetable and retrieving information on files with simple query:</p>
<pre>SELECT FT.Name
,IIF(FT.is_directory=1,'Directory','Files') [File Category]
,FT.file_type [File Type]
,(FT.cached_file_size)/1024.0 [File Size (KB)]
,FT.creation_time [Created Time]
,FT.file_stream.GetFileNamespacePath(1,0) [File Path]
,ISNULL(PT.file_stream.GetFileNamespacePath(1,0),'Root Directory') [Parent Path]
FROM [dbo].[ChartsR] FT
LEFT JOIN [dbo].[ChartsR] PT
ON FT.path_locator.GetAncestor(1) = PT.path_locator</pre>
<p><img alt="2016-09-25-13_33_40-using_filetable_to_store_graphs_generated_with_revoscale-sql-sicn-kastrun-file" class="wp-image-2544" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-25-13_33_40-using_filetable_to_store_graphs_generated_with_revoscale-sql-sicn-kastrun-file.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Going through the charts could be now much easier for multiple purposes.</p>
<p><img alt="2016-09-25-13_35_08-edit-post-tomaztsql-wordpress-com" class="wp-image-2547" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-25-13_35_08-edit-post-e280b9-tomaztsql-e28094-wordpress-com.png?resize=456%2C330&ssl=1" data-recalc-dims="1"></p>
<p>There might be some security issues: I have used mklink to create a logical drive pointing to FileTable directory.</p>
<p><img alt="2016-09-25-12_05_54-administrator_-command-prompt" class="wp-image-2551" src="https://i0.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-25-12_05_54-administrator_-command-prompt.png?resize=456%2C159&ssl=1" data-recalc-dims="1"></p>
<p>You might also want to use Local group policy editor for MSSQLLaunchpad to have access granted (write permissions) to FileTable directory.</p>
<p><img alt="2016-09-25-13_37_41-local-group-policy-editor" class="wp-image-2555" src="https://i1.wp.com/tomaztsql.files.wordpress.com/2016/09/2016-09-25-13_37_41-local-group-policy-editor.png?w=456&ssl=1" data-recalc-dims="1"></p>
<p>Code is available at <a target="_blank" href="https://github.com/tomaztk/FileTable_graphs_Microsoft_R_Server">GitHub</a>.</p>
<p>Happy R-SQLing!</p>
<p> </p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/2438/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/2438/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/"></div></div><div><a data-url="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/" data-counturl="https://www.r-bloggers.com/filetable-and-storing-graphs-from-microsoft-r-server/" data-count="vertical" data-text="FileTable and storing graphs from Microsoft R Server" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2016/09/25/filetable-and-storing-graphs-from-microsoft-r-server/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/jpMkFqqStIw"></a></div>