Write a columnar file

Status

Variants

Write another columnar format

Write a columnar format outside the common ones, such as Vortex.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

Row-oriented formats are more commonly used in online transaction processing (OLTP), and column-oriented formats in online analytical processing (OLAP).^[1] In Apache Parquet, queries that fetch specific column values need not read the entire row.^[2] Reach for a columnar file when the data will be read many times by analytical queries, and keep a row-oriented format for data that arrives and changes a row at a time. (Meridian's view)

DuckDB's COPY ... TO statement exports data from DuckDB to an external file, Parquet files included.^[3] In DuckDB, COPY ... TO overwrites the destination file if it already exists, without raising an error.^[3] By default, DuckDB writes to a temporary file first and replaces an existing destination only once the write completes successfully, so an interrupted write does not corrupt the previous file.^[3] In DuckDB, reading from and writing to the same file in a single statement is not guaranteed to be safe.^[3] Treat a columnar file as something written whole and replaced whole, and write it to a path that no query in the same statement reads. (Meridian's view)

The method

Tabular data is two-dimensional, but computer systems represent data in a linear memory model, so a table has to be mapped into a one-dimensional space.^[1] Data orientation is the decision taken in mapping a table into linear memory, and the two prominent mappings are row-oriented and column-oriented.^[1] A column-oriented layout benefits from a smaller compressed size, as a result of higher homogeneity within a column than within multiple rows.^[1] A column-oriented layout can also use dedicated encodings, such as a bitmap for a Boolean column or run-length encoding of a column.^[1]

Parquet is widely used as the underlying file format in cloud-based data lake architectures.^[2] In data lakes built on Iceberg, Delta Lake or Hudi, Parquet files serve as the immutable storage layer while the table formats manage data versioning and transactional integrity.^[2]

Compared with the alternatives

Predominant examples of row-oriented formats include comma-separated values (CSV) files and the formats used in most relational databases.^[1] Because row and column orientations represent the same data, a row-oriented dataset can be converted to a column-oriented one and back at the expense of compute.^[1] Advanced query engines often convert between orientations during execution: an Apache Spark query may read Parquet, load it into Spark's row-oriented in-memory format and convert it to Arrow for a specific computation.^[1] A CSV export suits a person who will open the file in a spreadsheet; a columnar file suits the next query. (Meridian's view)

Sources

1. [1]Data orientation - Wikipediasecondary source
2. [2]Apache Parquet - Wikipediasecondary source
3. [3]COPY Statement – DuckDBdocumentation

Used in