SAP HANA Database – SQL Script Guide

SAP HANA SQL Script

As we have described, the cornerstone of the architectural optimization   for applications designed for SAP HANA is the concept of code push down. Execution of data-intensive logic within the database begins with the usage of standard SQL and views. Developers, however, also need semantics that exceed the capabilities of SQL if they wish to create business logic within the database.

To this end, SAP delivers just such an extension to SQL, called SQL Script. SQL Script is the primary language for creating stored procedures and functions in SAP HANA. The extensions that form SQL Script allows the developer to push down more of the data-intensive logic into the database.

Typical SQL statements are often well suited to parallel processing because of their declarative nature. The primary weakness of SQL becomes apparent when you need to pass the result set of one SQL operation into the input of a second operation. In this scenario, developers traditionally had only two options: copy the result set to the application server, or write complex, nested SQL statements utilizing sub-queries or multiple join conditions.

SQL Script solves this problem by providing a feature that describes  the data flow from one SQL operation to another. This feature enables developers to continue to write logic as they would with an application server and also to declare and use intermediate result sets and variables. Yet it SQL Script often can be compiled down to database joins and sub-queries. This feature offers developers a syntax that is easier for a person to read and write while still providing a logic flow that is well suited to database execution. At the same time, SQL Script avoids [sending] massive data copies to an application server, [thereby] leveraging sophisticated parallel execution strategies within the database.

In addition to all of these benefits, SQL Script provides enhanced control flow capabilities as well as some limited procedural logic constructs. These features make it possible to rewrite some of the more complex parts of the application logic and push them down into the database layer as well.

Overall, SQL Script will improve the readability and structure of your data-intensive logic (compared to complex SQL alone) by passing the results of one SQL statement to another and by breaking complex SQL into smaller chunks. It also brings  the data-intensive  application logic close to the database by combining the existing Declarative logic of SQL with its own built-in functions as well as with orchestration logic such as Data Definition Language (DDL), Data Manipulation Language (DML), and imperative logic constructs.

When compared to standard SQL, SQL Script has several advantages. Procedures can return multiple results, whereas an SQL query can return only a single result set. Going further, SQL Script can decompose complex functions into smaller chunks. This capability enables modular programming, reuse, and a better understandability by functional abstraction. For structuring complex queries, standard SQL allows only the definition of SQL views. These views have neither parameters nor a fixed interface. Another advantage of SQL Script over SQL is that it supports local variables for intermediate results with implicitly defined types. With standard SQL, globally visible views have to be defined even for intermediate steps. Going further, SQL Script has a control logic such as if/else that is not available in the SQL standard. Finally, SQL Script can increase overall performance by utilizing parallel processing within most of its executions.

R

Stored Procedures serve a second purpose within SAP HANA: They are the mechanism that integrates other programming languages and interfaces directly into the database execution layer. The best example of this integration of third-party languages is the introduction of the R Language within Stored Procedures.

R is an open-source software language and environment for statistical computing and graphics that includes more than 3000 add-on packages. R covers a wide range of topics from Cluster Analysis to  Probability Distributions to Graphic Displays to Machine Learning — to name just a few.

With the R integration into SAP HANA, developers can write their R Scripts directly within a stored procedure. During execution, the R script, along with any input data, are sent to the remote, open-source R server. The execution takes place on the R server, and the results are sent back to SAP HANA. SAP provides the interfaces in both SAP HANA and the R server to make this integration completely transparent to the application developer.

Figure 9 — R Language Integration

L

In addition to SQL Script language for the implementation of Stored Procedures, SAP also has the language L. L is a low-level programming language used for programming L PROCEDURES on the SAP HANA DATABASE. It is a robust, low-level, high-performance programming language located inside SAP HANA that  allows code to be created at runtime. The L language is based on concepts from the C/C++ world; it can be roughly characterized as a safe subset of C/C++ that  is enriched by SAP HANA data types and concepts to simplify the manipulation of and interaction with database objects. L provides direct access to the table and column objects that are utilized in the Calculation Engine.

However, the direct access that makes L very powerful also makes it rather dangerous. Therefore, L is currently restricted to SAP internal usage. Customers and partners should use L only in close cooperation with SAP development resources. SAP’s long-term goal is to safely wrap the most useful abilities of L and integrate them into the SQL Script language. At this point, direct access to L will become unnecessary.

Checkout SAP HANA Interview Questions

AFL

AFL stands for Application Function Library. It represents multiple function libraries  — like  Business  Function Library (BFL)  and Predictive Analysis Library (PAL). BFL is a prebuilt, parameter-driven, basic building block library of calculations delivered at high performance, including depreciation, capacity optimization, and time-based functions such as year over year (YoY) and the delay.

The AFLs are written in C/C++, and they become closely linked with the database kernel itself. Significantly, only SAP can write these libraries. Because the AFLs interact so closely with the database kernel, they operate without a virtual machine abstraction. Nevertheless, customers and partners can easily access and reuse these libraries because, with the introduction of SAP HANA 1.0 SP5, SAP can now generate a Stored Procedure interface for them. Therefore, consuming one of the powerful functions of the Application Function Library is now as easy as working with any other SQL Script procedure. In time, SAP plans to release additional tools that make the consumption of these AFL-based procedures even easier. SAP may even open up development of new AFL functions to select partners.

Working with Large Teams

The  SAP  HANA  Change  Manager,  new in SP7 and a  part  of  SAP  HANA XS, includes enhancements aimed at facilitating multi-developer/multi-project environments. Enhancements include changes to the repository interface, enabling interactive artifact testing, and a new change manager interface that helps pinpoint code changes between versions.