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 has been 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 all data in the 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 syntax to specify the column and boundary for an existing table for which hot and cold data separation is not enabled when the table is created.

     -- In this example, hot and cold data separation is not enabled for the dt 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 table:

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

   • You can execute the following statement to modify the custom time column of the 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 created in the preceding section.

1. Create a secondary index and enable hot and cold data separation.

   • Method 1: Specify a custom time column and configure a hot and cold data boundary for the secondary index when you create 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 base table.

   • Method 2: Use the ALTER TABLE syntax to specify the column and boundary for an existing secondary index for which hot and cold data separation is not enabled when the index is created.

     -- In this example, hot and cold data separation is not enabled for the secondary index named idx when the index is created. 
     -- CREATE INDEX idx on dt (c1);
     
     -- Enable hot and cold data separation for the idx secondary index, 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.idx` 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 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 with a pair of backticks (`) 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;