Hacker News Reader

Comment by nomilk | Hacker News Reader

Do any data scientists here use duckdb daily? Keen to hear your experiences and comparisons to other tools you used before it.

I love tools that make life simpler. I've been toying with the idea of storing 1TB of data in S3 and querying it using duckdb on an EC2. That's really old/boring infrastructure, but is hugely appealing to me, since it's so much simpler than than what I currently use.

Would love to hear of others' experiences with duckdb.

View on HN

We just wrote a post[0] very similar to what you're thinking. Let me know if you have any questions.

0 - https://www.definite.app/blog/duckdb-datawarehouse

Storing as parquet, and using hive path partitioning, you can get passable batch performance assuming you queries are not mostly scans and aggregates across large portions of the data. On the order of ten seconds for regex matching on columns for example.

I thought it was a great way to give analysts direct access to data to do adhoc queries while they were getting familiar with the data.

We use it for simple use-cases and experimentation, especially because it works so well with various data formats and polars. Personally, I prefer to maintain proper SQL queries against duckdb including re-usable views, and then use polars for the remaining fiddling/exploration.

In combination with sqlalchemy, it is trivial to lift an app to other OLAP systems.

Wouldn't that be pretty slow? :) Why not store it on the EC2 instance directly? Or locally on your computer?

1 TB is .. not a lot of data .. I rent a server with 15 TB for ~50$ per month and buying a new 2TB disk is less than 100$.

DuckDB supports partial reading of Parquet files (also via HTTPS and S3) [1], so it can limit the scans to the required columns in the Parquet file. It can also perform filter pushdown, so querying data in S3 can be quite efficient.

Disclaimer: I work at DuckDB Labs.

[1] https://duckdb.org/docs/data/parquet/overview#partial-readin...

Does DuckDB supports partial reading of .duckdb files hosted externally?

oooh, cool

> Why not store it on the EC2 instance directly?

Since the data in S3 receives updates every few hours, querying it directly ensures queries are on up-to-date data, whereas creating a copy of the dataset on the EC2 would necessitate periodically checking for updates and moving the deltas across to the EC2 (not insurmountable; but complexity worth avoiding if possible).

ah, I see. I though you decided t put it there (also, what the sibling said makes it a better idea)

You should give us at MotherDuck a try for this use case.

(Co-founder)

I suggest you try using StarRocks to query the data lake directly. I know many large companies, including Tencent and Pinterest, are doing this. StarRocks has a truly mature vectorized execution engine and a robust distributed architecture. It can provide you with impressive query performance.

Reply on news.ycombinator.com