group stream events to a list and merge list into second stream

Category: sql server streaminsight


spud1955 on Fri, 17 May 2013 07:18:24

Using V1.2 of StreamInsight

I have an event stream

class Customer{


List<Invoices> inv;



and another stream of Invoice events

I want to group the invoices by customer and add to the field inv List<Invoices> to the customer event stream 

Is this possible?

Paul Tait


Allen Li - MSFT on Mon, 20 May 2013 06:37:20

Hi Paul,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.


spud1955 on Mon, 20 May 2013 13:46:46

I guess I should give you more help by telling you that I'm trying to use a typed input event which I thought would make it easier for my first SI project. I'm starting to think I made the wrong choice. In the past I've worked on stock market applications and they handled variable length collections in the old 'C' way ie. the following buffer contains 0-n of a certain type of struct. Am I right in thinking I'd need an untyped event to handle data like that?


DevBiker on Tue, 21 May 2013 14:52:11

Let's start by taking a step back. What is it that you are trying to accomplish? What is your data source?

Also, event streams aren't going to have List<> items. List isn't a supported type for StreamInsight payloads. That said, you can do groupings and aggregates but I'd like to understand what it is you are trying to accomplish here.

That said, the customer data is almost certainly going to be a reference stream. See this blog article from Mark Simms:

spud1955 on Wed, 22 May 2013 00:22:39

Yes you are right the Customer is slow changing reference data which we currently poll but it will eventually be pushed to us only when a change occurs. Invoices can occur singly, in groups or not at all, but we currently composite this data in a hand coded merge. I'm aware of StreamInsights non support of List<>() but my question was of a more general nature. How can I merge a stream of reference data with a stream of 0-n events related to the reference stream? In a previous contract I worked on a stock market stream which used the old a trusted 'C' way of doing it. ->static ref data -> 'n' denoting number of events to follow -> n * events. Obviously this is an untyped event stream, but the amount of coding needed to bridge the SI no List<>() stream to typical .NET objects seems prohibitive. It almost like the old DB to object impedance mismatch that ORM's fix

DevBiker on Wed, 22 May 2013 13:50:23

On the adapter question ... we use, almost exclusively ... untyped adapters. While typed adapters provide a better programming experience, untyped adapters provide FAR more flexibility, especially if you don't have the luxury of a well-defined and static payload.

To match customers with invoices, you'll use that slow-moving reference stream. If you want to get more sophisticated - or if your customer list is too large to feasibly hold in memory - you can use a UDSO that lazy loads customers and keeps a cache of recently accessed customer records once loaded.

Why is it that you need the full customer information? What analysis are you doing that requires that?

spud1955 on Wed, 22 May 2013 14:15:29

Ok the customers are actually patients in a hospital and their static data changes over their stay. Things like length of stay change etc. As doctors see them various referalls to specialists and scripts for drugs are raised (my invoices in the original example). When any of these streams changes or gets added to we were hoping that SI would composite this up into a record that could be sent via a website to a large monitor on each ward showing patient details in near realtime. Desktop versions would allow hovering over various columns to show details of referrals and scripts. So I guess we aren't using SI for its primary purpose of joining and aggregating (reducing) data for analysis. We just wanted multiple streams matched and joined as data became available.

I agree that untyped adapters are probably the best for most situations, but as we had already developed a lot of code we really wanted to use typed adapters and eventually we decided the cost of pulling our structures apart to put into SI and then building them back up as the data came out of SI just wasn't worth it for us. Thanks for your comments and help.

As a parting question... Even though SI is a SQL product it doesn't really have anything to do with databases so why aren't something as ubiquitous as collections supported?

DevBiker on Wed, 22 May 2013 15:10:52

Ah! I've spoken about this use case with another Sql MVP. It's a little different but actually a pretty good (and interesting) use case for StreamInsight.

What would be more interesting is to match it with other drugs that the patient is on, allergies that the patient has reported and the like ... and raise alerts based on that. You can then integrate this with the patient monitors to look for any potential warning signs of drug issues.

When you were saying "customer" ... I was thinking of a more traditional invoicing system. Since it's a hospital scenario, there would be a limited number of patients at any given time, so using the reference stream is absolutely doable. If you want to get creative, you can add support for things like check-in and check-out events to add/remove customers from the patient reference stream. There's all kinds of stuff that you can do with this type of scenario and while it's a little different from most applications, as I mentioned before, it's an interesting one and pretty applicable, IMHO.

As for collection support ... yeah, that's a challenge. The product team has been asked - several times - for some support of arrays (which would be close enough). The challenge, currently, is that, internally, StreamInsight doesn't use .NET objects - the classes that you work with merely provide the schema of your streams. Instead, StreamInsight has its own internal representation of the data.

spud1955 on Thu, 23 May 2013 11:27:59

Hey DevBiker sounds like you got just as excited as I did when my boss asked me to prototype this system. Unfortunately I spent way too much time trying to coerce typed events into doing what I wanted as I was trying to get something working "fast". I learned a lot in the 10 days I had to get the proof of concept working but our sprint was only 2 weeks so I had to call it quits and fall back on straight .NET stuff that will need constant extension and maintenance :-(

Living in the most isolated city in the world I think I'll have to bid adieu to SI but I think I'll keep the RSS feed going :-)

BMW K100