How do I use hints to implement data versioning based on timestamps - Lindorm

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

Applicable engines and versions

Hints are applicable only to LindormTable 2.3.1 and later versions.

Note

For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Overview

A timestamp is recorded for data that is written to each column of LindormTable. This timestamp indicates the server time when the data is written to the column. By default, the timestamp is used as the version ID of the data written to the column. You can also specify a timestamp for the data that you write to a column. A larger timestamp indicates a later version. To store multiple versions of a wide table column in LindormTable, you must configure the 'VERSIONS' = 'n' attribute when you create the table. In the attribute, n is a positive integer that indicates the maximum number of versions that can be stored for each column. To query versioned data based on timestamps in LindormTable, you must specify specific attributes in SQL statements. The following table describes the attributes that you can specify.

Attribute	Description	Applicable statement
_l_ts_(N)	Specifies a timestamp for the version.	UPSERT and SELECT
_l_versions_(N)	Specifies that the latest N versions of the data you query are returned.	SELECT
_l_ts_min_(N)	Specifies that versions whose timestamps are larger than N are returned.	SELECT
_l_ts_max_(N)	Specifies that versions whose timestamps are smaller than N are returned.	SELECT

Usage

Configure timestamps and query versioned data

In LindormTable, you can configure timestamps for non-primary key columns and use the timestamps to query the specified version of data.

Execute the following statement to 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 that you want to retain for the data in a table. In the statement, the table_name parameter indicates the name of the table and the num parameter indicates the number of versions that you want to retain.

Write data to the table. You must specify the _l_ts_(N) attribute in the following statements to configure timestamps.

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 the specified versions of data by using timestamps. To view the timestamps of the queried columns in the result, add the _l_ts suffix to the column names in the statement.

Example 1: Query data whose timestamp is 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 whose timestamp is in the [1000, 2001) range.

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: Query the latest N versions of the specified data by specifying the _l_versions_(N) attribute. In this example, the value of N is set to 1.

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    |
+----+----+---------+

Example 4: Query the latest two versions of all columns and display different columns with the same timestamp in the same row. If you specify the _l_versions_(N) attribute in the statement, the timestamps of all non-primary key columns are returned.

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: Query the latest six versions of the specified column. However, the VERSIONS attribute of the test table is set to 5 when the table is created. Therefore, only the latest five versions of the column are returned even if the _l_versions_(6) attribute is specified in the statement.
```
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    |
+----+----+---------+
```