StreamInsight 02 – Querying Events in SQL Server 2008 R2 StreamInsight

1. StreamInsight02 – Querying Events in SQL Server 2008 R2 StreamInsight SQL10R2UPD05-DECK-02 [Presenter Name][Presenter Title] [Company Name]

2. Module Overview • Introducing StreamInsight • Querying Events in StreamInsight • Designing StreamInsight Event Models and Adapters • Installing, Deploying, and Maintaining the StreamInsight Runtime Engine

3. Agenda • Querying StreamInsight Using LINQ • Demo: Querying StreamInsight Using LINQ • Using Advanced Query Options • Demo: Using Advanced Query Options • Lab: Querying StreamInsight with LINQ

4. Query Elements • Projection • Filtering • Joins • Unions • Aggregations • Event Windows • Hopping Window, Tumbling Window • Snapshot Window

5. LINQ-Based Queries • Read from an input stream • Outgoing events sent to an output stream

6. Query Element: Projection • Selects required fields from a stream for new stream • May create a new type or refer to an existing type • Cannot use constructor (must use field assignment) • May apply calculations to the data

7. Query Element: Filtering • Restrict events included in resulting event stream • Propagates event if where clause evaluates to true

8. Query Element: Joins • Correlates values from different streams • Supports inner joins, equijoins, cross joins • Checks overlapping time intervals • Checks join conditions

9. Query Element: Unions • Combines events from two streams into a single stream • Can be cascaded to union more than two streams

10. Query Element: Aggregations • Supported aggregations • Avg • Sum • Count • Min • Max • User-defined • Computed over events within a window (interval) • Can only be applied to windows

11. Query Element: Event Windows • Allows computation against events within an interval • The following operations are permitted on windows: • Aggregation • TopK • User-defined operations • Windows are time-based or count-based • Do not change the underlying events • Can have input and output policies

12. Query Element: Hopping Window • Hop forward in time by a fixed period • Defined by window size and hop size • Operators apply to events within the window

13. Variation: Tumbling Window • Simplified form of hopping window • Interval and hop size identical

14. Query Element: Snapshot Window • Defined by start/end times of events in the stream • No fixed grid along the timeline • No parameters in the window definition • Only output policy is required

15. Count Windows • Based on count of events rather than time • One form of count window available at RTM • Others likely in the future • Currently only support user-defined aggregates

16. Querying StreamInsight Using LINQ SQL10R2UPD05-DEMO-02 Demo

17. Agenda • Querying StreamInsight Using LINQ • Demo: Querying StreamInsight Using LINQ • Using Advanced Query Options • Demo: Using Advanced Query Options • Lab: Querying StreamInsight with LINQ

18. Advanced Query Elements • TopK • Group • Timestamp Modification

19. Query Element: TopK • Ranks events in a stream • Limits events to specified number

20. Project Rank Back into Stream • May want ranking to be part of stream • Lambda expression used • “Rank” and “Payload” properties

21. Query Element: Group • Partitions events into event groups • Uses LINQ “group by” operator • Aggregates can be performed on groups • Access to grouping key possible but at RTM requires explicit typing

22. Grouping on Several Keys • Each unique key combination produces a separate group

23. ApplyWithUnion • Aggregate is computed on the stream then joined back to the stream

24. Extending StreamInsight Events • Built-in operators flexible but will not cover all requirements • You might want to reuse existing .NET libraries • StreamInsight platform is extensible through • User-defined operators • User-defined functions • User-defined aggregates • .NET code deployed as assemblies • Temporal snapshot operator framework allows user-defined operators

25. User-Defined Functions • Allow reuse of existing libraries

26. User-Defined Aggregates

27. Exposing UDAGG to LINQ

28. Using Advanced Query Options SQL10R2UPD05-DEMO-03 Demo

29. Resources • StreamInsight Website • http://www.microsoft.com/sqlserver/2008/en/us/R2-complex-event.aspx • StreamInsight Books Online • http://msdn.microsoft.com/en-us/library/ee362541(SQL.105).aspx • StreamInsight Forums • http://social.msdn.microsoft.com/Forums/en-US/streaminsight/threads • StreamInsight Whitepaper • http://download.microsoft.com/download/F/D/5/FD5E855C-D895-45A8-9F3E-10AFADBE51A/Microsoft%20CEP%20Overview.docx

30. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.