Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
SAP HANA Cloud provides several built-in algorithms to process data. Custom logic can be implemented in SQL script and executed in procedures or functions. Apart from that, procedures and can leverage more advanced capabilities from the so called Advanced Processing Engines (like the Machine Learning, Graph and Spatial engines). How can a procedure be exposed to the outside world?

In this post I describe how to create an API in Cloud Foundry to call Stored Procedures in SAP HANA Cloud, using the SAP Cloud Application Programming (CAP) model.

There are lot of great tutorials, videos and blogs about how to work with CAP in SAP HANA Cloud, including how to work with stored procedures (many of them by thomas.jung, as you can see in the links below). However, you need to go through many of them if you want to create the service, add user authentication and finally deploy it.

In this blog I want to showcase a simple step-by-step example to create an API for a procedure with and without input parameters, and implemented through GET and POST methods. The procedure will be used to add information into a table. Custom functions and actions will be added as services, for the GET and POST APIs.

In the first place I will setup the project in SAP Business Application Studio (BAS), for which need to enable some extensions. The already mentioned procedures and table will be then deployed in an HDI container in SAP HANA Cloud. As I started this project from the HANA native template, the next step is to add SAP Cloud Application Model (CAP) to the project. With CAP I will define and implement the services that will be used to call those procedures. After granting HDI access in HANA Cloud to CAP, I will do an initial test of the APIs.

The following sections aim to deploy the APIs with the proper authentication. For that I will add an application router and then the User Authentication using xsuaa. Finally the services will be deployed in Cloud Foundry and I will do a test with Postman.

Setup Project


SAP Business Application Studio (BAS) will be used as development environment, in which the development space should include the following extensions:

  • MTA Tools

  • SAP HANA Tools

  • SAP HANA Database Explorer

  • CAP Tools

  • CDS Graphical Modeler


Once in the development space in BAS, I will create a New project from Template.


New Project from Template


If you choose to start a project from the template SAP HANA Database Project, then you can add CAP following the steps provided by thomas.jung on the video: Converting Existing SAP HANA Project to CAP Model

This is the approach I will follow in this demonstration. In the wizard I set:

  • Project name: HANA_API_Service_Test

  • Schema name: HANA_API_SERVICE_TEST

  • Rest options as default.


On the contrary, if you start from the template CAP Project and want to add SAP HANA, then follow the tutorial Create an SAP CAP Model Project for SAP HANA Cloud.

Create HANA Native Artefacts


All the HANA native artefacts will be located within the db > src folder.

For this demonstration I will create a table to store timestamp and 3 additional fields with a comment (text), a name (text) and a value (integer), by adding the following file:

db > src > CALL_HISTORY.hdbtable
COLUMN TABLE CALL_HISTORY (
"TS" TIMESTAMP,
"COMMENT" NVARCHAR(100),
"NAME" NVARCHAR(100),
"VALUE" INTEGER
)

A simple procedure, REGISTER_CALL, will be used to append a record with just a timestamp, leaving the other fields empty.

db > src > REGISTER_CALL.hdbprocedure
procedure REGISTER_CALL()
language sqlscript
as
begin
insert into "CALL_HISTORY"("TS") values (current_utctimestamp);
end;

To showcase how to use input parameters, the procedure REGISTER_CALL_WITH_PARAM will append a record not only with the timestamp but also with information for the COMMENT, NAME and VALUE fields.

db > src > REGISTER_CALL_WITH_PARAM.hdbprocedure
procedure REGISTER_CALL_WITH_PARAM(
in comment nvarchar(100),
in name nvarchar(100),
in val integer)
language sqlscript
as
begin
insert into CALL_HISTORY values (current_utctimestamp , :comment, :name, :val);
end;


HANA Artefacts


After deploying the HANA artifacts, the MTA.yaml file might have a db module and an hdi_db resource.

More information on how to create and deploy HANA Native artefacts can be found on:

Add SAP CAP to the project


We recommend to do a backup copy of the project before continuing.

For CDS initialization, including NodeJS, in the terminal run:
cds init --add nodejs

As a result the srv and app folders are added, together the file package.json, in the root folder.

Adjust adjust the MTA.yaml file by running:
cds add hana, mta

Note: you can use cds add hana, mta --force to override existing MTA file.

In the mta.yaml file you can see that the new HANA_API_Service_Test-srv module requires hdi_db and HANA_API_Service_Test-db. Both refer to the same hdi_db resource. Just keep the original one and remove the other one (below is commented). The resulting mta.yaml file is

> mta.yaml
_schema-version: "2.1"
ID: HANA_API_Service_Test
version: 0.0.1
modules:
- name: HANA_API_Service_Test-db-deployer
type: hdb
path: db
requires:
- name: hdi_db
properties:
TARGET_CONTAINER: ~{hdi-container-name}
parameters:
buildpack: nodejs_buildpack
- name: HANA_API_Service_Test-srv
type: nodejs
requires:
# - name: HANA_API_Service_Test-db
- name: hdi_db
resources:
- name: hdi_db
type: com.sap.xs.hdi-container
parameters:
config:
schema: HANA_API_SERVICE_TEST
service: hana
service-plan: hdi-shared
properties:
hdi-container-name: ${service-name}

The file package.json should include:

"cds": {
"requires": {
"db": {
"kind": "hana-cloud"
}
}
}


After that, missing dependencies are installed by running:
npm install

Create Services


Now that we have our table and procedures, it is time to define the OData services, one for each procedure. For this we can use functions or actions, which are called through GET or POST requests respectively. To demonstrate both options an action and a function will be implemented for each procedure.

Services definition


The service definition is in a cds file within the srv folder. In this example it will be named catalog-srv.cds, and defined as follows:

srv > catalog-srv.cds
service CatalogService {

function fun_register_call() returns Boolean;

function fun_register_call_with_param(comment: String, name: String, value: Integer) returns Boolean;

action act_register_call() returns Boolean;

action act_register_call_with_param(comment: String, name: String, value: Integer) returns Boolean;
}

Services Implementation


Those definitions need to be implemented in Javascript. The easiest way is to create another file in the same folder, with the same name and js as extension:

srv > catalog-srv.js
const cds = require('@sap/cds')
module.exports = cds.service.impl(function () {
this.on('fun_register_call', async () => {
try {
let dbQuery = ' Call "REGISTER_CALL"( )'
let result = await cds.run(dbQuery, {})
console.log(result)
return true
} catch (error) {
console.error(error)
return false
}
})

this.on('fun_register_call_with_param', async (req) => {
try {
const { comment, name, value } = req.data;
let dbQuery = `Call "REGISTER_CALL_WITH_PARAM"("COMMENT" => '${comment}', "NAME" => '${name}', "VAL" => ${value} )`;
let result = await cds.run(dbQuery, {})
console.log(result)
return true
} catch (error) {
console.error(error)
return false
}
})

this.on('act_register_call', async () => {
try {
let dbQuery = ' Call "REGISTER_CALL"( )'
let result = await cds.run(dbQuery, {})
console.log(result)
return true
} catch (error) {
console.error(error)
return false
}
})

this.on('act_register_call_with_param', async (req) => {
try {
const { comment, name, value } = req.data;
let dbQuery = `Call "REGISTER_CALL_WITH_PARAM"("COMMENT" => '${comment}', "NAME" => '${name}', "VAL" => ${value} )`;
let result = await cds.run(dbQuery, {})
console.log(result)
return true
} catch (error) {
console.error(error)
return false
}
})

})

To build the services, run
cds build

More information can be found in:

Grant HDI access to CAP


The HANA stored procedures are in an HDI container and the CAP services outside. Therefore, the next step is to grant CAP with access to HDI.

The .env file in the db folder contains the connection details to the SAP HANA Cloud instance. The properties “instance_name” and “service_key_name” are needed to replace below to bind CAP with HDI. In the terminal run:

Bind CAP with HDI by running:

cds bind -2 <instance_name>:<service_key_name>
cds bind -2 <instance_name>:<service_key_name>

Install any dependent modules by running:
npm install

More information:

Initial test


To test the service run:
cds watch --profile hybrid

A small dialog will pop up to open a new tab in the browser:


sap/cds Server


By clicking on $metadata a list of all services will be displayed:



To test the API just replace $metadata by the service function. For example:

… /catalog/fun_register_call()


API call through function (GET request)


Or with parameters:

… /catalog/fun_register_call_with_param(comment='test',name='Hernan',value=1)


API call through function with parameters


By going to the Database Explorer (you can find it on the left toolbar as marked with the arrow in the screenshot), select the HDI container (SAP_API_Service_Test-hdi…) and open the data of the table.


Database Explorer



Add routing configuration


This step is required to establish the authentication.

The application router is used to provide the application with a single entre point to all different microservices. In the terminal run:
cds add approuter

Content will be generated in the folder named app. All dependencies should be installed inside that folder by running:
cd app
npm install

By default, user-authentication will be added to the file xs-app.json (It can be removed by modifying the authenticationMethod to none😞

app > xs-app.json
{
"authenticationMethod": "route",
"routes": [
{
"source": "^/app/(.*)$",
"target": "$1",
"localDir": ".",
"authenticationType": "xsuaa",
"cacheControl": "no-cache, no-store, must-revalidate"
},
{
"source": "^/(.*)$",
"target": "$1",
"destination": "srv-api",
"authenticationType": "xsuaa",
"csrfProtection": true
}
]
}

The mta.yaml file will be updated as:
_schema-version: "2.1"
ID: HANA_API_Service_Test
version: 0.0.1
modules:
- name: HANA_API_Service_Test-db-deployer
type: hdb
path: db
requires:
- name: hdi_db
properties:
TARGET_CONTAINER: ~{hdi-container-name}
parameters:
buildpack: nodejs_buildpack
- name: HANA_API_Service_Test-srv
type: nodejs
requires:
# - name: HANA_API_Service_Test-db
- name: hdi_db
- name: HANA_API_Service_Test-auth
- name: HANA_API_Service_Test
type: approuter.nodejs
path: app # from cds.env.folders. Consider also cds.env.build.target -> gen/app
parameters:
keep-existing-routes: true
disk-quota: 256M
memory: 256M
requires:
- name: srv-api
group: destinations
properties:
name: srv-api # must be used in xs-app.json as well
url: ~{srv-url}
forwardAuthToken: true
- name: HANA_API_Service_Test-auth
resources:
- name: hdi_db
type: com.sap.xs.hdi-container
parameters:
config:
schema: HANA_API_SERVICE_TEST
service: hana
service-plan: hdi-shared
properties:
hdi-container-name: ${service-name}
- name: HANA_API_Service_Test-auth
type: org.cloudfoundry.managed-service
parameters:
service: xsuaa
service-plan: application
path: ./xs-security.json
config:
xsappname: HANA_API_Service_Test-${org}-${space}
tenant-mode: dedicated

However, some adjustments might be needed. The srv-api property required in the HANA_API_Service_Test module is not defined, this issue can be solved by adding a "provides" section to the HANA_API_Service_Test-srv module. The path to the HANA_API_Service_Test-srv is missing, but can be simply added it, usually as gen/srv. The adjusted mta.yaml file is:

> mta.yaml
_schema-version: "2.1"
ID: HANA_API_Service_Test
version: 0.0.1
modules:
- name: HANA_API_Service_Test-db-deployer
type: hdb
path: db
requires:
- name: hdi_db
properties:
TARGET_CONTAINER: ~{hdi-container-name}
parameters:
buildpack: nodejs_buildpack
- name: HANA_API_Service_Test-srv
type: nodejs
path: gen/srv
provides:
- name: srv-api # required by consumers of CAP services (e.g. approuter)
properties:
srv-url: ${default-url}
requires:
# - name: HANA_API_Service_Test-db
- name: hdi_db
- name: HANA_API_Service_Test-auth
- name: HANA_API_Service_Test
type: approuter.nodejs
path: app # from cds.env.folders. Consider also cds.env.build.target -> gen/app
parameters:
keep-existing-routes: true
disk-quota: 256M
memory: 256M
requires:
- name: srv-api
group: destinations
properties:
name: srv-api # must be used in xs-app.json as well
url: ~{srv-url}
forwardAuthToken: true
- name: HANA_API_Service_Test-auth
resources:
- name: hdi_db
type: com.sap.xs.hdi-container
parameters:
config:
schema: HANA_API_SERVICE_TEST
service: hana
service-plan: hdi-shared
properties:
hdi-container-name: ${service-name}
- name: HANA_API_Service_Test-auth
type: org.cloudfoundry.managed-service
parameters:
service: xsuaa
service-plan: application
path: ./xs-security.json
config:
xsappname: HANA_API_Service_Test-${org}-${space}
tenant-mode: dedicated

More information:

Add User Authentication


I will add a simple user authentication with OAuth 2.0 using the User Account and Authorization (UAA) service, following the blog Add User Authentication to Your Application (SAP HANA Cloud).

Add authentication to the services


Through annotations we can restrict access or require authentication in the service definition. In this demonstration I will simply require user authentication for each of the procedures. For that I will add @requires: 'authenticated-user' before each of the services. The resulting service definition file is:

srv > catalog-srv.cds
service CatalogService {

@requires: 'authenticated-user'
function fun_register_call() returns Boolean;

@requires: 'authenticated-user'
function fun_register_call_with_param(comment: String, name: String, value: Integer) returns Boolean;

@requires: 'authenticated-user'
action act_register_call() returns Boolean;

@requires: 'authenticated-user'
action act_register_call_with_param(comment: String, name: String, value: Integer) returns Boolean;

}

Adjust Security Descriptor File


The xs-security.json needs to be adjusted with the required security description. Check the current file content.

A sample xs-security.json can be generated by running (in the project root folder):
cd ..
cds compile srv/ --to xsuaa > xs-security.json

However, additional configuration is needed to test the security setup in SAP BAS.

> xs-security.json
{  
"xsappname": "HANA_API_Service_Test",
"tenant-mode": "dedicated",
"scopes": [
{
"name": "$XSAPPNAME.Admin",
"description": "Admin"
}
],
"attributes": [],
"role-templates": [
{
"name": "Admin",
"description": "generated",
"scope-references": [
"$XSAPPNAME.Admin"
],
"attribute-references": []
}
],
"oauth2-configuration": {
"credential-types": [
"binding-secret",
"x509"
],
"redirect-uris": [
"https://*.applicationstudio.cloud.sap/**"
]
}
}

Create XSUAA Service Instance and Service Key


Next, XSUAA service needs to be created in Cloud Foundry, by running in the terminal:
cf create-service xsuaa application HANA_API_Service_Test-auth -c xs-security.json

A service key is needed to give the XSUAA instance access to the credentials. For that run
cf create-service-key HANA_API_Service_Test-auth default

Bind the CAP application to the authentication service by running:
cds bind -2 HANA_API_Service_Test-auth:default

Create and grant roles for the application


In the SAP BTP Cockpit, at subaccount level, create a role collection. I named it HANA_API_Service_Test.

Edit the role collection and add a role.


BTP Cockpit: Role Collection


Add and Save the role.

From the Rolle Collection assign the roles to your user.

Adjust Application Router


Open the xs-app.json file. We will add the logout section and, within routes, the Application Application Router User API for testing the UAA connection.

app > xs-app.json
{
"authenticationMethod": "route",
"logout": {
"logoutEndpoint": "/app-logout",
"logoutPage": "/"
},
"routes": [
{
"source": "^/app/(.*)$",
"target": "$1",
"localDir": ".",
"cacheControl": "no-cache, no-store, must-revalidate",
"authenticationType": "xsuaa"
},
{
"source": "^/(.*)$",
"target": "$1",
"destination": "srv-api",
"csrfProtection": true,
"authenticationType": "xsuaa"
},
{
"source": "^/user-api(.*)",
"target": "$1",
"service": "sap-approuter-userapi"
}
]
}

Install some required dependencies in the root and in the gen/srv folder
Npm install passport
npm i @sap/xssec
Npm install

cd gen/srv
npm install passport
npm i @sap/xssec
npm install

cd ../..

Test User Authentication


If I try to test the APIs again with
cds watch –profile hybrid

and make call to the API from the URL, like

… /catalog/fun_register_call_with_param(comment='test_uaa',name='Hernan',value=1)


Authentication is required


As it can be seen, authentication is required.

Now, let’s run it through the Application Router. For that, open a second terminal without closing the CAP service, and run
cds bind --exec -- npm start --prefix app

The port is different (5000), which corresponds to the Application Router.

With the same call as before the result is:

… /catalog/fun_register_call_with_param(comment='test_uaa',name='Hernan',value=1)


Call via Application Router


More information:

Deployment


Before the deployment, some adjustments are needed.

In the xs-security.json file remove the oauth2-authorization section whose purpose was just to allow testing with SAP Business Application Studio.

> xs-security.json
{  
"xsappname": "HANA_API_Service_Test",
"tenant-mode": "dedicated",
"scopes": [
{
"name": "$XSAPPNAME.Admin",
"description": "Admin"
}
],
"attributes": [],
"role-templates": [
{
"name": "Admin",
"description": "generated",
"scope-references": [
"$XSAPPNAME.Admin"
],
"attribute-references": []
}
]
}

The rest of the adjustment will be in the mta.yaml file.

  • The default-env.json and .env files are used to deploy and test from SAP Business Application Studio, but should not be in the final deployment. They can be ignored them by adding build-parameters to the db

  • Also rename the xsappname from HANA_API_Service_Test-${org}-${space} to HANA_API_Service_Test


After deploying, new application for the db module will be created, as well as a service for the hdi_db resource. In order to avoid those generic names, I will do the following changes:

  • Rename db module name to HANA_API_Service_Test-db-deployer

  • Rename hdi_db resource to HANA_API_Service_Test-db.

  • Of course, all references to that resource should be renamed as well

  • Rename hdi-container-name to HANA_API_Service_Test-db


> mta.yaml
_schema-version: "2.1"
ID: HANA_API_Service_Test
version: 0.0.1
modules:
- name: HANA_API_Service_Test-db-deployer
type: hdb
path: db
requires:
- name: HANA_API_Service_Test-db
properties:
TARGET_CONTAINER: ~{hdi-container-name}
parameters:
buildpack: nodejs_buildpack
build-parameters:
ignore: ["default-env.json", ".env"]
- name: HANA_API_Service_Test-srv
type: nodejs
path: gen/srv
provides:
- name: srv-api # required by consumers of CAP services (e.g. approuter)
properties:
srv-url: ${default-url}
requires:
- name: HANA_API_Service_Test-db
# - name: hdi_db
- name: HANA_API_Service_Test-auth
- name: HANA_API_Service_Test
type: approuter.nodejs
path: app # from cds.env.folders. Consider also cds.env.build.target -> gen/app
parameters:
keep-existing-routes: true
disk-quota: 256M
memory: 256M
requires:
- name: srv-api
group: destinations
properties:
name: srv-api # must be used in xs-app.json as well
url: ~{srv-url}
forwardAuthToken: true
- name: HANA_API_Service_Test-auth
resources:
- name: HANA_API_Service_Test-db
type: com.sap.xs.hdi-container
parameters:
config:
schema: HANA_API_SERVICE_TEST
service: hana
service-plan: hdi-shared
properties:
# hdi-container-name: ${service-name}
hdi-container-name: HANA_API_Service_Test-db
- name: HANA_API_Service_Test-auth
type: org.cloudfoundry.managed-service
parameters:
service: xsuaa
service-plan: application
path: ./xs-security.json
config:
# xsappname: HANA_API_Service_Test-${org}-${space}
xsappname: HANA_API_Service_Test
tenant-mode: dedicated

 

To deploy the application, we need first to package the project for deployment. In the terminal run:
mbt build

The deployment of the application to BTP Cloud Foundry runtime is done by right clicking on the mtar file created in mta_archives folder, and select “Deploy MTA archive”. Alternative run cf deploy from the terminal

More information:

Testing


The API can be tested with, for example, with Postman. The credentials can be found at the service definition in the BTP Cockpit. More information in the the blog Using Postman for API Testing with XSUAA by iobert.

Here is an example using the POST API (action):


Postman example - POST request



Summary


This blog post showcase an example on how to create a GET or POST API to trigger a stored procedure in SAP HANA Cloud, using SAP Cloud Application Programming (CAP) model, SAP Business Application Studio and Cloud Foundry for hosting the service. For a deep-dive on these topics I suggest to go through the tutorials and blogs links mentioned above.

This a simple way to expose functions and procedures via APIs to the outside world. For example, a function or procedure can be used to apply a Machine Learning model using HANA PAL or APL as described in the blog Embedding HANA machine learning models into SAP Data Warehouse Cloud views, or to leverage the HANA Graph engine to call out-of-the-box or custom algorithms as described in the blog Create graphs on SAP HANA Cloud.

I want to thank ian.henry, remi.astier and stojanm for sharing their experience and providing support related to this blog post!
7 Comments