Making Data Dance (Abstractions)

MAKING DATA DANCE
Abstractions @barrettclark

View Slide

HARNESS HERETICAL
ANTIPATTERNS
FOR FUN AND PROFIT

View Slide

BARRETT CLARK
@barrettclark

View Slide


View Slide

Class (Model)
Object Relational
Mapper
Database
M
agic

View Slide

DATABASE AGNOSTIC

View Slide

ActiveRecord
Object Relational Mapper

View Slide

ActiveRecord

View Slide

RAW SQL

View Slide

COMPLICATED JOINS

View Slide

Category.joins(articles: [{ comments: :guest }, :tags])
http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations
???

View Slide

SELECT categories.* FROM categories
INNER JOIN articles ON articles.category_id = categories.id
INNER JOIN comments ON comments.article_id = articles.id
INNER JOIN guests ON guests.comment_id = comments.id
INNER JOIN tags ON tags.article_id = articles.id
http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations
Category.joins(articles: [{ comments: :guest }, :tags])

View Slide

DEEPER DATABASE
FUNCTIONALITY

View Slide

BUT DATABASE
PORTABILITY?!

View Slide

https://ﬂic.kr/p/eg4hih

View Slide

It just doesn't work like that.
Database portability pipe dream

View Slide

Different databases are different.

View Slide

You chose your database for a reason.
Use it!

View Slide

CAVEATS
• Long-running queries
• Connection pool

View Slide

LET'S GET OUR HANDS DIRTY

View Slide


View Slide

WINDOW FUNCTIONS

View Slide

–Postgres Documentation
A window function performs a calculation across a
set of table rows that are somehow related to the
current row.

View Slide

–Barrett Clark
Fold data from other rows into this row

View Slide

WINDOW FUNCTION GREATEST HITS
• lead()
• lag()
• first_value()
• last_value()
• row_number()

View Slide

id fruit
1 apple
2 apple
3 apple
4 pear
5 apple
6 pear
7 pear
8 pear
9 pear
10 banana

View Slide

SELECT *,LEAD(id) OVER(), LAG(id) OVER()
FROM fruits;
id | fruit | lead | lag
----+--------+------+-----
1 | apple | 2 |
2 | apple | 3 | 1
3 | apple | 4 | 2
4 | pear | 5 | 3
5 | apple | 6 | 4
6 | pear | 7 | 5
7 | pear | 8 | 6
8 | pear | 9 | 7
9 | pear | 10 | 8
10 | banana | | 9
(10 rows)
http://sqlﬁddle.com/#!15/230c3b/2

View Slide

–Postgres Documentation
A window function performs a calculation across a
set of table rows that are somehow related to the
current row.

View Slide

https://ﬂic.kr/p/7KGqEY

View Slide

SELECT *,
FIRST_VALUE(id) OVER(PARTITION BY fruit ORDER BY id),
ROW_NUMBER() OVER(PARTITION BY fruit ORDER BY id)
FROM fruits;
id | fruit | first_value | row_number
----+--------+-------------+------------
1 | apple | 1 | 1
2 | apple | 1 | 2
3 | apple | 1 | 3
5 | apple | 1 | 4
10 | banana | 10 | 1
4 | pear | 4 | 1
6 | pear | 4 | 2
7 | pear | 4 | 3
8 | pear | 4 | 4
9 | pear | 4 | 5
(10 rows)
http://sqlﬁddle.com/#!15/230c3b

View Slide

PRACTICAL EXAMPLE
Chat App

View Slide

SELECT id, message, room_id, user_id
FROM messages
ORDER BY id;
id | message | room_id | user_id
----+-----------------------------+---------+---------
1 | Leaving for the airport now | 1 | 1337
2 | Flight just landed! | 1 | 1337
3 | waiting on an uber | 2 | 1337
4 | omw y'all! | 1 | 1337
9 | hello, world | 1 | 27
(5 rows)

View Slide

SELECT id, message, room_id, user_id,
LAG(user_id)
OVER(
PARTITION BY room_id
ORDER BY room_id, id
) AS prev_user_id
FROM messages
ORDER BY 3, 1;
Look back
Deﬁnes the groups of rows to evaluate
against the current row

View Slide

id | message | room_id | user_id | prev_user_id
----+-----------------------------+---------+---------+--------------
1 | Leaving for the airport now | 1 | 1337 |
2 | Flight just landed! | 1 | 1337 | 1337
4 | omw y'all! | 1 | 1337 | 1337
9 | hello, world | 1 | 27 | 1337
3 | waiting on an uber | 2 | 1337 |
(5 rows)
LAG(user_id)
OVER(
) AS prev_user_id
FROM messages
ORDER BY 3, 1;

View Slide

LAG(user_id)
OVER(
) AS prev_user_id
FROM messages
ORDER BY 3, 1;
ProTip™
3
1 2 4 5

View Slide

class Message < ActiveRecord::Base
def self.window_example
sql = <<-SQL
LAG(user_id)
OVER(PARTITION BY room_id ORDER BY room_id, id)
AS prev_user_id
FROM messages
ORDER BY 3, 1;
SQL
connection.execute(sql)
end
end

View Slide

TOO ANTI-PATTERN FOR YOU?

View Slide

module PGConnection
def conn
config = YAML.load_file(File.open('config/database.yml'))['development']
@conn ||= PG.connect(
:dbname => config['database'],
:user => config['username'],
:password => config['password'],
:host => config['host'] || 'localhost'
)
end
end
class Antipattern
extend PGConnection
def self.window_example
sql = <<-SQL
LAG(user_id) OVER(PARTITION BY room_id ORDER BY room_id, id) AS prev_user_id
FROM messages
ORDER BY 3, 1;
SQL
conn.exec(sql).values
end
end

View Slide

MODELS DO NOT HAVE TO BE
BACKED BY ACTIVERECORD
Or even persisted in the database.

View Slide

QUESTIONS WE CAN NOW ANSWER
• When did something change?
• When did someone leave a place?
• How long did each thing last?
• How long did people stay in which places?
• Enter / Exit / Change events

View Slide

https://ﬂic.kr/p/5b8cq3

View Slide

SUBQUERIES

View Slide

SUBQUERY
• To ﬁlter and group
• For a ﬁeld value
• In a join

View Slide

BASE QUERY

View Slide

SELECT id, phone, major, minor, reading_timestamp,
LEAD(minor) OVER (
PARTITION BY phone
ORDER BY phone, reading_timestamp
) AS next_minor
FROM beacon_readings
ORDER BY 2, 5;
“For a given phone, pull in the next minor ﬁeld”

View Slide

WHEN DID SOMETHING CHANGE?

View Slide

SELECT major, minor, next_minor, COUNT(*)
FROM (
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY 2, 5
) AS beacon_readings_lead
WHERE major = 1
AND minor != next_minor
GROUP BY major, minor, next_minor
ORDER BY major, minor, next_minor;

View Slide

YO DAWG, I HEARD YOU LIKE QUERIES

View Slide

FROM (
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY 2, 5
WHERE major = 1

View Slide

major | minor | next_minor | count
-------+-------+------------+-------
1 | 0 | 1 | 18
1 | 0 | 2 | 3
1 | 0 | 3 | 23
1 | 0 | 4 | 7
1 | 1 | 0 | 19
1 | 1 | 2 | 59
1 | 1 | 3 | 158
1 | 1 | 4 | 44
1 | 2 | 0 | 5
1 | 2 | 1 | 59
1 | 2 | 3 | 85
1 | 2 | 4 | 40
1 | 3 | 0 | 21
1 | 3 | 1 | 154
1 | 3 | 2 | 85
1 | 3 | 4 | 52
1 | 4 | 0 | 6
1 | 4 | 1 | 49
1 | 4 | 2 | 42
1 | 4 | 3 | 45
(20 rows)

View Slide


View Slide

https://ﬂic.kr/p/9tT9Yh

View Slide

REFACTOR

View Slide

CTE
Common Table Expression

View Slide

FROM (
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY 2, 5
WHERE major = 1

View Slide

WITH beacon_readings_lead AS (
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY 2, 5
)
FROM beacon_readings_lead
WHERE major = 1

View Slide

WITH interval_query AS (
SELECT (ts ||' hour')::INTERVAL AS hour_interval
FROM generate_series(0,23) AS ts
), time_series AS (
SELECT DATE_TRUNC('hour', NOW()) +
INTERVAL '60 min' * ROUND(DATE_PART('minute', NOW()) / 60.0) -
interval_query.hour_interval AS start_time
FROM interval_query
), time_intervals AS (
SELECT start_time, start_time + '1 hour'::INTERVAL AS end_time
FROM time_series ORDER BY start_time
), reading_counts AS (
SELECT f.start_time, f.end_time, br.minor,
COUNT(DISTINCT br.phone) readings
FROM beacon_readings br
RIGHT JOIN time_intervals f
ON br.reading_timestamp >= f.start_time
AND br.reading_timestamp < f.end_time
AND br.major = 1
GROUP BY f.start_time, f.end_time, br.minor
ORDER BY f.start_time, br.minor
)
SELECT * FROM reading_counts;

View Slide


View Slide

Repetition Repetition Repetition Repetition Repetition Repetition Repetition
Repetition Repetition Repetition Repetition
Repetition Repetition Repetition

View Slide

VIEW
Stored Query

View Slide

CREATE OR REPLACE VIEW beacon_readings_lead_view
AS
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY 2, 5
;

View Slide

class CreateLeadView < ActiveRecord::Migration
def up
execute <<-SQL.strip_heredoc
CREATE OR REPLACE VIEW beacon_readings_lead_view
AS
SELECT
ID, phone, major, minor, reading_timestamp,
LEAD(minor) OVER (
PARTITION BY phone
) AS next_minor
ORDER BY phone, reading_timestamp;
SQL
end
def down
sql = "DROP VIEW IF EXISTS beacon_readings_lead_view;"
execute(sql)
end
end

View Slide

FROM beacon_readings_lead_view
WHERE major = 1

View Slide

BUSINESS LOGIC
IN THE DATABASE?!?


View Slide

YOU
Get to decide where your code runs

View Slide

https://ﬂic.kr/p/2MW7SY

View Slide

MY QUERY IS TOO SLOW

View Slide

DO THE HEAVY LIFTING
IN A SEPARATE PROCESS

View Slide

MATERIALIZED VIEW
Stored query AND data

View Slide

CREATE MATERIALIZED VIEW beacon_readings_lead_lag_mv AS
SELECT *
FROM beacon_readings_lead_lag_view
WHERE reading_timestamp >= NOW() - '1 day'::INTERVAL;
CREATE INDEX index_beacon_readings_mv_on_major
ON beacon_readings_lead_lag_mv USING btree(
major ASC NULLS LAST
);
CREATE INDEX index_beacon_readings_mv_on_phone
phone COLLATE "default" ASC NULLS LAST
);
CREATE INDEX index_beacon_readings_mv_on_reading_timestamp
reading_timestamp ASC NULLS LAST
);

View Slide

DATA
https://ﬂic.kr/p/khS6jn

View Slide

REFRESH MATERIALIZED VIEW CONCURRENTLY
beacon_readings_lead_lag_mv;
Available starting in 9.4

View Slide

namespace :db do
namespace :heroku do
# https://devcenter.heroku.com/articles/scheduler
desc 'Update the materialized view(s)'
task :update_materialized_view => :environment do
sql = 'REFRESH MATERIALIZED VIEW beacon_readings_lead_lag_mv;'
ActiveRecord::Base.connection.execute(sql)
end
end
end

View Slide

https://ﬂic.kr/p/bVUL7

View Slide

SUBQUERY FOR A
FIELD VALUE

View Slide

SELECT
id,
phone,
( SELECT MIN(id)
FROM beacon_readings_lead_lag_mv
WHERE
major = br1.major
AND minor = 0
AND phone = br1.phone
AND id >= br1.id
) AS session_close_id,
minor,
next_beacon_minor,
reading_timestamp
FROM beacon_readings_lead_lag_mv br1
WHERE major = 2
AND minor != next_beacon_minor
ORDER BY phone, reading_timestamp;

View Slide


View Slide

Making Data Dance (Abstractions)

Making Data Dance (Abstractions)

Barrett Clark

More Decks by Barrett Clark

Other Decks in Programming

Featured

Transcript