In order to uncover insights and trends, it is an increasingly common practice for companies of all shapes and sizes to gather large quantities of data and to then analyze that data. This data can come from a multitude of different sources, ranging from data gathered about consumer behavior to data gathered from sensors. The most prevalent way of storing and managing data has traditionally been a relational database management system (RDBMS). However, there is currently a disconnect between the tools used for analysis of data and the tools used for storing that data. Instead of working directly with RDBMSes, these tools are build to work in a stand-alone fashion, and offer integration with RDBMSes as an afterthought. The focus of my PhD research is on investigating different methods of combining popular analytical tools (such as R or Python) with database management systems in an efficient and user-friendly fashion

Raasveldt, M. (Mark)

English

CWI's Institutional Repository

Integrating Analytics with Relational DatabasesMark Raasveldtsupervised by Hannes Mu¨hleisen and Stefan ManegoldCentrum Wiskunde & InformaticaAmsterdam, Netherlandsm.raasveldt@cwi.nlABSTRACTIn order to uncover insights and trends, it is an increas-ingly common practice for companies of all shapes and sizesto gather large quantities of data and to then analyze thatdata. This data can come from a multitude of differentsources, ranging from data gathered about consumer behav-ior to data gathered from sensors. The most prevalent wayof storing and managing data has traditionally been a re-lational database management system (RDBMS). However,there is currently a disconnect between the tools used foranalysis of data and the tools used for storing that data.Instead of working directly with RDBMSes, these tools arebuild to work in a stand-alone fashion, and offer integrationwith RDBMSes as an afterthought. The focus of my PhDresearch is on investigating different methods of combiningpopular analytical tools (such as R or Python) with databasemanagement systems in an efficient and user-friendly fash-ion.1. INTRODUCTIONThere is a disconnect between data-intensive analyticaltools and traditional database management systems. Datascientists using these tools often prefer to manually managetheir data by storing it either as structured text (such asCSV or XML files), or as binary files [8]. This approachof managing data introduces a lot of problems, especiallywhen a large amount of data from different sources has tobe managed or combined. Flat file storage requires tremen-dous manual effort to maintain, is often difficult to reasonabout because of the lack of a rigid schema and is difficult toshare between multiple users. Furthermore, modifying thedata is prone to corruption because of lack of transactionalguarantees and atomic write actions. Another consequenceof this disconnect is that data scientists have re-implementedmany common database operations inside libraries such asdplyr [16] or Pandas [9]. Instead of performing joins or ag-gregations using a RDBMS, they perform them using theselibraries. However, these libraries suffer from having to loadProceedings of the VLDB 2018 PhD Workshop, August 27, 2018. Rio deJaneiro, Brazil.Copyright (C) 2018 for this paper by its authors. Copying permitted forprivate and academic purposes.(a) Client-Server connection.(b) In-database processing. (c) Embedded database.Figure 1: Different ways of connecting analytical tools witha database management system.all required data and intermediates into memory, leading tofrequent out of memory problems or poor performance dueto swapping.These issues could be solved through the use of a RDBMS.The RDBMS can prevent data corruption through ACIDproperties, it can automatically manage data storage forthe user and make data easier to reason about by enforc-ing a rigid schema. In addition, the RDBMS can performefficient execution on larger-than-memory data, and allowsconcurrent read and write access to the data in a safe way.Popular analytical tools such as R or Python can be usedin conjunction with database systems. There are SQLitebindings for these languages, and it is possible to connectto a database server using standard client connector proto-cols. However, data scientists prefer to use flat file storagemethods over these existing approaches, because these con-nections are either inefficient or inconvenient to use.The focus of this work is on identifying the problems en-countered when combining a RDBMS with analytical tools,and on implementing various solutions to overcome theseissues to allow for both a more efficient and more flexiblecombination of these tools. Figure 1 shows the three mainmethods in which a relational database can be combinedwith an analytical tool. We investigate each of these meth-ods, and attempt to improve them from both a usability anda performance perspective.1Figure 2: Communication between a client and server.2. CLIENT-SERVER CONNECTIONThe standard method of combining a standalone programwith a RDBMS is through a client-server connection. Thisis visualized in Figure 1a. In this way, the database serveris completely separate from the analytical tool. It runs aseither a separate process on the same machine or on a differ-ent machine entirely. The analytical tool can issue queries tothe database, after which the server will compute the answerto the query and transfer the results to the client throughthe socket. This process is shown in Figure 2.In order to perform analytics on data stored inside thedatabase, the data is exported from the database to the an-alytical tool over the socket connection, after which thatdata is processed in the client. The main advantage to thisapproach is that it is mostly database agnostic, as the stan-dardized ODBC or JDBC connectors can be used to connectto almost every database. In addition, it is relatively easy tointegrate into existing pipelines as the loading from flat filescan be replaced by loading from a database without havingto touch the rest of the pipeline.However, this approach is problematic when dealing witha large amount of data as is often required in modern analyt-ical pipelines. The time spent on serializing large result setsand transferring them from the server to the client can bea significant bottleneck. In addition, this approach requiresthe full dataset to fit inside the clients’ memory.In our work [13], we perform a survey of popular RDBMSand note that they are not optimized for the scenario ofhigh-volume data export. They take a significant amount oftime to export a relatively small amount of data even whenthe server and client are located on the same machine orconnected through a high-throughput network connection.This is because existing client protocols were designed forthe transfer of a small amount of rows in OLTP workloads,and have significant per-tuple and per-value overheads thatresult in the slow export of large tables.To remedy this problem, we investigate the different de-sign choices that can be made when designing a result setserialization format, and we propose a new client protocolthat is optimized for the transfer of large amounts of datafrom the server to the client. By using a column-majorchunk-wise format that utilizes lightweight compression anda binary format that is close to the native database format,we can export large tables an order of magnitude faster thanexisting solutions.However, even with a client protocol optimized for thisscenario, there is still a significant amount of time requiredto push data “over the wire”. In addition, as this approachonly replaces the loading of data from a flat file storagesystem with the loading of data from the RDBMS into theclient, it still requires the entire dataset and intermediatesto fit inside the clients’ main memory.3. IN-DATABASE PROCESSINGIn order to avoid the cost of exporting the data from thedatabase, the analysis can be performed inside the databaseserver. This method, known as in-database processing, isshown in Figure 1b.In-database processing can be performed in a database-agnostic way by rewriting the analysis pipeline in a set ofstandard-compliant SQL queries. However, most data anal-ysis, data mining and classification operators are difficultand inefficient to express in SQL. The SQL standard de-scribes a number of built-in scalar functions and aggregates,such as AVG and SUM [7]. However, this small number offunctions and aggregates is not sufficient to perform complexdata analysis tasks [15].Instead of writing the analysis pipelines in SQL, user-defined functions or user-defined aggregates in procedurallanguages such as C/C++ can be used to implement clas-sification and machine learning algorithms. This is the ap-proach taken by Hellerstein et al. [4]. However, these func-tions still require significant rewrites of existing analyticalpipelines written in vectorized scripting languages. In ad-dition, writing user-defined functions in these languages re-quire in-depth knowledge of the database internals and theexecution model used by the database [3].In order to make it easier to perform in-database ana-lytics, we introduced MonetDB/Python UDFs [12] in theOpen-Source DBMS MonetDB [6]. These user-defined func-tions can be written in Python, and process code in a vec-torized way. The input and output variables of the func-tions and aggregates can be provided as either standardizedNumPy arrays [14] or Pandas DataFrames [9]. In this way,the user-defined functions mimic the execution of regular an-alytical Python programs and can be written without anyknowledge of the database internals. Because of their vector-ized nature, the heavy interpreter overhead is not incurredonce for every tuple but only once for every invocation of thefunction. Combined with the use of zero-copy techniques forboth the input and output columns these functions can beexecuted efficiently on large datasets.1 SELECT MEDIAN(SQRT(i * 2)) FROM tbl;Listing 1: Chain of SQL operators.2Figure 3: Parallel operator chain of Listing 1.Parallelism. Another advantage of UDFs is that theycan take advantage of the databases’ automatic paralleliza-tion model. In MonetDB, parallel execution is achievedby marking individual operators as either parallelizable orblocking. When a chain of parallel operators is executed ona column, the column is split up into several chunks and theoperator is executed once on each chunk. When a blockingoperator is encountered, the chunks are packed into a sin-gle column and the blocking operator is executed on thatcolumn. This process is visualized in Figure 3.MonetDB/Python UDFs can be parallelized in the sameway. The functions can be set to either allow parallelization,in which case they are executed as a parallelizable operator,or to disallow parallelization, in which case they will operatewith the entire column as input. User-defined aggregates areparallelized over the different groups, where the aggregate iscalled once for each group with the tuples belonging to thatgroup as input. The aggregates computed for each groupare then gathered and combined to form the final result.Development Workflow. A challenge when developinguser-defined functions is that, since they are executed insidethe database server, standard tools and integrated develop-ment environments (IDEs) cannot be used to develop them.As a result, developers cannot use sophisticated debuggingtechniques (e.g., Interactive Debugging) and have to resortto inefficient debugging strategies to make their code work.In order to make it easier to develop MonetDB/PythonUDFs, we extended the client of MonetDB to allow for localtesting of user-defined functions [5]. The required data (ora sample of it) is automatically shipped from the databaseto the client together with the source code of the UDF. Itcan then be executed locally and run in either a stand-aloneinteractive debugger or a full-fledged IDE.Model Management. Another issue that arises is themanagement of different machine learning models. Currentsystems, such as TensorFlow, allow the models to be writtento disk as individual files. However, much like handling dataas flat files, handling models as flat files is cumbersome anderror-prone.In our work [11], we investigate how we can do model man-agement using a relational database. By storing the modelsin a relational database, we can store the models along-side their training information or meta-information gatheredabout the model. This allows us to query and apply themodels based on this information, as well as apply multiplemodels in parallel for ensemble learning.Figure 4: Transfer of the TPC-H lineitem table from thedatabase to the client process.4. EMBEDDED DATABASEBoth the previously managed approaches require the userto have a running database server. This requires signifi-cant manual effort from the user, as the database servermust be installed, tuned and continuously maintained. Forsmall-scale data analysis, the effort spent on maintainingthe database server often negates the benefits of using one.Embedding a database inside the client program, as shownin Figure 1c, is more applicable for these use cases. As thedatabase can be installed and run from within the clientprogram, maintaining and setting up the database is muchsimpler than with full-fledged database systems.The most commonly used embedded database is SQLite [2].However, SQLite is first and foremost designed for transac-tional workloads. It is a row-store database that uses avolcano-based processing model for query execution. Whilepopular analytical tools such as Python and R do haveSQLite bindings, it does not perform well when used for ana-lytical purposes. Even exclusively using SQLite as a storageengine typically does not work out well in these scenarios.Often only select columns of a table are used in analyses,and its row-wise storage layout forces it to always load en-tire tables. This can lead to very poor performance whendealing with wide data.To fill this gap, we created MonetDBLite [10], an Open-Source embedded database based on the popular columnardatabase MonetDB. Much like SQLite, it is an in-processdatabase that can be installed and run directly from withinpopular analytical tools without any external dependencies.However, unlike SQLite it is designed for analytical work-loads, and as such performs significantly better when ex-ecuting analytical queries that operate on large amountsof data. Because of the columnar layout of the databaseand zero-copy semantics, data can be copied between thedatabase and the analytical tool for a constant cost, and nolarge costs need to be paid when extracting only a subset ofcolumns from a wide table.This efficient data transfer is illustrated in the experi-ment in Figure 4, where we transfer the lineitem table fromthe TPC-H benchmark [1] from the database to the clientprocess using MonetDBLite, SQLite, MonetDB and Post-greSQL. We observe that data can be exported from Mon-etDBLite an order of magnitude faster than over either a3socket connection (in the case of MonetDB and PostgreSQL)or from the row-storage model of SQLite.5. RESEARCH DIRECTIONSWhile our current solutions have made it both easier andmore efficient to combine relational databases with analyt-ical tools, connecting them efficiently and effortlessly is byno means a solved problem. In this section, we will describethe open research problems that we have identified and howwe plan on tackling them in the future.Automatic Code Shipping. While user-defined func-tions allow for efficient in-database analytics, it still requiressignificant manual transformation effort to take an exist-ing analytical pipeline and make it run inside the databaseserver. Ideally, we would be able to automatically translatean existing analytical pipeline and execute it on data resid-ing in the database without requiring manual user effort.A solution to this problem could be to take a programthat uses a database connector to connect to a database,and run the code directly inside the database server. In-stead of connecting with the database through a socket, theSQL code could be directly executed inside the server andthe results could be used inside the analytical tool withoutrequiring data transfer. This approach does negate the po-tential advantages of automatic parallelization, however.Alternatively, the code could be analyzed and translatedinto user-defined functions that can be executed within thedatabase server and could potentially be parallelized. An-alyzing if arbitrary code could be safely parallelized is notpossible, though, as it would be equivalent to solving theHalting problem. However, it would already be useful if alimited subset of operations could be automatically shippedand executed in parallel inside the database server. For ex-ample, a number of commonly used operations of the Pandasand NumPy libraries could be supported.UDF Co-optimization. Currently, MonetDB/PythonUDFs are executed as black-box functions. As a result, thereis almost no room for automatic optimization of the actualcode. The only optimization we apply is the parallelizationof the functions, however, even this requires the user to tellus whether or not the function is parallelizable.Lazy evaluation could allow us to optimize the UDFsmore. Rather than executing the function in an eager man-ner, we could defer the execution of certain operations onthe input columns (e.g. common NumPy and Pandas oper-ations). This would allow us to build a computation graph,and either (1) run parts of that computation graph insidethe databases’ execution engine (where it could be executedin parallel and take advantage of existing indexes), or (2)feed information extracted from the computation graph tothe database optimizer.AcknowledgmentsThis work was funded by the Netherlands Organisationfor Scientific Research (NWO), project “Process Mining forMulti-Objective Online Control”.6. REFERENCES[1] TPC Benchmark H (Decision Support) StandardSpecification. Technical report, TransactionProcessing Performance Council, June 2013.[2] G. Allen and M. Owens. The Definitive Guide toSQLite. Apress, Berkely, CA, USA, 2nd edition, 2010.[3] Q. Chen, M. Hsu, and R. Liu. Extend UDFTechnology for Integrated Analytics. In T. Pedersen,M. Mohania, and A. Tjoa, editors, Data Warehousingand Knowledge Discovery, volume 5691 of LectureNotes in Computer Science, pages 256–270. SpringerBerlin Heidelberg, 2009.[4] J. M. Hellerstein, C. Re´, F. Schoppmann, D. Z. Wang,E. Fratkin, A. Gorajek, K. S. Ng, C. Welton, X. Feng,K. Li, and A. Kumar. The MADlib Analytics Library:Or MAD Skills, the SQL. Proc. VLDB Endow.,5(12):1700–1711, Aug. 2012.[5] P. Holanda, M. Raasveldt, and M. Kersten. Don’tHold My UDFs Hostage - Exporting UDFs ForDebugging Purposes. In Proceedings of the 28thInternational Conference on Simpo´sio Brasileiro deBanco de Dados, SSBD 2017, Uberlndia, Brazil, 2017.[6] S. Idreos, F. Groffen, N. Nes, S. Manegold,S. Mullender, and M. Kersten. MonetDB: TwoDecades of Research in Column-oriented DatabaseArchitectures. IEEE Data Eng. Bull, 2012.[7] ISO. ISO/IEC 9075:1992, Database Language SQL.Technical report, International Organization forStandardization (ISO), July 1992.[8] S. Kandel, A. Paepcke, J. M. Hellerstein, and J. Heer.Enterprise Data Analysis and Visualization: AnInterview Study. IEEE Transactions on Visualizationand Computer Graphics, 18(12):2917–2926, Dec. 2012.[9] W. McKinney. Data Structures for StatisticalComputing in Python. In S. van der Walt andJ. Millman, editors, Proceedings of the 9th Python inScience Conference, pages 51 – 56, 2010.[10] M. Raasveldt. MonetDBLite: An EmbeddedAnalytical Database. SIGMOD ’18: Proceedings of the2018 ACM International Conference on Managementof Data, 2018.[11] M. Raasveldt, P. Holanda, H. Mu¨hleisen, andS. Manegold. Deep Integration of Machine LearningInto Column Stores. In Proceedings of the 21stInternational Conference on Extending DatabaseTechnology (EDBT), 2018.[12] M. Raasveldt and H. Mu¨hleisen. Vectorized UDFs inColumn-Stores. In Proceedings of the 28thInternational Conference on Scientific and StatisticalDatabase Management, SSDBM 2016, Budapest,Hungary, July 18-20, 2016, pages 16:1–16:12, 2016.[13] M. Raasveldt and H. Mu¨hleisen. Don’t Hold My DataHostage: A Case for Client Protocol Redesign. Proc.VLDB Endow., 10(10):1022–1033, June 2017.[14] S. van der Walt, S. Colbert, and G. Varoquaux. TheNumPy Array: A Structure for Efficient NumericalComputation. Computing in Science Engineering,13(2):22–30, March 2011.[15] H. Wang and C. Zaniolo. User-Defined Aggregates inDatabase Languages. In R. Connor and A. Mendelzon,editors, Research Issues in Structured andSemistructured Database Programming, volume 1949of Lecture Notes in Computer Science, pages 43–60.Springer Berlin Heidelberg, 2000.[16] H. Wickham. Package ’dplyr’: A Grammar of DataManipulation, 2017.4

Integrating analytics with relational databases

Abstract

Similar works

Full text

Available Versions

CWI's Institutional Repository

NARCIS