BTP - External Entities

Table of contents

• Introduction
• Prerequisites
• SDA HANA Cloud to HANA on-premise
• External Entity 
• Logical External Schema
• Outbound Service
• Communication Scenario
• Configuration
• Communication System
• Communication Arrangement 
• Read-only access 
• What happens in the background
• Write access
• Next Steps
• Conclusion

External Entities are here. They make it possible to read data from remote databases as if it were present in the local database. With a little trick, you can also access on-premise databases. We'll look at that today.

 

Introduction

SAP introduced External Entities with Release 2408. This makes it possible to access tables in remote databases via SDA (Smart Data Access). Unfortunately, access from the ABAP environment to on-premise databases is currently not possible, as the CloudConnector required for this cannot be reached from the ABAP environment's HANA DB. However, with a HANA Cloud database between the ABAP environment and the CloudConnector, this access is also possible.  

The architecture for the scenario described in this article is as follows: 

 

Prerequisites

The following systems are required for the scenario: 

• ABAP environment 
• SAP HANA Cloud Database 
• Cloud Connector 
• SAP HANA Database (onPremise) 

 

For communication, the CloudConnector must be connected to the same BTP subaccount, like the SAP HANA Cloud database. The HANA on-premise database must be accessible and shared in the CloudConnector:

 

SDA HANA Cloud to HANA on-premise

Before we can look at the external entities for our scenario, we need to create the tables we want to access as virtual tables in the HANA Cloud database using SDA. To do this, we need a so-called RemoteSource in the HANA Cloud database for each relevant on-premise database schema. We can create these as follows:

CREATE REMOTE SOURCE <remote_source> 
    ADAPTER hanaodbc 
    CONFIGURATION
    'servernode=<virtual_host>:30041;useHaasSocksProxy=true;sccLocationId=<cloud_connector_location_id' 
    WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<User>;password=<Password>'; 

 

For each table that should be available in this way, we must create a virtual table in the HANA Cloud database once: 

CREATE VIRTUAL TABLE <virtual_table_name> AT <remote_source>."<NULL>".<schema>.<table_name>;

 

For our example, we use the following on-premise database table:

 

External Entity 

Now it's finally worth creating an external entity in the ABAP environment. To do this, we create a new DataDefinition and select the template for ExternalEntities:

 

In the editor, we can now add the attributes that interest us. The complete table looks like this:

@AccessControl.authorizationCheck: #NOT_REQUIRED 
@EndUserText.label: 'ExternalEntity: App Holiday' 
define external entity ZCT_X_AppHoliday external name APP_HOLIDAY 
{ 
  key Holiday         : abap.datn     external name HOLIDAY; 
      Description    : abap.char(50) external name REMARK; 
} with federated data 
  provided at runtime 

 

If we take a closer look, we see that the first two statements are annotations. We will not consider these further in the blog post and will not pay any further attention to them here. 

The actual definition begins with the keywords "define external entity." We then define the name under which the table should be accessible in the ABAP environment. After the keywords "external name," we must specify the name of the table in the HANA Cloud database.

In the projection list, we define and type the attributes of the table that we want to make available in the ABAP environment. Here, too, we must specify the names of the attributes in the remote table. We don't need to include all of the table's attributes.

Finally, we need to add the keywords "with federated data" and "provided at runtime." The first part indicates that the data is not located in the system itself. With the second part, we define that when accessing the table, we specify its actual location. SAP plans to include the target of the external entity at design time. This is not currently possible.

 

Logical External Schema

To access the external entity, we need a logical external schema for each schema on the HANA Cloud database. We create this as a new object in the ADT:

 

Outbound Service

The logical external schema must be used in an outbound service. We also create this in the ADT:

 

Communication Scenario

The last ADT object we need is a Communication Scenario. This allows us to create the configuration for the connection to the HANA Cloud database directly in the FLP. But one step at a time, here is the Communication Scenario:

 

In the "Outbound" tab we need to add our outbound service:

 

After saving, we need to publish the scenario.

 

Configuration

We have prepared everything in the ADT and can now store the configuration, i.e., the actual connection information.

 

Communication System

First, we create a communication system:

 

Here we have to enter the URL to the HANA Cloud database in the host name. In addition, we need to configure the system for remote SQL access and specify the username and password for the database.

 

Communication Arrangement 

We will use the communication system we have just created in a new communication arrangement:

 

Here is where we need to specify the database schema in which our virtual table is located.

 

Read-only access 

Now we can use the external entity. This is very easy with the following select: 

SELECT 
  FROM ZCT_X_AppHoliday 
    PROVIDED BY zct_les_hana_cloud 
  FIELDS * 
  INTO TABLE @DATA(holidays). 

 

Behind the keyword "Provided by," we must specify the logical external schema we created. This is how the outbound service and thus the communication scenario are found. This tells the ABAP environment how to establish the connection to the HANA Cloud database.

 

What happens in the background

On each first access after a change to the external definition, the ABAP environment checks whether there is a corresponding virtual table in the ABAP environment's database. If this is not the case, it is created automatically by default:

 

In our example, the virtual table is created by SAP as follows:

CREATE VIRTUAL TABLE "SAPABAP"."EEDVT#ZCT_X_APPHOLIDAY#ZCT_LES_HANA_CLOUD#100" AT "DD#LESRS#SAPABAP#ZCT_LES_HANA_CLOUD#100"."<NULL>"."<DB_SCHEMA>"."APP_HOLIDAY"

 

The result of the selection returns exactly the data that is in the on-premises database table:

 

Write access

In addition to read access, we can also write to the external entity. To do this, we must use the keyword "writeable". Add to our data definition:

 

For write access from the code, we must specify a connection. This refers to the logical external schema and is required for both writing and committing: 

DATA(connection) = cl_abap_sql_connection_builder=>write_enabled_4_logical_schema( 
    i_connection_name     = 'R/3*service' 
    i_logical_schema_name = 'ZCT_LES_HANA_CLOUD' 
)->create( ). 

DATA(new_holiday) = VALUE ZCT_X_AppHoliday( Holiday = '20250420' Description = 'Ostersonntag' ). 

INSERT ZCT_X_AppHoliday 
  PROVIDED BY zct_les_hana_cloud 
  CONNECTION @connection 
  FROM @new_holiday. 

COMMIT CONNECTION @connection.

 

Let's take a look at the result in the console:

 

Next Steps

Now that we have read and write access to the data, the question of possible applications naturally arises. For example, it would be conceivable to make the external entity available either for read-only access in a CustomEntity or for write access in an unmanaged RAP BO via FioriElements.

According to the documentation, it is even possible to join tables from different databases with external entities. This opens up new and exciting possibilities, depending on the existing data model.

 

Conclusion

Although the initial setup of the connection requires a few steps, and both the creation of all required ADT objects and the configuration of the connection are somewhat more complex, using the remotely available tables afterwards is incredibly easy. As soon as you want to read more than one table from the same schema of the on-premise HANA database, the cost-benefit ratio changes noticeably, since most of the objects that need to be created only need to be created once per participating database schema.

All in all, in my opinion, SAP has created a very elegant way to read tables from external databases without having to use other technologies (oData, etc.). Initial tests at our company also indicate more than acceptable performance.

The scenario described in this article becomes significantly easier if the data is originally stored in the HANA Cloud database and neither the on-premise database nor the CloudConnector play a role.

 

Source:
SAP Help - External Entities
SAP Help - SAP HANA Smart Data Access