<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/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In my previous blog post, <a target="_blank" rel="nofollow" href="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script">Saving input and output with sp_execute_external_script</a>, I was exploring the possibilities how to capture the R code that external procedure sends it internally to the <em>Launchpad.exe</em> program.  And a blog comment by reader Bob gave me additional push to write the second part to same topic. Thank you Bob for sharing this with us.</p>
<p>Bob was explained that how they are doing, and you can read all about it <a target="_blank" rel="nofollow" href="https://tomaztsql.wordpress.com/2017/04/10/saving-input-and-output-with-sp_execute_external_script/comment-page-1/#comment-262">here</a>. To recap, R code (as well as any additional packages) is stored on local file system, it gets copied, zipped, transferred and inserted to SQL table. Calling sp_execute_external_script everything gets unzipped and executed.</p>
<p>I will not comment on the solution Bob provided, since I don’t know how their infrastructure, roles, security is set up. At this point, I am grateful for his comment. But what I will comment, is that there is no straightforward way or any out-of-the-box solution. Furthermore, if your R code requires any additional packages, storing the packages with your R code is not that bad idea, regardless of traffic or disk overhead. And versioning the R code is something that is for sure needed.</p>
<p>To continue from previous post, getting or capturing R code, once it gets to Launchpad, is tricky. So storing R code it in a database table or on file system seems a better idea.</p>
<p>Starting with original sample R code:</p>
<pre>EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = N'
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3))
       c
       OutputDataSet &lt;- c'
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'

WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>We can create SQL table for R code to be persistent and always available.</p>
<pre>CREATE TABLE R_code 
(id INT
,R NVARCHAR(MAX))
INSERT INTO R_code
SELECT 1, '
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3))
       c
       OutputDataSet &lt;- c'
-- (1 row(s) affected)

DECLARE @r_code NVARCHAR(MAX)
SELECT @r_code = R FROM R_code WHERE id = 1

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = @r_code
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>This will yield same results. To have R code and T-SQL code side by side, I would suggest to store T-SQL in table as well.</p>
<pre>DROP TABLE IF EXISTS R_code
CREATE TABLE R_code 
(id INT
,R NVARCHAR(MAX)
,SQLC NVARCHAR(MAX))

INSERT INTO R_code
SELECT 1, '
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3))
       c
       OutputDataSet &lt;- c','SELECT 1 AS Nmbrs_From_R'
-- (1 row(s) affected)

DECLARE @r_code NVARCHAR(MAX)
DECLARE @sql_code NVARCHAR(MAX)
SELECT @r_code = R FROM R_code WHERE id = 1
SELECT @sql_code = SQLC FROM R_code WHERE id = 1

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = @r_code
    ,@input_data_1 = @sql_code
WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>Now, the best thing to do, is to add some logging to the R_code table and some versioning. Easiest way to achieve this by using <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables">Temporal Table</a>.</p>
<h3>TEMPORAL TABLE</h3>
<p>Rewrite the original table:</p>
<pre>CREATE TABLE R_code 
(
 id INT IDENTITY(1,1)
,CombinationID INT NOT NULL CONSTRAINT PK_ComboID PRIMARY KEY
,R NVARCHAR(MAX)
,SQLC NVARCHAR(MAX)
,Valid_From DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL
,Valid_To DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
)
WITH (SYSTEM_VERSIONING = ON);</pre>
<p>Please note, that table will be represented slightly differently (see the clock in the icon).</p>
<p><img sizes="(max-width: 337px) 100vw, 337px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/temp_table.png?w=456" data-orig-size="337,134" data-image-title="temp_table" class="wp-image-4306" data-comments-opened="1" data-recalc-dims="1" data-attachment-id="4306" data-permalink="https://tomaztsql.wordpress.com/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/temp_table/#main" alt="temp_table" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/temp_table.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/temp_table.png?w=456?w=337" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/temp_table.png?w=456"></p>
<p>Besides actual table (or Temporal Table), system automatically creates history table where all the changes are being kept.</p>
<p>Once this is done, I can store T-SQL and R-code.</p>
<pre>INSERT INTO R_code (CombinationID, R, SQLC)
SELECT 1,'
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3))
       c
       OutputDataSet &lt;- c','SELECT 1 AS Nmbrs_From_R'
-- (1 row(s) affected)</pre>
<p>Now I can run the query same way as before:</p>
<pre>DECLARE @r_code NVARCHAR(MAX)
DECLARE @sql_code NVARCHAR(MAX)
SELECT @r_code = R FROM R_code WHERE CombinationID = 1
SELECT @sql_code = SQLC FROM R_code WHERE CombinationID = 1

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = @r_code
    ,@input_data_1 = @sql_code
WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>Suppose that there are changes either to R code or SQL Code committed, and I will simulate this change with an UPDATE query:</p>
<pre>-- INSERT ANOTHER CombinationID = 1, with changed R Code
UPDATE R_code
SET R = '
       d &lt;- InputDataSet 
       c &lt;- data.frame(Num_V1 = c(1,2,3,4))
       c
       d
       OutputDataSet &lt;- c'
,SQLC = 'SELECT 1 AS Nmbrs_From_R'
WHERE
    CombinationID = 1
-- (1 row(s) affected)</pre>
<p>The best part is that system maintains the versioning automatically and I – as an end user – don’t need to worry about changing the original T-SQL code that executes R Script. So once again I can execute the same query:</p>
<pre>DECLARE @r_code NVARCHAR(MAX)
DECLARE @sql_code NVARCHAR(MAX)
SELECT @r_code = R FROM R_code WHERE CombinationID = 1
SELECT @sql_code = SQLC FROM R_code WHERE CombinationID = 1

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = @r_code
    ,@input_data_1 = @sql_code
WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>And now, I will get the results from updated R-script or T-SQL code.</p>
<p><img sizes="(max-width: 205px) 100vw, 205px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/result_temporal.png?w=456" data-orig-size="205,136" data-image-title="result_temporal" data-comments-opened="1" class="wp-image-4323" data-recalc-dims="1" data-attachment-id="4323" data-permalink="https://tomaztsql.wordpress.com/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/result_temporal/#main" alt="result_temporal" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/result_temporal.png?w=456?w=205" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/result_temporal.png?w=456?w=205" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/result_temporal.png?w=456"></p>
<h3>FILE TABLE</h3>
<p>Another way (among many) is to use <a target="_blank" rel="nofollow" href="https://docs.microsoft.com/en-us/sql/relational-databases/blob/filetables-sql-server">File Table</a>. In one of <a target="_blank" rel="nofollow" href="https://tomaztsql.wordpress.com/2016/09/25/filetable-and-storing-graphs-from-microsoft-r-server/">my previous blog posts</a>, I covered how to create and configure File Table. In this scenario, we will consider following. Create a file, that will contain R code and store it with *.R extension. And upload it to the directory, where File Table is considering this file-stream data to be landed or stored.</p>
<p>Quickly check the configuration:</p>
<pre>--- Check configurations
SELECT 
  DB_NAME(database_id) AS DbName
 ,non_transacted_access
 ,non_transacted_access_desc
 ,directory_name  
 ,*
FROM  sys.database_filestream_options
WHERE 
    DB_NAME(database_id) = db_name() --'FileTableRChart'</pre>
<p>By checking where my R file is residing:</p>
<pre>SELECT 
     FT.Name AS [File Name]
    ,IIF(FT.is_directory=1,'Directory','Files') AS [File Category]
    ,FT.file_type AS [File Type]
    ,(FT.cached_file_size)/1024.0 AS [File Size (KB)]
    ,FT.creation_time AS [File Created Time]
    ,FT.file_stream.GetFileNamespacePath(1,0) AS [File Path]
    ,ISNULL(PT.file_stream.GetFileNamespacePath(1,0),'Root Directory') AS [Parent Path]
FROM 
    [dbo].[ChartsR] AS FT
LEFT JOIN [dbo].[ChartsR] AS PT
ON FT.path_locator.GetAncestor(1) = PT.path_locator
WHERE
    FT.File_type = 'R'</pre>
<p>And you can see that I have created R file with the name R_combination1.R</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-19_46_14-document1-word.png?w=456" data-orig-size="904,202" data-image-title="2017-04-17 19_46_14-Document1 – Word" data-comments-opened="1" class="wp-image-4348" data-recalc-dims="1" data-attachment-id="4348" data-permalink="https://tomaztsql.wordpress.com/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/2017-04-17-19_46_14-document1-word/#main" alt="2017-04-17 19_46_14-Document1 - Word" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-19_46_14-document1-word.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-19_46_14-document1-word.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-19_46_14-document1-word.png?w=456"></p>
<p>So we can access this using OPENROWSET.</p>
<pre>SELECT * FROM 
OPENROWSET(BULK N'\\****\RCharts\DocumentTable\R_Combination1.R',
   SINGLE_CLOB) AS R_Code</pre>
<p>You can also do this using the master.dbo.xp_cmdshell, whatever suits you better.</p>
<p>Once you have code read from R file, you can simply continue to execute external procedure.</p>
<pre>-- Physical Location of FileTable
DECLARE @r_code NVARCHAR(MAX)
SELECT @r_code = BulkColumn FROM 
OPENROWSET(BULK N'C:\DataTK\00\R_Combination1.R', SINGLE_CLOB) AS R_Code

EXEC sys.sp_execute_external_script
     @language = N'R'
    ,@script = @r_code
    ,@input_data_1 = N'SELECT 1 AS Nmbrs_From_R'
WITH RESULT SETS ((Numbers_From_R INT));</pre>
<p>And result is the same. In this case you need to consider the usage of BCP, BulkCopy or XP_CMDSHELL. Again, based on your eco-system, what suits you best.</p>
<p>With file table versioning can be a bit of work-around. In addition, you should implement some naming convention to store files correctly or add additional logic to storing and keeping the changes.<img sizes="(max-width: 456px) 100vw, 456px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-20_31_20-store_input_output_sp_execute_external_script-sql-sicn-kastrun-filetablerchart.png?w=456" data-orig-size="456,123" data-image-title="2017-04-17 20_31_20-Store_input_output_sp_execute_external_script.sql – SICN-KASTRUN.FileTableRChart" data-comments-opened="1" class="wp-image-4364" data-recalc-dims="1" data-attachment-id="4364" data-permalink="https://tomaztsql.wordpress.com/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/2017-04-17-20_31_20-store_input_output_sp_execute_external_script-sql-sicn-kastrun-filetablerchart/#main" alt="2017-04-17 20_31_20-Store_input_output_sp_execute_external_script.sql - SICN-KASTRUN.FileTableRChart" data-medium-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-20_31_20-store_input_output_sp_execute_external_script-sql-sicn-kastrun-filetablerchart.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-20_31_20-store_input_output_sp_execute_external_script-sql-sicn-kastrun-filetablerchart.png?w=456?w=456" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/04/2017-04-17-20_31_20-store_input_output_sp_execute_external_script-sql-sicn-kastrun-filetablerchart.png?w=456"></p>
<p>So in my case, I am keeping versioning on FileName level, which can be done using T-SQL or renaming the file on file system and later creating hierarchies with files.</p>
<p> </p>
<p>As always, Code is available at <a target="_blank" rel="nofollow" href="https://github.com/tomaztk/Saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-">GitHub</a>.</p>
<p> </p>
<p>Happy coding!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/4255/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/4255/" 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>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/2017/04/17/saving-input-and-output-with-sp_execute_external_script-using-temporal-table-and-file-table-part-2/"> 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/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/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/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/fw7rRuqcHV4"></a></div>