Working with Cross HDI Container Access Scenarios in SAP HANA Cloud

Introduction

In SAP HANA Cloud, the database development artifacts are deployed from and to HDI containers. It is a common scenario in application development where the tables located in one HDI container have to be accessed from other HDI containers. For example, a specific application has tables and other database artifacts in an HDI container. If the some of these tables need to be used by another application where the database artifacts are built in a separate HDI container, this would require a cross HDI container access. Enabling cross container access would depend on whether the two HDI containers are located in same database instance/region or different. This blog and the subsequent blogs will cover the details of working with such cross HDI container access scenarios.

Prerequisites

Knowledge of creating a SAP HANA database project from SAP Business Application studio – Create an SAP HANA Database Project

Use Cases

The following scenarios are covered :

• HDI Containers located in the same SAP HANA Cloud Instance and Region
  1. Cross HDI container access using HDI service
  2. Cross HDI container access using User Provided Service. Case 2 blog link will be added soon
• HDI Containers located in different SAP HANA Cloud Instances in same/different Regions
  1. Cross HDI container access using virtual tables Case 3 blog link will be added soon

How to achieve the cross container access in the first use case((cross container access using HDI service) is detailed in this blog. For the other use cases(cross container access using UPS and virtual tables), the details can be found in the corresponding blog links provided above.

Use Case 1 – Cross HDI container access using HDI service for containers located within same SAP HANA Cloud Database Instance

Suppose the two HDI containers are located in the same SAP HANA Cloud database instance and an HDI container service has to be used for cross container access.

As in the picture above, this blog explains how to access the CS1TAB table in the HDI Container CS1HDIA from the calculation view CS1MYCV in the HDI Container CS1HDIB.

Here is the bird’s-eye view of the steps involved:
——————————————————————————————————————————–

1. In Business Application Studio(BAS) create new SAP HANA database project /HDI Container CS1HDIA
2. Create the following database artifacts in CS1HDIA/src
   • CS1TAB.hdbtable​
   • CS1TAB.hdbtabledata​
   • CS1TAB.csv​
   • CS1XHDIO.hdbrole – Role for object owner having schema and object privileges to access the table with grant option​
   • CS1XHDIA.hdbrole – Role for application user having schema and object privileges to access the table​
3. Build the project CS1HDIA and deploy​
4. Create a second database project/HDI Container CS1HDIB
5. Add the HDI service instance of the HDI container CS1HDIA under SAP HANA Projects -> CS1HDIB -> Database connections . This will get added as cross container service.​
6.  Create the following database artifacts in CS1HDIB/cfg:
   • SYNCS1TAB .hdbgrants file that uses the HDI service instance, the object owner and application roles created in CS1HDIA.​
   • SYNCS1TAB.hdbsynonymconfig file​
7.  Create the following database artifacts in CS1HDIB/src:
   •  SYNCS1TAB.hdbsynonym file to access the CS1TAB.hdbtable from HDI Container CS1HDIA.
   • CS1MYCV.hdbcalculation view that makes use of the above synonym ​
8. Build the Project CS1HDIB and deploy.
   —————————————————————————————————————————–

Details regarding each of the above steps follows below.

To begin with, make sure you have logged in to the correct Cloud Foundry Organization and Space.

Step 1: Create new SAP HANA database project /HDI Container CS1HDIA

Open BAS -> Create new project from template -> SAP HANA database project -> Start

In your mta.yaml file, you will see as follows:

Step 2: Create database artifacts in the  HDI container CS1HDIA/src

Create the following under CS1HDIA/src:

CS1TAB.hdbtable​
----------------------
column table "CS1TAB" (
   "SalesOrderId"  NVARCHAR(10)  NOT NULL   comment 'Sales Order ID',
   "ProductId"  NVARCHAR(10)  NOT NULL   comment 'Product ID',
   "Quantity"  INTEGER   comment 'Quantity',
   "DeliveryDate"  DATE     comment 'Scheduled Delivery Date',
    primary key  ( "SalesOrderId"))

CS1TAB.hdbtabledata​ :
-------------------
{
    "format_version": 1,
    "imports": [
      {
        "target_table": "CS1TAB",
        "source_data": {
          "data_type": "CSV",
          "file_name": "CS1TAB.csv",
          "has_header": false,
          "type_config": {
            "delimiter": ","
          }
        },
        "import_settings": {
          "import_columns": [
            "SalesOrderId",
            "ProductId",
            "Quantity",
            "DeliveryDate"  ],
          "include_filter": []
        },
        "column_mappings": {
          "SalesOrderId": 1,
          "ProductId": 2,
          "Quantity": 3,
          "DeliveryDate": 4
        }
      }
    ]
  }

CS1TAB.csv​
------------
SO0001,PR0001,111,20201225
SO0002,PR0002,222,20201119
SO0003,PR0001,100,20201018
SO0004,PR0004,333,20201018
SO0005,PR0001,99,20201119

CS1XHDIO.hdbrole
----------------
{
    "role": 
    {
    "name": "CS1XHDIO#",
    "schema_privileges": [
        {
            "privileges_with_grant_option": ["SELECT", "INSERT", "UPDATE", "EXECUTE", "DELETE", "SELECT METADATA"]
        }
                          ],
     "object_privileges": [
                             {
                                  "name": "CS1TAB",
                                  "type":"TABLE", 
                                  "privileges_with_grant_option": ["SELECT"]
                             }
                            ]
               }
}

CS1XHDIA.hdbrole
------------------
{
    "role":
    {
    "name": "CS1XHDIA",
    "schema_privileges": [
        {
            "privileges": ["SELECT", "INSERT", "UPDATE", "EXECUTE", "DELETE", "SELECT METADATA"]
        }
                          ],
    "object_privileges": [
                             {
                                  "name": "CS1TAB",
                                  "type":"TABLE",
                                  "privileges": ["SELECT"]
                             }
                            ]
               }
}

Step 3: Build the project CS1HDIA anddeploy​

Once deployed, the contents of CS1HDIA project will look as below:

Go to the DB explorer and check if select query on the table shows the data.

If you are wondering about the schema name being suffixed with ‘_1’ while the actual schema name provided during project creation is ‘CS1A_Schema’, here is the explanation. In this case, the project was built by clicking the ‘Deploy’ symbol(shown below) corresponding to the project under ‘SAP HANA Projects’. This adds ‘_1’ to the original schema name(CS1A_Schema) provided when it deploys the project. Hence the schema name is ‘CS1A_Schema_1’.

But if you deploy using mta.yaml file (mta.yaml file(right-click)  -> Build MTA project + Deploy MTA archive), the container created will have the original schema name provided(CS1A_Schema).

Depending upon how the project is deployed, make sure to use the relevant schema name within any code referring to the schema name, in the correct case(uppercase or lowercase or mixed). This can avoid a lot of errors.

Step 4: Create a second database project/HDI ContainerCS1HDIB

Mta.yaml will have the following:

Step 5: Add the HDI service instance to the database connection

Add the HDI service instance(CS1HDIASI) of the HDI container CS1HDIA under SAP HANA Projects -> CS1HDIB/CS1HDIBdb -> Database connections -> Add database connection .​

 This will get added as cross container service as shown below:

Now mta.yaml will have the contents as follows:             

Step 6: Create the following database artifacts in CS1HDIB/cfg

Create the database artifacts under CS1HDIB/CS1HDIBdb/cfg folder:

SYNCS1TAB.hdbgrants:
-------------------
{
    "CS1HDIASI": {
        "object_owner": {
             "container_roles" : ["CS1XHDIO#"]   
                        },
        "application_user": {         
             "container_roles": ["CS1XHDIA"]                       
                            }
                  }
}

SYNCS1TAB.hdbsynonymconfig :
----------------------
{
    "SYNCS1TAB": {
      "target": {
            "schema": "CS1A_SCHEMA_1",
            "object": "CS1TAB"
      }
    }
  }

As described towards the end of step 3 above, depending upon how the project is deployed, make sure to use the correct schema name within the .hdbsynonymconfig in the correct case(uppercase or lowercase or mixed)

Step 7: Create the following database artifacts in CS1HDIB/src:​

Create the database artifacts under CS1HDIB/CS1HDIBdb/src folder:

SYNCS1TAB.hdbsynonym 
--------------------
{
    "SYNCS1TAB": {}
}

Now create a calculation view that makes use of the synonym created above and check if it gives the expected set of data during data preview.

Created a Cube type calculation view – CS1MYCV.hdbcalculation view  – to show the ‘Total quantity’ for a particular Product Id from the table CS1TAB( created in the HDI Container CS1HDIA) making use of the synonym created above.

Step 8: Build the Project CS1HDIB anddeploy​

Below is how the SAP HANA Projects folder in Business Application Studio will look once all the above steps are done:

       

Once deployed, click on the icon (shown below) to open HDI container in Database explorer.

Go to SQL editor and run a query on the calculation view.

If this gives you the correct result, then your cross HDI access is working fine.

Conclusion

By now you would have got clear idea about how to use HDI service for cross HDI container access. For the second and the third use cases, the blogs will be out soon. For more information regarding SAP HANA Cloud and HDI containers, please refer to the following:

We highly appreciate for all your feedbacks and comments! In case you have any questions, please do not hesitate to ask in the Q&A area as well.

Finally, big thanks to Jan Zwickel from SAP HANA Database Product Management team and Stefan Hoffman from HANA Database and Analytics Cross Product Management team for all the support in making this happen.

Thank you for your time, and please stay tuned for our upcoming blog posts!