Schema

Schema is used to define the names, data types, and other information for the columns of a Pinot table.

The Pinot schema is composed of:

{
  "schemaName": "flights",
  "dimensionFieldSpecs": [
    {
      "name": "flightNumber",
      "dataType": "LONG"
    },
    {
      "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"
    }
  ]
}

The above json configuration is the example of Pinot schema derived from the flight data. As seen in the example, the schema is composed of 4 parts: schemaName, dimensionFieldSpec, metricFieldSpec, and dateTimeFieldSpec. Below is a detailed description of each type of field spec.

DimensionFieldSpec

A dimensionFieldSpec is defined for each dimension column. Here's a list of the fields in the dimensionFieldSpec:

Internal default null values for dimension

MetricFieldSpec

A metricFieldSpec is defined for each metric column. Here's a list of fields in the metricFieldSpec

Internal default null values for metric

DateTimeFieldSpec

A dateTimeFieldSpec is used to define time columns of the table. The following fields can be configured in the date time field spec -

New DateTime Formats

In the pinot master (0.12.0-SNAPSHOT), We have simplified date time formats for the users. The formats now follow the pattern - timeFormat|pattern/timeUnit|[timeZone/timeSize] . The fields present in [] are completely optional. timeFormat can be one of EPOCH , SIMPLE_DATE_FORMAT or TIMESTAMP .

• TIMESTAMP - This represents timestamp in milliseconds. It is equivalent to specifying EPOCH|MILLISECONDS|1 Examples -

  • TIMESTAMP

• EPOCH - This represents time in timeUnit since 00:00:00 UTC on 1 January 1970, where timeUnit is one of TimeUnit enum values, e.g. HOURS , MINUTES etc. You can also specify the timeSize parameter. This size is multiplied to the value present in the time column to get an actual timestamp. e.g. if timesize is 5 and value in time column is 4996308 minutes. The value that will be converted to epoch timestamp will be 4996308 * 5 * 60 * 1000 = 1498892400000 milliseconds. In simplest terms, EPOCH|SECONDS|5 denotes the count of intervals of length 5 seconds from epoch 0 to now. Examples -

  • EPOCH - Defaults to MILLISECONDS (only in master branch)

  • EPOCH|SECONDS

  • EPOCH|SECONDS|5

• SIMPLE_DATE_FORMAT - This represents time in the string format. The pattern should be specified using the Joda's DateTimeFormat representation. In the master branch build, if no pattern is specified, we use ISO 8601 DateTimeFormat to parse the date times. Optionals are supported with ISO format so users can specify date time string in yyyy or yyyy-MM or yyyy-MM-dd and so on You can also specify optional timeZone parameter which is the ID for a TimeZone, either an abbreviation such as PST, a full name such as America/Los_Angeles, or a custom ID such as GMT-8:00. Examples -

  • SIMPLE_DATE_FORMAT (only in master branch)

  • SIMPLE_DATE_FORMAT|yyyy-MM-dd HH:mm:ss

  • SIMPLE_DATE_FORMAT|yyyy-MM-dd|IST

Old date time formats

These date-time formats are still supported in Pinot for backward compatibility. However, new users should prefer to use the formats mentioned in the previous sections.

You will need to provide the format of the date along with the data type in the schema. The format is described using the following syntax: timeSize:timeUnit:timeFormat:pattern .

• time size - the size of the time unit. This size is multiplied to the value present in the time column to get an actual timestamp. e.g. if timesize is 5 and value in time column is 4996308 minutes. The value that will be converted to epoch timestamp will be 4996308 * 5 * 60 * 1000 = 1498892400000 milliseconds. If your date is not in EPOCH format, this value is not used and can be set to 1 or any other integer.

• time unit - one of TimeUnit enum values. e.g. HOURS , MINUTES etc. If your date is not in EPOCH format, this value is not used and can be set to MILLISECONDS or any other unit.

• timeFormat - can be either EPOCH or SIMPLE_DATE_FORMAT. If it is SIMPLE_DATE_FORMAT, the pattern string is also specified.

• pattern - This is optional and is only specified when the date is in SIMPLE_DATE_FORMAT . The pattern should be specified using Joda's DateTimeFormat representation. e.g. 2020-08-21 can be represented as yyyy-MM-dd.

Here are some sample date-time formats you can use in the schema:

• 1:MILLISECONDS:EPOCH - used when timestamp is in the epoch milliseconds and stored in LONG format

• 1:HOURS:EPOCH - used when timestamp is in the epoch hours and stored in LONG or INT format

• 1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd - when the date is in STRING format and has the pattern year-month-date. e.g. 2020-08-21

• 1:HOURS:SIMPLE_DATE_FORMAT:EEE MMM dd HH:mm:ss ZZZ yyyy - when date is in STRING format. e.g. Mon Aug 24 12:36:50 America/Los_Angeles 2019

Built-in virtual columns

There are several built-in virtual columns inside the schema the can be used for debugging purposes:

These virtual columns can be used in queries in a similar way to regular columns.

Advanced fields

Apart from these, there's some advanced fields. These are common to all field specs.