2

I am using eventgrid,the events are written to the topics and the data is processed and saved in cosmos db.

Now we are trying to build a report in power bi where we are planning to use streaming aggregation. Is it possible to aggregate the log and move the data to sql DB, say number of logs entered the eventgrid in every hour.

Kind of stateful operation, use something like a Samza EventHubs connector

Karthikeyan VK
  • 5,310
  • 3
  • 37
  • 50
  • so you want to aggregate it after cosmos db and store just count of logs per hour in sql db? If I may ask, how do you currently process the logs before storing in the CosmosDb? – kgalic Nov 28 '19 at 13:20
  • Yes. But it can happen in parallel also. I dont want to read the cosmos db, just read the event from eventgrid and aggregate the count per hour – Karthikeyan VK Nov 28 '19 at 13:22

1 Answers1

1

One idea would be the following:

Push events from the Event Grid to the Event Hub, and use the stream analytics to store the data in the cosmos Db, as you already have that, and to the blob storage in format {date}/{time}. The time you can set to be hourly.

This way, you can store your logs partitioned per date, and per hour in the file(s).

Now you could have azure function triggered on the blob, that will read the file, and count objects in the array, and write the count in the SQL DB. This way your count will be more event-driven, and more or less you will always have the accurate information in the DB

The alternative is that you do not store to the SQL DB and just do the count in the PowerBI, as there is a connector for Blob store as well.

Another idea would be to play with the Azure stream analytics job and time-window functions. But the tricky part here becomes partitioning, and how to make it work with insert-or-update properly on SQL DB. For this, we would need to know some implementation specifics.

kgalic
  • 2,441
  • 1
  • 9
  • 21