Columnar Storage & Nesting
Row storage writes each row's values next to each other, so reading 1 column means reading every row in full. Columnar storage writes each column's values together, in chunks, with the minimum and maximum of each chunk recorded in its footer. A query that touches 2 of 40 columns reads 2 streams, and a filter such as WHERE country = 'PT' skips every chunk whose min and max rule it out. Most analytical tables are stored this way, and nested columns get the same treatment, with a twist.
Nested columns are stored as leaves
The file format splits every nested column into its leaf fields and writes each leaf as its own stream, the same way it writes a top-level column. Next to each value it records how many levels of nesting are present for that row and whether the value starts a new list, which is enough to rebuild the row and enough to read 1 leaf on its own.
- listings: listing_id BIGINT, location ROW(city, country, geo ROW(lat, lon)), amenities ARRAY(VARCHAR), photos ARRAY(ROW(url, caption)), attributes MAP(VARCHAR, VARCHAR)
| Declared column | Leaf streams on disk |
|---|---|
| listing_id BIGINT | listing_id |
| location ROW(city, country, geo ROW(lat, lon)) | location.city, location.country, location.geo.lat, location.geo.lon |
| amenities ARRAY(VARCHAR) | amenities.element |
| photos ARRAY(ROW(url, caption)) | photos.url, photos.caption |
| attributes MAP(VARCHAR, VARCHAR) | attributes.key, attributes.value |
- 10
- 1
- 2
The consequences follow from the table. WHERE location.city = 'Lisbon' reads the location.city stream and nothing else, and its chunk statistics let the engine skip chunks that hold no Lisbon. Counting photos with CROSS JOIN UNNEST(photos) reads at most the photos.url stream, since the list markers beside it say where each listing's photos begin and end. Reading a field of a struct costs the same as reading a top-level column of the same type.
Maps break the pattern
A map has only 2 leaf streams, keys and values, shared by every key that will ever appear. To evaluate element_at(attributes, 'color') the engine reads the entire key stream, scans each row's keys for a match, and pulls the matching position from the value stream. The chunk statistics on the value stream cover ram sizes, shirt sizes and colors mixed together, so a filter on one key skips nothing. A struct field has its own stream and its own statistics, which is the whole difference.
| Operation | On a struct field | On a map key |
|---|---|---|
| Read 1 field | Reads 1 leaf stream | Reads the key stream and the value stream |
| Filter on 1 field | Skips chunks by min and max | Scans every row in every chunk |
| Add a field | Schema change, old rows read NULL | Write the key, no schema change |
This is why the monthly key-frequency query from the previous section matters. A key like pet_policy that sits on 80 percent of listings and appears in the search filter has outgrown the map. Promoting it to a top-level column gives it a stream and statistics, and the map keeps the long tail of rare keys where flexibility earns its cost.
JSON text is a different thing
A VARCHAR column holding JSON looks nested and stores as 1 stream of text. To answer json_extract_scalar(payload, '$.location.city') the engine reads every byte of every payload and parses it row by row, work that grows with the size of the whole document rather than the field asked for. Typed nested columns hand the engine the leaves; JSON text makes it dig for them on each query. A raw landing table can hold JSON text, and the first transformation should cast it into declared structs, arrays and maps.
- Do
- Declare structs for fields every row has, and read them by name
- Cast JSON text into typed nested columns in the first transformation
- Promote map keys that appear in filters to their own columns
- Cap embedded arrays, and move unbounded lists to a table
- Avoid
- Store numbers as text inside a MAP(VARCHAR, VARCHAR) and compare them
- Filter on a map key inside a hot query and expect chunk skipping
- Keep a 200-field struct where most fields are NULL on most rows
- Use array subscripts on arrays whose length you do not control
About this lesson
This section is part of the Nested Data lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.