Two Leading Approaches to Data Virtualization, and Which Scales Better? by Daniel Abadi

Should you move code to data or data to code? Conventional wisdom favors the former, but cloud trends push the latter. This session by the creator of PACELC explores the shift, its risks, and the ongoing debate in data virtualization between push- and pull-based processing.

1. A ScyllaDB Community
Two Leading Approaches to
Data Federation & Virtualization:
Which Scales Better?
Daniel Abadi
Professor

2. Daniel Abadi
■Performs research on database system architecture and implementation,
especially at the intersection with scalable and distributed systems.
■Best-known for the development of the storage and query execution engines
of the C-Store (column-oriented database) prototype, which was
commercialized by Vertica and eventually acquired by Hewlett-Packard in
2011, for his HadoopDB research on fault tolerant scalable analytical
database systems which was commercialized by Hadapt and acquired by
Teradata in 2014
■Is ACM Fellow (2020)
■Recipient of a VLDB Best Paper Award, two VLDB Test of Time Awards (for
the work on C-Store and HadoopDB), the 2008 SIGMOD Jim Gray Doctoral
Dissertation Award, the 2013-2014 Yale Provost's Teaching Prize, and the
2013 VLDB Early Career Researcher Award, and the CIDR 2025 Test of Time
Award.

3. WHAT IS DATA FEDERATION?

4. Example
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Teradata
Data Warehouse

5. Example
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Teradata
Data Warehouse
ScyllaDB

6. Example
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Teradata
Data Warehouse
ScyllaDB

7. Example
7
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
ScyllaDB
Teradata
Data Warehouse

8. Query federation engines
8
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
ScyllaDB

9. Query federation engines
9
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
ScyllaDB

10. Query federation engines
10
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB

11. Query federation engines
11
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB

12. Query federation engines
12
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB

13. What are the Technical
Challenges Behind Data
Federation?

14. Challenges
14
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB
CQL
SQL
Spark
SQL

15. Challenges
15
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB
What is the total
value of customers
in North Dakota?

16. Challenges
16
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
AWS
Data Lake
Teradata
Data Warehouse
QUERY
SUBQUERY
SUBQUERY
SUBQUERY
ScyllaDB
What happens if
under-resourced?

17. Whither Data Federation?

18. RIP Data Federation
18
Data
Federation

19. RIP Data Federation
19
Data Virtualization

20. RIP Data Federation
20
Data
Federation
and
Virtualization

28. What happened?
28
Transaction Data
(customerID, Data/Time, ProductID, Amount, …)
AWS
Data Lake
Teradata
Data Warehouse
Customer Loyalty data
(customerID, customerName, twitterID, …)
Social Network Comments
(twitterID, influenceRank, productID, …)
Pull-based federation engine
ScyllaDB

29. Which scales better?
29
•Push-based architectures push code to data
•Pull-based architectures push data to code
•Push-based architectures scale better in theory
•But scale worse in practice
•Scalability problems with pull-based architectures rarely
seen in practice
•Only when network is a bottleneck
•JDBC interfaces can limit performance

30. What else happened?
30
•Orders of magnitude cheaper to store data in an open data
format on a data lake than in a commercial DB
•Vendors are being forced to support querying these data sources
directly
•First wave of solutions were half-hearted “external tables”-type of
approaches
•Now vendors are competing based on query federation /
virtualization performance
•This is a super easy federation use case for pull-based federation systems

31. Data federation and virtualization moving forward
31
•Simple federation use cases are not going away
•Next level of complexity is also fairly straight-forward for pull-based systems
•We’ve gone to far in the other extreme
•We can push more processing down to storage than we are doing now
•Supporting writes is still hard
•Data integration is still hard

32. Sort Offload Algorithm
•Observation: sort costs dominate merge-sort join overhead
•Idea: when Presto is overloaded, pushdown sort
•Given a join between a table S and table T (S ⨝ T)
•Partition table S into S1 and S2 ( t = S1 ⋃ S2)
•Partition table T into T1 and T2 ( t = T1 ⋃ T2)
•Sort S1 and T1 in DV Engine and Scan the sorted result of
S2 and T2 from the underlying DB
•Merge sorted S1 and sorted S2 to get sorted S and
sorted T1 and sorted T2 to get sorted T
•Sorted S = Merge(Sort S1, TS(sort S2))
•Sorted T = Merge(Sort T1, TS(sort T2))
•Merge join sorted s and sorted t
•S ⨝ T = Merge(Merge(sort S1, TS(sort S2), Merge(sort T1,
TS(sort T2))


TS(S1)TS(sort S2)
DV Engine
Merge (Sorted
S1, Sorted S2)
Sort S1
Merge join (Sorted S, Sorted T)
TS(T1)TS(sort T2)
Merge (Sorted
T1, Sorted T2)
Sort T1
Underlying DB

33. Outer Pushdown Algorithm
•Given a join between a table S and table T (S ⨝ T)
•Partition table T into T1 and T2 ( T = T1 ⋃ T2)
•Execute a left outer join between S and T2 (S ⟕ T2)
•This can be done on the underlying system
•Extract the join result from (S ⟕ T2)
•Extract S, join it with T1 (S ⨝ T1)
•This can be done in DV Engine
•Extract the inner part result, which is actually (S ⨝ T2)
•Put the results together, which is (S ⨝ T1)∪(S ⨝ T2)

⟕
TS(S) TS(T2)
extract
output
S
TS(T1)
⨝
inner part
Underlying DB
DV Engine

34. For more info …
•Email me ([email protected]) for (free) link to
pdf version of the book

35. Stay in Touch
Daniel Abadi
[email protected]
@daniel_abadi
https://www.linkedin.com/in/databaseprof