VARIANT columns (OPEN_STRUCT)

VARIANT columns on External Tables are available in StarTree Cloud 0.18.0 and later.

An Iceberg v3 variant column, or a Parquet column with the VARIANT annotation, becomes a Pinot OPEN_STRUCT column on an External Table. Read a key with col['key'] and the whole document with SELECT col. Keys the writer shredded into typed Parquet columns are read from those columns directly; every other key is read from the variant value bytes.

What changes

Type mapping. Onboarding maps variant to OPEN_STRUCT. No key types are declared up front: each file’s footer tells Pinot which keys exist as typed columns. See Data Type Mapping.
Parquet without the annotation. A Parquet group that has a metadata leaf plus a value and/or typed_value field is also treated as VARIANT, so files from writers that predate the annotation work too.
Key access. props['page_type'] works in SELECT, WHERE and aggregates. Nested keys use a dotted path: props['device.os'].
Whole document. SELECT props returns one JSON object with every key: shredded, unshredded, nested objects and arrays.
No data is copied. The column stays in Parquet. Pinot reads it in place, the same as any other External Table column.

The writer’s shredding decides how each key is stored, and that decides how fast it can be filtered:

Shape in the source file	Has its own column in Pinot	Read from	How to query
Shredded scalar key (`page_type`)	Yes	its typed Parquet column	`props['page_type']`
Shredded nested object (`device`)	Its leaves do (`device.os`)	the leaf columns; the object is also kept whole	`props['device.os']`, `props['device']`
Shredded array, any depth (`tags`, `items`)	No	rebuilt as JSON text on read	`props['items']`, `JSON_EXTRACT_SCALAR(...)`
Unshredded key (`user_tier`)	No	the variant value bytes	`props['user_tier']`

A shredded key keeps the type of its Parquet column (INT, LONG, STRING, and so on). An unshredded key, and an array, read as text. Which keys are shredded is decided per file. Two files of the same table can store the same key differently. Query results are the same either way; only speed and index use differ.

Requirements

StarTree Cloud 0.18.0 or later.
An Iceberg table at format version 3, or Parquet files whose column is a VARIANT group.
The root of each variant value must be an object. A row whose variant is an array, a scalar or a top-level null can’t be read (see Limits).
Every External Table column stays RAW encoded, including the OPEN_STRUCT column.

Enable or disable

There is nothing to turn on. When the External Table is created from the catalog (SQL DDL, /tables/preview, or /externalTable/tables/list), the variant column is added to the schema as OPEN_STRUCT, and its field config is generated as RAW. There is no setting to map a variant column to STRING or JSON instead. You can change:

Indexes on keys and on the unshredded remainder. These are off by default. See Indexes.
Nothing else. Which keys are shredded, and their types, come from the source files.

Example

Source table

An Iceberg v3 table with a variant column, created in Spark:

CREATE TABLE lake.analytics.events_variant (
  ts      BIGINT,
  user_id STRING,
  props   VARIANT
) USING iceberg
TBLPROPERTIES ('format-version' = '3');

A variant column on a v2 table is refused by the catalog with variant is not supported until v3. Sample rows, shown as JSON. In this file the writer shredded page_type, latency_ms, device.os, tags and items; session and user_tier stay unshredded.

{"ts": 1789700000000, "user_id": "u-0", "props": {"page_type": "HOME",   "latency_ms": 100, "device": {"os": "ios"},     "tags": ["t0", "common"], "items": [{"sku": "sku-0-0", "promo": {"code": "P0"}}, {"sku": "sku-0-1", "promo": {"code": "P01"}}], "session": "s-0", "user_tier": "gold"}}
{"ts": 1789700001000, "user_id": "u-1", "props": {"page_type": "SEARCH", "latency_ms": 110, "device": {"os": "android"}, "tags": ["t1"],           "items": [{"sku": "sku-1-0", "promo": {"code": "P10"}}], "session": "s-1"}}
{"ts": 1789700004000, "user_id": "u-4", "props": {"page_type": "SEARCH", "latency_ms": "n/a", "device": {"os": "ios"},   "tags": ["t4", "common"], "items": [{"sku": "sku-4-0", "promo": {"code": "P0"}}, {"sku": "sku-4-1", "promo": {"code": "P41"}}], "session": "s-4"}}
{"ts": 1789700005000, "user_id": "u-5", "props": {"page_type": "CART",   "device": {"os": "android"},                    "tags": [],               "items": [{"sku": "sku-5-0", "promo": {"code": "P50"}}], "session": "s-5"}}

Row u-4 has a string in the INT key latency_ms, so that key reads as null on that row. Row u-5 has no latency_ms, so it also reads as null.

Create the External Table

With SQL DDL (POST /sql/ddl):

CREATE TABLE events_variant WITH (
  type = 'iceberg',
  catalog_type = 'rest',
  catalog_uri = 'https://catalog.example.com/api/catalog',
  schema_name = 'analytics',
  table_name = 'events_variant',
  storage.provider = 's3',
  storage.region = 'us-west-2'
);

The schema and table config are built from the catalog. You can also generate them with /tables/preview and create the table from the result.

Resulting Pinot schema

props appears under complexFieldSpecs with no child specs:

{
  "schemaName": "events_variant",
  "dimensionFieldSpecs": [
    { "name": "ts", "dataType": "LONG" },
    { "name": "user_id", "dataType": "STRING" }
  ],
  "complexFieldSpecs": [
    { "name": "props", "dataType": "OPEN_STRUCT", "fieldType": "COMPLEX", "childFieldSpecs": {} }
  ]
}

The generated field config for props:

{ "name": "props", "encodingType": "RAW", "indexes": { "forward": { "encodingType": "RAW" } } }

Query

Whole document:

SELECT props FROM events_variant WHERE user_id = 'u-0'

Returns one JSON object. A shredded nested object reads back as an object ("device": {"os": "ios"}), not as a device.os key. Arrays read back as arrays, in order. Filter on a key:

SELECT COUNT(*) FROM events_variant WHERE props['page_type'] = 'HOME'
SELECT COUNT(*) FROM events_variant WHERE props['user_tier'] = 'gold'

The first key is shredded; the second is not. The syntax is the same. Nested key:

SELECT COUNT(*) FROM events_variant WHERE props['device.os'] = 'ios'

IS NULL / IS NOT NULL:

SELECT COUNT(*) FROM events_variant WHERE props['latency_ms'] IS NULL
SELECT COUNT(*) FROM events_variant WHERE props['latency_ms'] IS NOT NULL
SELECT COUNT(*) FROM events_variant WHERE props['device.os'] IS NULL

A key is null when the row doesn’t have it, when it’s written as JSON null, or when its value doesn’t fit the shredded column’s type. A missing key reads as null, not as 0 or ''.

An unshredded key that a row doesn’t have reads as the string 'null', the default null value for a STRING column. So props['user_tier'] = 'null' also matches rows without the key, and a real "null" string can’t be told apart from a missing key. Use IS NULL to test for a missing key.

IN, not-equal, range:

SELECT COUNT(*) FROM events_variant WHERE props['user_tier'] IN ('gold', 'silver')
SELECT COUNT(*) FROM events_variant WHERE props['user_tier'] <> 'gold'
SELECT COUNT(*) FROM events_variant WHERE props['latency_ms'] >= 100

Aggregates. Turn on null handling so rows without the key don’t count as the type default:

SET enableNullHandling = true;
SELECT MIN(props['latency_ms']), COUNT(props['latency_ms']) FROM events_variant

Without enableNullHandling, MIN sees missing values as the INT default. Distinct count and GROUP BY on a key:

SELECT COUNT(DISTINCT props['page_type']) FROM events_variant

SELECT props['page_type'] AS page_type, COUNT(*) AS events
FROM events_variant
GROUP BY props['page_type']
ORDER BY COUNT(*) DESC

ORDER BY a key:

SELECT user_id, props['latency_ms'] FROM events_variant ORDER BY props['latency_ms'] DESC LIMIT 10

Casting:

SELECT COUNT(*) FROM events_variant WHERE CAST(props['latency_ms'] AS STRING) = '100'
SELECT COUNT(*) FROM events_variant WHERE CAST(props['device.os'] AS STRING) = 'android'

An unshredded key reads as text. Arrays. An array key returns the whole array as JSON text. Use JSON_EXTRACT_SCALAR to read inside it:

SELECT props['items'] FROM events_variant

SELECT COUNT(*) FROM events_variant
WHERE JSON_EXTRACT_SCALAR(props['items'], '$[0].promo.code', 'STRING', 'none') = 'P0'

SELECT COUNT(*) FROM events_variant
WHERE JSON_EXTRACT_SCALAR(props['tags'], '$[1]', 'STRING', 'none') = 'common'

-- Same element, addressed from the column root
SELECT COUNT(*) FROM events_variant
WHERE JSON_EXTRACT_SCALAR(props, '$.items[0].promo.code', 'STRING', 'none') = 'P0'

-- An empty array is a value, not a missing key
SELECT COUNT(*) FROM events_variant WHERE props['tags'] = '[]'

What to expect:

A key no row has matches nothing. It isn’t an error.
A dotted path through an array (props['items.promo.code']) matches nothing. Arrays have no per-key columns; use JSON_EXTRACT_SCALAR.
JSON_EXTRACT_SCALAR(props, '$.page_type', ...) and props['page_type'] return the same value.

What doesn’t work:

JSON_MATCH(props, ...) fails with without json index. Use props['key'] or JSON_EXTRACT_SCALAR.
A JSON index on the props column itself is rejected. Put it on the unshredded remainder with sparseFieldConfig instead.

Indexes

Every External Table column is RAW and its data is the Parquet file, so no index exists until you configure one and reload. A reload reads the values back from Parquet and builds the index. No data is rewritten. See Supported Indexes for index support on other columns. Indexes go under fieldConfigList[].indexes.open_struct on the props field config. There are two places:

valueFieldConfigs: one entry per shredded key, by name (page_type, device.os). Keep encodingType as RAW and ask for a dictionary under indexes. Only keys that are shredded in the source file have a column to index.
sparseFieldConfig: one config for the unshredded remainder of every row (the column named props$__sparse__). Add a json index, or a compositeJson index with includeAllPaths. Its encoding is always RAW. The older flag "sparseJsonIndex": true means the same as a json index here.

Sub-field kind	Indexes you can add	Filters served by the index	Filters that still scan
Shredded key	`dictionary` + `inverted`	`=`, `<>`, `IN`, `NOT IN`	`LIKE`, `REGEXP_LIKE`
Shredded key	`dictionary` + `range`	`<`, `<=`, `>`, `>=`, `BETWEEN`	`LIKE`, `REGEXP_LIKE`
Unshredded key	`json` (or `compositeJson` with `includeAllPaths`) on `sparseFieldConfig`	`=`, `<>`, `IN`, `NOT IN`	range, `LIKE`, `REGEXP_LIKE`
Array, or anything inside one	none	none	`JSON_EXTRACT_SCALAR` filters always scan

IS NULL and IS NOT NULL on any key are answered from the key’s null tracking, not by a filter scan. The JSON index on unshredded keys falls back to a scan (with a correct result) when:

the predicate value equals the key’s default null value,
the predicate is negated (<>, NOT IN) and enableNullHandling is on,
the key holds an array,
a compositeJson index wasn’t told to index that path (use includeAllPaths).

sorted and h3 aren’t supported on External Tables, at the column level or anywhere under open_struct.

Full config example

"fieldConfigList": [
  {
    "name": "props",
    "encodingType": "RAW",
    "indexes": {
      "forward": { "encodingType": "RAW" },
      "open_struct": {
        "valueFieldConfigs": [
          {
            "name": "page_type",
            "encodingType": "RAW",
            "indexes": { "dictionary": {}, "inverted": {} }
          },
          {
            "name": "latency_ms",
            "encodingType": "RAW",
            "indexes": { "dictionary": {}, "range": {} }
          },
          {
            "name": "device.os",
            "encodingType": "RAW",
            "indexes": { "dictionary": {}, "inverted": {} }
          }
        ],
        "sparseFieldConfig": {
          "name": "props$__sparse__",
          "indexes": { "json": {} }
        }
      }
    }
  }
]

To use a composite JSON index on the remainder instead, replace the sparseFieldConfig.indexes value with:

{ "compositeJson": { "invertedIndexConfigs": [ { "includeAllPaths": "true" } ] } }

Apply

Update the table config, then reload:

curl -X PUT "$BASE_URL/tables/events_variant" \
  -H "Content-Type: application/json" -d @events_variant_table_config.json

curl -X POST "$BASE_URL/segments/events_variant_OFFLINE/reload"

Indexes are built on the reload, not at sync time. A config added later takes effect on the next reload.

Confirm the index is used

Run the filter and check numEntriesScannedInFilter in the response. 0 means the index answered it:

SELECT COUNT(*) FROM events_variant WHERE props['page_type'] = 'HOME'     -- inverted on a shredded key
SELECT COUNT(*) FROM events_variant WHERE props['latency_ms'] >= 100      -- range on a shredded key
SELECT COUNT(*) FROM events_variant WHERE props['user_tier'] = 'gold'     -- JSON index on the remainder

A filter that scans, such as props['session'] > 's-5' or props['user_tier'] LIKE 'go%', shows a value above 0. To see the plan for every segment, use verbose mode. The default form shows one segment’s plan only:

SET explainPlanVerbose = true;
EXPLAIN PLAN FOR SELECT COUNT(*) FROM events_variant WHERE props['page_type'] = 'HOME'

Since shredding is per file, one segment can use a key’s index while another scans the same key.

Verify

GET $BASE_URL/schemas/events_variant shows props with "dataType": "OPEN_STRUCT".
SELECT props FROM events_variant LIMIT 1 returns a JSON object with both shredded and unshredded keys.
SELECT COUNT(*) FROM events_variant WHERE props['page_type'] = 'HOME' returns the count you expect from the source.
After adding an index and reloading, the same query shows numEntriesScannedInFilter = 0.

Limits

Object roots only. A row whose variant root is an array, a scalar or a top-level null fails the read with an error. It’s never returned as {}.
No dense-key tuning. maxDenseKeys, denseKeys, denseKeyMinFillRate and maxNestedKeyDepth are rejected. The writer’s shredding decides which keys are columns.
Arrays. Arrays never get their own column, at any depth. They read as JSON text, and filters inside them always scan.
JSON_MATCH. Not supported on the props column.
No JSON index on the column itself. A json index is accepted only on sparseFieldConfig.
Range and LIKE on unshredded keys scan. The JSON index serves only =, <>, IN and NOT IN.
First IS NULL on an unshredded key reads every row once per segment. The result is cached for later queries.
Type drift on a shredded key. Values that don’t fit the typed column read as null.
Per-file layout. Index use can differ between segments of one table, because each file is shredded on its own.
Malformed variant bytes. A row with undecodable variant bytes reads as absent for that key, so one bad row doesn’t fail the scan. The first occurrence per column is logged and every occurrence is counted on a server metric.
Contradictory files. If a file stores a scalar key both in its typed column and in its residual for the same row, the read fails rather than picking one.

Common errors

Error	Cause	Fix
`variant is not supported until v3`	The Iceberg table is format version 2 or lower. Raised by the catalog when the table is created or altered.	Create or upgrade the Iceberg table to format version 3.
`Does not support 'maxDenseKeys' for an external table. Column 'props' has 'maxDenseKeys' configured under 'open_struct'.` (also `denseKeys`, `denseKeyMinFillRate`, `maxNestedKeyDepth`)	A setting that picks which keys are columns. External Tables take that from the source file.	Remove the setting. Index keys with `valueFieldConfigs` and `sparseFieldConfig`.
`Does not support 'sorted' index. Column 'props' has 'sorted' configured in the indexes map under 'open_struct.valueFieldConfigs['page_type']'` (also `h3`, and `open_struct.sparseFieldConfig`)	`sorted` or `h3` asked for on a key or the remainder.	Remove it. Use `inverted` or `range` on a shredded key, `json` on the remainder.
`Requires RAW encoding for all columns. Column 'props' has DICTIONARY (default) encoding`	The `props` field config is missing `"encodingType": "RAW"`.	Set `"encodingType": "RAW"`. Ask for a dictionary under `indexes` on a key instead.
`Cannot apply JSON_MATCH on column: props without json index`	`JSON_MATCH` on an `OPEN_STRUCT` column.	Use `props['key']` or `JSON_EXTRACT_SCALAR(props, '$.path', ...)`.
`Cannot create JSON index on column: props of stored type other than STRING or MAP`	A `json` index on the `props` column itself.	Move it to `open_struct.sparseFieldConfig.indexes`.
`VARIANT column props has a non-object root at docId <n> (...); OPEN_STRUCT can only represent an object root`	A row’s variant value is an array, a scalar or null at the top level.	Wrap the value in an object in the source (for example `{"items": [...]}`).
`VARIANT key has both a typed value and a residual at docId <n>, which the shredding spec allows only for an object.`	The file stores one key twice for the same row, which breaks the shredding spec.	Rewrite the file with a spec-compliant writer.

Validation errors from table create or update are listed together under External table '<name>' has <N> validation error(s):.

Get Started

Ingestion

External Tables

Query Data

Manage Data

Observability

Visualize Data

Cluster Operations

Manage Security

Release Notes

Reference

VARIANT columns (OPEN_STRUCT)

What changes

Requirements

Enable or disable

Example

Source table

Create the External Table

Resulting Pinot schema

Query

Indexes

Full config example

Apply

Confirm the index is used

Verify

Limits

Common errors

​What changes

​Requirements

​Enable or disable

​Example

​Source table

​Create the External Table

​Resulting Pinot schema

​Query

​Indexes

​Full config example

​Apply

​Confirm the index is used

​Verify

​Limits

​Common errors

What changes

Requirements

Enable or disable

Example

Source table

Create the External Table

Resulting Pinot schema

Query

Indexes

Full config example

Apply

Confirm the index is used

Verify

Limits

Common errors