Use hints to set timestamps for data versioning-Lindorm(Lindorm)-阿里云帮助中心

This topic describes how to use hints in LindormTable SQL to set timestamps for data versioning.

Supported engine and version

The hint syntax is available only for LindormTable engine version 2.3.1 or later.

Note

To view or upgrade your current version, see Release notes for LindormTable and Upgrade a minor version.

How it works

In LindormTable, each column value receives a timestamp when written. By default, this timestamp is the server time of the write operation and acts as the column's version identifier. A larger timestamp indicates a newer version. You can also specify a custom timestamp. If you want a wide-column table to store multiple versions of data, you must set the version attribute 'VERSIONS' = 'n' when you create the table, where n is the maximum number of versions to retain for each column. To query multiple data versions, you must specify the appropriate hints in your query. The following table describes the hints used for data versioning.

Hint	Description	Applicable statement
_l_ts_(N)	Reads or writes data with the specified timestamp.	UPSERT, SELECT
_l_versions_(N)	Returns the N most recent versions of data in the result set.	SELECT
_l_ts_min_(N)	Filters the result set to return data with a timestamp greater than or equal to N.	SELECT
_l_ts_max_(N)	Filters the result set to return data with a timestamp less than N.	SELECT

Procedure

Set timestamps and query data

In LindormTable, you can set a timestamp for a non-primary key column and use that timestamp to query specific data versions.

Create a test table and specify the number of versions to retain:
```
CREATE TABLE t_test_versions_2 (c1 INT , c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1)) WITH(VERSIONS='5');
```
Note
You can execute the ALTER TABLE table_name SET 'VERSIONS' = 'num'; statement to specify or modify the number of versions for a table, where table_name is the table name and num is the number of versions.

To set a timestamp, you must specify the appropriate hint. For example:

UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c3) values (1, '11');
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c3) values (1, '22');
UPSERT /*+ _l_ts_(1000)  */ INTO t_test_versions_2(c1, c2) values (1, 1);
UPSERT /*+ _l_ts_(2001)  */ INTO t_test_versions_2(c1, c2) values (2, 1);
UPSERT /*+ _l_ts_(2002)  */ INTO t_test_versions_2(c1, c2) values (2, 2);
UPSERT /*+ _l_ts_(2003)  */ INTO t_test_versions_2(c1, c2) values (2, 3);
UPSERT /*+ _l_ts_(2004)  */ INTO t_test_versions_2(c1, c2) values (2, 4);
UPSERT /*+ _l_ts_(2005)  */INTO t_test_versions_2(c1, c2) values (2, 5);
UPSERT /*+ _l_ts_(2006)  */ INTO t_test_versions_2(c1, c2) values (2, 6);

Query a specific data version by using a timestamp. To view the timestamp of a column, you must specify the column and its extended column in your query. The name of the extended column follows the format column_name_l_ts.
For example, to view the timestamp of column c3, you must include both c3 and c3_l_ts in your SELECT statement.
Important
When querying version information, if you select an extended column (for example, 'c3_l_ts') without including a version-related hint, the extended column in the result set returns null. Therefore, the query statement must also include a version-related hint, such as /*+ _l_versions_(1) */ or /*+ _l_ts_min_(N) */.
- Example 1: Return data at timestamp 1000.
```
SELECT /*+ _l_ts_(1000) */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;
```
  The following result is returned:
```
+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 11 | 1000    |
+----+----+---------+
```
- Example 2: Query data with timestamps in the range [1000,2001).
```
SELECT /*+ _l_ts_min_(1000), _l_ts_max_(2001)  */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;
```
  The following result is returned:
```
+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 11 | 1000    |
+----+----+---------+
```
- Example 3: Return the N most recent versions of data by using the _l_versions_(N) hint.
```
SELECT /*+ _l_versions_(1) */ c1, c3, c3_l_ts FROM t_test_versions_2 WHERE c1 = 1;
```
  The following result is returned:
```
+----+----+---------+
| c1 | c3 | c3_l_ts |
+----+----+---------+
| 1  | 22 | 2001    |
+----+----+---------+
```
- Returns the two most recent versions for each column. Versions with the same timestamp are grouped into a single row. The _l_versions_(N) hint retrieves timestamp data for all non-primary key columns.
```
SELECT /*+ _l_versions_(2)  */ c1, c2, c3, c2_l_ts, c3_l_ts FROM t_test_versions_2;
```
  The following result is returned:
```
+----+------+------+---------+---------+
| c1 |  c2  |  c3  | c2_l_ts | c3_l_ts |
+----+------+------+---------+---------+
| 1  | null | 22   | null    | 2001    |
| 1  | 1    | 11   | 1000    | 1000    |
| 2  | 6    | null | 2006    | null    |
| 2  | 5    | null | 2005    | null    |
+----+------+------+---------+---------+
```
- Example 5: The number of versions returned is capped by the table's 'VERSIONS' attribute. For example, even though this query requests six versions with _l_versions_(6), it only returns five because the table was created with 'VERSIONS'='5'.
```
SELECT /*+ _l_versions_(6)  */ c1, c2, c2_l_ts FROM t_test_versions_2 WHERE c1=2;
```
  The following result is returned:
```
+----+----+---------+
| c1 | c2 | c2_l_ts |
+----+----+---------+
| 2  | 6  | 2006    |
| 2  | 5  | 2005    |
| 2  | 4  | 2004    |
| 2  | 3  | 2003    |
| 2  | 2  | 2002    |
+----+----+---------+
```