Lindorm:Separately store hot data and cold data based on custom time columns

Specify a hot and cold data boundary for a table

1. Specify a custom time column and hot and cold data boundary.

   • Method 1: Specify the column and boundary for a table when you create the table.

     You can execute the following statement to create a table named dt, set the hot and cold data boundary to one day, and specify p2 as the column based on which hot and cold data is separately stored:

     CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3)) WITH(COMPRESSION = 'ZSTD', CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=p2'); 

     Parameters

     • CHS: The hot and cold data boundary for the table. Data that has been stored in the table for a period longer than this period is archived in cold storage. The unit of the value of this parameter is seconds. For example, if the value of this parameter is 86400, data is archived to cold storage after the data is stored in written to the table for 86,400 seconds, which is equal to one day.

     • COMPRESSION: The algorithm used to compress the data. This parameter applies to data in the entire table. The value of this parameter is not case-sensitive. Default value: NONE.

     • CHS_L2: The layer-2 attribute. In general, this parameter is configured to specify the storage type. Set this parameter to storagetype=COLD.

     • CHS_COLUMN: The custom time column.

   • Method 2: Use the ALTER TABLE statement to add attributes to an existing table.

     -- Hot and cold data separation is not enabled for the table when the table is created.
     CREATE TABLE dt (p1 integer, p2 bigint, p3 bigint, c1 varchar,  constraint pk primary key(p1, p2, p3));
     
     -- Enable hot and cold data separation for the dt table, set the hot and cold data boundary to one day, and specify p2 as the column based on which hot and cold data is separately stored.
     ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2';

2. (Optional) Modify the hot and cold data boundary and custom time column of the table.

   • You can execute the following statement to modify the hot and cold data boundary of the secondary index table:

     ALTER TABLE dt SET 'CHS'='1000';

   • You can execute the following statement to modify the custom time column of the secondary index table:

     ALTER TABLE dt SET 'CHS_COLUMN'='COLUMN=p3'; 

3. (Optional) Disable hot and cold data separation for the table.

   ALTER TABLE dt SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

   Note

   After you change the hot and cold data boundary or disable hot and cold data separation, the data is transferred from cold storage to hot storage after Lindorm performs the compaction operation. To immediately transfer the data from cold storage to hot storage, manually run the major_compact command.

Configure a hot and cold data boundary for a secondary index

By default, secondary indexes are stored in a table. Therefore, you can enable hot and cold data separation for a secondary index.

In this example, a secondary index is created for the dt table that is created in the preceding section.

1. Create a secondary index for the table and configure a custom time column and a hot and cold data boundary for the secondary index.

   CREATE INDEX idx on dt (c1) WITH(CHS = '86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN='COLUMN=p2');

   Note

   The custom time column of a secondary index must be the same as the custom time column of the table.

2. (Optional) Modify the hot and cold data boundary and custom time column of the secondary index table.

   • You can execute the following statement to modify the hot and cold data boundary of the secondary index table:

     ALTER TABLE `dt.idx` SET 'CHS'='10000';

   • You can execute the following statement to modify the custom time column of the secondary index table:

     ALTER TABLE `dt.idx` SET 'CHS_COLUMN'='COLUMN=p2'; 

   Note

   The name of a secondary index table is in the following format: <Table name>.<Secondary index name>. The name contains a period (.), which is a special character. Therefore, you must enclose the name of the table name to escape it when you specify the table name in the statement. For example, if the name of the table is test.idx, the table name that you specify in the statement must be `test.idx`.

3. (Optional) Disable hot and cold data separation for the secondary index table.

   ALTER TABLE `dt.idx` SET 'CHS'='', 'CHS_L2' = '', 'CHS_COLUMN'='';

Examples

• Use the GET method to query data

  // p2 indicates the custom time column.
  SELECT * FROM dt WHERE p1 = 10 AND p2 = 10;

• Use the SCAN method to query data within a specified range

  // p2 indicates the custom time column.
  SELECT * FROM dt WHERE p2 > 10 AND p2 < 1000;

  Important

  If LindormTable determines that a SELECT query is an inefficient query, LindormTable does not process the query by default and throws an exception. For more information, see SELECT.

• Use hints to query only hot data

  SELECT /*+ _l_hot_only_(true) */ * FROM dt WHERE p1>1;