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

DuckDB over Pandas/Polars

pgrs.net

Post reply on HN
Print view View original

11–20 of 43 posts

Re: DuckDB over Pandas/Polars

#11

Post by signal11 »

nomilk wrote: post #6
ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else? I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

I’ve tried reading streamed parquet via PyArrow with Duck, and it’s been pretty promising. Depending on the query, you won’t need to download everything off HTTP.

Re: DuckDB over Pandas/Polars

#12

Post by hipadev23 »

minimaxir wrote: post #4

The test case of a simple aggregation is a good example of an important data science skill knowing when and here to use a given tool, and that there is no one right answer for all cases. Although it's worth noting that DuckDB and polars are comparable performance-wise for aggregation (DuckDB slightly faster: https://duckdblabs.github.io/db-benchmark/ ). For my cases with polars and function piping, certain aspects of…

The real winner is going to be a framework that, during dev, transparently materializes CTEs to temporary tables so you can iterate on them like you’re saying, while continuing to harness SQL for the end product.

Re: DuckDB over Pandas/Polars

#13

Post by swasheck »

nomilk wrote: post #6
ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else? I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

we use partitioned parquet files in s3. we use a csv in the bucket root to track the files. i’m sure there’s a better way but for now the 2tb of data are stored cheaply and we get fast reads by only reading the partitions we need to read.

Re: DuckDB over Pandas/Polars

#14

Post by wanderingmind »

My biggest issue with DuckDB is its not willing to implement edits to blob storages which allow edits (Azure). Having common object/blob storages that can be interacted and operated by multiple process will make it much more amenable to many data science driven workflows.

Re: DuckDB over Pandas/Polars

#15

Post by ramraj07 »

nomilk wrote: post #6
ramraj07 wrote: post #5

I am just using duckdb on a 3TB dataset in a beefy ec2, and am pleasantly surprised at its performance on such a large table. I had to do some sharding to be sure but am able to match performance of snowflake or other cluster based systems using this single machine instance. To clarify Clickhouse will likely match this performance as well, but doing things on a single machines look sexier to me than it ever did in de…

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else? I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

Local drives. DONT USE EBS! you’ll incur a huge IO charge. You have to choose instances with attached nvme storage which means one of the storage optimized instances.

Reading the data off s3 will mean you will be slower than offerings like snowflake. Snowflake has optimized the crap out of doing analytics in s3, so you can’t beat it with something as simple as duckdb.

Importantly you need the data in some distributed format like parquet or split csv. Otherwise duckdb can’t read it in parallel.

Re: DuckDB over Pandas/Polars

#16

Post by ramraj07 »

DiscreteTom wrote: post #10
nomilk wrote: post #6

Earlier quoted context omitted.

Where does your data reside, is it on an attached EBS volume, or in S3, or somewhere else? I had some spare time and tinkered with duckdb with a 70GB dataset, but just getting the 70GB on to the EC2 took hours. Would be pretty rocking if duckdb team could somehow set up a ~1TB sized demo that anyone can setup and try for themselves in, say, under an hour.

I tried to spread large dataset into thousands of files on S3 and use StepFunctions Distributed Map to launch thousands of Lambda instances to process those files in parallel, using DuckDB (or other libs) in Lambda. The parallel loading and processing is way faster than doing this in a single big EC2 instance.

Lambda isn’t infinitely parallel. I thought it doesn’t do more than 100 parallel runners? I4i.metal has 96 cores and can be faster than that.

Re: DuckDB over Pandas/Polars

#17

Post by halfcat »

lopatin wrote: post #9

I think the competition for the future is between DuckDB and Polars. Will we stick with the DataFrame model, made feasible by Polars's lazy execution, or will we go with in-process SQL a la DuckDB? Personally I've been using DuckDB because I already know SQL (and DuckDB provides persistence if I need it) and don't want to learn a new DataFrame DSL but I'd love to hear other the experience of other people.

I really like the dataframe approach. I think it’s because I like REPL-driven-development where I can drop into the REPL and work through how to transform the data interactively.

To be fair, it can nearly always be done in SQL also (unless it’s ML or some Python-specific thing like that), but the SQL with nested queries and numerous CTEs is harder for me to wrap my brain around.

If I were betting, I’d pick DuckDB, because DuckDB seems more able to implement something Polars-like, than Polars is to implement something DuckDB-like.

Re: DuckDB over Pandas/Polars

#18

Post by ok_computer »

lopatin wrote: post #9

I think the competition for the future is between DuckDB and Polars. Will we stick with the DataFrame model, made feasible by Polars's lazy execution, or will we go with in-process SQL a la DuckDB? Personally I've been using DuckDB because I already know SQL (and DuckDB provides persistence if I need it) and don't want to learn a new DataFrame DSL but I'd love to hear other the experience of other people.

I’d recommend using the polars SQL context manager if wanting to defer learning how to do everything through their API. The API is a big enough shift from pandas it took me a minute to figure out but I really enjoy having the choice to stay in dataframe methods or switch to SQL only transformations. It has global state too if that’s needed. I like that it isn’t a RDBMS but provides all of the SQL I use.

https://docs.pola.rs/api/python/stable/reference/sql/python_...

https://docs.pola.rs/api/python/stable/reference/sql/python_...

Re: DuckDB over Pandas/Polars

#19

Post by halfcat »

hipadev23 wrote: post #12
minimaxir wrote: post #4

The test case of a simple aggregation is a good example of an important data science skill knowing when and here to use a given tool, and that there is no one right answer for all cases. Although it's worth noting that DuckDB and polars are comparable performance-wise for aggregation (DuckDB slightly faster: https://duckdblabs.github.io/db-benchmark/ ). For my cases with polars and function piping, certain aspects of…

The real winner is going to be a framework that, during dev, transparently materializes CTEs to temporary tables so you can iterate on them like you’re saying, while continuing to harness SQL for the end product.

Do dbt or SQLMesh do this, or if not can you say more about what you’re envisioning?

Re: DuckDB over Pandas/Polars

#20

Post by rubinelli »

lopatin wrote: post #9

I think the competition for the future is between DuckDB and Polars. Will we stick with the DataFrame model, made feasible by Polars's lazy execution, or will we go with in-process SQL a la DuckDB? Personally I've been using DuckDB because I already know SQL (and DuckDB provides persistence if I need it) and don't want to learn a new DataFrame DSL but I'd love to hear other the experience of other people.

I've written a fair bit of PySpark code and Polars's syntax feels fairly similar, but it also offers a limited SQL dialect.
Post reply on HN