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

11–20 of 81 posts

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

#11

Post by nickpeterson »

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.

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

#12

Post by RyanHamilton »

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.

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

#13

Post by jalk »

papruapap wrote: post #9

Could anyone that uses these tools regularly tell if this a better than jq for querying?

It really depends. Using the relational operators to query deeply nested json objects is pretty painful (multiple layers of unnest's ) but fairly simple in jq. On the other hand, joining a couple of "flat" json files will be simple in DuckDB but not readily supported in jq. And if you already know sql thats a win ofc. i.e. I know how to group and aggregate using DuckDB since I know SQL, but currently have no idea about how to do that in jq. And once I find a solution in jq, that is not knowledge I can transfer to other tools.

jq's syntax is deliberately terse which works really really well for "one-liners", while sql queries tend to be more verbose.

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

#14

Post by eatonphil »

Welcome to the gang! :)

https://github.com/multiprocessio/dsq#comparisons

Realistically though aside from the variety of input formats that DuckDB doesn't (yet) support, I think most people should probably use DuckDB or ClickHouse-local. Tools like dsq can provide broader support or a slightly simpler UX in some cases (and even that is obviously debatable). But I think the future is more the DuckDB or ClickHouse-local way.

dsq may end up being a frontend over DuckDB some day.

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

#15

Post by necrotic_comp »

masukomi wrote: post #7
truculent wrote: post #4

Earlier quoted context omitted.

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

GNU Recutils https://www.gnu.org/software/recutils/ is a good example of an actual database that uses plaintext files in your filesystem. I can see the argument that doing this with JSON is better (or worse), but regardless, Recutils is an interesting idea that i wish more people knew about. I can imagine a lot of cool things emerging if people would iterate on the idea.

Recutils is great, but it needs a rewrite, I think.

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

#16

Post by mkaic »

I'm currently operating a very small (10s of millions of rows, ~20GB of total data) low-write MySQL DB with a couple different tables. I'm new to RDBs in general and am using MySQL because my thought was any "real" DB would be better than our previous "pipeline", which was just doing all our data filtering/merging with CSVs and Pandas in Python (extremely slowly, and frustrating).

I like the simplicity of DuckDB's proposal, but haven't seen much info about how fast to expect it to be in comparison with traditional RDBs, for smaller, mostly-read-only applications.

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

#17

Post by piperswe »

mkaic wrote: post #16

I'm currently operating a very small (10s of millions of rows, ~20GB of total data) low-write MySQL DB with a couple different tables. I'm new to RDBs in general and am using MySQL because my thought was any "real" DB would be better than our previous "pipeline", which was just doing all our data filtering/merging with CSVs and Pandas in Python (extremely slowly, and frustrating). I like the simplicity of DuckDB's pr…

Scanning through a CSV can be quite close to querying a SQL database in performance when the SQL database doesn't have any indices. The primary benefits of using a SQL database for querying are (1) indices and (2) a declarative query language. Using DuckDB or SQLite's CSV/JSON support gets you the best of both worlds (minus indices), where you get the declarative query language and query planner but your data's still just CSV/JSON files.

For a dataset that size, I'd probably use SQLite to avoid having to manage a persistent MySQL process, especially when it's being used as an alternative to CSV files. That is, unless there's a MySQL/Postgres server already running I can just create a new database on.

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

#18

Post by cube2222 »

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 authoring extensions, it'd be really cool if somebody implemented a Rust extension SDK, or even something like Steampipe[2] does for Postgres FDWs which would provide a shim for quickly implementing non-performance-sensitive extensions for various things.

Godspeed!

[0]: https://duckdb.org/2022/09/30/postgres-scanner.html

[1]: https://github.com/cube2222/octosql

[2]: https://steampipe.io

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

#19

Post by cube2222 »

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…

To answer myself, I've found a project which enables extension development for DuckDB using Rust[0].

[0]: https://github.com/Mause/duckdb-extension-framework

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

#20

Post by mytherin »

mkaic wrote: post #16

I'm currently operating a very small (10s of millions of rows, ~20GB of total data) low-write MySQL DB with a couple different tables. I'm new to RDBs in general and am using MySQL because my thought was any "real" DB would be better than our previous "pipeline", which was just doing all our data filtering/merging with CSVs and Pandas in Python (extremely slowly, and frustrating). I like the simplicity of DuckDB's pr…

Perhaps have a look at this article [1]

[1] https://www.vantage.sh/blog/querying-aws-cost-data-duckdb

Post reply on HN