public SqlOutputAdapter(SqlOutputConfig configInfo, CepEventType eventType, TOutputAdapter outputAdapter)
{
this.outputAdapter = outputAdapter;
this.config = configInfo;
this.bindTimeEventType = eventType;
ValidateConfigParameter(this.config.StartTimeColumnName);
ValidateConfigParameter(this.config.EndTimeColumnName);
ValidateConfigParameter(this.config.TableName);
}
public SqlOutputPoint(SqlOutputConfig config, CepEventType eventType)
{
// check if the config provides the required timestamp fields
if ((config.StartTimeColumnName == null) || (config.StartTimeColumnName.Length == 0))
{
throw new ArgumentException(string.Format(
CultureInfo.CurrentCulture,
@"* * * StartTimeStamp field not specified for this output adapter * * *"));
}
this.config = config;
this.outputAdapter = new SqlOutputAdapter <SqlOutputPoint, PointEvent>(config, eventType, this);
}
internal static void Main()
{
using (Server server = Server.Create("Default"))
{
Application application = server.CreateApplication("SqlApplication");
SqlInputConfig inputConfig = new SqlInputConfig
{
ConnectionString = @"integrated security = true; database = AdventureWorks",
Statement = // see here for schema: http://msdn.microsoft.com/en-us/library/ms124879.aspx
@"SELECT [SalesOrderID]
,[OrderDate]
,[ShipDate]
,[TerritoryID]
FROM [AdventureWorks].[Sales].[SalesOrderHeader]
WHERE [OrderDate] IS NOT NULL AND [ShipDate] IS NOT NULL AND [TerritoryID] IS NOT NULL
ORDER BY [OrderDate]",
StartTimeColumnName = "OrderDate",
EndTimeColumnName = "ShipDate",
CultureName = "en-US"
};
AdvanceTimeSettings inputAdvaceTimeSettings = new AdvanceTimeSettings(
new AdvanceTimeGenerationSettings((uint)1, TimeSpan.FromSeconds(0), true),
default(AdvanceTimeImportSettings),
default(AdvanceTimePolicy));
// define a source stream of interval events from SQL input data,
// the interval defined as the duration between the OrderDate and ShipDate
var streamSource = CepStream<SqlInput>.Create(
"SqlInputStream",
typeof(SqlInputFactory),
inputConfig,
EventShape.Interval,
inputAdvaceTimeSettings);
// find time intervals during which more than 3 orders are processed within a territory
// The result of this query is ""Between time T1 and T2, X many orders were processed in Y territory"
var kpiQuery = from o in streamSource
group o by o.TerritoryID into g
from window in g.SnapshotWindow(SnapshotWindowOutputPolicy.Clip)
select new { OrderCount = window.Count(), TerritoryID = g.Key }
into agg
where agg.OrderCount > 3
select agg;
// define a sink stream of interval events reporting territories and order count.
SqlOutputConfig outputConfig = new SqlOutputConfig
{
ConnectionString = @"integrated security = true; database = AdventureWorks",
Statement = @"INSERT INTO [AdventureWorks].[Dbo].[PeakSalesByTerritory]
( OrderDate
, ShipDate
, OrderCount
, TerritoryID)
VALUES (@OrderDate, @ShipDate, @OrderCount, @TerritoryID)",
StartTimeColumnName = "OrderDate",
EndTimeColumnName = "ShipDate",
TableName = "PeakSalesByTerritory"
};
// Create the table if not present already
CreateExampleTable(outputConfig.ConnectionString, outputConfig.TableName);
// set this to false to output to the table
bool outputToFile = false;
// Write output to a file (console) or to the output table itself
var query = outputToFile ?
kpiQuery.ToQuery(
application,
"KPIQuery",
@"Time intervals with order count > 3 and territories",
typeof(TextFileWriterFactory),
new TextFileWriterConfig { OutputFileName = string.Empty, Delimiter = '\t' },
EventShape.Interval,
StreamEventOrder.FullyOrdered) :
kpiQuery.ToQuery(
application,
"KPIQuery",
@"Time intervals with order count > 3 and territories",
typeof(SqlOutputFactory),
outputConfig,
EventShape.Interval,
// EventShape.Point,
StreamEventOrder.FullyOrdered);
Console.WriteLine("Start query");
// Start the query
query.Start();
// Wait for the query to be suspended - that is the state
// it will be in as soon as the output adapter stops due to
// the end of the input stream. Then retrieve diagnostics.
DiagnosticView dv = server.GetDiagnosticView(query.Name);
while ((string)dv[DiagnosticViewProperty.QueryState] == "Running")
{
// Sleep for 1s and check again
Thread.Sleep(1000);
dv = server.GetDiagnosticView(query.Name);
}
// Retrieve diagnostic information from the CEP server about the query.
Console.WriteLine(string.Empty);
RetrieveDiagnostics(server.GetDiagnosticView(new Uri("cep:/Server/Application/SqlApplication/Query/KPIQuery")), Console.Out);
query.Stop();
Console.WriteLine("\nPress Enter to quit the application");
Console.ReadLine();
}
}