Database

I reach for JQ almost every day. It’s fast, it’s everywhere, and for quick JSON inspection it’s hard to beat. But somewhere along the way I started using DuckDB for anything more complex, and I don’t plan to go back. Here’s how it works and when to use it. Getting Started Basic Setup First, ensure you have DuckDB installed # On macOS brew install duckdb # On Linux, Windows, or for you favourite programming language: # Follow instructions here: https://duckdb.org/docs/installation/ Sample Data We’ll use this example JSONL file (data.jsonl) throughout our examples: ...

Motivation The motivation for this article comes from my time at a comically dysfunctional mobile game studio and particularly a rather brash “VP of Core Tech”. This particular “leader” once went on at length longly, loudly, and very publicly, about how poor a decision it was for the analytics department I was leading to choose BigQuery a “SQL based database” to use as our data lake. This was confusing for a few reasons. Firstly our team and analytics more broadly was in now way shape or form connected to his division, responsibility, or his expertiese. Secondly, given that we had chosen BigQuery (which is built for the sort of workloads we were running), I was deeply confused by his rantings. That is to say, I was deeply confused until I realised he had conflated SQL with relational database management systems typically used for CRUD (Create, Read, Update, and Delete) operations. BigQuery and more broadly Columnar Databases are many things but that is absolutely not one of them, and by the end of this article you will understand why, when to use a tool like this, and very importantly when not to. ...