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:
Expressions in calculation views
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 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. As well are writing custom logic to embed in calculation views, modelers should understand the behavior of calculation views at run-time when SQL is generated from the calculation view, and to use the debugging tools which display the generated SQL.
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 run-time 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 SQL Console is launched from the Database Explorer, which is launched from SAP Business Application Studio. Each time you launch the SQL Console a new tab appears where you write your code. You can open as many tabs as you wish.
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 because the executed SQL will run against that connection. So the design-time errors, for example, a table you specify can't be found, will be based on the connection you are using that might not include the table you are querying.
To launch the SQL Console, click the button Open Database in Database Explorer next to the database connection you would like to use. This opens up the tool 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:
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. HDI containers are covered in more detail, later.
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 Databasecatalog type, then you must specify the schema in your statements.