semi joins and bloom join
play

Semi-Joins and Bloom Join Databases: The Complete Book Ch 20 1 - PowerPoint PPT Presentation

Apr 17, 2023 •91 likes •707 views

Semi-Joins and Bloom Join Databases: The Complete Book Ch 20 1 Practical Concerns UNION R 1 S 1 R 1 S 2 R 2 S 1 R N S M R 1 R 2 R N S 1 S 2 S M 2 Practical Concerns UNION R 1 S 1 R 1 S 2 R 2 S 1 R N

  1. Semi-Joins and Bloom Join Databases: The Complete Book Ch 20 1

  2. Practical Concerns UNION R 1 ⋈ S 1 R 1 ⋈ S 2 R 2 ⋈ S 1 R N ⋈ S M R 1 R 2 R N S 1 S 2 S M … … 2

  3. Practical Concerns UNION R 1 ⋈ S 1 R 1 ⋈ S 2 R 2 ⋈ S 1 R N ⋈ S M R 1 R 2 R N S 1 S 2 S M … … Where does the computation happen? How does the data get there? 2

  4. Distributing the Work Let’s start simple… what can we do with no partitions? ⋈ B R S 3

  5. Distributing the Work Let’s start simple… what can we do with no partitions? ⋈ B R S R and S may be any RA expression… 3

  6. Distributing the Work ⋈ B R S Node 1 4

  7. Distributing the Work ⋈ B R S Node 1 No Parallelism! 4

  8. Distributing the Work ⋈ B Node 3 R S Node 1 Node 2 5

  9. Distributing the Work All of R and ⋈ B All of S Node 3 get sent! R S Node 1 Node 2 Lots of Data Transfer! 5

  10. Distributing the Work ⋈ B R S Node 1 Node 2 6

  11. Distributing the Work All of R get sent ⋈ B R S Node 1 Node 2 Better! We can guess whether R or S is smaller. 6

  12. Distributing the Work What can we do if R is partitioned? U ⋈ B ⋈ B R 1 S R 2 7

  13. Distributing the Work There are lots of partitioning strategies, but this one is interesting…. U ⋈ B ⋈ B R 1 S R 2 Node 1 Node 2 Node 3 8

  14. Distributing the Work … it can be used as a model for partitioning S… U ⋈ B ⋈ B R 1 S 1 R 2 Node 1 Node 2 Node 3 9

  15. Distributing the Work … it can be used as a model for partitioning S… U ⋈ B ⋈ B R 1 S 2 R 2 Node 1 Node 2 Node 3 10

  16. Distributing the Work …and neatly captures the data transfer issue. U ⋈ B ⋈ B R 1 S R 2 Node 1 Node 2 Node 3 11

  17. Distributing the Work So let’s use it: S i joins with R 1 ,R 2 ,…,R N locally. 12

  18. Distributing the Work So let’s use it: S i joins with R 1 ,R 2 ,…,R N locally. Goal : Minimize amount of data sent from R k to S i 12

  19. Distributing the Work So let’s use it: S i joins with R 1 ,R 2 ,…,R N locally. Goal : Minimize amount of data sent from R k to S i Solution 1 : Use the partitioning strategy (like last lecture) 12

  20. Distributing the Work So let’s use it: S i joins with R 1 ,R 2 ,…,R N locally. Goal : Minimize amount of data sent from R k to S i Solution 1 : Use the partitioning strategy (like last lecture) Solution 2 : “Hints” to figure out what R k should send 12

  21. Sending Hints R k ⋈ B S i The naive approach… Node 1 Node 2 R k S i 13

  22. Sending Hints R k ⋈ B S i The naive approach… Node 1 Node 2 Send me R k R k S i 14

  23. Sending Hints R k ⋈ B S i The naive approach… R k Node 1 Node 2 R k S i 15

  24. Sending Hints R k ⋈ B S i The smarter approach… π B ( ) S i Node 1 Node 2 R k S i 16

  25. Sending Hints R k ⋈ B S i The smarter approach… π B ( ) S i Node 1 Node 2 ⋈ π B ( ) R k S i R k S i 17

  26. Sending Hints R k ⋈ B S i The smarter approach… Node 1 Node 2 <1,A> <2,X> <2,B> <3,Y> <2,C> <6,Y> <3,D> <4,E> 18

  27. Sending Hints R k ⋈ B S i The smarter approach… Node 1 Node 2 Send me rows <1,A> <2,X> with a ‘B’ of <2,B> <3,Y> 2,3, or 6 <2,C> <6,Y> <3,D> <4,E> 19

  28. Sending Hints R k ⋈ B S i The smarter approach… Node 1 Node 2 <2,B> Send me rows <1,A> <2,X> <2,C> with a ‘B’ of <2,B> <3,Y> 2,3, or 6 <3,D> <2,C> <6,Y> <3,D> <4,E> 20

  29. Sending Hints R k ⋈ B S i The smarter approach… Node 1 Node 2 <2,B> Send me rows <1,A> <2,X> <2,C> with a ‘B’ of <2,B> <3,Y> 2,3, or 6 <3,D> <2,C> <6,Y> <3,D> This is called a semi-join. <4,E> 20

  30. Sending Hints Now Node 1 sends as little data as possible… … but Node 2 needs to send a lot of data. Can we do better? 21

  31. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bits Node 1 Node 2 <1,A> <2,X> 1 0 <2,B> <6,Y> 0 0 <2,C> 0 <3,D> 1 <4,E> 0 22

  32. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bits Node 1 Node 2 Send me data <1,A> <2,X> 1 0 with a parity <2,B> <6,Y> 0 0 bit of ‘0’ <2,C> 0 <3,D> 1 <4,E> 0 23

  33. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bit Node 1 Node 2 <2,B> Send me data <1,A> <2,X> 1 0 <2,C> with a parity <2,B> <6,Y> 0 0 <4,E> bit of ‘0’ <2,C> 0 <3,D> 1 <4,E> 0 24

  34. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bit Node 1 sending too much is ok! (Node 2 still needs to compute ⋈ B ) Node 1 Node 2 <2,B> Send me data <1,A> <2,X> 1 0 <2,C> with a parity <2,B> <6,Y> 0 0 <4,E> bit of ‘0’ <2,C> 0 <3,D> 1 <4,E> 0 24

  35. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bit Node 1 sending too much is ok! (Node 2 still needs to compute ⋈ B ) Node 1 Node 2 <2,B> Send me data <1,A> <2,X> 1 0 <2,C> with a parity <2,B> <6,Y> 0 0 <4,E> bit of ‘0’ <2,C> 0 <3,D> 1 Problem: One parity bit is too little <4,E> 0 24

  36. Sending Hints R k ⋈ B S i Strategy 1 : Parity Bit Node 1 Node 2 <1,A> <2,X> 1 0 <2,B> <3,Y> 0 1 <2,C> <6,Y> 0 0 <3,D> 1 Problem: One parity bit is too little <4,E> 0 25

  37. Sending Hints R k ⋈ B S i Strategy 2 : Parity Bits Node 1 Node 2 <2,B> Send me data <1,A> <2,X> 01 10 <2,C> with parity <2,B> 11 <3,Y> 10 <3,D> bits 10 or 11 <2,C> <6,Y> 10 10 <3,D> 11 <4,E> 00 26

  38. Sending Hints R k ⋈ B S i Strategy 2 : Parity Bits Node 1 Node 2 <2,B> Send me data <1,A> <2,X> 01 10 <2,C> with parity <2,B> 11 <3,Y> 10 <3,D> bits 10 or 11 <2,C> <6,Y> 10 10 <3,D> 11 Problem: Almost as much data as π B <4,E> 00 26

  39. Sending Hints Can we summarize the parity bits? 27

  40. Bloom Filters Alice Bob Carol Dave 28

  41. Bloom Filters Alice Bob Bloom Filter Carol Dave 29

  42. Bloom Filters Is Alice part of the set? Alice Bob Bloom Filter Carol Dave 30

  43. Bloom Filters Is Alice part of the set? Yes Alice Bob Bloom Filter Carol Dave 30

  44. Bloom Filters Is Alice part of the set? Yes Alice Bob Is Eve part of Bloom Filter the set? Carol Dave 30

  45. Bloom Filters Is Alice part of the set? Yes Alice Bob Is Eve part of Bloom No Filter the set? Carol Dave 30

  46. Bloom Filters Is Alice part of the set? Yes Alice Bob Is Eve part of Bloom No Filter the set? Carol Dave Is Fred part of the set? 30

  47. Bloom Filters Is Alice part of the set? Yes Alice Bob Is Eve part of Bloom No Filter the set? Carol Dave Yes Is Fred part of the set? 30

  48. Bloom Filters Is Alice part of the set? Yes Alice Bob Is Eve part of Bloom No Filter the set? Carol Dave Yes Bloom Filter Guarantee Is Fred part Test definitely returns Yes if the element is in the set of the set? Test usually returns No if the element is not in the set 30

  49. Bloom Filters A Bloom Filter is a bit vector M - # of bits in the bit vector K - # of hash functions For ONE key (or record): For i between 0 and K: bitvector[ hash i (key) % M ] = 1 31

  50. Bloom Filters A Bloom Filter is a bit vector M - # of bits in the bit vector K - # of hash functions For ONE key (or record): For i between 0 and K: bitvector[ hash i (key) % M ] = 1 Each bit vector has ~K bits set 31

  51. Bloom Filters Filters are combined Key 1 00101010 by Bitwise-OR e.g. (Key 1 | Key 2) Key 2 01010110 = 01111110 Key 3 10000110 Key 4 01001100 32

  52. Bloom Filters Filters are combined Key 1 00101010 by Bitwise-OR e.g. (Key 1 | Key 2) Key 2 01010110 = 01111110 Key 3 10000110 How do we test for inclusion? Key 4 01001100 32

  53. Bloom Filters Filters are combined Key 1 00101010 by Bitwise-OR e.g. (Key 1 | Key 2) Key 2 01010110 = 01111110 Key 3 10000110 How do we test for inclusion? (Key & Filter) == Key? Key 4 01001100 (Key 1 & S) = 00101010 (Key 3 & S) = 00000110 (Key 4 & S) = 01001100 32

  54. Bloom Filters Filters are combined Key 1 00101010 by Bitwise-OR e.g. (Key 1 | Key 2) Key 2 01010110 = 01111110 Key 3 10000110 How do we test for inclusion? (Key & Filter) == Key? √ Key 4 01001100 (Key 1 & S) = 00101010 (Key 3 & S) = 00000110 (Key 4 & S) = 01001100 32

  55. Bloom Filters Filters are combined Key 1 00101010 by Bitwise-OR e.g. (Key 1 | Key 2) Key 2 01010110 = 01111110 Key 3 10000110 How do we test for inclusion? (Key & Filter) == Key? √ Key 4 01001100 (Key 1 & S) = 00101010 X (Key 3 & S) = 00000110 (Key 4 & S) = 01001100 32

Recommend

JOINS IN SQL By Rohit Dhanwani OBJECTIVES Define and use different types of joins INNER

JOINS IN SQL By Rohit Dhanwani OBJECTIVES Define and use different types of joins INNER

JOINS IN SQL By Rohit Dhanwani OBJECTIVES Define and use different types of joins INNER JOIN EQUI JOIN NATURAL JOIN OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN 2 PROCESSING MULTIPLE TABLES - JOIN

416 views • 18 slides
Types of joins PAN DAS JOIN S F OR S P READS H EET US ERS John Miller Principal Data Scientist

Types of joins PAN DAS JOIN S F OR S P READS H EET US ERS John Miller Principal Data Scientist

Types of joins PAN DAS JOIN S F OR S P READS H EET US ERS John Miller Principal Data Scientist Three types of joins Types One-to-one One-to-many Many-to-many Join type indicates relationship of tables Like lookup values in VLOOKUP

570 views • 18 slides
Evaluation of Join Operations Ramakrishnan/Gehrke Chapter 14, Part A (Joins) 340151 Big Data

Evaluation of Join Operations Ramakrishnan/Gehrke Chapter 14, Part A (Joins) 340151 Big Data

Evaluation of Join Operations Ramakrishnan/Gehrke Chapter 14, Part A (Joins) 340151 Big Data &amp; Cloud Computing (P. Baumann) 1 Relational Operations: Join Definition Natural join: R S := L ( C ( R S ) ) Where C:

361 views • 12 slides
CS 61: Database Systems Joins Adapted from Silberschatz, Korth, and Sundarshan unless otherwise

CS 61: Database Systems Joins Adapted from Silberschatz, Korth, and Sundarshan unless otherwise

CS 61: Database Systems Joins Adapted from Silberschatz, Korth, and Sundarshan unless otherwise noted Agenda 1. Joins 2. nyc_inspections schema 3. Joins on nyc_inspections 4. Conditional evaluation 2 With JOIN store data one time in

604 views • 27 slides
When to Optimize Enumerating all possible plans Selection Pushdown Join Conversion Join

When to Optimize Enumerating all possible plans Selection Pushdown Join Conversion Join

When to Optimize Enumerating all possible plans Selection Pushdown Join Conversion Join Reordering Pick a Join Algo Which Plan is the Best? Always push down selections Always convert joins Which join order??? Which join algo? What makes a

363 views • 3 slides
Concurrent Programming with Join Patterns via STMs Satnam Singh, Microsoft WG2.8 Estonia

Concurrent Programming with Join Patterns via STMs Satnam Singh, Microsoft WG2.8 Estonia

Concurrent Programming with Join Patterns via STMs Satnam Singh, Microsoft WG2.8 Estonia Overview Nothing clever. Bait and switch operation. Join patterns in Comega Joins encoded with STMs: Synchronous and asynchronous joins.

604 views • 18 slides
Locality-Adaptive Parallel Hash Joins using Hardware Transactional Memory ANIL SHANBHAG , HOLGER

Locality-Adaptive Parallel Hash Joins using Hardware Transactional Memory ANIL SHANBHAG , HOLGER

Locality-Adaptive Parallel Hash Joins using Hardware Transactional Memory ANIL SHANBHAG , HOLGER PIRK, SAM MADDEN MIT CSAIL History of Parallel Hash Joins Shared Hash Table Radix Partitioning based Join based Join Pictures from

353 views • 18 slides
OVERVIEW OF JOIN TYPES Overview This will begin to build our understanding of the various

OVERVIEW OF JOIN TYPES Overview This will begin to build our understanding of the various

OVERVIEW OF JOIN TYPES Overview This will begin to build our understanding of the various JOIN types. This presentation is a resource for you in this lecture! JOINS Overview As we learn about more types of JOINS it will become very

783 views • 18 slides
SQL Workshop Joins Doug Shook Inner Joins Joins are used to combine data from multiple

SQL Workshop Joins Doug Shook Inner Joins Joins are used to combine data from multiple

SQL Workshop Joins Doug Shook Inner Joins Joins are used to combine data from multiple tables into one result Requires the name of the column from the second table, along with a condition that defines the relationship 2 Inner

262 views • 24 slides
Mul,-way Joins J(a,b,c) :- R(a,b) S(b,c) T(a,c)

Mul,-way Joins J(a,b,c) :- R(a,b) S(b,c) T(a,c)

Worst Case Op,mal Joins CompSci 590.04 Instructor: Ashwin Machanavajjhala Lecture 19 : 590.04 Fall 15 1 Mul,-way Joins

1.02k views • 22 slides
CS411 Database Systems Join Expressions 06: SQL Kazuhiro Minami Join Expressions Products and

CS411 Database Systems Join Expressions 06: SQL Kazuhiro Minami Join Expressions Products and

CS411 Database Systems Join Expressions 06: SQL Kazuhiro Minami Join Expressions Products and Natural Joins Natural join is obtained by: SQL provides a number of expression forms that act like varieties of join in relational R

212 views • 6 slides
Joins, and more plotting Joins, and more plotting Abhijit Dasgupta Abhijit Dasgupta Fall, 2019

Joins, and more plotting Joins, and more plotting Abhijit Dasgupta Abhijit Dasgupta Fall, 2019

Joins, and more plotting Joins, and more plotting Abhijit Dasgupta Abhijit Dasgupta Fall, 2019 Fall, 2019 1 BIOF339, Fall, 2019 Goals today Learn how to join data sets (merging) See how to transform data sets to help our plotting Some

690 views • 54 slides
SQL$Joins Max$Masnick August&amp;7,&amp;2015 What%are%joins?

SQL$Joins Max$Masnick August&amp;7,&amp;2015 What%are%joins?

SQL$Joins Max$Masnick August&amp;7,&amp;2015 What%are%joins? Combine(two((or(more)(tables(into(a(single(results(table I&quot;will&quot;go&quot;through&quot;the&quot;most&quot;common&quot;joins&quot;here. Setup

597 views • 21 slides
LinDP++: Generalizing Linearized DP to Crossproducts and Non-Inner Joins Bernhard Radke, Thomas

LinDP++: Generalizing Linearized DP to Crossproducts and Non-Inner Joins Bernhard Radke, Thomas

LinDP++: Generalizing Linearized DP to Crossproducts and Non-Inner Joins Bernhard Radke, Thomas Neumann Technische Universitt Mnchen Join Ordering D D F E C B A Query Graph C SELECT ... F E B A AND C.d=D.d AND E.e=F.e WHERE

1.48k views • 29 slides
From relation algebra to semi-join algebra: an approach for graph query optimization Jelle

From relation algebra to semi-join algebra: an approach for graph query optimization Jelle

From relation algebra to semi-join algebra: an approach for graph query optimization Jelle Hellings 1 Catherine L. Pilachowski 2 Dirk Van Gucht 2 Marc Gyssens 1 Yuqing Wu 3 1 Hasselt University 2 Indiana University 3 Pomona College 1/19 Graph

378 views • 34 slides
Multi-join Query Evaluation on Big Data Section 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Multi-join Query Evaluation on Big Data Section 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Multi-join Query Evaluation on Big Data Section 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins on Big Data March, 2015 1 / 9 Prove that the AGM Bound is Tight Q ( x , y , z ) = R ( x , y ) , S ( y , z ) , T ( z , x ) AGM ( Q ) = min u m u R R

402 views • 15 slides
Outline Bloom filters Applications of Bloom filters Our replacement for Bloom filters

Outline Bloom filters Applications of Bloom filters Our replacement for Bloom filters

An Optimal Bloom Filter Replacement a Rasmus Pagh, IT University of Copenhagen Joint work with Anna Pagh, IT University of Copenhagen S. Srinivasa Rao, University of Waterloo a To appear in SODA 2005 1 Outline Bloom filters

405 views • 18 slides
ApproxJoin Approximate Distributed Joins Do Le Quoc, Istemi Ekin Akkus, Pramod Bhatotia, Spyros

ApproxJoin Approximate Distributed Joins Do Le Quoc, Istemi Ekin Akkus, Pramod Bhatotia, Spyros

ApproxJoin Approximate Distributed Joins Do Le Quoc, Istemi Ekin Akkus, Pramod Bhatotia, Spyros Blanas, Ruichuan Chen, Christof Fetzer, Thorsten Strufe 10/2018 Motivation X Join is a critical operation in big data analytics systems, but it

486 views • 21 slides
Multi-join Query Evaluation on Big Data Lecture 3 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Multi-join Query Evaluation on Big Data Lecture 3 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Algorithm Lower Bound Equivalence Summary Multi-join Query Evaluation on Big Data Lecture 3 Dan Suciu March, 2015 Dan Suciu Multi-Joins Lecture 3 March, 2015 1 / 26 Algorithm Lower Bound Equivalence Summary Multi-join Query

599 views • 49 slides
Mayfield in Bloom 2019 Categories: Large Village Parish in Bloom Judging day 4th

Mayfield in Bloom 2019 Categories: Large Village Parish in Bloom Judging day 4th

Mayfield in Bloom 2019 Categories: Large Village Parish in Bloom Judging day 4th July 2019 Representatives from: Mayfield Britain in Bloom steering group Mayfield &amp; Five Ashes Parish in Bloom Stg Grp Parish Council

440 views • 18 slides
room to bloom EUROPEAN ALTERNATIVES- 2020 EUROPEAN ALTERNATIVES- 2020 Summary ROOM TO BLOOM

room to bloom EUROPEAN ALTERNATIVES- 2020 EUROPEAN ALTERNATIVES- 2020 Summary ROOM TO BLOOM

room to bloom EUROPEAN ALTERNATIVES- 2020 EUROPEAN ALTERNATIVES- 2020 Summary ROOM TO BLOOM Room to Bloom supports 100 young feminist artists. It is a training and peer learning programme. It funds 15 artistic productions, which will be staged

241 views • 22 slides
www.norwichinbloom.co.uk BLOOM PARTNERSHIPS and FRIENDSHIPS DURING MY CAREER PRESENTATION BY

www.norwichinbloom.co.uk BLOOM PARTNERSHIPS and FRIENDSHIPS DURING MY CAREER PRESENTATION BY

www.norwichinbloom.co.uk BLOOM PARTNERSHIPS and FRIENDSHIPS DURING MY CAREER PRESENTATION BY TERRY BANE 1986 NORWICH IN BLOOM ASSOCIATION FORMED NOTCUTTS SPONSOR LOCAL COMPETITION 1989 FRIENDS OF NORWICH IN BLOOM PRESENTATION DAY AT

483 views • 46 slides
Revisiting Bloom Filters Payload attribution via Hierarchiecal Bloom Filters Kulesh

Revisiting Bloom Filters Payload attribution via Hierarchiecal Bloom Filters Kulesh

Revisiting Bloom Filters Payload attribution via Hierarchiecal Bloom Filters Kulesh Shanmugasundaram, Herve Bronnimann, Nasir Memon 600.624 - Advanced Network Security version 3 Overview Questions Collaborative Intrusion Detection

1.29k views • 58 slides
Multi-join Query Evaluation on Big Data Lecture 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Multi-join Query Evaluation on Big Data Lecture 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins

Background AGM Friedgut Optimal Algorithms Summary Multi-join Query Evaluation on Big Data Lecture 1 Dan Suciu March, 2015 Dan Suciu Multi-Joins Lecture 1 March, 2015 1 / 34 Background AGM Friedgut Optimal Algorithms Summary

1.52k views • 87 slides

More recommend