SAP HANA 2.0 SPS04 is now released with new and continuous innovations across various themes and the overview of what was newly introduced is provided from the blog post written by Joerg Latza.
I would like to provide deeper insight from a developer's perspective of what is newly available for SQL & SQLScript language features. One of the strategic focus is becoming more general-purpose supporting various use cases and applications by extending the coverage of SQL standards functionality and easier development for SAP HANA. With SPS04, there is a long list of new features to support it.
We will go through a series of blog posts to cover what is newly available in SQL and SQLScript. Let's start looking into the new features for the SQL language extension.
If we have to name the most important feature for SPS04, I believe transactional savepoint will be at the top of the list. This feature has been requested for a long time from various customers for more fine controlled transactional processing and now finally available.
Savepoint allows rollback of a transaction to a defined point allowing partial rollback. Several savepoints can be named at different positions and transaction can be rolled back to any specific named position allowing partial rollback.
The following SQL statements are supported for Transactional Savepoint.
Regular COMMIT or ROLLBACK will release any savepoints defined for the transaction. The savepoint identifier should be unique within a transaction and if a savepoint is defined with a same name, existing savepoint will be released and the newly defined savepoint will be kept.
The transactional savepoint is also supported as part of the JDBC client interface protocol. As part of the java.sql.Connection class, the following methods are supported.
Another item top on the list is statement level triggers. The trigger is fired after executing each of the INSERT, UPDATE, and DELETE trigger defined statement. Previously, this was only supported for row store table but now extended to also support column store.
Statement level triggers are very useful for processing bulk data where computation is processed for the entire statement for the whole processed data instead of individual computation for each record. The following are examples of how statement level tri
The data that has been inserted can be referenced through table <transition_table_new> during the trigger execution.
The original data can be referenced by table <transition_table_old > and updated data by <transition_table_new> during the trigger execution.
The original data can be referenced by table <transition_table_old > during the trigger execution.
System-versioned table, Application-time table, and Bi-temporary table is part of the SQL:2011 standard. With SPS03, system-versioned table was first introduced and now fully supported for the remaining.
captures the time, in which a record is valid in the business world.
Application-time period tables in SAP HANA:
example of application-time table
allow change tracking on database tables.
System-versioned Tables in SAP HANA:
combine system-versioned tables for tracking changes and application-time period table for valid data in the business world.
Allows correlation between table subqueries put side by side in the context of JOIN operation horizontally from left table to right table in subquery
As shown in the example above, the subquery need to traverse all TR against TL to check column b2 value is less that a2.
Define a sorting order based on language collation settings
Converts a tabular data to JSON document string format
The return data is JSON document string
Additional options for formatting is allowed
and the return JSON is formatted
additional options to also display null value and omit array wrap
and returned value is where null value is also return and square brackets are removed.
The following are additional features
There are other new and exciting features for SPS04 which can be found in our help documentation online. The structure have been changed recently and the details can be read from the blog post by Laura Nevin.
Next in the series, we will look into what's new for SQLScript.
I would like to provide deeper insight from a developer's perspective of what is newly available for SQL & SQLScript language features. One of the strategic focus is becoming more general-purpose supporting various use cases and applications by extending the coverage of SQL standards functionality and easier development for SAP HANA. With SPS04, there is a long list of new features to support it.
We will go through a series of blog posts to cover what is newly available in SQL and SQLScript. Let's start looking into the new features for the SQL language extension.
Transactional Savepoint
If we have to name the most important feature for SPS04, I believe transactional savepoint will be at the top of the list. This feature has been requested for a long time from various customers for more fine controlled transactional processing and now finally available.
Savepoint allows rollback of a transaction to a defined point allowing partial rollback. Several savepoints can be named at different positions and transaction can be rolled back to any specific named position allowing partial rollback.
The following SQL statements are supported for Transactional Savepoint.
SAVEPOINT <name> -- Savepoint with user defined name
ROLLBACK TO SAVEPOINT <name> -- Rollback to a Savepoint by name
RELEASE SAVEPOINT <name> -- Release a Savepoint by name
Regular COMMIT or ROLLBACK will release any savepoints defined for the transaction. The savepoint identifier should be unique within a transaction and if a savepoint is defined with a same name, existing savepoint will be released and the newly defined savepoint will be kept.
The transactional savepoint is also supported as part of the JDBC client interface protocol. As part of the java.sql.Connection class, the following methods are supported.
Savepoint setSavepoint( ) throws SQLException
Savepoint setSavepoint(String <name>) throws SQLException
void rollback(Savepoint <savepoint>) throws SQLException
void releaseSavepoint(Savepoint <savepoint>) throws SQLException
Statement Level Triggers
Another item top on the list is statement level triggers. The trigger is fired after executing each of the INSERT, UPDATE, and DELETE trigger defined statement. Previously, this was only supported for row store table but now extended to also support column store.
Statement level triggers are very useful for processing bulk data where computation is processed for the entire statement for the whole processed data instead of individual computation for each record. The following are examples of how statement level tri
After INSERT statement
The data that has been inserted can be referenced through table <transition_table_new> during the trigger execution.
CREATE TRIGGER <trigger_name>
AFTER INSERT ON <table_name>
REFERENCING NEW TABLE <transition_table_new>
FOR EACH STATEMENT
BEGIN
-- Triger Body
END;
After UPDATE statement
The original data can be referenced by table <transition_table_old > and updated data by <transition_table_new> during the trigger execution.
CREATE TRIGGER <trigger_name>
AFTER UPDATE ON <table_name>
REFERENCING NEW TABLE <transition_table_new>
OLD TABLE <transition_table_old>
FOR EACH STATEMENT
BEGIN
-- Triger Body
END;
After DELETE statement
The original data can be referenced by table <transition_table_old > during the trigger execution.
CREATE TRIGGER <trigger_name>
AFTER DELETE ON <table_name>
REFERENCING OLD TABLE <transition_table_old>
FOR EACH STATEMENT
BEGIN
-- Triger Body
END;
Application-time and Bi-temporal tables
System-versioned table, Application-time table, and Bi-temporary table is part of the SQL:2011 standard. With SPS03, system-versioned table was first introduced and now fully supported for the remaining.
Application-time table
captures the time, in which a record is valid in the business world.
- Validity periods are determined by the application.
- Application can update validity period of a record, e.g., to correct errors.
- Temporal information can arbitrarily reflect the past, present or future at any granularity.
Application-time period tables in SAP HANA:
- Based on regular column-store tables.
- Only one table.
- Validity period can be data type DATE, TIME, TIMESTAMP, …
Special DML operations for updating a records temporal information
UPDATE <table> FOR PORTION OF APPLICATION_TIME …Special SQL syntax, allowing access to time-dependent information
… FOR SYSTEM_TIME AS OF <timestamp>example of application-time table
CREATE COLUMN TABLE EMPLOYEE
(
empl_no int,
Empl_name nvarchar(200),
Empl_department int,
…
valid_from <some date type> not null,
valid_to <some date type> not null,
period for APPLICATION_TIME (valid_from, valid_to),
)
ALTER TABLE EMPLOYEE ADD PERIOD
FOR APPLICATION_TIME(<VALID_FROM_COLUMN_NAME>, <VALID_TO_COLUMN_NAME>)
SELECT * FROM EMPLOYEE FOR APPLICATION_TIME AS OF ‘<timestamp>’;
UPDATE EMPLOYEE FOR PORTION OF
APPLICATION_TIME FROM <point in time 1> TO <point in time 2>
SET <set clause list>
WHERE <search condition>System-versioned table
allow change tracking on database tables.
- A new record version is inserted for every change to preserve a records history.
- Validity periods are automatically maintained by the database whenever a record is changed.
System-versioned Tables in SAP HANA:
- Based on regular column-store tables.
- Two structurally equivalent tables: CURRENT and HISTORY.
- Tables are automatically combined in the SQL layer.
- Validity periods are based on data type TIMESTAMP.
- Inherent support for table partitioning and associated features.
Special SQL syntax, allowing access to historic data
SELECT … FOR SYSTEM_TIME AS OF <timestamp>
SELECT … FOR SYSTEM_TIME FROM <t1> TO <t2>
SELECT … FOR SYSTEM_TIME BETWEEN <t1> AND >t2>Bi-temporal table
combine system-versioned tables for tracking changes and application-time period table for valid data in the business world.
Lateral Join
Allows correlation between table subqueries put side by side in the context of JOIN operation horizontally from left table to right table in subquery
- Functioning as a for each loop of the left table joining to the right table.
- Useful when cross referencing column is used as computing the rows to be joined
- Cross Product, INNER and LEFT OUTER joins are only supported
As shown in the example above, the subquery need to traverse all TR against TL to check column b2 value is less that a2.
Collations
Define a sorting order based on language collation settings
- The collation supported can be verified by select * from COLLATIONS
- Currently, 144 collation type is supported
- Special collations such as German Phonebook, Chinese Stroke, Case Insensitive (CI), Accent Insensitive (AI), etc. are supported
- Accent Insensitive is superset of Case Insensitive
To get a full list of collations supported
SELECT * FROM <table_name>
ORDER BY COLLATE <collation_name>Sample collation that is supported to list just a few
SELECT...FOR JSON
Converts a tabular data to JSON document string format
CREATE TABLE LANG (id int, name nvarchar(20));
INSERT INTO LANG VALUES (1, NULL);
INSERT INTO LANG VALUES (2, 'en');
INSERT INTO LANG VALUES (3, 'de');
SELECT id, name from LANG for JSON;The return data is JSON document string
[{"ID":1},{"ID":2,"NAME":"en"},{"ID":3,"NAME":"de"}]Additional options for formatting is allowed
SELECT id, name from LANG for JSON ('format'='yes');and the return JSON is formatted
"[{
""ID"": 1
}, {
""ID"": 2,
""NAME"": ""en""
}, {
""ID"": 3,
""NAME"": ""de""
}]"additional options to also display null value and omit array wrap
SELECT id, name from LANG for JSON ('omitnull'='no', 'arraywrap'='no');and returned value is where null value is also return and square brackets are removed.
{"ID":1,"NAME":null},{"ID":2,"NAME":"en"},{"ID":3,"NAME":"de"}Misc Enhancements
The following are additional features
- RENAME SCHEMA
- CREATE OR REPLACE for views, triggers, and synonyms instead of drop then create.
- CTEs within subquery
Next in Series
For more information
There are other new and exciting features for SPS04 which can be found in our help documentation online. The structure have been changed recently and the details can be read from the blog post by Laura Nevin.
Next in the series, we will look into what's new for SQLScript.
- SAP Managed Tags:
2 Comments
