Importance of SQL in SAP HANA Modeling
SQL plays an important role in SAP HANA Cloud modeling and can be implemented in different modeling objects, including the following:
Expressions in calculation views
Procedures
Functions
SQL-based analytic privileges
SQL is used to implement custom logic in calculation views where standard features of the graphical calculation view are not sufficient. SQL provides a very large number of functions that can provide users with access to complex data processing tasks.
SAP HANA Cloud modelers should acquire skills in the SQL language so that they can be fully effective in projects. In addition to writing custom logic to be embedded in calculation views, modelers should understand the behavior of calculation views at runtime when SQL is generated from the calculation view, and to use the debugging tools that display the generated SQL.
Caution
SQL should not be considered as an alternative approach to graphical modeling. For example, you should NOT create an SQL-based object (function or procedure) to achieve a result that a graphical Calculation View can achieve, even if you have advanced SQL knowledge. Calculation views provide more optimization possibilities than SQL as they generate their own SQL code at runtime based on the variety of query conditions and implement advanced pruning to generate the leanest code to provide the best performance.Launching the SQL Console
The SQL Console, which is available in the Database Explorer, is used to write and execute SQL statements.
Although the SQL Console is ideal for executing one-time instructions, such as creating a temporary table or executing a test query, it is not recommended for generating persistent modeling objects such as functions, procedure or tables that will become part of an application. For this, we recommend you insert your SQL code in a source file that corresponds to the type of object you wish to create, for example:
To create a table, always use source file type .hdbtable.
To create a function, always use source file type .hdbfunction.
When you deploy the source file, the corresponding runtime database object is generated.
Nevertheless, the SQL Console is an important tool for testing and executing one-time statements, so let's take a look at it.
The steps to use the SQL Console are as follows:
- Open SQL Console
- Write SQL
- Execute
- View results
The SQL Console is a feature of the Database Explorer, which is launched from SAP Business Application Studio. The Database Explorer a separate tool from SAP Business Application Studio. The Database Explorer opens in a new browser tab.
Each time you select the SQL Console, a new tab appears inside the Database Explorer where you write your code. You can open as many SQL Console tabs as you wish.
Note
You might notice another button: Open SAP HANA SQL Console. This provides most of the functions of the standalone Database Explorer, but it is conveniently embedded in SAP Business Application Studio and does not open the separate tool. A new SQL Console tab opens in SAP Business Application Studio, in this case. This version does not contain the full feature set of the standalone Database Explorer tool, but might be sufficient for your needs.The Database Explorer can contain many database connections that point to different parts of the database. When you launch an SQL Console you need to be aware of which database connection you are using for that console because the executed SQL will run against that database connection. For example, if the SQL statement you execute refers to a table that cannot be found, it might be that the table does not exist under the database connection you are using. If you change the database connection, you might then find the table.
To launch the SQL Console, choose the Open Database in Database Explorer button next to the database connection you would like to use. This opens up the Database Explorer in a new browser tab.
You can clearly see which database connection you are currently using because this is always displayed at the top of the screen.
Regardless of which connection you choose, you can easily swap the connection by using the button in the toolbar Connect this SQL console to a different database. This is useful when you want to test your SQL against a alternative connection that points to a different set of tables without having to re-write the SQL.
HDI or Classic Database Connections
There are two main types of database connection:
HDI Container
SAP HANA database (also known as catalog)
It is important that you understand which type you are writing statements against in the SQL Console because this affects how you write the SQL code.
A HDI container is generated automatically the first time you deploy your database module in your development project. The container is basically a hidden schema. Later in the course, HDI containers are covered in more detail.
The key thing to remember is that when executing statements against an HDI Container, you do not need to specify the schema. The schema is already known to the container you are running the statement against.
However, if you are executing statements against a connection that is an SAP HANA Database catalog type, then you must specify the schema in your statements.