Real-time data visualization with SQL Server and Python Dash

The need for visualizing the real-time data (or near-real time) has been and still is a very important daily driver for many businesses. Microsoft SQL Server has many capabilities to visualize streaming data and this time, I will tackle this issue using Python. And python Dash package  for building web applications and visualizations. Dash is build on top of the Flask, React and Plotly and give the wide range of capabilities to create a interactive web applications, interfaces and visualizations.

First, we will create a sample SQL Table where data will be inserted as mimicking the data stream:

DROP TABLE IF EXISTS dbo.LiveStatsFromSQLServer;
GO

CREATE TABLE dbo.LiveStatsFromSQLServer
(
 ID int identity(1,1)
,Num TINYINT NOT NULL
)

And using this query, we will generate some random data, that will be inserted into the table and simultaneously presented on the graph:

-- Do some inserts to mimic the data stream
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
WAITFOR DELAY '00:00:01.500'
GO 1000

Python code will be executed on the server (localhost) from the command line.

In your favorite Python editor, you will import the following packages:

import dash
from dash.dependencies import Output, Event
import dash_core_components as dcc
import dash_html_components as html
import plotly
import plotly.graph_objs as go
from collections import deque
import pandas as pd
import pyodbc

To create a connection to Microsoft SQL Server and the the table you have created in previous step, we will use the following function from the pyodbc:

def connectSQLServer(driver, server, db):
    connSQLServer = pyodbc.connect(
        r'DRIVER={' + driver + '};'
        r'SERVER=' + server + ';'
        r'DATABASE=' + db + ';'
        r'Trusted_Connection=yes;',
       autocommit=True
    )
    return connSQLServer

Another function will be needed that will ingest the data from Microsoft SQL Server and create a data set and layout set for the Dash:

def update_graph_scatter():
    dataSQL = [] #set an empty list
    X = deque(maxlen=10) 
    Y = deque(maxlen=10)

    sql_conn = connectSQLServer('ODBC Driver 13 for SQL Server', 
                            'TOMAZK\MSSQLSERVER2017', 'test') 
    cursor = sql_conn.cursor()
    cursor.execute("SELECT num,ID FROM dbo.LiveStatsFromSQLServer")
    rows = cursor.fetchall()
    for row in rows:
        dataSQL.append(list(row))
        labels = ['num','id']
        df = pd.DataFrame.from_records(dataSQL, columns=labels)
        X = df['id']
        Y = df['num']

     data = plotly.graph_objs.Scatter(
             x=list(X),
             y=list(Y),
             name='Scatter',
             mode= 'lines+markers'
             )

    return {'data': [data],'layout' : go.Layout(
                                  xaxis=dict(range=[min(X),max(X)]),
                                  yaxis=dict(range=[min(Y),max(Y)]),)}

This function will fetch next row from SQL Server Table using previously declared function connectSQLServer and  extract the two columns ID and num from the Pandas DataFrame and pass it to the plotly.graph_objs.Scatter object. Function returns the data list and the definition of the layout of the graph; that is, the borders of the graph.

The declaration of the Dash application will be specifies as:

name_title = 'Stats from SQL Server'
app = dash.Dash(__name__)

app.layout = html.Div(children=[
     html.H1(children='Read near real-time data from SQL Server 
                            on Scatterplot '),
     dcc.Graph(
          id='example-graph',
          animate=True),
      dcc.Interval(
           id='graph-update',
           interval=1*500),
])

@app.callback(Output('example-graph', 'figure'), 
 events=[Event('graph-update', 'interval')])

This part finally declares the behaviour of our graph, the interval of refresh (0,5 seconds) and the @app.callback, as the events will be triggered for the graph refresh.

You can download the code from the Github, for easier code manipulation. Next, open CMD and navigate to your Anaconda or Python environment, where all the packages are already pre-installed and run:

Python C:\Real_TimeStatsVisualizationWithDash.py

You will be prompted with:

2018-06-18 20_01_09-Administrator_ Command Prompt - Python C__DataTK_dash_py_live_stats3.py

Once this is active, open your browser and point to: http://127.0.0.1:8050.

To simulate the real-time visualization, in your SSMS, run the query:

-- Do some inserts to mimic the data stream
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
WAITFOR DELAY '00:00:01.500'
GO 1000

And the graph in the browser will be visualizing the data stream.

2018-06-18 18_28_08-Dash

For building slightly more informative dashboards for the need of your data streaming (either for the purposes of DBA and health checks, inbound/outbound OLTP transactions, for the purposes of data science or simply for monitoring network, ….) more beautiful graphs can be added, more controls and even CSS can be included for esthetics.

As always, the code is available at Github for your purpose and needs.

I have also added a short clip for to show how this visualization works, available also at Github.

Happy coding!

Tagged with: , , , , ,
Posted in Uncategorized
10 comments on “Real-time data visualization with SQL Server and Python Dash
  1. […] Tomaz Kastrun shows how to use Python Dash to visualize data living in SQL Server in real time: […]

    Like

  2. […] the previous post, I have showed how to visualize near real-time data using Python and Dash module.  And it is time […]

    Like

  3. […] the previous post, I have showed how to visualize near real-time data using Python and Dash module.  And it is time […]

    Like

  4. sadalsuud says:

    Hi, I’m new to Dash, my question is: can I use MySQL instead of SQL Server for the same purpose? Thanks!

    Like

  5. Saul Salcedo says:

    Do you know how is the string to connect mysql? Regards.

    Like

  6. X = deque(maxlen=20)
    can you explain kindly what does the above statement do

    Like

  7. Hi I have tried but I’m getting this error
    TypeError: update_graph_scatter() takes 0 positional arguments but 1 was given

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

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

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, Python and beyond

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

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

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Ms SQL Girl

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

R-bloggers

R news and tutorials contributed by hundreds of 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 DLM 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.

%d bloggers like this: