recommendation and graph algorithms in hadoop and sql

Recommendation and graph algorithms in Hadoop and SQL

DAVID F. GLEICH ASSISTANT PROFESSOR "COMPUTER SCIENCE "PURDUE UNIVERSITY

David Gleich · Purdue 1

Code github.com/dgleich/matrix-hadoop-tutorial

Ancestry.com

@dgleich dgleich@purdue.edu

Matrix computations

A =

2

66664

A1,1 A1,2 · · · A1,n

A2,1 A2,2 · · ·...

.... . .

. . . Am�1,nAm,1 · · · Am,n�1 Am,n

3

77775

Least squares Eigenvalues

Ax Ax = b min kAx � bk Ax = �x

Operations Linear "systems David Gleich · Purdue 2 Ancestry.com

Outcomes Recognize relationships between matrix methods and things you’ve already been doing" Example SQL queries as matrix computations See how to work with big graphs as large edge lists in Hadoop and SQL" Example Connected components Understand how to use Hadoop to compute these matrix methods at scale for BigData" Example Recommenders with social network info

David Gleich · Purdue 3 Ancestry.com

matrix computations "≠"

linear algebra

David Gleich · Purdue 4 Ancestry.com

World’s simplest recommendation system.

Suggest the average rating.

David Gleich · Purdue 5 Ancestry.com

A SQL statement as a "matrix computation

http://stackoverflow.com/questions/4217449/returning-average-rating-from-a-database-sql

How do I find the average rating for each product?

David Gleich · Purdue 6 Ancestry.com

A SQL statement as a "matrix computation

http://stackoverflow.com/questions/4217449/returning-average-rating-from-a-database-sql

SELECT ! p.product_id, ! p.name, ! AVG(pr.rating) AS rating_average!FROM products p !INNER JOIN product_ratings pr!ON pr.product_id = p.product_id!GROUP BY p.product_id!ORDER BY rating_average DESC !

How do I find the average rating for each product?

David Gleich · Purdue 7 Ancestry.com

This SQL statement is a "matrix computation!

8 Image from rockysprings, deviantart, CC share-alike Ancestry.com David Gleich · Purdue

SELECT ! ... ! AVG(pr.rating) !... !GROUP BY p.product_id!

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

Is a matrix!

pid1 pid2 pid3 pid4 pid5 pid6 pid7 pid8 pid9

David Gleich · Purdue 9 Ancestry.com

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

Is a matrix!

pid1 pid2 pid3 pid4 pid5 pid6 pid7 pid8 pid9

But it’s a weird matrix"

Missing entries!

David Gleich · Purdue 10

Ancestry.com

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4

Is a matrix!

pid1 pid2 pid3 pid4 pid5 pid6 pid7 pid8 pid9

4

4

4

4 5 4

But it’s a weird matrix"

Matrix

SELECT AVG(r) ... GROUP BY pid

Vector

Average"of ratings

David Gleich · Purdue 11

Ancestry.com

But it’s a weird matrix"and not a linear operator

A =

2

66664

A1,1 A1,2 · · · A1,n

A2,1 A2,2 · · ·...

.... . .

. . . Am�1,nAm,1 · · · Am,n�1 Am,n

3

77775

avg(A) =

2

6664

Pj A1,j/

Pj “A1,j 6= 0”P

j A2,j/P

j “A2,j 6= 0”...P

j Am,j/P

j “Am,j 6= 0”

3

7775

David Gleich · Purdue 12

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

Is a matrix!

Ancestry.com

matrix computations "≠"

linear algebra

David Gleich · Purdue 13

Ancestry.com

Hadoop, MapReduce, and Matrix Methods

David Gleich · Purdue 14

Ancestry.com

MapReduce

David Gleich · Purdue 15

dataMap

dataMap

dataMap

dataMap

keyvalue

keyvalue

keyvalue

keyvalue

keyvalue

keyvalue

()

Shuffle

keyvaluevalue

dataReduce

keyvaluevaluevalue

dataReduce

keyvalue dataReduce

Ancestry.com

The MapReduce Framework Originated at Google for indexing web pages and computing PageRank.

Express algorithms in "“data-local operations”. Implement one type of communication: shuffle. Shuffle moves all data with the same key to the same reducer.

MM R

RMM

Input stored in triplicate

Map output"persisted to disk"before shuffle

Reduce input/"output on disk

1 MM R

RMMM

Maps Reduce

Shuffle

2

3

4

5

1 2 M M

3 4 M M

5 M

Data scalable

Fault-tolerance by design

16

David Gleich · Purdue Ancestry.com

wordcount "is a matrix computation too

map(document) :

for word in document

emit (word, 1)

reduce(word, counts) :

emit (word, sum(counts))

1 2 D D

3 4 D D

5 D

matrix,1 matrix,1 matrix,1 matrix,1

bigdata,1 bigdata,1 bigdata,1 bigdata,1 bigdata,1 bigdata,1 bigdata,1 bigdata,1

hadoop,1 hadoop,1 hadoop,1 hadoop,1 hadoop,1 hadoop,1 hadoop,1

David Gleich · Purdue 17

Ancestry.com

wordcount "is a matrix computation too

A =

2

66664

A1,1 A1,2 · · · A1,n

A2,1 A2,2 · · ·...

.... . .

. . . Am�1,nAm,1 · · · Am,n�1 Am,n

3

77775

doc1

doc2

docm

= A

colsum(A) = AT e word count = e is the vector of all ones

David Gleich · Purdue 18

Ancestry.com

inverted index"is a matrix computation too

A =

2

66664

A1,1 A1,2 · · · A1,n

A2,1 A2,2 · · ·...

.... . .

. . . Am�1,nAm,1 · · · Am,n�1 Am,n

3

77775

doc1

doc2

docm

= A

David Gleich · Purdue 19

Ancestry.com

2

66664

A1,1 A2,1 · · · Am,1

A1,2 A2,2 · · ·...

.... . .

. . . Am,n�1A1,n · · · Am�1,n Am,n

3

77775= AT

term1

term2

termm

inverted index"is a matrix computation too

David Gleich · Purdue 20

Ancestry.com

A recommender system "with social info

David Gleich · Purdue 21

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

friends_links

uid6 uid1 uid8 uid9 uid7 uid7 uid7 uid4 uid6 uid2 uid7 uid1 uid3 uid1 uid1 uid8 uid7 uid3 uid9 uid1

Ancestry.com

A recommender system "with social info

David Gleich · Purdue 22

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

friends_links

uid6 uid1 uid8 uid9 uid7 uid7 uid7 uid4 uid6 uid2 uid7 uid1 uid3 uid1 uid1 uid8 uid7 uid3 uid9 uid1

pid1

pid2

2

64A1,1 A2,1 · · ·A1,2 A2,2 · · ·...

. . .. . .

3

75uid1

uid2

2

64A1,1 A2,1 · · ·A1,2 A2,2 · · ·...

. . .. . .

3

75

Ancestry.com

A recommender system "with social info

David Gleich · Purdue 23

product_ratings

pid8 uid2 4 pid9 uid9 1 pid2 uid9 5 pid9 uid5 5 pid6 uid8 4 pid1 uid2 4 pid3 uid4 4 pid5 uid9 2 pid9 uid8 4 pid9 uid9 1

friends_links

uid6 uid1 uid8 uid9 uid7 uid7 uid7 uid4 uid6 uid2 uid7 uid1 uid3 uid1 uid1 uid8 uid7 uid3 uid9 uid1

R S

Ancestry.com

A recommender system "with social info

David Gleich · Purdue 24

Recommend each item based on the average rating of all trusted users

“X = S RT” with something that is"almost a matrix-matrix"product

R pid1

pid2

2

64A1,1 A2,1 · · ·A1,2 A2,2 · · ·...

. . .. . .

3

75 S uid1

uid2

2

64A1,1 A2,1 · · ·A1,2 A2,2 · · ·...

. . .. . .

3

75

Xuid,pid =

X

uid2

Suid,uid2Ruid2,pid

!· X

uid2

“Suid,uid2 and Ruid2,pid 6= 0”

!�1

Ancestry.com

Tools I like

hadoop streaming dumbo mrjob hadoopy C++

David Gleich · Purdue 25

Ancestry.com

Tools I don’t use but other people seem to like …

pig java hbase mahout Eclipse Cassandra

David Gleich · Purdue 26

Mahout is the closest thing to a library for matrix computations in Hadoop. If you like Java, you should probably start there. I’m a low-level guy

Ancestry.com

hadoop streaming

the map function is a program"(key,value) pairs are sent via stdin"output (key,value) pairs goes to stdout the reduce function is a program"(key,value) pairs are sent via stdin"keys are grouped"output (key,value) pairs goes to stdout

David Gleich · Purdue 27

Ancestry.com

mrjob from

a wrapper around hadoop streaming for map and reduce functions in python

class MRWordFreqCount(MRJob): def mapper(self, _, line): for word in line.split(): yield (word.lower(), 1) def reducer(self, word, counts): yield (word, sum(counts)) if __name__ == '__main__': MRWordFreqCount.run()

David Gleich · Purdue 28

Ancestry.com

Connected components in SQL and Hadoop

Ancestry.com David Gleich · Purdue 29

Connected components

Ancestry.com David Gleich · Purdue 30

3 “components” in this graph How can we find them algorithmically … … on a huge network?

Connected components

Ancestry.com David Gleich · Purdue 31

Algorithm!Assign each node a random component id. For each node, take the minimum component id of itself and all neighbors.

DEMO

Ancestry.com David Gleich · Purdue 32

Computing Connected Components in SQL

Graph!Edges : id | head | tail !

!“Vector”!v : id | comp ! initialized to random ! component!

Ancestry.com David Gleich · Purdue 33

!CREATE TABLE v2 AS ( !SELECT ! e.tail AS id, ! MIN(v.comp) as COMP !FROM edges e !INNER JOIN vector v !ON e.head = v.id!GROUP BY e.tail!); !!DROP TABLE v; !ALTER TABLE v2 ! RENAME TO v; !!... Repeat ... !!

Matrix-vector product and connected components in Hadoop

David Gleich · Purdue 34

Ax = y

y

i

=X

k

A

ik

x

k

A x

See example! ���matrix-hadoop/codes/smatvec.py!

Ancestry.com

Google’s PageRank Word count, average rating!

“AT

x = y”y

i

= min(xi

, mink

A

ki

x

k

)

Connected components

Matrix-vector product

David Gleich · Purdue 35

Ax = y

y

i

=X

k

A

ik

x

k

A x

A is stored by “node”

$ head samples/smat_5_5.txt !0 0 0.125 3 1.024 4 0.121 !1 0 0.597 !2 2 1.247 !3 4 -1.45 !4 2 0.061 !

v initially random !

$ head samples/vec_5.txt !0 0.241 !1 -0.98 !2 0.237 !3 -0.32 !4 0.080 !

Follow along! ���matrix-hadoop/codes/smatvec.py!

Ancestry.com

Matrix-vector product"(in pictures)

David Gleich · Purdue 36

Ax = y

y

i

=X

k

A

ik

x

k

A x

A x

Input Map 1!Align on columns"

Reduce 1!Output Aik xk"keyed on row i

A

x Reduce 2!Output sum(Aik xk)"

y

Ancestry.com

Matrix-vector product"(in pictures)

David Gleich · Purdue 37

Ax = y

y

i

=X

k

A

ik

x

k

A x

A x

Input Map 1!Align on columns"

def joinmap(self, key, line): ! vals = line.split() ! if len(vals) == 2: ! # the vector ! yield (vals[0], # row ! (float(vals[1]),)) # xi ! else: ! # the matrix ! row = vals[0] ! for i in xrange(1,len(vals),2): ! yield (vals[i], # column ! (row, # i,Aij! float(vals[i+1]))) !

Ancestry.com

“Matrix-vector” for connected components

David Gleich · Purdue 38

A x

A x

Input Map 1!Align on columns"

def joinmap(self, key, line): ! vals = line.split() ! if len(vals) == 2: ! # the vector ! yield (vals[0], # row ! (float(vals[1]),)) # vi ! else: ! # the matrix ! row = vals[0] ! for i in xrange(1,len(vals),2): ! yield (row, # head ! (vals[i], # tail)) !

Ancestry.com

“AT

x = y”y

i

= min(xi

, mink

A

ki

x

k

)

Matrix-vector product"(in pictures)

David Gleich · Purdue 39

Ax = y

y

i

=X

k

A

ik

x

k

A x

A x

Input Map 1!Align on columns"

Reduce 1!Output Aik xk"keyed on row i

A

x def joinred(self, key, vals): ! vecval = 0. ! matvals = [] ! for val in vals: ! if len(val) == 1: ! vecval += val[0] ! else: ! matvals.append(val) ! for val in matvals: ! yield (val[0], val[1]*vecval) !

Note that you should use a secondary sort to avoid reading both in memory

Ancestry.com

“Matrix-vector” for connected components

David Gleich · Purdue 40

A x

A x

Input Map 1!Align on columns"

Reduce 1!Output Aik xk"keyed on row i

A

x def joinred(self, key, vals): ! vecval = 0. ! matvals = [] ! for val in vals: ! if len(val) == 1: ! vecval += val[0] ! else: ! matvals.append(val) ! for val in matvals: ! yield (val[0], vecval) !

Note that you should use a secondary sort to avoid reading both in memory

Ancestry.com

“AT

x = y”y

i

= min(xi

, mink

A

ki

x

k

)

Matrix-vector product"(in pictures)

David Gleich · Purdue 41

Ax = y

y

i

=X

k

A

ik

x

k

A x

A x

Input Map 1!Align on columns"

Reduce 1!Output Aik xk"keyed on row i

A

x Reduce 2!Output sum(Aik xk)"

y

def sumred(self, key, vals): ! yield (key, sum(vals)) !

Ancestry.com

Our social recommender

David Gleich · Purdue 42

RT S

Follow along! ���matrix-hadoop/recsys/recsys.py!

R is stored entry-wise !

$ gunzip –c data/rating.txt.gz!139431556 591156 5 !139431556 1312460676 5 !139431556 204358 4 139431556 368725 5 !Object ID! User ID! Rating!

S is stored entry-wise !

$ gunzip –c data/rating.txt.gz!3287060356 232085 -1 !3288305540 709420 1 !3290337156 204418 -1 !3294138244 269243 -1 !Other ID! Trust!My ID!

Ancestry.com

Matrix-matrix product

David Gleich · Purdue 43

A B

Follow along! ���matrix-hadoop/codes/matmat.py!

AB = CCij =

X

k

Aik Bkj

Ancestry.com

Conceptually, the first step is the same as the matrix-vector product with a block of vectors.

Matrix-matrix product "(in pictures)

David Gleich · Purdue 44

A B

AB = CCij =

X

k

Aik Bkj

A Map 1!Align on columns"

B Reduce 1!Output Aik Bkj"keyed on (i,j)

A

B Reduce 2!Output sum(Aik Bkj)"

C

Ancestry.com

Social recommender "(in code)

David Gleich · Purdue 45

A B

A Map 1!Align on columns"

B

def joinmap(self, key, line): ! parts = line.split('\t') ! if len(parts) == 8: # ratings ! objid = parts[0].strip() ! uid = parts[1].strip() ! rat = int(parts[2]) ! yield (uid, (objid, rat)) ! else len(parts) == 4: # trust ! myid = parts[0].strip() ! otherid = parts[1].strip() ! value = int(parts[2]) ! if value > 0: ! yield (otherid, (myid,)) !

Ancestry.com

Matrix-matrix product "(in pictures)

David Gleich · Purdue 46

A B

A Map 1!Align on columns"

B Reduce 1!Output Aik Bkj"keyed on (i,j)

A

B

def joinred(self, key, vals): ! tusers = [] # uids that trust key ! ratobjs = [] # objs rated by uid=key ! for val in vals: ! if len(val) == 1: ! tusers.append(val[0]) ! else: ! ratobjs.append(val) !! for (objid, rat) in ratobjs: ! for uid in tusers: ! yield ((uid, objid), rat) !

Conceptually, the second step

is the same as the matrix-

matrix product too, we “map”

the ratings from each trusted

user back to the source.

Ancestry.com

Matrix-matrix product "(in pictures)

David Gleich · Purdue 47

A B

AB = CCij =

X

k

Aik Bkj

A Map 1!Align on columns"

B Reduce 1!Output Aik Bkj"keyed on (i,j)

A

B Reduce 2!Output sum(Aik Bkj)"

C def avgred(self, key, vals): ! s = 0. ! n = 0 ! for val in vals: ! s += val! n += 1 ! # the smoothed average of ratings ! yield key, ! (s+self.options.avg)/float(n+1) ! !

Ancestry.com

Better ways to store "matrices in Hadoop

David Gleich · Purdue 48

A B

A B

Block matrices minimize the number of intermediate keys and values used. I’d form them based on the first reduce No need for “integer” keys that

fall between 1 and n!

Ancestry.com

49

A

From tinyimages"collection

Ancestry.com

Tall-and-Skinny matrices (m ≫ n) Many rows (like a billion) A few columns (under 10,000)

regression and general linear models"with many samples

block iterative methods panel factorizations

simulation data analysis !

big-data SVD/PCA!

Used in

David Gleich · Purdue

Questions?

50

Image from rockysprings, deviantart, CC share-alike Ancestry.com David Gleich · Purdue