Selectively Move Data Between In-Memory and Extended Tables
- Migrating data between in-memory and Dynamic Tiering table instances.
- Setting “Auto Commit” and “Isolation Level” property values.
- Step 1
LINEITEM_DTtable contains identical information to the
LINEITEM_CStable. In order to easily be able to notice that data has been selectively moved from one table to another, we will first remove all data from the
LINEITEM_DTtable. To do so, copy and paste the script below into the SQL console. Then press the Execute button to delete the data in the table.
In SAP HANA Administration Console perspective, go to the System view on the left side. Select your system and open a SQL console.sqlCopy
TRUNCATE TABLE "TPCH"."LINEITEM_DT"; SELECT * FROM "TPCH"."LINEITEM_DT";
The result should be empty to indicate the data has been deleted.
- Step 2
By default, SQL Console connections from HANA Studio to your HANA system are created with the Auto Commit option turned “On”. The Auto Commit option automatically issues a commit after each statement that is executed against the server. You need to change Auto Commit to “Off” in order to execute the migration script, which executes two statements, as a single atomic transaction.
Open a new SQL console connected to your system, right click on your system and select Open SQL Console.
To check and change the “Auto Commit” setting, click to open the Properties tab in the bottom right panel.
Note: If the Properties tab is blank, double check that you are connected to a HANA server and then click inside the Properties tab to set the focus and press the F5 function key to refresh the view.
Click on the value cell for Auto Commit and change it to Off.
- Step 3
Database isolation levels determine how the database server handles concurrency when multiple users may be reading and writing to the database at the same time. The default isolation level is “READ COMMITTED” which ensures that any given statement only sees committed records. The “READ COMMITTED” isolation level does not ensure consistency between statements even within a single transaction.
Since the process of migrating data between in-memory and extended tables requires both an insert and a delete statement, you need to increase the isolation level to either “REPEATABLE READ” or “SERIALIZABLE” both of which ensure consistency across multiple statements within a single transaction.
Note: Since HANA uses snapshot isolation, “REPEATABLE READ” and “SERIALIZABLE” isolation levels are identical. For more information on Isolation Levels in SAP HANA, see the SET TRANSACTION Statement (Transaction Management).
To change the isolation level, click on the Isolation Level field in the Properties tab and choose REPEATABLE READ.
Left click on the SQL console, and you should be prompted by a “Rollback Connection” pop up. Click OK to set the isolation level.
Which value do you increase the isolation level to?
- Step 4
Copy and paste the script below into the SQL console. Press the Execute button to execute the migration. Ensure the entire script executed correctly. The script below copies records older than 2015-1-1 from the in-memory
LINEITEM_CStable to the extended table
LINEITEM_DTin Dynamic Tiering. It then deletes the moved records from the
LINEITEM_CStable to free up storage space in-memory now that the data has been copied to Dynamic Tiering. The “
WHERE” statement in the script is used to selectively choose data. Specifically, in the script above, the “
WHERE” statement is used to select data from the
LINEITEM_CStable whose “
L_SHIPDATE” is before January 1st, 2015.sqlCopy
INSERT INTO "TPCH"."LINEITEM_DT" (SELECT * FROM "TPCH"."LINEITEM_CS" WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1'); DELETE FROM "TPCH"."LINEITEM_CS" WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1'; COMMIT;
Because you have turned off Auto Commit, you now need to explicitly commit the transaction yourself using the “COMMIT” statement.
LINEITEM_CShas records with ship dates ranging from Jan 2, 2012 (2012-1-2) to Dec 1, 2018 (2018-12-1). For the purpose of this lesson, you will be migrating records older than Jan 1, 2015 (2015-1-1) from the
ORDERS_CStable to the
Verify that the data has been inserted into the
LINEITEM_DTtable either by executing the query below or by right clicking on the table in the catalogue and choosing Open Data Preview.sqlCopy
SELECT * FROM "TPCH"."LINEITEM_DT";
Verify that the data has been deleted from the
LINEITEM_CStable by executing the query below and confirming that no records are returned.sqlCopy
SELECT * FROM "TPCH"."LINEITEM_CS" WHERE "TPCH"."LINEITEM_CS"."L_SHIPDATE" < '2015-1-1';