Skip to main content
This feature requires StarTree Cloud 0.18.0 or later. Earlier releases reject derived columns on External Tables.
A derived column is a column that isn’t in the source Parquet files. Pinot computes it from columns that are, using a transform function such as dateTrunc, upper or times. A common use is folding a timestamp into a coarser bucket you can group by, or rebuilding a single value from several partition columns. Once it exists, a derived column behaves like any other Pinot column. You can filter on it, group by it, and put indexes on it.

How it works

An External Table normally reads only the Parquet footers when it onboards a file. Derived columns keep it that way.
  1. Onboarding. The segment built for each file leaves the derived columns out, so onboarding still reads only the footer. Adding derived columns doesn’t make onboarding slower.
  2. Segment load. When a server loads the segment, it sees the derived column as missing, reads the source columns it needs from the Parquet file, and computes the values.
  3. Query. The result is an ordinary Pinot column. It can be dictionary-encoded, indexed and pruned.
The cost moves to segment load. Each derived column reads its source columns from object storage when a server loads the segment, so a table with many derived columns over wide source columns takes longer to come online.

Declare a derived column

You need two things:
  • a transformConfigs entry under ingestionConfig in the table config, with the column name and the transform;
  • a field for the column in the schema, with its data type.
For example, to add a day bucket over a millisecond timestamp:
And in the schema:
Declare derived columns only in ingestionConfig.transformConfigs. A transformFunction set on the schema field itself is rejected, because the External Table load path never reads it.

Encoding and indexes

Physical columns on an External Table must be RAW. A derived column can be RAW or DICTIONARY, because Pinot builds it itself instead of reading it from Parquet. Add a fieldConfig for it as you would on a regular table, and put the indexes you need on it.

Rules

Table creation and every sync check the following. A table that breaks one fails at create time, with all the errors listed together, not later at segment load.

Change or remove a derived column

The values are computed when a segment loads, so a change to the transform reaches a segment the next time it loads. Reload the table’s segments to apply the change everywhere. To remove a derived column, drop its transformConfigs entry and its schema field, then reload.

Limits

  • A derived column can’t be the table’s time column.
  • A derived column can’t drive Iceberg partition pruning. The importer skips columns that are the output of an ingestion transform.
  • Segments are only as fast to load as their source reads. A derived column over a wide or high-cardinality source column adds that column’s read to every segment load.
  • The server that computes the column must run 0.18.0 or later. Upgrade servers before you add derived columns.

Common errors