Schema

Each table in Pinot is associated with a Schema. A schema defines what fields are present in the table along with the data types. 
The schema is stored in the Zookeeper along with the table configuration.
Categories
A schema also defines what category a column belongs to. Columns in a Pinot table can be broadly categorized into three categories - 
Category
Description
Dimension
​
Dimension columns are typically used in slice and dice operations for answering business queries. Some operations for which dimension columns are used:
GROUP BY - group by one or more dimension columns along with aggregations on one or more metric columns
Filter clause such as WHERE
Metric
These columns represent the quantitative data of the table. Such columns are used for aggregation. In data warehouse terminology, these can also be referred to as fact or measure columns.
Some operation for which metric columns are used:
Aggregation - SUM, MIN, MAX, COUNT, AVG etc
Filter clause such as WHERE
DateTime
This column represents time columns in the data. There can be multiple time columns in a table, but only one of them can be treated as primary. Primary time column is the one that is present in the segment config. 
The primary time column is used by Pinot, for maintaining the time boundary between offline and realtime data in a hybrid table and for retention management. A primary time column is mandatory if the table's push type is APPEND and optional if the push type is REFRESH . 
​
Common operations which can be done on time column:
GROUP BY
Filter clause such as WHERE
Data Types
Data types determine the operations that can be performed on a column. Pinot supports the following data types:
Data Type
Default Dimension Value
Default Metric Value
INT
​Integer.MIN_VALUE​
0
LONG
​Long.MIN_VALUE​
0
FLOAT
​Float.NEGATIVE_INFINITY​
0.0
DOUBLE
​Double.NEGATIVE_INFINITY​
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
BOOLEAN, TIMESTAMP, JSON are added after release 0.7.1. In release 0.7.1 and older releases, BOOLEAN is equivalent to STRING.
Pinot also supports columns that contain lists or arrays of items, but there isn't an explicit data type to represent these lists or arrays. Instead, you can indicate that a dimension column accepts multiple values. For more information, see DimensionFieldSpec in the Schema configuration reference.
Date Time Fields
Since Pinot doesn't have a dedicated DATETIME datatype support, you need to input time in either STRING, LONG, or INT format. However, Pinot needs to convert the date into an understandable format such as epoch timestamp to do operations.
To achieve this conversion, 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. eg: 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 the java SimpleDateFormat 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 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. s 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:
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
These virtual columns can be used in queries in a similar way to regular columns.
Creating a Schema
First, Make sure your cluster is up and running. 
Let's create a schema and put it in a JSON file. For this example, we have created a schema for flight data.
For more details on constructing a schema file, see the Schema configuration reference.
flights-schema.json
1
{
2
  "schemaName": "flights",
3
  "dimensionFieldSpecs": [
4
    {
5
      "name": "flightNumber",
6
      "dataType": "LONG"
7
    },
8
    {
9
      "name": "tags",
10
      "dataType": "STRING",
11
      "singleValueField": false,
12
      "defaultNullValue": "null"
13
    }
14
  ],
15
  "metricFieldSpecs": [
16
    {
17
      "name": "price",
18
      "dataType": "DOUBLE",
19
      "defaultNullValue": 0
20
    }
21
  ],
22
  "dateTimeFieldSpecs": [
23
    {
24
      "name": "millisSinceEpoch",
25
      "dataType": "LONG",
26
      "format": "1:MILLISECONDS:EPOCH",
27
      "granularity": "15:MINUTES"
28
    },
29
    {
30
      "name": "hoursSinceEpoch",
31
      "dataType": "INT",
32
      "format": "1:HOURS:EPOCH",
33
      "granularity": "1:HOURS"
34
    },
35
    {
36
      "name": "dateString",
37
      "dataType": "STRING",
38
      "format": "1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd",
39
      "granularity": "1:DAYS"
40
    }
41
  ]
42
}
Copied!
Then, we can upload the sample schema provided above using either bash command or REST API call.
pinot-admin.sh
curl
1
bin/pinot-admin.sh AddSchema -schemaFile flights-schema.json -exec
2
​
3
OR
4
​
5
bin/pinot-admin.sh AddTable -schemaFile flights-schema.json -tableFile flights-table.json -exec
Copied!
1
curl -F [email protected]  localhost:9000/schemas
Copied!
Check out the schema in the Rest API to make sure it was successfully uploaded
Tenant
Table
Last modified 3mo ago
Copy link
Contents