Steps to create a HDI container type DB using SAP WEB IDE in the Cloud Foundry

This blog is for enthusiastic people who want to learn to create HDI container type Database using SAP WEB IDE and deploy the same in Cloud Foundry and can use SAP WEB IDE to perform DML/DDL operations on DB.

We will go through step by step processes to achieve the same.

Pre-requisites required:-

Accounts and infrastructure:-

1. SAP Cloud platform Cloud Foundry account.
2. SAP Web Ide Full stack service enabled.

Skills:-

1. Basic knowledge of Cloud Foundry and SAP Cloud Platform.
2. Basic knowledge of SQL.

Let’s start:

Login into Cockpit and navigate to your trial account and then to services and then open SAP WEB IDE full stack.

Click on SAP Web IDE Full -Stack grid and then “Go to Service” link.

It will navigate to SAP web ide in that select my workbench which will navigate you to editor page where you start creating a project as below.

Create an MTAR project from the project template

Enter Basic details

It will create a project folder with the mta.yaml file.

Create a DB module inside the project. as shown below.

We are trying to create a DB for storing employee and their role details.

Enter basic details.

This will create a folder named Employee, Now create a new file named cdsAtrifcat, under src as mentioned below.

namespace FirstFullStackApp.Employee;

context cdsArtifact {

    /*@@layout{"layoutInfo":{"x":32,"y":121.5}}*/

    entity employeeDetails {

        key EMPLOYEE_ID      : String(10) not null;

            DESCRIPTION : String(50) not null;

            DEPARTMENT  : String(20);

            EMPLOYEE_NAME  : String(50) not null;

            association : association[1, 1..*] to cdsArtifact.roleEnrollments { EMPLOYEE_ID };

    };

    /*@@layout{"layoutInfo":{"x":-444,"y":105.5}}*/

    entity roleEnrollments {

        key EMPLOYEE_ID     : String(20) not null;

        key ROLE_ID  : String(20) not null;

            ROLE_NAME : String(20) not null;

            EMPLOYEE_NAME  : String(50) not null;

            EMAIL      : String(40) not null;

            LOCATION   : String(20);

    };

};

Create a new file named employeeDetails.hdbtabledata

{

"format_version": 1,

"imports":

[ {

"target_table" : "FirstFullStackApp.Employee::cdsArtifact.employeeDetails",

"source_data" : { "data_type" : "CSV", "file_name" : "FirstFullStackApp.Employee::employeeDetails.csv", "has_header" : false },

"import_settings" : { "import_columns" : ["EMPLOYEE_ID","DESCRIPTION","EMPLOYEE_NAME" ] },

"column_mappings" : {"EMPLOYEE_ID" : 1,"DESCRIPTION" : 2,"EMPLOYEE_NAME" : 3}

}

]

}

Create a file named roleEnrollments.hdbtabledata

{

"format_version": 1,

"imports":

[ {

"target_table" : "FirstFullStackApp.Employee::cdsArtifact.roleEnrollments",

"source_data" : { "data_type" : "CSV", "file_name" : "FirstFullStackApp.Employee::roleEnrollments.csv", "has_header" : false },

"import_settings" : { "import_columns" : ["EMPLOYEE_ID", "ROLE_ID", "ROLE_NAME","EMPLOYEE_NAME","EMAIL"] },

"column_mappings" : {"EMPLOYEE_ID": 1, "ROLE_ID" : 2, "ROLE_NAME" : 3,"EMPLOYEE_NAME" :4,"EMAIL" :5}

}

]

}

Now for above-created tables, we will create data by using CSV file as shown below under the src folder.

Create employeeDetails.csv

"E100","desc1","Arun"

"E101","desc2","Anand"

"E102","desc3","Ram"

"E103","desc4","Ananya"

Create roleEnrollments.csv

"E100","J5136","JavaCodes","Arun","arun.rage@sap.com"

"E101","J5137","BusinessAnaylist","Anand","anand.kista@sap.com"

"E102","J5138","Designer","Ram","ram.raja@sap.com"

"E103","J5139","Fresher","Ananya",”ananya.sudheer@sap.com”

Folder structure will look like this

Right click on Project (FirstFullStackApp) and navigate to project setting to install cloud Foundry builder as shown below. This will help us to build and deploy our DB module to Cloud Foundry further.

Click on “install builder” button

Note: If you find default settings are not present or not as per your cloud Foundry trial account in the cockpit, then from your trail account space you can get the required API endpoint details and paste the same here and click on build installer button.

Once the builder is installed successfully click on the save button.

Now right click on Employee (DB module folder) and build it as shown below.

Once the build is successful, In cockpit->cloud foundry-> space check for the deployed DB module

Check for instance created.

Now your HDI container type DB is ready in Cloud Foundry. We can access it in another module if required by using data service.

Below are the steps to create OData service.

Create a Nodejs module.

Create service.xsodata under lib/xsodata/service.xsodata

service  

{

"FirstFullStackApp.Employee::cdsArtifact.employeeDetails" as "employeeDetails" navigates ("role_Enrollments" as "roleEnrollments");

"FirstFullStackApp.Employee::cdsArtifact.roleEnrollments" as "roleEnrollments" ;

association "role_Enrollments" principal "employeeDetails"("EMPLOYEE_ID") 

multiplicity "1" dependent "roleEnrollments"("EMPLOYEE_ID") multiplicity "*";

}

In MTA.yaml file add HDI as required for Odatajs

Build Odatajs module with cloud Foundry setting to the project.

Once build is successful then Run the application as Nodejs Application.

Once the application started successfully, we can check the metadata.

replace index.js in the URL from the browser it opened after running successfully, with  /xsodata/service.xsodata/$metadata.

By using this URL we can create “Destination” in cloud Foundry which can be used further in other modules.

if you are not willing to use OData service then you can skip the step of the creation of Odata module itself and directly navigate to ” Accessing DB explorer ” and check for tables as shown below.

Accessing DB from SAP WEB IDE.

This will add DB explorer to you SAP Web IDE. Now you can see DB icon on the left side of the screen.

Click on the icon–> then click on “+” symbol where can add your DB.

Select the container and click on OK.

Onclick of tables in this list will list down all the tables available. Right click on the respective table and open data will display the data present inside tables.

Top right corner “SQL” image “on click” will open an editor where you can execute SQL queries.