Access Remote Sources with SAP HANA Database Explorer

1. From the SAP HANA Cloud cockpit, open the SAP HANA database explorer and execute the following SQL statements to create the tourist_reviews table.

   If needed, first create a schema and user.

   CREATE SCHEMA HOTEL;
   CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
   GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO USER1;
   

   CREATE COLUMN TABLE HOTEL.TOURIST_REVIEWS(
       review_id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       review_date DATE NOT NULL,
       destination_id INTEGER,
       destination_rating INTEGER,
       review CHAR(500) NOT NULL
   );
   
   INSERT INTO HOTEL.TOURIST_REVIEWS(review_date, destination_id, destination_rating, review) VALUES('2019-03-15', 1, 5, 'We had a great day swimming at the beach and exploring the beach front shops.  We will for sure be back next summer.');
   INSERT INTO HOTEL.TOURIST_REVIEWS(review_date, destination_id, destination_rating, review) VALUES('2019-02-02', 1, 4, 'We had an enjoyable meal.  The service and food was outstanding.  Would have liked to have slightly larger portions');
   

2. The result can be seen below.

   SELECT * FROM HOTEL.TOURIST_REVIEWS;
   

   

3. To create a remote source from SAP HANA, express edition to SAP HANA Cloud, open the SAP HANA database explorer from the SAP HANA, express edition.

   Right-click Remote Sources and select Add Remote Source.

   

   Specify the server, port, extra adapter properties, and credentials (User1, Password1).

   

   Alternatively, in a SQL console, enter the SQL statement below after adjusting the ServerNode.

   CREATE REMOTE SOURCE REMOTE_HC_TRIAL ADAPTER "hanaodbc" CONFIGURATION 'ServerNode=7e2a2bb8-c560-46b3-b33f-dc4a1a9fd47c.hana.trial-us10.hanacloud.ondemand.com:443;encrypt=true;Driver=libodbcHDB.so;dml_mode=readwrite;sslTrustStore="-----BEGIN CERTIFICATE-----MIIDrzCCApegAwIBAgIQCDvgVpBCRrGhdWrJWZHHSjANBgkqhkiG9w0BAQUFADBhMQswCQYDVQQGEwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMSAwHgYDVQQDExdEaWdpQ2VydCBHbG9iYWwgUm9vdCBDQTAeFw0wNjExMTAwMDAwMDBaFw0zMTExMTAwMDAwMDBaMGExCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xIDAeBgNVBAMTF0RpZ2lDZXJ0IEdsb2JhbCBSb290IENBMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4jvhEXLeqKTTo1eqUKKPC3eQyaKl7hLOllsBCSDMAZOnTjC3U/dDxGkAV53ijSLdhwZAAIEJzs4bg7/fzTtxRuLWZscFs3YnFo97nh6Vfe63SKMI2tavegw5BmV/Sl0fvBf4q77uKNd0f3p4mVmFaG5cIzJLv07A6Fpt43C/dxC//AH2hdmoRBBYMql1GNXRor5H4idq9Joz+EkIYIvUX7Q6hL+hqkpMfT7PT19sdl6gSzeRntwi5m3OFBqOasv+zbMUZBfHWymeMr/y7vrTC0LUq7dBMtoM1O/4gdW7jVg/tRvoSSiicNoxBN33shbyTApOB6jtSj1etX+jkMOvJwIDAQABo2MwYTAOBgNVHQ8BAf8EBAMCAYYwDwYDVR0TAQH/BAUwAwEB/zAdBgNVHQ4EFgQUA95QNVbRTLtm8KPiGxvDl7I90VUwHwYDVR0jBBgwFoAUA95QNVbRTLtm8KPiGxvDl7I90VUwDQYJKoZIhvcNAQEFBQADggEBAMucN6pIExIK+t1EnE9SsPTfrgT1eXkIoyQY/EsrhMAtudXH/vTBH1jLuG2cenTnmCmrEbXjcKChzUyImZOMkXDiqw8cvpOp/2PV5Adg06O/nVsJ8dWO41P0jmP6P6fbtGbfYmbW0W5BjfIttep3Sp+dWOIrWcBAI+0tKIJFPnlUkiaY4IBIqDfv8NZ5YBberOgOzW6sRBc4L0na4UU+Krk2U886UAb3LujEV0lsYSEY1QSteDwsOoBrp+uvFRTp2InBuThs4pFsiv9kuXclVzDAGySj4dzp30d8tbQkCAUw7C29C79Fv1C5qfPrmAESrciIxpg0X40KPMbp1ZWVbd4=-----END CERTIFICATE-----"' WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=User1;password=Password1';
   

   The ServerNode can be copied from SAP HANA Cloud Central by choosing Actions > Copy > SQL Endpoint.

   

   ---

   If the above command fails, one reason might be that an allowlist has been set on the SAP HANA Cloud instance. This can be seen by choosing Actions > Edit.

   

   ---

   The public root certificate of the certificate authority (CA) that signed the SAP HANA Cloud instance’s server certificate is required in the sslTrustStore parameter. For more information, see Secure Communication Between SAP HANA Cloud and JDBC/ODBC Clients.

4. After executing the create remote source SQL statement, the result can be seen in the Remote Sources folder, as shown below. You may need to press the refresh button.

   

   Additional details can be found at CREATE REMOTE SOURCE Statement.

5. A virtual table named vt_tourist_reviews will be created in SAP HANA, express edition. This will enable access to the tourist_reviews table that was created in SAP HANA Cloud. This can be visualized as follows:

   

   Open the SAP HANA database explorer from the SAP HANA, express edition. If needed, create the HOTEL schema and a user who can access the schema.

   CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
   CREATE SCHEMA HOTEL;
   GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO USER1;
   

6. Right-click the remote source named REMOTE_HC_TRIAL and choose Open.

7. Set the Schema to be HOTEL and press the Search button.

   Check the TOURIST_REVIEWS checkbox and press the Create Virtual Object(s) button.

   

8. Set the Object Name to be VT_TOURIST_REVIEWS and the Schema to be HOTEL.

   Press the Create button.

   

   Alternatively, the virtual table can also be created with the SQL statement below.

   CREATE VIRTUAL TABLE HOTEL.VT_TOURIST_REVIEWS AT "REMOTE_HC_TRIAL"."Dans_HC_Trial_US-hana"."HOTEL"."TOURIST_REVIEWS";
   

9. Open the virtual table VT_TOURIST_REVIEWS and notice that its type is virtual.

   

10. Perform queries against the local tables and the remote table, and perform a federated query that contains both local and remote tables.

    SELECT * FROM HOTEL.RESERVATION;
    SELECT * FROM HOTEL.CUSTOMER;
    SELECT * FROM HOTEL.VT_TOURIST_REVIEWS;
    SELECT C.NAME, TR.REVIEW, REVIEW_DATE
    FROM
        HOTEL.RESERVATION AS R JOIN
        HOTEL.VT_TOURIST_REVIEWS AS TR
        ON TR.REVIEW_DATE = R.ARRIVAL JOIN
        HOTEL.CUSTOMER AS C
      ON C.CNO = R.CNO;
    

    

    Notice the executed time is greater when the data is retrieved from a virtual table.

    

11. Add a new review.

    INSERT INTO HOTEL.VT_TOURIST_REVIEWS(review_date, destination_id, destination_rating, review) VALUES('2020-08-21', 1, 5, 'The harbour cruise was fantastic.  It was great to see the city from a different viewpoint');
    SELECT * FROM HOTEL.VT_TOURIST_REVIEWS;
    

    Notice that the virtual table is editable.

A benefit of a virtual table is that there is no data movement. There is only one location where the data is persisted. As seen above, this can lead to longer query times when accessing remote data.

SAP HANA Cloud, data lake can be used to store large amounts of data that is not accessed frequently. The following steps create the table tourist_reviews in SAP HANA Cloud, data lake and access the table from the associated SAP HANA Cloud instance.

1. If needed, in SAP HANA Cloud Central, add an SAP HANA Cloud, data lake instance to your SAP HANA Cloud instance, by choosing Actions > Add Data Lake.

   

2. In a SQL console connected to the SAP HANA Cloud instance, execute the following SQL to create a table named tourist_reviews in the data lake.

   CALL SYSRDL#CG.REMOTE_EXECUTE('
   BEGIN
       CREATE TABLE TOURIST_REVIEWS (
       REVIEW_ID INTEGER PRIMARY KEY,
       REVIEW_DATE DATE NOT NULL,
       DESTINATION_ID INTEGER,
       DESTINATION_RATING INTEGER,
       REVIEW VARCHAR(500) NOT NULL
     );
     INSERT INTO TOURIST_REVIEWS(REVIEW_ID, REVIEW_DATE, DESTINATION_ID, DESTINATION_RATING, REVIEW) VALUES(1, ''2019-03-15'', 1, 5, ''We had a great day swimming at the beach and exploring the beach front shops.  We will for sure be back next summer.'');
     INSERT INTO TOURIST_REVIEWS(REVIEW_ID, REVIEW_DATE, DESTINATION_ID, DESTINATION_RATING, REVIEW) VALUES(2, ''2019-02-02'', 1, 4, ''We had an enjoyable meal.  The service and food was outstanding.  Would have liked to have slightly larger portions'');
   END');
   

   For additional details consult Creating a Data Lake Table.

3. Notice that under remote sources, there is a connection named SYSRDL#CG_SOURCE. This is the remote source for the SAP HANA Cloud, data lake that is associated with the SAP HANA Cloud instance.

   

4. Create a virtual table in SAP HANA Cloud named VT_DL_TOURIST_REVIEWS in the schema HOTEL that maps to the newly created table in SAP HANA Cloud, data lake.

   

   This can be visualized as follows:

   

5. Query the local SAP HANA table and the equivalent SAP HANA Cloud, data lake table.

   SELECT * FROM HOTEL.VT_DL_TOURIST_REVIEWS;
   

   

6. Add a new review.

   INSERT INTO HOTEL.VT_DL_TOURIST_REVIEWS VALUES(3, '2020-08-21', 1, 5, 'The harbour cruise was fantastic.  It was great to see the city from a different viewpoint');
   SELECT * FROM HOTEL.VT_DL_TOURIST_REVIEWS;
   

   Notice that the remote data source is updateable. Data stored in an SAP HANA Cloud, data lake is stored on disk, which has cost advantages compared to memory storage. SAP HANA Cloud, data lake can also be used to store large amounts of data.

1. Enable the Cloud Connector connectivity in SAP HANA Cloud. This feature is not available in the HANA Cloud trial.

   

2. Download the Cloud Connector. The software needs to run on a machine that can access your on-premise SAP HANA instance. In this example, the Cloud Connector was installed on Windows and is accessing an SAP HANA, express edition database running in a VM on the same machine.

   

3. As described at Installation on Microsoft Windows OS, Java 8 is a requirement.
   The following commands were used to start the Cloud Connector.

   set PATH=C:\Windows\System32;c:\jdk1.8\bin
   set JAVA_HOME=c:\jdk1.8
   C:\SAP\scc20>go.bat
   

   

   Note, it may take a while to start the Cloud Connector (3 minutes) and a line such as Cloud Connector 2.13.0 started will appear as shown above when it is started.

4. In a browser, open the URL https://localhost:8443.

   The initial user name and password are Administrator and manage.

5. Configure the Cloud Connector to connect to the Cloud Foundry subaccount.

   

   The region and subaccount ID and be found in the SAP Cloud Platform cockpit.

   

   Note, if you are an SAP employee you may need to enter your password + a time-based passcode as the password.

   Successfully connected.

   

   For additional details, see Initial Configuration.

6. Configure the Cloud Connector to connect to the on-premise database.

   Select Cloud To On-Premise and press the + icon.

   Select SAP HANA for the back-end type.

   Select TCP SSL for the protocol.

   Specify the host and port for the SAP HANA, express database.

   The virtual name and port can be different.

   

7. From the SAP HANA Cloud cockpit, open the SAP HANA database explorer, then right-click Remote Sources and select Add Remote Source.

   

   Specify the server, port, extra adapter properties, and credentials (User1, Password1).

   

   Alternatively, in a SQL console, enter the SQL statement below after adjusting the ServerNode.

   CREATE REMOTE SOURCE REMOTE_HANA_EXPRESS
   	ADAPTER "hanaodbc"
   		CONFIGURATION 'ServerNode=v-linux-bj72:39015;use_haas_socks_proxy=true'
   			WITH CREDENTIAL TYPE 'PASSWORD'
   				USING 'user=User1;password=Password1';
   

   Additional details can be found at CREATE REMOTE SOURCE Statement.

8. After executing the create remote source SQL statement, the result can be seen in the Remote Sources folder, as shown below. You may need to press the refresh button.

   

9. Right-click the remote source named REMOTE_HANA_EXPRESS and choose Open.

10. Set the Schema to be HOTEL and press the Search button.

    

    Notice that it is possible to create virtual tables to access the data from the on-premise system in SAP HANA Cloud.

    This can be visualized as follows:

    

For further information, see also Install the SAP Connectivity Service Cloud Connector in your System Landscape, Replicate data from on-premise to the cloud with the Cloud Connector, Access Data Across Your On-Premise and Cloud Data Sources, and Getting Started with SAP HANA Cloud | Remote Data Source.

Congratulations! You have now used remote sources to access data running on a different SAP HANA instance and on a SAP HANA Cloud, data lake.