How to create an API to run a HANA Cloud Stored Procedure in Cloud Foundry

[gonzalo_hernan_sendra]

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:

• Tutorials: Add SAP HANA Cloud Native Artifacts to a CAP Application.


• Blog: SAP HANA Development with SAP Cloud Application Programming Model using SAP Business Application Stu... by shraddhashetty

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:

• Tutorial: Create HANA Stored Procedure and Expose as CAP Service Function (SAP HANA Cloud).


• Documentation: Custom Actions & Functions

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:

• Tutorial: Create a User Interface with CAP (SAP HANA Cloud).

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:

• Tutorial: Create a User Interface with CAP (SAP HANA Cloud)

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:

• SAP Authorization and Trust Management service for SAP BTP in the Cloud Foundry environment: What is the SAP Authorization and Trust Management Service


• Tutorial: Add User Authentication to Your Application (SAP HANA Cloud)

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:

• Tutorial: Deploy CAP with SAP HANA Cloud project as MTA.

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!