SAP HANA SQL for bizy geeks: Episode #3: Let's ride my cute TIME MACHINE

[former_member182913]

I may have already told you that I attended the SAP HANA focused In-Memory Computing Conference in Düsseldorf an eternity ago. Now that I am thinking about it, it was actually not even 2 months ago, but in my Entrepreneur universe, time goes by in very strange ways... Sometimes everything goes dead slow, like in the surroundings of a black hole, and the next day, you feel crazily accelerated like in the spaceship Enterprise.

One of the visitors I had a chat with in the In-Memory Computing Conference was an Oracle guy. He looked quite normal... one head, two arms, two legs, no horns, no pointed tail... and he was not in disguise, or anything... He introduced himself very openly, and we had a nice natter with each other. At some point he happened to state that SAP HANA has no good TIME TRAVEL capabilities. I directly contradicted his statement, because I had read that SAP HANA can definitely do TIME TRAVEL. However, I had not tested the functionality myself yet, although I had superficially read something in the SAP HANA SQL documentation.

Here I am not talking about Database log based TIME TRAVEL or standard Backup/Recovery capabilities... This is something every Database can do blindfolded since the beginning of relational databases time... Even the main-framish IBM AS/400 (this was the first Hardware and Database platform I worked with while at SAP... We ported SAP R/3 to this platform, which used to be EBCDIC back then...) could rewind and bring the system to any point in time using standard logs!

What I am talking about now is TIME TRAVEL capabilities for chosen tables, without the need of bringing the whole system back and forth in time.

After checking with some colleagues, reading some papers, and experimenting a bit, I created a very lean example using the SQL editor in the SAP HANA Studio.

Here is my example:

USING THE SQL EDITOR IN THE SAP HANA STUDIO

-- I will use my own Catalog Schema with my family name

SET SCHEMA DURANY;

-- Now I create a HISTORY TABLE... I'm excited!

/* By the way, since I am creating this table only for illustration purposes, and I don't expect it to grow much,

I have enough with an 8-bit unsigned TINYINT data type as "ID" for my primary key */

CREATE HISTORY COLUMN TABLE MY_TIME_MACHINE (

       "ID" TINYINT, "MOODS" VARCHAR (100),

PRIMARY KEY ("ID"));

/* Alternatively, if you originally created a non-history table and want to enable it for TIME TRAVEL later on, you can use this SQL statement:

   ALTER TABLE "WHATEVER_TABLE" CREATE HISTORY; */

/* With the statement below you can check if your table is really a HISTORY Table */

SELECT SESSION_TYPE FROM SYS.TABLES

WHERE SCHEMA_NAME = 'DURANY' AND TABLE_NAME = 'MY_TIME_MACHINE';

/* Now we need to turn Auto Commit to 'off' in the Properties Tab under the SQL editor in the SAP HANA Studio.

Up to now, I did not find the way to switch off the Autocommit using SQL syntax... I tried first with an SQL statement I

found in an older version of the SAP HANA SQL documentation, but the system spitted the following paradox:

"Error... In Autocommit mode you cannot change the Autocommit mode..."

Hmmm...

*/

-- As next step, I will insert some moods into MY_TIME_MACHINE

/* By the way, since Autocommit is now 'off', we need to COMMIT every database change.

We also need to merge the delta to the main HANA storage. */

INSERT INTO MY_TIME_MACHINE
VALUES ('1', 'I feel sad... Will I ever be happy again? Do not think so');

COMMIT;

UPDATE MY_TIME_MACHINE MERGE DELTA INDEX;

-- Let's get the COMMIT_ID after this change

SELECT LAST_COMMIT_ID FROM M_TRANSACTIONS

WHERE CONNECTION_ID = CURRENT_CONNECTION;

-- The COMMIT_ID is 500343. Let's insert more data...

DELETE FROM MY_TIME_MACHINE WHERE "ID" = '1';

INSERT INTO MY_TIME_MACHINE VALUES ('1', 'I feel happy... Did I ever feel sad? Do not think so');

COMMIT;

UPDATE MY_TIME_MACHINE MERGE DELTA INDEX;

-- Let's get the COMMIT_ID after this mood change

SELECT LAST_COMMIT_ID FROM M_TRANSACTIONS

WHERE CONNECTION_ID = CURRENT_CONNECTION;

-- The COMMIT_ID is 500360. Now let's time travel, my dears...

-- But first, we will get the list of COMMIT_IDs and their corresponding TIMESTAMPS

SELECT * FROM TRANSACTION_HISTORY WHERE COMMIT_ID >= 500343 ORDER BY COMMIT_ID;

-- And now, let's drive the TIME MACHINE!

-- You felt you would be sad forever...

SELECT * FROM MY_TIME_MACHINE AS OF UTCTIMESTAMP '2012-07-23 08:02:00';

-- and later on, you thought you could delete this memory forever...

-- But now you are happy again! And we can prove that you can change your mood without much ado: Q.E.D.

SELECT * FROM MY_TIME_MACHINE AS OF UTCTIMESTAMP '2012-07-23 08:06:00';

LEAVING THE SQL EDITOR AND RETURNING TO THE BLOG

That was it. And it worked wonderfully!

I am happy... And I hope this mood will stay with me for a loooong, loooong while...

Gemma Durany

Founder and Managing Director

WeeDooCare Business Solutions GmbH

and Co-Founder of Glooobal GmbH