4 ms·

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 choi

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?

corford 4y ago
This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/ https://motherduck.com/blog/six-reasons-duckdb-slaps/
- nerdponx 4y ago
  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).
  - simonw 4y ago
    Don't Polars and Pandas both require your entire data to fit in memory?
    - nerdponx 4y ago
      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.
      - jcheng 4y ago
        I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html https://pandas.pydata.org/docs/user_guide/scale.html
        nerdponx 4y ago
        Yes, thank you, that was a typo.
      - corford 4y ago
        Apparently duckdb paired with arrow lets you work performatively on bigger than memory parquet data (I haven't tried this though)
        riezebos 4y ago
        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-data https://github.com/pola-rs/polars#handles-larger-than-ram-da...
syntaxfree 4y ago
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)
- nerdponx 4y ago
  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).
  - IanCal 4y ago
    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.
    - wenc 4y ago
      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.
benjaminwootton 4y ago
SQL is easier and more natural to work with than Pandas.
- nojito 4y ago
  How would you do df.T in sql?
  - wenc 4y ago
    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.
    - nojito 4y ago
      >DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other. Transforming into a pandas df isn't zero copy. UNPIVOT and PIVOT are quite verbose compared to df.T.
- brahbrah 4y ago
  Sometimes yes and sometimes no. For example if you had price data and you wanted to bump prices up a dollar in 2020 how would you do that in sql? In pandas it’s: prices_df.loc['2020'] += 1 If you had a temperature forecast and you wanted to add the expected temperature miss to them, how would you do that on sql? In pandas it’s: temps_df + expected_miss_df
brahbrah 4y ago
Pandas is in a separate category from all of these, including polars. If you were to say “pandas in long format only” then yes that would be correct, but the power of pandas comes in its ability to work in a long relational or wide ndarray style. Pandas was originally written to replace excel in financial/econometric modeling, not as a replacement for sql. Models written solely in the long relational style are near unmaintainable for constantly evolving models with hundreds of data sources and thousands of interactions being developed and tuned by teams of analysts and engineers. For example, this is how some basic operations would look in pandas. Bump prices in 2020 up $1: prices_df.loc['2020'] += 1 Add expected temperature offsets to base temperature forecast: temp_df + offset_df Now imagine thousands of such operations, and you can see the necessity of pandas in models like this.

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?

corford 4y ago

This blog post offers a nice summary: https://motherduck.com/blog/six-reasons-duckdb-slaps/ https://motherduck.com/blog/six-reasons-duckdb-slaps/

nerdponx 4y ago
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).
- simonw 4y ago
  Don't Polars and Pandas both require your entire data to fit in memory?
  - nerdponx 4y ago
    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.
    - jcheng 4y ago
      I think you mean that Pandas does require your entire data to fit in memory? https://pandas.pydata.org/docs/user_guide/scale.html https://pandas.pydata.org/docs/user_guide/scale.html
      - nerdponx 4y ago
        Yes, thank you, that was a typo.
    - corford 4y ago
      Apparently duckdb paired with arrow lets you work performatively on bigger than memory parquet data (I haven't tried this though)
      - riezebos 4y ago
        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-data https://github.com/pola-rs/polars#handles-larger-than-ram-da...

syntaxfree 4y ago

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)

nerdponx 4y ago
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).
- IanCal 4y ago
  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.
  - wenc 4y ago
    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.

benjaminwootton 4y ago

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

nojito 4y ago
How would you do df.T in sql?
- wenc 4y ago
  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.
  - nojito 4y ago
    >DuckDB produces Pandas dataframes, so you would just do df.T. No need to choose between one the other. Transforming into a pandas df isn't zero copy. UNPIVOT and PIVOT are quite verbose compared to df.T.
brahbrah 4y ago
Sometimes yes and sometimes no. For example if you had price data and you wanted to bump prices up a dollar in 2020 how would you do that in sql? In pandas it’s: prices_df.loc['2020'] += 1 If you had a temperature forecast and you wanted to add the expected temperature miss to them, how would you do that on sql? In pandas it’s: temps_df + expected_miss_df

brahbrah 4y ago

Pandas is in a separate category from all of these, including polars. If you were to say “pandas in long format only” then yes that would be correct, but the power of pandas comes in its ability to work in a long relational or wide ndarray style. Pandas was originally written to replace excel in financial/econometric modeling, not as a replacement for sql. Models written solely in the long relational style are near unmaintainable for constantly evolving models with hundreds of data sources and thousands of interactions being developed and tuned by teams of analysts and engineers. For example, this is how some basic operations would look in pandas. Bump prices in 2020 up $1: prices_df.loc['2020'] += 1 Add expected temperature offsets to base temperature forecast: temp_df + offset_df Now imagine thousands of such operations, and you can see the necessity of pandas in models like this.