Real-time data visualization using R and data extracting from SQL Server

In the previous post, 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 RODBC package to extract data from SQL Server.

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.

First, we will create a sample table in SQL Server and populate it with some sample data:

CREATE DATABASE Test;
GO

USE Test;
GO

CREATE TABLE dbo.LiveStatsFromSQLServer
(ID INT IDENTITY(1,1)
,Num tinyint NOT NULL)

And populate it with some sample data:

-- Insert some test data
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
GO 10

Now, that we have SQL foundations set up, let’s focus on R code.

First we set the environment variable and the RODBC library:

library(RODBC)
# create env for storing the variables/data frames between the functions
assign("getREnvironment", new.env(), envir = .GlobalEnv)

We will generate a function for extracting data from SQL Server and storing it in environment data.frame variable:

# Function to read data from SQL Server
getSQLServerData <- function()
{
#extract environment settings for storing data
getREnvironment <- get("getREnvironment", envir = .GlobalEnv, mode = "environment")
#get the SQL Server data
con <- odbcDriverConnect('driver={SQL Server};
                         server=TOMAZK\\MSSQLSERVER2017;
                         database=test;trusted_connection=true')
db_df <- sqlQuery(con, 'SELECT 
                         TOP 20 id
                           ,num 
                        FROM LiveStatsFromSQLServer ORDER BY id DESC')
close(con)
#overwrite existing data with new data
df_overwrite <- db_df
getREnvironment$db_df <- data.frame(df_overwrite)
try(assign("getREnvironment", getREnvironment, envir = .GlobalEnv))
invisible() #do not print the results
}

 

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:

# 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 <- get("getREnvironment", envir = .GlobalEnv, mode = "environment")
  data <- getREnvironment$db_df
  plot( data$id, data$num, type='l',main='Realtime data from SQL Server')
  Sys.sleep(0.5)
}

 

Once we run the complete R code, we need to trigger and run also the new inserts in SQL Server Management studio:

-- 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

Once we do this, we can observe the realtime data from SQL Server being plotted in R environment (R Studio).

2018-07-22 20_51_00-Window

As always, complete code is available at Github.

Happy R-coding! 🙂

Advertisements
Tagged with: , , , , , ,
Posted in Uncategorized
3 comments on “Real-time data visualization using R and data extracting from SQL Server
  1. […] article was first published on R – TomazTsql, and kindly contributed to […]

    Like

  2. […] Tomaz Kastrun shows how simple it can be to plot real(ish)-time data from SQL Server using R: […]

    Like

  3. […] Real-time data visualization using R and data extracting from SQL Server […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use
Programs I Use
Programs I Use
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

2 EUR

Top SQL Server Bloggers 2018
Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

DB NewsFeed

Matan Yungman's SQL Server blog

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Clocksmith Games

We make games we love to play

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R

Search Msdn

Tomaz doing BI and DEV with SQL Server and R

R-bloggers

Tomaz doing BI and DEV with SQL Server and R

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by (750) R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and .net in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

William Durkin

William Durkin a blog on SQL Server, Replication, Performance Tuning and whatever else.

%d bloggers like this: