dplyr and Databases
Harlan D. Harris, PhD
thanks to Jay Hyer and Leo Apolonio!
Introduction and Review
review of dplyr for in-memory data
Sequential Verbs:
df %>% filter(color != ‘blue’) %>%� group_by(year) %>%� summarise(cost=mean(cost)) %>%� left_join(profit, � by=c(‘color’, ‘year’)) %>%� arrange(desc(year)) -> df2�
Bad Practice, except�for presentations
Talk about: pipelining, order of operations, “DSL”, plyr,
what I’ve/we’ve used dplyr for
how databases work
Caveat: my PhD in Computer Science includes 0 database theory classes...
connecting to databases in R
con <- dbConnect(dbDriver("PostgreSQL"), host, port,� dbname, username, password)
df <- dbGetQuery(con, “SELECT … FROM … WHERE …”)
# or
ret <- dbSendQuery(con, “SELECT … FROM … WHERE …”)
df <- fetch(ret)
Avoid learning about: database drivers, cursors...
Why…?
ORMs in other languages
# Python peewee
usernames = ['charlie', 'huey', 'mickey']�users = User.select().where(User.username << usernames)�tweets = Tweet.select().where(Tweet.user << users)
# C# LINQ
var custQuery = � from cust in Customers� where cust.Orders.Any()� select cust;
And you thought I knew nothing about databases! I know less about this!
unified syntax benefits
con <- src_postgres(...)
tbl(con, ‘tblname’) %>% � filter(color != ‘blue’) %>%� group_by(year) %>%� summarise(cost=mean(cost)) %>%� left_join(tbl(con, ‘profit’), � by=c(‘color’, ‘year’)) %>%� arrange(desc(year)) %>%� collect() -> df�
Or:
profit <- tbl(con, ‘profit’)
left_join(profit, …)
Code Switching, Flow
lazy evaluation
sobig <- tbl(con, ‘hugetable’) %>% � left_join(tbl(con, “bigtable”))
reasonable <- sobig %>%� filter(year == 2004 & color == ‘blue’) %>%� collect()
tiny <- sobig %>%� filter(cust_id = 1234567) %>%� collect()
promise of data
actual data
��actual data
#1 Reason Why R’s Not Going Anywhere
Examples
windowing functions
dplyr does it correctly:
df %>%� group_by(year) %>%� mutate(cumul_revenue =� cumsum(revenue))
vs SQL:
SELECT sum(revenue) OVER� (PARTITION BY ...fml...)
windowing example
batting %>% � group_by(yearID, lgID) %>%� summarise(runs=sum(R)) %>%� group_by(lgID) %>%� arrange(yearID) %>%� mutate(cumul_runs=cumsum(runs))
Source: postgres 9.4.4 [harlan@localhost:5432/harlan]
From: <derived table> [?? x 4]
Grouped by: lgID
yearID lgID runs cumul_runs
(int) (chr) (dbl) (dbl)
1 1882 AA 2438 2438
2 1883 AA 4465 6903
3 1884 AA 6889 13792
4 1885 AA 4885 18677
5 1886 AA 6327 25004
6 1887 AA 7234 32238
7 1888 AA 5691 37929
8 1889 AA 6783 44712
9 1890 AA 6042 50754
10 1891 AA 6553 57307
remote-or-local tradeoffs
all_remote <- batting %>%
filter(teamID != 'BOS') %>%
group_by(yearID) %>%
mutate(cumul_runs=cumsum(R)) %>%
select(yearID, R, cumul_runs) %>%
collect()
half_local <- batting %>%
filter(teamID != 'BOS') %>%
select(yearID, R) %>%
collect() %>%
group_by(yearID) %>%
mutate(cumul_runs=cumsum(R))
Five times faster?!
parameterized queries
tot_runs <- function(dat, year_range=c(2002,2012), � ignore_redsox=FALSE) {
ret <- (if (ignore_redsox) filter(dat, teamID != 'BOS')
else dat) %>%
filter(yearID >= year_range[[1]] & yearID <= year_range[[2]]) %>%
summarize(tot_runs=sum(R)) %>% collect()
ret[[1]][[1]]
}�
> tot_runs(batting, ignore_redsox = TRUE)
[1] 236635
> tot_runs(batting)
[1] 246145
In raw SQL, this is a paste() nightmare...
Nitty-Gritty
inspecting queries
qry <- batting %>%
group_by(yearID) %>%
mutate(cumul_runs=cumsum(R)) %>%
select(yearID, R, cumul_runs)
print(qry$query)�
SELECT "yearid" AS "yearID","r" AS "R","cumul_runs" AS "cumul_runs"
FROM (SELECT "row.names","playerid","yearid","stint","teamid","lgid",� "g","ab","r","h" ,"x2b","x3b","hr","rbi","sb","cs","bb","so",� "ibb","hbp","sh","sf","gidp",� SUM("r") OVER ( PARTITION BY "yearid" ROWS unbounded preceding)
AS "cumul_runs" FROM "batting") AS "zzz16"
I pretty-formatted this...
understanding performance
qry <- batting %>%
filter(teamID != 'BOS') %>%
group_by(yearID) %>%
tally() %>%
rename(year=yearID, runs=n) %>%
inner_join(global, by='year') %>%
select(year, runs, celsius) %>%
arrange(year)
understanding performance
explain(qry)
Merge Join (cost=3288.24..3289.91 rows=65 width=20)
Merge Cond: (batting."yearID" = global.year)
-> Sort (cost=3284.63..3284.98 rows=138 width=12)
Sort Key: batting."yearID"
-> HashAggregate (cost=3276.97..3278.35 rows=138 width=4)
Group Key: batting."yearID"
-> Seq Scan on batting (cost=0.00..2799.07 rows=95579 width=4)
Filter: ("teamID" <> 'BOS'::text)
-> Sort (cost=3.61..3.77 rows=65 width=12)
Sort Key: global.year
-> Seq Scan on global (cost=0.00..1.65 rows=65 width=12)
collapse
> qry <- filter(select(batting, yearID, R), yearID > 2010)
> qry$query
<Query> SELECT "yearID" AS "yearID", "R" AS "R"
FROM "batting"
WHERE "yearID" > 2010.0
> collapse(qry)$query
<Query> SELECT "yearID", "R"
FROM (SELECT "yearID" AS "yearID", "R" AS "R"
FROM "batting"
WHERE "yearID" > 2010.0) AS "zzz27"
Forces a subquery -- can help SQL optimizer
SQL literals
filter(batting, sql("\"playerID\" LIKE 'finger%'")) %>% � arrange(desc(yearID))
row.names playerID yearID stint teamID lgID G AB R H X2B X3B
(chr) (chr) (int) (int) (chr) (chr) (int) (int) (int) (int) (int) (int)
1 62504 fingero01 1985 1 ML4 AL 47 NA NA NA NA NA
2 61509 fingero01 1984 1 ML4 AL 33 NA NA NA NA NA
3 59509 fingero01 1982 1 ML4 AL 50 0 0 0 0 0
4 58559 fingero01 1981 1 ML4 AL 47 0 0 0 0 0
5 57625 fingero01 1980 1 SDN NL 66 18 0 5 3 0
6 56658 fingero01 1979 1 SDN NL 54 12 1 1 0 0
7 55698 fingero01 1978 1 SDN NL 67 12 0 2 0 0
8 54725 fingero01 1977 1 SDN NL 78 20 0 1 0 0
9 53814 fingero01 1976 1 OAK AL 70 0 0 0 0 0
10 52913 fingero01 1975 1 OAK AL 76 1 0 0 0 0
Postgres and case sensitivity… sigh...
quirks: PostgreSQL and Views
dbGetQuery(con$con,
"CREATE VIEW v_rollie AS SELECT * FROM � batting WHERE \"playerID\" = 'fingero01'")
> tbl(con, "v_rollie")
Error: Table v_rollie not found in database
> tbl(con, sql("select * from v_rollie"))
row.names playerID yearID stint teamID lgID G AB R H X2B X3B
(chr) (chr) (int) (int) (chr) (chr) (int) (int) (int) (int) (int) (int)
1 46713 fingero01 1968 1 OAK AL 1 0 0 0 0 0
2 47488 fingero01 1969 1 OAK AL 60 25 2 5 0 0
3 48419 fingero01 1970 1 OAK AL 45 39 1 4 0 0
indexes
dbGetQuery(con$con, "CREATE INDEX i_team ON batting � (\"teamID\")")
> explain(batting %>% filter(teamID == 'BOS'))
Bitmap Heap Scan on batting (cost=81.36..1685.70 rows=4267 width=97)
Recheck Cond: ("teamID" = 'BOS'::text)
-> Bitmap Index Scan on i_team (cost=0.00..80.30 rows=4267 width=0)
Index Cond: ("teamID" = 'BOS'::text)
temp tables for efficiency
compute(filter(batting, playerID == 'fingero01'), � name="tmp_rollie")
> tbl(con, "tmp_rollie")
row.names playerID yearID stint teamID lgID G AB R H X2B X3B
(chr) (chr) (int) (int) (chr) (chr) (int) (int) (int) (int) (int) (int)
1 46713 fingero01 1968 1 OAK AL 1 0 0 0 0 0
2 47488 fingero01 1969 1 OAK AL 60 25 2 5 0 0�3 48419 fingero01 1970 1 OAK AL 45 39 1 4 0 0
4 49327 fingero01 1971 1 OAK AL 48 33 4 7 0 0
5 50210 fingero01 1972 1 OAK AL 65 19 2 6 0 0
6 51108 fingero01 1973 1 OAK AL 62 1 0 0 0 0
�
temp tables for contextual data
mydat <- data.frame(yearID=1973:1985, � x=rnorm(n=length(1973:1985)))
copy_to(con, mydat, name="tmp_mydat")
left_join(tbl(con, "tmp_rollie"), � tbl(con, "tmp_mydat"), � by="yearID") %>% select(yearID, R, x)
yearID R x
(int) (int) (dbl)
1 1968 0 NA
2 1969 2 NA
3 1970 1 NA
4 1971 4 NA
5 1972 2 NA
6 1973 0 0.1509868
7 1974 0 0.5574017
8 1975 0 -1.5495107
9 1976 0 -1.1996780�10 1977 0 0.3704155
.. ... ... ...
Contextual Data is Where It’s At!
dplyr source code
https://github.com/hadley/dplyr/tree/master/R
Read it -- you’re learn something!
hopes and dreams
thanks!
@harlanh
oh, and we’re hiring: http://bit.ly/EABDSAlgo
code for this prsentation: https://gist.github.com/HarlanH/448ef40d528cdc7c70d0