5 ms·

Why is DuckDB so popular when one can use Python + Pandas? Better perf + SQL is that mostly it?

RobinL 3mo ago
I wrote a blog post a while back to address this question here: https://www.robinlinacre.com/recommend_duckdb/ https://www.robinlinacre.com/recommend_duckdb/
paytonjjones 3mo ago
Pandas has lots and lots of problems. Performance is definitely one of them, but it also has inconsistent and duplicated methods, inconsistent defaults (e.g. some methods are inplace by default), copy by reference issues, I could go on. It was an early winner in an extremely popular language. That's really the main thing going for it, but alternatives have been a long time coming.
estetlinus 3mo ago
Why would you prefer Python and Pandas over good old SQL? Pandas is so verbose and hard to debug, most of the times struggle to be performant on small datasets. SQL has been around since the dawn of databases. I am happy to see a trend away from pandas.
refactor_master 3mo ago
The better question is, why is DuckDB so popular when one can use Polars which has a sane, lintable, typesafe API compared to the mess that is SQL: WITH lagged AS ( SELECT *, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time FROM events ), sessions AS ( SELECT *, SUM(COALESCE((date_diff('minute', prev_time, event_time) > 30)::INT, 1)) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id FROM lagged ) SELECT user_id, session_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end, COUNT(*) AS event_count FROM sessions GROUP BY ALL ORDER BY user_id, session_start; vs result = ( df.sort(["user_id", "event_time"]) .with_columns( session_id=( pl.when(pl.col("event_time").diff().is_null()) .then(1) .when(pl.col("event_time").diff().dt.total_minutes() > 30) .then(1) .otherwise(0) .cum_sum() .over("user_id") ) ) .group_by(["user_id", "session_id"]) .agg( session_start=pl.col("event_time").min(), session_end=pl.col("event_time").max(), event_count=pl.col("event_time").count(), ) .sort(["user_id", "session_start"]) )
- porridgeraisin 3mo ago
  I understand the linting aspect but not gonna lie I understood the first one immediately way more than the 2nd one due to knowing SQL well.
- homebessguy 3mo ago
  "Languages come and go, but SQL is forever"
- brikym 3mo ago
  Polars typesafe? It doesn't show you any errors until runtime right? Kusto query language is the best I've seen at type safety and I wish open source DBs would steal some ideas from it.
  - refactor_master 3mo ago
    Yes, the most of its safety comes after you compile the graph. In that sense, it's "compile-safe" strictly speaking, which puts it on par with DuckDBs validation step. But you don't need to load any data to validate the execution graph (as opposed to Pandas).
- IshKebab 3mo ago
  That does look nicer if you have a Parquet file and want to analyze it. But DuckDB is also a database - if you want a persistent, reliable and mutable data store I don't think Polars would be suitable would it? (Genuine question - you sound like an expert and I'm not.)
  - tomjakubowski 3mo ago
    The other thing DuckDB does quite well, and which is out of scope for polars, is its keeping of an absolute zoo of external data sources you can query with SQL from the same database client. It's an excellent data warehousing tool.
- coldtea 3mo ago
  Precisely to avoid the custom NIH Polars API, and use SQL which works everywhere (yes, inconsistencies aside).
tannenfreund87 3mo ago
Because I can just write SQL to access all my data. The data comes from CSVs, SQLite, MySQL, Postgres, ... I can write SQL everywhere. I write SQL from within my R scripts, I write SQL from within my Python scripts. SQL is THE language for querying databases, others came and went, SQL will stay with us for the next decades. Even the dbplyr people say (parapfhrased): If you can't express what you want with the tidyverse, just write your SQL and load the result as a dataframe.

I wrote a blog post a while back to address this question here: https://www.robinlinacre.com/recommend_duckdb/ https://www.robinlinacre.com/recommend_duckdb/

paytonjjones 3mo ago

Pandas has lots and lots of problems. Performance is definitely one of them, but it also has inconsistent and duplicated methods, inconsistent defaults (e.g. some methods are inplace by default), copy by reference issues, I could go on. It was an early winner in an extremely popular language. That's really the main thing going for it, but alternatives have been a long time coming.

estetlinus 3mo ago

Why would you prefer Python and Pandas over good old SQL? Pandas is so verbose and hard to debug, most of the times struggle to be performant on small datasets. SQL has been around since the dawn of databases. I am happy to see a trend away from pandas.

refactor_master 3mo ago

The better question is, why is DuckDB so popular when one can use Polars which has a sane, lintable, typesafe API compared to the mess that is SQL: WITH lagged AS ( SELECT *, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time FROM events ), sessions AS ( SELECT *, SUM(COALESCE((date_diff('minute', prev_time, event_time) > 30)::INT, 1)) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id FROM lagged ) SELECT user_id, session_id, MIN(event_time) AS session_start, MAX(event_time) AS session_end, COUNT(*) AS event_count FROM sessions GROUP BY ALL ORDER BY user_id, session_start; vs result = ( df.sort(["user_id", "event_time"]) .with_columns( session_id=( pl.when(pl.col("event_time").diff().is_null()) .then(1) .when(pl.col("event_time").diff().dt.total_minutes() > 30) .then(1) .otherwise(0) .cum_sum() .over("user_id") ) ) .group_by(["user_id", "session_id"]) .agg( session_start=pl.col("event_time").min(), session_end=pl.col("event_time").max(), event_count=pl.col("event_time").count(), ) .sort(["user_id", "session_start"]) )

porridgeraisin 3mo ago
I understand the linting aspect but not gonna lie I understood the first one immediately way more than the 2nd one due to knowing SQL well.
homebessguy 3mo ago
"Languages come and go, but SQL is forever"
brikym 3mo ago
Polars typesafe? It doesn't show you any errors until runtime right? Kusto query language is the best I've seen at type safety and I wish open source DBs would steal some ideas from it.
- refactor_master 3mo ago
  Yes, the most of its safety comes after you compile the graph. In that sense, it's "compile-safe" strictly speaking, which puts it on par with DuckDBs validation step. But you don't need to load any data to validate the execution graph (as opposed to Pandas).
IshKebab 3mo ago
That does look nicer if you have a Parquet file and want to analyze it. But DuckDB is also a database - if you want a persistent, reliable and mutable data store I don't think Polars would be suitable would it? (Genuine question - you sound like an expert and I'm not.)
- tomjakubowski 3mo ago
  The other thing DuckDB does quite well, and which is out of scope for polars, is its keeping of an absolute zoo of external data sources you can query with SQL from the same database client. It's an excellent data warehousing tool.
coldtea 3mo ago
Precisely to avoid the custom NIH Polars API, and use SQL which works everywhere (yes, inconsistencies aside).

tannenfreund87 3mo ago

Because I can just write SQL to access all my data. The data comes from CSVs, SQLite, MySQL, Postgres, ... I can write SQL everywhere. I write SQL from within my R scripts, I write SQL from within my Python scripts. SQL is THE language for querying databases, others came and went, SQL will stay with us for the next decades. Even the dbplyr people say (parapfhrased): If you can't express what you want with the tidyverse, just write your SQL and load the result as a dataframe.