kgorman

Python - comparing JSON data structures

A function compare_json_data(source_data_a,source_data_b), accepting structures populated with data loaded from json.load() and comparing for equality.

Example

$ ./compare.py 
Compare JSON result is: True

kgorman

{"userid": 100, "theamount": 14000, "ending_ts": 1618848883}
{"userid": 200, "theamount": 23300, "ending_ts": 1618848883}

kgorman

SELECT 
userid, 
SUM(amount) AS theamount,
tumble_end(timestamp, interval '1' hour) AS ending_ts
FROM mystream
GROUP BY userid, tumble(timestamp, interval '1' hour)

kgorman

{"userid": 100, "amount": 10000, "timestamp": 1618852483}
{"userid": 200, "amount": 23000, "timestamp": 1618852483}
{"userid": 100, "amount": 4000, "timestamp": 1618848883}
{"userid": 200, "amount": 300, "timestamp": 1618848883}

kgorman

// hello world
function HELLOWORLD() {
  return "Hello World";
}
HELLOWORLD();

kgorman

-- return aggregation of total payments 
-- over a 1 hour tumbling window
SELECT SUM(CAST(amount AS numeric)) AS payment_volume,
CAST(TUMBLE_END(eventTimestamp, interval '1' hour) AS varchar) AS ts
FROM payments
GROUP BY TUMBLE(eventTimestamp, interval '1' hour);

kgorman

-- eventTimestamp is the Kafka timestamp
-- as unix timestamp. Magically added to every schema.
SELECT max(eventTimestamp) FROM solar_inputs;

-- make it human readable
SELECT CAST(max(eventTimestamp) AS varchar) as TS FROM solar_inputs;

-- dete math with interval
SELECT * FROM payments
WHERE eventTimestamp > CURRENT_TIMESTAMP-interval '10' second;

kgorman

-- detect multiple auths in a short window and
-- send to lock account topic/microservice
SELECT card,
MAX(amount) as theamount,
TUMBLE_END(eventTimestamp, interval '5' minute) as ts
FROM payments
WHERE lat IS NOT NULL
AND lon IS NOT NULL
GROUP BY card, TUMBLE(eventTimestamp, interval '5' minute)
HAVING COUNT(*) > 4 -- >4==fraud

kgorman

-- new to my airspace
SELECT icao, pings FROM (
    SELECT icao, speed,
    COUNT(*) OVER (PARTITION BY icao ORDER BY eventTimestamp RANGE BETWEEN INTERVAL '60' SECOND PRECEDING AND CURRENT ROW) AS pings
    FROM airplanes
) WHERE pings < 5
AND icao like 'A%'

kgorman

-- fraud processing on a stream of auths from a point of sale
SELECT card,
MAX(amount) as max_amount,
TUMBLE_END(eventTimestamp, interval '5' minute) as ts,
lat,
lon
FROM auth
GROUP BY card, TUMBLE(eventTimestamp, interval '5' minute)
HAVING COUNT(*) > 4