Getting Started with SAP HANA Cloud | Remote Data Source (multicloud)

Multicloud Access

Chief partner engineer, Philip MUGGLESTONE just updated his SAP HANA Cloud playlist on the SAP HANA Academy YouTube channel with two new video tutorials covering how to enable multicloud access between SAP HANA Cloud instances.

In Blog IV on Cloud Foundry space travel we already covered how we can connect to our SAP HANA Cloud instance from another Cloud Foundry space. In this blog we cover how to connect SAP HANA Cloud instances from different SAP Cloud Platform subaccounts or global accounts in different regions and from different cloud providers with some additional information and resources.

We cover

• How to create a certificate collection (PSE) with the required certificate to establish the trust between both systems
• How to whitelist a cloud provider region for incoming traffic
• How to create a remote data source
• How to explicitly and implicitly create a virtual object
• How to worked with linked databases and local table replicas

Prerequisites

For this sample environment we have created one SAP HANA Cloud instance with cloud provider AWS in the US East region and another with cloud provider Microsoft Azure in Europe region.

YouTube Playlist

For the complete list of tutorial videos for this playlist, see,

• SAP HANA Cloud

GitHub Code Repository

For the sample code repository, go to

• github.com/saphanaacademy/SAPHANACloud

Documentation

• Connecting SAP HANA Cloud to Remote Data Sources – SAP HANA Cloud Getting Started Guide (QRC 02/2020)
• Data Access with SAP HANA Cloud – SAP HANA Cloud Administration Guide

Multicloud Access – Create Remote Source

In the first tutorial, we create a certificate collection in instance B and whitelist this instance (the cloud provider region) for ingress traffic on instance A and create a remote source.

[00:00] – Introduction

[00:30] – AWS and Azure hosted instances

[01:00] – Sample code on GitHub: remotehc.sql

[01:30] – Create schema, table, and user using SAP HANA Database Explorer for instance A

[02:40] – Connect to instance B with SAP HANA cockpit as user DBADMIN (or equivalent to create remote sources and certificates)

[03:10] – 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.

[04:45] – Add instance B as whitelisted connection (required for instances in different regions or cloud providers) using the nslookup command and the instance properties page in the SAP Cloud Platform Cockpit (requires instance restart).

# sample Azure DC
nslookup <guid>.hana.prod-eu20.hanacloud.ondemand.com    

Non-authoritative answer:
Name:	<guid>.hana.prod-eu20.hanacloud.ondemand.com
Address: 40.74.1.116

[06:50] – open SAP HANA database explorer on instance B to add remote source using the endpoint for server and port parameters with encrypt=true as extra adapter property.

[08:30] – verify the connection can be established by selecting schema and search for remote object.

Alternatively, we can create the remote source using SQL:

CREATE REMOTE SOURCE "hana1" ADAPTER hanaodbc 
 CONFIGURATION 
 'Driver=libodbcHDB.so;ServerNode=<endpoint>;dml_mode=readonly;encrypt=true;'
 WITH CREDENTIAL TYPE 'PASSWORD' 
 USING 'user=<user>;password=<password>'

Multicloud Access – Working with Virtual Tables

In the second tutorial, we explore some of the options about working with virtual tables, like creating synonyms and local table replicas.

[00:00] – Introduction and recap

[00:30] – create virtual object

[01:00] – Open data

[01:40] – Alternatively, we can use SQL to create the remote object; the <null> corresponds to the database name as  configured for the remote source (see above).

CREATE VIRTUAL TABLE MYDATA."v_SALES" 
 AT "hana1"."<null>"."MYDATA"."SALES";

[03:50] – Or access the remote table as “linked database”. This will trigger the creation of a virtual table in the _SYS_LDB schema (LDB = linked database).

SELECT * FROM "hana1"."MYDATA"."SALES";

[04:30] – create a synonym

[04:50] – update metadata with statement

ALTER REMOTE SOURCE <name> REFRESH LINKED OBJECT

[05:30] – drop metadata with statement

ALTER REMOTE SOURCE <name> DROP LINKED OBJECT CASCADE

[06:00] – system views M_REMOTE_STATEMENTS and M_EXPENSIVE_STATEMENTS

[06:30] – create replica table with the statement

ALTER VIRTUAL TABLE MYDATA."v_SALES" ADD SHARED SNAPSHOT REPLICA;

[07:30] – changes are not replicated automatically (depending on configuration)

[08:10] – system views M_VIRTUAL_TABLE_REPLICAS and VIRTUAL_TABLE_REPLICAS

Share and Connect 

Questions? Post as comment.

Useful? Give us a like and share on social media. Thanks!

If you would like to receive updates, connect with me on

• LinkedIn > linkedin.com/in/dvankempen
• Twitter > @dvankempen

For the author page of SAP Press, visit

• Denys van Kempen | SAP Press

For the SAP HANA Cloud e-bite, see

• Introducing SAP HANA Cloud