Learning Models over Relational Data: A Brief Tutorial

11/15/2019 ∙ by Maximilian Schleich, et al. ∙ 0

This tutorial overviews the state of the art in learning models over relational databases and makes the case for a first-principles approach that exploits recent developments in database research. The input to learning classification and regression models is a training dataset defined by feature extraction queries over relational databases. The mainstream approach to learning over relational data is to materialize the training dataset, export it out of the database, and then learn over it using a statistical package. This approach can be expensive as it requires the materialization of the training dataset. An alternative approach is to cast the machine learning problem as a database problem by transforming the data-intensive component of the learning task into a batch of aggregates over the feature extraction query and by computing this batch directly over the input database. The tutorial highlights a variety of techniques developed by the database theory and systems communities to improve the performance of the learning task. They rely on structural properties of the relational data and of the feature extraction query, including algebraic (semi-ring), combinatorial (hypertree width), statistical (sampling), or geometric (distance) structure. They also rely on factorized computation, code specialization, query compilation, and parallelization.



There are no comments yet.


page 1

page 2

page 3

page 4

This week in AI

Get the week's most popular data science and artificial intelligence research sent straight to your inbox every Saturday.

1 The Next Big Opportunity

Machine learning is emerging as general-purpose technology just as computing became general-purpose 70 years ago. A core ability of intelligence is the ability to predict, that is, to turn the information we have into the information we need. Over the last decade, significant progress has been made on improving the quality of prediction by techniques that identify relevant features and by decreasing the cost of prediction using more performant hardware.

According to a 2017 Kaggle survey on the state of data science and machine learning among 16,000 machine learning practitioners 

[26], the majority of practical data science tasks involve relational data: in retail, 86% of used data is relational; in insurance, it is 83%; in marketing, it is 82%; while in finance it is 77%. This is not surprising. The relational model is the jewel in the data management crown. It is one of the most successful Computer Science stories. Since its inception in 1969, it has seen a massive adoption in practice. Relational data benefit from the investment of many human hours for curation and normalization and are rich with knowledge of the underlying domain modelled using database constraints.

Yet the current state of affairs in building predictive models over relational data largely ignores the structure and rich semantics readily available in relational databases. Current machine learning technology throws away this relational structure and works on one large training dataset that is constructed separately using queries over relational databases.

This tutorial overviews on-going efforts by the database theory and systems community to address the challenge of efficiently learning machine learning models over relational databases. It invariably only highlights some of the representative contributions towards this challenge, with an emphasis on recent contributions by the authors. The tutorial does not cover the wealth of approaches that use arrays of GPUs or compute farms for efficient machine learning. It instead puts forward the insight that an array of known and novel database optimization and processing techniques can make feasible a wide range of analytics workloads already on one commodity machine. There is still much to explore in the case of one machine before turning to compute farms. A key practical benefit of this line of work is energy-efficient, inexpensive analytics over large databases.

The organization of the tutorial follows the structure of the next sections.

2 Overview of Main Approaches to Machine Learning over Relational Databases

The approaches highlighted in this tutorial are classified depending on how tightly they integrate the data system, where the input data reside and the training dataset is constructed, and the machine learning library (statistical software package), which casts the model training problem as an optimization problem.

2.1 No Integration of Databases and Machine Learning

By far the most common approach to learning over relational data is to use two distinct systems, that is, the data system for managing the training dataset and the ML library for model training. These two systems are thus distinct tools on the technology stack with no integration between the two. The data system first computes the training dataset as the result of a feature extraction query and exports it as one table commonly in CSV or binary format. The ML library then imports the training dataset in its own format and learns the desired model.

For the first step, it is common to use open source database management systems, such as PostgreSQL or SparkSQL 

[57], or query processing libraries, such as Python Pandas [33] and R dplyr [56]. Common examples for ML libraries include scikit-learn [44], R [46]

, TensorFlow 

[1], and MLlib [34].

One advantage is the delegation of concerns: Database systems are used to deal with data, whereas statistical packages are for learning models. Using this approach, one can learn virtually any model over any database.

The key disadvantage is the non-trivial time spent on materializing, exporting, and importing the training dataset, which is commonly orders of magnitude larger than the input database. Even though the ML libraries are much less scalable than the data systems, in this approach they are thus expected to work on much larger inputs. Furthermore, these solutions inherit the limitations of both of their underlying systems, e.g., the maximum data frame size in R and the maximum number of columns in PostgreSQL are much less than typical database sizes and respectively number of model features.

2.2 Loose Integration of Databases and Machine Learning

A second approach is based on a loose integration

of the two systems, with code of the statistical package migrated inside the database system space. In this approach, each machine learning task is implemented as a distinct user-defined aggregate function (UDAF) inside the database system. For instance, there are distinct UDAFs for learning: logistic regression models, linear regression models,

-means, Principal Component Analysis, and so on. Each of these UDAFs are registered in the underlying database system and there is a keyword in the query language supported by the database system to invoke them. The benefit is the direct interface between the two systems, with one single process running for both the construction of the training dataset and learning. The database system computes one table, which is the training dataset, and the learning task works directly on it. Prime example of this approach is MADLib 

[23] that extends PostgreSQL with a comprehensive library of machine learning UDAFs. The key advantage of this approach over the previous one is better runtime performance, since it does not need to export and import the (usually large) training dataset. Nevertheless, one has to explicitly write a UDAF for each new model and optimization method, essentially redoing the large implementation effort behind well-established statistical libraries. Approaches discussed in the next sections also suffer from this limitation, yet some contribute novel learning algorithms that can be asymptotically faster than existing off-the-shelf ones.

A variation of the second approach provides a unified programming architecture, one framework for many machine learning tasks instead of one distinct UDAF per task, with possible code reuse across UDAFs. Prime example of this approach is Bismark [16]

, a system that supports incremental (stochastic) gradient descent for convex programming. Its drawback is that its code may be less efficient than the specialized UDAFs. Code reuse across various models and optimization problems may however speed up the development of new functionalities such as new models and optimization algorithms.

2.3 Tight Integration of Databases and Machine Learning

The aforementioned approaches do not exploit the structure of the data residing in the database. The next and final approach features a tight integration of the data and learning systems. The UDAF for the machine learning task is pushed into the feature extraction query and one single evaluation plan is created to compute both of them. This approach enables database optimizations such as pushing parts of the UDAFs past the joins of the feature extraction query. Prime examples are Orion [29],which supports generalized linear models, Hamlet [30], which supports logistic regression and naïve Bayes, Morpheus [11]

, which linear and logistic regression, k-means clustering, and Gaussian non-negative matrix factorization, F 

[51, 40, 41], which supports ridge linear regression, AC/DC [3], which supports polynomial regression and factorization machines [47, 48, 49], and LMFAO [50]

, which supports a larger class of models including the previously mentioned ones and decision trees 

[10], Chow-Liu trees [12], mutual information, and data cubes [19, 22].

Feature ExtractionQuery


materialized output

ML Tool


Model Reformulation

Batch of Queries

Query BatchEvaluation

Figure 1: Structure-aware versus structure-agnostic learning over relational databases.

3 Structure-aware Learning

The tightly-integrated systems F [51], AC/DC [3], and LMFAO [50] are data structure-aware in that they exploit the structure and sparsity of the database to lower the complexity and drastically improve the runtime performance of the learning process. In contrast, we call all the other systems structure-agnostic, since they do not exploit properties of the input database. Figure 1 depicts the difference between structure-aware (in green) and structure-agnostic (in red) approaches. The structure-aware systems compile the model specification into a set of aggregates, one per feature or feature interaction. This is called model reformulation in the figure. Data dependencies such as functional dependencies can be used to reparameterize the model, so a model over a smaller set of functionally determining features is learned instead and then mapped back to the original model. Join dependencies, such as those prevalent in feature extraction queries that put together several input tables, are exploited to avoid redundancy in the representation of join results and push the model aggregates past joins. The model aggregates over the feature extraction query define a batch of queries. In practice, for training datasets with tens of features, query batch sizes can be in the order of: hundreds to thousands for ridge linear regression; thousands for computing a decision tree node; and tens for an assignment step in -means clustering [50]. The result of a query batch is then the input to an optimizer such as a gradient descent method that iterates until the model parameters converge.

Structure-aware methods have been developed (or are being developed) for a variety of models [4]. Besides those mentioned above, powerful models that can be supported are: Principal Component Analysis (PCA) [35]

, Support Vector Machines (SVM) 

[25], Sum Product Networks (SPN) [45]

, random forests, boosting regression trees, and AdaBoost. Newer methods also look at linear algebra programs where matrices admit a database interpretation such as the results of queries over relations. In particular, on-going work 

[17, 24] tackles various matrix decompositions, such as QR, Cholesky, SVD [18], and low-rank [54].

Structure-aware methods call for new data processing techniques to deal with large query batches. Recent work puts forward new optimization and evaluation strategies that go beyond the capabilities of existing database management systems. Recent experiments confirm this observation: Whereas existing query processing techniques are mature at executing one query, they miss opportunities for systematically sharing computation across several queries in a batch [50].

Tightly-integrated DB-ML systems commonly exploit four types of structure: algebraic, combinatorial, statistical, and geometric.

Algebraic Structure

The algebraic structure of semi-rings underlies the recent work on factorized databases [42, 41]. The distributivity law in particular allows to factor out data blocks common to several tuples, represent them once and compute over them once. Using factorization, relations can represented more succinctly as directed acyclic graphs. For instance, the natural join of two relations is a union of Cartesian products. Instead of representing such a Cartesian product of two relation parts explicitly as done by relational database systems, we can represent it symbolically as a tree whose root is the Cartesian product symbol and has as children the two relation parts. It has been shown that factorization can improve the performance of joins [42], aggregates [9, 6], and more recently machine learning [51, 41, 4, 2]. The additive inverse of rings allows to treat uniformly data updates (inserts and deletes) and enables incremental maintenance of models learned over relational data [28, 39, 27]. The sum-product abstraction in (semi) rings allows to use the same processing (computing and maintaining) mechanism for seemingly disparate tasks, such as database queries, covariance matrices, inference in probabilistic graphical models, and matrix chain multiplication [6, 39]. The efficient maintenance of covariance matrices is a prerequisite for the availability of fresh models under data changes [39]. A recent tutorial overviews advances in incremental view maintenance [15].

Combinatorial Structure

The combinatorial structure prevalent in relational data has been formalized by notions such as width and data degree measures. If a feature extraction query has width , then its data complexity is for a database of size , where hides logarithmic factors in . Various width measures have been proposed recently, such as: the fractional edge cover number [20, 8, 37, 38, 55] to capture the asymptotic size of the results for join queries and the time to compute them; the fractional hypertree width [32] and the submodular width [7] to capture the time to compute Boolean conjunctive queries; the factorization width [42] to capture the size of the factorized results of conjunctive queries; the FAQ-width [6] that extends the factorization width from conjunctive queries to functional aggregate queries; and the sharp-submodular width [2] that improves on the previous widths for functional aggregate queries.

The degree information captures the number of occurrences of a data value in the input database [38]. Existing processing techniques adapt depending on the high or low degree of data values. A recent such technique has been shown to be worst-case optimal for incrementally maintaining the count of triangles in a graph [27]. Another such technique achieves a low complexity for computing queries with negated relations of bounded degree [5]. A special form of bounded degree is given by functional dependencies, which can be used to reparameterize (polynomial regression and factorization machine) models and learn simpler, equivalent models instead [4].

Statistical Structure

The statistical structure allows to sample through joins, such as the ripple joins [21] and the wander joins [31], and to sample for specific classes of machine learning models [43]. Sampling is employed whenever the input database is too large to be processed within a given time budget. It may nevertheless lead to approximation of both steps in the end-to-end learning task, from the computation of the feature extraction query to the subsequent optimization task that yields the desired model. Work in this space quantifies the loss in accuracy of the obtained model due to sampling.

Geometric Structure

Algorithms for clustering methods such as -means [35]

can exploit distance measures (such as the optimal transport distance between two probability measures) to obtain constant-factor approximations for the

-means objective by clustering over a small grid coreset instead of the full result of the feature extraction query [14].

4 Database Systems Considerations

Besides exploiting the structure of the input data and the learning task, the problem of learning models over databases can also benefit tremendously from database system techniques. Recent work [50] showed non-trivial speedups (several orders of magnitude) brought by code optimization for machine learning workloads over state-of-the-art systems such as TensorFlow [1], R [46], Scikit-learn [44], and mlpack [13]. Prime examples of code optimizations leading to such performance improvements include:

Code Specialization and Query Compilation

It involves generating code specific to the query and the schema of its input data, following prior work [36, 52, 53], and also specific to the model to be learned. This technique improves the runtime performance by inlining code and improving cache locality for the hot data path.

Sharing Computation

Sharing is best achieved by decomposing the aggregates in a query batch into simple views that are pushed down the join tree of the feature extraction query. Different aggregates may then need the same simple views at some nodes in the join tree. Sharing of scans of the input relations can also happen across views, even when they have different output schemas.


Parallelization can exploit multi-core CPU architectures but also large share-nothing distributed systems. It comprises both task parallelism, which identifies subqueries that are independent and can be computed in parallel, and domain parallelism, which partitions relations and computes the same subqueries over different parts in parallel.

This tutorial is a call to arms for more sustained and principled work on the theory and systems of structure-aware approaches to data analytics. What are the theoretical limits of structure-aware learning? What are the classes of machine learning models that can benefit from structure-aware learning over relational data? What other types of structure can benefit learning over relational data?


  • [1] Abadi, M., Barham, P., Chen, J., Chen, Z., Davis, A., Dean, J., Devin, M., Ghemawat, S., Irving, G., Isard, M., Kudlur, M., Levenberg, J., Monga, R., Moore, S., Murray, D.G., Steiner, B., Tucker, P.A., Vasudevan, V., Warden, P., Wicke, M., Yu, Y., Zheng, X.: Tensorflow: A system for large-scale machine learning. In: OSDI. pp. 265–283 (2016)
  • [2] Abo Khamis, M., Curtin, R.R., Moseley, B., Ngo, H.Q., Nguyen, X., Olteanu, D., Schleich, M.: On functional aggregate queries with additive inequalities. In: PODS. pp. 414–431 (2019)
  • [3] Abo Khamis, M., Ngo, H.Q., Nguyen, X., Olteanu, D., Schleich, M.: AC/DC: In-database learning thunderstruck. In: DEEM. pp. 8:1–8:10 (2018)
  • [4]

    Abo Khamis, M., Ngo, H.Q., Nguyen, X., Olteanu, D., Schleich, M.: In-database learning with sparse tensors. In: PODS. pp. 325–340 (2018)

  • [5] Abo Khamis, M., Ngo, H.Q., Olteanu, D., Suciu, D.: Boolean tensor decomposition for conjunctive queries with negation. In: ICDT. pp. 21:1–21:19 (2019)
  • [6] Abo Khamis, M., Ngo, H.Q., Rudra, A.: FAQ: questions asked frequently. In: PODS. pp. 13–28 (2016)
  • [7] Abo Khamis, M., Ngo, H.Q., Suciu, D.: What do shannon-type inequalities, submodular width, and disjunctive datalog have to do with one another? In: PODS. pp. 429–444 (2017)
  • [8] Atserias, A., Grohe, M., Marx, D.: Size bounds and query plans for relational joins. In: FOCS. pp. 739–748 (2008)
  • [9] Bakibayev, N., Kociský, T., Olteanu, D., Závodný, J.: Aggregation and ordering in factorised databases. PVLDB 6(14), 1990–2001 (2013)
  • [10] Breiman, L., Friedman, J., Olshen, R., Stone, C.: Classification and Regression Trees. Wadsworth and Brooks, Monterey, CA (1984)
  • [11] Chen, L., Kumar, A., Naughton, J.F., Patel, J.M.: Towards linear algebra over normalized data. PVLDB 10(11), 1214–1225 (2017)
  • [12]

    Chow, C., Liu, C.: Approximating discrete probability distributions with dependence trees. IEEE Trans. Inf. Theor.

    14(3), 462–467 (2006)
  • [13] Curtin, R.R., Edel, M., Lozhnikov, M., Mentekidis, Y., Ghaisas, S., Zhang, S.: mlpack 3: A fast, flexible machine learning library. J. Open Source Soft. 3,  726 (2018)
  • [14] Curtin, R.R., Moseley, B., Ngo, H.Q., Nguyen, X., Olteanu, D., Schleich, M.: Rk-means: Fast coreset construction for clustering relational data (2019)
  • [15] Elghandour, I., Kara, A., Olteanu, D., Vansummeren, S.: Incremental techniques for large-scale dynamic query processing. In: CIKM. pp. 2297–2298 (2018), Tutorial
  • [16] Feng, X., Kumar, A., Recht, B., Ré, C.: Towards a unified architecture for in-rdbms analytics. In: SIGMOD. pp. 325–336 (2012)
  • [17]

    van Geffen, B.: QR decomposition of normalised relational data (2018), MSc thesis, University of Oxford

  • [18] Golub, G.H., Van Loan, C.F.: Matrix Computations. The Johns Hopkins University Press, fourth edn. (2013)
  • [19] Gray, J., Bosworth, A., Layman, A., Pirahesh, H.: Data cube: A relational aggregation operator generalizing group-by, cross-tab, and sub-total. In: ICDE. pp. 152–159 (1996)
  • [20] Grohe, M., Marx, D.: Constraint solving via fractional edge covers. In: SODA. pp. 289–298 (2006)
  • [21] Haas, P.J., Hellerstein, J.M.: Ripple joins for online aggregation. In: SIGMOD. pp. 287–298 (1999)
  • [22] Harinarayan, V., Rajaraman, A., Ullman, J.D.: Implementing data cubes efficiently. In: SIGMOD. pp. 205–216 (1996)
  • [23] Hellerstein, J.M., Ré, C., Schoppmann, F., Wang, D.Z., Fratkin, E., Gorajek, A., Ng, K.S., Welton, C., Feng, X., Li, K., Kumar, A.: The madlib analytics library or MAD skills, the SQL. PVLDB 5(12), 1700–1711 (2012)
  • [24] Inelus, G.R.: Quadratically Regularised Principal Component Analysis over multi-relational databases (2019), MSc thesis, University of Oxford
  • [25] Joachims, T.: Training linear svms in linear time. In: SIGKDD. pp. 217–226 (2006)
  • [26] Kaggle: The State of Data Science and Machine Learning (2017), https://www.kaggle.com/surveys/2017
  • [27] Kara, A., Ngo, H.Q., Nikolic, M., Olteanu, D., Zhang, H.: Counting triangles under updates in worst-case optimal time. In: ICDT. pp. 4:1–4:18 (2019)
  • [28] Koch, C., Ahmad, Y., Kennedy, O., Nikolic, M., Nötzli, A., Lupei, D., Shaikhha, A.: Dbtoaster: higher-order delta processing for dynamic, frequently fresh views. VLDB J. 23(2), 253–278 (2014)
  • [29] Kumar, A., Naughton, J.F., Patel, J.M.: Learning generalized linear models over normalized data. In: SIGMOD. pp. 1969–1984 (2015)
  • [30]

    Kumar, A., Naughton, J.F., Patel, J.M., Zhu, X.: To join or not to join?: Thinking twice about joins before feature selection. In: SIGMOD. pp. 19–34 (2016)

  • [31] Li, F., Wu, B., Yi, K., Zhao, Z.: Wander join and XDB: online aggregation via random walks. ACM Trans. Database Syst. 44(1), 2:1–2:41 (2019)
  • [32] Marx, D.: Approximating fractional hypertree width. ACM Trans. Algorithms 6(2), 29:1–29:17 (2010)
  • [33] McKinney, W.: pandas: a foundational python library for data analysis and statistics. Python for High Performance and Scientific Computing 14 (2011)
  • [34] Meng, X., Bradley, J., Yavuz, B., Sparks, E., Venkataraman, S., Liu, D., Freeman, J., Tsai, D., Amde, M., Owen, S., Xin, D., Xin, R., Franklin, M.J., Zadeh, R., Zaharia, M., Talwalkar, A.: Mllib: Machine learning in apache spark. J. Mach. Learn. Res. 17(1), 1235–1241 (2016)
  • [35] Murphy, K.P.: Machine learning : a probabilistic perspective. MIT Press, Cambridge, Mass. (2013)
  • [36] Neumann, T.: Efficiently Compiling Efficient Query Plans for Modern Hardware. PVLDB 4(9), 539–550 (2011)
  • [37] Ngo, H.Q., Porat, E., Ré, C., Rudra, A.: Worst-case optimal join algorithms. In: PODS. pp. 37–48 (2012)
  • [38]

    Ngo, H.Q., Ré, C., Rudra, A.: Skew strikes back: New developments in the theory of join algorithms. In: SIGMOD Rec. pp. 5–16 (2013)

  • [39] Nikolic, M., Olteanu, D.: Incremental view maintenance with triple lock factorization benefits. In: SIGMOD. pp. 365–380 (2018)
  • [40] Olteanu, D., Schleich, M.: F: Regression models over factorized views. PVLDB 9(10) (2016)
  • [41] Olteanu, D., Schleich, M.: Factorized databases. SIGMOD Rec. 45(2), 5–16 (2016)
  • [42] Olteanu, D., Závodný, J.: Size bounds for factorised representations of query results. TODS 40(1),  2 (2015)
  • [43]

    Park, Y., Qing, J., Shen, X., Mozafari, B.: Blinkml: Efficient maximum likelihood estimation with probabilistic guarantees. In: SIGMOD. pp. 1135–1152 (2019)

  • [44] Pedregosa, F., Varoquaux, G., Gramfort, A., Michel, V., Thirion, B., Grisel, O., Blondel, M., Prettenhofer, P., Weiss, R., Dubourg, V., VanderPlas, J., Passos, A., Cournapeau, D., Brucher, M., Perrot, M., Duchesnay, E.: Scikit-learn: Machine learning in python. J. Machine Learning Research 12, 2825–2830 (2011)
  • [45] Poon, H., Domingos, P.M.: Sum-product networks: A new deep architecture. In: UAI. pp. 337–346 (2011)
  • [46] R Core Team: R: A Language and Environment for Statistical Computing. R Foundation for Stat. Comp., www.r-project.org (2013)
  • [47] Rendle, S.: Factorization machines. In: Proceedings of the 2010 IEEE International Conference on Data Mining. pp. 995–1000. ICDM ’10, IEEE Computer Society, Washington, DC, USA (2010)
  • [48] Rendle, S.: Factorization machines with libFM. ACM Trans. Intell. Syst. Technol. 3(3), 57:1–57:22 (2012)
  • [49] Rendle, S.: Scaling factorization machines to relational data. PVLDB 6(5), 337–348 (2013)
  • [50] Schleich, M., Olteanu, D., Abo Khamis, M., Ngo, H.Q., Nguyen, X.: A layered aggregate engine for analytics workloads. In: SIGMOD. pp. 1642–1659 (2019)
  • [51] Schleich, M., Olteanu, D., Ciucanu, R.: Learning linear regression models over factorized joins. In: SIGMOD. pp. 3–18 (2016)
  • [52] Shaikhha, A., Klonatos, Y., Koch, C.: Building efficient query engines in a high-level language. TODS 43(1), 4:1–4:45 (Apr 2018)
  • [53] Shaikhha, A., Klonatos, Y., Parreaux, L., Brown, L., Dashti, M., Koch, C.: How to architect a query compiler. In: SIGMOD. pp. 1907–1922 (2016)
  • [54] Udell, M., Horn, C., Zadeh, R., Boyd, S.: Generalized low rank models. Foundations and Trends in Machine Learning 9(1), 1–118 (2016)
  • [55] Veldhuizen, T.L.: Triejoin: A simple, worst-case optimal join algorithm. In: ICDT. pp. 96–106 (2014)
  • [56] Wickham, H., Francois, R., Henry, L., Müller, K., et al.: dplyr: A grammar of data manipulation. R package version 0.4 3 (2015)
  • [57] Zaharia, M., Chowdhury, M., et al.: Resilient distributed datasets: A fault-tolerant abstraction for in-memory cluster computing. In: NSDI. pp. 2–2 (2012)