Advanced Microsoft SQL Server 2008 R2 StreamInsight

1. PDC09-SVR08 Advanced Microsoft SQL Server 2008 R2 StreamInsight Beysim Sezgin (Architect) Roman Schindlauer (Program Manager) Microsoft Corporation

2. Agenda • Use Cases & Challenges • Formulating Declarative Queries • Windows in Time • Implementing Adapters • Architecture • Extensibility • Event Flow Debugging

3. What is CEP? Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency.

4. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

5. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

6. What is CEP? Event Complex Event Processing (CEP) is the continuous and incremental processing of event streams from multiple sources based on declarative query and pattern specifications with near-zero latency. request output stream input stream response

7. StreamInsight Platform .NET C# LINQ StreamInsight Application Development StreamInsight Application at Runtime Event sources Event targets Input Adapters Output Adapters StreamInsight Engine Devices, Sensors Pagers & Monitoring devices Standing Queries KPI Dashboards, SharePoint UI 1 Web servers Query Logic Query Logic 4 2 3 Trading stations Event stores & Databases Query Logic Event stores & Databases Stock ticker, news feeds

9. Use Case: NBC Sunday Night Football 1 Telemetry Receiver 4 StreamInsight Listener Adapter GeoTag and group by region SQL Adapter PerfCounter Adapter 2 Count total events Count session starts Count active sessions 3

10. Use Case: Data Center Power Consumption Visualize Process Information Complex Aggregations/ Correlations Central time series archive Query ETW Input Adapter Query 2 1 Query Power Meter Input Adapter 3

11. ChallengesHow do I … • detect interesting patterns? • reason about temporal semantics? • correlate data? • aggregate data? • avoid writing custom imperative code? • create a runtime environment for continuous and event-driven processing? As a developer, I need a platform!

12. Query Expressiveness • Selection of events (filter) • Calculations on the payload (project) • Correlation of streams (join) • Stream partitioning (group and apply) • Aggregation (sum, count, …) over event windows • Ranking over event windows (topK)

13. Query Expressiveness • Projection var result = from e ininputStream selectnew { id = e.id, W = (double)e.intW / 10 };

14. Query Expressiveness • Projection • Filter var result = from e ininputStream where e.id > 3 selectnew { id = e.id, W = (double)e.intW / 10 };

15. Query Expressiveness • Projection • Filter • Correlation (Join) var result = fromeLeftin inputStream1 joineRightin inputStream2 on eLeft.id equals eRight.id selectnew { id = eLeft.id, diff = eLeft.W - eRight.w };

16. Query Expressiveness • Projection • Filter • Correlation (Join) • Aggregation over windows var result = from win ininputStream.TumblingWindow( TimeSpan.FromSeconds(10)) selectnew { avg = win.Avg(e => e.W) };

17. Query Expressiveness • Projection • Filter • Correlation (Join) • Aggregation over windows • Group and Aggregate var result = from e ininputStream group e by e.id intoeachGroup from win ineachGroup.TumblingWindow( TimeSpan.FromSeconds(10)) selectnew { eachGroup.Key, avg = win.Avg(e => e.W) };

18. Time Windows Tumbling Window Time

19. Time Windows Hopping Window Time

20. demo LINQ Queries

21. Adapters • Import data into StreamInsight platform • Adapter characteristics: • Pullvs Push • Out-of-order vs. In-Order Events • Adapter API • Observable model • wraps adapter framework

22. Adapter Factory • publicclassMyFactory :ITypedInputAdapterFactory<MyConfig> • { • public InputAdapterBase • Create<Payload>(MyConfigconf, EventShapeev) • { • return newMyAdapter(conf); • } • public void Dispose() • { ... } • }

23. Adapter API • publicclassMyAdapter :TypedPointInputAdapter<MyPayload> • { • public MyAdapter(MyConfigconf) • { ... } • public override void Start() • { ... } • public override void Resume() • { ... } • }

24. Stream Liveliness ? ? Time

25. Development Steps .Net Development 1 2 LINQ 3 StreamInsight IL User App Engine 4 Runtime query Adapter Adapter

26. StreamInsight Architecture Compilation Runtime Algebrizer Streams Parser Events Stream Manager Compiler Expression Service Event Manager Type System Execution Operators Scheduler Metadata Stream OS Resource Governor Synch. Primitives Buffer Manager CLR Hosting

27. Query Compilation • Multiple Syntactic surfaces over a single algebra, e.g., LINQ, CQL, etc. • Compile time type checking and type safe code generation for minimal runtime impact. • Leverage CLR • Code generator, JIT support • Type System • Tools and Libraries (LINQ Expressions, IDE, etc.) • Multiple Deployment Environment with different form factors, e.g. SmartPhone, PDA, PCs. Compilation Algebrizer Parser Compiler Expression Service Type System Metadata

28. Runtime – Events, Streams, Scheduler • JIT code generation for field references, expression evaluation • Event manager is implemented as a combination of managed and native code in order to minimize overhead and ensure predictable performance. Runtime Streams Events Stream Manager Event Manager Execution Operators Scheduler • Cooperative Scheduler (less context switch, better SLA) • Operators are affinitized to a Scheduler. Periodic checks and migration for load balancing • DataFlow Architecture: Reduced coupling and pipeline parallelism

29. Runtime – Execution Operators XYZ ~ REDUCE Union X,Y,Z • Efficient implementation of operators that perform incremental evaluation as each event is processed. • GroupAndApply Operator: • Enables parallelism for both scale-up (multi-core) and scale-out(cluster). • Groups are dynamically instantiated and torn down based upon the data. Large numbers of groups can be simultaneously active. ZZ YYY XX Machine1 Machine2 Apply Apply Apply AA BBB CC Machine1 Group A,B,C ~ MAP ABC

30. Extensibility • Need for domain-specific extensions • Integrate with functionality available in existing libraries • User-defined operators, functions, aggregates • Code written in .NET, deployed as .NET assembly • Window-based semantics for UDOs, UDAs • Receive a set of events • Produce a value / set of events

31. Supportability • Diagnostic interface • Retrieve statistical information at runtime • Part of the object model API • Manage through Powershell • Debugger tool • Retrieve live diagnostics • Record and replay queries • Analyze event processing along operators

32. Debugger Tool demo

33. Conclusion • CEP Platform & API • Event-triggered, fast Computation • API for Adapters, Queries, Applications • Declarative LINQ • Flexible Adapter API • Extensible • Supportability

34. Please Evaluate! • Sign in to microsoftpdc.com • Follow the evaluation link for the session • PDC will donate $1 for each person who completes an evaluation to the local Boys and Girls Club