Modeling in SAP HANA Cloud

Working with SQLScript

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

After completing this lesson, you will be able to:

  • Develop Skills using SQLScript

Why Do We Need SQLScript?

SQLScript is a database language developed by SAP that is based on standard SQL, but adds additional capabilities to exploit the advanced features of SAP HANA Cloud. The figure, SQLScript: Concept, lists some of the key features of SQLScript.

Note

The version of standard SQL that SQLScript is based on is the popular ANSI-92 version which is supported by nearly all databases.

Additional features of SQLScript include working with SAP HANA Cloud column tables, passing parameters between calculation views and SQLScript, working with spatial data types, and predictive analysis libraries.

SQLScript Extends SQL

In traditional client-server approaches, the business logic is defined and executed in the application server using application programming languages such as ABAP or C++. With SAP HANA Cloud, much of this logic can be defined using SQLScript which allows the developer to define and execute all data processing logic in the database in order to achieve improved performance.

SQLScript allows you to use variables to break a large complex SQL statement into smaller, simpler statements. This makes the code much easier to understand. It also helps with SQL HANA Cloud’s performance because many of these smaller statements can be run in parallel.

Let’s have a look at an example.

Code snippet
books_per_publisher = SELECT publisher, COUNT (*) AS num_books FROM BOOKS GROUP BY publisher;

publisher_with_most_books = SELECT * FROM :books_per_publisher WHERE num_books >= (SELECT MAX (num_books) FROM :books_per_publisher);
Copy code

You normally write this example as a single SQL statement using a temporary table, or by repeating a sub-query multiple times. In this example, however, you break this into two smaller SQL statements by using table variable. Notice how the table variable does not even need to be declared in advance; its definition is taken from the output result.

The first statement calculates the number of books each publisher has and stores the entire result set into the table variable called books_per_publisher.

This variable, containing the entire result set, is used twice in the second statement.

Notice that the table variable is prefixed in SQLScript with a colon (‘:’) to indicate that this is used as an input variable. All output variables just have the name, and all input variables have a colon prefix.

The second statement uses :books_per_publisher as inputs, and uses a nested SELECT.

The SQLScript compiler and optimizer determine how to best execute these statements, whether by using a common sub-query expression with database hints or by combining this into a single complex query. The code becomes easier to write and understand, more maintainable, and developer productivity increases.

By breaking the SQLScript into smaller statements and filling table variables, you also mirror the way in which you have learned to build your calculation views. Just like when you start building calculation views in layers, starting from the bottom up, you do the same with your SQLScript code. Keep in mind that the precise sequence of your SQLScript steps is not necessarily the runtime order, because the optimizer will always decide on the best execution plan. However, the optimizer will never alter your desired outcome. This is also true for calculation views.

SQLScript Data Types

SQLScript also adds extra data types to help address missing features from standard SQL; for example, spatial data types and text data types. These data types are listed in the figure, ANSI-92 and SQLScript Data Types.

The Use of Variables

The use of variables in SQL statements is not specified by the ANSI-92 SQL. In SAP HANA Cloud, SQLScript implements the concept of variable as an extension to ANSI-92 SQL.

Variables can be declared, assigned, and reused within a script.

Using Variables in SQLScript

  • Declare a variable

    DECLARE <variable_name> <type> [NOT NULL] [= <value>]

    Example 1: DECLARE a int;

    Example 2: DECLARE b int = 0; (the value is assigned when creating the variable)

  • Assign a variable (define the value of a variable)

    <variable_name> = <value> or <expression>

    Example 1: a = 3;

    Example 2: b = select count(*) from baseTable;

  • Use a variable in an expression

    :<variable_name> (with a colon) returns the current value of the variable

    Example: b = :a + 5; (assigns the value of a + 5 to b)

Processing Input Parameters Defined in Calculation Views with SQLScript

When you use a select statement in SQLScript to query a calculation view that has input parameters, you use the PLACEHOLDER keyword with the name of the input parameter wrapped in $$ symbols, and the value you want to pass to the input parameter. For example:

Using Input Parameters in SQLScript

Use the PLACEHOLDER keyword to pass input parameters to the calculation view in a select statement:

Code snippet
SELECT "PRODUCT", sum("TAXED_AMOUNT")
      FROM "_SYS_BIC"."MyPackage/SalesResults" 
       ('PLACEHOLDER' = ('$$Tax Amount$$', '17.5')) 
GROUP BY "PRODUCT"  
Copy code

Within SQLScript you refer to the input parameter using a colon:;

Code snippet
Sales with tax = Sales Revenue * :Tax_Amount 
Copy code

It is also possible to pass output parameters generated from an SQLScript, into a calculation view using the parameter mapping feature.

Imperative Logic

In general, SQL code is written in a declarative way. This means you state the intent of the action and do not dictate how the data processing steps should be carried out in a particular order. We do this so that you allow the SQL optimizer to figure out the best execution plan based on many dynamic factors at run-time.

However, sometimes you need to control the flow logic of the SQL so that steps are performed in a particular order. For example, when you need to perform a step only if a certain condition is met, or you need to loop a number of times. In these cases, you will use imperative logic.

This is (mostly) not available in ANSI SQL, and therefore SAP HANA provides declarative logic language as part of SQLScript to provide flow control.

A lack of imperative logic language is what often causes the developer to move the processing of data to the application layer, When an application runs on HANA, you really should push as much data processing to the database. SAP HANA SQLScript imperative logic enables this so less data handling needs to be done in the application layers and more can be done in the database.

Hint
Remember that imperative logic cannot have the same performance as declarative logic. This is because the SQL optimizer is not free to decide on the sequence of steps and has to take care to follow your logic, which might destroy optimizations. If you require the best performance, try to avoid imperative logic.

The figure, The IF Statement, shows an example of this statement, which consists of a Boolean expression bool-expr1. If this expression evaluates to true, then the statement then-stmts1 in the mandatory THEN block is executed. The IF statement ends with END IF. The remaining parts are optional.

If the Boolean expression bool-expr1 does not evaluate to true, then the ELSE branch is evaluated. In most cases this branch starts with ELSE. The statement else-stmts3 is executed without further checks. After an else branch, no further ELSE branch or ELSEIF branch is allowed.

Alternatively, when ELSEIF is used instead of ELSE, another Boolean expression bool-expr2 is evaluated. If it evaluates to true, the statement then-stmts2 is executed. You can add an arbitrary number of ELSEIF clauses in this way.

This statement can be used to simulate the switch-case statement known from many programming languages.

WHILE and FOR Loops

Dynamic SQL

Sometimes there are elements of the SQL code that cannot be known until run-time. For example: the name of a table or a column may not be known until a session variable is filled, or a look-up is performed on current data. in that case, dynamic SQL can be used.

The EXEC Statement

The EXEC statement helps to create dynamic SQL statements. For example, let’s say that you want to find the travel arrangements for users. A web form is requesting the user’s passport number. You insert this passport number into a partially prepared SQL statement, and then execute this SQL statement using the EXEC statement to get the travel plans.

EXEC ‘<SQL statement goes here>’

Dynamic SQL statements are used to construct SQL statements at runtime in a procedure.

The EXEC statement executes the SQL statement passed in a string argument. This statement allows for dynamically constructing an SQL statement at execution time of a procedure.

For example, a SELECT statement could reference a table whose name is not known until runtime of the procedure. Perhaps the table name is retrieved from a lookup based on a condition.

Therefore, on the positive side, dynamic SQL allows the use of variables where they might not be supported in SQLScript, or more flexibility in creating SQL statements.

On the negative side, dynamic SQL comes with the following additional costs at run-time:

  • Opportunities for optimizations are limited.
  • The statement is potentially recompiled every time the statement is executed.
  • It is not possible to use SQLScript variables in the SQL statement (but when constructing the SQL statement string).
  • It is not possible to bind the result of a dynamic SQL statement to a SQLScript variable.
  • SQL injection bugs could harm the integrity or security of the database.

Built-In Procedures to Secure Dynamic SQL

For variables containing an SQL string literal, use the following: ESCAPE_SINGLE_QUOTES (string_var)

For variables containing a delimited SQL identifier, use the following: ESCAPE_DOUBLE_QUOTES (string_var)

To check that a variable contains safe, simple SQL identifiers (up to num_tokens, where the default is 1), use the following: IS_SQL_INJECTION_SAFE(string_var[, num_tokens])

Note

Although supported, SAP recommends that you carefully consider whether you really need to use dynamic SQL because it might have a negative impact on security and/or performance.

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