Lindorm:Separate hot and cold data based on a custom time column

Prerequisites

Before you begin, make sure you have:

• Cold storage enabled on your instance. For details, see Storage-optimized Lindorm instances

• LindormTable version 2.4.3 or later. To check or upgrade your version, see LindormTable version guide and Perform a minor version update

• A connection to LindormTable via Lindorm-cli. For details, see Connect to and use LindormTable using Lindorm-cli

• The STORAGE_POLICY of the target table or column family is not set to COLD. A cold storage table cannot be used for hot and cold data separation. To modify storage properties, see Configure cold storage

Constraints

• This feature is supported only for Lindorm SQL. HBase-compatible operations are not supported.

• When a row's value in the custom time column crosses the hot data boundary, the entire row is archived to cold storage.

• The custom time column must satisfy all of the following conditions:

  • It must be a primary key column.

  • It cannot be the first column of the primary key.

  • Its data type must be BIGINT or TIMESTAMP. If the column does not exist, has no data, or contains data of an incorrect type, all data is written to hot storage.

Configure hot and cold data separation for a table

-- Default time unit: milliseconds (ms)
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');

-- Set time unit to seconds (s)
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|TIMEUNIT=SECONDS');

-- Set time unit to minutes (min)
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|TIMEUNIT=MINUTES');

-- Enable hot and cold data separation with a one-day boundary, using p2 (default unit: milliseconds)
ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2';

-- Use p2 with time unit set to seconds
ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2|TIMEUNIT=SECONDS';

-- Use p2 with time unit set to minutes
ALTER TABLE dt SET 'CHS' ='86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN' = 'COLUMN=p2|TIMEUNIT=MINUTES';

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

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

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

Configure hot and cold data separation for a secondary index

A secondary index is stored within the base table, so you configure it the same way. The following examples use the dt table created in the previous section.

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

-- Enable hot and cold data separation on an existing index
ALTER TABLE `dt.idx` SET 'CHS' = '86400', 'CHS_L2' = 'storagetype=COLD', 'CHS_COLUMN'='COLUMN=p2';

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

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

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

Write data

Writing data to a table with hot and cold data separation enabled works the same as writing to a standard table. Data is initially stored in hot storage (Medium or Compute-optimized). A row is archived to cold storage during compaction when the following condition is met:

Current time - Value of the time column > Value of CHS

Query data

Hot and cold data reside in the same table. All queries target that single table. When you filter on the custom time column with a time range, LindormTable uses it to determine whether to scan hot storage, cold storage, or both—so the query only reads the relevant tier. Without a time range filter, the query may hit cold data, and throughput is limited by cold storage capacity. For details, see Storage-optimized Lindorm instances.

To query only hot data when you cannot filter on the custom time column, use the _l_hot_only_ hint.

-- p2 is the custom time column
SELECT * FROM dt WHERE p1 = 10 AND p2 = 10;

-- p2 is the custom time column
SELECT * FROM dt WHERE p2 > 10 AND p2 < 1000;

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

Example: Internet of Vehicles scenario

In Internet of Vehicles (IoV) scenarios, the primary key typically consists of vin (Vehicle Identification Number) and ts (timestamp). Define ts as the custom time column. When querying data for a specific vehicle within a time range, LindormTable automatically determines whether to access hot or cold storage based on the query conditions.

USE test;
CREATE TABLE dt (
  vin varchar, ts bigint, c1 varchar, c2 varchar,
  constraint pk primary key(vin, ts)
) WITH (COMPRESSION = 'ZSTD', CHS ='86400', CHS_L2 = 'storagetype=COLD', CHS_COLUMN = 'COLUMN=ts');

-- Query data for a specific vehicle within a time range
SELECT * FROM dt WHERE vin = 'xxxx' AND ts > 1675065122000 AND ts < 1675975222000;

FAQ

If I update existing cold data, does the updated data remain cold?

Yes, if the update does not change the value in the custom time column. If the value changes, LindormTable re-evaluates the row's hot/cold status based on the new value.

For example: a table has primary key columns p1 and p2, and non-primary key columns c1 and c2. A row has values p1=row1, p2=Jan-28-2023, c1="c1", c2="c2". With a 1-day boundary (CHS) and a current date of Jan-30-2023, the row is cold. Updating c1 or c2 keeps the row cold. Updating p2 to Jan-30-2023 makes the row hot, and it becomes cold again on Feb-01-2023.

If a row has no value in the custom time column, is the data separated?

No. Rows with no value in the custom time column are kept in hot storage.