Rows vs Columns
Concepts covered: Distributed Primitives
Most analytical queries touch a handful of columns across millions of rows: total revenue by country, average session length by device. For storage, what matters is which bytes sit next to each other on disk, because a scan reads contiguous runs of bytes and pays for every byte it passes over.
One table, two byte orders
Take 4 rows of a clickstream table called events. As a table they look like this:
| event_id | user_id | country | event_type | dur_ms |
|---|---|---|---|---|
| 1001 | 48211 | BR | click | 5120 |
| 1002 | 48211 | BR | scroll | 870 |
| 1003 | 30777 | US | click | 2310 |
| 1004 | 91002 | IN | purchase | 15400 |
A file has to put these 20 values in a single sequence. A row-oriented file writes every field of row 1 before it starts on row 2. CSV and line-delimited JSON work this way. A column-oriented file writes all 4 values of event_id before any value of user_id, and carries on column by column through dur_ms.
Now run a query that needs 2 columns: the average dur_ms per country.
events.groupBy("country").avg("dur_ms").collect()In the row layout, the country of row 3 sits between that row's user_id and event_type, and its dur_ms comes right after. The wanted values are scattered through every block, so the scan reads every block and discards 3 fields out of 5. In the column layout, the wanted values fill the country block and the dur_ms block, which sit contiguously, and the scan never touches the other 3.
How rows come back together
Splitting a table into column blocks loses nothing about which values belong together. The 3rd value in the country block and the 3rd value in the dur_ms block both belong to row 3, so the reader rebuilds rows by position: (US, 2310) for row 3. Spark receives ordinary rows holding only the 2 requested columns, and the rest of the job runs as it would on any DataFrame.
Why the scan cannot hop over fields
Disks and object stores serve reads as ranges of bytes, and each request carries a fixed delay before its first byte arrives. Picking country out of a row file would take 1 small request per row, billions of them, each paying that delay. Reading the whole file in order is cheaper than that, so a row-oriented scan always reads everything. A column file turns the same need into a few large requests, 1 per column block.
At production scale
Suppose the real events table has 30 columns of roughly equal width and 20 billion rows, and the daily report needs country and event_type along with dur_ms. A row-oriented scan reads all 30 columns of every row. A columnar scan fetches the 3 column blocks the report names and leaves the rest on disk.
- 27
- 10x
What rows are still good for
- Row layout wins when
- A program writes or reads a whole record at a time
- An application looks up a single order by its id
- Records arrive one by one and must land immediately
- A partner system expects a file it can open in a text editor
- Column layout wins when
- A query aggregates a few columns over many rows
- Tables are wide and most queries use a small slice
- Data is written once in bulk and read many times
- Storage size and scan time drive the bill
A 40-column table with columns of roughly equal width is stored once as CSV and once in a columnar format. A query averages 1 column grouped by another. Roughly how much of each file does the scan read?
- Both read about 5% of the file, because Spark parses only the 2 columns the query names
- The CSV scan reads all of it, and the columnar scan reads 2 of 40 column blocks, about 5%
- The columnar scan reads all of it, because columnar files must be read whole to rebuild rows
- Both read all of it, because storage cannot serve bytes from the middle of a file
About this lesson
This section is part of the Storage Formats: Beginner 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.