Possibility of updating data in real-time on a client

Refresh

April 2019

Views

143 time

0

I have the following scenario that I was wondering if it's possible/feasible to implement. I apologize if this is considered an overly "broad" question, but I think SO would be the best place to ask this.

Let us suppose I have a website and I want to display a graph to an end-user. For the purposes of this example, let's say we want to show them "Sales per category" in the past hour. The data would be displayed in a graph, and the SQL to run the query might be something like this:

SELECT SUM(revenue) FROM sales 
WHERE timestamp > NOW() - INTERVAL 1 HOUR
GROUP BY category

As far as I'm aware, there are two general ways to update the data for the end-user:

  1. Do some sort of polling (or a similar technique) at a certain interval to re-fetch the data from the query. However, this can become quite expensive depending on the complexity/duration of the query and how many people are connected simultaneously.
  2. The second method would be to store all the data in-memory and push the update directly to that memory store (which could be either client-side, or server side, and we could send a ws request to the end user whenever there's a data update. An example of this would be using something like https://github.com/jpmorganchase/perspective.

My question then is if it's possible at all do do real-time data updating (the case I describe in Example 2) when the data is too large to store in memory. I think the answer is a "no", but perhaps I'm missing some ways to do this. For example, let's say I have 1TB of data stored in BigQuery and I am streaming updates to it with new product purchases -- is there a way to push updates to the end-client without having to re-run the query for every time I want to get an update? Are there any other technologies that might be used/useful for this scenario?

Again, I don't think it's possible but wanted to see what's possible for as near-real-time display to an end-client as possible on a queried data set.

1 answers

0

Since you are interested in this option I decided to extend the comment to an answer. I will take the SQL Server and C# compoment - sqltabledependency. You can check it out if it fits your needs.

  1. You would create a temp table where you would put any changes from the sales table e.g. sales_data_watch (you could have there also the precalculation aggregations as in your example).

  2. You would create a hourly job which would monitor the changes in the sales table and perform insert/updates on the sales_data_watch

  3. You would have connected the C# sqltabledependency connected to sales_data_watch (note: taken from the example to fit your table)

    public class SaleData
    {
       public int revenue{ get; set; }
    }
    
    public class Program
    {
     private static string _con = "data source=.; initial catalog=MyDB; integrated security=True";
    
     public static void Main()
     {
      // The mapper object is used to map model properties 
      // that do not have a corresponding table column name.
      // In case all properties of your model have same name 
      // of table columns, you can avoid to use the mapper.
      var mapper = new ModelToTableMapper<SaleData>();
      mapper.AddMapping(s => s.revenue, "Aggregated revenue");
    
      // Here - as second parameter - we pass table name: 
      // this is necessary only if the model name is different from table name 
      // (in our case we have Sale vs Sales). 
      // If needed, you can also specifiy schema name.
      using (var dep = new SqlTableDependency<SaleData>(_con, "sales_data_watch", mapper: mapper));
      {
       dep.OnChanged += Changed;
       dep.Start();
    
       Console.WriteLine("Press a key to exit");
       Console.ReadKey();
    
       dep.Stop();
      } 
     }
    
     public static void Changed(object sender, RecordChangedEventArgs<SaleData> e)
     {
      var changedEntity = e.Entity;
    
      Console.WriteLine("DML operation: " + e.ChangeType);
      Console.WriteLine("Revenue: " + changedEntity.Revenue);
     }
    }
    
  4. After all the notifications have been distributed you could do truncate table sales_data_watch after (if you don't want the table to grow too big which would slow down the whole process eventually.

This is using only sql server and C# component. There are other, probably better options, for example: Detect record table change with MVC, SignalR, jQuery and SqlTableDependency to do it differently. That will depend on your preferences.

Edit a complete example link for Building real time charts with Angular 5, Google Charts, SignalR Core, .NET Core 2, Entity Framework Core 2 and SqlTable dependency (this link is first page of three). At the top of the page you can see real-time google's gaugeschart. All credits go to anthonygiretti. You can download the example project at github.

Technologies used

Database

Sql Server, localDb with Visual Studio 2017 is correct to make it work

Front End technologies

Angular 5
Google Charts
Visual Studio Code
SignalR Client

BackEnd technologies

.NET Core 2
SignalR Core
EntityFramework Core
EntityFramework Core for Sql Server
SqlTableDependency

First is to install compoenents needed - service broker, SQL Table, Angular-CLI, Angular 5 project, SignalR client (VS 2017, .Net Core 2 SDK installed) - link is the same part1

Next comes the backend setup - part2

To make it work this project contains :

  • A DbContext (GaugesContext.cs) for EntityFramework Core
  • A Hub (GaugeHub.cs) for SignalR that broadcast data
  • A Model that contains strongly typed data to send (Gauge.cs)
  • A Repository exposed with Entity Framework and its Interface (GaugeRepository.cs and IGaugeRepository.cs)
  • A Subscription to Gauge sql table with SqlTableDependency and its Interface (GaugeDatabaseSubscription.cs and IDatabaseSubscription)
  • Two Extension methods that extends IServiceCollection (AddDbContextFactory.cs) and IApplicationBuilder (UseSqlTableDependency.cs) And Startup.cs and Program.cs

Last part is to setup the frontend - part3

We have :

  • A folder that contains the gauge chart component (gaugeschart.component.html and gaugeschart.component.ts)
  • A folder that contains a gauge chart service and a Google Charts base service (google-gauges-chart.service.ts and google-charts.base.service.ts)
  • A folder that contains environments files
  • A folder that contains a strongly typed model for the gauge chart (gauge.ts)
  • Finally at the root of src folder the defaults files components and module (app component files and app module file)

In the next step you should test it to see if the data are projected into the graphs correctly when you changed the data.