Dynamic Tiering in SAP HANA cloud platform

[ganadha203]

Introduction:

This blog post dives into how to implement the dynamic range partitioning and dynamic aging on HANA cloud tables which would help to keep the frequently used data in memory and offload the less frequently used data into NSE (Native Storage Extension). This would help the customers to save the cost and use the HANA system for operational reporting.

What is Dynamic Range Partitioning: 

Dynamic Range Partitioning is available to support the automatic maintenance of the 'others' partition using range partitions (single/multilevel).

• Using the dynamic range feature the OTHERS partition is monitored by a background job and will be automatically split into an additional range partitions when it reaches a predefined size threshold/interval.
• Row count Threshold Setting can done using create/alter statements or DYNAMIC_RANGE_THRESHOLD field of the TABLE_PLACEMENT or define a threshold value as a system configuration parameter.
• INTERVAL option can be used to either define a time value using data types such as DATE, TIMESTAMP, SECONDDATE or Define a numeric interval using data types such as INT, BIGINT, NVARCHAR.
• Dynamic range partition can be enabled through table creation or ALTER statement, this can be used with either a THRESHOLD value to define a maximum row count number or an INTERVAL value which can be used to define a maximum time or other numeric 'distance' value.
• If threshold does not exceeded, nothing happens. If threshold exceeded, the process closes the actual OTHERS partition (fills out the Range details automatically), and opens the new OTHERS partition.
• An "additional" feature, which deletes the partitions with zero records - empty partitions.

In the [partitioning] section of indexserver.ini file the following configuration parameters are available for dynamic partitioning:

• dynamic_range_default_threshold - enter the value you require, the default value is 10,000,000 rows. If no other value has been specified then this parameter value is used.
• dynamic_range_check_time_interval_sec - the background job runs at a predefined interval defined as a number of seconds. By default this is 900 but this can be changed here if required. You can set a value of zero to deactivate the background job (a value of minus 1 has the same effect).

What is Dynamic Aging: 

• Dynamic Aging would automatically manage at which point in time older partitions can be moved to the 'warm' data store.
• The data in a new OTHERS partition is 'hot' data, that is, stored in memory with the load-unit attribute implicitly set to COLUMN LOADABLE. As an extension of the Dynamic Range Partitioning feature Dynamic Aging makes it possible to automatically manage when older partitions can be moved to the 'warm' data store (Native Storage Extension) with the load-unit attribute for the partition set to PAGE LOADABLE. Warm data is then stored on disk and only loaded to memory when required.
• Dynamic Aging can be applied to a table by setting a value for the additional DISTANCE property for the OTHERS partition with either CREATE TABLE or ALTER TABLE. This can be either a THRESHOLD value or INTERVAL value.

Important points to note:

• The process handles only the OTHERS partition of the table.
• The process does not reorganize the actual partitioning of the table.
• The process only closes the actual OTHERS partition (which exceeded the threshold value) and creates a new one.
• In HANA Cloud, when using heterogeneous partitioning, PRIMARY KEY CHECK is enabled by default, it need to switch it off in cloud using the "NO PRIMARY KEY CHECK" clause for creating dynamic partitions based on Date/timestamp fields.

Limitations:

• Dynamic partitioning/ aging can be implemented with Range/Range-Range Partitions only.
• PRIMARY KEY CHECK is not supported on multi-store tables or in combination with the time selection feature.

Now lets create the dynamic partitions and dynamic aging on HANA cloud new tables and existing tables.

Scenario 1: 

• Enable Dynamic Partitioning and Aging for New tables
• Primary key check should be disabled and Date column should not be null for range partitioning and aging based on date/timestamp fields.
• Execute statement to create table with dynamic range partitioning and aging, now table is created with single range partition and partition others
• Load the data into the table.
• Waiting until the check process triggered.
• After the automated process executed the partitions changed as below. and older than 6 months partitions moved to NSE.
  

  Scenario 2: 

  • Enable Dynamic partitioning and aging for existing tables
  • Example:BPF table, primary key (ID) check should be disabled and date column (BUDAT) should be NOT NULL.
  • Run the ALTER query to create the dynamic partitions and move the partitions to NSE.
  • 
  • 
  • Table partitioned based on date field and by switching off the primary key check.
  • Waiting until the check process triggered.
  • After the automated process executed the partitions changed as below. and older than 6 months partitions moved to NSE.
  
• 

  We can follow the same process as scenario 1 & 2 to perform the table dynamic partitioning and dynamic aging based on THRESHOLD value for the INTEGER data types.

  Hope this helps to understand the dynamic partitioning and dynamic aging and implement the same in SAP HANA cloud.

Reference SAP help links:

https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/...

https://help.sap.com/docs/hana-cloud-database/sap-hana-cloud-sap-hana-database-administration-guide/...