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.
SAP Business Application Studio (BAS) will be used as development environment, in which the development space should include the following extensions:
Once in the development space in BAS, I will create a 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:
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.
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
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
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
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:
We recommend to do a backup copy of the project before continuing.
For CDS initialization, including NodeJS, in the terminal run:
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:
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
The file package.json should include:
After that, missing dependencies are installed by running:
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.
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
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
To build the services, run
More information can be found in:
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>
Install any dependent modules by running:
More information:
To test the service run:
A small dialog will pop up to open a new tab in the browser:
To test the API just replace $metadata by the service function. For example:
Or with parameters:
… /catalog/fun_register_call_with_param(comment='test',name='Hernan',value=1)
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.
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:
Content will be generated in the folder named app. All dependencies should be installed inside that folder by running:
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
The mta.yaml file will be updated as:
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
More information:
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).
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
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):
However, additional configuration is needed to test the security setup in SAP BAS.
> xs-security.json
Next, XSUAA service needs to be created in Cloud Foundry, by running in the terminal:
A service key is needed to give the XSUAA instance access to the credentials. For that run
Bind the CAP application to the authentication service by running:
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.
Add and Save the role.
From the Rolle Collection assign the roles to your user.
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
Install some required dependencies in the root and in the gen/srv folder
If I try to test the APIs again with
and make call to the API from the URL, like
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
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)
More information:
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
The rest of the adjustment will be in the mta.yaml file.
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:
> mta.yaml
To deploy the application, we need first to package the project for deployment. In the terminal run:
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:
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):
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!
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 nodejsAs 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, mtaNote: 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 installCreate 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 buildMore 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 installMore information:
Initial test
To test the service run:
cds watch --profile hybridA small dialog will pop up to open a new tab in the browser:
sap/cds Server
To test the API just replace $metadata by the service function. For example:
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 approuterContent will be generated in the folder named app. All dependencies should be installed inside that folder by running:
cd app
npm installBy 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.jsonHowever, 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.jsonA 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 defaultBind the CAP application to the authentication service by running:
cds bind -2 HANA_API_Service_Test-auth:defaultCreate 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 hybridand make call to the API from the URL, like
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 appThe 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 buildThe 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!
- SAP Managed Tags:
7 Comments
