Product Information
Store More with SPS04
About the authors
Abby Barry is a Platform and Technologies Solution Engineer with experience in database, data management, and analytics solutions. She has been at SAP for 3 years in this role.
Doug Hoover is a Platform and Technologies Principal Solution Engineer with decades of experience in database, real-time data movement, enterprise data warehouse, advanced analytics, and big data.
This is our first post in our new blog series about the new innovations in the most recent SAP HANA release. Interested in a topic? Let us know!
Product: SAP HANA 2.0 SPS04
Feature: Native Storage Extension (NSE)
With SAP HANA’s latest service pack update (SPS04) comes new innovations. In this blog post, I will be explaining Native Storage Extension (NSE), a new native option for SAP HANA customers for warm data tiering. With NSE, you will be able to reduce TCO by gaining storage capacity by storing data in pageable columnar tables. An sample of the SQL code used to administer data storage and a technical deep-dive video are also included below.
Reduce TCO by gaining storage capacity
Reduce your TCO by significantly expanding your data capacity without requiring an additional SAP HANA License.
Think of your mission critical sales data from the past few days. Process managers need this data available in real-time to make critical operational decisions. Once the business process has been completed, you still need access but not with the same response time required by operational business needs. Now with NSE, this older data can be stored in a lower cost tier while still managed as part of the SAP HANA database.
Store data in pageable columnar tables
SAP HANA stores data at rest in columnar tables in-memory. NSE extends this in-memory storage to support pageable columnar tables while maintaining efficient and fast accesses. This mechanism provides a significantly lower Total Cost of Ownership by expanding the capacity of the SAP HANA database. Although slower than SAP HANA columnar in-memory tables, NSE is still much faster and extremely more efficient than standard row-based databases. Therefore, a huge benefit is that NSE allows SAP HANA to store huge amounts of data very affordably, supporting both vertical and horizontal partitioning of columnar tables across memory and disk.
Administer data storage with SQL
/*To create a page loadable table*/
CREATE COLUMN TABLE T (C1 INT, C2 VARCHAR(10)) PAGE LOADABLE;
/*To create a page loadable partition*/
CREATE COLUMN TABLE TP (C1 INT) PARTITION BY RANGE (C1)
((PARTITION 0 <= VALUES < 1000 PAGE LOADABLE,
PARTITION 1000 <= VALUES < 2000 COLUMN LOADABLE,
PARTITION OTHERS COLUMN LOADABLE));
/*To create a page loadable column*/
CREATE COLUMN TABLE TC (C1 INT, C2 VARCHAR(10) PAGE LOADABLE);
Technical overview
Check out this video to see a technical overview of NSE
To start using SAP HANA today
We are excited to roll out this native SAP HANA warm data tiering solution that boasts full SAP HANA functionality at a lower TCO for customers. Drop a question below if you want to learn more. Want to start using SAP HANA today? Check out SAP HANA Service homepage on the SAP Cloud Platform site and contact your SAP Account Executive!
Would you like to discuss storing data using NSE with an expert? Join Abby and Doug this Friday afternoon for a 1 hour SAP HANA Expert Question and Answer session. Use the link below to register:
https://sap-se.zoom.us/webinar/register/0f72653619acc763dc2040ba88984b7b
After registering, you will receive a confirmation email containing information about joining the webinar.
Hi Feng Liu
Here are some comparisons...
Notice in-memory speed of 552ms vs NSE speed of 2503ms but second run was only 706ms, so depending on code and how often data is reused speed can vary from 1.2 to 5 times slower...
In this example all of the tables where completely loaded into default HANA in-memory tables:
SELECT R_NAME, SUM(O_TOTALPRICE)
FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O
WHERE C_CUSTKEY = O_CUSTKEY
AND N_NATIONKEY = C_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
GROUP BY R_NAME;
-- Since none of the page loadable columns are used...
--Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O WHERE ...'
--executed in 552 ms.
In this example all of the tables where completely loaded into HANA NSE (PAGE LOADABLE) on-disk tables:
SELECT R_NAME, SUM(O_TOTALPRICE)
FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O
WHERE C_CUSTKEY = O_CUSTKEY
AND N_NATIONKEY = C_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
GROUP BY R_NAME;
--Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'
--executed in 2503 ms.
--Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'
--executed in 706 ms.
Now if you partition a table across both in-memory and on-disk then and you only hit the in-memory data with your query then you will get the in-memory speed 512ms
SELECT R_NAME, SUM(O_TOTALPRICE)
FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O
WHERE C_CUSTKEY = O_CUSTKEY
AND N_NATIONKEY = C_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
AND O_ORDERDATE >= '20080101'
GROUP BY R_NAME;
-- Since date newer than 2008 then only in-memory COLUMN LOADABLE Columns are used
--Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_HP O WHERE ...'
--executed in 512 ms.
CREATE COLUMN TABLE NSE.ORDERS_HP ("O_ORDERKEY" BIGINT NOT NULL ,
"O_CUSTKEY" BIGINT NOT NULL ,
"O_ORDERSTATUS" CHAR(1) NOT NULL ,
"O_TOTALPRICE" DECIMAL(12,2) NOT NULL ,
"O_ORDERDATE" DATE NOT NULL ,
"O_ORDERPRIORITY" VARCHAR(15) NOT NULL ,
"O_CLERK" VARCHAR(15) NOT NULL ,
"O_SHIPPRIORITY" INTEGER NOT NULL ,
"O_COMMENT" VARCHAR(79) NOT NULL ,
PRIMARY KEY ("O_ORDERKEY",O_ORDERDATE))
PARTITION BY RANGE (O_ORDERDATE) ((
PARTITION '20090101' <= VALUES < '20100101' COLUMN LOADABLE,
PARTITION '20080101' <= VALUES < '20090101' COLUMN LOADABLE,
PARTITION '20070101' <= VALUES < '20080101' PAGE LOADABLE,
PARTITION '20060101' <= VALUES < '20070101' PAGE LOADABLE,
PARTITION '20050101' <= VALUES < '20060101' PAGE LOADABLE,
PARTITION '20040101' <= VALUES < '20050101' PAGE LOADABLE,
PARTITION '20030101' <= VALUES < '20040101' PAGE LOADABLE,
PARTITION '20020101' <= VALUES < '20030101' PAGE LOADABLE)) ;
SAME result with vertical partitioning 512ms...
SELECT R_NAME, SUM(O_TOTALPRICE)
FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O
WHERE C_CUSTKEY = O_CUSTKEY
AND N_NATIONKEY = C_NATIONKEY
AND N_REGIONKEY = R_REGIONKEY
GROUP BY R_NAME;
-- Since none of the page loadable columns are used...
--Statement 'SELECT R_NAME, SUM(O_TOTALPRICE) FROM CUSTOMER C, NATION N, REGION R, ORDERS_VP O WHERE ...'
--executed in 552 ms.
create column table NSE.ORDERS_VP (
O_ORDERKEY BIGINT not null,
O_CUSTKEY BIGINT not null,
O_ORDERSTATUS CHAR not null,
O_TOTALPRICE DECIMAL(12,2) not null,
O_ORDERDATE DATE not null,
O_ORDERPRIORITY VARCHAR(15) not null,
O_CLERK VARCHAR(15) not null PAGE LOADABLE,
O_SHIPPRIORITY INTEGER not null PAGE LOADABLE,
O_COMMENT VARCHAR(79) not null PAGE LOADABLE,
primary key (O_ORDERKEY)
);
Before using NSE and deactivate the "HANA Power", rather think about the usage of SAP IQ as large Datastore together with SAP HANA, as SAP IQ always uses columnar compression (on disk).
See - SAP (Sybase) IQ – the hidden treasure …