This is the second part of the blog series on working with cross HDI container access scenarios in SAP HANA Cloud. The main blog or part 1 can be found here. Please note that these blogs applies only to stand alone SAP HANA Cloud instance.

Recap

In SAP HANA Cloud, the database development artifacts are deployed from and to HDI containers. It is a common scenario where the tables located in one HDI container have to be accessed from other HDI containers. In the part 1 blog, we discussed cross HDI container access using HDI service between the containers located within the same SAP HANA Cloud instance. This blog(part 2 blog) explains how to achieve the cross HDI container access using user-provided service.

Use Case

Suppose the two HDI containers are located in the same SAP HANA Cloud instance, and a user-provided service has to be used for cross HDI container access.

As shown in the above picture, this blog explains how to access the CS1TAB table in the HDI Container CS1HDIA from the calculation view CS2AMYCV in the HDI Container CS2UPSB. We are making use of a user-provided service for cross container access in this case, which is the most recommended for such cases.

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

1. 1. Create the following database artifacts under 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​
   2. Build the project CS1HDIA and deploy​
   3. Create a database user USR2B and grant the roles created above with admin option
   4. Create a second database project/HDI Container CS2UPSB
   5. Create a user-provided service using the database user and password created in step 4, and add this to database connections of CS2UPSB.​
   6. Create the following database artifacts in CS2UPSB/cfg
      • SYNCS2ATAB .hdbgrants file
      • SYNCS2ATAB.hdbsynonymconfig file​
   7. Create the following database artifacts in CS2UPSB/src
      •  SYNCS2ATAB.hdbsynonym file to access the CS1TAB.hdbtable from HDI Container CS1HDIA.
      • CS2AMYCV.hdbcalculation view that makes use of the above synonym ​
   8. Build the Project CS2UPSB 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.

Reusing the HDI container CS1HDIA of use case 1, created in Main Blog, for this exercise as well. Follow the steps 1, 2 and 3 from the Main Blog. Then continue with the following steps:

Step 4: Create a database user and grant the roles created in CS1HDIA with admin option

From SAP HANA Database Explorer with a user having ROLE ADMIN privilege, open SQL editor from the SAP HANA database and run the following SQL:

• CREATE USER USR2B PASSWORD “HanaRocks01” SET USERGROUP DEFAULT;
• ALTER USER USR2B DISABLE PASSWORD LIFETIME;
• Grant CS1A_SCHEMA_1.”CS1XHDIO#” to USR2B WITH ADMIN option;
• Grant CS1A_SCHEMA_1.”CS1XHDIA” to USR2B WITH ADMIN option;

Depending upon how the project is deployed, make sure to use the correct schema name(with or without ‘_1’ suffix) above.

Step 5: Create a second database project B/HDI Container CS2UPSB​

The mta.yaml file will show the following:

Step 6: Create a user-provided service using the database user and password created in step 4, and add this to database connections of CS2UPSB

The “user-provided service” enables assigning of roles automatically to the generated users of the HDI container. It uses the permissions assigned to a specified user(USR2B created at step 4 above in this case) to connect to a database and execute grant statements during application deployment. The grant statements gets generated from contents of .hdbgrants file(provided in the next step).

Under Business Application studio -> SAP HANA Projects -> CS2UPSB/CS2UPSdb -> Database connections -> Add database connection  -> Create user-provided service instance ‘UPSCASE2’

Provide the username(USR2B) and the password, schema name(CS1A_SCHEMA_1) and click ‘Add’. Make sure to use the relevant schema name here and within any code referring to the schema name, in the correct case(uppercase or lowercase or mixed). This can avoid a lot of errors. The UPS will get added as ‘cross-container-service’ as shown below:

The mta.yaml file also will have entries related to this as seen above.

Step 7: Create the following database artifacts in CS2UPSB/cfg

Create the following database artifacts under CS2UPSB -> cfg folder

SYNCS2ATAB.hdbgrants:
--------------------
{
"UPSCASE2": {
    "object_owner": {
       "schema_roles" : [
        {
          "schema" : "CS1A_SCHEMA_1",   
          "roles_with_admin_option" : [ "CS1XHDIO#" ]
        }
      ]
    },
    "application_user": {
        "schema_roles": [
           {
               "schema" : "CS1A_SCHEMA_1", 
               "roles" : [ "CS1XHDIA" ] 
           }
        ]
  }
  }
}

On startup, the HDI Deployer looks for .hdbgrants files and processes the contents in the following manner:

1. For each grantor in the file, the HDI Deployer checks for the existence of a bound service with the specified name (“UPSCASE2” in the example above)
2. Connects to the database with the credentials of the named service
3. Grants the specified privileges to the grantees.

The above details are from the SAP help documentation Syntax Options in the hdbgrants File and more details regarding the hdbgrants can be found in there.

SYNCS2ATAB.hdbsynonymconfig​ :
----------------------
{
  "SYNCS2ATAB": {
    "target": {
      "object": "CS1TAB",
      "schema.configure": "UPSCASE2/schema"
    }
  }
}

Step 8: Create the following database artifacts in CS2UPSB/src:​

SYNCS2ATAB.hdbsynonym :
--------------------
{
    "SYNCS2ATAB": {}
}

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

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

Step 9: Build the Project CS2UPSB and deploy​

Deploy the project to the SAP HANA Cloud instance.

Once deployed, go to SAP HANA Database Explorer 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

Hope this blog gave you a good picture about the steps involved in cross HDI container access using user-provided service. For the third use case(cross HDI container access using virtual tables), please check case 3 blog.

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

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