Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

When I traveled through time using SAP HANA!!

former_member198127
Participant
‎02-12-2013 2:30 AM

Yes! SAP HANA can make time travel happen. Literally!!

(Image source: http://images5.fanpop.com/image/photos/26500000/Back-To-The-Future-Trilogy-back-to-the-future-265816...)

During our exploration of SAP HANA, we found that SAP has made time travel possible via SAP HANA. This is because SAP HANA runs on SAP In-Memory Computing Engine, which provides support for History Tables. These tables, known as 'HISTORY COLUMN' tables, have session type as ‘HISTORY’ and they support time travel queries.

‘Time travel’ queries are the queries against historical states of the database. In a way, it allows to fetch the historical values (say, some inventory statistics for date 1 Jan 2011 in the table CURRENT_INVENTORY), without actually having to load the data from backups taken on 1 Jan 2011.

This concept (and specially this emotional touch of TRAVELLING through time) caught us, and we ended up exploring this concept in the following manner. All the steps and information sources were duly documented, so that other interested enthusiasts can also enjoy this experience in their own labs.

Following is the basic procedure for using Time Travel Queries in SAP HANA:

  1. Create History Tables
  2. Turn Auto Commit Off
  3. Keep track of commit IDs (or connection IDs) dealing with the History tables
  4. Execute Time Travel Queries on the History Tables

Creating History tables

The SAP HANA database provides support for a new table type called History Tables. In such History Tables, updates to and deletion of a record results in a new version of the existing record, with the original record being invalidated.

Syntax for creating a History Table:

Let us create a simple history table MODEL_HISTORY_TABLE, which holds the information about the ‘key’ as the primary key, and a column ‘value’ that holds relevant values.

CREATE HISTORY COLUMN TABLE "PRACTICE_TEST"."MODEL_HISTORY_TABLE"

("KEY" VARCHAR (2) NOT NULL DEFAULT '',

"VALUE" INTEGER,

PRIMARY KEY ("KEY")

);

In case it is subsequently determined that a standard column table requires history, then the table can be modified into a history table using the alter table command. For more details, see the link CREATE TABLE, and the document ‘0204 - Modeling - Time Travel Example.pdf’, section ‘Introduction’,
which is available as a zipped content at http://www.experiencesaphana.com/servlet/JiveServlet/downloadBody/2171-102-4-4396/SAPHANA_TechnicalD....

Notes: [Source: http://help.sap.com/hana/html/sql_create_table_history_time_travel.html] 

  • A history table must have a primary key
  • The session type can be checked from the column, SESSION_TYPE of the system table SYS.TABLES. Use the following code to check the session type:

Select SCHEMA_NAME, TABLE_NAME, TABLE_TYPE, SESSION_TYPE from sys.tables where table_name like 'MODEL_HISTORY_TABLE' 

Output:
 
 

     Figure 1. Session_Type for a history table

Turn Auto Commit Off

In order for time travel to work, the Auto commit option of SAP HANA DB needs to be turned off. This limits the number of Commit IDs that needs to be worked with. Besides, if not done, an exception will be thrown with an appropriate error message. (Reference: http://help.sap.com/hana/html/sql_create_table_history_time_travel.html ).

Steps for turning off Auto Commit Option:

  1. Open the Properties tab of the SQL Editor: In SAP HANA editor, when the SQL Editor is open, click: Windows > Show View > Properties

     

   2. In the properties window at the bottom of screen, select ‘Off’ against “Auto Commit” option.

      

Adding initial data into the table

To insert the initial few rows of data, following syntax statements were used:

INSERT INTO "PRACTICE_TEST"."MODEL_HISTORY_TABLE" VALUES ('AA', 1);

INSERT INTO "PRACTICE_TEST"."MODEL_HISTORY_TABLE" VALUES ('BB', 2);

INSERT INTO "PRACTICE_TEST"."MODEL_HISTORY_TABLE" VALUES ('CC', 2);

COMMIT;

UPDATE "PRACTICE_TEST"."MODEL_HISTORY_TABLE" MERGE DELTA INDEX;

Since auto-commit is turned off, writing a commit statement and then doing a merge delta after every set of transactions is a must.

Keeping track of commit IDs / connection IDs

The historical state of the History table can be referenced in two ways, either by using the Commit ID, or via UTC time stamping.  Details on actual usage is described in the next section (Executing Time Travel Queries).

Since Auto Commit has been turned off, the COMMIT command is necessary to ensure that the data is inserted into the table. So after every transactions is complete, commit statement should be used to commit the values into the database. Each commit statement SAP HANA is marked internally with a commit ID, which can be viewed using following syntax:

SELECT LAST_COMMIT_ID FROM M_TRANSACTIONS

WHERE CONNECTION_ID = CURRENT_CONNECTION;

<commit_id> can also be obtained from m_history_index_last_commit_id after each commit. These values are used later on to reference back to any historical state of the database, when the transactions were performed on the database.

The history of transactions can be validated by viewing the default hidden columns ‘$validfrom$’ and ‘$validto$’ of the history tables:

SELECT *, "$validfrom$", "$validto$"

FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" ('REQUEST_FLAGS'='ALLROWS');

The hidden columns “$validfrom$” and “$validto$” (case sensitive) contain the Commit ID when the record was added – “$validfrom$” – and superceded or deleted - “$validto$”.

A question mark (?) under the $validto$ column represents that the data in these rows is still valid (they don’t have any end date yet).

Adding more tables and data

To make our example more lively, let us add few more tables and rows.

Let us add another history table ‘COLOUR_HISTORY_TABLE’ that holds information about the ‘VALUE’ (same as used in the above history table),
and another column ‘HUE’.

Creating another table

CREATE HISTORY COLUMN TABLE "PRACTICE_TEST"."COLOUR_HISTORY_TABLE"(

"VALUE" INTEGER NOT NULL DEFAULT 0,

"HUE" VARCHAR (10),

PRIMARY KEY ("VALUE"));

Adding data

INSERT INTO "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" VALUES (1, 'GREEN');

INSERT INTO "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" VALUES (2, 'BLUE');

INSERT INTO "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" VALUES (3, 'RED');

COMMIT;

UPDATE"PRACTICE_TEST"."COLOUR_HISTORY_TABLE" MERGE DELTA INDEX;

Viewing data

Now let’s do a simple join to view the data in the two tables

SELECT M."KEY", C."HUE" FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" M

JOIN "PRACTICE_TEST"."COLOUR_HISTORY_TABLE"
C ON M."VALUE" = C."VALUE";

The result for the above query, based on the data entered till now would be as follows:

Updating the records to generate some HISTORY

Now let us make some updates in the records, so that the records will have some history of updates since the time they were created.

UPDATE "PRACTICE_TEST"."MODEL_HISTORY_TABLE" SET "VALUE" = 4 WHERE "KEY" = 'AA';

UPDATE "PRACTICE_TEST"."MODEL_HISTORY_TABLE" SET "VALUE" = 5 WHERE "KEY" = 'CC';

COMMIT;

UPDATE"PRACTICE_TEST"."MODEL_HISTORY_TABLE" MERGE DELTA INDEX;

Executing Time Travel Queries

There are a number of ways of using “Time Travel” queries. We can either track the historic values using the commit ID for the transactions, or by using the UTC time-stamping for the transactions, or by setting the session back to some earlier historic session.

For better understanding, just repeating what we have done so far. We created two History tables as follows:

   

Also, the below graphic shows the entire history of the updates made in both the tables, along with respective commit IDs and timestamp values.

The above representation is good for quick understanding in a demo purpose, but in real life, there are no such views available. For that, we need some alternate ways of getting all the required commit ID and the timestamps. Following section shows steps for doing that.

Getting all the Commit IDs and UTC Timestamps

The entire list of all the commit IDs/Commit-time can be obtained using the following syntax:

SELECT * FROM TRANSACTION_HISTORY ORDERBY COMMIT_ID;

This results into the following list of commit IDs:

Note: In our case, the number of transaction were limited, so we have listed the Commit IDs for all the transactions made. In case this list is huge, the relevant commit IDs can be tracked by adding where clause in the above statement, as mentioned in following example:

SELECT * FROM TRANSACTION_HISTORY

WHERE COMMIT_ID >= 200610

ORDERBY COMMIT_ID;

Now the TIME TRAVEL queries can be executed, i.e. historical values can be retrieved in following possible ways:

SELECT VALUES AS OF COMMIT_ID:

Use the select statement with phrase ‘AS OF COMMIT ID’ to refer to some historical values of the history table.

The historical values of the table, when the tables were first committed (i.e. Commit ID= 200611) can be derived using the following syntax:

select * from "PRACTICE_TEST"."MODEL_HISTORY_TABLE"

AS OF COMMIT ID 200611

Until this commit ID, there was no data entered in the second table ("PRACTICE_TEST"."COLOUR_HISTORY_TABLE"), so doing a join operation between the two tables for this commit ID would result into NULL return.

SELECT M."KEY", C."HUE" FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" M

JOIN "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" C ON M."VALUE" = C."VALUE"

AS OF COMMIT ID 200611;

As expected, it was all blank result set, since at this commit ID, there is no data in the COLOUR_HISTORY_TABLE.

The output for the join between two tables will be available only after the instance since when both tables had some valid data, which is after the commit ID 200721. So repeating the same join query with this commit ID 200721 should fetch some valid results:

This way, simple (or bit complex) queries can be used to browse through historic data without having to actually load the old data, or keeping multiple copies of data in backup, saving enormous amount of storage space and efforts.

Alternatively, the time stamping information of transactions (called UTC timestamps) can also be used for browsing values in historic instances.

SELECT VALUES AS OF UTCTIMESTAMP:

Using the select statement with phrase ‘AS OF UTCTIMESTAMP’ also refers to historical values of the history table based on the UTC time stamps made in the history table.

As already discussed, the UTC Timestamp can be identified from the sys.transaction_history table.

If specific commit ID is known, corresponding commit time can be identified using following syntax:

select commit_time from sys.transaction_history

where commit_id = 201039;

Output: '2013-02-04 12:01:42.682'

This timestamp can be used to refer to historical values of the tables using the following syntax:

SELECT M."KEY", C."HUE" FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" M

JOIN "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" C ON M."VALUE" = C."VALUE"

AS OF utctimestamp '2013-02-04 12:01:42.682';

Please note that at the commit ID 201039 (or time stamp value '2013-02-04 12:01:42.682'), the data in the first table (MODEL_HISTORY_TABLE) was already altered, and so now I has only one common value, which appears as the output of above join statement.

SET HISTORY SESSION TO COMMIT ID

A database session can be set back to a certain point-in-time, based on the provided value for commit_id. For doing this SET HISTORY SESSION can be used.

SET HISTORY SESSIONTO COMMIT ID 201039;

SELECT M."KEY", C."HUE" FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" M

JOIN "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" C ON M."VALUE" = C."VALUE"

This would result in same output as above, since we are referring to the same instance of the database at exactly the same historic time as the above query.

By setting the HISTORY SESSION to a particular Commit ID, all subsequent queries will return data as of that Commit ID, until the command is cancelled by setting the history session to another value, or cancelling it by setting it to NOW.

SET HISTORY SESSION TO NOW;

SET HISTORY SESSION TO UTCTIMESTAMP

In similarity to above method, the database session can be set back to a certain point-in-time, based on the provided value for UTC time stamp.

SET HISTORY SESSIONTO utctimestamp '2013-02-04 12:01:42.682';

SELECT M."KEY", C."HUE" FROM "PRACTICE_TEST"."MODEL_HISTORY_TABLE" M

JOIN "PRACTICE_TEST"."COLOUR_HISTORY_TABLE" C ON M."VALUE" = C."VALUE"

Again, we would get the same output. In a way, these (time travelling via using SELECT statement with commit ID, UTC Timestamps or by using SET HISTORY SESSION) are all different ways of reaching to the same results.

Important Notes:

[Source: http://help.sap.com/hana/html/sql_create_table_history_time_travel.html]

  • Autocommit has to be turned off when a session should be restored (otherwise an exception will be thrown with an appropriate error message)
  • Non-history tables in restored sessions always show their current snapshot
  • Only data query statement (select) is allowed inside restored sessions.

Conclusion

For demonstration purposes, implementing time travel seems to be bit easy task. But in real life scenarios, implementation would be much more complex, and would demand entire new set of plans and designs for fully utilizing this new concept.

We would love to hear more about real life scenarios where this concept could make a real difference. So do provide your feedback and suggestions about this topic.

References

33 Comments
Labels in this area
Popular Blog Posts