Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

DuckDB – An in-process SQL OLAP database management system

duckdb.org

Post reply on HN
Print view View original

71–80 of 104 posts

Re: DuckDB – An in-process SQL OLAP database management system

#71

Post by nerdponx »

simonw wrote: post #63
nerdponx wrote: post #35

Earlier quoted context omitted.

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).

Don't Polars and Pandas both require your entire data to fit in memory?

Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.

Re: DuckDB – An in-process SQL OLAP database management system

#72

Post by wenc »

IanCal wrote: post #44
nerdponx wrote: post #36

Earlier quoted context omitted.

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).

Very blurry. The answer now is just "whichever is easier for the small part of the task right now". Since duckdb happily talks arrow, you can use pandas for part of it, quickly do some SQL where that is easier (with no data copying) then switch back to pandas for something. You don't really have to choose which one to use any more.

Exactly. In my DuckDB workflow I use Pandas data frames and DuckDB queries interchangeably.

  import duckdb as db
  import pandas as pd 

  df = pd.read_excel(“z.xlsx”)
  df2 = db.query(“select * from df join ‘s3://bucket/a.parquet’ b on df.col b.col”).df()
  df3 = df2.col.apply(lambda x: x)
DuckDB can refer any Pandas data frame in the namespace as a SQL object. You can query across Parquet, CSV and Pandas data frames seamlessly.

Need to join Excel with Parquet with CSV? No problem. You can do it all within DuckDB.

Re: DuckDB – An in-process SQL OLAP database management system

#73

Post by SnowflakeOnIce »

RobinL wrote: post #13
SnowflakeOnIce wrote: post #12

DuckDB 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)

My experience with a modest machine and a ~100GB dataset was that DuckDB was significantly easier to use and much faster (20x) than Ray Data. Have not compared directly with Spark.

There was no cluster to set up or administer using DuckDB.

Re: DuckDB – An in-process SQL OLAP database management system

#74

Post by jcheng »

nerdponx wrote: post #71
simonw wrote: post #63

Earlier quoted context omitted.

Don't Polars and Pandas both require your entire data to fit in memory?

Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.

I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html

Re: DuckDB – An in-process SQL OLAP database management system

#75

Post by camgunz »

89vision wrote: post #59
benjaminwootton wrote: post #55

Earlier quoted context omitted.

DuckDB is a relational OLAP store. If you want to do transformations on relational data using SQL then I think nowadays you would look at the modern data stack and do it with DBT. If you have genuinely big and unstructured data then of course you need a cluster and would reach for Spark. If you have smallish data then maybe DuckDB has a role because working with SQL is nicer than Pandas. But a lot of time you actuall…

> working with SQL is nicer than Pandas Really? I prefer working with dataframe apis. You get a nice sql-like paradigm plus all the control structures of the runtime.

Databases are just much, much faster than Pandas, and that's before you start factoring the extraction and loading of data. I treat Pandas as a last resort when I can't do something in SQL, generally this is something like integrating with external services or running recordlinkage.

Re: DuckDB – An in-process SQL OLAP database management system

#76

Post by nerdponx »

jcheng wrote: post #74
nerdponx wrote: post #71

Earlier quoted context omitted.

Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.

I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html

Yes, thank you, that was a typo.

Re: DuckDB – An in-process SQL OLAP database management system

#78

Post by corford »

nerdponx wrote: post #71
simonw wrote: post #63

Earlier quoted context omitted.

Don't Polars and Pandas both require your entire data to fit in memory?

Pandas doesn't. Polars I think has some lazy-loading capability, but it's not the default mode of operation and I don't think it supports all features. If DuckDB doesn't, then that's a big advantage.

Apparently duckdb paired with arrow lets you work performatively on bigger than memory parquet data (I haven't tried this though)

Re: DuckDB – An in-process SQL OLAP database management system

#80

Post by adgjlsfhk1 »

RobinL wrote: post #13
SnowflakeOnIce wrote: post #12

DuckDB 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)

spark is just a tool to let you take a computation that would run in an hour on your laptop if coded properly and send it to a server with 1000 cores where it runs in 2 hours.
Post reply on HN