ALTER TABLE ROLLUP

Description

This statement is used to perform a rollup modification operation on an existing table. The rollup is an asynchronous operation, and the task is returned when the task is submitted successfully. After that, you can use the SHOW ALTER command to view the progress.

grammar:

ALTER TABLE [database.]table alter_clause;

The alter_clause of rollup supports the following creation methods

1. Create a rollup index

grammar:

ADD ROLLUP rollup_name (column_name1, column_name2, ...)
[FROM from_index_name]
[PROPERTIES ("key"="value", ...)]

properties: Support setting timeout time, the default timeout time is 1 day.

2. Create rollup indexes in batches

grammar:

ADD ROLLUP [rollup_name (column_name1, column_name2, ...)
                    [FROM from_index_name]
                    [PROPERTIES ("key"="value", ...)],...]

Notice:

• If from_index_name is not specified, it will be created from base index by default
• Columns in rollup table must be columns already in from_index
• In properties, the storage format can be specified. For details, see CREATE TABLE

3. Delete rollup index

grammar:

DROP ROLLUP rollup_name [PROPERTIES ("key"="value", ...)]

4. Batch delete rollup index

grammar:

DROP ROLLUP [rollup_name [PROPERTIES ("key"="value", ...)],...]

Notice:

• cannot delete base index

Example

1. Create index: example_rollup_index, based on base index (k1,k2,k3,v1,v2). Columnar storage.

ALTER TABLE example_db.my_table
ADD ROLLUP example_rollup_index(k1, k3, v1, v2);

2. Create index: example_rollup_index2, based on example_rollup_index (k1,k3,v1,v2)

ALTER TABLE example_db.my_table
ADD ROLLUP example_rollup_index2 (k1, v1)
FROM example_rollup_index;

3. Create index: example_rollup_index3, based on base index (k1,k2,k3,v1), with a custom rollup timeout of one hour.

ALTER TABLE example_db.my_table
ADD ROLLUP example_rollup_index(k1, k3, v1)
PROPERTIES("timeout" = "3600");

4. Delete index: example_rollup_index2

ALTER TABLE example_db.my_table
DROP ROLLUP example_rollup_index2;

5. Batch Delete rollup index

ALTER TABLE example_db.my_table
DROP ROLLUP example_rollup_index2,example_rollup_index3;

4. Keywords

ALTER, TABLE, ROLLUP, ALTER TABLE

Best Practice