9 ms·

DuckDB: Querying JSON files as if they were tables

294 points by cmdlineluser 4y ago

Waterluvian 4y ago
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.
- jmartin2683 4y ago
  We’re currently building real-time apis backed by terabytes of compressed parquet… hundreds of billions of ‘rows’… in exactly this fashion using polars. It amazes us at every turn. Join us and help!
  - ploppyploppy 4y ago
    What project? Do you mean polars reading Parquet into DuckDB to process that amount of data?
    - jmartin2683 4y ago
      Internal. We're using Polars as the query engine to effectively query that data statically at rest (more accurately, mmap'd on disk in arrow ipc format)
- truculent 4y ago
  What does this look like in practice? Using the filesystem as a database?
  - vlovich123 4y ago
    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.
  - masukomi 4y ago
    GNU Recutils https://www.gnu.org/software/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.
    - necrotic_comp 4y ago
      Recutils is great, but it needs a rewrite, I think.
  - bobleeswagger 4y ago
    Isn't linux a good example of this? Everything is a file.
  - mcdonje 4y ago
    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.
richraposa 4y ago
It'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-tool-clickhouse-local https://clickhouse.com/blog/worlds-fastest-json-querying-too...
chrisjc 4y ago
Awesome 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 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?
orthoxerox 4y ago
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.
- ihateolives 4y ago
  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.
- nickpeterson 4y ago
  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.
  - orthoxerox 4y ago
    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.
    - benjaminwootton 4y ago
      What’s the workflow with JDBC? Say you connect Tableau to it. How would you populate it with data?
      - orthoxerox 4y ago
        My workflow is that I have a connection to c:\temp\scratchpad.db in DBeaver that I populate via create table some_data as select * from 'c:\temp\whatever.csv' or create table other_data as select * from read_parquet('c:\temp\000000_0') which I then can transform using SQL and export the result into CSV, Parquet or SQLite when needed.
papruapap 4y ago
Could anyone that uses these tools regularly tell if this a better than jq for querying?
- jalk 4y ago
  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.
eatonphil 4y ago
Welcome to the gang! :) https://github.com/multiprocessio/dsq#comparisons 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.
- snthpy 4y ago
  I made a docker image with a number of extensions already installed and enabled so you can start using DuckDB with the lowest friction. `alias dckr='docker run --rm -it -v $(pwd):/data -w /data duckerlabs/ducker'` then `dckr` gives you a DuckDB shell with PRQL, httpfs, json, parquet, postgres, sqlite, and substrait enabled. For example, to get the first 5 lines of a csv file named "albums.csv", you could run it with PRQL ```dckr -c 'from `albums.csv` | take 5;'``` https://github.com/duckerlabs/ducker https://github.com/duckerlabs/ducker
pbreit 4y ago
Besides XML, JSON is about the worst way to format tabular data, right?
- chundicus 4y ago
  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.
  - pbreit 4y ago
    It's interesting that JSON is still the format of choice for transmitting tabular data to SPAs and mobile apps. Granted, it's likely compressed. But still seems something more efficient like CSV would be better.
- mcdonje 4y ago
  If it's tabular, self-describing formats have way too much overhead. I ran a query with a tabular result in the neighborhood of 100 columns by 215k rows, and exported it in multiple formats: - CSV: 166mb - JSON: 795mb That said, not all data is tabular. DuckDB already supports Parquet, which supports structs and is a very good format for storing data for reporting workloads. But JSON is a standard interchange format, so a lot of people are going to want to do something with JSON payloads they receive from API calls. I could definitely imagine a workload where you receive JSON from an API call, load it into DuckDB or similar to help with ETL, then store results in Parquet.
- lnkuiper 4y ago
  This is very true. DuckDB does not support JSON because it’s a good tabular format, but because JSON is ubiquitous, and there are many use cases where querying JSON dumps for analytics is useful.
- AtNightWeCode 4y ago
mkaic 4y ago
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.
- piperswe 4y ago
  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.
  - sidpatil 4y ago
    > Using DuckDB or SQLite's CSV/JSON support gets you the best of both worlds (minus indices) DuckDB automatically creates indexes for all general-purpose columns. However, they're not persisted. https://duckdb.org/docs/sql/indexes.html https://duckdb.org/docs/sql/indexes.html
- mytherin 4y ago
  Perhaps have a look at this article [1] [1] https://www.vantage.sh/blog/querying-aws-cost-data-duckdb https://www.vantage.sh/blog/querying-aws-cost-data-duckdb
- nkh 4y ago
  DuckDB is "column oriented" vs "row oriented". I have found it 10x* faster for queries on data your size compared to SQLite or MySQL or Postgres. The added advantage of it being a single file is very nice as well. *I use the HoneySQL (clojure) library to programmatically build up queries and execute them via the JDBC driver.
cube2222 4y ago
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 https://duckdb.org/2022/09/30/postgres-scanner.html [1]: https://github.com/cube2222/octosql https://github.com/cube2222/octosql [2]: https://steampipe.io https://steampipe.io
- cube2222 4y ago
  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 https://github.com/Mause/duckdb-extension-framework
- obi1kenobi 4y ago
  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 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=IyBDcm9zcyBBUEkgcXVlcnkgKEFsZ29saWEgKyBGaXJlYmFzZSk6CiMgRmluZCBjb21tZW50cyBvbiBzdG9yaWVzIGFib3V0ICJvcGVuYWkuY29tIiB3aGVyZQojIHRoZSBjb21tZW50ZXIncyBiaW8gaGFzIGF0IGxlYXN0IG9uZSBHaXRIdWIgb3IgVHdpdHRlciBsaW5rCnF1ZXJ5IHsKICAjIFRoaXMgaGl0cyB0aGUgQWxnb2xpYSBzZWFyY2ggQVBJIGZvciBIYWNrZXJOZXdzLgogICMgVGhlIHN0b3JpZXMvY29tbWVudHMvdXNlcnMgZGF0YSBpcyBmcm9tIHRoZSBGaXJlYmFzZSBITiBBUEkuCiAgIyBUaGUgdHJhbnNpdGlvbiBpcyBzZWFtbGVzcyAtLSBpdCBpc24ndCB2aXNpYmxlIGZyb20gdGhlIHF1ZXJ5LgogIFNlYXJjaEJ5RGF0ZShxdWVyeTogIm9wZW5haS5jb20iKSB7CiAgICAuLi4gb24gU3RvcnkgewogICAgICAjIEFsbCBkYXRhIGZyb20gaGVyZSBvbndhcmQgaXMgZnJvbSB0aGUgRmlyZWJhc2UgQVBJLgogICAgICBzdG9yeVRpdGxlOiB0aXRsZSBAb3V0cHV0CiAgICAgIHN0b3J5TGluazogdXJsIEBvdXRwdXQKICAgICAgc3Rvcnk6IHN1Ym1pdHRlZFVybCBAb3V0cHV0CiAgICAgICAgICAgICAgICAgICAgICAgICAgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNpdGVQYXR0ZXJuIl0pCgogICAgICBjb21tZW50IHsKICAgICAgICByZXBseSBAcmVjdXJzZShkZXB0aDogNSkgewogICAgICAgICAgY29tbWVudDogdGV4dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICBieVVzZXIgewogICAgICAgICAgICBjb21tZW50ZXI6IGlkIEBvdXRwdXQKICAgICAgICAgICAgY29tbWVudGVyQmlvOiBhYm91dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICAgICMgVGhlIHByb2ZpbGUgbXVzdCBoYXZlIGF0IGxlYXN0IG9uZQogICAgICAgICAgICAjIGxpbmsgdGhhdCBwb2ludHMgdG8gZWl0aGVyIEdpdEh1YiBvciBUd2l0dGVyLgogICAgICAgICAgICBsaW5rCiAgICAgICAgICAgICAgQGZvbGQKICAgICAgICAgICAgICBAdHJhbnNmb3JtKG9wOiAiY291bnQiKQogICAgICAgICAgICAgIEBmaWx0ZXIob3A6ICI%2BPSIsIHZhbHVlOiBbIiRtaW5Qcm9maWxlcyJdKQogICAgICAgICAgICB7CiAgICAgICAgICAgICAgY29tbWVudGVySURzOiB1cmwgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNvY2lhbFBhdHRlcm4iXSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBAb3V0cHV0CiAgICAgICAgICAgIH0KICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIH0KICAgIH0KICB9Cn0%3D&v=ewogICJzaXRlUGF0dGVybiI6ICJodHRwW3NdOi8vKFteLl0qXFwuKSpvcGVuYWkuY29tLy4qIiwKICAibWluUHJvZmlsZXMiOiAxLAogICJzb2NpYWxQYXR0ZXJuIjogIihnaXRodWJ8dHdpdHRlcilcXC5jb20vIgp9 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-over-2000x/ https://predr.ag/blog/speeding-up-rust-semver-checking-by-ov...
tracker1 4y ago
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.
- tanin 4y ago
  That is exactly https://superintendent.app https://superintendent.app (disclaimer: I'm the creator)
antman 4y ago
Very nice! Does anyone know if we can query duckdb with a pandas dialect?
- data_ders 4y ago
  Check out ibis! https://github.com/ibis-project/ibis https://github.com/ibis-project/ibis
- closed 4y ago
  Hey, I maintain a tool called siuba that converts pandas methods to SQL, including for duckdb! * duckdb example: https://siuba.org/guide/workflows-backends.html#duckdb https://siuba.org/guide/workflows-backends.html#duckdb * supported methods: https://siuba.org/guide/ops-support-table.html https://siuba.org/guide/ops-support-table.html
- snthpy 4y ago
  Not Pandas but very similar and (in my very biased opinion) better is PRQL (www.prql-lang.org) which as of yesterday you can now use in DuckDB! See my comment above: https://news.ycombinator.com/item?id=35027712 https://news.ycombinator.com/item?id=35027712
nmy 4y ago
I used to do this with Apache Drill a few years ago. There is something beautiful about downloading 1 binary and being able to query your files (json/csv/parquet etc) right away
theloco 4y ago
this looks preddy cool. i was using the json datatype in mysql at the beginning of my project and we ended up yanking it out because of the way you query data within the json. it just started getting kludgey and i felt like i was trying to turn mysql into mongo, but suffering because its not. will follow duckdb.
spullara 4y ago
Running into a couple issues right out of the gate: 1) Needed to increase maximum_object_size 2) Unexpected yyjson tag in ValTypeToString Couldn't find a reference anywhere to that error. Loads into Snowflake without a hitch - which is where I normally query large JSON files.
- mytherin 4y ago
  Thanks for trying it out! Could you perhaps open an issue [1] or share the file with us so we could investigate the problem? [1] https://github.com/duckdb/duckdb/issues https://github.com/duckdb/duckdb/issues
  - atombender 4y ago
    I tried to do "select * from ... limit 1" from a 1.7GB JSON file (array of objects), and I had to increase maximum_object_size to 1GB to make it not throw an error. But DuckDB then consumed 8GB of RAM and sat there consuming 100% CPU (1 core) for ever — I killed it after about 10 minutes. Meanwhile, doing the same with Jq ("jq '.[0]'") completed in 11 seconds and consumed about 2.8GB RAM. I love DuckDB, but it does seem like something isn't right here.
- jjwiseman 4y ago
  Tried "select * from 'data.json' limit 10" on a 6.3 MB file (which feels relatively tiny…) and got the same `unexpected end of data. Try increasing "maximum_object_size"` error. (This is my very first attempt to use duckdb, so with respect I'm not invested enough to open an issue).
simonw 4y ago
> If your JSON file is newline-delimited, DuckDB can parallelize reading. I'd like to understand more about what that means. Does it use multiple threads each reading from a different position in the file?
- lnkuiper 4y ago
  DuckDB will use multiple threads for reading the same file. Each thread will read different parts of the file, but the output will be in the order that the file came in due to DuckDB’s order preserving parallelism.
janee 4y ago
Ah I was looking for exactly this the other day. I'm try to build a git based interface to our BI tool so that we can get config for our reports in source control instead of configuration in a db. Was looking for something to read json files which will house the config via SQL, i.e. a human readable db as an alternative to what the BI tool is using for it's config persistence. Will give DuckDB a go, thanks for posting!
- amcaskill 4y ago
  I’m working on an open source BI alternative called Evidence which works nicely with version control and CI/CD. If that’s of interest, you can read our launch HN here: https://news.ycombinator.com/item?id=28304781 https://news.ycombinator.com/item?id=28304781 One of our community members has built a pretty cool Duck DB + dbt + evidence data stack that you can run entirely in GitHub codespaces. He’s calling it modern data stack in a box. You can see that repo here: https://github.com/matsonj/nba-monte-carlo https://github.com/matsonj/nba-monte-carlo
- Jgrubb 4y ago
  Are you talking about metabase by any chance?
  - janee 4y ago
    Haha indeed! I've started with a very basic Ruby api client that can read and create dashboards. My plan is to poc a tool that allows you to edit metabase config as files and secondly something that can replicate cloud instances to other environments like local docker image or staging instance
vgt 4y ago
One of the major reasons we bet big on DuckDb at MotherDuck is due to the extendable nature of the code base, as demonstrated by the frequency of major improvements and additions.
luxurytent 4y ago
I'm impressed to all heck how fast this is, and how intuitive! I tried it against a large-ish (hundreds of mb) log file and the query was simple to write while spitting out the result .. very quick. Impressed!
xyzzy4747 4y ago
How does this compare with CouchDB, which also stores JSON and allows you to construct cached map/reduce views? edit: Looks like DuckDB lets you use SQL-style queries
nigamanth 4y ago
Really makes you wonder what's the difference between NoSQL and SQL databases if they can be rendered as each other.
cweill 4y ago
If you ever need to join two large dataframes, but are OOMing on the join, write them to disk as parquet files then use DuckDB to do the join. It's amazing what you can do on one machine thanks to DuckDB.
- qolop 4y ago
  This isn't unique to duckdb. Almost all databases allow for sorting and joins of large tables that don't fit into memory.
  - cweill 4y ago
    Yes but if you're in a Jupyter notebook, you may not be directly connected to a DB. If you're using pandas, this unlocks some scalability before needing dask and a cluster.
MrPowers 4y ago
This is cool, but would like to give some higher level context about querying JSON files. JSON is a row based file format. It doesn't allow query engines to skip rows or skip columns when running queries, so all data needs to get read into memory. That's really inefficient. Column based file formats allow for query engines to skip entire columns of data (e.g. Parquet). Parquet also stores metadata on row groups and allows query engines to skip rows when reading data. These performance enhancements can speed up queries from 0x - 100x or more (depends on how much data is skipped). Data Lakehouse storage systems abstract the file metadata to a separate layer, which is even better than storing it in the file footer like Parquet does. This DuckDB functionality is cool, but I think it's best to use it to convert JSON files to Parquet / a Lakehouse storage system, and then query them. JSON is a really inefficient file format for running queries.

294 points by cmdlineluser 4y ago

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.

jmartin2683 4y ago
We’re currently building real-time apis backed by terabytes of compressed parquet… hundreds of billions of ‘rows’… in exactly this fashion using polars. It amazes us at every turn. Join us and help!
- ploppyploppy 4y ago
  What project? Do you mean polars reading Parquet into DuckDB to process that amount of data?
  - jmartin2683 4y ago
    Internal. We're using Polars as the query engine to effectively query that data statically at rest (more accurately, mmap'd on disk in arrow ipc format)
truculent 4y ago
What does this look like in practice? Using the filesystem as a database?
- vlovich123 4y ago
  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.
- masukomi 4y ago
  GNU Recutils https://www.gnu.org/software/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.
  - necrotic_comp 4y ago
    Recutils is great, but it needs a rewrite, I think.
- bobleeswagger 4y ago
  Isn't linux a good example of this? Everything is a file.
- mcdonje 4y ago
  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.

richraposa 4y ago

It'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-tool-clickhouse-local https://clickhouse.com/blog/worlds-fastest-json-querying-too...

chrisjc 4y ago

Awesome 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 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?

orthoxerox 4y ago

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.

ihateolives 4y ago
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.
nickpeterson 4y ago
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.
- orthoxerox 4y ago
  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.
  - benjaminwootton 4y ago
    What’s the workflow with JDBC? Say you connect Tableau to it. How would you populate it with data?
    - orthoxerox 4y ago
      My workflow is that I have a connection to c:\temp\scratchpad.db in DBeaver that I populate via create table some_data as select * from 'c:\temp\whatever.csv' or create table other_data as select * from read_parquet('c:\temp\000000_0') which I then can transform using SQL and export the result into CSV, Parquet or SQLite when needed.

papruapap 4y ago

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

jalk 4y ago
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.

eatonphil 4y ago

Welcome to the gang! :) https://github.com/multiprocessio/dsq#comparisons 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.

snthpy 4y ago
I made a docker image with a number of extensions already installed and enabled so you can start using DuckDB with the lowest friction. `alias dckr='docker run --rm -it -v $(pwd):/data -w /data duckerlabs/ducker'` then `dckr` gives you a DuckDB shell with PRQL, httpfs, json, parquet, postgres, sqlite, and substrait enabled. For example, to get the first 5 lines of a csv file named "albums.csv", you could run it with PRQL ```dckr -c 'from `albums.csv` | take 5;'``` https://github.com/duckerlabs/ducker https://github.com/duckerlabs/ducker

pbreit 4y ago

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

chundicus 4y ago
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.
- pbreit 4y ago
  It's interesting that JSON is still the format of choice for transmitting tabular data to SPAs and mobile apps. Granted, it's likely compressed. But still seems something more efficient like CSV would be better.
mcdonje 4y ago
If it's tabular, self-describing formats have way too much overhead. I ran a query with a tabular result in the neighborhood of 100 columns by 215k rows, and exported it in multiple formats: - CSV: 166mb - JSON: 795mb That said, not all data is tabular. DuckDB already supports Parquet, which supports structs and is a very good format for storing data for reporting workloads. But JSON is a standard interchange format, so a lot of people are going to want to do something with JSON payloads they receive from API calls. I could definitely imagine a workload where you receive JSON from an API call, load it into DuckDB or similar to help with ETL, then store results in Parquet.
lnkuiper 4y ago
This is very true. DuckDB does not support JSON because it’s a good tabular format, but because JSON is ubiquitous, and there are many use cases where querying JSON dumps for analytics is useful.
AtNightWeCode 4y ago

mkaic 4y ago

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.

piperswe 4y ago
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.
- sidpatil 4y ago
  > Using DuckDB or SQLite's CSV/JSON support gets you the best of both worlds (minus indices) DuckDB automatically creates indexes for all general-purpose columns. However, they're not persisted. https://duckdb.org/docs/sql/indexes.html https://duckdb.org/docs/sql/indexes.html
mytherin 4y ago
Perhaps have a look at this article [1] [1] https://www.vantage.sh/blog/querying-aws-cost-data-duckdb https://www.vantage.sh/blog/querying-aws-cost-data-duckdb
nkh 4y ago
DuckDB is "column oriented" vs "row oriented". I have found it 10x* faster for queries on data your size compared to SQLite or MySQL or Postgres. The added advantage of it being a single file is very nice as well. *I use the HoneySQL (clojure) library to programmatically build up queries and execute them via the JDBC driver.

cube2222 4y ago

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 https://duckdb.org/2022/09/30/postgres-scanner.html [1]: https://github.com/cube2222/octosql https://github.com/cube2222/octosql [2]: https://steampipe.io https://steampipe.io

cube2222 4y ago
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 https://github.com/Mause/duckdb-extension-framework
obi1kenobi 4y ago
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 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=IyBDcm9zcyBBUEkgcXVlcnkgKEFsZ29saWEgKyBGaXJlYmFzZSk6CiMgRmluZCBjb21tZW50cyBvbiBzdG9yaWVzIGFib3V0ICJvcGVuYWkuY29tIiB3aGVyZQojIHRoZSBjb21tZW50ZXIncyBiaW8gaGFzIGF0IGxlYXN0IG9uZSBHaXRIdWIgb3IgVHdpdHRlciBsaW5rCnF1ZXJ5IHsKICAjIFRoaXMgaGl0cyB0aGUgQWxnb2xpYSBzZWFyY2ggQVBJIGZvciBIYWNrZXJOZXdzLgogICMgVGhlIHN0b3JpZXMvY29tbWVudHMvdXNlcnMgZGF0YSBpcyBmcm9tIHRoZSBGaXJlYmFzZSBITiBBUEkuCiAgIyBUaGUgdHJhbnNpdGlvbiBpcyBzZWFtbGVzcyAtLSBpdCBpc24ndCB2aXNpYmxlIGZyb20gdGhlIHF1ZXJ5LgogIFNlYXJjaEJ5RGF0ZShxdWVyeTogIm9wZW5haS5jb20iKSB7CiAgICAuLi4gb24gU3RvcnkgewogICAgICAjIEFsbCBkYXRhIGZyb20gaGVyZSBvbndhcmQgaXMgZnJvbSB0aGUgRmlyZWJhc2UgQVBJLgogICAgICBzdG9yeVRpdGxlOiB0aXRsZSBAb3V0cHV0CiAgICAgIHN0b3J5TGluazogdXJsIEBvdXRwdXQKICAgICAgc3Rvcnk6IHN1Ym1pdHRlZFVybCBAb3V0cHV0CiAgICAgICAgICAgICAgICAgICAgICAgICAgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNpdGVQYXR0ZXJuIl0pCgogICAgICBjb21tZW50IHsKICAgICAgICByZXBseSBAcmVjdXJzZShkZXB0aDogNSkgewogICAgICAgICAgY29tbWVudDogdGV4dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICBieVVzZXIgewogICAgICAgICAgICBjb21tZW50ZXI6IGlkIEBvdXRwdXQKICAgICAgICAgICAgY29tbWVudGVyQmlvOiBhYm91dFBsYWluIEBvdXRwdXQKCiAgICAgICAgICAgICMgVGhlIHByb2ZpbGUgbXVzdCBoYXZlIGF0IGxlYXN0IG9uZQogICAgICAgICAgICAjIGxpbmsgdGhhdCBwb2ludHMgdG8gZWl0aGVyIEdpdEh1YiBvciBUd2l0dGVyLgogICAgICAgICAgICBsaW5rCiAgICAgICAgICAgICAgQGZvbGQKICAgICAgICAgICAgICBAdHJhbnNmb3JtKG9wOiAiY291bnQiKQogICAgICAgICAgICAgIEBmaWx0ZXIob3A6ICI%2BPSIsIHZhbHVlOiBbIiRtaW5Qcm9maWxlcyJdKQogICAgICAgICAgICB7CiAgICAgICAgICAgICAgY29tbWVudGVySURzOiB1cmwgQGZpbHRlcihvcDogInJlZ2V4IiwgdmFsdWU6IFsiJHNvY2lhbFBhdHRlcm4iXSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBAb3V0cHV0CiAgICAgICAgICAgIH0KICAgICAgICAgIH0KICAgICAgICB9CiAgICAgIH0KICAgIH0KICB9Cn0%3D&v=ewogICJzaXRlUGF0dGVybiI6ICJodHRwW3NdOi8vKFteLl0qXFwuKSpvcGVuYWkuY29tLy4qIiwKICAibWluUHJvZmlsZXMiOiAxLAogICJzb2NpYWxQYXR0ZXJuIjogIihnaXRodWJ8dHdpdHRlcilcXC5jb20vIgp9 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-over-2000x/ https://predr.ag/blog/speeding-up-rust-semver-checking-by-ov...

tracker1 4y ago

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.

tanin 4y ago
That is exactly https://superintendent.app https://superintendent.app (disclaimer: I'm the creator)

antman 4y ago

Very nice! Does anyone know if we can query duckdb with a pandas dialect?

data_ders 4y ago
Check out ibis! https://github.com/ibis-project/ibis https://github.com/ibis-project/ibis
closed 4y ago
Hey, I maintain a tool called siuba that converts pandas methods to SQL, including for duckdb! * duckdb example: https://siuba.org/guide/workflows-backends.html#duckdb https://siuba.org/guide/workflows-backends.html#duckdb * supported methods: https://siuba.org/guide/ops-support-table.html https://siuba.org/guide/ops-support-table.html
snthpy 4y ago
Not Pandas but very similar and (in my very biased opinion) better is PRQL (www.prql-lang.org) which as of yesterday you can now use in DuckDB! See my comment above: https://news.ycombinator.com/item?id=35027712 https://news.ycombinator.com/item?id=35027712

nmy 4y ago

I used to do this with Apache Drill a few years ago. There is something beautiful about downloading 1 binary and being able to query your files (json/csv/parquet etc) right away

theloco 4y ago

this looks preddy cool. i was using the json datatype in mysql at the beginning of my project and we ended up yanking it out because of the way you query data within the json. it just started getting kludgey and i felt like i was trying to turn mysql into mongo, but suffering because its not. will follow duckdb.

spullara 4y ago

Running into a couple issues right out of the gate: 1) Needed to increase maximum_object_size 2) Unexpected yyjson tag in ValTypeToString Couldn't find a reference anywhere to that error. Loads into Snowflake without a hitch - which is where I normally query large JSON files.

mytherin 4y ago
Thanks for trying it out! Could you perhaps open an issue [1] or share the file with us so we could investigate the problem? [1] https://github.com/duckdb/duckdb/issues https://github.com/duckdb/duckdb/issues
- atombender 4y ago
  I tried to do "select * from ... limit 1" from a 1.7GB JSON file (array of objects), and I had to increase maximum_object_size to 1GB to make it not throw an error. But DuckDB then consumed 8GB of RAM and sat there consuming 100% CPU (1 core) for ever — I killed it after about 10 minutes. Meanwhile, doing the same with Jq ("jq '.[0]'") completed in 11 seconds and consumed about 2.8GB RAM. I love DuckDB, but it does seem like something isn't right here.
jjwiseman 4y ago
Tried "select * from 'data.json' limit 10" on a 6.3 MB file (which feels relatively tiny…) and got the same `unexpected end of data. Try increasing "maximum_object_size"` error. (This is my very first attempt to use duckdb, so with respect I'm not invested enough to open an issue).

simonw 4y ago

> If your JSON file is newline-delimited, DuckDB can parallelize reading. I'd like to understand more about what that means. Does it use multiple threads each reading from a different position in the file?

lnkuiper 4y ago
DuckDB will use multiple threads for reading the same file. Each thread will read different parts of the file, but the output will be in the order that the file came in due to DuckDB’s order preserving parallelism.

janee 4y ago

Ah I was looking for exactly this the other day. I'm try to build a git based interface to our BI tool so that we can get config for our reports in source control instead of configuration in a db. Was looking for something to read json files which will house the config via SQL, i.e. a human readable db as an alternative to what the BI tool is using for it's config persistence. Will give DuckDB a go, thanks for posting!

amcaskill 4y ago
I’m working on an open source BI alternative called Evidence which works nicely with version control and CI/CD. If that’s of interest, you can read our launch HN here: https://news.ycombinator.com/item?id=28304781 https://news.ycombinator.com/item?id=28304781 One of our community members has built a pretty cool Duck DB + dbt + evidence data stack that you can run entirely in GitHub codespaces. He’s calling it modern data stack in a box. You can see that repo here: https://github.com/matsonj/nba-monte-carlo https://github.com/matsonj/nba-monte-carlo
Jgrubb 4y ago
Are you talking about metabase by any chance?
- janee 4y ago
  Haha indeed! I've started with a very basic Ruby api client that can read and create dashboards. My plan is to poc a tool that allows you to edit metabase config as files and secondly something that can replicate cloud instances to other environments like local docker image or staging instance

vgt 4y ago

One of the major reasons we bet big on DuckDb at MotherDuck is due to the extendable nature of the code base, as demonstrated by the frequency of major improvements and additions.

luxurytent 4y ago

I'm impressed to all heck how fast this is, and how intuitive! I tried it against a large-ish (hundreds of mb) log file and the query was simple to write while spitting out the result .. very quick. Impressed!

xyzzy4747 4y ago

How does this compare with CouchDB, which also stores JSON and allows you to construct cached map/reduce views? edit: Looks like DuckDB lets you use SQL-style queries

nigamanth 4y ago

Really makes you wonder what's the difference between NoSQL and SQL databases if they can be rendered as each other.

cweill 4y ago

If you ever need to join two large dataframes, but are OOMing on the join, write them to disk as parquet files then use DuckDB to do the join. It's amazing what you can do on one machine thanks to DuckDB.

qolop 4y ago
This isn't unique to duckdb. Almost all databases allow for sorting and joins of large tables that don't fit into memory.
- cweill 4y ago
  Yes but if you're in a Jupyter notebook, you may not be directly connected to a DB. If you're using pandas, this unlocks some scalability before needing dask and a cluster.

MrPowers 4y ago

This is cool, but would like to give some higher level context about querying JSON files. JSON is a row based file format. It doesn't allow query engines to skip rows or skip columns when running queries, so all data needs to get read into memory. That's really inefficient. Column based file formats allow for query engines to skip entire columns of data (e.g. Parquet). Parquet also stores metadata on row groups and allows query engines to skip rows when reading data. These performance enhancements can speed up queries from 0x - 100x or more (depends on how much data is skipped). Data Lakehouse storage systems abstract the file metadata to a separate layer, which is even better than storing it in the file footer like Parquet does. This DuckDB functionality is cool, but I think it's best to use it to convert JSON files to Parquet / a Lakehouse storage system, and then query them. JSON is a really inefficient file format for running queries.