Using DuckDB to Query Data Stored as YAML With read_yaml

DuckDB queries files in place: CSV, JSON, Parquet. YAML is not in that list in the core, but a community extension adds it, and the result is the shortest path from a folder of YAML to a SQL answer. This post installs it, reads a file, shows what the type inference does, and covers the failure modes, based on a run with DuckDB 2.0.0 and the extension version that installed. It belongs to the cluster on using YAML with databases. The SQLite route, which converts to JSON first, is reading YAML into SQLite for quick queries.

Install and load

INSTALL yaml FROM community;
LOAD yaml;

INSTALL downloads the extension once, which needs network access, and LOAD activates it in the session. After loading, duckdb_functions() lists what it added: the table functions read_yaml, read_yaml_objects, read_yaml_frontmatter and parse_yaml, scalar functions such as yaml_to_json, yaml_contains and yaml_keys, an aggregate yaml_agg, and settings functions such as yaml_set_max_expansion_nodes and yaml_set_max_nesting_depth. A community extension is third-party code that runs inside your database process, so install it only from the community repository and pin the DuckDB version in automation.

Read a file

The file is a list of two services:

- name: api
  port: 8080
  tags: [web, public]
  limits: {cpu: 2, memory: 512}
- name: worker
  port: 9000
  tags: [queue]
  limits: {cpu: 1}

A list at the root becomes one row per element:

SELECT * FROM read_yaml('svc.yaml');

|  name  | port |     tags      |           limits           |
|--------|-----:|---------------|----------------------------|
| api    | 8080 | [web, public] | {'cpu': 2, 'memory': 512}  |
| worker | 9000 | [queue]       | {'cpu': 1, 'memory': NULL} |

DESCRIBE on the same query shows the types DuckDB chose: name is VARCHAR, port is SMALLINT, tags is VARCHAR[] and limits is STRUCT(cpu TINYINT, memory SMALLINT). The inference samples the file and picks the narrowest type that fits. That is compact and has one consequence to remember: the types come from the sample. If a later file holds a port of 70000, a SMALLINT is too small, so for repeated jobs fix the schema explicitly or read with enough samples (the function takes sample_size). The worker has no memory, and its struct field is NULL.

Use the structure in SQL

A struct column is addressed with a dot, and a list column with the usual list functions:

SELECT name, port, limits.cpu AS cpu, limits.memory AS memory, tags
FROM read_yaml('svc.yaml') ORDER BY port;

|  name  | port | cpu | memory |     tags      |
|--------|-----:|----:|-------:|---------------|
| api    | 8080 | 2   | 512    | [web, public] |
| worker | 9000 | 1   | NULL   | [queue]       |

unnest turns a list into rows:

SELECT name, unnest(tags) AS tag FROM read_yaml('svc.yaml') ORDER BY 1, 2;

|  name  |  tag   |
|--------|--------|
| api    | public |
| api    | web    |
| worker | queue  |

Aggregates work as you expect: SELECT count(*) AS services, sum(limits.cpu) AS cpus FROM read_yaml('svc.yaml') returned 2 and 3. Filtering on a list uses list_contains(tags, 'web'), which returned only the api row. No loop, no loader script, and no intermediate JSON file.

Many files

read_yaml takes a glob or a list of paths. Two small files with different keys, web (name, port) and db (name, port, replicas):

SELECT * FROM read_yaml('svcs/*.yaml') ORDER BY name;

| name | port | replicas |
|------|-----:|---------:|
| db   | 5432 | 2        |
| web  | 80   | NULL     |

The result has the union of columns, with NULL where a file lacks a key. That behavior makes cross-file questions direct: "which files do not set replicas" is WHERE replicas IS NULL. Multi-document files return one row per document by default, and multi_document := false kept only the first document of a two-document file (the call returned a, 1 and not b, 2).

Mixed types stay as written

The type inference matters most when a column mixes things YAML parsers disagree about. A list of three records:

- {id: 1, v: "3.10"}
- {id: 2, v: 3.10}
- {id: 3, v: no}

DESCRIBE reported id as TINYINT and v as type yaml. The values came back as written:

| id |  v   |
|---:|------|
| 1  | 3.10 |
| 2  | 3.10 |
| 3  | no   |

DuckDB did not coerce the column to a float, which would have turned 3.10 into 3.1, or no into a boolean. When values of different kinds share a column, the extension keeps the text and a yaml type, which leaves the decision to you. This is the opposite of what PyYAML does with the same file, and it is a good property for auditing: you see the raw text, including the quoting mistakes described in YAML version numbers turning into floats. The yaml_type function reports the type of a value if you need it.

Broken files

A syntax error in one file stops the query by default:

IO Error: Error processing YAML file 'broken.yaml': {"exception_type":"IO","exception_message":"Error parsing multi-document YAML file: yaml-cpp: error at line 2, column 5: end of sequence flow not found"}

The message names the file, the parser (yaml-cpp, a C++ library) and the line and column. With ignore_errors := true, a list of one good and one broken file returned the rows of the good file and no error. That is convenient for exploration and dangerous for a report: a skipped file is a silent gap. Count the files you expected against the files you got, or run without ignore_errors in anything that matters.

Writing YAML back out

The extension also writes. COPY (SELECT 'api' AS name, 8080 AS port) TO '/tmp/o.yaml' (FORMAT yaml) produced:

name: api
port: 8080

That makes a round trip possible, query then write, for tables of reasonable size. Be careful with the result as an editing path, since a generated file loses the comments and layout of a hand-written one, as in round tripping YAML without losing comments.

Beyond the extension: other routes

If you cannot install a community extension, three alternatives exist. Convert with yq -o=json and use read_json, which is core DuckDB (querying JSON files with DuckDB covers it). Load with PyYAML in Python and pass a pandas DataFrame or a list of dictionaries to DuckDB (duckdb.sql("select * from df")). Or read the file as text with read_text and parse in SQL, which is only for trivial cases. The JSON route is the most portable, and it makes the type decisions the YAML parser's, so converting YAML to JSON in Python applies.

When the DuckDB YAML extension is not the right tool

You need exact YAML semantics. The extension is a third-party implementation on yaml-cpp. For anchors, tags and 1.1 versus 1.2 details, test with your own files against the parser your application uses.

The files are untrusted. Parsing is bounded by settings such as yaml_set_max_expansion_nodes and yaml_set_max_nesting_depth, but you should read what they default to for your version and set them. The issue is covered in limiting alias expansion in a YAML parser.

You cannot install extensions. Locked-down environments may block the download. Use the JSON route.

The data is a single small file. yq answers a one-off question without a database.

The extension is listed in the DuckDB community extensions catalog, and the core feature set in the DuckDB documentation. For a quick look at a file's structure first, the YAML formatter works in the browser.