Technical Articles
Steps to create a HDI container type DB using SAP WEB IDE in the Cloud Foundry
Hi Autodidact,
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:-
- SAP Cloud platform Cloud Foundry account.
- SAP Web Ide Full stack service enabled.
Skills:-
- Basic knowledge of Cloud Foundry and SAP Cloud Platform.
- 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.
Congratulations!!, Now your HDI type DB to ready in Cloud Foundry. You can use this further in UI or any other modules with the help of Odata service if required.
Hi,
In SAP Cloud Foundry i have two services i.e SAP HANA Schemas & HDI Container and SAP Hana Service as per your blog you are using the service SAP HANA Schemas & HDI Container but my question if already i have table and data in SAP Hana Service and how to get those data in Webide by creating Multi Target project.
In the Documentation of the Service SAP HANA schemas & HDI containers it is mentioned that The SAP HANA schemas & HDI containers service is part of the SAP HANA service.
But my question is that i already have table and data in SAP Hana Service and how to get those data in Webide by creating Multi Target project and we can data from the table in the SAP HANA service through SAP HANA schemas & HDI containers?
Hi Durga,
FYR,
https://developers.sap.com/tutorials/haas-dm-create-db-mta.html
Regards,
Sushma
Hi Sushma,
my question is in the same line as Durga's. I have a HANA Service (service: hana-db, plan: enterprise) and I would like that my HDI containers are created inside this service. Since it's a HANA 2.0 database, it should support the creation of HDI containers inside it.
Is this possible?
In the tutorials you pasted above (https://developers.sap.com/tutorials/haas-dm-create-db-mta.html), it tells to use the HDI service (service: hana, plan: hdi-shared) and then replicate the data to the HAAS database.
Thanks,
Lucas
Hi Lucas, HDI container schema is always inside the HANA instance which is associated with your sub-account. But HDI containers are isolated to your HANA database schema. if you wanted to access HANA db objects inside Webi IDE HANA for HDI development, you will need to create an user provided service and then create synonyms.
So the process explained the developer tutorials is, Extract or replicate the data into HANA database, then access that in HDI containers using synonyms. It does not have to be in the same way, you can directly load or replicate the data into HDI container schema objects and use them as local objects.
Hi Sreekanth,
The problem I had is that my account only had entitlement for the hana-db service. The hdi-shared was not enabled.
Once I managed to enable hdi-shared, the HDI containers were created inside my HANA database provided by the hana-db service.
In short, to use HDI Containers in a hana-db service it's also needed the hdi-shared service.
Hello Lucas,
How you handled and were able to create HDI containers inside your HaaS? Would you mind sharing the how-to?
Thank you very much in advanced.
Hi Sushma Kudum,
Nice blog.
I am facing an error. Could you please tell me why that error is occurring. I have done same as your blog. But while building i am getting following error.
Hi Loganathan,
Are you using Trial Account?
Hi Takao Baltazar,
Yes i am using Trial account.
Hi Sushma Kudum,
Thanks for your Great Blog on how to create HDI Container type DB in SAP Web IDE of Cloud Foundry.
I tried to follow your steps, and during the process of creating "SAP Hana Database Module" in SAP Web IDE Full stack service of an MTA project template, I encountered an error stating : "There is no database available. Ensure that you have a database available in space 'dev' within organization 'cde6cedatrial'"
Full Details of error:
I am using a trial account in SAP Cloud Platform of Cloud Foundry.
Thanks,
Takao
My Issue was resolved by switching from EU to US region of Cloud Foundry Trial account, and creating a SAP Hana Cloud database.