Skip to main content

Schema Change

Users can modify the schema of existing tables through the Schema Change operation. Doris currently supports the following modifications:

  • Add and delete columns
  • Modify column type
  • Adjust column order
  • Add and modify Bloom Filter
  • Add and delete bitmap index

This document mainly describes how to create a Schema Change job, as well as some considerations and frequently asked questions about Schema Change.

Glossary​

  • Base Table: When each table is created, it corresponds to a base table. The base table stores the complete data of this table. Rollups are usually created based on the data in the base table (and can also be created from other rollups).
  • Index: Materialized index. Rollup or Base Table are both called materialized indexes.
  • Transaction: Each import task is a transaction, and each transaction has a unique incrementing Transaction ID.
  • Rollup: Roll-up tables based on base tables or other rollups.

Basic Principles​

The basic process of executing a Schema Change is to generate a copy of the index data of the new schema from the data of the original index. Among them, two parts of data conversion are required. One is the conversion of existing historical data, and the other is the conversion of newly arrived imported data during the execution of Schema Change.

+----------+
| Load Job |
+----+-----+
     |
     | Load job generates both origin and new index data
     |
     |      +------------------+ +---------------+
     |      | Origin Index     | | Origin Index  |
     +------> New Incoming Data| | History Data  |
     |      +------------------+ +------+--------+
     |                                  |
     |                                  | Convert history data
     |                                  |
     |      +------------------+ +------v--------+
     |      | New Index        | | New Index     |
     +------> New Incoming Data| | History Data  |
            +------------------+ +---------------+

Before starting the conversion of historical data, Doris will obtain a latest transaction ID. And wait for all import transactions before this Transaction ID to complete. This Transaction ID becomes a watershed. This means that Doris guarantees that all import tasks after the watershed will generate data for both the original Index and the new Index. In this way, when the historical data conversion is completed, the data in the new Index can be guaranteed to be complete.

Create Job​

The specific syntax for creating a Schema Change can be found in the help ALTER TABLE COLUMN for the description of the Schema Change section .

The creation of Schema Change is an asynchronous process. After the job is submitted successfully, the user needs to view the job progress through the SHOW ALTER TABLE COLUMN command.

View Job​

SHOW ALTER TABLE COLUMN You can view the Schema Change jobs that are currently executing or completed. When multiple indexes are involved in a Schema Change job, the command displays multiple lines, each corresponding to an index. For example:

mysql> SHOW ALTER TABLE COLUMN\G;
*************************** 1. row ***************************
        JobId: 20021
    TableName: tbl1
   CreateTime: 2019-08-05 23:03:13
   FinishTime: 2019-08-05 23:03:42
    IndexName: tbl1
      IndexId: 20022
OriginIndexId: 20017
SchemaVersion: 2:792557838
TransactionId: 10023
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)
  • JobId: A unique ID for each Schema Change job.
  • TableName: The table name of the base table corresponding to Schema Change.
  • CreateTime: Job creation time.
  • FinishedTime: The end time of the job. If it is not finished, "N/A" is displayed.
  • IndexName: The name of an Index involved in this modification.
  • IndexId: The unique ID of the new Index.
  • OriginIndexId: The unique ID of the old Index.
  • SchemaVersion: Displayed in M: N format. M is the version of this Schema Change, and N is the corresponding hash value. With each Schema Change, the version is incremented.
  • TransactionId: the watershed transaction ID of the conversion history data.
  • State: The phase of the operation.
    • PENDING: The job is waiting in the queue to be scheduled.
    • WAITING_TXN: Wait for the import task before the watershed transaction ID to complete.
    • RUNNING: Historical data conversion.
    • FINISHED: The operation was successful.
    • CANCELLED: The job failed.
  • Msg: If the job fails, a failure message is displayed here.
  • Progress: operation progress. Progress is displayed only in the RUNNING state. Progress is displayed in M/N. Where N is the total number of copies involved in the Schema Change. M is the number of copies of historical data conversion completed.
  • Timeout: Job timeout time. Unit of second.

Cancel Job​

In the case that the job status is not FINISHED or CANCELLED, you can cancel the Schema Change job with the following command: CANCEL ALTER TABLE COLUMN FROM tbl_name;

Best Practice​

Schema Change can make multiple changes to multiple indexes in one job. For example: Source Schema:

+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
+-----------+-------+------+------+------+---------+-------+

You can add a row k4 to both rollup1 and rollup2 by adding the following k5 to rollup2:

ALTER TABLE tbl1
ADD COLUMN k4 INT default "1" to rollup1,
ADD COLUMN k4 INT default "1" to rollup2,
ADD COLUMN k5 INT default "1" to rollup2;

When completion, the Schema becomes:

+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
+-----------+-------+------+------+------+---------+-------+

As you can see, the base table tbl1 also automatically added k4, k5 columns. That is, columns added to any rollup are automatically added to the Base table.

At the same time, columns that already exist in the Base table are not allowed to be added to Rollup. If you need to do this, you can re-create a Rollup with the new columns and then delete the original Rollup.

Modify Key column​

Modifying the Key column of a table is done through the key keyword. Let's take a look at an example below.

This usage is only for the key column of the duplicate key table

Source Schema :

+-----------+-------+-------------+------+------+---------+-------+
| IndexName | Field | Type        | Null | Key  | Default | Extra |
+-----------+-------+-------------+------+------+---------+-------+
| tbl1      | k1    | INT         | No   | true | N/A     |       |
|           | k2    | INT         | No   | true | N/A     |       |
|           | k3    | varchar(20) | No   | true | N/A     |       |
|           | k4    | INT         | No   | false| N/A     |       |
+-----------+-------+-------------+------+------+---------+-------+

The modification statement is as follows, we will change the degree of the k3 column to 50

alter table example_tbl modify column k3 varchar(50) key null comment 'to 50'

When done, the Schema becomes:

+-----------+-------+-------------+------+------+---------+-------+
| IndexName | Field | Type        | Null | Key  | Default | Extra |
+-----------+-------+-------------+------+------+---------+-------+
| tbl1      | k1    | INT         | No   | true | N/A     |       |
|           | k2    | INT         | No   | true | N/A     |       |
|           | k3    | varchar(50) | No   | true | N/A     |       |
|           | k4    | INT         | No   | false| N/A     |       |
+-----------+-------+-------------+------+------+---------+-------+

Because the Schema Chanage job is an asynchronous operation, only one Schema chanage job can be performed on the same table at the same time. To check the operation status of the job, you can use the following command

SHOW ALTER TABLE COLUMN\G;

Notice​

  • Only one Schema Change job can be running on a table at a time.

  • Schema Change operation does not block import and query operations.

  • The partition column and bucket column cannot be modified.

  • If there is a value column aggregated by REPLACE in the schema, the Key column is not allowed to be deleted.

     If the Key column is deleted, Doris cannot determine the value of the REPLACE column.           All non-Key columns of the Unique data model table are REPLACE aggregated.     

  • When adding a value column whose aggregation type is SUM or REPLACE, the default value of this column has no meaning to historical data.

     Because the historical data has lost the detailed information, the default value cannot actually reflect the aggregated value.     

  • When modifying the column type, fields other than Type need to be completed according to the information on the original column.

     If you modify the column k1 INT SUM NULL DEFAULT" 1 " as type BIGINT, you need to execute the following command:

```ALTER TABLE tbl1 MODIFY COLUMN `k1` BIGINT SUM NULL DEFAULT "1"; ```

Note that in addition to the new column types, such as the aggregation mode, Nullable attributes, and default values must be completed according to the original information.

  • Modifying column names, aggregation types, nullable attributes, default values, and column comments is not supported.

FAQ​

  • the execution speed of Schema Change

    At present, the execution speed of Schema Change is estimated to be about 10MB / s according to the worst efficiency. To be conservative, users can set the timeout for jobs based on this rate.

  • Submit job error Table xxx is not stable. ...

    Schema Change can only be started when the table data is complete and unbalanced. If some data shard copies of the table are incomplete, or if some copies are undergoing an equalization operation, the submission is rejected.      Whether the data shard copy is complete can be checked with the following command: ADMIN SHOW REPLICA STATUS FROM tbl WHERE STATUS != "OK";

    If a result is returned, there is a problem with the copy. These problems are usually fixed automatically by the system. You can also use the following commands to repair this table first:
    ADMIN REPAIR TABLE tbl1;

    You can check if there are running balancing tasks with the following command:

    SHOW PROC "/cluster_balance/pending_tablets";

    You can wait for the balancing task to complete, or temporarily disable the balancing operation with the following command:

    ADMIN SET FRONTEND CONFIG ("disable_balance" = "true");

Configurations​

FE Configurations​

  • alter_table_timeout_second: The default timeout for the job is 86400 seconds.

BE Configurations​

  • alter_tablet_worker_count: Number of threads used to perform historical data conversion on the BE side. The default is 3. If you want to speed up the Schema Change job, you can increase this parameter appropriately and restart the BE. But too many conversion threads can cause increased IO pressure and affect other operations. This thread is shared with the Rollup job.

More Help​

For more detailed syntax and best practices used by Schema Change, see ALTER TABLE COLUMN command manual, you can also enter HELP ALTER TABLE COLUMN in the MySql client command line for more help information.

Report issue
ASF
Resources
Community
Join the community
Copyright © 2024 The Apache Software Foundation,Licensed under the Apache License, Version 2.0. Apache, Doris, Apache Doris, the Apache feather logo and the Apache Doris logo are trademarks of The Apache Software Foundation.