SQL - the best development language for Big Data?

1. SQL - the best development language for Big Data? Exploring the Analytical Power of SQL in Oracle Database 12c

2. Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3. Keith Laker Senior Principal Product Manager Andrew Witkowski Architect SankarSubramanian Senior Director of Development

4. The On-Going Evolution of SQL 5 4

5. The On-Going Evolution of SQL • Introduction of Window functions 5 4

6. The On-Going Evolution of SQL • Statistical functions • SQL model clause • Partition Outer Join • Data mining I • Introduction of Window functions 5 4 • Enhanced Window functions (percentile,etc) • Rollup, grouping sets, cube

7. The On-Going Evolution of SQL • Statistical functions • SQL model clause • Partition Outer Join • Data mining I • Introduction of Window functions 5 4 • Data mining II • SQL Pivot • Recursive WITH • ListAgg, Nth value window • Enhanced Window functions (percentile,etc) • Rollup, grouping sets, cube

8. The On-Going Evolution of SQL • Statistical functions • SQL model clause • Partition Outer Join • Data mining I • Introduction of Window functions • Pattern matching • Top N clause • Data Mining III 5 4 • Data mining II • SQL Pivot • Recursive WITH • ListAgg, Nth value window • Enhanced Window functions (percentile,etc) • Rollup, grouping sets, cube

9. Finding Patterns in Big Data Typical use cases in today’s world of fast exploration of big data Big Data Tracking Stock Market Session-ization Returns Fraud Money Laundering Buying Patterns Fraud Financial Services Retail

10. Finding Patterns in Big Data Typical use cases in today’s world of fast exploration of big data Big Data Tracking Stock Market Session-ization Returns Fraud Money Laundering Buying Patterns Fraud Financial Services Retail

11. Finding Patterns in Big Data Typical use cases in today’s world of fast exploration of big data Law & Order Big Data Utilities Tracking Stock Market Monitoring Suspicious Activities Session-ization SIM Card Fraud Returns Fraud Call Quality Money Laundering Buying Patterns Money Laundering Network Analysis Unusual Usage Fraud Fraud Financial Services Retail Telcos

12. SQL Pattern MatchingKey Concepts

13. Pattern Recognition In Sequences of Rows “SQL Pattern Matching” - Concepts • Recognize patterns in sequences of events using SQL • Sequence is a stream of rows • Event equals a row in a stream • New SQL construct MATCH_RECOGNIZE • Logically partition and order the data • ORDER BY mandatory (optional PARTITION BY) • Pattern defined using regular expression using variables • Regular expression is matched against a sequence of rows • Each pattern variable is defined using conditions on rows and aggregates

14. SQL Pattern Matching in action Stock price Find a W-shape pattern in a ticker stream: Output the beginningand endingdate of the pattern Calculate average price each the W-shape Find only patterns that lasted less than a week Example: Find adouble bottom pattern (W-shape) in ticker stream days

15. SQL Pattern Matching in action Stock price Example: Find W-Shape New syntax for discovering patterns using SQL: MATCH_RECOGNIZE ( ) SELECT . . . FROM ticker MATCH_RECOGNIZE ( . . . ) days

16. SQL Pattern Matching in action Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Set the PARTITION BY and ORDER BY clauses SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time days

17. SQL Pattern Matching in action Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Define the pattern– the “W-shape” SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time PATTERN (X+ Y+ W+ Z+) days

18. SQL Pattern Matching Building Regular Expressions • Concatenation: no operator • Quantifiers: • * 0 or more matches • + 1 or more matches • ? 0 or 1 match • {n} exactly n matches • {n,} n or more matches • {n, m} between n and m (inclusive) matches • {, m} between 0 an m (inclusive) matches • Reluctant quantifier – an additional ?

19. SQL Pattern Matching in action X Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Define the pattern– the first down part of the “W-shape” SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), days

20. SQL Pattern Matching in action X Y Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Define the pattern– the first up part of “W-shape” SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), days

21. SQL Pattern Matching in action X Y W Z Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Define the pattern– the second down (w) and the second up(z) of the “W-shape” SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price))) days

22. SQL Pattern Matching in action X Z Stock price Example: Find W-Shape Find a W-shape pattern in a ticker stream: Define the measures to output once a pattern is matched: • FIRST: beginning date • LAST: ending date SELECT … FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price))) days

23. SQL Pattern Matching in action Stock price Find a W-shape pattern in a ticker stream: Output one row each time we find a match to our pattern Example: Find W-Shape SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price))) 9 13 1 days 19

24. SQL Pattern Matching Stock price “Declarative”Pattern Matching, cont. • What rows to return • ONE ROW PER MATCH • ALL ROWS PER MATCH • ALL ROWS PER MATCH WITH UNMATCHED ROWS • After match SKIP option : • SKIP PAST LAST ROW • SKIP TO NEXT ROW • SKIP TO <VARIABLE> • SKIP TO FIRST(<VARIABLE>) • SKIP TO LAST (<VARIABLE>) days

25. Can refer to previous variables SQL Pattern Matching in action X Z Stock price Find a W-shape pattern in a ticker stream: Extend the pattern to find W-shapes that lasted less than a week Example: Find W-Shape lasts < 7 days SELECT first_x, last_z FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND z.time - FIRST(x.time) <= 7 )) 9 13 1 days 19

26. SQL Pattern Matching in action Average stock price: $52.00 Stock price Find a W-shape pattern in a ticker stream: Calculate average price in the second ascent Example: Find average price within W-Shape SELECT first_x, last_z, avg_price FROM ticker MATCH_RECOGNIZE ( PARTITION BY name ORDER BY time MEASURES FIRST(x.time) AS first_x, LAST(z.time) AS last_z, AVG(z.price) AS avg_price ONE ROW PER MATCH PATTERN (X+ Y+ W+ Z+) DEFINE X AS (price < PREV(price)), Y AS (price > PREV(price)), W AS (price < PREV(price)), Z AS (price > PREV(price) AND z.time - FIRST(x.time) <= 7 )))) 9 13 1 days 19

27. SQL Pattern Matching Syntax <table_expression> := <table_expression> MATCH_RECOGNIZE ( [ PARTITION BY <cols> ] [ ORDER BY <cols> ] [ MEASURES <cols> ] [ ONE ROW PER MATCH | ALL ROWS PER MATCH ] [ SKIP_TO_option ] PATTERN ( <row pattern> ) [ SUBSET <subset list> ] DEFINE <definition list> )

28. SQL Pattern Matching in action Example: Sessionizationfor user log • Define a session as a sequence of one or more events with the same partition key where the inter-timestamp gap is less than a specified threshold • Example “user log analysis” • Partition key: User ID, Inter-timestamp gap: 10 (seconds) • Detect the sessions • Assign a within-partition (per user) surrogate Session_ID to each session • Annotate each input tuple with its Session_ID

29. SQL Pattern Matching in action Example Sessionization for user log

30. SQL Pattern Matching in action Example Sessionization for user log Identify sessions

31. SQL Pattern Matching in action Example Sessionization for user log Number Sessions per user Identify sessions

32. SQL Pattern Matching in action Example Sessionization for user log: MATCH_RECOGNIZE • . . . • FROM Events MATCH_RECOGNIZE • (PARTITION BY user_ID ORDER BY time • MEASURES match_number() as session_id • ALL ROWS PER MATCH • PATTERN (b s*) • DEFINE • s as (s.time - prev(s.time) <= 10) • );

33. SQL Pattern Matching in action Example Sessionization – Aggregation of sessionized data • Primitive sessionization only a foundation for analysis • Mandatory to logically identify related events and group them • Aggregation for the first data insight • How many “events” happened within an individual session? • What was the total duration of an individual session?

34. SQL Pattern Matching in action Example Sessionization – Aggregation of sessionized data Aggregate sessions per user

35. SQL Pattern Matching Example Sessionization – Aggregation: ONE ROW PER MATCH . . . FROM Events MATCH_RECOGNIZE (PARTITION BY user_ID ORDER BY time ONE ROW PER MATCH MEASURES match_number() session_id, count(*) as no_of_events, first(time) start_time, last(time) - first(time) duration PATTERN (b s*) DEFINE s as (s.time - prev(time) <= 10) ) ORDER BY user_id, session_id;

36. SQL Pattern MatchingSample use cases

37. SQL Pattern Matching Example Call Detail Records Analysis • The to-be-sessionized phenomena are characterized by • Start_Time, End_Time • Caller_ID, Callee_ID

38. SQL Pattern Matching Example Call Detail Records Analysis prior to Oracle Database 12c With Sessionized_Call_Details as (select Caller, Callee, Start_Time, End_Time, Sum(case when Inter_Call_Intrvl < 60 then 0 else 1 end) over(partition by Caller, Callee order by Start_Time) Session_ID from (select Caller, Callee, Start_Time, End_Time, (Start_Time - Lag(End_Time) over(partition by Caller, Callee order by Start_Time)) Inter_Call_Intrvl from Call_Details)), Inter_Subcall_Intrvls as (select Caller, Callee, Start_Time, End_Time, Start_Time - Lag(End_Time) over(partition by Caller, Callee, Session_ID order by Start_Time) Inter_Subcall_Intrvl, Session_ID from Sessionized_Call_Details) Select Caller, Callee, Min(Start_Time) Start_Time, Sum(End_Time - Start_Time) Effective_Call_Duration, Nvl(Sum(Inter_Subcall_Intrvl), 0) Total_Interuption_Duration, (Count(*) - 1) No_Of_Restarts, Session_ID from Inter_Subcall_Intrvls group by Caller, Callee, Session_ID;

39. SQL Pattern Matching Example Call Detail Records Analysis using SQL Pattern Matching With Sessionized_Call_Details as (select Caller, Callee, Start_Time, End_Time, Sum(case when Inter_Call_Intrvl < 60 then 0 else 1 end) over(partition by Caller, Callee order by Start_Time) Session_ID from (select Caller, Callee, Start_Time, End_Time, (Start_Time - Lag(End_Time) over(partition by Caller, Callee order by Start_Time)) Inter_Call_Intrvl from Call_Details)), Inter_Subcall_Intrvls as (select Caller, Callee, Start_Time, End_Time, Start_Time - Lag(End_Time) over(partition by Caller, Callee, Session_ID order by Start_Time) Inter_Subcall_Intrvl, Session_ID from Sessionized_Call_Details) Select Caller, Callee, Min(Start_Time) Start_Time, Sum(End_Time - Start_Time) Effective_Call_Duration, Nvl(Sum(Inter_Subcall_Intrvl), 0) Total_Interuption_Duration, (Count(*) - 1) No_Of_Restarts, Session_ID from Inter_Subcall_Intrvls group by Caller, Callee, Session_ID; SELECT Caller, Callee, Start_Time, Effective_Call_Duration, (End_Time - Start_Time) - Effective_Call_Duration AS Total_Interruption_Duration, No_Of_Restarts, Session_ID FROM call_details MATCH_RECOGNIZE ( PARTITION BY Caller, Callee ORDER BY Start_Time MEASURES A.Start_Time AS Start_Time, End_Time AS End_Time, SUM(End_Time - Start_Time) as Effective_Call_Duration, COUNT(B.*) as No_Of_Restarts, MATCH_NUMBER() as Session_ID PATTERN (A B*) DEFINE B as B.Start_Time - prev(B.end_Time) < 60) ;

40. SQL Pattern Matching Example Suspicious Money Transfers • Detect suspicious money transfer pattern for an account • Three or more small amount (<1K) money transfers within 30 days • Subsequent large transfer (>=1M) within 10 days of last small transfer. • Report account, date of first small transfer, date of last large transfer Three small transfers within 30 days

41. SQL Pattern Matching Example Suspicious Money Transfers • Detect suspicious money transfer pattern for an account • Three or more small amount (<1K) money transfers within 30 days • Subsequent large transfer (>=1M) within 10 days of last small transfer. • Report account, date of first small transfer, date of last large transfer Three small transfers within 30 days Large transfer within 10 days of last small transfer

42. SQL Pattern Matching Example Suspicious Money Transfers SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X ) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30) Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 ) Single record criteria

43. SQL Pattern Matching Example Suspicious Money Transfers SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X ) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30) Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 ) Single record criteria Record set criteria

44. SQL Pattern Matching Example Suspicious Money Transfers SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X ) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30) Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 ) Any event (not X), followed by X

45. SQL Pattern Matching Example Suspicious Money Transfers SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X ) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30) Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 ) Any event (not X), followed by X (Any event (not X), followed by X) happens three or more times within 30 days

46. SQL Pattern Matching Example Suspicious Money Transfers SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X ) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30) Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 ) Any event (not X), followed by X Followed by Y within 10 days of last X (Any event (not X), followed by X) happens three or more times within 30 days

47. SQL Pattern Matching Example Suspicious Money Transfers - Refined • Detect suspicious money transfer pattern between accounts • Three or more small amount (<1K) money transfers within 30 days • Transfers to different accounts (total sum of small transfers small (20K)) • Subsequent large transfer (>=1M) within 10 days of last small transfer. • Report account, date of first small transfer, date last large transfer Three small transfers within 30 days to different acct and total sum < 20K Large transfer within 10 days of last small transfer

48. SQL Pattern Matching Example Suspicious Money Transfers - Refined SELECT user_id, first_t, last_t, amount FROM event_logMATCH_RECOGNIZE (PARTITION BY user_idORDER BY time) PATTERN ( (; X) {3,} Y ) MEASURES user_id, first(x.timestamp) first_t, y.timestamplast_t, y.amount DEFINE X AS (event = ‘Transfer’ AND amount < 1,000 AND last(X.timestamp) - first(X.timestamp) < 30 AND prev(X.transfer_to) <> X.transfer_to Y AS (event = ‘Transfer’ AND amount > 1,000,000 AND y.timestamp - last(x.timestamp) < 10 AND sum(X.amount) < 20,000);

49. Pattern Matching with SQL Analytics Java vs. SQL: Stock Markets - Searching for ‘W’ Patterns in Trade Data

50. Pattern Matching with SQL Analytics Java vs. SQL: Stock Markets - Searching for ‘W’ Patterns in Trade Data public Tuple exec(Tuple input) throws IOException { private booleaneq(String a, String b) { private booleangt(String a, String b) { package pigstuff; import java.io.IOException; import java.util.ArrayList; import java.util.Iterator; import org.apache.pig.EvalFunc; import org.apache.pig.PigException; import org.apache.pig.backend.executionengine.ExecException; import org.apache.pig.data.BagFactory; import org.apache.pig.data.DataBag; import org.apache.pig.data.DataType; import org.apache.pig.data.Tuple; import org.apache.pig.data.TupleFactory; import org.apache.pig.impl.logicalLayer.FrontendException; import org.apache.pig.impl.logicalLayer.schema.Schema; /** * * @author nbayliss */ private class V0Line { String state = null; String[] attributes; String prev = "”; String next = ””; public V0Line(String[] atts) { attributes = atts; } public String[] getAttributes() { return attributes; } public void setState(String state) { this.state = state; } public String setState(V0Line linePrev, V0Line lineNext) { @Override public Schema outputSchema(Schema input) { Schema.FieldSchemalinenumber = new Schema.FieldSchema("linenumber", DataType.CHARARRAY); Schema.FieldSchemapbykey = new Schema.FieldSchema("pbykey", DataType.CHARARRAY); Schema.FieldSchema count = new Schema.FieldSchema("count", DataType.LONG); Schema tupleSchema = new Schema(); tupleSchema.add(linenumber); tupleSchema.add(pbykey); tupleSchema.add(count); return new Schema(tupleSchema); } } 250+ Lines of Java and PIG