What does Arrow have to do with Parquet? We are talking about the file format Parquet, right? Does Arrow use Parquet as its default data storage format?
But isn't Arrow a format too? As I understand it, Arrow is a format optimized for transferring in-memory data from one distributed system to another (ser-des), while also facilitating and optimizing certain set operations. From RAM in one system to RAM in another.
Moreover, since Arrow is a format, why is it being compared to databases like SQLite and DuckDB? If we're talking about formats, why not compare Arrow queries against Parquet data to DuckDB queries against Parquet data? https://duckdb.org/docs/data/parquet
Why not at least benchmark the query execution alone instead of startup and loading of data? For Arrow, isn't it assumed that there is an engine like Spark or Snowflake already up and running that's serving you data in the Arrow format? Ideally, with Arrow you should never be dealing with data starting in a resting format like Parquet. The data should already be in RAM to reap the benefits of Arrow. Its value proposition is it'll get "live" data from point A to B as efficiently as possible, in an open, non-proprietary, ubiquitous (eventually) format.
Exactly what of SQLite, DuckDB and Arrow is being compared here?
I would assume the benefits of Arrow in R (or DataFrames in general) would be getting data from a data engine into your DataFrame runtime as efficiently as possible. (just as interesting might be where and how push-downs are handled)
Perhaps I'm missing the trees for the forest?
No disrespect to the author... Seems like they're on a quest for knowledge, and while the article is confusing to me, it certainly got me thinking.
Disclaimer: I don't read R too good, and I'm still struggling with what exactly Arrow is. (Comparisons like this actually leave me even more confused about what Arrow is)
This question comes up quite often. Parquet is a _file_ format, Arrow is a language-independent _in-memory_ format. You can e.g. read a parquet file into a typed Arrow buffer backed by shared memory, allowing code written in Java, Python, or C++ (and many more!) to read from it in a performant way (i.e. without copies).
Another way of looking at it, if you have a C++ background, is that (roughly speaking) it makes C++'s coolest feature - templates -, and the performance gains obtained by the concomitant inlinability of the generated code - available in other languages. For example, you can write `pa.array([1, 2], type=pa.uint16())` in python, which translates roughly to `std::vector<uint16_t>{1, 2}` in C++. But it's not quite that; Arrow arrays actually consist of several buffers, one of which is a bit mask indicating whether the next item in the array is valid or missing (what previously was accomplished by NaN).
While I'm not a huge fan of Arrow's inheritance-based C++ implementation (it's quite clunky to say the least), it's an important project IMHO.
Next, why compare Arrow with SQLite and DuckDB? Because it's what it's being used for already! For example, PySpark uses Arrow to mediate data between Python and Scala (the implementation language), providing access to the data through an SQL-like language.
> read ... into a typed Arrow buffer backed by shared memory, allowing code written in Java, Python, or C++ (and many more!) to read from it in a performant way (i.e. without copies).
Very powerful indeed.
You lost me here though:
> Next, why compare Arrow with SQLite and DuckDB? Because it's what it's being used for already!
What is already being used for what?
The example that follows that describes the advantages of PySpark (Python/Scala) using Arrow makes sense, but I'm having trouble understanding your assertion relating it to SQLite and DuckDB?
That's like comparing SQLite to Scala because Spark is written in Scala and exposes a SQL interface.
> Why not at least benchmark the query execution alone instead of startup and loading of data?
Because they don’t intend to have R running with 4gb of data in memory constantly.
I’d assume there’s a SQLite /duckdb instance running, but that it is negligible. Maybe the test even includes starting the database server.
So the perspective is “I want some data on my hard drive, and I want to query it from R every once in a while, what are my options?”
And suddenly a file format becomes comparable to a database.
Another way to look at it is to consider the compiled arrow code in the arrow library as a database engine. Then arrow is just another alternative to SQLite.
In my opinion, the point about formats remains and the purpose of Arrow is lost.
I don't know enough about Arrow, but surely there is a better storage format than Parquet, all though storage isn't primary consideration for Arrow. The purpose of Arrow is not to have to convert from one format to another. Data can be efficiently transferred from RAM across the wire to RAM again without any significant transformation.
Surely there is a storage representation for Arrow to deliver similar characteristics as its intended use? Eg:
disk (arrow format?) -> RAM (arrow)
disk (parquet) -> RAM (parquet) -> CPU (transform) -> RAM (arrow)
After all, isn't this exactly how SQLite is being tested in this case? The original data file is loaded into SQLite and stored in SQLite's native file format providing all of the des-ser advantages SQLite provides out of the box. Not to mention the indexing that's defined as part of this preparation.
I imagine they’re all getting compared because 2 of them are “embedded” relational DB (even though their data structure layout is optimised for different things) and the remaining option appears to present similar benefits and functionality in a different style.
## format median_time mem_alloc
## 1 R (RDS) 1.34m 4.08GB
## 2 SQL (SQLite) 5.48s 6.17MB
## 3 SQL (DuckDB) 1.76s 104.66KB
## 4 Arrow (Parquet) 1.36s 453.89MB
Edit: that said a benchmark would be worthwhile, and similarly the tidiverse should evolve towards arrow speed I hope since they also share a co-creator.
There's:
* arrow, a in-memory format for dataframe-shaped things supporting fast computation, zero-copy sharing, etc.
* arrow Feather V1, an on-disk format for dataframe-shaped things
* arrow IPC, a (de)serialization for arrow buffers and a protocol for sending/receiving to other processes.
* arrow Feather V2, an on-disk format that's basically the IPC serialization written to file[1]
* arrow Flight, a protocol for requesting/sending/receiving data to remote machines that's basically gRPC layered over the IPC format
* arrow DataFusion/Ballista, nascent system(s) for local/distributed query execution over arrow buffers
* other subprojects I'm surely forgetting
* a (very good) C++ Parquet reader [2]/[3] developed under the auspices of the project
* libraries in / bindings to many languages, including R and Python, supporting interaction with (subsets of) the above.
It's only the last piece that's exposed to most data-science-y users, and thus identified with the 'arrow' name. Since those libraries are also very good, and hiding their abstractions well, those users are free to use the functionality relevant to them, be it dealing with parquet, feather, etc. without needing to understand how they work.
Not that this is a criticism of the project, or those users! Arrow encompasses lots of different functionality, which enables it to provide different things to different people. As a result, though, 'Arrow' connotes lots of different things (and different _sorts_ of things) to different users, which can cause some confusion if terms aren't fully specified, or even a bit misunderstood
[1] https://stackoverflow.com/a/67911190/881025 [2] https://github.com/apache/parquet-cpp [3] https://github.com/apache/arrow/tree/master/cpp
The analysis would be more telling if it measured insert performance as well.
Columnar databases are a good fit here.
Mature users need to evaluate how their workloads match the benchmarks, it isn't a matter of DuckDB or SQLite "winning".
Adoption is slow, but it'll get there.
I’m using DataFusion (via Rust) and it’s pretty fantastic. Would love to swap out some Spark stuff for DataFusion/Ballista stuff at some point as well.
(This requires both ends to be able to handle the Arrow representation.)
Eg, it has the potential to speed up query processing in PySpark by a lot, because of its Java/Python interoperability.
* The comparison used a single, simple, query: filter, group by, and sum.
* The comparison does not include repeated multiple queries (similar or dissimilar).
* A moderate amount of data is used.
This is certainly relevant and important, but it's not an extensive comparison.
>dbSendQuery(con, "CREATE INDEX reporter_iso ON yrpc (reporter_iso)")
would sqlite use these for the query given?
it seems they wouldn't help with grouping, and the grouping seems antagonistic to filtering. if you're trying for performance, you can avoid having the query engine refer back to the original table during processing by using a covering index that includes the data fields in addition to the query fields
"CREATE INDEX covering (reporter_iso, year, trade_value_usd_exp, trade_value_usd_imp)"
or maybe even something like
"CREATE INDEX covering (reporter_iso, year, reporter_iso, trade_value_usd_exp, trade_value_usd_imp)"
Though I'm not sure how it's query planner would do with a duplicate column in the index.
This just looks like a tidyverse library comparison. I'd expect a benchmark using their own libs, rather than assuming tidyverse will have an optimal way of querying them.
The fact that ´tbl´ in the beginning is called with a connection, ensures that all the following functions don't execute on a dataframe, but instead builds up a query.
R, kinda like Julia, executes different versions of a function, depending on the type of the first parameter.
From an API point of view, I think it is absolutely ingenious!
So that we can appreciate your point about the elegance of the API, how would you do the opposite to allow execution to take place on the data-frame?
It is unclear how much functionality is available for Arrow under R: any comments? It would also be interesting to see a similar benchmark for Python, which could include the embedded version of MonetDB -- an R package for MonetDB/e is not yet available.
Edit: amended the TLDR to reflect jhoechtl's and wodenokoto's comments. SQLite provided reasonably memory efficient aggregates.
RDS is slow to load, because it has to unzip and read everything into memory. All others are fast to load because they have somesort of index into data on disk (at the cost of being much larger at rest)
Everything else is fast to load compared to RDS, with arrow being the fastest because its index happened to be optimized for the test query.
I nowhere read that the authors come to the conclusion that SQlite does NOT provide fast and memory efficient database aggregates. Instead they conclude
> SQLite and DuckDB files consists in a single large file (3 GB and 2.5 GB each), but the indexes we created allow their respective packages to read a copy of the tables that has just the year and reporter_iso columns, and therefore allows very fast filtering to provide the exact location of what we need to read in the large tables.
If you prefer SQL, using raw Arrow or in-memory data doesn't make sense - Except duckdb does support SQL queries over these non-db data sources as well.
Anyway, R data ecosystem very rich for both R and SQL fans and I as a Python user feel a bit envious.
Also, Parquet supports different encodings, some of which may be faster, denser, and/or consume less RAM, or all of the above.
It can certainly be used in the same manner as DataFusion, but can also serve as a stand-alone database system. DuckDB aims to have much more comprehensive SQL support beyond only SELECT queries.
Not an advice, but you should probably consider spinning a secondary product from DuckDB with a sole focus on "reading data from parquet files and running aggregations the most efficiently possible". You can probably skip INSERT, UPDATE, DELETE completely.
There is currently a gap in practical solutions for this pain point. You can use Spark or Airflow, but nothing that comes without a big infra price tag (you can do that with pandas, but you need a large instance to load the entire dataset in memory). I think the right product could even outpace what you currently have with DuckDB.
Anytime you might want to use SQlite as an actual RDBMS (not as an easy disk fileformat), drop in DuckDB instead and get much better performance. DuckDB is actually designed to support OLAP.
Aren't RDBMS good at both OLAP and OLTP while not being the best at each?
> DataFusion is an extensible query execution framework, written in Rust, that uses Apache Arrow as its in-memory format.
Does DuckDB use Arrow as its in-memory format? If so, that's pretty awesome.
I'm hearing about DuckDB for the first time today and it's already leaving an impression on me.
I don't think it's the case, but they serve the same purpose anyway: inject the CSV or parquet files you got from the data wharehouse into something you can use for analytics.
Memory-based storage is super fast and fine until your datasets reach a terabyte. So you have a few solutions left:
1. You fire a fresh postgres and you store your data there. It's going to take quite a bit of time to inject those 600M lines into the instance.
2. You have some kind of tool (DataFusion) that allow you to run SQL queries on those parquet files without going through a painful copy/insert process. Performances matter a bit, but not as much because you're already avoiding a full dataset copy and a large-instance expense in the process. Even a 40% perf hit vs Postgres is absolutely acceptable, because you're already winning on both sides (total execution time, financial expenses).
Let's say you have some data. You can choose to store it in a relational DB, like SQLite or DuckDB, or you can store it in a parquet file (and load it into an Arrow buffer).
And the point is that if you combine Arrow with, say, Spark, then as a user you can accomplish something similar to what you might accomplish with a relational DB. But you don't need to hassle with setting up a DB server and maintaining it. All you need is a job that outputs a parquet file, and uploads that to S3. And then Spark - through Arrow! - will allow you to execute queries against that DB.
Using Arrow + Spark, you get the ability to a dataframe as if it's SQL, but you can still do pandas-style stuff i.e. treat it as a dataframe. OTOH you lose the more esoteric SQL stuff like fancy constraints, triggers, foreign keys.
[1] https://github.com/ggrothendieck/sqldf
I'm pretty sure that I'll be getting my hands dirty with R eventually.
I think the format layouts are the same, the main difference between them being compression. Compressed data can in many cases be faster than uncompressed data to read/scan, (https://stackoverflow.com/questions/48083405/what-are-the-di...) so the cpu transform above is simply an uncompress step, which is notably simpler than what would go on with Sqlite to transform the data to a sqlite structure.
What we're looking at here in this test is a direct-data-access pattern (DDA), which is important as you can avoid ETL caching steps eg. Parquet->Postgres (which have an ingestion time) if you can access the data quick enough for your use case, and if the data is on say s3, you can have multiple (parallel) readers onto the same data rather than a connection pool for databases.
It also allows joining different larger-than-memory datasets efficiently, and avoids much of the infrastructure costs for something like Presto (Athena has per-query costs instead of infrastructure costs).
What I'd have liked to see, would be a Vaex/DuckDB benchmark, as the main differentiation between them appears to be SQL vs df/linq/dyplr/Rx semantics.
There’s also an Arrow “blob” format that can be used, but that’s not meant to be used as a full persistence format (as I understand)
The value proposition is that increasing numbers of query/analysis “engines” support the Arrow in memory format which is fantastic for interoperability. “I need a data frame library like Pandas but I’m not in Python” is no longer an issue.
I thought the database process was still a server when running locally, like how Wayland is a display server.
I don't know much about R, but if it considers the whole memory space of the R process, it probably counted the memory consumption of the libraries themselves as well.
If you start comparing them to solutions that copy datasets, you haven't understood what problem they are solving. For that problem, use postgresql or bigquery.