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

31–40 of 81 posts

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

#31

Post by sidpatil »

piperswe wrote: post #17
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…

> 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

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

#32

Post by benjaminwootton »

orthoxerox wrote: post #28
nickpeterson wrote: post #11

Earlier quoted context omitted.

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.

What’s the workflow with JDBC? Say you connect Tableau to it. How would you populate it with data?

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

#33

Post by benjaminwootton »

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.

The benefit of “in process” isn’t really clicking for me.

We had Pandas or similar if we wanted to load and transform some data in memory. SQL is nicer than Pandas APIs but not sure that’s a killer feature?

If we have a lot of data and multiple people working with it then it makes sense to centralise it in a database or warehouse where it’s then easy to access via SQL anyway.

We can query files on S3 with it and have the processing locally, but then we have network latency because compute and storage are further apart. There’s a cost benefit here because we don’t need to run a server which could be significant.

It’s nicely implemented but I’m not seeing a big gap that it fills?

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

#34

Post by orthoxerox »

benjaminwootton wrote: post #32
orthoxerox wrote: post #28

Earlier quoted context omitted.

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.

What’s the workflow with JDBC? Say you connect Tableau to it. How would you populate it with data?

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.

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

#36

Post by mcdonje »

pbreit wrote: post #25

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

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.

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

#37

Post by nkh »

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…

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.

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

#38

Post by RyanHamilton »

ihateolives wrote: post #30
RyanHamilton wrote: post #12

Earlier quoted context omitted.

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.

:( Thanks for trying. The only thing I can think is perhaps a version conflict (requires 0.70) but I would have expected it to say, rather than freeze. I will try to recreate the problem. Thanks.

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

#39

Post by orthoxerox »

benjaminwootton wrote: post #33
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.

The benefit of “in process” isn’t really clicking for me. We had Pandas or similar if we wanted to load and transform some data in memory. SQL is nicer than Pandas APIs but not sure that’s a killer feature? If we have a lot of data and multiple people working with it then it makes sense to centralise it in a database or warehouse where it’s then easy to access via SQL anyway. We can query files on S3 with it and have…

Well, I know SQL much better than I know either Scala+Spark or Python+Pandas. DuckDB is also much easier to set up, being a self-contained JAR. I also like how having a DB file means I don't have to worry about losing in-memory dataframes or leaving random temp files all over the filesystem.

In short, it's not about having a single killer feature, but about being a low-friction solution to my problems.

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

#40

Post by pbreit »

chundicus wrote: post #29
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.

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.
Post reply on HN