This is the third part of the blog series on working with cross HDI container access scenarios in SAP HANA Cloud. The main blog(part 1) can be found here and part 2 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 between the containers located within the same SAP HANA Cloud instance using HDI service. In the part 2 blog, the same scenario but making use of user-provided service was discussed. This blog(part 3 blog) explains how to achieve the cross HDI container access using virtual tables.

Use Case

Suppose the two HDI containers are located in different SAP HANA Cloud instances under same/different regions/providers. Virtual tables/SDA has to be used for cross HDI container access in this case.

As shown in the above picture, this blog explains how to access the CS1TAB table in the HDI Container CS1HDIA from the calculation view CS4AMYCV in the HDI Container CS2HDIB using virtual table.

Here are the steps involved:

————————————————————-

1. Create new SAP HANA database project /HDI Container CS1HDIA to be deployed to SAP HANA Cloud Instance 1
2. 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​
3. Build the project CS1HDIA and deploy​ to SAP HANA Cloud Instance 1.
4. Create a database user REMUSERA in SAP HANA Cloud Instance 1 and grant the roles created above with admin option
5. Import the DigiCert Global Root CA certificate, and add a certificate collection (PSE) with REMOTE SOURCE as purpose, to SAP HANA Cloud instance 2
6. Create/Add Remote Source to SAP HANA Cloud Instance 2
7. Create database user in SAP HANA Cloud Instance 2 for using in a user-provided service that will be created later
8. Create a second database project/HDI Container CS2HDIB to be deployed to SAP HANA Cloud Instance 2.
9. Create a user-provided service using the database user and password created in step 7, and add this to database connections of CS2HDIB.​
10. Create the following database artifacts in CS2HDIB/cfg
    • SYNCS2TAB .hdbgrants file
    • VIR_CS1TAB.hdbvirtualtableconfig​
11. Create the following database artifacts in CS2HDIB/src
    •  VIR_CS1TAB.hdbvirtualtable file to access the CS1TAB.hdbtable from HDI Container CS1HDIA.
    • CS4AMYCV.hdbcalculation view that uses the above virtual table. ​
12. Build the Project CS2HDIB and deploy​ to SAP HANA Cloud Instance 2.

—————————————————————————————-

Details regarding each of the above steps follows below.

To begin with make sure you have logged in to the correct CF organization and Space.

Reusing the HDI container CS1HDIA created in Case1Blog link as the project/container in SAP HANA Cloud Instance 1 in this exercise. Follow the steps 1, 2 and 3 from the Case1Blog link. Then continue with the following steps.

Step 4:Create a database user in SAP HANA Cloud Instance 1 and grant the roles created above with admin option

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

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

Step 5: Import the DigiCert Global Root CA certificate and add it a certificate collection (PSE) with REMOTE SOURCE as purpose

Use Certificate Store app to import the DigiCert Global Root CA certificate from dl.cacerts.digicert.com/DigiCertGlobalRootCA.crt.pem and add it a certificate collection (PSE) with REMOTE SOURCE as purpose.

To do this, go to BTP Cockpit ->Cloud Foundry spaces -> SAP HANA Cloud -> Select the SAP HANA Cloud instance 2 -> Actions -> Open SAP HANA Cockpit -> Security and User management -> Certificate Store

Click ‘Go to Certificate Collections’ -> Add Collection -> give anyname , say, ‘PSEREM’ -> OK -> Now click ‘add certificate’

Now Click ‘Edit Purpose’-> Select ‘remote source’ -> Save

If the SAP HANA Cloud instances are from different providers or different regions, then we need to allow access between the two. For the system that is being accessed, SAP HANA Cloud Instance 1 in this case,  BTP Cockpit -> Cloud Foundry spaces -> SAP HANA Cloud -> Select the SAP HANA Cloud instance1 -> Edit -> give specific IP address. But in this case, it is not required as both the SAP HANA Cloud instances are under the same subaccount or provider, there should be access between the two automatically.

Step 6: Add Remote Source to SAP HANA Cloud Instance 2

From BTP Cockpit -> Cloud Foundry spaces -> SAP HANA Cloud -> Select the SAP HANA Cloud instance 2 -> Actions -> Open SAP HANA Database Explorer -> SAP HANA database(SAP HANA Cloud Instance 2) -> Catalog -> remote Sources right-click -> Add remote source -> REMSRCA

For server name,copy the SQL endpoint of SAP HANA Cloud Instance 1 and remove the ‘:<port number>’
DML Mode: Readonly
Credentials Mode: Technical User
Credentials :<User credentials from Step 4>
The remote source is REMSRCA is now created in the SAP HANA Cloud instance 2.

Once it is created, go to SAP HANA Database Explorer ->SAP HANA database(instance 2) -> catalog-> Remote Sources -> Click REMSRCA -> will show as follows:

Step 7: Create database user in SAP HANA Cloud Instance 2 for using in a user-provided service that will be created later

Create the database user for use in user-provided service that will be used to assign the role(which has CREATE VIRTUAL TABLE privilege) to the  object owner of the project CS2HDIB(HDI Container 2).

From SAP HANA Database explorer -> SAP HANA Database Instance 2-> SQL Console -> login with the user who created the remote source and run the following:

• CREATE ROLE UPSROLE;
• GRANT CREATE VIRTUAL TABLE, CREATE REMOTE SUBSCRIPTION ON REMOTE SOURCE “REMSRCA” to UPSROLE;
• CREATE USER UPSUSER  PASSWORD “HanaRocks01” NO FORCE_FIRST_PASSWORD_CHANGE SET USERGROUP DEFAULT;
• ALTER USER UPSUSER DISABLE PASSWORD LIFETIME;
• GRANT UPSROLE to UPSUSER WITH ADMIN OPTION;

Step 8 :Create a second database project/CS2HDIB to be deployed to the SAP HANA Cloud database instance 2

In mta.yaml, you would see the database id of SAP HANA Cloud Instance 2.

Step 9 : Create a user-provided service using the database user and password created in step 7, and add this to database connections of CS2HDIB

​Create User provided service, VIRTABUPS, as follows:

Under SAP HANA Projects -> CS2HDIB/CS2B_db -> Database connections -> Add database connection  -> Create user-provided service instance -> Use ‘UPSUSER’ credentials from step 7.

Step 10 : Create the following database artifacts in CS2HDIB/cfg

Create .hdbgrants file to assign the UPSROLE role to the object owner of the virtual table project (CS2HDIB)

SYNCS2TAB .hdbgrants:
----------------------
{
    "VIRTABUPS": {
        "object_owner": {
             "roles" : ["UPSROLE"]            
        },
        "application_user": {            
                 "roles": ["UPSROLE"]                        
         }
}
}

Create .hdbvirtualtableconfig file for the virtual table:

VIR_CS1TAB.hdbvirtualtableconfig :
---------------------------------
{
 "VIR_CS1TAB" : {
    "target" : {
       "remote"   : "REMSRCA",
       "database" : "<SAP HANA Cloud database instance 1 ID>",
       "schema"   : "CS1A_SCHEMA_1",
       "object"   : "CS1TAB"
    }} }

Depending upon how the project was deployed(using the deploy symbol in BAS or mta.yaml), make sure to use the correct schema name(with or without ‘_1’ suffix) within the .hdbvirtualtableconfig code above.

Step 11: Create the following database artifacts in CS2HDIB/src

VIR_CS1TAB.hdbvirtualtable:
---------------------------------
VIRTUAL TABLE "VIR_CS1TAB" AT "REMSRCA"."<NULL>"."<NULL>"."CS1TAB" 

Now the contents of the project CS2HDIB in SAP Business Application Studio will look as follows

Now create a calculation view that makes use of the virtual table 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.hdbcalculationview – 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 virtual table created above.

Step 12: Build the Project CS2HDIB and deploy​

Deploy the project to the SAP HANA Cloud instance 2.

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 container access is working fine.

Conclusion

Hope this blog gave you a good idea about the steps involved in cross HDI container access using virtual tables when the HDI containers are located in different SAP HANA Cloud database instances.

To find out about the latest on SAP HANA Cloud, check database and data management.

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.