1.1B Taxi Rides Benchmark Using DuckDB(tech.marksblogg.com)
tech.marksblogg.com
1.1B Taxi Rides Benchmark Using DuckDB
https://tech.marksblogg.com/duckdb-1b-taxi-rides.html
17 comments
There’s a lot of DuckDB activity this week. Especially those using its WASM version inside browser. Observable Framework that was published month ago added DuckDB SQL support last week and a cross filtering visualization Mosaic library this week. Here they show one million taxi rides in their docs: https://observablehq.com/framework/lib/mosaic
One billion rides might just be too much for browser based DuckDB. Perhaps one could select only a few months of data. If split into smaller files DuckDB should be able to read only relevant parts.
One billion rides might just be too much for browser based DuckDB. Perhaps one could select only a few months of data. If split into smaller files DuckDB should be able to read only relevant parts.
I had the same issue with ingesting lots of csv files in a previous version (8.x), though it seemed to be hardware dependent; issue was only on windows not Ubuntu. I think setting the number of threads to low value helped. I had hoped these issues would be solved by v 0.10 but I am nevertheless a big fan of duckdb.
I tried DuckDb once some months ago after reading that it can handle json files. I don’t remember the exact failure details, but it crapped out parsing a json file only few MB large, with some buffer size errors.
What’s the overall opinion of duckdb here?
DuckDB is ok at JSON data but it hasn’t worked around all the corner cases like Pandas has. Converting from a hierarchical format like JSON to a tabular format is always fraught. I find Pandas to be slightly more robust and faster.
However for any kind of tabular data, DuckDB blows everything out of the water. I’ve mostly been using it with Parquet and TSV files and the performance is just unbelievable. SQL that even Redshift struggles with can be done in DuckDB in no time. If the data is parquet, there is no need to reach for Spark. DuckDB is just way faster.
The level of SQL supported by duckdb is also outstanding, from pivot/unpivot, and friendly SQL constructs like group by all, order by all, it makes writing complicated SQL a pleasure.
However for any kind of tabular data, DuckDB blows everything out of the water. I’ve mostly been using it with Parquet and TSV files and the performance is just unbelievable. SQL that even Redshift struggles with can be done in DuckDB in no time. If the data is parquet, there is no need to reach for Spark. DuckDB is just way faster.
The level of SQL supported by duckdb is also outstanding, from pivot/unpivot, and friendly SQL constructs like group by all, order by all, it makes writing complicated SQL a pleasure.
I’m having the same issues, where I need to load json files one by one instead of loading them in a batch.
It looks like memory is not freed as soon as the file is parsed.
Edit: setting threads to a low value as read in this thread solved my issue.
Edit: setting threads to a low value as read in this thread solved my issue.
I'm unclear why this article is flagged. Mark's been doing these articles for years. This one is straight-up tech, like most of his posts.
It would be interesting to see the performance of protobuf vs Parquet in this case...not sure if DuckDB supports that...
How is query performance on DuckDB’s native format compared to parquet?
I wrote a page about this: https://duckdb.org/docs/guides/performance/file_formats
TL;DR: if your workload is aggregation-heavy, Parquet is pretty good. If you have many joins, the lack of fine-grained statistics will likely result in bad join plans.
(Disclaimer: I work at DuckDB Labs)
TL;DR: if your workload is aggregation-heavy, Parquet is pretty good. If you have many joins, the lack of fine-grained statistics will likely result in bad join plans.
(Disclaimer: I work at DuckDB Labs)
[deleted]
Nice summary how some development progresses. Sometimes it’s struggle after struggle after struggle.
It's about "how fast DuckDB can run the 1.1 billion taxi rides benchmark", with the bulk of the article dedicated to importing the data. It's not about use in production for schedule 1B rides or storing the records associated with these rides.
If you write some custom software for it that uses binary representations and maps repetitive information to shorter IDs, you should be able to fit the entire dataset into 64GB of RAM (less than 60 bytes / row). At that point running most analytical queries on it becomes really quick even if you do not care about indexing the main data and just rely on tools like MapReduce. You could probably dump that representation into an mmap'ed file on a SSD and get similar results even with less RAM.
Should you still use a DB? Probably. But if your DB is a bigger hassle and significantly slower than one machine running some wonky code you wrote in an afternoon, it probably sucks. Now after reading the article I'm still not 100% sure whether the initial issue with DuckDB was performance or data related. If it's the former, then any DB running on modern hardware that starts choking handling a million anything doesn't deserve to be called a database in this day and age - at that point Microsoft Excel is your main competitor and offers a better user experience.