StreamInsight 03 – Designing SQL Server 2008 R2 StreamInsight Event Models and Adapters

StreamInsight03 – Designing SQL Server 2008 R2 StreamInsight Event Models and Adapters SQL10R2UPD05-DECK-03 [Presenter Name][Presenter Title] [Company Name]

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

Agenda • Event-related StreamInsight Objects • Maintaining Query Responsiveness Through CTI Entries • Demo: Working with CTI Entries • Working with StreamInsight Adapters • Demo: Creating an Input Adapter

Event Flow and Control • Event data flow is unidirectional: • Source -> Standing Query -> Sink • Event flow within server: • Source -> Input Adapter • Input Adapter -> Query • Query -> Query -> Query (from one operator to the next) • Query -> Output Adapter • Output Adapter -> Sink

Streams • All data organized into Streams • Potentially unending • Data may change over time • Often represent the same value over time

Events • Data in streams is packaged into events • Two parts to an event • Header (kind of event and timestamps) • Payload (.NET data structure) • Timestamps are DateTimeOffset data type • All times normalized to UTC in server

Event Kind • Two event kinds • INSERT (new data for the stream) • CTI (current time increment) • CTI allows • Processing out of order data • Responsive stream

Event Models • Three event models • Interval (payload valid for a period) • Point (payload valid at an instant) • Edge (payload changing over time)

Event Models: Interval • StartTime and EndTime • Values are valid for the defined interval • EndTime is not included in the interval

Event Models: Point • StartTime • Event occurrence at a single point in time • EndTime is inferred by server as StartTime + 1 cronon

Event Models: Edge • StartTime • End Time initially unknown (set to DateTime.MaxValue) • Important to know event occurring prior to completion

Payload • .NET struct or class • Fixed structure once declared • Must use public fields or properties • Private fields, properties, and methods are ignored

Payload Considerations • Only simple (non-nested) scalar types supported • Maximum of 32 payload fields • Empty payload structures are not permitted • Fields may not be adorned with custom attributes • Nullability is inferred from type • int? vs int • String, byte[] always nullable • byte[] size defaults to 512

Sample Payload Definition publicclassTollPayload { publicintTollBoothID { get; set; } publicintLaneID { get; set; } publicintVehicleType { get; set; } publicStringTagID { get; set; } }

Field Order • Event types are ordered lists of fields in server • .NET Framework structures do not impose order • Order of event fields is critical for untyped adapters • Fields are not known at adapter design time • Field access is by ordinal • Suggest using classes instead

CTI • Current Time Increment • Added to input stream • Typically added by input adapter • Can be declaratively added in query binding • Indicates where aggregation can occur up to • Necessary for event output • Time based or event count based

Stream Liveliness ? ? Time

Query Binding: CTI Behavior • Two policies provided • Drop • Adjust

Query Element: Timestamp Modification • Useful if you need to modify the timestamp of an event // shift events by 15 minutes into the future. var shifted = inputStream.ShiftEventTime(e => e.StartTime + TimeSpan.FromMinutes(15)); // set event duration to 1 hour. varonehour = inputStream.AlterEventDuration(e => TimeSpan.FromHours(1)); // double event duration. var doubled = inputStream.AlterEventDuration(e => e.Lifetime * 2); // shift event 1 minute into the past, but leave duration unchanged. varnewStream = inputStream.AlterEventLifetime(TimeSpan.FromMinutes(-1), e => e.Lifetime + TimeSpan.FromMinutes(1)); // set lifetime to a single tick var points = inputStream.ToPointEventStream();

Working with CTI Entries SQL10R2UPD05-DEMO-04 Demo

Adapter Development Tasks

Adapter Types • Typed • Where payload definition is known • Generic • Untyped adapter • Payload is specified at adapter creation time from factory

Choose Adapter Base Class

AdapterFactory public class MyFactory :ITypedInputAdapterFactory<MyConfig>, ITypedDeclareAdvanceTimeProperties<MyConfig> { publicInputAdapterBase Create<Payload>(MyConfigconf, EventShapeev) { return newMyAdapter(conf); } public void Dispose() { ... } publicAdapterAdvanceTimeSettings DeclarAdvanceTimeProperties<Tpayload> (MyConfigconf, EventShapeev) { … return ats; } }

Adapter publicclassMyAdapter :TypedPointInputAdapter<MyPayload> { public MyAdapter(MyConfig conf) { ... } public override void Start() { ... } public override void Resume() { ... } }

UntypedAdapters • Useful where configuration provides field list • Must be available at query binding time • Able to configure a source or sink to handle multiple event types • Example sources • CSV files • SQL queries

Requirements for Untyped Adapters • Input Adapter • Needs to determine type of each field • Reads this from configuration parameters provided at query bind time • Output Adapter • Needs to retrieve results of query processing from a dequeued event • Bases this on configuration information • Each adapter instance supports only one event type

Push vs. Pull Model • Interaction points support both • Events pushed from provider to consumer • Events pulled by the consumer from the provider • Consumer can throttle input • Consumer communicates its ability to handle events • Policy to avoid overload of events

Compile and Test Adapter • Build the .NET assembly • Test with simple passthrough operations • Avoid complex query processing • Validate that the adapter is reading or writing

Creating an Input Adapter SQL10R2UPD05-DEMO-05 Demo

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

© 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.

StreamInsight 03 – Designing SQL Server 2008 R2 StreamInsight Event Models and Adapters