Friday, 14 September 2018

Build database objects/services in HANA service using SAP WebIDE Full-Stack : Part 1

I walked through the steps to install and configure the HANA service on SAP Cloud Platform Cloud Foundry environment, In this blog series, I will focus on how to get started with building database objects using SAP WebIDE Full-Stack service. One of the main advantages of using SAP WebIDE is that you can now build a full-stack application (Database object, services with business logic and UI components etc). In this blog series, I am going to focus on how to create database objects and expose them as XSOData services. I have come across several requirements where data needs to be stored in SAP HANA service and needs to be exposed for other cloud platform services to consume. Hence, I will start by showing how to create DB artefacts and end by showing how to expose contents of a table using an OData service.

Setup WebIDE Full-Stack service


Enable and launch WebIDE Full-Stack serivce in Neo environment.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

In the preferences, enable the below features

◉ SAP HANA Database Development Tools
◉ SAP HANA Database Explorer
◉ Tools for node.js Development

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

In the Cloud Foundry settings (under Workspace preference), you will need to provide your Cloud Foundry API Endpoint, Organization and space. You can find this info in your global subaccount – Overview menu.

After providing the details, click on the “Install builder”. This is required to enable you to build applications later in this blog.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

This should create a builder application in the respective Cloud Foundry space as shown below

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Create a Database Module


From WebIDE, create a new project using the template.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Select “Multi-Target Application”

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Give a name for the project such as “ProductsApp01” and accept the default to create the project.

Once your project is created, select the folder and use the context menu to navigate to “Project Settings”.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Select the API Endpoint, Organization and Space in the Cloud Foundry section and save the changes

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

You are now ready to start creating the DB artefacts.

Select the project folder and use the context menu to create a new “SAP HANA Database Module”.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Give it a name as “products01_db”.

Remove the project name from the namespace and select the DB version. In my example, I am using 2.0 SPS 03

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Once the project is created, select the “src” folder and use the context menu to create a new “HDB CDS Artifact”

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Provide the name as “catalog.hdbcds”. Double click on the catalog table shown below.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

This will take us to a blank screen where we can create entities and provide relationships between them. For this blog, we are going to create a simple product entity with set of attributes. On the left hand corner, select the “create entity” button and place the entity in the middle of the screen. Change the name to “product” as shown below.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Double click on the entity to navigate to the elements section

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Create 6 elements as shown below with Id as “Key”.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Save your changes. Open the mta.yaml file using the code editor and update the resources section by adding parameters to it.

resources:
 - name: hdi_products01_db
   parameters:
      config:
        schema: product01
        database_id: 72b80a42-ea9d-477a-8722-9f8aa5281a3b
   properties:
      hdi-container-name: ${service-name}
   type: com.sap.xs.hdi-container

Notice that the schema can be any name. The database ID can be obtained from the HANA service dashboard as shown below.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

After saving the changes, your yaml file should look like below.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Select the folder “products01_db” and use the context menu to build the artefacts

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

You should be able to see a success message in the log console.

Switch to the Database explorer from the left hand pane.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Click on the “Connect” button to establish a connection with the database.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

When you are in the Database Explorer, click on the “+” sign to add database.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Search for “products” and select the HDI container which you have created.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Navigate to the Tables and select it. This will load all the tables in the bottom left hand corner. Select the product table and view its definitions/schema details. You can also view the contents of the table. It will be empty as we have not yet loaded data into it.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Open the SQL console from here and also look up the schemas which you have created as shown below.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Next, lets prepare a CSV file to upload data into the table. Select the “src” folder and use the context menu to create a new “Database Artifact”

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Provide the name as “sampledata” and select file type as “CSV”

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Provide sample values into the editor. Notice the header is retained at the top.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Similarly, create another database artefact of type hdbtabledata. Give it a name “uploadProducts”.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Paste the below contents into the file and save your changes. In the below file, we are referring to the csv file and providing the target table to upload the data. Notice that the object names are case sensitive.

{
    "format_version": 1,
    "imports": [
        {
            "target_table": "products01_db::catalog.product",
            "source_data": {
                "data_type": "CSV",
                "file_name": "products01_db::sampledata.csv",
                "has_header": true
            },
            "import_settings": {
                "import_columns": [
                    "Id","name","supplierName","category","unitPrice","unitsInStock"
                ]
            }
        }
    ]
}     

Below is how your file should look like:

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

Now you are ready to build the files. Select the folder “ProductsApp01” and use the context menu to Build the database module.

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides

If the build is successful you should see a success message in the logs.

Navigate to the database explorer to view the contents of the table

SAP HANA Database, SAP HANA WebIDE, SAP HANA Study Materials, SAP HANA Guides