Published on April 16, 2008
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.