DBTrail

Query with DuckDB

Four ways to query the Parquet copy with DuckDB: reading S3 from your computer, from downloaded files, on the DBTrail machine, or inside its Docker container.

You need:

  • DuckDB installed on the machine that runs the queries.
  • The views.sql file. It tells DuckDB where each Parquet file is, so every table becomes a view with the table's own name: shop.orders. Each of the four ways below says where to get it.

Four ways to do it

All four open the same copy. They differ in speed and convenience: the closer DuckDB sits to the files, the faster the queries; the more current you want the data, the more access you need.

Four ways. One, your computer reading the copy in S3, live from anywhere, needs AWS access. Two, your computer with the files taken along, fast and offline, download again to refresh. Three, the DBTrail machine reading the folder it already keeps, the fastest, nothing to set up. Four, inside the box, where the folder lives in a container and the recipe is in the repo.
WhereSpeedCurrent data?What it takes
1. Your computer, reading S3Slowest: every query goes over the internetYesAn S3 location and AWS access
2. Your computer, files downloadedFast: local diskNo, the moment you downloadedNothing. Start here
3. The DBTrail machineFastest: the files are right thereYesA shell on that machine, and a non-Docker install
4. Inside the Docker boxFastYesOne docker run on that machine

1. Your computer, reading S3

Settings → MCP Server → Download a DuckDB schema → tick Works on another machine (it shows when the server keeps snapshots in S3) → download views.sql. It uses the AWS access your machine already has.

duckdb -init views.sql lake.db

Use DuckDB 1.5 or newer: the file then opens in about 9 seconds over the internet for 17 tables. Older versions take closer to a minute. To move to a newer snapshot in a session that is already open, read the file again (.read views.sql).

Someone else should read the bucket too? Give them the read-only policy.

2. Your computer, files downloaded

Snapshots → Download → Download the data. Unpack it and open DuckDB inside the folder it creates:

tar xzf dbtrail-backup-*.tar.gz
cd <the folder it created>
duckdb -init views.sql lake.db

What you downloaded is the copy at that moment. For newer data, download again.

3. The DBTrail machine

Settings → MCP Server → Download a DuckDB schema, with Works on another machine unticked. Then, on that machine, duckdb -init views.sql lake.db.

On the Docker install the files live inside a Docker volume, so this file's paths do not exist on the machine itself: use option 4.

4. Inside the Docker box

Get views.sql as in option 3 and put it in the folder with docker-compose.yml on the server. Then, in that folder:

docker run --rm -it \
  -v "$(basename "$PWD")_bintrail-state:/var/lib/bintrail:ro" \
  -v "$PWD:/work" \
  duckdb/duckdb -init /work/views.sql /work/lake.db

Keep it current

Options 1, 3 and 4 follow the copy as DBTrail updates it, once the schedule is on: Snapshots, tab Settings, card Update the copy, pick how often, Turn on. It is off until you do. An S3 location alone works too (since 0.101.0); before that version, add a Local folder, or every update reads your whole database again. Download views.sql again when a table is added, dropped or changes shape. How the updates work: How the Parquet copy works.

An error opening the copy: Troubleshooting. Charts on top of it: Dashboards.

Architecture

What DBTrail is made of, what connects to what, what it never does to your database, and what stays on your side.

Query Parquet with MySQL

Use the MySQL client you already have to query DBTrail's copy of your tables, on port 3309, with no load on your MySQL.

On this page

Four ways to do it1. Your computer, reading S32. Your computer, files downloaded3. The DBTrail machine4. Inside the Docker boxKeep it current