Wander Join: Online Aggregation via Random Walks

slide1. Wander Join: Online Aggregation via Random Walks Feifei Li Bin Wu, Ke Yi Zhuoyue Zhao
University of Utah Hong Kong University Shanghai Jiao Tong
of Science and Technology University Slides from http://datagroup.cs.utah.edu/seminars.php
Adapted for Duke DB Group by Brett Walenz
Added slides 3, 7-10, 12, 19-21<br>
slide2. Database Workloads Wander Join: Online Aggregation via Random Walks 2<br>
slide3. Online Aggregation Goal: Analytical queries do not always need 100% accuracy. Can we return an approximate answer with improving ‘quality’ guarantee?

Concretely, how do we estimate an aggregate query that involves multiple joins?

Notion of quality: express in form of confidence intervals: that is, we’d like to be able to say that with high probability, the actual query answer is somewhere in a given interval (preferably small). Wander Join: Online Aggregation via Random Walks 3<br>
slide4. Online Aggregation [Haas, Hellerstein, Wang SIGMOD’97] Wander Join: Online Aggregation via Random Walks 4 Confidence Interval Confidence Level<br>
slide5. Complex Analytical Queries (TPC-H) SELECT SUM(l_extendedprice * (1 - l_discount))
FROM customer, lineitem, orders, nation, region
WHERE c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_returnflag = 'R'
AND c_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'

This query finds the total revenue loss due to returned orders in a given region. Wander Join: Online Aggregation via Random Walks 5<br>
slide6. Ripple Join [Haas, Hellerstein, SIGMOD’99] Store tuples in each table in random order
In each step
Reads the next tuple from a table in a round-robin fashion
Join with sampled tuples from other tables
Estimate the aggregation value from samples, calculate confidence interval from estimator
Works well for full Cartesian product
But most joins are sparse … Wander Join: Online Aggregation via Random Walks 6<br>
slide7. Ripple Join [Haas, Hellerstein, SIGMOD’99] Estimator for Wander Join: Online Aggregation via Random Walks 7 Is this estimator unbiased? Yes, since tuples pulled at random from EACH table.
Is this estimator consistent? Yes, the final result is the actual query result.<br>
slide8. Ripple Join [Haas, Hellerstein, SIGMOD’99] How do we use this estimator to develop a confidence interval? Use the central limit theorem.
1.

2. Shift to a standard normal:

3. Find the area under this curve

4. Let

5. Then Wander Join: Online Aggregation via Random Walks 8<br>
slide9. Ripple Join [Haas, Hellerstein, SIGMOD’99] How do we find

We need another estimator:

Ripple join is NOT independent: for every sample r, there are multiple samples s that may join. Thus the variance estimator needs to take into account the proportion of EACH table it has seen so far. Wander Join: Online Aggregation via Random Walks 9<br>
slide10. Ripple Join [Haas, Hellerstein, SIGMOD’99] Wander Join: Online Aggregation via Random Walks 10 Now can use procedure for calculating confidence interval earlier.<br>
slide11. A Running Example Wander Join: Online Aggregation via Random Walks 11 What’s the total revenue of all orders from customers in China?<br>
slide12. Wander Join Take a randomly sampled tuple from ONLY one table
Conduct a random walk from that tuple to the neighbors (join tuples)
For queries with many join relations, there may be different walk paths
Can handle cyclical queries
Assumes indexes on other tables
Provide an unbiased estimator for each aggregator, calculate confidence intervals
Does not provide consistent result: must run full join in conjunction with wander join
Estimate and confidence interval converges faster than ripple join in experiments Wander Join: Online Aggregation via Random Walks 12<br>
slide13. Join as a Graph Wander Join: Online Aggregation via Random Walks 13 Conceptual only
Never materialized<br>
slide14. Join as a Graph Wander Join: Online Aggregation via Random Walks 14 Conceptual only
Never materialized<br>
slide15. Join as a Graph Wander Join: Online Aggregation via Random Walks 15 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID<br>
slide16. Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 16 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID<br>
slide17. Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 17 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID<br>
slide18. Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 18 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID<br>
slide19. Sampling by Random Walks Estimator of aggregate might be biased Wander Join: Online Aggregation via Random Walks 19 Idea: Penalize paths that are sampled with higher
probability proportionally.<br>
slide20. Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 20<br>
slide21. Confidence Interval More complicated than it looks! This is just the normal variance formula. Estimator is more straightforward than ripple join. Wander Join: Online Aggregation via Random Walks 21<br>
slide22. Sampling by Random Walks Wander Join: Online Aggregation via Random Walks 22 SELECT SUM(Price)
FROM Customers C,
Orders O,
Items I
WHERE
C.Nation = ‘China’
C.CID = O.BuyerID
O.OrderID =
I.OrderID<br>
slide23. Walk Plan Optimization Structure of the data graph
Selection predicates
Starting table: use index
Table in the middle: reject random walk
Data distribution
Non-uniformitymay not be a badthing! Wander Join: Online Aggregation via Random Walks 23<br>
slide24. Walk Plan Optimizer Enumerate all plans
Conduct ~ 100 trial random walks using each plan
Measure the variance of each plan
Select the best plan
All trials runs are still useful Wander Join: Online Aggregation via Random Walks 24<br>
slide25. Convergence Comparison Wander Join: Online Aggregation via Random Walks 25<br>
slide26. Wander Join in PostgreSQL Logarithmic growth due to B-tree lookup to find random neighbours Wander Join: Online Aggregation via Random Walks 26<br>
slide27. Running on Insufficient Memory (4GB) Wander Join: Online Aggregation via Random Walks 27 Insufficient memory incurs a heavy, one-time penalty
Growth is still logarithmic
Fundamentally: Random sampling at odds with hard disks
But does it matter? Spark, In-Memory DB, RAM cloud…
The algorithm is embarrassingly parallel Turbo DBO [Dobra, Jermaine, Rusu, Xu, VLDB’09]<br>
slide28. Wander Join vs Ripple Join Wander Join: Online Aggregation via Random Walks 28<br>
slide29. Online Aggregation vs Data Cube Wander Join: Online Aggregation via Random Walks 29<br>
slide30. Thank you!<br>
slide31. Dealing with Selection Predicates One predicate
Little impact: Can start walk from that table
Multiple highly selective predicates
More random walks will fail
Running full query becomes faster
Can simply switch to full query when selectivity <1% (say) Wander Join: Online Aggregation via Random Walks 31<br>
slide32. Index Ripple Join [Lipton, Naughton, Schneider, SIGMOD’90] Wander Join: Online Aggregation via Random Walks 32<br>
slide33. Sampling from a B-tree [Olken, ’93] Wander Join: Online Aggregation via Random Walks 33 4 2 3 Sampling from an aggregate (ranked) B-tree is easy
But
incurs heavy cost for transactions
need to modify existing B-tree implementations<br>
slide34. Rejection Sampling [Olken, ’93] Wander Join: Online Aggregation via Random Walks 34 Imagine each node has maximum fanout
Reject as soon as it walks out of bound<br>
slide35. Non-Uniform Sampling Wander Join: Online Aggregation via Random Walks 35 As long as we can compute the sampling probability, wander join still works!<br>
slide36. Compare with BlinkDB [Agarwal, Mozafari, Panda, Milner, Madden, Stoica, ’13] Wander Join: Online Aggregation via Random Walks 36<br>
slide37. Accuracy Achieved in 1/10 Time of Full Join Wander Join: Online Aggregation via Random Walks 37<br>