4.2SQLScript

The previous section showed how SAP HANA enhances the SQL standard with some functions. Let’s now discuss SQLScript.

SQLScript is an SQL-based programming language in SAP HANA whose goal is to move data-intensive calculations to the database with little effort and in their entirety. In the following, we’ll first detail the qualities of SQLScript and then describe how SQLScript is processed in the SAP HANA database (Section 4.3 will then explain how to create procedures in SAP HANA.)

4.2.1Basic Principles of SQLScript

SQLScript has several advantages over Open SQL and the SQL standard. We’ll use a specific example to illustrate the intrinsic qualities of SQLScript. Note that some details are omitted at first so that we can discuss them in later sections.

As was the case in the previous chapters, the example we’ll use here is based on the SFLIGHT data model. For this example, we’ll calculate two key performance indicators (KPIs) for an airline’s top connections:

We also want to identify those travel agencies that achieve the highest sales revenue based on an airline’s top connections. The sales revenue for each travel agency is determined in the same way as the total booking revenue.

You can use SQLScript to implement database procedures and calculation views (see Section 4.4.3). Internally, calculation views implemented using SQLScript are represented as database procedures.

A database procedure comprises input/output parameters and the processing logic. You can use database procedures to modularize complex tasks. Figure 4.1 demonstrates how different database procedures can interact with one another to determine the KPIs and travel agencies associated with an airline’s top connections.

Using Multiple Database Procedures to Modularize Complex Tasks

Figure 4.1Using Multiple Database Procedures to Modularize Complex Tasks

Internally, the GET_DATA_FOR_TOP_CONNECTIONS database procedure uses the following:

Thanks to modularization, you can simply reuse parts of the implementation for other tasks. For example, you can call the GET_KPIS_FOR_CONNECTIONS method for an airline’s top connections as well as for any connections for multiple airlines.

In addition to using multiple database procedures to modularize complex tasks, SQLScript also enables you to split up complex database queries within a procedure. There you can assign the result of a SELECT statement to a table variable and then use this table variable for subsequent SELECT statements. We’ll now demonstrate this using the example of the GET_AGENCIES_FOR_CONNECTIONS procedure.

The purpose of this procedure is to aggregate all bookings that weren’t canceled for a given set of flight connections, to identify the five travel agencies with the highest sales revenue, and then read the addresses of the five travel agencies identified. The corresponding database query can look as shown in Listing 4.2.

ET_AGENCIES = SELECT A.AGENCYNUM, T.NAME, T.POSTCODE,
T.CITY, T.COUNTRY, A.PAYMENTSUM, A.CURRENCY
FROM ( SELECT TOP 5 B.AGENCYNUM, SUM(B.LOCCURAM) AS
PAYMENTSUM, B.LOCCURKEY AS CURRENCY
FROM :IT_CONNECTIONS AS C INNER JOIN SBOOK AS B ON
B.CARRID = C.CARRID AND B.CONNID = C.CONNID
WHERE B.MANDT = :IV_MANDT AND B.CANCELLED <> 'X'
GROUP BY B.AGENCYNUM, B.LOCCURKEY
ORDER BY SUM(B.LOCCURAM) DESC ) AS A
INNER JOIN STRAVELAG AS T ON
T.AGENCYNUM = A.AGENCYNUM WHERE T.MANDT = :IV_MANDT;

Listing 4.3Example of a Complex Database Query

Alternatively, with SQLScript, you can use one table variable to combine two database queries (see Listing 4.4).

LT_AGENCIES = SELECT TOP 5 B.AGENCYNUM,
SUM(B.LOCCURAM) AS PAYMENTSUM, B.LOCCURKEY AS
CURRENCY FROM :IT_CONNECTIONS AS C
INNER JOIN SBOOK AS B ON B.CARRID = C.CARRID AND
B.CONNID = C.CONNID
WHERE B.MANDT = :IV_MANDT AND B.CANCELLED <> 'X'
GROUP BY B.AGENCYNUM, B.LOCCURKEY
ORDER BY SUM(B.LOCCURAM) DESC;

ET_AGENCIES = SELECT A.AGENCYNUM, T.NAME, T.POSTCODE,
T.CITY, T.COUNTRY, A.PAYMENTSUM, A.CURRENCY
FROM :LT_AGENCIES AS A INNER JOIN STRAVELAG AS T
ON T.AGENCYNUM = A.AGENCYNUM
WHERE T.MANDT = :IV_MANDT;

Listing 4.4Splitting Up a Complex Database Query

The following advantages are associated with using SQLScript to split up complex database queries:

The database optimizer decides how to execute multiple database queries (both within and across database procedures). Internally, it can combine multiple SELECT statements into one database query. Under certain conditions, the optimizer is able to process multiple SELECT statements in parallel (if the statements are independent).

We’ll also demonstrate the parallel processing of independent database queries using the same example. As you know from the previous descriptions, several steps must be undertaken to complete the task:

Calculating the KPIs and identifying the travel agencies with the highest sales revenue (including reading their addresses) are dependent on identifying an airline’s top connections but are fully independent of each other. Consequently, the SAP HANA database can process these database queries in parallel, as shown in Figure 4.2.

Parallel processing of database queries in SQLScript is a fundamental difference from Open SQL. If you use Open SQL to send multiple SELECT statements to the SAP HANA database (and use, e.g., the FOR ALL ENTRIES clause to connect them), they are processed in succession.

Parallel Processing

Figure 4.2Parallel Processing

As a result of processing database queries in parallel, tasks can be accelerated considerably. However, this is only one form of parallelization in SAP HANA. The system can also use multiple threads to process individual database queries (e.g., calculating KPIs) in parallel (see also Chapter 1, Section 1.2.2). Open SQL also benefits from this form of parallelization.

SQL is a declarative programming language. Declarative programming focuses on the problem description (i.e., the “what”). SQLScript adds elements of imperative programming to the SQL standard. Imperative programming focuses on the problem solution (i.e., the “how”).

The imperative language elements in SQLScript enable you to work, for example, with case distinctions (IF ... THEN ... ELSEIF ... ELSE ... END IF) and loops (WHILE ... ENDWHILE) in database procedures and calculation views, thus enabling you to orchestrate the (declarative) processing logic. Here, you also have options that extend far beyond the SQL standard.

Let’s imagine that you want to identify an airline’s top connections based on the sales revenue or percentage utilization. In this case, you can assign an input parameter to the DETERMINE_TOP_CONNECTIONS database procedure, and, depending on its value, you can execute different database queries (see Listing 4.5).

IF IV_ALGORITHM = 'P' THEN
ET_CONNECTIONS = SELECT TOP 5 CARRID, CONNID
FROM SFLIGHT
WHERE MANDT = :IV_MANDT AND CARRID = :IV_CARRID
GROUP BY CARRID, CONNID
ORDER BY SUM(PAYMENTSUM) DESC;
ELSE
ET_CONNECTIONS = SELECT TOP 5 CARRID, CONNID
FROM SFLIGHT
WHERE MANDT = :IV_MANDT AND CARRID = :IV_CARRID
GROUP BY CARRID, CONNID
ORDER BY AVG(TO_DECIMAL(SEATSOCC + SEATSOCC_B +
SEATSOCC_F) / TO_DECIMAL(SEATSMAX + SEATSMAX_B +
SEATSMAX_F)) DESC;
END IF;

Listing 4.5Imperative Language Elements

Note that the use of imperative programming may prevent parallelization of database queries. In particular, we recommend that you avoid loop processing combined with the use of cursors as much as possible.

It’s often a challenge to access business logic in the event of a code pushdown from the application layer to the database layer. In ABAP application development, a large part of the business logic previously resided in the application layer and therefore was only available for data records transferred from the database to the application server. Currency conversion is a good example here.

SQLScript makes crucial business logic functions available in the database layer. In addition to currency conversion, SQLScript also supports the conversion of units of measure in accordance with Customizing for AS ABAP. You can also access the SAP HANA function libraries in database procedures and calculation views (see Chapter 12), which gives you considerably more options in terms of moving data-intensive calculations to the database than those available with Open SQL or Native SQL.

Now that we’ve discussed the advantages of SQLScript, we’ll explain how the SAP HANA database processes SQLScript. Here, we distinguish between processing when activating SQLScript and processing when invoking SQLScript.

When activating SQLScript, the SAP HANA database first checks the syntax of the database procedure. The system then checks the semantic correctness. It derives, among other things, the table variable types, which can be implicitly typed in SQLScript. The system checks whether the variables are being used consistently and whether all of the output parameters associated with the database procedure have been filled.

The system then optimizes the database procedures and creates a calculation model (possibly multilevel) that resembles a graphical calculation view. In this model, imperative language elements are generated as L nodes. L is a programming language that makes some language elements of C++ available and supports SAP HANA’s system of data types. Internally, the SAP HANA database uses L as an intermediate language when compiling a database procedure to C++.

Finally, the system stores the database procedure in the database catalog and, if necessary, in the SAP HANA repository.

Two phases are associated with invoking a database procedure: compilation and execution.

When compiling a database procedure, the SAP HANA database rewrites the database procedure call so that it can be executed by the calculation engine. Then, when executing the database procedure, the system binds the actual parameters associated with the call to the calculation model created when the procedure was activated. This process is known as instantiating the calculation model. During instantiation, the system possibly optimizes the calculation model further. Lastly, the system uses the engines available (see Chapter 1, Section 1.3) to execute the calculation model.

4.2.2SQLScript Programming

Now that you’re familiar with some basic principles of SQLScript, let’s discuss the creation of database procedures and table types, variables, imperative enhancements, and calculation engine (CE) plan operators.

As discussed previously, a database procedure in SAP HANA consists of input/output parameters and the processing logic. From a technical perspective, SQL is used to generate, call, change, and delete database procedures. The SAP HANA database provides the following statements for this purpose:

Even though you can execute these commands directly via the SQL console, we don’t recommend this (with the exception of simple tests) because procedures created via the SQL console aren’t stored in the SAP HANA repository. You therefore lose version management and transport management, among other things. Instead, we recommend that you create native database procedures in SAP HANA Studio in the SAP HANA Development perspective and thus in the SAP HANA repository (see Section 4.3). If you want to use procedures from ABAP, you should use ABAP Managed Database Procedures (AMDP), as described in Chapter 6.

SAP HANA distinguishes between two types of database procedures:

The use of INSERT, UPDATE, DELETE, and Data Definition Language (DDL) statements are prohibited in read-only procedures. Whereas read/write procedures can call any database procedure, read-only procedures can only call read-only procedures (see Figure 4.3).

Read-Only and Read/Write Procedures

Figure 4.3Read-Only and Read/Write Procedures

In general, SQLScript is used to implement database procedures. The SAP HANA database also supports two additional programming languages:

The following sections first describe which commands are available in the SAP HANA database for creating database procedures and table types (which can be used for defining the interface to the procedure). Then we discuss variables, imperative enhancements, and CE plan operators.

Creating Database Procedures

You use the CREATE PROCEDURE statement to create a database procedure. The complete syntax is provided in Listing 4.6.

CREATE PROCEDURE <proc_name> [(<parameter_clause>)]
[LANGUAGE <lang>] [SQL SECURITY <mode>]
[DEFAULT SCHEMA <default_schema_name>]
[READS SQL DATA [WITH RESULT VIEW <view_name>]] AS
BEGIN [SEQUENTIAL EXECUTION]
<procedure_body>
END

Listing 4.6Syntax for the CREATE PROCEDURE Statement

The CREATE PROCEDURE statement is followed by the name <proc_name> of the database procedure and a series of optional additions. Finally, the statement is enclosed between BEGIN and END, with the actual implementation in the form of source code <procedure_body> (i.e., the processing logic). The optional additions have the following meaning:

Creating Table Types

If you want to use table parameters in a database procedure interface, you can define them with reference to database tables or table types. Table types are an enhancement to the SQL standard and are part of the data type system supported by the SAP HANA database. Conceptually, table types are similar to the structures within the DDIC. The relevant command here is CREATE TYPE. The complete syntax is shown in Listing 4.7.

CREATE TYPE <type_name> AS TABLE (<column_definition>
[{,<column_definition>}...])

Listing 4.7Syntax for the CREATE TYPE Statement

The CREATE TYPE command is followed by the name of the table type <type_name> and individual columns. Each table type has at least one column, and each column definition (<column_definition>) comprises the name of the column and its data type (simple).

Using Table Variables

Table variables can be input/output parameters or local variables. They are based, either explicitly or implicitly, on a table type and can be linked to the result of an SQL statement or CE plan operator (see the upcoming section on the usage of CE plan operators) by means of the equals sign (=). The contents of the table variables are accessed using the relevant variable name supplemented by the prefix : (colon). This occurs in the same way in which database tables are accessed. We’ll explain this a bit later via an example.

If you want to define a tabular input or output parameter, you must type this explicitly. When you assign the result of an SQL statement or CE plan operator to a tabular output parameter, the system checks whether both are type-compatible.

You cannot explicitly type a local table variable using the DECLARE statement. If required, the system automatically derives the required table type from the SQL statement or assigned CE plan operator. This simplifies programming and provides more flexibility but can also lead to unnecessary type conversions.

The example in Listing 4.3 shows how to use table variables. We’ve intentionally omitted some details from the source code (e.g., restricting the selection to one client).

CREATE PROCEDURE EXAMPLE_TABLE_VARIABLES (OUT
ET_FLIGHTS TT_FLIGHTS) LANGUAGE SQLSCRIPT SQL SECURITY
INVOKER READ SQL DATA AS
BEGIN
LT_FLIGHTS = SELECT CARRID, CONNID, FLDATE
FROM SFLIGHT;
ET_FLIGHTS = SELECT * FROM :LT_FLIGHTS;
END;

Listing 4.8Using Table Variables

In this example, a SELECT statement is used to assign the CARRID, CONNID, and FLDATE columns in database table SFLIGHT to the LT_FLIGHTS local table variable, which is implicitly typed by the system.

Then, a second SELECT statement is used to assign the contents of the LT_FLIGHTS table variable to the ET_FLIGHTS table variable, which is an output parameter and is explicitly typed. It uses the TT_FLIGHTS table type.

Using Scalar Variables

Similar to table variables, scalar variables can be input/output parameters or local variables. They are based on a simple data type. Values are assigned using the assignment operator :=. Similar to table variables, the value of scalar variables is accessed using the variable name supplemented by the prefix :.

You must always explicitly type a scalar variable. For local variables, you use the DECLARE statement (similar to local table variables). During the typing process, you can refer to the SQL data types supported by SAP HANA.

The following simple example shows how to use scalar variables. Once again, we’ve intentionally omitted some details from the source code in Listing 4.4.

CREATE PROCEDURE EXAMPLE_SCALAR_VARIABLES (IN
IV_CUSTOMID NVARCHAR(8) , IN IV_ADDITIONAL_DISCOUNT
INTEGER) LANGUAGE SQLSCRIPT SQL SECURITY INVOKER
READS SQL DATA AS
BEGIN
DECLARE LV_DISCOUNT INTEGER;
DECLARE LV_NEW_DISCOUNT INTEGER;

SELECT TO_INT(DISCOUNT) INTO LV_DISCOUNT
FROM SCUSTOM WHERE ID = :IV_CUSTOMID;
LV_NEW_DISCOUNT := :LV_DISCOUNT +
:IV_ADDITIONAL_DISCOUNT;
END;

Listing 4.9Using Scalar Variables

The database procedure in this example increases the customer discount by a specific percentage by using multiple scalar variables. The variables IV_CUSTOMID and IV_ADDITIONAL_DISCOUNT are input parameters, whereas LV_DISCOUNT and LV_NEW_DISCOUNT are local variables.

Using Imperative Enhancements

If necessary, you can also work with imperative language elements in SQLScript, which we’ll briefly discuss here for the sake of completeness. In general, however, you should only use imperative enhancements as often as necessary, but as little as possible.

In particular, SQLScript is used to move data-intensive calculations to the database. SAP HANA should process data-intensive calculations in parallel as much as possible. If you work with imperative enhancements, this may prevent parallelization.

You can use control structures to control (orchestrate) a database procedure’s process flow. SQLScript supports loops and case distinctions.

The WHILE... DO... END WHILE and FOR... IN... DO... END FOR statements are available for loop processing. If you want to end the current loop pass during loop processing, you can use the CONTINUE statement for this purpose. If you want to fully exit a loop, you can use the BREAK statement for this purpose. You can use the IF... THEN... ELSEIF... ELSE... END IF statement to implement case distinctions.

The sample database procedure in Listing 4.5 illustrates the use of control structures.

LT_SPFLI = SELECT MANDT, CARRID, CONNID FROM SPFLI
WHERE MANDT = :IV_MANDT
AND AIRPFROM = :IV_AIRPFROM
AND AIRPTO = :IV_AIRPTO;
LV_DAYS := 0;
WHILE LV_DAYS <= IV_MAX_DAYS DO
ET_FLIGHTS = SELECT P.CARRID, P.CONNID, F.FLDATE
FROM :LT_SPFLI AS P
INNER JOIN SFLIGHT AS F ON F.MANDT = P.MANDT AND
F.CARRID = P.CARRID AND F.CONNID = P.CONNID
WHERE TO_DATE(F.FLDATE) >=
ADD_DAYS (TO_DATE(:IV_FLDATE), –1 * :LV_DAYS)
AND TO_DATE(F.FLDATE) <=
ADD_DAYS (TO_DATE(:IV_FLDATE), :LV_DAYS);
SELECT COUNT(*) INTO LV_CONNECTION_FOUND
FROM :ET_FLIGHTS;
IF :LV_CONNECTION_FOUND > 0 THEN
BREAK;
ELSE
LV_DAYS := :LV_DAYS + 1;
END IF;
END WHILE;

Listing 4.10Control Structures in SQLScript

The database procedure determines the flights available between two given airports (IV_AIRPFROM and IV_AIRPTO) for a given flight date (IV_FLDATE). If (and only if) no flight is available for the given flight date, the database procedure tries to find flights one day before and one day after. If (and only if) no flights are available for this date, the database procedure tries to find flights two days before and two days after. The IV_MAX_DAYS input parameter controls the maximum number of days searched before or after a given flight date. The database procedure uses a WHILE... DO... END WHILE loop combined with an IF... THEN... ELSE... END IF case distinction. It uses the BREAK statement to exit the loop prematurely, if necessary.

Similarly, as described for Open SQL in Chapter 3, Section 3.2.2, you can also work with cursors in SQLScript. The example in Listing 4.11 shows how to define a cursor in SQLScript and then use it to read data.

DECLARE CURSOR LT_CONNECTIONS (LV_MANDT NVARCHAR(3),
LV_CARRID NVARCHAR(3)) FOR
SELECT CARRID, CONNID FROM SPFLI
WHERE MANDT = :LV_MANDT AND CARRID = :LV_CARRID;
BEGIN
FOR LS_CONNECTIONS AS LT_CONNECTIONS(:IV_MANDT,
:IV_CARRID) DO
/* DO SOMETHING */
...
END FOR;
END;

Listing 4.11Cursor Processing with SQLScript

Only use cursors if there is no other way to implement the required processing logic. The SAP HANA database can’t easily optimize database procedures that contain cursors.

You can use dynamic SQL to construct SQL statements at runtime. The EXEC and EXECUTE IMMEDIATE statements are available for this purpose.

The example in Listing 4.12 shows how you can construct a SELECT statement at runtime to determine an airline’s flight connections. In this example, it isn’t absolutely necessary to use dynamic SQL.

EXECUTE IMMEDIATE 'SELECT * FROM SPFLI
WHERE MANDT = ''' || :IV_MANDT || ''' AND CARRID =
''' || :IV_CARRID || '''';

Listing 4.12Dynamic SQL

We advise you to refrain, as much as possible, from using dynamic SQL because it has limited optimization options. A database procedure that contains dynamic SQL may need to be recompiled for each call. With dynamic SQL, there is also a risk of SQL injections.

Using Calculation Engine Plan Operators

In this section, for the sake of completeness, we’ll discuss CE plan operators, which you can use in database procedures as an alternative to SQL statements. SAP no longer recommends using them for implementing database procedures. Instead, you should always use SQL statements within database procedures whenever possible. For this reason, we only briefly discuss the principle of CE plan operators and provide an overview of existing functions.

To help you understand the concept of CE plan operators, we’ll consider a very simple database procedure for determining the sales revenue of all flight connections associated with an airline. When an SQL statement is implemented, this database procedure looks like that shown in Listing 4.6.

ET_PAYMENTSUM = SELECT CARRID, CONNID, CURRENCY,
SUM(PAYMENTSUM) AS PAYMENTSUM
FROM SFLIGHT
WHERE MANDT = :IV_MANDT AND CARRID = :IV_CARRID
GROUP BY CARRID, CONNID, CURRENCY;

Listing 4.13Implementation Using an SQL Statement

The SQL statement selects data from table SFLIGHT. This statement uses a WHERE clause to restrict the selection to the specified airline. It also uses the SUM aggregate function combined with a GROUP BY expression to add the sales revenue for each airline, connection, and currency.

When CE plan operators are used, the same database procedure looks like the one shown in Listing 4.7.

LT_SFLIGHT = CE_COLUMN_TABLE("SFLIGHT");
LT_SFLIGHT_PROJECTION = CE_PROJECTION(:LT_SFLIGHT,
["MANDT", "CARRID", "CONNID", "CURRENCY",
"PAYMENTSUM"], '"MANDT" = '':IV_MANDT'' AND
"CARRID" = '':IV_CARRID'' ');
LT_SFLIGHT_AGGREGATION = CE_AGGREGATION(
:LT_SFLIGHT_PROJECTION, [SUM("PAYMENTSUM") AS
"PAYMENTSUM"], ["CARRID", "CONNID", "CURRENCY"]);
ET_PAYMENTSUM = CE_PROJECTION(:LT_SFLIGHT_AGGREGATION,
["CARRID", "CONNID", "CURRENCY", "PAYMENTSUM"]);

Listing 4.14Implementation Using CE Plan Operators

The database procedure uses different CE plan operators, which are linked to one another by means of table variables:

  1. First, the database procedure uses the CE_COLUMN_TABLE CE plan operator to bind the LT_SFLIGHT table variable to database table SFLIGHT.

  2. It then uses the CE_PROJECTION CE plan operator to restrict the selection to the MANDT, CARRID, CONNID, and CURRENCY columns, as well as to restrict the selection to the connections associated with the specified airline. The LT_SFLIGHT table variable, which was bound in the first step, is used as the input, while the LT_SFLIGHT_PROJECTION table variable is used as the output.

  3. The CE_AGGREGATION CE plan operator adds the sales revenue for each airline, connection, and currency. Here, the LT_SFLIGHT_PROJECTION table variable is used as the input, and the LT_SFLIGHT_AGGREGATION table variable is used as the output.

  4. In a final step, the database procedure uses the CE_PROJECTION CE plan operator to perform a projection again. This projection is necessary because (due to the way the CE_AGGREGATION works) the sequence of the columns in the LT_SFLIGHT_AGGREGATION table variable doesn’t correspond to the sequence of the columns in the ET_PAYMENTSUM output parameter.

CE plan operators are implemented directly in the calculation engine. They are divided into data source access operators, relational operators, and special operators.

You can use data source access operators to bind table variables to a database table or view. The access operators include CE_COLUMN_TABLE, CE_JOIN_VIEW, CE_OLAP_VIEW, and CE_CALC_VIEW.

Relational operators make the operations typically associated with relational algebra available to you. They work on the table variables that you’ve previously bound using the data source access operators, for example. SAP HANA currently provides the following access operators: CE_JOIN, CE_PROJECTION, CE_AGGREGATION, CE_UNION_ALL, and CE_CALC.

In addition to data source access operators and relational operators, the calculation engine currently makes the following three additional operators available: