Create and change an archived table - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to create and change an archived table.

Create an archived table

The following example describes how to create an archived table. The TTL table t_order and the archived table t_order_oss are used in the example.

Execute the following statement to create the TTL table t_order in the database in AUTO mode:

CREATE TABLE t_order (
    id bigint NOT NULL AUTO_INCREMENT,
    gmt_modified DATETIME NOT NULL,
    PRIMARY KEY (id, gmt_modified)
)
PARTITION BY HASH(id)
PARTITIONS 16
LOCAL PARTITION BY RANGE (gmt_modified)
INTERVAL 1 MONTH
EXPIRE AFTER 12
PRE ALLOCATE 3;

Note

If the t_order table already exists and is not a TTL table, execute the following statement to change it to a TTL table:

ALTER TABLE t_order
  LOCAL PARTITION BY RANGE (gmt_modified)
  STARTWITH '2021-01-01'
  INTERVAL 1 MONTH
  EXPIRE AFTER 12
  PRE ALLOCATE 6;

For more information, see TTL tables.

Execute the following statement to create an archive table named t_order_oss and associate the table with the t_order table:
```
CREATE TABLE t_order_oss LIKE t_order ENGINE = 'OSS' ARCHIVE_MODE = 'TTL';
```
Note
- ENGINE = 'OSS' indicates that the table engine is OSS. ARCHIVE_MODE = 'TTL' indicates that the TTL mode is used for cold data archiving.
- Archived tables can only be read. You cannot write data to archived tables by executing SQL statements.
After data in the t_order table expires and is archived to the t_order_oss table, you can view the status of the archived table on the Archived Data Details page.
You can also query the information_schema.archive view to check the status of the archived table.

DDL operations for an archived table

After you bind the TTL table to an archived table, PolarDB-X allows you to perform specific DDL operations on the original TTL table and automatically associates the DDL operations with the archived table. The following syntax can be used.

Note

You can execute the ALTER TABLE statement to modify the schema of a table. For example, you can add columns, add indexes, or change data types. For more information, see ALTER TABLE (AUTO mode).

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ALGORITHM [=] {DEFAULT | INPLACE | COPY}
  | ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | DROP DEFAULT
    }
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
}

CREATE [UNIQUE] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

DROP INDEX index_name ON tbl_name
    [algorithm_option | lock_option]

The following items describe notes on using the MODIFY COLUMN, CHANGE COLUMN clauses:

After you change a column that supports null to not null, existing nulls in the cold data table remain unchanged.
If you change the column type, the column type written to the archived table is converted to the column type of the current table when data is read.
You cannot change the shard key type when you change the column type.
Online modify column is not supported.

Only conversions within one of the following data type families are supported:

The unsigned integer type family includes tinyint unsigned, smallint unsigned, mediumint unsigned, int unsigned, and bigint unsigned. You can change the value range from small to large and change the length of data types.
The signed integer type family includes tinyint, smallint, mediumint, int, and bigint. You can change the value range from small to large and change the length of data types.
The string type family includes char and varchar. You can convert data types in this family and change the length of data types.