2 Oracle Yalamachi Complex Event Processing in the

Information about 2 Oracle Yalamachi Complex Event Processing in the

Published on April 16, 2008

Author: Irvette

Source: authorstream.com

Content

Complex Event Processing in Oracle RDBMS Use Case: Order Management & Advanced Pricing :  Complex Event Processing in Oracle RDBMS Use Case: Order Management & Advanced Pricing Event Processing Symposium November 8th 2006 Aravind Yalamanchi Principal Member of Technical Staff Server Technologies Complex Event Processing in RDBMS : Goals:  Complex Event Processing in RDBMS : Goals React to streaming data as well as evolving data in the database Handle continuous queries capable of identifying new results in real-time and acting on them. Reorder an item from the supplier when a new purchase order pushes the number of items in the warehouse below a threshold. Coordinate composite requests involving data distributed over time and application space. Raise an exception when the purchase order from a gold customer is not shipped within two days of the order placement. Enable real-time analytics and decision making. Alert the account manager for a customer when the average size of the cancelled orders in the last 30 days is greater than the 60 day average by 25%. Approach: Event-Condition-Action Rules:  Approach: Event-Condition-Action Rules Rules are processed for events that arise from Event submissions through programmatic interfaces, message queues, or web-services requests. Transactional or Non-transactional changes to data Non-occurrence of some expected change (exception) Rule conditions are expressed using SQL and XML syntax SQL-WHERE clause for identifying individual events of interest (filtering) using predicates on Scalar, XML, Spatial, or Text data. XML to correlate events and create complex event patterns Action preferences determine the exact action for each rule Pre-packaged actions can be executed in the DB – Alerts, Enqueue, Mail, Web-service requests etc.,. Results from rule evaluation can be obtained as a SQL result set by querying a view for application level actions. Types of Event Relationships in ECA Rules :  Types of Event Relationships in ECA Rules Conjunctions All the specified events happened Sequencing The events happened in the specified order Disjunction (any n) Any n of the m specified events happened Temporal association Events happened within n time units of each other Negation An event did not happen within a deadline Aggregation Collections of events following sliding window semantics Order Management System – Sample Rule:  Order Management System – Sample Rule If the purchase order from a Gold customer contains more than one item and the order is partially filled, send a multi-part shipment report to the customer. ON PurchaseOrder (orderId, itemCount, custId, exptdDate ..) porder, ShipItem (itemId, itemType, orderId, itemCount, ..) sitem IF <condition> <and join= “porder.orderId = sitem.orderId and order.itemCount > sitem.itemCount”> <object name= “porder”> itemCount > 1 and CustomerType(custId) = ‘Gold’ </object> <object name= “sitem”> itemType = ‘Reusable Container’ </object> </and> </condition> THEN PerformAction(porder.custId, ‘Notify’, ‘Multi-part Shipment’) Rules Managed as Data in Relational tables:  Rules Managed as Data in Relational tables OrderMgmtRC Rule Class (Table) OrderMgmt ( porder PurchaseOrder, sitem ShipItem, truck TruckAtDock) ) OrderMgmt Event Structure Rule Identifier Rule Condition Rule Action Preferences PerformAction(..) OrderMgmtCBK Action Callback Procedure ShipItem (‘RC123’,’Reusable Container’,2345, 5, …); Event Instances PurcahseOrder (2345, 8, 23456, …); ECA Rules - Evaluation:  ECA Rules - Evaluation A rule condition is considered true when a set of primitive events satisfying the predicates on the event data also satisfy the event pattern specification. Each rule condition acts as state machine that reaches an accepting state when the rule condition evaluates to true. The outcome of a rule matching a set of primitive events is a higher-level event, or a composite event consisting of all the primitive events. Event Sources App Server DB Change Notification Message Queues Web Services & Other Rules Application Event Consumers App Server Web Services & Other Message Queues Database App Event instance Events Repository Incremental Results  Non-occurrence of events:  Non-occurrence of events Route the order to a high priority queue if there is a chance for it to get delayed. ON PurchaseOrder (orderId, itemCount, custId, exptdDate ..) porder, ShipItem (itemId, itemType, orderId, itemCount, ..) sitem IF <condition> <and equal= “porder.orderId, sitem.orderId”> <object name= “porder”> CustomerType(custId) = ‘Gold’ </object> <not by=“porder.exptdDate-2”> <object name= “sitem”/> </not> </and> </condition> THEN PerformAction(porder.orderid, ‘Redirect’,‘High Priority Q’) Composite event with Collections:  Composite event with Collections Mark the order as complete when the number of items shipped equal the number of items ordered. ON PurchaseOrder (orderId, itemCount, custId, ..) porder, ShipItem (itemId, itemType, orderId, itemCount, ..) sitem IF <condition> <and equal = “porder.orderId, sitem.orderId” having = "count(sitem.*) = porder.itemCount“> <object name= “porder”/> <collection name=“sitem” groupby="orderId“> itemType != ‘Reusable Container’ </collection> </and> </condition> THEN PerformAction(porder.orderid, ‘Update Order Status’,‘Complete’) Collection of events – Advanced Pricing Rule:  Collection of events – Advanced Pricing Rule Offer a 15% discount on the current purchase order if the customer has at least 10 orders over 10000 dollars in the past year. ON PurchaseOrder (orderId, itemCount, custId, exptdDate, amount) porder IF <condition> <collection name = “porder” groupby = “custId” windowlen = “365” having = “count(*) >= 10”> amount > 10000 </collection> </condition> THEN PerformAction(porder.orderid, ‘Discount’,‘15’) Collection of events – Advanced Pricing Rule:  Collection of events – Advanced Pricing Rule Offer a 10% discount on the current purchase order if the average size of the last 5 orders is over 10000 dollars. ON PurchaseOrder (orderId, itemCount, custId, exptdDate, amount) porder IF <condition> <collection name = “porder”> groupby =“custId” windowsize = “5” having = “avg(amount) > 10000”/> </condition> THEN PerformAction(porder.orderid, ‘Discount’, ‘10’) Logical Grouping of Rules:  Logical Grouping of Rules Declarative policies for event lifecycle management Consumption: Specification for event reuse EXCLUSIVE: At most one matching rule executed with the event SHARED: Event used for multiple rule executions RULE: Custom consumption policy on a per rule basis Conflict Resolution: To control the order of rule execution SQL ORDER BY Clause involving some event and/or rule attributes Duration: Lifetime of unconsumed events TRANSACTION/SESSION: Until the end of database tx./session CALL: Event valid only at the instance it is added N Units of Time: Elapsed time since the event is first added Rules can be grouped into hierarchies to manage event abstractions at different levels. Conclusions:  Conclusions CEP in the database – Production since July 2005 (Oracle 10.2) Scalable event detection and correlation with persistent (recoverable) state Support for long running event scenarios Support for large and dynamic rules sets Support for rich data types Declarative policies for event management Technologies used Expression Filter for performance and scalability Database Change Notification, Triggers, Message queues, and Web service requests as event sources SQL and XML based rule condition language for expressibility UI modeling tools for ease-of-use http://www.oracle.com/technology/products/database/rules_manager/index.html Rules Manager feature of Oracle Database:  Rules Manager feature of Oracle Database Rules Results View Applications Generating Events ECA Rules & Indexing Persistent Events State Event Policies Action Callback Procedure Oracle Database with Rules Manager Events Storage SQL PL/SQL APIs JDBC SQL*Net Persistent state for incremental evaluation & event coordination Indexing to identify applicable rules within large rule sets Any Oracle data type in rule conditions, including XML, spatial Declarative event relationships: AND, ANY, NOT, sets, order, time Dynamic, batch and DML events w/ policies to control behavior Processes events on multi-terabyte data sets w/ rules in the database WS Rules Manager Application Order Management System:  Rules Manager Application Order Management System Rules Application - Step 1:  Rules Application - Step 1 Create the Object type(s) that represent the event structure(s). TYPE PurchaseOrder as OBJECT ( -- Primitive event type -- orderId NUMBER, custId NUMBER, exptdDate DATE, itemId NUMBER, itemCount NUMBER, amount NUMBER); TYPE ShipItem as OBJECT ( orderId NUMBER, itemId NUMBER, itemType VARCHAR(30), loadId NUMBER); TYPE TruckAtDock as OBJECT ( loadId NUMBER, truckId NUMBER, status VARCHAR2(30), capacity NUMBER); TYPE OrderMgmt as OBJECT ( -- Composite event type -- porder PurchaseOrder, sitem ShipItem, truck TruckAtDock); Alternately, the OrderMgmt type can be configured to make use of PurchaseOrder, ShipItem, and TruckAtDock tables with corresponding columns. Rules Application - Step 2:  Rules Application - Step 2 Create the Rule Class using the event structure(s) created in step 1 and set the rule class properties that include event management policies. DBMS_RLMGR.CREATE_RULE_CLASS ( rule_class => ‘OrderMgmtRC’, -- Rule Class Name -- event_struct => ‘OrderMgmt’, -- Event structure name -- action_cbk => ‘OrderMgmtCBK’, -- Action callback proc -- actprf_spec => -- Action pref categories -- ‘actionType VARCHAR(40), actionParam VARCHAR(100)’, rslt_viewnm => 'MatchedRules', -- Results view (optional) – rlcls_prop => -- Rule class properties -- ‘<composite equal="(porder.orderId, sitem.orderId) | (sitem.loadId, truck.loadId)“ ordering="rlm$rule.rlm$ruleid, porder.orderid”> <collection type=“PurchaseOrder” ... /> </composite>’ ); Rules Application - Step 3:  Rules Application - Step 3 Implement the action callback procedure’s body to perform the appropriate action for each rule matching a set of primitive events. The skeleton for the procedure is generated by the rule class creation step. CREATE OR REPLACE PROCEDURE OrderMgmtCBK ( porder PurchaseOrder, -- primitive events that matched the rule -- sitem ShipItem, trucl TruckAtDock, rlm$rule OrderMgmtRC%ROWTYPE) IS -- rule that matched -- BEGIN PerformAction(decode(porder.orderId, null, ..), rlm$rule.actionType, rlm$rule.actionParam); END; Rules Application - Step 4:  Rules Application - Step 4 Add rules to the rule class using standard DML statements (or UI tools). INSERT INTO OrderMgmtRC (rlm$ruleid, actionType, actionParam, rlm$ruledesc, rlm$rulecond) values ('Delayed Order redirect', 'REDIRECT','HIGH_PRIORITY_QUEUE', 'Route the order to a high priority queue if there is a chance for it to get delayed', '<condition> <and equal= “porder.orderId, sitem.orderId”> <object name= “porder”> CustomerType(custId) = ‘Gold’ </object> <not by=“porder.exptdDate-2”> <object name= “sitem”/> </not> </and> </condition>'); Rules Application - Step 5:  Rules Application - Step 5 Process the rules for the primitive events. dbms_rlmgr.process_rules( rule_class => ‘OrderMgmtRC’, event_inst => AnyData.convertObject( PurchaseOrder(12345, 345, ’11-Nov-2006’, ...))); dbms_rlmgr.process_rules( rule_class => ‘OrderMgmtRC’, event_inst => AnyData.convertObject( ShipItem(12345, 567, ’Reusable Container’, ...))); The action callback procedure is called when an event causes a rule to reach an accepting state.

Related presentations


Other presentations created by Irvette

Propaganda
29. 12. 2007
0 views

Propaganda

lecture3 yeast
16. 10. 2007
0 views

lecture3 yeast

Giving Bad News
28. 09. 2007
0 views

Giving Bad News

Nutrient Mobility 26
04. 10. 2007
0 views

Nutrient Mobility 26

bad honnef
05. 10. 2007
0 views

bad honnef

olsug 0402 10Hannay Nature
08. 10. 2007
0 views

olsug 0402 10Hannay Nature

Presentation Japan
09. 10. 2007
0 views

Presentation Japan

IRAN and RUSSIA Oil Gas Policies
11. 10. 2007
0 views

IRAN and RUSSIA Oil Gas Policies

present
12. 10. 2007
0 views

present

Presentation Russia
12. 10. 2007
0 views

Presentation Russia

Aula 6
15. 10. 2007
0 views

Aula 6

13 Equine Sciences
19. 10. 2007
0 views

13 Equine Sciences

Panama Canal powerpoint
22. 10. 2007
0 views

Panama Canal powerpoint

Cathia Palmer MI BANCO
22. 10. 2007
0 views

Cathia Palmer MI BANCO

MOROCCO Time Line Power Point
23. 10. 2007
0 views

MOROCCO Time Line Power Point

clim forecast
02. 10. 2007
0 views

clim forecast

5004
10. 12. 2007
0 views

5004

Quake06 template v3
29. 10. 2007
0 views

Quake06 template v3

Hendra
31. 10. 2007
0 views

Hendra

Veschetti PL
01. 11. 2007
0 views

Veschetti PL

imo bw dip con highlights
06. 11. 2007
0 views

imo bw dip con highlights

itt recruitment challenges wales
06. 11. 2007
0 views

itt recruitment challenges wales

showcase
17. 10. 2007
0 views

showcase

fuel tank safety
07. 11. 2007
0 views

fuel tank safety

CClub2 11 08 05 Part 2
19. 11. 2007
0 views

CClub2 11 08 05 Part 2

2006StatsRRTC 3 4
29. 11. 2007
0 views

2006StatsRRTC 3 4

Hospitality Industry Workforce
14. 12. 2007
0 views

Hospitality Industry Workforce

Goff
06. 12. 2007
0 views

Goff

florence1
31. 10. 2007
0 views

florence1

cindy zheng update
17. 10. 2007
0 views

cindy zheng update

Town Hall PowerPoint 6 14 04
18. 10. 2007
0 views

Town Hall PowerPoint 6 14 04

abc5fm
09. 10. 2007
0 views

abc5fm

D Baker RadBelt
30. 10. 2007
0 views

D Baker RadBelt

Thursday
19. 10. 2007
0 views

Thursday

humn presentation
17. 10. 2007
0 views

humn presentation

berkeley04r
28. 11. 2007
0 views

berkeley04r

Hybridmedia in Japan 2005 small
09. 10. 2007
0 views

Hybridmedia in Japan 2005 small

osipenko
15. 10. 2007
0 views

osipenko

Henry VIIIs Problems
05. 10. 2007
0 views

Henry VIIIs Problems

NWAV 2007
31. 10. 2007
0 views

NWAV 2007

Rhetoric AmerDream ppt
19. 02. 2008
0 views

Rhetoric AmerDream ppt

AW12
29. 10. 2007
0 views

AW12

Small Arms Marking Overview
26. 02. 2008
0 views

Small Arms Marking Overview

HazeConsult 032207
05. 10. 2007
0 views

HazeConsult 032207

09 EMI
05. 11. 2007
0 views

09 EMI

class10 22
28. 02. 2008
0 views

class10 22

L17 PT2 Splicing1
15. 10. 2007
0 views

L17 PT2 Splicing1

ES110 Hazardous Waste
10. 03. 2008
0 views

ES110 Hazardous Waste

003 Glenn Jerome
16. 03. 2008
0 views

003 Glenn Jerome

explorer
25. 03. 2008
0 views

explorer

Lect 10A Tides
03. 04. 2008
0 views

Lect 10A Tides

chap19
08. 04. 2008
0 views

chap19

leb shaw
13. 04. 2008
0 views

leb shaw

gelisim 1
16. 10. 2007
0 views

gelisim 1

rm2 lautanala
19. 10. 2007
0 views

rm2 lautanala

Kickoff Poster Italy 1
31. 10. 2007
0 views

Kickoff Poster Italy 1

Collateral Damage 9 27 06
22. 04. 2008
0 views

Collateral Damage 9 27 06

Food show Big
05. 01. 2008
0 views

Food show Big

B1 U10 A
06. 05. 2008
0 views

B1 U10 A

EC2004 ch10
07. 05. 2008
0 views

EC2004 ch10

Home Hemo dialysis Presentation
07. 05. 2008
0 views

Home Hemo dialysis Presentation

CBMB discrimination
08. 05. 2008
0 views

CBMB discrimination

Youth Program Presentation
08. 05. 2008
0 views

Youth Program Presentation

5 Hall evidence based medicine
08. 05. 2008
0 views

5 Hall evidence based medicine

Remote Learning VATAT
02. 05. 2008
0 views

Remote Learning VATAT

Heart Reports
02. 05. 2008
0 views

Heart Reports

CDC033 Presentation
02. 05. 2008
0 views

CDC033 Presentation

ProviderFraudTrainin gPartII
02. 05. 2008
0 views

ProviderFraudTrainin gPartII

TEXPEC ACP program
02. 05. 2008
0 views

TEXPEC ACP program

Presentation30
11. 10. 2007
0 views

Presentation30

raceway Jarnigan
12. 10. 2007
0 views

raceway Jarnigan

Presentation MAROC 10 2006
24. 10. 2007
0 views

Presentation MAROC 10 2006

MoscowPres2fin GK 220207
26. 10. 2007
0 views

MoscowPres2fin GK 220207

131
18. 04. 2008
0 views

131

Boeing2003
24. 10. 2007
0 views

Boeing2003

morocco1105
24. 10. 2007
0 views

morocco1105

Linux101
02. 11. 2007
0 views

Linux101

Airspace needs
09. 10. 2007
0 views

Airspace needs

JackieWilliamson Sabah
13. 03. 2008
0 views

JackieWilliamson Sabah

WVEB NCSM
08. 10. 2008
0 views

WVEB NCSM

Presentacion AIDS Helder Mario
28. 12. 2007
0 views

Presentacion AIDS Helder Mario

PURI SCAG Presentation 111601 1
10. 04. 2008
0 views

PURI SCAG Presentation 111601 1

EQ OEM Presentation Nov04
02. 05. 2008
0 views

EQ OEM Presentation Nov04

Mr Anup Banerji ppt to CII
11. 12. 2007
0 views

Mr Anup Banerji ppt to CII

AdrianaMaria2
24. 10. 2007
0 views

AdrianaMaria2

El mundo del espectaculo2
02. 11. 2007
0 views

El mundo del espectaculo2

ECS2006PresentationGB
15. 10. 2007
0 views

ECS2006PresentationGB

SURF X4L
12. 10. 2007
0 views

SURF X4L

Schulte
06. 03. 2008
0 views

Schulte

Valtio luennot
20. 11. 2007
0 views

Valtio luennot

trw vortrag 18 03 04
16. 11. 2007
0 views

trw vortrag 18 03 04

GA GTIPGTEJones2
03. 10. 2007
0 views

GA GTIPGTEJones2

interim03
10. 10. 2007
0 views

interim03

haulintr
27. 02. 2008
0 views

haulintr

pandrangi
29. 09. 2007
0 views

pandrangi

Disz
01. 05. 2008
0 views

Disz

ETW2005SeminarVIII
23. 10. 2007
0 views

ETW2005SeminarVIII

Galkin prospecting
30. 10. 2007
0 views

Galkin prospecting

edpre040505
07. 10. 2007
0 views

edpre040505

sensor mama
03. 10. 2007
0 views

sensor mama

2nd European GIC
07. 01. 2008
0 views

2nd European GIC