In the age of massive data, databases are getting less convenient for data exploration tasks due to the costly loading phase. Still, the highly optimized query engines of database systems are greatly beneficial for the performance of data analysis tasks. With our research, we want to bridge this gap and provide paramount analytical performance without the need of static data loading. Our approach enables the integration of Parquet files - one of the most used columnar file format in the data lake context - into the data processing pipeline of a database system in a convenient way. We allow end-users to benefit from the database system performance without a costly and time-consuming loading phase.
Published in the Proceedings of the Workshops of the EDBT/ICDT 2024 Joint Conference (March 25-28, 2024), Paestum, Italy
© 2024 Copyright for this paper by its authors. Use permitted under Creative Commons License CPWrEooUrckReshdoinpgs IhStpN:/c1e6u1r3-w-0s.o7r3g ACttEribUutRion W4.0oInrtekrnsahtioonpal (PCCroBYce4.0e).dings (CEUR-WS.org)
For example, Mühlbauer et al. [
Apache Arrow, an in-memory columnar format, was
used as a foundation for the storage layer of a DBMS in
the work of Li et al. [
approach to eficiently process JSON files. They also face the challenge of nested data and tackle it by extracting common schema parts and materializing them into a columnar format. Robust parallelization. We aim for optimal perfor
Wang et al. [10] present Steed, a native database system mance independent of how the utilized Parquet writer designed for tree-structured data. They built a row and a distributed the data over the Parquet levels (Row Groups columnar data layout to store JSON data. For the column and Pages) and for which granularity statistics were coldata, they utilize the Dremel schema. They suggest opti- lected and stored in the Parquet file. Some Parquet file mizing for simple accesses with at most one array along writers that write significantly more rows into one row the access path, so fewer nodes must be visited. group than what we consider a good number of rows that
Trance, presented by Smith et al. [11], is a framework should be processed as one batch [12, 13].Hence, we deal that transfers queries that run on nested collections to with this situation by parallelizing below row group level a set of relational queries that can run on flattened col- which we visualize in Figure 1 with the threads T1 - T4, lections. They introduce a “fully-flattened” data repre- where each row group is processed by two threads in sentation, whereas the Dremel encoding stores the data parallel. While scanning a single batch, we scan as little “semi-flattened”. data as possible. First, we only scan the columns that are
already completed. Afterwards, we explain our current work. As a first step, we focused on eficiently scanning Parquet files without any nested types. In the second step, which we are working on, we look into adding support for nested types. This support will be added orthogonally so that we are not forced to rewrite our scanner entirely and keep the complexity of nested types outside of the scanner implementation.
The main challenge of building an eficient Parquet Scanner is to handle the broad variety of potential input file structures. Parquet files are first split horizontally into row groups with an arbitrary number of rows. Each row group is then stored column-wise in so-called column chunks. The data is split per column chunk into one or more pages with an arbitrary number of values per page using one of the many diferent supported encodings and compressions. Such files can be created by diferent Parquet writers with diferent settings, leading to arbitrarily diferent files. In our example in Figure 1, the Parquet file contains three columns (x, y, and z) and is split into two row groups. The column chunks inside the row groups are split into one to three pages. required for producing the query result, columns x and 3.2. Dedremelize Parquet Files z in our example. This optimization can be easily realized since Parquet stores the data in a columnar format. We consider the handling of nested data an orthogonal Hence, columns can be accessed independently, which problem to an eficient Parquet scanner. Therefore, we ifts quite nicely into a columnar engine like Umbra [ 13]. investigate that topic based on the assumption that the underlying database system already supports scanning
Multi-level pruning. Suppose we have to evaluate Parquet files with basic types. selection predicates on specific columns; we can use these Relational database systems are optimized for nonpredicates in two phases to minimize the data we have nested data, specifically in third normal form. The to access. First, we use them to exclude files, row groups, Dremel encoding allows Parquet files to store nested data and pages as early as possible. In our example in Figure 1, in a columnar way. Basic fields are stored in their own we have a selection predicate for column x with whom columns, and the so-called repetition levels tell us later we can exclude in each row group one entire page. how the nested structure can be rebuilt using an automa
Since min/max statistics are specified as optional fields ton presented in the Dremel encoding paper [
The second phase, where we use the predicates to min- If we postpone the reconstruction of the nesting to imize the scanned data, is during the actual data loading. a later stage, we can split the scan of nested data into We start by loading the columns that are restricted by the multiple scans that can be performed individually on the predicates and evaluate them with vectorized functions. grouped column sets from above. Based on the repetition In Figure1, we do that for column x. Then, we only access levels, we can generate what we call surrogate keys that the rest of the columns, in our example column y, if the allow us to later join the diferent levels back together. We predicate holds for the specific row. will publish more details on that in the future, addressing all the requirements and showing performance results.
On-the-fly statistics computation. Even with those parallelization and pruning techniques, we observed Benchmarks for nested data. Finding approprisignificant performance diferences for more complex ate benchmarks is very challenging since nested data queries that are ran on Parquet files instead of their na- can be very versatile. The number of nesting levtive database relation equivalents. Due to some Parquet els and the children-to-parent ratio can vary signifiifles lacking statistics entirely and only basic min/max cantly. We identified two resources for benchmarks: statistics being available in the rest of the Parquet files, we Firstly, there exists work on benchmarking big data decided to compute our own set of statistics for each col- systems using a modified version of the TPC-H benchumn inside a Parquet file whenever they are accessed for mark that nests lineitems into the corresponding the first time. We compute HyperLogLog sketches [ 14] orders [16] and even the orders into the correspondand keep a random data sample. We keep the overhead ing customers [11]. The downside of nesting the TPC-H of the statistics computation low by not accessing the dataset is that one of the benchmark’s key challenges, data unnecessarily. We only write such statistics when the join performance, is at an advantage since the data scanning the data for query evaluation. Starting with the is already grouped by the primary key/foreign key join second time the file is accessed, our query optimizer can predicates. work with these statistics to generate query plans that Second, there exists work on benchmarking other are as optimized as for standard database tables. nested file formats like JSON and XML [ 9, 17, 18]. The
Based on the statistics and information from the query downside of these benchmarks is that they were designed plan, we also try to estimate the primary keys of each file to highlight the performance bottlenecks that those file which help us decide if we have a primary key/foreign formats introduce, like the unknown schema and the key join. Based on the usage of the columns and distinct reading from a non-binary file format. In addition, they value estimates, we check if single columns and pairs of were not designed to be used for big data workloads columns could be primary key candidates. but for data sizes that are still manageable in a human
Our experiments show that combining all these tech- readable format. Scaling those benchmarks up to achieve niques enables us to process Parquet files very eficiently big data scale is not always possible. DeepBench [19] is and achieve similar performance compared to data stored an extensible and scalable benchmark for JSON data foin traditional database relations. In addition, we can out- cusing on diferent nesting levels and array types which perform other existing systems that support querying could be extended to Parquet since it is targeting similar Parquet files directly. bottlenecks. We will evaluate these benchmarks and microbenchmarks in the future to get a better understanding fast transactional workloads on universal columnar of their capabilities and limitations. data file formats, VLDB 14 (2020) 534–546. [7] C. Liu, A. Pavlenko, M. Interlandi, B. Haynes, A deep dive into common open formats for analytical 4. Future Work dbmss, VLDB 16 (2023) 3044–3056. [8] X. Zeng, Y. Hui, J. Shen, A. Pavlo, W. McKinney, Our current work already discusses how Parquet scan- H. Zhang, An empirical evaluation of columnar ning capabilities can be integrated into database engines. storage formats, VLDB 17 (2023) 148–161. In the real world, extensions for Parquet files and other [9] D. Durner, V. Leis, T. Neumann, JSON tiles: Fast columnar storage formats make file-based storage for- analytics on semi-structured data, in: SIGMOD mats even more powerful. Well-known extensions are Conference, 2021, pp. 445–458. Iceberg [20] and Deltalake [21]. They allow schema evo- [10] Z. Wang, S. Chen, Exploiting common patterns for lution, support updates as deltas or full rewrites, and even tree-structured data, in: SIGMOD Conference, 2017, support time travel to earlier versions. In the future, we pp. 883–896. plan to investigate how the aforementioned extensions [11] J. Smith, M. Benedikt, M. Nikolic, A. Shaikhha, Scalalign with the capabilities of a database engine and how able querying of nested data, VLDB 14 (2020) 445– these can be integrated seamlessly into existing systems. 457. [12] V. Leis, P. A. Boncz, A. Kemper, T. Neumann, 5. Conclusion Morsel-driven parallelism: a numa-aware query evaluation framework for the many-core age, in: We presented our investigations on how the gap between SIGMOD Conference, 2014, pp. 743–754. highly eficient relational database systems and data lake [13] T. Neumann, M. J. Freitag, Umbra: A disk-based ifle formats can be bridged. We started with a Parquet system with in-memory performance, in: CIDR, scanner that shows how these files can be seamlessly 2020. integrated into the stack of a relational database engine. [14] P. Flajolet, É. Fusy, O. Gandouet, F. Meunier, HyperOur current work focuses on supporting nested data, a loglog: the analysis of a near-optimal cardinality common pattern in big data workloads. The goal is to estimation algorithm, in: Discrete Mathematics and keep the implementation efort and complexity as low as Theoretical Computer Science, 2007, pp. 137–156. possible. In addition, we want to abstract as much logic [15] S. Melnik, A. Gubarev, J. J. Long, G. Romer, S. Shivaway from the core database engine as possible. The first akumar, et al., Dremel: A decade of interactive SQL measurements show promising results, proving that our analysis at web scale, VLDB 13 (2020) 3461–3472. vision of a clean, easy-to-implement addition does not [16] P. Pirzadeh, M. J. Carey, T. Westmann, A perforcontradict competitive performance. mance study of big data analytics platforms, in: IEEE BigData, 2017, pp. 2911–2920. [17] A. Schmidt, F. Waas, M. L. Kersten, M. J. Carey, References I. Manolescu, R. Busse, Xmark: A benchmark for XML data management, in: VLDB, 2002, pp. 974– 985. [18] C. Truica, E. S. Apostol, J. Darmont, T. B. Pedersen,
The forgotten document-oriented database management systems: An overview and benchmark of native XML dodbmses in comparison with JSON dodbmses, Big Data Res. 25 (2021) 100205. [19] S. Belloni, D. Ritter, M. Schröder, N. Rörup, Deepbench: Benchmarking JSON document stores, in:
DBTest@SIGMOD, 2022, pp. 1–9. [20] Apache Software Foundation, Apache iceberg, 2013.
URL: https://iceberg.apache.org. [21] M. Armbrust, T. Das, S. Paranjpye, R. Xin, S. Zhu, et al., Delta lake: High-performance ACID table storage over cloud object stores, VLDB 13 (2020) 3411–3424.