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

Why DuckDB is my first choice for data processing

robinlinacre.com

Post reply on HN
Print view View original

31–40 of 124 posts

Re: Why DuckDB is my first choice for data processing

#31

Post by nchagnet »

s-a-p wrote: post #12

"making DuckDB potentially a suitable replacement for lakehouse formats such as Iceberg or Delta lake for medium scale data" > I'm a Data Engineering noob, but DuckDB alone doesn't do metadata & catalog management, which is why they've also introduce DuckLake. Related question, curious as to your experience with DuckLake if you've used it. I'm currently setting up s3 + Iceberg + duckDB for my company (startup) and wa…

We're using ducklake with data storage on Google cloud storage and the catalog inside a postgres database and it's a breeze! It may not be the most mature product, but it's definitely a good setup for small to medium applications which still require a data lake.

Re: Why DuckDB is my first choice for data processing

#32

Post by chaps »

RobinL wrote: post #28
noo_u wrote: post #15

I'd say the author's thoughts are valid for basic data processing. Outside of that, most of claims in this article, such as: "We're moving towards a simpler world where most tabular data can be processed on a single large machine1 and the era of clusters is coming to an end for all but the largest datasets." become very debatable. Depending on how you want to pivot/ scale/augment your data, even datasets that seeming…

Author here. Re: 'SQL should be the first option considered', there are certainly advantages to other dataframe APIs like pandas or polars, and arguably any one is better in the moment than SQL. At the moment Polars is ascendent and it's a high quality API. But the problem is the ecosystem hasn't standardised on any of them, and it's annoying to have to rewrite pipelines from one dataframe API. I also agree you're go…

Yeah, my experiences match yours and I very, very much work with messy data (FOIA data), though I use postgres instead of duckdb.

Most of the datasets I work with are indeed Thing about SQL is that it can be taken just about anywhere, so the time spent improving your SQL skills is almost always well worth it. R and pandas much less so.

Re: Why DuckDB is my first choice for data processing

#33

Post by noo_u »

RobinL wrote: post #28
noo_u wrote: post #15

I'd say the author's thoughts are valid for basic data processing. Outside of that, most of claims in this article, such as: "We're moving towards a simpler world where most tabular data can be processed on a single large machine1 and the era of clusters is coming to an end for all but the largest datasets." become very debatable. Depending on how you want to pivot/ scale/augment your data, even datasets that seeming…

Author here. Re: 'SQL should be the first option considered', there are certainly advantages to other dataframe APIs like pandas or polars, and arguably any one is better in the moment than SQL. At the moment Polars is ascendent and it's a high quality API. But the problem is the ecosystem hasn't standardised on any of them, and it's annoying to have to rewrite pipelines from one dataframe API. I also agree you're go…

I think both of us are ultimately wary of using the wrong tool for the job.

I see your point, even though my experience has been somewhat the opposite. E.g. a pipeline that used to work fast enough/at all up until some point in time because the scale of the data or requirements allowed it. Then some subset of these conditions changes, the pipeline cannot meet them, and one has to reverse engineer obscure SQL views/stored procedures/plugins, and migrate the whole thing to python or some compiled language.

I work with high density signal data now, and my SQL knowledge occupies the "temporary solution" part of my brain for the most part.

Re: Why DuckDB is my first choice for data processing

#35

Post by gofreddygo »

chaps wrote: post #32
RobinL wrote: post #28

Earlier quoted context omitted.

Author here. Re: 'SQL should be the first option considered', there are certainly advantages to other dataframe APIs like pandas or polars, and arguably any one is better in the moment than SQL. At the moment Polars is ascendent and it's a high quality API. But the problem is the ecosystem hasn't standardised on any of them, and it's annoying to have to rewrite pipelines from one dataframe API. I also agree you're go…

Yeah, my experiences match yours and I very, very much work with messy data (FOIA data), though I use postgres instead of duckdb. Most of the datasets I work with are indeed Thing about SQL is that it can be taken just about anywhere, so the time spent improving your SQL skills is almost always well worth it. R and pandas much less so.

I advocated for a SQL solution at work this week and it seems to have worked. My boss is wary of the old school style SQL databases with their constraints and just being a pain to work with. As a developer, these pains aren't too hard to get used to or automate or document away for me and never understood the undue dislike of sql.

The fact that I can use sqlite / local sql db for all kinds of development and reliably use the same code (with minor updates) in the cloud hosted solution is such a huge benefit that it undermines anything else that any other solution has to offer. I'm excited about the sql stuff I learned over 10 years ago being of of great use to me in the coming months.

The last product I worked heavily on used a nosql database and it worked fine till you start tweak it just a little bit - split entities, convert data types or update ids. Most of the data access layer logic dealt with conversion between data coming in from the database and the guardrails to keep the data integrity in check while interacting with the application models. To me this is something so obviously solved years ago with a few lines of constraints. Moving over to sql was totally impossible. Learned my lesson, advocated hard for sql. Hoping for better outcomes.

Re: Why DuckDB is my first choice for data processing

#36

Post by oulipo2 »

mrtimo wrote: post #19

What I love about duckdb: -- Support for .parquet, .json, .csv (note: Spotify listening history comes in a multiple .json files, something fun to play with). -- Support for glob reading, like: select * from 'tsa20*.csv' - so you can read hundreds of files (any type of file!) as if they were one file. -- if the files don't have the same schema, union_by_name is amazing. -- The .csv parser is amazing. Auto assigns type…

Malloy and PRQL (https://prql-lang.org/book/) are quite cool

Re: Why DuckDB is my first choice for data processing

#37

Post by newusertoday »

mrtimo wrote: post #19

What I love about duckdb: -- Support for .parquet, .json, .csv (note: Spotify listening history comes in a multiple .json files, something fun to play with). -- Support for glob reading, like: select * from 'tsa20*.csv' - so you can read hundreds of files (any type of file!) as if they were one file. -- if the files don't have the same schema, union_by_name is amazing. -- The .csv parser is amazing. Auto assigns type…

its 32 mb uncompressed and around 6MB compressed, its not that small https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/

it is also difficult to customize as compared to sqlite so for example if you want to use your own parser for csv than it becomes hard.

But yes it provides lot of convenience out of the box as you have already listed.

Re: Why DuckDB is my first choice for data processing

#38

Post by efromvt »

DuckDB can't get enough love! Beyond being a really performant database, being so friendly [sql and devx] is really underrated and hard to pull off well and is key to it being so fun - you get a compounding ecosystem because it's so easy to get started. Hoping that they can manage to keep it vibrant without it slowing down the pace of innovation at all.

The web/WASM integration is also fabulous. Looking forward to more "small engines" getting into that space to provide some competition and keep pushing it forward.

Re: Why DuckDB is my first choice for data processing

#39

Post by efromvt »

canadiantim wrote: post #17
smithclay wrote: post #6

Agree with the author, will add: duckdb is an extremely compelling choice if you’re a developer and want to embed analytics in your app (which can also run in a web browser with wasm!) Think this opens up a lot of interesting possibilities like more powerful analytics notebooks like marimo ( https://marimo.io/ ) … and that’s just one example of many.

The wasm is pretty heavy data-wise tho, I’m hoping eventually it’ll be lighter for easier loading on not so good devices.

I'd really love a minimalist version, I'm not sure how small it's feasible for them to shrink it. As long as it doesn't get bigger and devices keep getting faster, I suppose?

Re: Why DuckDB is my first choice for data processing

#40

Post by chaps »

gofreddygo wrote: post #35
chaps wrote: post #32

Earlier quoted context omitted.

Yeah, my experiences match yours and I very, very much work with messy data (FOIA data), though I use postgres instead of duckdb. Most of the datasets I work with are indeed Thing about SQL is that it can be taken just about anywhere, so the time spent improving your SQL skills is almost always well worth it. R and pandas much less so.

I advocated for a SQL solution at work this week and it seems to have worked. My boss is wary of the old school style SQL databases with their constraints and just being a pain to work with. As a developer, these pains aren't too hard to get used to or automate or document away for me and never understood the undue dislike of sql. The fact that I can use sqlite / local sql db for all kinds of development and reliably…

I totally understand the apprehension towards SQL. It's esoteric as all hell and it tends to be managed by DBAs who you really only go to whenever there's a problem. Organizationally it's simpler to just do stuff in-memory without having to fiddle around with databases.
Post reply on HN