Working with Temporal Tables

SAP HANA already supports history tables.

• Allow time-based queries on previous versions of a record.
• Write-operations don’t physically overwrite an existing record, but insert a new version.
• Each version has a validity period, which is based on commit IDs.
• Proprietary implementation. Doesn’t match SQL standard.

For a long time, SAP HANA SQL has supported the creation of history tables. This was done by issuing a CREATE HISTORY COLUMN TABLE <table_name> SQL statement.  As a result of this statement, a log of all transactions executed against a history table would be permanently retained in system tables. The log contained the unique commit ID of the transaction (one is created for each and every commit SQL statement executed in SQL) along with the UTC timestamp of when that commit executed. The commit ID and/or the UTC timestamp allowed a developer, when selecting records from a table, to retrieve not just the current version of the record but any and all prior versions of that record that existed at any point in the past (known as a time travel query).

To retrieve prior versions of a record, the developer would execute the SET HISTORY SESSION TO <when> statement where <when> would be set to 1 for the unique commit ID, 2 for the UTC timestamp of when the commit occurred, or 3 using the SQL keyword NOW (indicating to only retrieve current versions of the record).

While useful, there were several limitations to using the history table approach. The first was that the commit ID was HANA-instance-specific and could not be migrated to other HANA instances. In addition, the commit ID was not externally accessible (from ABAP, for example). Finally, the design of history tables was not based off of any SQL standard.

SQL:2011 or ISO/IEC 9075:2011 was officially adopted in December 2011. Among other areas, it established a standardized way of designing temporal tables in database systems. SAP HANA 2.0 SPS03 and later supports the creation of temporal tables in accordance with the SQL:2011 specification. 

A temporal table gives the developer not only the ability to execute "time travel" queries, but also to define validity periods for a record.

Temporal Tables are tables whose records are associated with one or more temporal periods.

System-Versioned Tables allow change tracking on database tables.

• Validity periods are automatically maintained by the database whenever a record is changed.
• Retrospective changes to the validity period of a record are strictly prohibited. A new version is inserted for every change to preserve a records history.
• Temporal information must have a very high granularity.

Application-Time Period Tables capture the time, in which a record is valid in the real world.

• Validity periods are determined by the application.
• Applications can update validity period of a record, e.g., to correct errors.
• Temporal information can arbitrarily reflect the past, present or future at any granularity.

Bitemporal Tables combine system-versioned tables and application-time period tables.

Temporal periods are based on already existing SQL data types.

• Two columns, defining START and END.
• Temporal data type, e.g., TIMESTAMP.

When creating temporal tables, the developer has several choices. 

1. The first option is to create system-versioned tables, which will result in any and all changes to records in the table being stored in an associated history table (not to be confused with history tables discussed previously). With system-versioned tables, specific validity period columns will be added to the table and the type of those columns will be the built-in HANA SQL type TIMESTAMP. With system-versioned tables, any and all changes to a record result in a new version (and thus the start of a new validity period) of the record being created. Under no circumstances with system-versioned tables can the validity period of a record be retroactively adjusted. Therefore, every version of the record that has ever existed will be preserved with a corresponding start and end validity period.

2. The second option is to create application-time period tables. With this option, the developer will create their own validity period columns (with whatever names and SQL data types preferred) for a record that is freely modifiable based on business logic needs of record maintenance. Therefore, the start and end validity periods are adjustable based on business needs. In addition, the developer can issue transactions that change certain fields of a record without necessarily changing the validity period of the record. These validity period columns will be ignored for all system-versioned table operations. 

3. The final option that the developer has in regard to temporal table definition is to apply both approaches. This is known as a bitemporal table definition. In this case, both system-versioned validity period fields and application-time period validity period fields will be created, allowing the developer to implement both change tracking and business logic validity in the table design.

Column tables are the only type supported for system-versioned tables. In addition, only the HANA SQL type TIMESTAMP is supported for the VALID_FROM and VALID_TO columns.

The structure of the history table must match the structure of the column table for which it is designated as the history table. The VALIDATED HANA SQL keyword can be used to confirm that the structure of the history table is correct, but is not required.

DML statements on system-versioned tables do not differ from DML statements on regular tables.

• Internal triggers take care of archiving updated or deleted records.
• Archived records are physically moved to the HISTORY table as part of the same transaction.
• Current versions stay in the CURRENT table.
• TIMESTAMP is determined by the first write access to a system-versioned table of the current transaction.

For executed transactions that result in changes to a system-versioned table, the timestamp is determined by the first write access to a system-versioned table of the current transaction.

Current versions stay in the CURRENT table. Internal triggers take care of archiving updated or deleted records and archived records are physically moved to the HISTORY table as part of the same transaction.

... FOR SYSTEM_TIME AS OF <timestamp>

Returns all records that were active right at <timestamp>.

1234
SELECT ITEM_ID, STOCK 
    FROM STOCKS 
FOR SYSTEM_TIME 
AS OF "2017-04-28 10:36:00"

Reading ranges of versions:

… FOR SYSTEM_TIME FROM <t1> TO <t2>

• Returns all records that were active any time within the specified time range.
• Records that became active right at <t2> are not included.

1234
SELECT ITEM_ID, STOCK 
    FROM STOCKS 
FOR SYSTEM_TIME 
FROM "2017-04-28 10:33:00" TO "2017-04-28 10:37:46"

Reading ranges of versions:

… FOR SYSTEM_TIME BETWEEN <t1> AND <t2>

• Returns all records that were active any time within the specified time range.
• Records that became active right at <t2> are included as well.

1234
SELECT ITEM_ID, STOCK 
    FROM STOCKS 
FOR SYSTEM_TIME 
BETWEEN "2017-04-28 10:33:00" AND "2017-04-28 10:37:46"

Language enhancements according to the SQL:2011 standard include:

• Time period definitions based on two regular table columns, representing start and end time with close-open semantics.

• Query language extensions using temporal predicates, e.g.,

  • CONTAINS
  • OVERLAPS
  • EQUALS
  • PRECEDES
  • SUCCEEDS

There are additional keywords that can be used to formulate queries against temporal tables.