Tag Archives: data-warehousing

JavaOne – Simplified and Fast Fraud Detection

Posted on by
Reply

Simplified and Fast Fraud Detection”

Speaker: Keith Laker

For more blog posts from JavaOne, see the table of contents

Live SQL

  • free online Oracle 12C database
  • Can save scripts
  • Google searchable
  • Each OTN (oracle tech network) users sees own copy of data. Sandboxed
  • Can download data as CSV

https://livesql.oracle.com/apex/livesql/file/index.html

And for this session the live sql URL

Pattern Matching

  • types – regex, sed/awk
  • in SQL – row level regex
  • new: pattern recognition in a stream or rows – aka can match across rows and columns
  • new SQL construct MATCH_RECOGNIZE – ANSII standard; not Oracle specific

Steps

  1. Bucket and order the data
    • This makes the patterns “visible”.
    • Used order by or partition by/order by so queries are deterministic (this does not require the paid Oracle partitioning feature)
  2. Define the pattern
    • Regular expression like pattern
    • Ex: PATTERN (X+ Y+ Z+) where X/Y/Z is a boolean expression. Ex: bal < PREV(bal)
    • Common qualifiers: * + ? {n} {n,} {n,m}
    • Also have extra ? for reluctant qualifiers – helps deal with what to do with overlapping matches
  3. Define measures
    • Define columns in output table
    • pattern navigation options; PREV, NEXT, FIRST, LAST
    • column
    • optional aggregates (COUNT, SUM, AVG, MAX, MIN)
    • special measures: CLASSIFIER() – which component of the pattern applied to this row and MATCH_NUMBER() – how many matches within each partition – both are good for debugging
    • Ex: MEASURES FIRST(x.tstamp) as first_x
  4. Controlling output
    • by default get a column per measure along with the partitioning column (when using one row per match). Get more columns with all rows per match)
    • how many rows back: ONE ROW PER MATCH (default) ALL ROWS PER MATCH or ALL ROWS PER MATCH WITH UNMATCHED ROWS (good for debugging)
    • where to start next search: AFTER MATCH SKIP PAST LAST ROW (default), also options for next row and relating to variables

Demo

  • Find 3 or more small (<2K) money transfers within 30 days. Then find large transfer (?=1M) within 10 days of last small transfer
  • Can do in SQL without pattern matching, but a lot of code.
  • Can do in Java, but. [copying the database…]
  • Showed how to create a table for JSON data – reads into a CLOB and Oracle checks it is valid JSON. Loaded with insert statements because live sql is web based and can’t access underlying file system.
  • Can use dot notation to access SQL fields

Sample pattern matching statement:


SELECT *
FROM transfers_view
MATCH_RECOGNIZE(
 ORDER BY time_id
 MEASURES
 user_id AS user_id,
 amount AS amount
 PATTERN (X{3,} Y)
 DEFINE
 X AS (amount < 2000) AND 
 LAST(time_id) - FIRST(time_id) < 30,
 Y AS (amount >= 1000000) AND 
 time_id - LAST(x.time_id)< 10);

My take: This was a two hour “tutorial” which differs from a hands on lab. We were still able to follow along with a laptop or “large tablet.” I followed along with the demos on my Mac. Which also let me play a bit. It was fun. I’ve always liked SQL :). I like that he uses QR codes for the links/blogs he wants people to go to. They are also linked in the PowerPoint when it becomes available.

It was also interesting blogging on my laptop. On my tablet, I blog in HTML because it is a pain to u se the visual editor on the tablet. A laptop has no such problem. But a laptop battery doesn’t last all day so…