News for hackers. Stuff that matters.
Re:DuckDB: Querying JSON files as if they were tables by mkaic on Friday March 03, 2023 @12:26PM (#35012551)
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 by piperswe on Friday March 03, 2023 @12:29PM (#35012601)
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 by sidpatil on Friday March 03, 2023 @01:47PM (#35013493)
> 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 by mytherin on Friday March 03, 2023 @12:47PM (#35012810)
Perhaps have a look at this article [1]

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

Re:DuckDB: Querying JSON files as if they were tables by nkh on Friday March 03, 2023 @02:20PM (#35013830)
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.