> ## Documentation Index > Fetch the complete documentation index at: https://docs.startree.ai/llms.txt > Use this file to discover all available pages before exploring further. # VARIANT columns (OPEN_STRUCT) > Query Iceberg v3 and Parquet VARIANT columns on External Tables. They map to Pinot OPEN_STRUCT and are read by key with col['key']. > [!NOTE] > > 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](https://docs.startree.ai/corecapabilities/external-table/data-type-mapping.md). * **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](#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](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md), `/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](#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: ```sql theme={null} 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. ```json theme={null} {"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](https://docs.startree.ai/corecapabilities/external-table/sql-ddl.md) (`POST /sql/ddl`): ```sql theme={null} 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: ```json theme={null} { "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`: ```json theme={null} { "name": "props", "encodingType": "RAW", "indexes": { "forward": { "encodingType": "RAW" } } } ``` ## Query **Whole document:** ```sql theme={null} 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:** ```sql theme={null} 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:** ```sql theme={null} SELECT COUNT(*) FROM events_variant WHERE props['device.os'] = 'ios' ``` **IS NULL / IS NOT NULL:** ```sql theme={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 `''`. > [!NOTE] > > 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:** ```sql theme={null} 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: ```sql theme={null} 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:** ```sql theme={null} 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:** ```sql theme={null} SELECT user_id, props['latency_ms'] FROM events_variant ORDER BY props['latency_ms'] DESC LIMIT 10 ``` **Casting:** ```sql theme={null} 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: ```sql theme={null} 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](https://docs.startree.ai/corecapabilities/external-table/indexes.md) 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 ```json theme={null} "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: ```json theme={null} { "compositeJson": { "invertedIndexConfigs": [ { "includeAllPaths": "true" } ] } } ``` ### Apply Update the table config, then reload: ```bash theme={null} 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: ```sql theme={null} 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: ```sql theme={null} 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 1. `GET $BASE_URL/schemas/events_variant` shows `props` with `"dataType": "OPEN_STRUCT"`. 2. `SELECT props FROM events_variant LIMIT 1` returns a JSON object with both shredded and unshredded keys. 3. `SELECT COUNT(*) FROM events_variant WHERE props['page_type'] = 'HOME'` returns the count you expect from the source. 4. 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 (...); 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 , 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 '' has validation error(s):`. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.