Skip to Content
Technical Articles
Author's profile photo Maxime Simon

Use CAP to expose HANA Cloud tables as OData services

When you want to expose data residing into a HANA database to the outside world, the recommended best practice is to use OData.
Recently, SAP started promoting a new Cloud Application Programming model (CAP). The SAP Cloud Application Programming model is a framework of languages, libraries, and tools for building enterprise-grade services and applications. It guides developers along a ‘golden path’ of proven best practices and a great wealth of out-of-the-box solutions to recurring tasks.

CAP-based projects benefit from a primary focus on domain. Instead of delving into overly technical disciplines.

In this blog, I will use the SAP Cloud Application Programming Model to create tables on SAP HANA Cloud, and expose these tables as OData services.With this method, data is exposed using OData v4.0. As opposed to the traditional xsodata method, where data is exposed using OData v2.0.

References :
Building hana-opensap-cloud-2020
SAP Experience academy (SAP Internal)
CAP Getting started guide

Prerequisites

Get your development environment ready

When your HANA Cloud instance is set up, and you are ready to start, open the subscriptions in your subaccount and click on SAP Business Application Studio.

Create a new Dev space.

Select the SAP Cloud Business Application Template and provide a name for your Dev Space.

Wait until the status changes from Starting to Running, then click on the tile with the Dev Space name. In the background, the Dev Space has been prepared with all of the necessary components that you would otherwise have to install on your laptop. For example, Node JS, CDS, etc.

Create a CAP Project from a template

Now we have our Business Application Studio started, configured and ready for use. On the Welcome tab, click on “Create project from template”.

Select the @sap/cap Project template.

Check the hana box in order to include SAP HANA-related features in your project.

Behind the scenes, your project will be generated. Once complete, the screen will return and you will see a pop-up message box in the bottom right to open a workspace with your project. Click on the Open in New Workspace button.

The editor will reopen in a new workspace and now you can start creating.
Notice the blue bar at the bottom of your screen that the Space has not been set with Cloud Foundy.

Click on this bar to connect Business Application Studio to the space where you want to deploy your OData service.

 

Insert the Cloud foundry endpoint, then enter your credentials and select the space in which you want to work.

Create your database model

Business Application Studio is now connected to your Cloud Foundry space, let’s create objects in your database model.

From the File Structure on the left, right-click on the db folder and create a new file ending with .cds .
I call mine schema.cds . The name of the file can be anything, this file will define all objects(tables, views) deployed in your HANA database.

Within the new schema.cds, create your first CAP Structure :

namespace scp.cloud;

using {
  cuid,
  sap.common
} from '@sap/cds/common';

entity SafetyIncidents : cuid {
  title                  : String(50)                    @title : 'Title';
  description            : String(1000)                  @title : 'Description';
}

In this example, we are defining a namespace scp.cloud.
We then call the library @sap/cds/common and use the cuid aspect. It automatically defines an ID column for us in the entity SafetyIncidents. Learn more about aspects in the CAP documentation.
An entity defined in CAP will be deployed as a table in your database.

Open a terminal window by going to the Top Menu and selecting Terminal -> New Terminal.

Within your project folder, execute the command npm install

Now we will introduce you to a very useful command: cds watch .
Whenever you feed your project with new content, for example, by adding or modifying .cds , .json , or .js files, the server automatically restarts to serve the new content.

Execute cds watch in the terminal window.

As long as that command is running, each time you change the project structure, it will automatically save and redeploy those project changes.

After a few seconds, the cds watch command generates your OData service.
It also creates the table from schema.cds in an SQLite database within your development environment.
Click on the button Expose and Open to see if your initial empty service gets rendered in the browser window.

It is still empty now.

Expose an entity as an OData service

Now that we have an entity for SafetyIncidents defined, you can easily add a service definition to expose it as an OData service. Lets do that now! Create a new file within the srv folder called incidentService.cds

Within incidentService.cds, enter the following code:

using scp.cloud from '../db/schema';

service IncidentService {
    entity SafetyIncidents as projection on cloud.SafetyIncidents;
}

The first line references the schema.cds file we created earlier. The second line exposes cloud.SafetyIncidents as an OData service called Incident Service.

If you closed your preview tab, you can always re-open it by clicking on view: find commands, then searching for the command Ports: Preview . This will open a preview of the currently exposed ports.

Now let’s insert some data into our table. Start by creating a new folder called data within the db folder.

Within that folder, create a file called scp.cloud.SafetyIncidents.csv with the following entries:

ID;title;description
067460c5-196c-4783-9563-ede797399da8;Broken machine;The printing machine is leaking
efec3e9f-ceea-4d17-80a7-50073f71c322;Software bug;The computer is on fire

The file name has to match the name space (scp.cloud) and the entity name (SafetyIncidents) where you want to insert data.

Double check as shown in the screenshot that you have the right spelling of the data folder under the db folder and that the filename is spelt correctly. Make sure that the column names are correct in the actual csv file.

If the cds watch is still running, stop it once and execute cds run in the terminal to ensure data is imported into your SQLite table.

The message > filling scp.cloud.SafetyIncidents from db/data/scp.cloud.SafetyIncidents.csv  tells you that data is being imported.

Now that’s it running, you can open the service and click on the SafetyIncidents entry, you should see the following data:

You now have a table deployed on your SQLite database within your development environment, filled with some test data. This table is exposed through an OData service which can be accessed from outside through REST calls.

Deploy your data model and your OData service to SAP Cloud Platform

Now that you have the backend services running on SQLite in a local environment, it’s time to get this project running on SAP HANA Cloud.

Quick recap

  • A schema for the incident management application has been created schema.cds
  • A service definition has been added to expose the correct entities incidentService.cds
  • The SQLite node module let us run the application connected to SQLite with data loaded into a table

Prepare your project for HANA Cloud

On SAP HANA Cloud, CDS models are deployed through the hdbtable and hdbview formats instead of hdbcds. Edit your package.json to set the deploy-format to hdbtable.
Add the following line in the “cds” section of package.json.

"hana" : { "deploy-format": "hdbtable" }

Your code should be similar to this screenshot :

Build your project

Within the Node.JS world, there is an environment variable called NODE_ENV. Until now we have been using the “development” environment. It is time to switch that variable to “production”. It will affect the way that CDS behave. In order to deploy your project to SAP Cloud Platform, the following commands must be run from the Terminal window.

  1. Stop your running cds process with CTRL+C if it’s already running.
  2. Execute : export NODE_ENV=production
  3. After this command runs successfully, execute : cds build/all --clean

This command will build all of the relevant HANA artifacts and place them in a newly created folder that you should now see called gen. If you expland it, you should see 2 folders DB and SRV. As you might expect, if you drill into the DB folder, you will see the HANA DB artifacts and if you drill into the SRV, there are new files in there as well.

Create your HDI Container and deploy objects

Once the build process has completed, you will now execute 3 commands in succession in order to (1)create the HANA Deployment Infrastructure (HDI) container on Cloud Foundry, (2)deploy the HANA Artifacts and (3)deploy the SRV Artifacts.
Notice that in your terminal, the build process tells you which command you need to run in order to create the HDI container.

Execute the following command: cf create-service hana hdi-shared cap_project-db
(The creation of the container can take a few minutes, you should wait before executing the next step!)

This will create a HDI container called cap_project-db.

Note: On the screenshot below, I run the command cf create-service hanatrial hdi-shared cap_project-db: I am actually deploying on “HANA as a Service”.
Use the parameter hana to deploy on HANA Cloud.

Execute the following command: cf push -f gen/db -k 256M

This will deploy the generated  hdbtable and hdbview objects to your HDI Container.
The HDI container creation takes a couple of minutes,

Execute the following command: cf push -f gen/srv --random-route -k 320M

This will deploy the Node.JS application exposing your OData service.

If all three of the last commands executed correctly, you should see a route specified towards the bottom of the terminal window. The use of the word option --random-route directs the process to create a random URL.

Once you find the route name that was generated uniquely for you, you can paste that URL into a browser to validate that it is running and available on the internet.

Open a web browser, paste your newly created route and you should see a familiar screen that looks like this when you open your entity. This fully deployed service is now available on the internet and using SAP HANA Cloud as a persistence layer.

Good job, you just deployed an OData service on SAP HANA Cloud !

Expore further

If you want to build a more complex OData service, here are some ideas to start. I will use this service in my next blog where I create a Fiori UI to create an application where users can report Safety Incidents. Explore the entity definitions below and the CAP Documentation to learn how CAP makes your life easier.

Replace your schema.cds file with the following code:

namespace scp.cloud;

 using {
   cuid,
   managed,
   sap.common
 } from '@sap/cds/common';

 entity SafetyIncidents : cuid, managed {
   title                  : String(50)                   @title : 'Title';
   category               : Association to Category       @title : 'Category';
   priority               : Association to Priority       @title : 'Priority';
   incidentStatus         : Association to IncidentStatus @title : 'IncidentStatus';
   description            : String(1000)                  @title : 'Description';
   incidentResolutionDate : Date                          @title : 'ResolutionDate';
   assignedIndividual     : Association to Individual;
   incidentPhotos         : Association to many IncidentPhotos
                              on incidentPhotos.safetyIncident = $self;
   incidentHistory        : Association to many IncidentHistory 
                             on incidentHistory.safetyIncident = $self;
 }

 entity Individual : cuid, managed {
       firstName       : String @title : 'First Name';
       lastName        : String @title : 'Last Name';
       emailAddress    : String @title : 'Email Address';
       safetyIncidents : Association to many SafetyIncidents
                           on safetyIncidents.assignedIndividual = $self;
 }

 entity IncidentHistory : cuid, managed {
   oldStatus : Association to IncidentStatus @title : 'OldCategory';
   newStatus : Association to IncidentStatus @title : 'NewCategory';
   safetyIncident : Association to SafetyIncidents;
 }

 entity IncidentPhotos : cuid, managed {
   @Core.IsMediaType : true imageType  : String;
   @Core.MediaType   : ImageType image : LargeBinary;
   safetyIncident                      : Association to SafetyIncidents;
 }

 entity IncidentsCodeList : common.CodeList {
   key code : String(20);
 }

 entity Category : IncidentsCodeList {}
 entity Priority : IncidentsCodeList {}
 entity IncidentStatus : IncidentsCodeList {}

Replace your incidentService.cds with the following code:

using scp.cloud from '../db/schema';

service IncidentService {

entity SafetyIncidents as projection on cloud.SafetyIncidents {*,assignedIndividual: redirected to Individual };
entity Individual as projection on cloud.Individual {*,safetyIncidents : redirected to SafetyIncidents};
entity SafetyIncidentsNoImages as projection on cloud.SafetyIncidents{ID ,createdAt, priority, incidentStatus,description};
entity IncidentPhotos as projection on cloud.IncidentPhotos {*,safetyIncident : redirected to SafetyIncidents};
entity IncidentHistory as projection on cloud.IncidentHistory {*,safetyIncident : redirected to SafetyIncidents};
entity IncidentsByCategory as select from cloud.SafetyIncidents {count(ID) as categories:Integer,key category} Group By category;

@readonly entity Category as projection on cloud.Category;
@readonly entity Priority as projection on cloud.Priority;
}

Find here a few more advanced examples of using CAP to develop applications :

Maxime SIMON

Assigned Tags

      20 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Murali Shanmugham
      Murali Shanmugham

      Very useful. Thanks for sharing.

      Author's profile photo Thomas Jung
      Thomas Jung

      You said you are deploying this to HANA Cloud, but in your HANA HDI container instance creation you are using the hanatrial service:

      cf create-service hanatrial hdi-shared cap_service-db

      The hanatrial service is actually the older HANA As A Service offering not HANA Cloud.  Actually the hana service in the create-service command should be used for the HANA Cloud trial as well.  It is not just for production as you stated in this blog post.

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      Hello Thomas, thanks for the comment.

      As you said, I deployed my HDI container on HANA as a Service as I did not have a HANA Cloud instance running.
      I updated the blog to use the hana service in the create-service command, in order to stick to a HANA Cloud tutorial.

       

      Author's profile photo Helena Nacinovic
      Helena Nacinovic

      Hi Maxime,

      If you want to try it out with SAP HANA Cloud, you can sign up for trial. Alternatively, reach out to me and I can share more info.

      Author's profile photo Steven Spronk
      Steven Spronk

      Hi Maxime,

       

      Fantastic blog! It explains how to easily create a CAP based application with a HANA Cloud db. I have one remark:

      When creating the HDI service you say:

      Execute the following command: cf create-service hana hdi-shared cap_service-db

      While the name of the service should be cap_project-db

      You may want to change that.

      Thanks!

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      Thanks, I corrected the mistake

      Author's profile photo Juan Francisco Zurita Duque
      Juan Francisco Zurita Duque

      When executing the create-service command, it wouldn’t it create an HDI instance under the SAP Hana Service service and not SAP Hana Cloud?

      cf create-service hana hdi-shared cap_service-db

      We are on a migration scenario, so I want to make sure the container is binded SAP Hana Cloud service.

      SAP documentation indicated this pattern to be used:

      cf create-service hana hdi-shared SERVICE_INSTANCE -c '{"database_id":"hana-db_service_instance_guid"}'

      https://help.sap.com/viewer/db19c7071e5f4101837e23f06e576495/LATEST/en-US/2863434ddda042b8b8011a3f24856281.html

       

       

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      The -c parameter allows you to define the database ID if you have several databases running in the same space.

      Author's profile photo Srikanth Anthargam
      Srikanth Anthargam

      Hi Maxime,

      The Blog is really wonderful. Can you please share sample data (csv files for all tables) for Incident Example.

       

      Thanks

      a srikanth

      Author's profile photo Janith Illangakoon
      Janith Illangakoon

      How to expose cloud HANA db tables & CDS views as XSOData services

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      CDS views need to be exposed as OData services through the ABAP layer of S/4HANA.
      This blog does not cover S/4HANA.

      Author's profile photo Ali Asad
      Ali Asad

      This was very helpful 🙂 one question though, as we are in the process of implemeting something similiar. Just to have a clear idea about the possiblities after exposing the OData service, can it be consumed as a destination in Cloud foundry and used in another app created on BAS?

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      Yes. After you deploy your Odata service, it can be consumed from the destinations in Cloud foundry and from other apps.

      Author's profile photo Siddharth Satyam
      Siddharth Satyam

      Hi,

      I am trying to acquire data in SAP Analytics Cloud through OData Services. I have carried out all the required steps in this blog. I wanted to know about what authentication (Basic Authentication, OAuth, no authentication) will we require to fill when connecting it to SAC. Also, is the Data service URL the same as the random URL generated through --random-route ?

      This%20is%20the%20dialog%20box%20in%20SAC%20%3A

      This is the dialog box in SAC 

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      Hello, in this blog I did not cover about authentication. If you replicated my steps, authentication is not required to access your OData service.

      If you want to set up authentication, you need to use token-based authentication (XSUAA).

      Author's profile photo Siddharth Satyam
      Siddharth Satyam

      Hi, thank you for the reply. Could you please also tell if you have idea about the Data Service URL.
      Is it the same as the random URL generated through --random-route ?

      Is%20this%20the%20Data%20Service%20URL%20to%20be%20used%20%3F

      Is the "Application Routes" the Data Service URL to be used ?

      Author's profile photo Maxime Simon
      Maxime Simon
      Blog Post Author

      Yes, this is the correct. As explained in the blog in the "Create your HDI Container and deploy objects" section

      Author's profile photo Ruchi Dhiman
      Ruchi Dhiman

      Thank you for the blog post. All steps were successful for me except for deployment of tables and views.

      Author's profile photo Siddharth Satyam
      Siddharth Satyam

      Hi Ruchi,

      Could you please check if the HANA Database instance is running, in which your HDI container is deployed? I got the same error and it worked for me when I started my database instance.

      Author's profile photo Ruchi Dhiman
      Ruchi Dhiman

      My database is running. HDI container was created, however the tables/ views were not deployed.