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

DuckDB: Querying JSON files as if they were tables

duckdb.org

Post reply on HN
Print view View original

21–30 of 81 posts

Re: DuckDB: Querying JSON files as if they were tables

#21

Post by mcdonje »

truculent wrote: post #4
Waterluvian wrote: post #2

Mind you this isn’t appropriate for most cases. But I love the idea of “you start with text file. You end with text file. All the database stuff, indexes, etc. are just a detail.” Often I find that the database wants to be the authority and that makes working with different formats a bit uncomfortable.

What does this look like in practice? Using the filesystem as a database?

Apache Spark / Databricks is an example of this. Parquet files are stored in folders. A folder is assumed to hold one dataset split into multiple files based on specified partition criteria. The VMs read the necessary files into memory and then operate on it.

Re: DuckDB: Querying JSON files as if they were tables

#22

Post by tracker1 »

What's funny, is I've wanted something similar as a feature for "Azure Data Studio" that can open/use a CSV file and query it as a sqlite table. Basically an auto-import to a temp or in-memory db/table that you can then query against.

Would just be a nice gui feature to have.

Re: DuckDB: Querying JSON files as if they were tables

#24

Post by obi1kenobi »

cube2222 wrote: post #18

This is really cool! With their Postgres scanner[0] you can now easily query multiple datasources using SQL and join between them (i.e. Postgres table with JSON file). Something I previously strived to build with OctoSQL[1]. There's even predicate push-down to the underlying databases (for Postgres)! It's amazing to see how quickly DuckDB is adding new features. Not a huge fan of C++, which is right now used for auth…

It's a very exciting time to be working in this space! Going beyond structured databases and file formats like JSON/CSV, there are also systems that can query APIs, source code, ML models, etc.

My own Trustfall query engine is one of them: https://github.com/obi1kenobi/trustfall

For example, you can query the HackerNews APIs from your browser: "Which Twitter/GitHub users comment on stories about OpenAI?" https://play.predr.ag/hackernews#?f=1&q=IyBDcm9zcyBBUEkgcXVl...

One of its real-world use cases is at the core a Rust semver linter: https://predr.ag/blog/speeding-up-rust-semver-checking-by-ov...

Re: DuckDB: Querying JSON files as if they were tables

#26

Post by ihateolives »

orthoxerox wrote: post #8

I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.

I'm recent convert too. I used to work with SQLite for querying datasets but for my usecase DuckDB is much faster plus CLI is nicer to work with.

Re: DuckDB: Querying JSON files as if they were tables

#28

Post by orthoxerox »

nickpeterson wrote: post #11
orthoxerox wrote: post #8

I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.

Any chance you’ve tried clickhouse local? I was thinking it might be a good fit but haven’t used duckdb at all so I might be missing out on big differences.

No, I haven't, but it looks like it's a standalone console application, while DuckDB is in-process, like SQLite, and lives inside its JDBC driver. This means I can use it inside any compatible GUI and get stuff like schema browsing and IntelliSense out of the box. Since I have DBeaver open all day anyway, DuckDB is always a tab away.

Re: DuckDB: Querying JSON files as if they were tables

#29

Post by chundicus »

pbreit wrote: post #25

Besides XML, JSON is about the worst way to format tabular data, right?

For me it depends a lot on the context. JSON is often very human readable (as long as it's not too deeply nested), fairly well defined (compared to CSVs), and most languages and software have easy out of the box support for parsing and manipulating it.

If I were building a system that had to deal with large amounts of tabular data that isn't directly consumed by humans, JSON wouldn't be my first choice nor my last.

Re: DuckDB: Querying JSON files as if they were tables

#30

Post by ihateolives »

RyanHamilton wrote: post #12
orthoxerox wrote: post #8

I think I write this under every article about DuckDB, but it's become an indispensable tool for me. I used to abuse Excel because going from Excel to a script to process some data was too much friction, but with DuckDB the friction is gone: loading CSV and Parquet (and now JSON) files is a snap, you can create and persist any tables you want, the SQL dialect has lots of useful sugar.

I've just added support for duckdb to the free SQL tool I make: https://www.timestored.com/qstudio/help/duckdb-sql-editor It allows click to open, browsing tables etc. If you have some time, I would really appreciate feedback from a real user, other than myself.

Tried to open DuckDB file with one table with 890K rows and about 30 columns. Process stalled, had to kill it (MacOS). DuckDB cli opens it in a snap.
Post reply on HN