DuckDB: Querying JSON files as if they were tables
1–10 of 81 posts
Re: DuckDB: Querying JSON files as if they were tables
#2Mind 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.
Re: DuckDB: Querying JSON files as if they were tables
#3It's definitely cool to be able to query data in place instead of inserting it into a table. You can use clickhouse-local to do the same thing with JSON files (and with dozens of other data formats): https://clickhouse.com/blog/worlds-fastest-json-querying-too...
Re: DuckDB: Querying JSON files as if they were tables
#4Mind 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?
Re: DuckDB: Querying JSON files as if they were tables
#5Mind 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?
Anything that stores data on a computer is essentially a database. It's all about representation and what kinds of operations you prioritize for performance.
Re: DuckDB: Querying JSON files as if they were tables
#6Awesome stuff!
I was about to comment about how this is all fantastic stuff, but I've really found reading through duckdb docs quite challenging. But for these json table functions, documentation looks much better.
https://duckdb.org/docs/extensions/json
Need to spend some more time digging in, but this json functionality combined with some kind of file partitioning (Hive or hive-like) looks promising for some of my use cases.
Incidentally, the documentation for hive/parquet stuff is a good example of what I'm talking about above. For the `parquet_scan` function, where can i see all of the possible function parameters? Where can get more information about the specifics of `FILENAME`, `HIVE_PARTITIONING`, etc?
Re: DuckDB: Querying JSON files as if they were tables
#7Mind 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?
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.
Re: DuckDB: Querying JSON files as if they were tables
#8I 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.
Re: DuckDB: Querying JSON files as if they were tables
#9Could anyone that uses these tools regularly tell if this a better than jq for querying?
Re: DuckDB: Querying JSON files as if they were tables
#10Mind 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?
Isn't linux a good example of this? Everything is a file.