9 ms·

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).

by mrtimo 8mo ago

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 types well. -- It's small! The Web Assembly version is 2mb! The CLI is 16mb. -- Because it is small you can add duckdb directly to your product, like Malloy has done: https://www.malloydata.dev/ https://www.malloydata.dev/ - I think of Malloy as a technical persons alternative to PowerBI and Tableau, but it uses a semantic model that helps AI write amazing queries on your data. Edit: Malloy makes SQL 10x easier to write because of its semantic nature. Malloy transpiles to SQL, like Typescript transpiles to Javascript.

oulipo2 8mo ago
Malloy and PRQL (https://prql-lang.org/book/ https://prql-lang.org/book/) are quite cool
newusertoday 8mo ago
its 32 mb uncompressed and around 6MB compressed, its not that small https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ 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.
arjie 8mo ago
This is a great sell. I have this annoyingly manual approach with a SQLite import and so on. This is great. Thank you!
exographicskip 8mo ago
Thanks for the excellent comment! Now excuse me while I go export my spotify history to play around with duckdb <3
- mrtimo 8mo ago
  Spotify says it will take 30 days for the export... it really only takes about 48 hours if I remember correctly. While you wait for the download here is an example listening history exploration in malloy - I converted the listening history to .parquet: https://github.com/mrtimo/spotify-listening-history https://github.com/mrtimo/spotify-listening-history
skeeter2020 8mo ago
>> The .csv parser is amazing Their csv support coupled with lots of functions and fast & easy iterative data discovery has totally changed how I approach investigation problems. I used to focus a significant amount of time on understanding the underlying schema of the problem space first, and often there really wasn't one - but you didn't find out easily. Now I start with pulling in data, writing exploratory queries to validate my assumptions, then cleaning & transforming data and creating new tables from that state; rinse and repeat. Aside from getting much deeper much quicker, you also hit dead ends sooner, saving a lot of otherwise wasted time. There's an interesting paper out there on how the CSV parser works, and some ideas for future enhancements. I couldn't seem to find it but maybe someone else can?
- tosh 8mo ago
  not a paper but I found this: https://duckdb.org/2025/04/16/duckdb-csv-pollock-benchmark https://duckdb.org/2025/04/16/duckdb-csv-pollock-benchmark
hk1337 8mo ago
-- hive partitioning
HowardStark 8mo ago
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('<ch cluster>', 'https://...<s3 url>.../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/s3Cluster https://clickhouse.com/docs/sql-reference/table-functions/s3... [1] https://clickhouse.com/docs/interfaces/schema-inference https://clickhouse.com/docs/interfaces/schema-inference [2] https://github.com/ClickHouse/ClickHouse/pull/55892 https://github.com/ClickHouse/ClickHouse/pull/55892
freakynit 8mo ago
I built https://zenquery.app https://zenquery.app and have used duckdb internally to do all procssing. The speed is crazy, schema auto-detection works correctly (most of the times) and LLM's generate correct SQL's for given queries in plain english.
falconroar 8mo ago
Polars has all of these benefits (to some degree), but also allows for larger-than-memory datasets.
- prometheon1 8mo ago
  DuckDB supports this as well, depending on which benchmark you look at it regularly performs better on those datasets than Polars.
jorin 8mo ago
I built Shaper following Malloy's idea of combining data queries and visualizations. But Shaper uses SQL instead of a custom language. It turns DuckDB into a dashboard builder for when you all you need is SQL. https://github.com/taleshape-com/shaper https://github.com/taleshape-com/shaper

by mrtimo 8mo ago

oulipo2 8mo ago

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

newusertoday 8mo ago

its 32 mb uncompressed and around 6MB compressed, its not that small https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm/dist/ 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.

arjie 8mo ago

This is a great sell. I have this annoyingly manual approach with a SQLite import and so on. This is great. Thank you!

exographicskip 8mo ago

Thanks for the excellent comment! Now excuse me while I go export my spotify history to play around with duckdb <3

mrtimo 8mo ago
Spotify says it will take 30 days for the export... it really only takes about 48 hours if I remember correctly. While you wait for the download here is an example listening history exploration in malloy - I converted the listening history to .parquet: https://github.com/mrtimo/spotify-listening-history https://github.com/mrtimo/spotify-listening-history

skeeter2020 8mo ago

>> The .csv parser is amazing Their csv support coupled with lots of functions and fast & easy iterative data discovery has totally changed how I approach investigation problems. I used to focus a significant amount of time on understanding the underlying schema of the problem space first, and often there really wasn't one - but you didn't find out easily. Now I start with pulling in data, writing exploratory queries to validate my assumptions, then cleaning & transforming data and creating new tables from that state; rinse and repeat. Aside from getting much deeper much quicker, you also hit dead ends sooner, saving a lot of otherwise wasted time. There's an interesting paper out there on how the CSV parser works, and some ideas for future enhancements. I couldn't seem to find it but maybe someone else can?

tosh 8mo ago
not a paper but I found this: https://duckdb.org/2025/04/16/duckdb-csv-pollock-benchmark https://duckdb.org/2025/04/16/duckdb-csv-pollock-benchmark

hk1337 8mo ago

-- hive partitioning

HowardStark 8mo ago

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('<ch cluster>', 'https://...<s3 url>.../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/s3Cluster https://clickhouse.com/docs/sql-reference/table-functions/s3... [1] https://clickhouse.com/docs/interfaces/schema-inference https://clickhouse.com/docs/interfaces/schema-inference [2] https://github.com/ClickHouse/ClickHouse/pull/55892 https://github.com/ClickHouse/ClickHouse/pull/55892

freakynit 8mo ago

I built https://zenquery.app https://zenquery.app and have used duckdb internally to do all procssing. The speed is crazy, schema auto-detection works correctly (most of the times) and LLM's generate correct SQL's for given queries in plain english.

falconroar 8mo ago

Polars has all of these benefits (to some degree), but also allows for larger-than-memory datasets.

prometheon1 8mo ago
DuckDB supports this as well, depending on which benchmark you look at it regularly performs better on those datasets than Polars.

jorin 8mo ago

I built Shaper following Malloy's idea of combining data queries and visualizations. But Shaper uses SQL instead of a custom language. It turns DuckDB into a dashboard builder for when you all you need is SQL. https://github.com/taleshape-com/shaper https://github.com/taleshape-com/shaper