I see no MDX query support. How is this an OLAP database? Or do I misunderstand what it is?
DuckDB – An in-process SQL OLAP database management system
31–40 of 104 posts
Re: DuckDB – An in-process SQL OLAP database management system
#32Earlier quoted context omitted.
We use DuckDB extensively where I work ( https://watershed.com ), the primary way we're using it is to query Parquet formatted files stored in GCS, and we have some machinery to make that doable on demand for reporting and analysis "online" queries.
Do you load the Parquet files in duckdb or just query them directly?
We query them directly in most cases with the httpfs plugin, but for hot paths we fetch them and cache them on disk locally.
Re: DuckDB – An in-process SQL OLAP database management system
#33Earlier quoted context omitted.
We use DuckDB extensively where I work ( https://watershed.com ), the primary way we're using it is to query Parquet formatted files stored in GCS, and we have some machinery to make that doable on demand for reporting and analysis "online" queries.
Storing data in Parquet files and querying via DuckDB is fast and kind of magical.
Shockingly fast and nice and having the intermediate files be immutable is super nice.
Re: DuckDB – An in-process SQL OLAP database management system
#34Our database is heroku postgresql database. What's the best way to get this working with DuckDB? I see there's a postgresql connector but I'm not totally following how to deploy it. Would I just spin up a dyno with the docker image / custom build pack and connect it to the DB?
Re: DuckDB – An in-process SQL OLAP database management system
#35Earlier quoted context omitted.
As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?
This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/
The blog post doesn't really make a comparison between DuckDB and data frame libraries. It mentions that the DuckDB Python bindings can interoperate with Pandas, but it doesn't really explain why you would use DuckDB instead of Pandas, or Polars (which is both faster and more portable than Pandas).
Re: DuckDB – An in-process SQL OLAP database management system
#36Earlier quoted context omitted.
As far as I can tell, DuckDB is an alternative to "data frame" libraries like Data.table, Polars, Pandas, etc. Is that the case? What makes DuckDB a better choice than, say, Polars?
It’s a drop in alternative to SQLite that’s column-oriented/OLAP. I’ve been profiling entire projects in production switching between SQLite and duckdb (no clear conclusions yet)
I suppose that leads to a broader question: when should you use an in-memory database, and when should you use a data frame library? The distinction between the two seems to be getting blurry (which maybe is a good thing).
Re: DuckDB – An in-process SQL OLAP database management system
#37DuckDB is terrific. I'm bullish on its potential for simplifying many big data pipelines. Particularly, it's plausible that DuckDB + Parquet could be used on a large SMP machine (32+ cores and 128GB+ memory) to deal with data munging for 100s of gigabytes to several terabytes, all from SQL, without dealing with Hadoop, Spark, Ray, etc. I have successfully used DuckDB like above for preparing an ML dataset from about…
I'd love to hear any real world experiences of anyone who's tried to run jobs that would usually require a spark cluster on a single machine with loads of cores and memory. How big can you go, and how does speed compare to Spark? (I'm guessing significantly faster from my experience using Duckdb on smaller machines)
I have a single machine EC2 instance with 32 cores and 240GB memory and about 200 GB of partitioned Parquet files. I use DuckDB and Python with complex SQL (window functions, inequality joins, quantile functions etc) to extract data from this data.
Because it’s a single machine (no distributed cluster) DuckDB can heavily parallelize and vectorize. I don’t know if I can give you perf numbers but complex analytic queries over the entire dataset regularly finish in 1-2 mins (not scientific since I’m not telling what kinds of queries I’m running).
I’ve used Spark SQL and DuckDB overall is just more ergonomic, less boilerplate and is much faster since it is so lightweight.
Granted DuckDB can only process data on one machine (whereas Spark can scale up indefinitely by adding machines) but most data sets I work with fit on a single beefy machine.
Distributed computing — most of the time, you ain’t gonna need it.
It’s like StackOverflow: it serves 2B requests a month but only runs on a few on-prem servers. Most people think this is impossible but you can actually do a lot with very few machines if you’re smart about it. Same with data. Big data is overrated.
Re: DuckDB – An in-process SQL OLAP database management system
#38Earlier quoted context omitted.
Yes using it in production - Stateless and ephemeral. For sure there’s a learning curve.
I see. Would it be fair to say you treat it almost like Pandas, except that it has a lower memory footprint since data is written to disk instead of memory. IE you use it for on the fly analysis of large frames of data, not like more traditional database/datawarehouse?
I'm not sure they're saying that.
BTW, your questions are exactly those that I've been ask over the last few months, but also with a lot of focus over the last few days. Still learning as much as I can so the following might not be true.
For what it's worth, there's a difference between using duckdb to query a set of files vs loading a bunch of files in to a table. But once the data has been loaded into a table it can be backed up as a duckdb db file.
Therefore it might be more performant to preprocess duckdb db files (perhaps a process that works in conjunction with whatever manages your external tables) and load these db files into duckdb as needed (on the fly analysis) instead of loading datafiles into duckdb, transforming and CTAS every time.
https://duckdb.org/docs/sql/statements/attach
Of course all of this might be introducing more latency esp if you're trying to do NRT analytics.
I assume you could partition your data into multiple db files similar to how you would probably do it with your data files (managing external tables).
Re: DuckDB – An in-process SQL OLAP database management system
#39Anyone tried using the Go driver? It has a few open bugs:
https://github.com/marcboeker/go-duckdb/issues
And a third-party effort, only:
Re: DuckDB – An in-process SQL OLAP database management system
#40Isn’t SQLite adding features for analytic queries that should take the wind out of the sails of duckdb?