3 ms·

I'm a bit confused by the claim that duckdb doesn't support dataframes. This blog post suggests that it has been supported since 2021 and matches my experience

I'm a bit confused by the claim that duckdb doesn't support dataframes. This blog post suggests that it has been supported since 2021 and matches my experience. https://duckdb.org/2021/05/14/sql-on-pandas.html https://duckdb.org/2021/05/14/sql-on-pandas.html

antman 2y ago
Maybe they mean the other way around aka querying duckdb using dataframe queries instead of sql, which can be achieved through the ibis project
simicd 2y ago
From what I understood the article refers to the point that DuckDB doesn't provide its own dataframe API, meaning a way to express SQL queries in Python classes/functions. The link you shared shows how DuckDB can run SQL queries on a pandas dataframe (e.g. `duckdb.query("<SQL query>")`. The SQL query in this case is a string. A dataframe API would allow you to write it completely in Python. An example for this would be polars dataframes (`df.select(pl.col("...").alias("...")).filter(pl.col("...") > x)`). Dataframe APIs benefit from autocompletion, error handling, syntax highlighting, etc. that the SQL strings wouldn't. Please let me know if I missed something from the blog post you linked!
- adsharma 2y ago
  Your point about SQL strings vs more strongly typed DF APIs stands. However it's somewhat weakened by the possibility that some parts of the SQL string are resolved by the surrounding python context.
- adsharma 2y ago
  For non trivial queries I write them in a separate SQL file where I get the benefit of syntax highlighting, auto formatting and error checks. There may be another benefit: a lot of LLMs are getting good at how do I do X in Duckdb.
- mwc360 2y ago
  Author here: that’s exactly what I was trying to communicate but you said it better :)
  - cmdlineluser 2y ago
    There is a Spark API[1] being built using their Relational API[2]. Progress is being tracked on Github Discussions[3]. [1]: https://duckdb.org/docs/api/python/spark_api.html https://duckdb.org/docs/api/python/spark_api.html [2]: https://duckdb.org/docs/api/python/relational_api.html https://duckdb.org/docs/api/python/relational_api.html [3]: https://github.com/duckdb/duckdb/discussions/14525 https://github.com/duckdb/duckdb/discussions/14525
    - mwc360 2y ago
      Very cool! This seems like fantastic functionality and would make it super easy to migrate small Spark workloads to DuckDB :)

antman 2y ago

Maybe they mean the other way around aka querying duckdb using dataframe queries instead of sql, which can be achieved through the ibis project

simicd 2y ago

From what I understood the article refers to the point that DuckDB doesn't provide its own dataframe API, meaning a way to express SQL queries in Python classes/functions. The link you shared shows how DuckDB can run SQL queries on a pandas dataframe (e.g. `duckdb.query("<SQL query>")`. The SQL query in this case is a string. A dataframe API would allow you to write it completely in Python. An example for this would be polars dataframes (`df.select(pl.col("...").alias("...")).filter(pl.col("...") > x)`). Dataframe APIs benefit from autocompletion, error handling, syntax highlighting, etc. that the SQL strings wouldn't. Please let me know if I missed something from the blog post you linked!

adsharma 2y ago
Your point about SQL strings vs more strongly typed DF APIs stands. However it's somewhat weakened by the possibility that some parts of the SQL string are resolved by the surrounding python context.
adsharma 2y ago
For non trivial queries I write them in a separate SQL file where I get the benefit of syntax highlighting, auto formatting and error checks. There may be another benefit: a lot of LLMs are getting good at how do I do X in Duckdb.
mwc360 2y ago
Author here: that’s exactly what I was trying to communicate but you said it better :)
- cmdlineluser 2y ago
  There is a Spark API[1] being built using their Relational API[2]. Progress is being tracked on Github Discussions[3]. [1]: https://duckdb.org/docs/api/python/spark_api.html https://duckdb.org/docs/api/python/spark_api.html [2]: https://duckdb.org/docs/api/python/relational_api.html https://duckdb.org/docs/api/python/relational_api.html [3]: https://github.com/duckdb/duckdb/discussions/14525 https://github.com/duckdb/duckdb/discussions/14525
  - mwc360 2y ago
    Very cool! This seems like fantastic functionality and would make it super easy to migrate small Spark workloads to DuckDB :)