Access Remote Sources with SAP HANA Database Explorer

Remote sources are connections to other databases. Virtual tables use remote sources to create local tables that point to data stored in another database. Federated queries use virtual and non virtual tables.

To illustrate these concepts, a table will be created in the remote database containing fictitious comment data from some of the top tourist attractions near a given hotel. There may be a correlation between hotel accommodation and customers' desire to visit nearby tourist attractions or restaurants.

For more details about SAP HANA smart data access (SDA) and SAP HANA Smart Data Integration (SDI), see connecting SAP HANA cloud to remote data sources and using SAP HANA cloud for data access.

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

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

Of course, you can also execute sql statements to complete the addition of remote data sources:

CREATE REMOTE SOURCE REMOTE_HC_TRIAL ADAPTER "hanaodbc" CONFIGURATION 'ServerNode=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.hana.trial-XXXX.hanacloud.ondemand.com:443;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';

You can copy servernodes from SAP HANA Cloud Central by selecting actions > replication > sql endpoint.

After executing the create remote source SQL statement, you can see the results in the Remote Sources folder, as shown in the following figure. You may need to press the refresh button.

Next, in SAP HANA express, you will create a file named vt_ tourist_ Virtual table for reviews. This will allow access to the tour created in SAP HANA Cloud_ Review table. This can be visualized as follows:

Open SAP HANA Database Explorer from SAP HANA express. If necessary, create the HOTEL schema and the users who can access it.

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

Right click the name remote_ HC_ Remote source for trial and select Open.

Check tourist_ Review check box and press the Create Virtual Object(s) button.

Set the object name to VT_TOURIST_REVIEWS, set the schema to HOTEL.

Press the Create button.

Or create a virtual table using an SQL statement:

CREATE VIRTUAL TABLE HOTEL.VT_TOURIST_REVIEWS AT "REMOTE_HC_TRIAL"."HC_HDB_Trial"."HOTEL"."TOURIST_REVIEWS";

Open the created database table and find that its type is VIRTUAL:

The following SQL statement demonstrates executing queries on local and remote tables and executing federated queries containing 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;

From the execution time, it is not difficult to find that reading data from a remote table takes significantly more time than reading from a local table:

Connect from SAP HANA Cloud to SAP HANA Cloud, data lake IQ

SAP HANA Cloud, data lake can be used to store a large amount of infrequently accessed data. The following steps create the tour table in SAP HANA Cloud, data lake IQ_ Review and access the table from the associated SAP HANA Cloud instance.

If necessary, in SAP HANA Cloud Central, add the SAP HANA cloud data Lake instance to your SAP HANA cloud instance by selecting action: add data lake.

Execute the following SQL to create a named tourist in the data Lake IQ_ Table for reviews.

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');

In the SAP HANA database connection, create a remote source from the HANA database to the data Lake IQ. Be sure to replace the host and password values.

CREATE REMOTE SOURCE HC_DL_Trial
	ADAPTER "IQODBC"
		CONFIGURATION 'Driver=libdbodbc17_r.so;host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443;ENC=TLS(tls_type=rsa;direct=yes)'
			WITH CREDENTIAL TYPE 'PASSWORD'
				USING 'user=HDLADMIN;password=myPassword1';

After refreshing, you will see a file named HC_ DL_ remote source of trial:

Right click HC_DL_TRIAL and select open. Create a VT in the architecture HOTEL in SAP HANA Cloud_ DL_ TOURIST_ The virtual table of reviews, which maps to the newly created table in SAP HANA Cloud data Lake IQ.

The schematic diagram is as follows:

SELECT * FROM HOTEL.VT_DL_TOURIST_REVIEWS;

Note that the remote data source is updatable. The data is stored in SAP HANA Cloud, and the data is stored on disk, which has cost advantages compared with memory storage. SAP HANA Cloud and data lake can also be used to store large amounts of data.

More Jerry's original articles are: "Wang Zixi":