<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/real-time-data-visualization-using-r-and-data-extracting-from-sql-server/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2018/07/23/real-time-data-visualization-using-r-and-data-extracting-from-sql-server/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>In the <a rel="nofollow" href="https://tomaztsql.wordpress.com/2018/06/18/real-time-data-visualization-with-sql-server-and-python-dash/" target="_blank">previous post</a>, I have showed how to visualize near real-time data using Python and Dash module.  And it is time to see one of the many ways, how to do it in R. This time, I will not use any additional frames for visualization, like shiny, plotly or any others others, but will simply use base R functions and <a rel="nofollow" href="https://cran.r-project.org/web/packages/RODBC/index.html" target="_blank">RODBC</a> package to extract data from SQL Server.</p>
<p>Extracting data from SQL Server will and simulating inserts in SQL Server table will primarily simulate the near real-time data. If you have followed the previous post, you will notice that I am using same T-SQL table and query to extract real-time data.</p>
<p>First, we will create a sample table in SQL Server and populate it with some sample data:</p>
<pre>CREATE DATABASE Test;
GO

USE Test;
GO

CREATE TABLE dbo.LiveStatsFromSQLServer
(ID INT IDENTITY(1,1)
,Num tinyint NOT NULL)</pre>
<p>And populate it with some sample data:</p>
<pre>-- Insert some test data
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
GO 10</pre>
<p>Now, that we have SQL foundations set up, let’s focus on R code.</p>
<p>First we set the environment variable and the RODBC library:</p>
<pre>library(RODBC)
# create env for storing the variables/data frames between the functions
assign(&quot;getREnvironment&quot;, new.env(), envir = .GlobalEnv)</pre>
<p>We will generate a function for extracting data from SQL Server and storing it in environment data.frame variable:</p>
<pre># Function to read data from SQL Server
getSQLServerData &lt;- function()
{
#extract environment settings for storing data
getREnvironment &lt;- get(&quot;getREnvironment&quot;, envir = .GlobalEnv, mode = &quot;environment&quot;)
#get the SQL Server data
con &lt;- odbcDriverConnect('driver={SQL Server};
                         server=TOMAZK\\MSSQLSERVER2017;
                         database=test;trusted_connection=true')
db_df &lt;- sqlQuery(con, 'SELECT 
                         TOP 20 id
                           ,num 
                        FROM LiveStatsFromSQLServer ORDER BY id DESC')
close(con)
#overwrite existing data with new data
df_overwrite &lt;- db_df
getREnvironment$db_df &lt;- data.frame(df_overwrite)
try(assign(&quot;getREnvironment&quot;, getREnvironment, envir = .GlobalEnv))
invisible() #do not print the results
}</pre>
<p> </p>
<p>Once we have this function registered, we can now create a small for loop that will update the plot with newly fetched data from SQL Server:</p>
<pre># Plot graph 
n=1000 #nof iterations
windowQuery=20 # syncronised with TOP clause in SELECT statement
for (i in 1:(n-windowQuery)) {
  flush.console()
  getSQLServerData()
  getREnvironment &lt;- get(&quot;getREnvironment&quot;, envir = .GlobalEnv, mode = &quot;environment&quot;)
  data &lt;- getREnvironment$db_df
  plot( data$id, data$num, type='l',main='Realtime data from SQL Server')
  Sys.sleep(0.5)
}</pre>
<p> </p>
<p>Once we run the complete R code, we need to trigger and run also the new inserts in SQL Server Management studio:</p>
<pre>-- Do some inserts to mimic the data stream
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
WAITFOR DELAY '00:00:00.500'
GO 100</pre>
<p>Once we do this, we can observe the realtime data from SQL Server being plotted in R environment (R Studio).</p>
<p><img data-orig-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-22-20_51_00-window.png?w=450&amp;h=419&amp;fit=564%2C419" data-orig-size="788,585" data-medium-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-22-20_51_00-window.png?w=450&amp;h=419&amp;fit=564%2C419?w=300" data-attachment-id="5035" data-image-meta="{" data-image-description="" src="https://tomaztsql.files.wordpress.com/2018/07/2018-07-22-20_51_00-window.png?w=450&h=419&fit=564%2C419" data-permalink="https://tomaztsql.wordpress.com/2018/07/23/real-time-data-visualization-using-r-and-data-extracting-from-sql-server/2018-07-22-20_51_00-window/" data-large-file="https://tomaztsql.files.wordpress.com/2018/07/2018-07-22-20_51_00-window.png?w=450&amp;h=419&amp;fit=564%2C419?w=605" alt="2018-07-22 20_51_00-Window" data-image-title="2018-07-22 20_51_00-Window" sizes="(max-width: 564px) 100vw, 564px" data-comments-opened="1" class="wp-image-5035" data-recalc-dims="1"></p>
<p>As always, complete code is available at <a rel="nofollow" href="https://github.com/tomaztk/Real_Time_Visualization_with_R_and_SQLServer" target="_blank">Github</a>.</p>
<p>Happy R-coding! <img alt="🙂" src="https://i0.wp.com/s0.wp.com/wp-content/mu-plugins/wpcom-smileys/twemoji/2/72x72/1f642.png?w=456&ssl=1" class="wp-smiley" 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/2018/07/23/real-time-data-visualization-using-r-and-data-extracting-from-sql-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 rel="nofollow" href="https://www.r-project.org/" title="The R Project for Statistical Computing">R</a> news and <a rel="nofollow" href="https://www.r-bloggers.com/search/tutorial" title="R tutorials">tutorials</a> on topics such as: <a rel="nofollow" href="https://www.r-bloggers.com/search/data%20science" title="Data science">Data science</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data" title="Big Data">Big Data, <a rel="nofollow" href="https://www.r-users.com/" title="R jobs">R jobs</a>, visualization (<a rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2" title="ggplot and ggplot2 tutorials">ggplot2</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/boxplot" title="Boxplots using lattice and ggplot2 tutorials">Boxplots</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/map" title="Maps and gis">maps</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/animation" title="Animation in R">animation</a>), programming (<a rel="nofollow" href="https://www.r-bloggers.com/search/RStudio" title="RStudio IDE for R">RStudio</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/sweave" title="Sweave and literate programming">Sweave</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX" title="LaTeX in R">LaTeX</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/SQL" title="SQL and databases">SQL</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/eclipse" title="Eclipse IDE for R">Eclipse</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/git" title="git and github, Version Control System">git</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/hadoop" title="Large data in R using Hadoop">hadoop</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping" title="Web Scraping of google, facebook, yahoo, twitter and more using R">Web Scraping</a>) statistics (<a rel="nofollow" href="https://www.r-bloggers.com/search/regression" title="Regressions and ANOVA analysis tutorials">regression</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/PCA" title="principal component analysis tutorial">PCA</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/time+series" title="Time series">time series</a>, <a rel="nofollow" href="https://www.r-bloggers.com/search/trading" title="finance trading">trading</a>) and more...
<img alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/HNs6XgpJ9_M" height="1"></a></div>