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:

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.

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!

‹ Native scoring in SQL Server 2017 using R

Connecting Azure Machine Learning studio with on-premises SQL Database ›

Tagged with: data stream, data wrangling, Python, real-time, SQL Server, Visualization
Posted in Uncategorized