multi join query evaluation on big data lecture 1
play

Multi-join Query Evaluation on Big Data Lecture 1 Dan Suciu March, - PowerPoint PPT Presentation

Apr 01, 2024 •639 likes •1.52k views

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. 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

  2. Background AGM Friedgut Optimal Algorithms Summary About Me Orignally from Romania Had fun with Math: 1976 IMO PhD from University of Pennsylvania: Parallel Query Languages Bell Labs and AT&T Labs: Semistructured Data, XML University of Washington: data privacy, probabilistic data, Big Data Today’s topic: Big Data! Dan Suciu Multi-Joins – Lecture 1 March, 2015 2 / 34

  3. Background AGM Friedgut Optimal Algorithms Summary Course Organization Four lectures (1.5h): slides available on the course Website Two sections (1h): mostly interactive A problem set to pass the course: seven problems (simple to challenging); email me your solutions by April 30, 2015. I hope you can attend all lectures and sections: you need them in order to solve the problems. Dan Suciu Multi-Joins – Lecture 1 March, 2015 3 / 34

  4. Background AGM Friedgut Optimal Algorithms Summary Multi-join Query Evaluation – Outline Part 1 Optimal Sequential Algorithms. Thursday 14:15-15:45 Part 2 Lower bounds for Parallel Algorithms. Friday 14:15-15:45 Part 3 Optimal Parallel Algorithms. Saturday 9-10:30 Part 3 Data Skew. Saturday 11-12 Dan Suciu Multi-Joins – Lecture 1 March, 2015 4 / 34

  5. Background AGM Friedgut Optimal Algorithms Summary Multi-join Query Evaluation – Outline Part 1 Optimal Sequential Algorithms. Thursday 14:15-15:45 Part 2 Lower bounds for Parallel Algorithms. Friday 14:15-15:45 Part 3 Optimal Parallel Algorithms. Saturday 9-10:30 Part 3 Data Skew. Saturday 11-12 Dan Suciu Multi-Joins – Lecture 1 March, 2015 4 / 34

  6. Background AGM Friedgut Optimal Algorithms Summary Multi-join Query Evaluation – Outline Part 1 Optimal Sequential Algorithms. Thursday 14:15-15:45 Part 2 Lower bounds for Parallel Algorithms. Friday 14:15-15:45 Part 3 Optimal Parallel Algorithms. Saturday 9-10:30 Part 3 Data Skew. Saturday 11-12 Dan Suciu Multi-Joins – Lecture 1 March, 2015 4 / 34

  7. Background AGM Friedgut Optimal Algorithms Summary Multi-join Query Evaluation – Outline Part 1 Optimal Sequential Algorithms. Thursday 14:15-15:45 Part 2 Lower bounds for Parallel Algorithms. Friday 14:15-15:45 Part 3 Optimal Parallel Algorithms. Saturday 9-10:30 Part 3 Data Skew. Saturday 11-12 Dan Suciu Multi-Joins – Lecture 1 March, 2015 4 / 34

  8. Background AGM Friedgut Optimal Algorithms Summary Bibliography E Friedgut, Hypergraphs, entropy, and inequalities, American Mathematical Monthly, 749-760, 2004. Albert Atserias, Martin Grohe, Dniel Marx: Size Bounds and Query Plans for Relational Joins. SIAM J. Comput. 42(4): 1737-1767 (2013) Hung Q. Ngo, Christopher R´ e, Atri Rudra: Skew strikes back: new developments in the theory of join algorithms. SIGMOD Record 42(4): 5-16 (2013) Paul Beame, Paraschos Koutris, Dan Suciu: Skew in parallel query processing. PODS 2014: 212-223 Paul Beame, Paraschos Koutris, Dan Suciu: Communication steps for parallel query processing. PODS 2013: 273-284 Dan Suciu Multi-Joins – Lecture 1 March, 2015 5 / 34

  9. Background AGM Friedgut Optimal Algorithms Summary Outline for Lecture 1 Background: Queries, Databases, Query Evaluation The AGM inequality Friedgut’s inequality Worst-case optimal query evaluation Summary Dan Suciu Multi-Joins – Lecture 1 March, 2015 6 / 34

  10. Background AGM Friedgut Optimal Algorithms Summary Relations and Databases Person Name Age City Hobby Alice 22 L´ odt´ z knitting Bob 33 Lyon karate Carol 44 L´ odt´ z kayaking David 33 Lima karate Eve 22 Lima knitting Schema Relation/table name Person ; Attribute/column names Name, Age, City, Hobby ; Key Name Instance Set of tuples/rows/records, e.g. (Alice, 22, L´ odt´ z, knitting) Size Number of tuples m = 5; note: relation is a set Database is a set of relations = a finite structure Dan Suciu Multi-Joins – Lecture 1 March, 2015 7 / 34

  11. Background AGM Friedgut Optimal Algorithms Summary Relations and Databases Person Name Age City Hobby Alice 22 L´ odt´ z knitting Bob 33 Lyon karate Carol 44 L´ odt´ z kayaking David 33 Lima karate Eve 22 Lima knitting Schema Relation/table name Person ; Attribute/column names Name, Age, City, Hobby ; Key Name Instance Set of tuples/rows/records, e.g. (Alice, 22, L´ odt´ z, knitting) Size Number of tuples m = 5; note: relation is a set Database is a set of relations = a finite structure Dan Suciu Multi-Joins – Lecture 1 March, 2015 7 / 34

  12. Background AGM Friedgut Optimal Algorithms Summary Relations and Databases Person Name Age City Hobby Alice 22 L´ odt´ z knitting Bob 33 Lyon karate Carol 44 L´ odt´ z kayaking David 33 Lima karate Eve 22 Lima knitting Schema Relation/table name Person ; Attribute/column names Name, Age, City, Hobby ; Key Name Instance Set of tuples/rows/records, e.g. (Alice, 22, L´ odt´ z, knitting) Size Number of tuples m = 5; note: relation is a set Database is a set of relations = a finite structure Dan Suciu Multi-Joins – Lecture 1 March, 2015 7 / 34

  13. Background AGM Friedgut Optimal Algorithms Summary Relations and Databases Person Name Age City Hobby Alice 22 L´ odt´ z knitting Bob 33 Lyon karate Carol 44 L´ odt´ z kayaking David 33 Lima karate Eve 22 Lima knitting Schema Relation/table name Person ; Attribute/column names Name, Age, City, Hobby ; Key Name Instance Set of tuples/rows/records, e.g. (Alice, 22, L´ odt´ z, knitting) Size Number of tuples m = 5; note: relation is a set Database is a set of relations = a finite structure Dan Suciu Multi-Joins – Lecture 1 March, 2015 7 / 34

  14. Background AGM Friedgut Optimal Algorithms Summary Relations and Databases Person Name Age City Hobby Alice 22 L´ odt´ z knitting Bob 33 Lyon karate Carol 44 L´ odt´ z kayaking David 33 Lima karate Eve 22 Lima knitting Schema Relation/table name Person ; Attribute/column names Name, Age, City, Hobby ; Key Name Instance Set of tuples/rows/records, e.g. (Alice, 22, L´ odt´ z, knitting) Size Number of tuples m = 5; note: relation is a set Database is a set of relations = a finite structure Dan Suciu Multi-Joins – Lecture 1 March, 2015 7 / 34

  15. Background AGM Friedgut Optimal Algorithms Summary Basic Stuff that’s Good To Know Relational database systems: Oracle, SQL Server, DB2, Postgres, SQLite, Dremel, Scope, Spark SQL Relations are flat (atomic values only): 1st normal form. Relations are persistent: stored in file systems, or in distributed file systems like Hadoop Physical data independence: system is allowed to organize the relation how it wishes. E.g. indexes, column-oriented DBs, partition on distributed servers, replicated. Dan Suciu Multi-Joins – Lecture 1 March, 2015 8 / 34

  16. Background AGM Friedgut Optimal Algorithms Summary Relational Algebra Cartesian product / Join: ⋈ Projection: Π A Selection: σ C Union: ∪ Set difference: − This course: select-project-join Dan Suciu Multi-Joins – Lecture 1 March, 2015 9 / 34

  17. Background AGM Friedgut Optimal Algorithms Summary Join R ⋈ X = Y S The set of pairs ( t 1 , t 2 ) , with t 1 ∈ R and t 2 ∈ S , s.t. t 1 . X = t 2 . Y T = R ⋈ X = Y S R X U S Y V X U Y V a 1 b 1 a 1 c 1 a 1 b 1 a 1 c 1 a 1 b 2 a 1 c 2 a 1 b 1 a 1 c 2 a 2 b 3 a 3 c 3 a 1 b 2 a 1 c 1 a 3 b 4 a 4 c 4 a 1 b 2 a 1 c 2 a 3 b 4 a 3 c 3 Input schemas: R ( X , U ) , S ( Y , V ) Output schema: T ( X , U , Y , V ) Dan Suciu Multi-Joins – Lecture 1 March, 2015 10 / 34

  18. Background AGM Friedgut Optimal Algorithms Summary Natural Join R ⋈ S Joins R , S on all common attributes, removes duplicate attributes T = R ⋈ S R A B S A C A B C a 1 b 1 a 1 c 1 a 1 b 1 c 1 a 1 b 2 a 1 c 2 a 1 b 1 c 2 a 2 b 3 a 3 c 3 a 1 b 2 c 1 a 3 b 4 a 4 c 4 a 1 b 2 c 2 a 3 b 4 c 3 Input schemas: R ( A , B ) , S ( A , C ) Output schema: T ( A , B , C ) Dan Suciu Multi-Joins – Lecture 1 March, 2015 11 / 34

  19. Background AGM Friedgut Optimal Algorithms Summary Natural Join Examples Question In each case below: what is the output schema? What does the join do? R ( A , B , E , G ) ⋈ S ( A , C , D , E , F ) Dan Suciu Multi-Joins – Lecture 1 March, 2015 12 / 34

  20. Background AGM Friedgut Optimal Algorithms Summary Natural Join Examples Question In each case below: what is the output schema? What does the join do? R ( A , B , E , G ) ⋈ S ( A , C , D , E , F ) Returns Output ( A , B , C , D , E , F , G ) = R ⋈ ( R . A = S . A )∧( R . E = S . E ) S Dan Suciu Multi-Joins – Lecture 1 March, 2015 12 / 34

  21. Background AGM Friedgut Optimal Algorithms Summary Natural Join Examples Question In each case below: what is the output schema? What does the join do? R ( A , B , E , G ) ⋈ S ( A , C , D , E , F ) Returns Output ( A , B , C , D , E , F , G ) = R ⋈ ( R . A = S . A )∧( R . E = S . E ) S R ( A , B ) ⋈ S ( C , D , E ) Dan Suciu Multi-Joins – Lecture 1 March, 2015 12 / 34

Recommend

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
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
Coordination-free query evaluation and multi-query optimization in parallel and distributed

Coordination-free query evaluation and multi-query optimization in parallel and distributed

Formal approaches to: Coordination-free query evaluation and multi-query optimization in parallel and distributed systems Bas Ketsman Outline CALM Formalization CALM Revision 1 Coordination-free evaluation Conclusion Parallel-Correctness

1.25k views • 88 slides
Scalable Uncertainty Management 05 Query Evaluation in Probabilistic Databases Rainer Gemulla

Scalable Uncertainty Management 05 Query Evaluation in Probabilistic Databases Rainer Gemulla

Scalable Uncertainty Management 05 Query Evaluation in Probabilistic Databases Rainer Gemulla Jun 1, 2012 Overview In this lecture Primer: relational calculus Understand complexity of query evaluation How to determine whether a query is

1.54k views • 119 slides
Single-Round Multi-Join Evaluation Bas Ketsman Outline 1. Introduction 2. Parallel-Correctness

Single-Round Multi-Join Evaluation Bas Ketsman Outline 1. Introduction 2. Parallel-Correctness

Single-Round Multi-Join Evaluation Bas Ketsman Outline 1. Introduction 2. Parallel-Correctness 3. Transferability 4. Special Cases 2 Motivation Single-round Multi-joins Less rounds / barriers Formal framework for reasoning about

754 views • 32 slides
CS 327E Class 3 September 23, 2019 1) Which SQL join type does this query contain? S(a: int, b:

CS 327E Class 3 September 23, 2019 1) Which SQL join type does this query contain? S(a: int, b:

CS 327E Class 3 September 23, 2019 1) Which SQL join type does this query contain? S(a: int, b: string); A. Inner Join T(c: string, d: date); B. Natural Join C. Cross Join SELECT * D. None of the above FROM T, S; 2) The following query,

581 views • 28 slides
Multi- -dimensional Data and dimensional Data and Spatial Range Spatial Range Multi Query in

Multi- -dimensional Data and dimensional Data and Spatial Range Spatial Range Multi Query in

Multi- -dimensional Data and dimensional Data and Spatial Range Spatial Range Multi Query in Sensor Networks Query in Sensor Networks Jie Gao Computer Science Department Stony Brook University 1 Papers Papers [Li03a] X. Li, Y. J.

615 views • 40 slides
Optimizing Join Enumeration in Transformation-based Query

Optimizing Join Enumeration in Transformation-based Query

Optimizing Join Enumeration in Transformation-based Query Optimizers ANIL SHANBHAG, S. SUDARSHAN IIT BOMBAY anils@mit.edu, sudarsha@cse.iitb.ac.in VLDB 2014 Query Optimization: Quick

576 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 & Cloud Computing (P. Baumann) 1 Relational Operations: Join Definition Natural join: R S := L ( C ( R S ) ) Where C:

361 views • 12 slides
Two Adaptive Query Execution Systems Outline Motivation Tukwila Eddies Evaluation

Two Adaptive Query Execution Systems Outline Motivation Tukwila Eddies Evaluation

Two Adaptive Query Execution Systems Outline Motivation Tukwila Eddies Evaluation Data integration Extreme form of distributed database Sources of data all over the internet (unrelated) Query optimization is difficult

507 views • 33 slides
Overview of Query Evaluation [R&G] Chapter 12 CS4320 1 Overview of Query Evaluation

Overview of Query Evaluation [R&G] Chapter 12 CS4320 1 Overview of Query Evaluation

Overview of Query Evaluation [R&G] Chapter 12 CS4320 1 Overview of Query Evaluation Plan : Tree of R.A. ops, with choice of alg for each op. Each operator typically implemented using a `pull interface: when an operator is

1.06k views • 21 slides
Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2014/15 Lecture III:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2014/15 Lecture III:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2014/15 Lecture III: Multi-dimensional Indexing Querying Multi-dimensional Data This example query involves a range predicate in two dimensions . The general

771 views • 41 slides
Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture III:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture III:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture III: Multi-dimensional Indexing Querying Multi-dimensional Data This example query involves a range predicate in two dimensions . The general

820 views • 48 slides
Multi Multi-dimensional Data and Spatial Range dimensional Data and Spatial Range Query in

Multi Multi-dimensional Data and Spatial Range dimensional Data and Spatial Range Query in

Multi Multi-dimensional Data and Spatial Range dimensional Data and Spatial Range Query in Sensor Networks Query in Sensor Networks 1 Orthogonal range search Orthogonal range search Find all the sensors inside a rectangular box.

649 views • 38 slides
Cost-based Query Sub-System Select * Queries From Blah B Where B.blah = blah Query Parser

Cost-based Query Sub-System Select * Queries From Blah B Where B.blah = blah Query Parser

Faloutsos/Pavlo CMU - 15-415/615 CMU SCS Carnegie Mellon Univ. Dept. of Computer Science 15-415/615 - DB Applications C. Faloutsos A. Pavlo Lecture#13: Query Evaluation CMU SCS Today's Class Catalog (12.1) Intro to Operator

377 views • 18 slides
CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query

CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query

CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query Sub-System Select * Queries From Blah B Where B.blah = blah Query Parser Query Optimizer Plan Plan Cost Catalog Manager Generator Estimator

711 views • 44 slides
CryptDB Protecting Confidentiality with Encrypted Query Processing Katarzyna Baranowska

CryptDB Protecting Confidentiality with Encrypted Query Processing Katarzyna Baranowska

CryptDB Protecting Confidentiality with Encrypted Query Processing Katarzyna Baranowska University of Warsaw January 21, 2012 Threats Threat 1 Speed Evaluation Threat 2 More Evaluation Why secure data? Medical data Contact data Payment

1.27k views • 78 slides
CS 764: Topics in Database Management Systems Lecture 4: Query Optimization-1 Xiangyao Yu

CS 764: Topics in Database Management Systems Lecture 4: Query Optimization-1 Xiangyao Yu

CS 764: Topics in Database Management Systems Lecture 4: Query Optimization-1 Xiangyao Yu 9/16/2020 1 Discussion Highlights Consider a nested loop join between R and S. Initially R and S are both stored on disk. The buffer management policy

770 views • 48 slides
Challenges for Efficient Query Evaluation on Structured Probabilistic Data SUM2016 SEPTEMBER

Challenges for Efficient Query Evaluation on Structured Probabilistic Data SUM2016 SEPTEMBER

Challenges for Efficient Query Evaluation on Structured Probabilistic Data SUM2016 SEPTEMBER 23, 2016, NICE Antoine Amarilli, Silviu Maniu, Mikal Monet 2 A probabilistic database R S a d d e f c f e a e d a c e b e Q c

682 views • 55 slides
SQL for the IoT Move the query to the data, not the data to the query. Internals Why Do You

SQL for the IoT Move the query to the data, not the data to the query. Internals Why Do You

SQL for the IoT Move the query to the data, not the data to the query. Internals Why Do You Care? So you can better understand what your query is doing. So that you can appreciate how much work the database is saving you. So that

1.24k views • 99 slides
Trade-offs in Static and Dynamic Query Evaluation Ahmet Kara, Milos Nikolic Dan Olteanu, and

Trade-offs in Static and Dynamic Query Evaluation Ahmet Kara, Milos Nikolic Dan Olteanu, and

Trade-offs in Static and Dynamic Query Evaluation Ahmet Kara, Milos Nikolic Dan Olteanu, and Haozhe Zhang fdbresearch.github.io KOCOON Workshop 2019, Arras Static and Dynamic Query Evaluation Static Query Evaluation preprocessing

1.38k views • 51 slides
DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // @JOY_ARULRAJ LECTURE #17: QUERY

DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // @JOY_ARULRAJ LECTURE #17: QUERY

DATABASE SYSTEM IMPLEMENTATION GT 4420/6422 // SPRING 2019 // @JOY_ARULRAJ LECTURE #17: QUERY EXECUTION & SCHEDULING 2 TODAYS AGENDA Process Models Query Parallelization Data Placement Scheduling 3 QUERY EXECUTION A query plan is

1.08k views • 85 slides
Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture IX:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture IX:

Systems Infrastructure for Data Science Web Science Group Uni Freiburg WS 2012/13 Lecture IX: Distributed query processing and optimization Roadmap Overview (Query Decomposition) Data Localization Query Optimization Uni

894 views • 60 slides
CAS CS 460/660 Introduction to Database Systems Query Evaluation I Slides from UC Berkeley 1.1

CAS CS 460/660 Introduction to Database Systems Query Evaluation I Slides from UC Berkeley 1.1

CAS CS 460/660 Introduction to Database Systems Query Evaluation I Slides from UC Berkeley 1.1 Introduction We ve covered the basic underlying storage, buffering, and indexing SQL Query technology. Now we can move on to query

617 views • 32 slides

More recommend