SAP HANA SQL – An Ultimate Cheat Sheet of SQL Statements in SAP HANA
With this introductory SAP HANA SQL tutorial, we will start with learning the SQL implementation on SAP HANA database. SQL is an integral part of working with SAP HANA. So, let us quickly begin with learning some basics of SQL in SAP HANA.
Introduction to SAP HANA SQL
SQL stands for Structured Query Language which interacts with relational databases. It is a standardized language that is used to store, manipulate and retrieve data from the different relational databases such as Oracle, MySQL, etc. SAP HANA users can create SQL scripts and apply complex business logic to the database manipulating data as per their requirement. For using SQL in SAP HANA, the basic knowledge of SQL includes SQL data types, functions, expressions, operators, sequences, triggers, synonyms, script, explain plans, etc.
You can add comments in a SQL script to make notes and improve the understanding of the script. The comments can be added in two ways; either by adding the comment after double hyphens “–” or between “/*” and “*/”. Although, the SQL parser ignores the comments.
Types of SQL Statements in SAP HANA
SAP HANA supports various SQL statements such as:
1. Data Manipulation Language Statements
We use the data manipulation language (DML) within schema objects to manipulate and manage data in them. Generally used DML keywords are SELECT (to retrieve data from database), INSERT (to add data into a data table) and UPDATE (to update data that already exists in a table).
2. Data Definition Language Statements
We use the DDL statements and keywords to define the structure of data in databases and schemas. Commonly used DDL keywords are CREATE (creates objects in the database), ALTER (alters the database structure), DROP (deletes or drops objects from the database).
3. Data Control Language Statements
We use the DCL statements to control user access and privileges to the database. Commonly used DCL keywords are GRANT (to give access privilege to database to the users) and REVOKE (to take back the access privileges given using GRANT command).
4. Backup and Recovery Statements
Backup and recovery statements enable users to perform backup and recovery operations on the HANA database.
5. Access Control Statements
Access control statements allow database administrators to create, alter and drop access to the HANA database.
6. Data Import Export Statements
Data import export statements enable import and export operations on data from the HANA database.
7. JSON Document Store Statements
These statements enable users to use the SAP HANA database in the form of a JSON document store.
8. Encryption Statements
Encryption statements allow database administrators to work with encrypted data in the HANA database.
9. Tenant Database Management Statements
Tenant database management statements manage the SAP HANA tenant databases.
10. Procedural Statements
The procedural statements allow users to manage both the system and user-defined procedures for data in HANA database.
11. Transaction Management Statements
These statements manage transactions in the SAP HANA database.
12. Session Management Statements
We use these statements to manage database sessions.
13. System Management Statements
These statements enable users to manage system configuration settings in the HANA database.
14. Workload Management Statements
These statements manage both the workload classes and mappings.
Key Tasks of SQL in SAP HANA
In SAP HANA, we use SQL statements to perform five basic tasks as mentioned below:
- Data manipulation
- System management
- Schema definition and manipulation
- Session management
- Transaction management
Significance of SQL in SAP HANA
SQL and SQL scripting holds great importance in SAP HANA because a database only understands a standardized language like SQL. Whenever an SAP HANA user wishes to work with data stored in the HANA database, the interaction happens through SQL.
Using SQL keywords, SQL expressions, and SQL functions, one can efficiently access data from the databases, transform it, and perform other management tasks on the system. In addition to this, while making information models, SQL plays an important role. Especially in creating SAP HANA calculation views, SQL scripting is used to create procedures and apply complex logic and calculations to the data residing in the database.
This concludes our introductory discussion on SQL in SAP HANA. In the upcoming tutorials, we will cover different elements of SQL used in SAP HANA.
Did you like our explanation? We will wait for your feedback and suggestions. See you in our next tutorial!
Check out different job profiles in SAP HANA and choose the most suitable one.