Complex Event Processing in Oracle RDBMS Use Case: Order Management & Advanced Pricing

1. Complex Event Processing in Oracle RDBMS Use Case: Order Management & Advanced Pricing Event Processing SymposiumNovember 8th 2006 Aravind Yalamanchi Principal Member of Technical Staff Server Technologies

2. 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%.

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

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

5. 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> THENPerformAction(porder.custId, ‘Notify’, ‘Multi-part Shipment’)

6. Rules Managed as Data in Relational tables OrderMgmt Event Structure OrderMgmtCBK Action Callback Procedure OrderMgmt ( porder PurchaseOrder, sitem ShipItem, truck TruckAtDock) ) PerformAction(..) Event Instances PurcahseOrder (2345, 8, 23456, …); ShipItem (‘RC123’,’Reusable Container’,2345, 5, …); Rule Identifier Rule Condition Rule Action Preferences OrderMgmtRC Rule Class (Table)

7. 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. App Server App Server Rules Application DB Change Notification Database App  Message Queues Message Queues Event instance Web Services & Other Web Services & Other Event Sources Event Consumers Events Repository Incremental Results

8. 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> THENPerformAction(porder.orderid, ‘Redirect’,‘High Priority Q’)

9. 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> THENPerformAction(porder.orderid, ‘Update Order Status’,‘Complete’)

10. 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> THENPerformAction(porder.orderid, ‘Discount’,‘15’)

11. 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> THENPerformAction(porder.orderid, ‘Discount’, ‘10’)

12. 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 eventis first added • Rules can be grouped into hierarchies to manage event abstractions at different levels.

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

14. Rules Manager feature of Oracle Database Processes events on multi-terabyte data sets w/ rules in the database Applications Generating Events PL/SQL APIs SQL JDBC SQL*Net WS ECARules & Indexing Event Policies Rules Results View Action Callback Procedure Persistent Events State Events Storage Oracle Database with Rules Manager • 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

15. Rules Manager Application Order Management System

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

17. 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>’ );

18. 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;

19. 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>');

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