Every SAP HANA developer should be fluent in SQL and ideally SQLScript.
Although for convenience and speed, you’ll often rely on development tools to generate the SQL for you, for advanced functionalities, for the latest features, and for troubleshooting security or performance issues, you’ll need to understand what the SQL is doing and use SQLScript for what SQL cannot do (or would do poorly).
In this blog post, we’ll describe both SQL and SQLScript as they relate to SAP HANA. In addition, we’ll also cover how SQLScript can be used in ABAP-managed database procedures (AMDP), which, as the name indicates, are ABAP development objects and SAP HANA procedures at the same time, which enables you to include SQLScript inside your ABAP objects.
SQL
SQL is the lingua franca of the database, but implementations can differ widely. To effectively work with SQL in SAP HANA, you’ll need a copy of the SQL Reference Guide on your desk (preferably on the desktop of your computer, as the SPS 04 guide consists of 2,259 pages). In this guide, you’ll find the available data types, operators, expressions, predicates, SQL functions, statements, error codes (all 5,737), and system limitations (for example, 16,383 table locks, 4,095 tables in a statement, 2 GB SQL statement length, and 1,945 GB size of all stored procedures combined).
The Structured Query Language (SQL) is as old as IBM’s System R, the first relational database management system (RDBMS), and goes back to the early 1970s. SQL has been an ANSI/ISO standard since the mid-1980s, and from time to time, a new version is published (called a dialect), the latest being SQL:2016. Most databases are ANSI-compliant to some extent, most often SQL:1992, which means that you can run the same SQL statement on all compliant databases with the same result and without errors. In practice, databases have vendor-specific extensions to the SQL language to take advantage of specific features, and SAP HANA is no exception. For example, the SQL statement SELECT TA.a1, TB.b1 FROM TA, LATERAL (SELECT b1, b2 FROM TB WHERE b3 = TA.a3) TB WHERE TA.a2 = TB.b2 uses lateral joins and thus will only execute on SAP HANA and requires a specific release level that supports lateral joins (SPS 04 or later).
SQL views are used extensively in SAP HANA modeling and present a virtual table as input for analysis. Through the use of SQL functions, WHERE clause filters, and JOIN statements, a specific and dynamic result set can be returned when the view is accessed. Although you can create views at runtime with the CREATE VIEW statement, most often views will be defined by the developer as design-time artifacts (PurchaseOrderItem.hdbview) and deployed to the repository or, in case of SAP HANA XS Advanced, to an HDI container.
Although SAP HANA includes different store types and you can access the database using different clients (ODBC, JDBC, Python, .NET) and protocols (HTTP/OData), you’ll still access the database through the same SQL interface. This main access gate to the SAP HANA platform is important, and developers must be familiar with the different functionalities it provides. When you interact with the database using the SAP HANA studio or the SAP HANA cockpit—every action, almost every click (create a table, change a parameter) is converted into a SQL statement. Every performance graph or pie chart with table values is the result of a SQL query, which, again, is the only way to interact with the database. One exception is starting/stopping the database; instead, you’ll connect to an operating system process and execute a sapcontrol command. The same executable is used with SAP NetWeaver systems, although not every option and parameter is supported as used for SAP HANA.
Another indication of the importance of SQL is that a new functionality is typically first released on the database side (the “backend”) with only a SQL interface. The frontend, the client UI, is often added later. Lightweight Directory Access Protocol (LDAP) authentication, for example, was introduced in the first SAP HANA 2.0 release with a modification for the CREATE USER SQL statement. The corresponding configuration interface in the SAP HANA cockpit arrived later, in a subsequent release, and because SAP HANA studio is no longer under development, no UI exists for LDAP authentication at all. Although a long list and not always easy to understand at first, the release notes for changes to SQL and SQL views provide a lot of information about development priorities.
Support for additional features (capabilities) is provided through SQL functions. For example, with the full-text LANGUAGE function, SAP HANA returns the language of a specified column:
SELECT LANGUAGE(CONTENT), CONTENT FROM T;
The JSON_VALUE function parses JSON:
SELECT JSON_VALUE('{"item1":10}', '$.item1') AS "value" FROM DUMMY;
Series data functions provide functionalities for SAP HANA (time) series. These functions are aggregate functions for analytics; security functions; spatial functions; hierarchy functions; functions to perform mathematical operations; and functions to work on strings, arrays, data type conversion; and more.
For good performance, you’ll need good SQL. In SQL, you can sometimes get the same result in different ways. Sometimes, the response time will be comparable, but sometimes, changing a SQL expression turns a 10-minute query into a split-second response time. SQL statements are optimized for execution by the SQL parser. Statistics on the database objects (the number of rows, the number of empty values) provide inputs to the parser to determine the most optimal execution plan. However, in some situations, the developer will need to provide the parser with additional information to come to the best plan. This information are called hints, and you can include them in your statements.
To analyze SQL statement performance, you can use SQL Analyzer (Plan Visualizer in SAP HANA studio), the Expensive Statement app, and SQL plan cache, to name a few applications. More advanced tools are the SQL trace and the Expensive Statement trace. Trace files can be difficult to read, but tools are available to help you parse the information, like the SQL Trace Analyzer.
SQLScript
The objective for SQLScript is to embed data-intensive application logic into the database. In a traditional three-tier architecture, the middle tier, the application layer, tends to dumb down the database and executes all the business logic. For example, the application might perform the following steps: Select all rows from table A, move to the application server, and then loop through each row to perform some action. A massive data copy is a common approach with imperative languages like Java or ABAP, but this approach fails to leverage the (massive) parallel processing capabilities of SAP HANA with multicore CPUs and all the data residing in-memory. Code pushdown is required to get the best performance, and SQLScript makes code pushdown possible.
Conceptually, the language is related to stored procedures and uses a combination of declarative and (imperative) orchestration logic (loops and conditions). You can use the SQL data types available in the database as well as create your own user-defined types, just as you can create user-defined functions (UDFs) and libraries. Anonymous blocks and autonomous transactions are supported, as are features like SQLScript encryption.
A few key best practices to keep in mind when using SQLScript include the following:
- Break down complex queries into subexpressions: Allows the compiler to recognize patterns and avoid repeating expensive computations.
- Reduce dependencies and avoid cursors (loops): Maximizes the opportunities for parallel processing.
- Avoid dynamic SQL: Dynamic SQL is generated at runtime and requires compilation and query optimization for each call. Static SQL is compiled once and can be executed many times, which results in much faster performance.
To help you write SQLScript code, the SAP Web IDE for SAP HANA includes a SQLScript debugger, also available in the ADT for Eclipse.
SQL and SQLScript are documented in two reference guides, available from the SAP Help Portal:
- SAP HANA SQL and System Views Reference
- SAP HANA SQLScript Reference
For additional information, see the following SAP Notes:
- SAP Note 2000002 – FAQ: SAP HANA SQL Optimization
- SAP Note 2142945 – FAQ: SAP HANA Hints
- SAP Note 2412519 – FAQ: SAP HANA SQL Trace Analyzer
For some good hands-on exercises, try out the following SQLScript tutorial group on the SAP Developer Community: “Tutorial Navigator: Leverage SQLScript in Stored Procedures and User Defined Functions”
Learn about commonly used SQL statements for SAP here.
ABAP-Managed Database Procedures
To leverage parallel processing and the in-memory database for business applications powered by SAP HANA, the same code pushdown development paradigm found in SQLScript has also been also promoted for ABAP development. AMDP are central to this approach.
The ABAP programming language, short for Advanced Business Application Programming, was first released in 1983—halfway between C (1972) and Java (1995). Initially, ABAP was a general reporting preprocessor (Allgemeiner Berichtsaufbereitungsprozessor) for SAP R/2. Later, ABAP evolved as the development language for the SAP R/3 (1992) platform, mainly for Reports, Interfaces, Conversions, Extensions, Forms, and Workflows (RICEFW objects). ABAP is a proprietary language with object-oriented extensions added in 1999.
ABAP programs are stored inside the database but abstract specific database implementations using Open SQL, which works on any other database (AnyDB). With the development of SAP business applications that only run on SAP HANA, like SAP S/4HANA and SAP BW/4HANA, code pushdown is the new paradigm. Code pushdown is also referenced as code-to-data, and calls for data-intensive operations to be pushed down to the database, leveraging parallel processing and in-memory computing on the database tier as much as possible, which is promoted by the use of CDS and AMDP.
AMDP is a framework that enables you to call SQLScript embedded in an ABAP objects. The figure below shows an ABAP procedure with between the brackets of the execute_ddl ABAP function call SQLScript code. The development, maintenance, and transport of the SQLScript are all performed on the ABAP side.
A good place to start is the ABAP for SAP HANA Development User Guide, available from the SAP Help Portal.
For some hands-on exercises, try out some of the tutorials on the SAP Developer Community for topic “ABAP Development”: “Tutorial Navigator: ABAP Development”.
11
Editor’s note: This post has been adapted from a section of the book SAP HANA 2.0: An Introduction by Denys van Kempen.
Comments