Sunday, 17 February 2019

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

Enter Basic details

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

Enter basic details.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

{

"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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

{

"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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

"E100","desc1","Arun"
"E101","desc2","Anand"
"E102","desc3","Ram"
"E103","desc4","Ananya"

Create roleEnrollments.csv

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

"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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

Check for instance created.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

Build Odatajs module with cloud Foundry setting to the project.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

Select the container and click on OK.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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.

SAP WEB IDE, SAP HANA Study Materials, SAP HANA Guides, SAP HANA Certifications

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

No comments:

Post a Comment