1. First queries
Sections
Reading settings
Natural voice recorded for this course, the same in every browser.
No voice for this language in this browser.
More voices
Code and English terms
Off: the page’s voice reads everything, with no pause between two voices.
No voice for this language in this browser.
More voices
More voices: Microsoft Edge has natural online voices, and Windows (Settings › Time & language › Speech), macOS and Android install others.
Installing the CLI
Section titled “Installing the CLI”DuckDB is a library: it runs inside the process that uses it, like SQLite. The DuckDB CLI is one such process, a single executable with no dependencies. It’s the quickest way to start, and the one this course uses until lesson 5.
winget install DuckDB.cliOpen a new terminal afterwards so duckdb is on the PATH. The CI of this course downloads duckdb_cli-windows-amd64.zip from the GitHub releases instead, to pin the version: the executable only needs to be unzipped.
curl https://install.duckdb.org | shThe script installs the binary in ~/.duckdb/cli/<version>/, points ~/.duckdb/cli/latest to it, and prints the line to add to your shell profile:
export PATH="$HOME/.duckdb/cli/latest":$PATHIf ~/.local/bin exists, it also creates a duckdb link there. On a GitHub runner: Successfully installed DuckDB 1.5.5 to /home/runner/.duckdb/cli/1.5.5/duckdb. Set DUCKDB_VERSION=1.5.5 before sh to pin the version.
brew install duckdbOr the same script as on Linux, which the CI of this course uses on macos-latest:
curl https://install.duckdb.org | shduckdb --versionv1.5.5 (Variegata) d8cdaa33fdThe data
Section titled “The data”Clone this site’s repository and go to the course folder, where the relative paths of every query in this course work:
git clone https://github.com/spareilleux/learn.gitcd learn/code/duckdbdata/runs.json is one JSON array of 125 objects, one per GitHub Actions run of this repository:
[{"attempt":1,"conclusion":"success","createdAt":"2026-09-14T14:01:09Z","databaseId":34852867099,"event":"push","headBranch":"main","headSha":"95a3830ebbcfa3f0addbc1eba8335a20cc2c41f2","startedAt":"2026-09-14T14:01:09Z","status":"completed","updatedAt":"2026-09-14T14:03:38Z","workflowName":"Deploy to GitHub Pages"}, …A query on a file
Section titled “A query on a file”Start the CLI with duckdb: it answers with its version and Enter ".help" for usage hints., and an in-memory database. Then, without creating anything:
DESCRIBE FROM 'data/runs.json';┌────────────────────────┐│ data/runs.json ││ ││ attempt bigint ││ conclusion varchar ││ createdAt timestamp ││ databaseId bigint ││ event varchar ││ headBranch varchar ││ headSha varchar ││ startedAt timestamp ││ status varchar ││ updatedAt timestamp ││ workflowName varchar │└────────────────────────┘A file name in quotes is a table. DuckDB saw the .json extension, read the file, and inferred a type for each field: bigint for the numbers, timestamp for the ISO 8601 strings, varchar for the rest. In SQL Server, the closest thing is OPENROWSET(BULK …) with OPENJSON and an explicit WITH clause listing the columns and their types.
DESCRIBE FROM 'data/runs.json' is also a first taste of DuckDB’s friendly SQL: FROM can come first, and a query with only FROM means SELECT *.
The first query of sql/01-first-queries.sql: which workflows run most, and how often they fail.
From sql/01-first-queries.sql, lines 2-6:
SELECT workflowName, count(*) AS runs, count(*) FILTER (conclusion = 'failure') AS failuresFROM 'data/runs.json'GROUP BY ALLORDER BY runs DESC, workflowNameLIMIT 5;┌────────────────────────┬───────┬──────────┐│ workflowName │ runs │ failures ││ varchar │ int64 │ int64 │├────────────────────────┼───────┼──────────┤│ Deploy to GitHub Pages │ 65 │ 2 ││ Rust course examples │ 18 │ 3 ││ Java course examples │ 7 │ 0 ││ GHA 03: triggers │ 5 │ 2 ││ GHA 07: security │ 4 │ 0 │└────────────────────────┴───────┴──────────┘Two extensions to the SQL you know:
GROUP BY ALLgroups by every column of theSELECTthat isn’t an aggregate. No more copying the column list intoGROUP BY.FILTERrestricts one aggregate. The SQL Server way issum(CASE WHEN conclusion = 'failure' THEN 1 ELSE 0 END).FILTERis standard SQL, and PostgreSQL has it too.
LIMIT is TOP at the end of the query. The output format is duckbox, the CLI’s default: column types under the names, and numbers aligned right.
Reading the errors
Section titled “Reading the errors”A PostgreSQL habit, snake_case column names:
SELECT workflow_name FROM 'data/runs.json';Binder Error: Referenced column "workflow_name" not found in FROM clause!Candidate bindings: "workflowName", "conclusion"
LINE 1: SELECT workflow_name FROM 'data/runs.json'; ^A Binder Error means the SQL parsed, but a name doesn’t resolve. DuckDB suggests the closest names and points at the position. A table that doesn’t exist yet gives a Catalog Error:
FROM runs;Catalog Error: Table with name runs does not exist!Did you mean "duckdb_constraints"?
LINE 1: FROM runs; ^The suggestion is the closest name among the system views: a guess, not a hint that something is wrong with duckdb_constraints.
First look at the data: SUMMARIZE
Section titled “First look at the data: SUMMARIZE”SUMMARIZE computes statistics for every column. It returns 12 columns, too wide for this page; since its result is a table like any other, a query can pick some:
SELECT column_name, column_type, min, max, approx_unique, null_percentageFROM (SUMMARIZE FROM 'data/runs.json');┌──────────────┬─────────────┬──────────────────────────────────────────┬──────────────────────────────────────────┬───────────────┬─────────────────┐│ column_name │ column_type │ min │ max │ approx_unique │ null_percentage ││ varchar │ varchar │ varchar │ varchar │ int64 │ decimal(9,2) │├──────────────┼─────────────┼──────────────────────────────────────────┼──────────────────────────────────────────┼───────────────┼─────────────────┤│ attempt │ BIGINT │ 1 │ 3 │ 2 │ 0.00 ││ conclusion │ VARCHAR │ cancelled │ success │ 4 │ 0.00 ││ createdAt │ TIMESTAMP │ 2026-09-13 15:53:16 │ 2026-09-14 14:01:09 │ 93 │ 0.00 ││ databaseId │ BIGINT │ 34766934801 │ 34852867099 │ 131 │ 0.00 ││ event │ VARCHAR │ push │ workflow_dispatch │ 2 │ 0.00 ││ headBranch │ VARCHAR │ gha-06-invalid │ main │ 2 │ 0.00 ││ headSha │ VARCHAR │ 04c8770830c5666ca1e9bb8a4711065edfb351b4 │ fd52d46d1d2940e5dee33790e4a2873056f074f3 │ 59 │ 0.00 ││ startedAt │ TIMESTAMP │ 2026-09-13 15:53:16 │ 2026-09-14 14:01:09 │ 94 │ 0.00 ││ status │ VARCHAR │ completed │ completed │ 1 │ 0.00 ││ updatedAt │ TIMESTAMP │ 2026-09-13 15:53:55 │ 2026-09-14 14:03:38 │ 122 │ 0.00 ││ workflowName │ VARCHAR │ Deploy to GitHub Pages │ WSL containers examples │ 17 │ 0.00 │└──────────────┴─────────────┴──────────────────────────────────────────┴──────────────────────────────────────────┴───────────────┴─────────────────┘ 11 rows 6 columnsWhat this says in a few seconds:
- 125 rows and no
NULLanywhere: the snapshot only has completed runs (statushas one value). - The history covers less than a day: from 2026-09-13 15:53 to 2026-09-14 14:01.
attemptgoes up to 3: at least one run was re-run twice.headBranchhas two values:main, and the branch of an experiment from the GitHub Actions course.approx_uniqueis an estimate.databaseId, the run id, is unique, yet 131 is more than the 125 rows. It uses HyperLogLog, which trades exactness for constant memory. Exercise 2 finds the exact counts.minandmaxarevarcharfor every column, so they fit in one result column:maxofattemptis the string'3'.
The timestamps in the file end with Z (UTC). DuckDB read them as TIMESTAMP, without a time zone: the values are the UTC times, and nothing in the type says so. Lesson 2 comes back to this.
Keeping the rows: a table
Section titled “Keeping the rows: a table”Each query above read and parsed the JSON file again. To read it once, store the rows in a table (CREATE TABLE … AS, the SELECT INTO of SQL Server) (sql/01-first-queries.sql, lines 8-13):
CREATE TABLE runs AS SELECT * FROM 'data/runs.json';
SELECT event, conclusion, count(*) AS runsFROM runsGROUP BY ALLORDER BY event, conclusion;┌───────────────────┬─────────────────┬───────┐│ event │ conclusion │ runs ││ varchar │ varchar │ int64 │├───────────────────┼─────────────────┼───────┤│ push │ cancelled │ 2 ││ push │ failure │ 7 ││ push │ startup_failure │ 1 ││ push │ success │ 96 ││ workflow_dispatch │ cancelled │ 2 ││ workflow_dispatch │ failure │ 9 ││ workflow_dispatch │ success │ 8 │└───────────────────┴─────────────────┴───────┘Manual runs (workflow_dispatch) failed 9 times out of 19; pushes, 7 out of 106. That matches how the GitHub Actions course was written: the exercise workflows, started by hand, were designed to fail.
The table lives in the in-memory database, and disappears when the CLI exits.
Keeping the database: a file
Section titled “Keeping the database: a file”The file name is the first argument of duckdb. The file is created if it doesn’t exist:
mkdir outduckdb out/ci.duckdbCREATE TABLE runs AS FROM 'data/runs.json';.quitStart it again with the same file, and the table is still there:
duckdb out/ci.duckdbSELECT count(*) FROM runs;SELECT database_name, path FROM duckdb_databases() WHERE NOT internal;┌──────────────┐│ count_star() ││ int64 │├──────────────┤│ 125 │└──────────────┘┌───────────────┬─────────────────────────────────────────────────────────────┐│ database_name │ path ││ varchar │ varchar │├───────────────┼─────────────────────────────────────────────────────────────┤│ ci │ C:\Users\spare\source\repos\learn\code\duckdb\out\ci.duckdb │└───────────────┴─────────────────────────────────────────────────────────────┘The database is named after the file. The .duckdb extension is a convention; DuckDB doesn’t require it.
Writing files: COPY
Section titled “Writing files: COPY”COPY … TO writes a table or a query result to a file, and picks the format from the extension:
COPY runs TO 'out/runs.csv';COPY runs TO 'out/runs.parquet';The sizes, for the same 125 rows:
| File | Bytes |
|---|---|
data/runs.json |
39,705 |
out/runs.csv |
21,058 |
out/runs.parquet |
8,553 |
out/ci.duckdb |
536,576 |
Parquet is a compressed, columnar file format with types, and the one lesson 4 recommends for data you keep. The .duckdb file is the largest. A database file with nothing in it is 12,288 bytes of headers; data is stored in blocks of 262,144 bytes (block_size in PRAGMA database_size), and this one has two: 12,288 + 2 × 262,144 = 536,576. The format is made for millions of rows, not for 125.
COPY doesn’t create folders. Without the mkdir out above:
IO Error: Cannot open file "out/runs.parquet": The system cannot find the path specified.The second half of that message is the Windows system error text.
Two CLI settings
Section titled “Two CLI settings”Dot commands configure the CLI itself. They are not SQL, and take no semicolon (list):
.timer onSELECT count(*) FROM 'out/runs.parquet';┌──────────────┐│ count_star() ││ int64 │├──────────────┤│ 125 │└──────────────┘Run Time (s): real 0.001 user 0.000000 sys 0.000000On 125 rows, every query takes about a millisecond: the timer only gets interesting in lesson 7. .mode changes the output format; .mode line shows one field per line, handy for wide rows:
.mode lineFROM 'data/runs.json' LIMIT 1; attempt = 1 conclusion = success createdAt = 2026-09-14 14:01:09 databaseId = 34852867099 event = push headBranch = main headSha = 95a3830ebbcfa3f0addbc1eba8335a20cc2c41f2 startedAt = 2026-09-14 14:01:09 status = completed updatedAt = 2026-09-14 14:03:38workflowName = Deploy to GitHub PagesScripts and CSV output: how this course is tested
Section titled “Scripts and CSV output: how this course is tested”The CLI also reads SQL from standard input, and -csv switches the output to CSV. check.sh runs every script of sql/ that way and compares the result with the file of the same name in sql/expected/:
duckdb -csv < sql/01-first-queries.sqlworkflowName,runs,failuresDeploy to GitHub Pages,65,2Rust course examples,18,3Java course examples,7,0GHA 03: triggers,5,2GHA 07: security,4,0event,conclusion,runspush,cancelled,2push,failure,7push,startup_failure,1push,success,96workflow_dispatch,cancelled,2workflow_dispatch,failure,9workflow_dispatch,success,8On a GitHub runner, for each of the three operating systems: ok 01-first-queries. -c runs a single command and exits, for one-liners in a terminal: duckdb -c "FROM 'data/runs.json' LIMIT 3".
Key takeaways
Section titled “Key takeaways”- DuckDB is a library that runs in your process; the CLI is one executable, and needs no server.
- A quoted file name is a table: DuckDB infers the format from the extension and the types from the content.
DESCRIBEandSUMMARIZEare the first two commands to run on unknown data;approx_uniqueis an estimate.- A table stays in memory unless the CLI was started with a database file;
COPY … TOwrites CSV, JSON or Parquet. - Errors name their kind (
Binder,Catalog,IO) and suggest the closest names.
Exercises
Section titled “Exercises”The solutions are in sql/01-exercises.sql, checked by CI like the lesson.
- How many manual runs (
workflow_dispatch) failed?
Solution
From sql/01-exercises.sql, lines 3-5:
SELECT count(*) AS failed_manual_runsFROM 'data/runs.json'WHERE event = 'workflow_dispatch' AND conclusion = 'failure';┌────────────────────┐│ failed_manual_runs ││ int64 │├────────────────────┤│ 9 │└────────────────────┘The same number as the workflow_dispatch / failure row of the table above.
SUMMARIZEestimates 17 distinct workflow names and 59 distinct commits. What are the exact numbers?
Solution
From sql/01-exercises.sql, lines 8-9:
SELECT count(DISTINCT workflowName) AS workflows, count(DISTINCT headSha) AS commitsFROM 'data/runs.json';┌───────────┬─────────┐│ workflows │ commits ││ int64 │ int64 │├───────────┼─────────┤│ 21 │ 65 │└───────────┴─────────┘21 and 65: the estimate was low by about 10% on both, and high by 5% on the run ids. count(DISTINCT …) is exact but has to remember every value it has seen; approx_count_distinct(…) gives the same kind of estimate as SUMMARIZE in constant memory. On 125 rows, use the exact one.
- The Binder Error above suggested
workflowName. Wouldworkflowname, all lowercase, work? And what would the column be called in the result?
Solution
From sql/01-exercises.sql, lines 12-17:
SELECT workflowname, count(*) AS runsFROM 'data/runs.json'WHERE workflowname LIKE 'GHA 0%'GROUP BY ALLORDER BY workflownameLIMIT 3;┌─────────────────────────┬───────┐│ workflowName │ runs ││ varchar │ int64 │├─────────────────────────┼───────┤│ GHA 01: hello │ 1 ││ GHA 02: build and test │ 3 ││ GHA 02: exercise checks │ 1 │└─────────────────────────┴───────┘Yes. Identifiers are case-insensitive in DuckDB, “similarly to PostgreSQL”, and also in double quotes, unlike PostgreSQL where "workflowName" and workflowname are two different names. The comparison is ASCII-only: é and É are different letters for it. DuckDB preserves the case of the name as it was defined, so the result column is workflowName, not the case typed in the query. workflow_name failed because the underscore makes it a different name, not because of the case.