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

81–90 of 124 posts

Re: Why DuckDB is my first choice for data processing

#81

Post by HowardStark »

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…

Been playing around with Clickhouse a lot recently and have had a great experience particularly because it hits many of these same points. In my case the "local files" hasn't been a huge fixture but the Parquet and JSON ingestion have been very convenient and I think CH intends for `clickhouse-local` to be some sort of analog to the "add duckdb" point.

One of my favorite features is `SELECT ... FROM s3Cluster('', 'https://....../data//.json', ..., 'JSON')`[0] which lets you wildcard ingest from an S3 bucket and distributes the processing across nodes in your configured cluster. Also, I think it works with `schema_inference_mode` (mentioned below) though I haven't tried it. Very cool time for databases / DB tooling.

(I actually wasn't familiar with `union_by_name` but it looks to be like Clickhouse has implemented that as well [1,2] Neat feature in either case!)

[0] https://clickhouse.com/docs/sql-reference/table-functions/s3... [1] https://clickhouse.com/docs/interfaces/schema-inference [2] https://github.com/ClickHouse/ClickHouse/pull/55892

Re: Why DuckDB is my first choice for data processing

#82

Post by dimitrieh »

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.

100%

We recently created a survey website for the community survey results for Node-RED making it completely dynamic and segment-able. Creates lots of value and allows everyone to look at the data through their own lens. It uses DuckDB with WASM under the hood. Awesome technologies.

https://nodered.org/community-survey/

Re: Why DuckDB is my first choice for data processing

#84

Post by lz400 »

I do a lot of data processing and my tool of choice is polars. It's blazing fast and has (like pandas) a lot of very useful functions that aren't in SQL or are awkward to emulate in SQL. I can also just do Python functions if I want something that's not offered.

Please sell DuckDB to me. I don't know it very well but my (possibly wrong) intuition is that even giving equal performance, it's going to drop me to the awkwardness of SQL for data processing.

Re: Why DuckDB is my first choice for data processing

#85

Post by mr_toad »

physicsguy wrote: post #69
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…

You can get 32TiB of RAM instances on AWS these days

Which is a lot for a single user, but when you have a dozens or hundreds of analysts who all want to run their own jobs on your hundred terabyte data warehouse then even the largest single machine wont cut it.

Re: Why DuckDB is my first choice for data processing

#86

Post by mr_toad »

tjchear wrote: post #7

I’ve not used duckdb before nor do I do much data analysis so I am curious about this one aspect of processing medium sized json/csv with it: the data are not indexed, so any non-trivial query would require a full scan. Is duckdb so fast that this is never really a problem for most folks?

Many analytical queries require full scans of fact tables anyway, so indexes are less useful. Joins are usually to dimensional tables, which are quite small. Snowflake doesn’t use indexes at all, and it’s built for handling the largest volumes of data.

However, you wouldn’t want to use either for transaction processing, the lack of indexes would really hurt.

Re: Why DuckDB is my first choice for data processing

#88

Post by erikcw »

theLiminator wrote: post #54
wood_spirit wrote: post #52

Earlier quoted context omitted.

I’m also a duckdb convert. All my notebooks have moved from Pandas and polars to Duckdb. It is faster to write and faster to read (after you return to a notebook after time away) and often faster to run. Certainly not slower to run. My current habit is to suck down big datasets to parquet shards and then just query them with a wildcard in duckdb. I move to bigquery when doing true “big data” but a few GB of extract f…

I personally find polars easier to read/write than sql. Especially when you start doing UDFs with numpy/et. al. I think for me, duckdb's clear edge is the cli experience. > It is faster to write and faster to read At least on clickbench, polars and duckdb are roughly comparable (with polars edging out duckdb).

I use them both depending on which feels more natural for the task, often within the same project. The interop is easy and very high performance thanks to Apache Arrow: `df = duckdb.sql(sql).pl()` and `result = duckdb.sql("SELECT * FROM df")`.

Re: Why DuckDB is my first choice for data processing

#89

Post by nylonstrung »

DuckDB is awesome.

If you want it's power as a query engine but like to write python instead of SQL, I highly recommend using it as a backend for the Ibis dataframe library

It let's you interchange pythonic dataframe syntax (like Pandas and Polars) with SQL that 'compile' down to SQL in DuckDB dialect

And you can use those queries interchangably in postgres, sqlite, polars, spark, etc

https://ibis-project.org/

Re: Why DuckDB is my first choice for data processing

#90

Post by bovinejoni »

lz400 wrote: post #84

I do a lot of data processing and my tool of choice is polars. It's blazing fast and has (like pandas) a lot of very useful functions that aren't in SQL or are awkward to emulate in SQL. I can also just do Python functions if I want something that's not offered. Please sell DuckDB to me. I don't know it very well but my (possibly wrong) intuition is that even giving equal performance, it's going to drop me to the awk…

I could anecdotally tell you it’s significantly faster and more concise for my workloads, but it’s a standalone executable so just try it out and benchmark for your use case. It doesn’t require fine tuning or even a learning curve
Post reply on HN