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

81–90 of 104 posts

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

#81

Post by jammycrisp »

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.

You might be interested in checking out Ibis (https://ibis-project.org/). It provides a dataframe-like API, abstracting over many common execution engines (duckdb, postgres, bigquery, spark, ...). Ibis wrapping duckdb has pretty much replaced pandas as my tool of choice for local data analysis. All the performance of duckdb with all the ergonomics of a dataframe API. (disclaimer: I contribute to Ibis for work).

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

#82

Post by benjaminwootton »

neeleshs wrote: post #65
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…

I am not sure I understand the first comment very well. Are you saying that instead of duckdb use modern data stack? Because DBT and DuckDB don't seem to contradict, but can work together. FWIW, I think the only important breakthrough in the "modern data stack" is really dbt. The rest, nothing modern about it

It’s more a comment on where the market is at rather than a recommendation. I’m not saying it is bad tech, but I don’t see it’s niche.

If you have a few billion records and you want to filter, join, aggregate them using SQL then DBT against an OLAP server solves that issue so well that it doesn’t leave much white space for DuckDB.

I mentioned modern data stack because when you have SaaS, low code, consumption based billing, open source etc then it treads even more on the DuckDB value prop. DuckDB would have been great if Oracle was my only choice, but when I have Snowflake and Clickhouse in the toolbox it is a tougher market for them to carve out a niche.

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

#83

Post by nojito »

benjaminwootton wrote: post #56
nerdponx wrote: post #22

Earlier 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?

SQL is easier and more natural to work with than Pandas.

How would you do df.T in sql?

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

#84

Post by hobs »

benjaminwootton wrote: post #82
neeleshs wrote: post #65

Earlier quoted context omitted.

I am not sure I understand the first comment very well. Are you saying that instead of duckdb use modern data stack? Because DBT and DuckDB don't seem to contradict, but can work together. FWIW, I think the only important breakthrough in the "modern data stack" is really dbt. The rest, nothing modern about it

It’s more a comment on where the market is at rather than a recommendation. I’m not saying it is bad tech, but I don’t see it’s niche. If you have a few billion records and you want to filter, join, aggregate them using SQL then DBT against an OLAP server solves that issue so well that it doesn’t leave much white space for DuckDB. I mentioned modern data stack because when you have SaaS, low code, consumption based b…

Also fwiw its always lowercase - dbt.

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

#85

Post by capkutay »

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…

reminds me of this blog on streaming data to Parquet files and running queries on data in the native format.

https://pedram.substack.com/p/streaming-data-pipelines-with-...

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

#86

Post by tkhattra »

aynyc wrote: post #77

I don't get this: * When to not use DuckDB: Multiple concurrent processes reading from a single writable database* So no concurrent reads? Or is it no concurrent reads while writing?

the latter - you can either have multiple concurrent readers or a single writer. see https://duckdb.org/faq.html#how-does-duckdb-handle-concurren....

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

#87

Post by RobinL »

camgunz wrote: post #75
89vision wrote: post #59

Earlier quoted context omitted.

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

If you're curious, I've written a FOSS record linkage library that executes everything as SQL. It supports multiple SQL backends including DuckDB and Spark for scale, and runs faster than most competitors because it's able to leverage the speed of these backends: https://github.com/moj-analytical-services/splink

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

#88

Post by je42 »

jammycrisp wrote: post #81
89vision wrote: post #59

Earlier quoted context omitted.

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

You might be interested in checking out Ibis ( https://ibis-project.org/ ). It provides a dataframe-like API, abstracting over many common execution engines (duckdb, postgres, bigquery, spark, ...). Ibis wrapping duckdb has pretty much replaced pandas as my tool of choice for local data analysis. All the performance of duckdb with all the ergonomics of a dataframe API. (disclaimer: I contribute to Ibis for work).

Interesting ! One newbie question, how does ibis differ from sqlalchemy ?

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

#89

Post by riezebos »

corford wrote: post #78
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.

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

Arrow seems to be the common denominator for larger than ram. Polars is completely built on arrow, so the same advantages should apply.

https://github.com/pola-rs/polars#handles-larger-than-ram-da...

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

#90

Post by wenc »

nojito wrote: post #83
benjaminwootton wrote: post #56

Earlier quoted context omitted.

SQL is easier and more natural to work with than Pandas.

How would you do df.T in sql?

df.T is a special Pandas dataframe transpose on the dataframe index and the columns.

DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other.

But to answer your original question, the SQL analogue to a transpose are PIVOT/UNPIVOT operations which are mathematically rotation operations on invariants (your dimensions). This makes them much more general than a transpose -- which are just rotation operations on the rows/cols. PIVOT/UNPIVOT work on non-square data and allow you to specify different types of aggregations. PIVOT/UNPIVOT keywords are not yet implemented in DuckDB but are on the roadmap if I'm not mistaken.

Post reply on HN