1 of 1

Schema Evolution

Schema evolution occurs over time. As business requirements evolve, and data formats or structures need to change, use Pinot to keep your schemas up-to-date. If you're just starting out with schemas in Pinot, see how to create a new schema for a Pinot table.

In this tutorial, you'll learn how to add a new column to your schema, load data to the updated schema, run a query to test the updated schema, and backfill data.

Pinot only supports adding new columns to a schema. To drop a column or change the column name or data type, you must create a new table.

Prerequisites

Before you get started, you must have a Pinot cluster up and running, and a baseballStats table (created when you set up a Pinot cluster using the Quickstart option). For more information, see how to option.

Add a new column to your schema

Fetch the existing schema using the controller API:
Edit the baseballStats.schema file to include a new column at the end of the schema. For example, here we're adding a new column called yearsOfExperience with a dataType of INT and defaultNullValue of 1

Update the schema using the following command:

Reload table segments

After you add the new column to your schema, reload the consuming segments.

(Real-time tables only): Open , and set pinot.server.instance.reload.consumingSegment to true.
To ensure the baseballStats column shows up, run the following command to reload the table segments--be sure to replace the accurate reloadJobId for your schema:

Command

Response

This triggers a reload operation on each of the servers hosting the table's segments. The API response has a reloadJobId that you can use to monitor the status of the reload operation using the segment reload status API.

Reloading a segment shouldn't impact in-flight queries. New segments are reloaded to replace existing segments only after an existing segment isn't serving any in-flight queries.

Command

Response

For real-time consuming segments, the reload is performed as force commit, which commits the current consuming segment and loads it as an immutable segment. A new consuming segment is created after the current one is committed, and picks up the changes in the table config and schema.
Upsert and dedup config change cannot be applied via reload because they will change the table level (cross segments) metadata management. To apply these changes, server needs to be restarted.

Query and backfill data

After reloading the segments, run the the following to query the new column:

Command

Response

As you can see, the query returns the defaultNullValue for the newly added column. To populate this column with real values, re-run the batch ingestion job for the past datesBackfill data.

Backfilling data does not work for real-time tables. You can convert a real-time table to a hybrid table by adding an offline table that uses the same counterpart, and then backfilling the offline table to fill in values for the newly added column. For more information, see .

Schema Evolution

In this tutorial, you'll learn how to add a new column to your schema, load data to the updated schema, run a query to test the updated schema, and backfill data.

Pinot only supports adding new columns to a schema. To drop a column or change the column name or data type, you must create a new table.

Prerequisites

Add a new column to your schema

Fetch the existing schema using the controller API:
Edit the baseballStats.schema file to include a new column at the end of the schema. For example, here we're adding a new column called yearsOfExperience with a dataType of INT and defaultNullValue of 1

Update the schema using the following command:

Reload table segments

After you add the new column to your schema, reload the consuming segments.

(Real-time tables only): Open , and set pinot.server.instance.reload.consumingSegment to true.
To ensure the baseballStats column shows up, run the following command to reload the table segments--be sure to replace the accurate reloadJobId for your schema:

Command

Response

Reloading a segment shouldn't impact in-flight queries. New segments are reloaded to replace existing segments only after an existing segment isn't serving any in-flight queries.

Command

Response

For real-time consuming segments, the reload is performed as force commit, which commits the current consuming segment and loads it as an immutable segment. A new consuming segment is created after the current one is committed, and picks up the changes in the table config and schema.
Upsert and dedup config change cannot be applied via reload because they will change the table level (cross segments) metadata management. To apply these changes, server needs to be restarted.

Query and backfill data

After reloading the segments, run the the following to query the new column:

Command

Response

As you can see, the query returns the defaultNullValue for the newly added column. To populate this column with real values, re-run the batch ingestion job for the past datesBackfill data.

Schema Evolution

hashtagPrerequisites

hashtagAdd a new column to your schema

hashtagReload table segments

hashtagQuery and backfill data

Schema Evolution

hashtagPrerequisites

hashtagAdd a new column to your schema

hashtagReload table segments

hashtagQuery and backfill data

Prerequisites

Add a new column to your schema

Reload table segments

Query and backfill data

Prerequisites

Add a new column to your schema

Reload table segments

Query and backfill data