# Schema Configuration This page is the flat reference for Pinot schema JSON. It brings the top-level schema object, field-spec tables, null-handling rules, date-time formats, MAP support, and advanced column options into one place. ## Key Areas | Area | What it covers | Jump | | ----------------- | ------------------------------------------------------- | --------------------------------------------- | | Schema object | Top-level schema fields and a complete example | [Schema object](#schema-object) | | Field specs | Dimension, metric, and date-time column definitions | [Field specs](#field-specs) | | Null handling | `enableColumnBasedNullHandling` and default null values | [Null handling](#null-handling) | | Complex fields | `MAP` columns and `childFieldSpecs` | [ComplexFieldSpecs](#complexfieldspecs) | | Date-time formats | New and legacy format syntaxes | [New DateTime Formats](#new-datetime-formats) | ## Schema Object Use a schema to define the column names, data types, null-handling behavior, and field groups for a Pinot table. | Field | Release Version | Default | Description | | ------------------------------- | --------------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `schemaName` | - | required | Name of the schema. This must match the table name without the `OFFLINE` or `REALTIME` suffix, so both physical tables of a hybrid table share one schema. | | `description` | - | omitted | Optional human-readable description of the schema. | | `tags` | - | omitted | Optional list of tags for categorizing the schema. | | `enableColumnBasedNullHandling` | 1.1.0 | `false` | When `true`, enables column-based null handling. When `false`, Pinot uses table-based null handling. See [Null value support](/build-with-pinot/querying-and-sql/sql-syntax/null-value-support.md). | | `dimensionFieldSpecs` | - | `[]` | Dimension-column definitions. See [DimensionFieldSpecs](#dimensionfieldspecs). | | `metricFieldSpecs` | - | `[]` | Metric-column definitions. See [MetricFieldSpecs](#metricfieldspecs). | | `dateTimeFieldSpecs` | - | `[]` | Time-column definitions. A schema can define multiple time columns. See [DateTimeFieldSpecs](#datetimefieldspecs). | | `complexFieldSpecs` | - | `[]` | Complex-column definitions. Pinot currently supports `MAP` through this section. See [ComplexFieldSpecs](#complexfieldspecs). | ## Example Schema ```json { "schemaName": "flights", "description": "Tracks flight events and attributes.", "tags": ["production", "real-time"], "enableColumnBasedNullHandling": false, "dimensionFieldSpecs": [ { "name": "flightNumber", "dataType": "LONG", "description": "Unique flight identifier." }, { "name": "airline", "dataType": "STRING" }, { "name": "tags", "dataType": "STRING", "singleValueField": false, "defaultNullValue": "null" } ], "metricFieldSpecs": [ { "name": "price", "dataType": "DOUBLE", "defaultNullValue": 0 } ], "dateTimeFieldSpecs": [ { "name": "millisSinceEpoch", "dataType": "LONG", "format": "EPOCH", "granularity": "15:MINUTES" }, { "name": "hoursSinceEpoch", "dataType": "INT", "format": "EPOCH|HOURS", "granularity": "1:HOURS" }, { "name": "dateString", "dataType": "STRING", "format": "SIMPLE_DATE_FORMAT|yyyy-MM-dd", "granularity": "1:DAYS" } ], "complexFieldSpecs": [ { "name": "attributes", "dataType": "MAP", "fieldType": "COMPLEX", "notNull": false, "childFieldSpecs": { "key": { "name": "key", "dataType": "STRING", "fieldType": "DIMENSION", "notNull": false }, "value": { "name": "value", "dataType": "STRING", "fieldType": "DIMENSION", "notNull": false } } } ] } ``` ## Data Types Pinot supports the following schema data types: | Data Type | Default Dimension Value | Default Metric Value | | ------------- | -------------------------------------------------------------------------------------------------------------- | ------------------------ | | `INT` | [Integer.MIN\_VALUE](https://docs.oracle.com/javase/7/docs/api/java/lang/Integer.html#MIN_VALUE) | `0` | | `LONG` | [Long.MIN\_VALUE](https://docs.oracle.com/javase/7/docs/api/java/lang/Long.html#MIN_VALUE) | `0` | | `FLOAT` | [Float.NEGATIVE\_INFINITY](https://docs.oracle.com/javase/7/docs/api/java/lang/Float.html#NEGATIVE_INFINITY) | `0.0` | | `DOUBLE` | [Double.NEGATIVE\_INFINITY](https://docs.oracle.com/javase/7/docs/api/java/lang/Double.html#NEGATIVE_INFINITY) | `0.0` | | `BIG_DECIMAL` | `0.0` | `0.0` | | `BOOLEAN` | `0` (`false`) | N/A | | `TIMESTAMP` | `0` (`1970-01-01 00:00:00 UTC`) | N/A | | `STRING` | `"null"` | N/A | | `JSON` | `"null"` | N/A | | `BYTES` | byte array of length `0` | byte array of length `0` | The `TIMESTAMP` type supports milliseconds epoch precision. Nanoseconds are not supported. All schema data types are comparable, and ordering must be consistent with equality. Pinot normalizes a few edge cases to preserve that property: * For `FLOAT` and `DOUBLE`, negative zero (`-0.0`) becomes `0.0`. * For `FLOAT` and `DOUBLE`, `NaN` becomes the default null value. * For `BIG_DECIMAL`, trailing zeros are stripped unless `allowTrailingZeros` is enabled. ## Field Specs Pinot groups columns into dimension, metric, date-time, and complex field specs. The field spec controls column behavior during ingestion, storage, and query execution. ## Null Handling At schema level, null handling is controlled by `enableColumnBasedNullHandling` and by per-column `defaultNullValue` settings. * Set `enableColumnBasedNullHandling` to `true` when you want Pinot to store null-ness per column. * Leave it `false` to keep table-based null handling behavior. * If a field does not define `defaultNullValue`, Pinot falls back to the internal default for that field type. ### Dimension Default Null Values | Data Type | Internal Default Null Value | | ----------- | -------------------------------------------------------------------------------------------------------------- | | `INT` | [Integer.MIN\_VALUE](https://docs.oracle.com/javase/7/docs/api/java/lang/Integer.html#MIN_VALUE) | | `LONG` | [Long.MIN\_VALUE](https://docs.oracle.com/javase/7/docs/api/java/lang/Long.html#MIN_VALUE) | | `FLOAT` | [Float.NEGATIVE\_INFINITY](https://docs.oracle.com/javase/7/docs/api/java/lang/Float.html#NEGATIVE_INFINITY) | | `DOUBLE` | [Double.NEGATIVE\_INFINITY](https://docs.oracle.com/javase/7/docs/api/java/lang/Double.html#NEGATIVE_INFINITY) | | `BOOLEAN` | `0` (`false`) | | `TIMESTAMP` | `0` (`1970-01-01 00:00:00 UTC`) | | `STRING` | `"null"` | | `BYTES` | byte array of length `0` | | `JSON` | `"null"` | ### Metric Default Null Values | Data Type | Internal Default Null Value | | ------------- | --------------------------- | | `INT` | `0` | | `LONG` | `0` | | `FLOAT` | `0.0` | | `DOUBLE` | `0.0` | | `BIG_DECIMAL` | `0.0` | | `BYTES` | byte array of length `0` | ## DimensionFieldSpecs Define one dimension field spec for each dimension column. | Property | Description | | ------------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `name` | Name of the dimension column. | | `description` | Optional human-readable description of the column. | | `tags` | Optional list of tags for categorizing the column. | | `dataType` | Data type of the dimension column. Supported types are `INT`, `LONG`, `FLOAT`, `DOUBLE`, `BIG_DECIMAL`, `BOOLEAN`, `TIMESTAMP`, `STRING`, `BYTES`, and `JSON`. | | `defaultNullValue` | Value Pinot should write when the source record is null. If omitted, Pinot uses the internal default for the type. | | `singleValueField` | Whether the column is single-valued. If `false`, Pinot stores a multi-value list, preserves order, and allows duplicates. This includes `BIG_DECIMAL` and `BYTES` dimension columns. The default null value for a multi-value column is a single-element list containing the configured or internal null value. | ## MetricFieldSpecs Define one metric field spec for each metric column. | Property | Description | | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `name` | Name of the metric column. | | `description` | Optional human-readable description of the column. | | `tags` | Optional list of tags for categorizing the column. | | `dataType` | Data type of the metric column. Supported types are `INT`, `LONG`, `FLOAT`, `DOUBLE`, `BIG_DECIMAL`, and `BYTES` for serialized sketches such as HLL or TDigest. | | `defaultNullValue` | Value Pinot should write when the source record is null. If omitted, Pinot uses the internal default for the type. | ## DateTimeFieldSpecs Use date-time field specs to define time columns. | Property | Description | | ------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `name` | Name of the date-time column. | | `description` | Optional human-readable description of the column. | | `tags` | Optional list of tags for categorizing the column. | | `dataType` | Data type of the date-time column. Supported types are `STRING`, `INT`, `LONG`, and `TIMESTAMP`. Internally `TIMESTAMP` is stored as `LONG` milliseconds since epoch. If you use `TIMESTAMP`, source data must be either `LONG` epoch values or JDBC timestamp strings such as `2021-01-01 01:01:01.123`. | | `format` | Format in which the time value is stored. See [New DateTime Formats](#new-datetime-formats). | | `granularity` | Bucket size and unit in `bucketSize:bucketUnit` form, for example `15:MINUTES`. This is descriptive metadata only; Pinot does not automatically round values to the declared granularity. | | `defaultNullValue` | Value Pinot should write when the source record is null. String-based date-time fields default to `"null"`. `TIMESTAMP` defaults to epoch `0` (`1970-01-01 00:00:00 UTC`). For the primary time column named in table `segmentsConfig`, the default value must fall between `1971-01-01 UTC` and `2071-01-01 UTC`; otherwise Pinot uses segment creation time for segment-management features such as retention and time boundary. | ## New DateTime Formats The current simplified syntax is: ``` timeFormat|pattern/timeUnit|[timeZone/timeSize] ``` The bracketed fields are optional. `timeFormat` can be `EPOCH`, `SIMPLE_DATE_FORMAT`, or `TIMESTAMP`. * `TIMESTAMP` Represents a timestamp in milliseconds. It is equivalent to `EPOCH|MILLISECONDS|1`. Example: `TIMESTAMP` * `EPOCH` Represents time in a `TimeUnit` since `1970-01-01 00:00:00 UTC`. Examples: `EPOCH`, `EPOCH|SECONDS`, `EPOCH|SECONDS|5` * `SIMPLE_DATE_FORMAT` Represents a string-formatted time using Joda `DateTimeFormat` syntax. Examples: `SIMPLE_DATE_FORMAT`, `SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss`, `SIMPLE_DATE_FORMAT|yyyy-MM-dd|America/Los_Angeles` Only lexicographically ordered date-time patterns are supported. For example, `yyyy-MM-dd`, `MM-dd`, and `yyyy-dd` are valid, while `MM-dd-yyyy` is not. ## Old Date Time Formats Legacy date-time formats are still supported for backward compatibility. New schemas should prefer the simplified formats above. Use the following syntax: ``` timeSize:timeUnit:timeFormat:pattern ``` * `timeSize` Interval size for `EPOCH` values. Pinot multiplies the stored value by this size and unit to compute the actual timestamp. * `timeUnit` A [`TimeUnit`](https://docs.oracle.com/javase/8/docs/api/java/util/concurrent/TimeUnit.html) enum such as `HOURS` or `MINUTES`. * `timeFormat` Either `EPOCH` or `SIMPLE_DATE_FORMAT`. * `pattern` Optional Joda `DateTimeFormat` pattern string, used only with `SIMPLE_DATE_FORMAT`. Examples: * `1:MILLISECONDS:EPOCH` * `1:HOURS:EPOCH` * `1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd` * `1:HOURS:SIMPLE_DATE_FORMAT:EEE MMM dd HH:mm:ss ZZZ yyyy` ## ComplexFieldSpecs Use complex field specs for complex data types. Pinot currently supports `MAP`. | Property | Description | | ----------------- | --------------------------------------------------- | | `name` | Name of the complex column. | | `description` | Optional human-readable description of the column. | | `tags` | Optional list of tags for categorizing the column. | | `dataType` | Complex data type. Currently supports `MAP`. | | `fieldType` | Must be `COMPLEX`. | | `notNull` | Whether the column can contain null values. | | `childFieldSpecs` | Definition of the map `key` and `value` sub-fields. | ## childFieldSpecs The `childFieldSpecs` object describes the structure of the `key` and `value` entries inside a `MAP`. ### key childFieldSpec Map keys are always strings. | Property | Description | | ----------- | ------------------------------------------------------------------- | | `name` | Must be `key`. | | `dataType` | Must be `STRING`. | | `fieldType` | Must be `DIMENSION`. | | `notNull` | Whether the key can be null. In practice this is typically `false`. | ### value childFieldSpec Map values can use several scalar types. | Property | Description | | ----------- | --------------------------------------------------------------------------------------- | | `name` | Must be `value`. | | `dataType` | Data type for the map value, for example `STRING`, `INT`, `LONG`, `FLOAT`, or `DOUBLE`. | | `fieldType` | Should be `DIMENSION` for non-numeric values. | | `notNull` | Whether the value can be null. | ## Built-in Virtual Columns Pinot exposes several built-in virtual columns that you can query for debugging and inspection: | Column Name | Column Type | Data Type | Description | | -------------- | ----------- | --------- | --------------------------------------------- | | `$hostName` | Dimension | `STRING` | Name of the server hosting the data. | | `$segmentName` | Dimension | `STRING` | Name of the segment containing the record. | | `$docId` | Dimension | `INT` | Document ID of the record within the segment. | ## Advanced Fields These advanced properties are available across field specs: | Property | Description | | ------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `maxLength` | Maximum length for `STRING`, `JSON`, and `BYTES` columns. | | `maxLengthExceedStrategy` | Behavior when incoming values exceed `maxLength`. Supported values are `TRIM_LENGTH`, `SUBSTITUTE_DEFAULT_VALUE`, `NO_ACTION`, and `ERROR`. Defaults to `TRIM_LENGTH` for `STRING` and `NO_ACTION` for `JSON` and `BYTES`. | | `allowTrailingZeros` | Whether `BIG_DECIMAL` should preserve trailing zeros. Defaults to `false`, which strips them. | | `virtualColumnProvider` | Provider used to populate a virtual column value. | For `JSON` columns, Pinot also supports cluster-wide or node-local fallback defaults when the field spec omits `maxLength` or `maxLengthExceedStrategy`. By default, Pinot uses `512` and `NO_ACTION`. You can override those defaults with `pinot.field.spec.default.json.max.length` and `pinot.field.spec.default.json.max.length.exceed.strategy`. Explicit field-spec values still take precedence. ## Related Pages * [Configuration Reference](/reference/configuration-reference.md) * [Table Configuration](/reference/configuration-reference/table.md) * [First Table + Schema](/start-here/first-table-and-schema.md) * [Table Overview](/architecture-and-concepts/components/table.md) * [Legacy Schema Page](/reference/configuration-reference/schema.md) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.pinot.apache.org/reference/configuration-reference/schema.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.