The described aims to enlighten the audience on the various components, frameworks and strategic mechanisms that make up the SAP HANA Database artifacts.
The DB artifacts are represented in an SQLscript which serves as an extension of ANSI SQL being the lingua franca of the database. The objective for SQLscript is to exploit specific features of SAP HANA, allowing much more pushdown of the data intensive processing to the SAP HANA Database layer.
Which otherwise in a traditional three-tier architecture the application layer would assume the responsibility of executing the business logic but this approach fails to leverage fast column operations, query optimization and massive parallel processing capabilities of SAP HANA with multicore CPUs and all the data residing in-memory (Brandeis 2021)
Ideally, SQLscript is the language used in SAP HANA to write the below mentioned using a combination of declarative and imperative orchestration logic:
Common artifact types:
Procedure:
A set of structured query language (SQL) statements with an assigned name stored within the SAP HANA DB. Commonly used to consolidate and centralize data intensive logic. A procedure can accept import parameters and return export parameters when invoked.
Function:
A set of structured query language (SQL) statements with an assigned name stored within the SAP HANA DB. A function may accept one or many parameters, but must return a single value. Functions are invoked as expressions using variables to hold the returned value. Depending on the returned interface in SAP HANA we distinguish between a Scalar and Table UDF.
Table
The SAP HANA database supports two types of tables being columnar and row-based data storage. SAP HANA is optimized for column storage. A database table is typically a two-dimensional data structure with cells organized in rows and columns usually with a Primary Key and sometimes an association.
Primary Key: a column or a set of columns in a table whose values uniquely identify a row in the table.
Associations: Define relationships between tables.
View
A virtual table based on the dynamic results returned in response to an SQL statement. A view may occasionally contain additional security checks such as STRUCTURED PRIVILEGE CHECK which is used to provide fine-grained control over which data a user can see within a view.
Library
A collection of functions and procedures within a defined library written in SQLScript. A user may create libraries and utilize them in other procedures or functions. Libraries are limited to SQLScript procedures or functions and are not available in other SQL statements.
Synonym
Used to re-point functions and stored procedures to various external objects (tables, views or sequences) without the need to rewrite the function nor the procedure. When creating a Synonym, the user needs to follow the bellow steps:
1.Identify /create a user (granting user) which as the necessary privileges for the external object and has the ability to grant these privileges (Grant option)
Commands to create a user with respective privileges:
Commands to create role with respective privileges:
2.Create a user- provided service that accesses the remote database and schema via the driver named com.sap.db.jdbc.Driver
3.Create a .hdbgrants file within a project which will then trigger the assignment of the required additional authorization to the object owner during the project deployment.
4.Create a synonym to point to external objects
5.Derived external object from the SFlight schema
SAP HANA Deployment Infrastructure (HDI) Container
An HDI container is a service layer residing above the SAP HANA database which harmonizes the deployment of SAP HANA database artifacts by providing a declarative approach for defining database objects and maintaining consistent deployment into the database. In principle all deployed database objects (Tables, Views, Procedures etc.) reside within the container.
Tooling
There are several tools provided by SAP which can be used for the development of the DB artifacts, monitoring and the administration of the entire SAP HANA DB. The choice of tool/s is often depending on the Hardware capabilities of the Server and the client, personal preferences and the task at hand.
Native Tools:
In addition to the above list SAP has recently released the first version of SAP HANA Database Explorer as a visual studio code extension which can be used for local development in VSCode.
Thank you all for taking a glimpse into the above Content. Please don’t for get to like, comment and share.
Abbreviations:
DB: Database
GUI: Graphical User interface
HDI: Hana Deployment Infrastructure
IDE: Integrated Development Environment
SQL: Structured Query Language
UDF: User Defined function
VSCode: Visual Studio Code
Reference:
Brandeis, J, 2021, SQLScript for SAP HANA, Rheinwerk Publishing, Quincy, MA 02171
The DB artifacts are represented in an SQLscript which serves as an extension of ANSI SQL being the lingua franca of the database. The objective for SQLscript is to exploit specific features of SAP HANA, allowing much more pushdown of the data intensive processing to the SAP HANA Database layer.
Which otherwise in a traditional three-tier architecture the application layer would assume the responsibility of executing the business logic but this approach fails to leverage fast column operations, query optimization and massive parallel processing capabilities of SAP HANA with multicore CPUs and all the data residing in-memory (Brandeis 2021)
Ideally, SQLscript is the language used in SAP HANA to write the below mentioned using a combination of declarative and imperative orchestration logic:
- Stored procedures
- User-defined functions
Source: SAP SE or an SAP affiliate company 2021
Figure 1: SQLScript Summary
Common artifact types:
Procedure:
A set of structured query language (SQL) statements with an assigned name stored within the SAP HANA DB. Commonly used to consolidate and centralize data intensive logic. A procedure can accept import parameters and return export parameters when invoked.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Create Procedure Statement
Figure 2: Procedure Syntax
Function:
A set of structured query language (SQL) statements with an assigned name stored within the SAP HANA DB. A function may accept one or many parameters, but must return a single value. Functions are invoked as expressions using variables to hold the returned value. Depending on the returned interface in SAP HANA we distinguish between a Scalar and Table UDF.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Create Function Statement
Figure 3: Function Syntax
Table
The SAP HANA database supports two types of tables being columnar and row-based data storage. SAP HANA is optimized for column storage. A database table is typically a two-dimensional data structure with cells organized in rows and columns usually with a Primary Key and sometimes an association.
Primary Key: a column or a set of columns in a table whose values uniquely identify a row in the table.
Associations: Define relationships between tables.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Create Table Statement
Figure 4: Columnar Table Syntax
View
A virtual table based on the dynamic results returned in response to an SQL statement. A view may occasionally contain additional security checks such as STRUCTURED PRIVILEGE CHECK which is used to provide fine-grained control over which data a user can see within a view.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Structured Privilege
Figure 5: View Syntax
Library
A collection of functions and procedures within a defined library written in SQLScript. A user may create libraries and utilize them in other procedures or functions. Libraries are limited to SQLScript procedures or functions and are not available in other SQL statements.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Create Library Statement
Figure 6: Library Syntax
Synonym
Used to re-point functions and stored procedures to various external objects (tables, views or sequences) without the need to rewrite the function nor the procedure. When creating a Synonym, the user needs to follow the bellow steps:
1.Identify /create a user (granting user) which as the necessary privileges for the external object and has the ability to grant these privileges (Grant option)
Commands to create a user with respective privileges:
Commands to create role with respective privileges:
2.Create a user- provided service that accesses the remote database and schema via the driver named com.sap.db.jdbc.Driver
Figure 7: User-Provided Service instance
3.Create a .hdbgrants file within a project which will then trigger the assignment of the required additional authorization to the object owner during the project deployment.
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Syntax options in the hdbgrants file
Figure 7.1: .hdbgrants Syntax
4.Create a synonym to point to external objects
Source: Thomas Jung 2021, https://www.youtube.com/playlist?list=PL6RpkC85SLQAPHYG1x6IEu_exE5pa0UK_
SAP HANA SQL Reference Guide for SAP HANA: Create Synonym
Figure 7.2: Synonym GUI editor
5.Derived external object from the SFlight schema
Figure 7.3: Synonym artifact
SAP HANA Deployment Infrastructure (HDI) Container
An HDI container is a service layer residing above the SAP HANA database which harmonizes the deployment of SAP HANA database artifacts by providing a declarative approach for defining database objects and maintaining consistent deployment into the database. In principle all deployed database objects (Tables, Views, Procedures etc.) reside within the container.
Figure 8: TBS (dev) HDI Container
Tooling
There are several tools provided by SAP which can be used for the development of the DB artifacts, monitoring and the administration of the entire SAP HANA DB. The choice of tool/s is often depending on the Hardware capabilities of the Server and the client, personal preferences and the task at hand.
Native Tools:
- SAP HANA Studio
- SAP HANA hdbsql
- SAP Web IDE
- SAP HANA Cockpit
- XS Advanced Cockpit
In addition to the above list SAP has recently released the first version of SAP HANA Database Explorer as a visual studio code extension which can be used for local development in VSCode.
Source: VSCode Marketplace
Documentation Link: https://marketplace.visualstudio.com/items?itemName=SAPSE.hana-database-explorer
For more detailed usage info and getting started tips, Please have a look at the below blog from Thomas Jung, https://blogs.sap.com/2022/01/26/sap-hana-database-explorer-vscode-extension-getting-started-tips/co...
Figure 9: SAP HANA DB Explorer Integration for VSCode extension
Thank you all for taking a glimpse into the above Content. Please don’t for get to like, comment and share.
Abbreviations:
DB: Database
GUI: Graphical User interface
HDI: Hana Deployment Infrastructure
IDE: Integrated Development Environment
SQL: Structured Query Language
UDF: User Defined function
VSCode: Visual Studio Code
Reference:
Brandeis, J, 2021, SQLScript for SAP HANA, Rheinwerk Publishing, Quincy, MA 02171
- SAP Managed Tags:
