Introducing SAP HANA Cloud SQL Console

Objectives
After completing this lesson, you will be able to:

After completing this lesson, you will be able to:

  • Write and execute SQL in the SQL Console

Working with the SQL Console

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

  • 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 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.

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 run-time 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 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.

Note
You might notice another button: Open SAP HANA SQL Console. This provides most of the functions of the Database Explorer embedded in the Business Application Studio interface, and might be sufficient for your needs. However, with this option you cannot create or remove database connections.

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. 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.

Access and work with the SQL Console

SQL Console Basics

Watch this video to learn about SQL Console Basics.

Save progress to your learning plan by logging in or creating an account