Skip to Content

Access Remote Sources with SAP HANA Database Explorer

Use SAP HANA federation capabilities to query data from other SAP HANA and SAP HANA Cloud, data lake Relational Engine databases using SAP HANA smart data access (SDA) and the Cloud Connector.
You will learn
  • How to use SAP HANA smart data access (SDA) to create connections (remote sources) to other databases
  • How to create virtual tables from a remote source
  • How to setup the Cloud Connector to enable a remote source from SAP HANA Cloud to an on-premise SAP HANA database
  • Instances of SAP HANA, express edition, SAP HANA Cloud, SAP HANA database, and data lake Relational Engine
danielvaDan van LeeuwenAugust 17, 2022
Created by
danielva
October 20, 2020
Contributors
danielva
thecodester

Prerequisites

  • You have completed the first 3 tutorials in this group
  • Two SAP HANA databases and an SAP HANA Cloud, data lake instance

Remote sources are connections to other databases. Virtual tables use a remote source to create a local table that points to data stored in another database. Federated queries make use of virtual and non virtual tables.

To illustrate these concepts, a table will be created in the remote database that contains fictitious review data from some of the top tourist sites near a given hotel. There is likely a correlation between hotel stays and the desire for customers to visit nearby tourist attractions or restaurants.

For additional details on SAP HANA smart data access (SDA) and SAP HANA Smart Data Integration (SDI), consult Connecting SAP HANA Cloud to Remote Data Sources and Data Access with SAP HANA Cloud.

Note that this tutorial requires more than one database to complete. It is not necessary to complete this tutorial to continue to the next tutorial in this group.

Note that the SAP HANA Cloud trial is limited to creating one SAP HANA database per trial account.

The example in step 1 demonstrates connectivity from an on-premise, SAP HANA, express edition database to an SAP HANA Cloud database. The example in step 2 demonstrates a connection from an SAP HANA Cloud database to an SAP HANA Cloud, data lake Relational Engine. The example in step 3 demonstrates connecting from SAP HANA Cloud, database via the Cloud Connector to an SAP HANA, express edition database.

  • Step 1
    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.

      SQL
      Copy
      CREATE SCHEMA HOTEL;
      CREATE USER USER1 PASSWORD Password1 no force_first_password_change;
      GRANT ALL PRIVILEGES ON SCHEMA HOTEL TO USER1;
      
      SQL
      Copy
      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 VARCHAR(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.

      SQL
      Copy
      SELECT * FROM HOTEL.TOURIST_REVIEWS;
      
      tourist reviews in HANA Cloud
    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.

      Add remote source

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

      Add remote source properties

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

      SQL
      Copy
      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';
      CALL PUBLIC.CHECK_REMOTE_SOURCE('REMOTE_HC_TRIAL');
      

      Additional details can be found at CREATE REMOTE SOURCE Statement.

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

      Host and Port

      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.

      allowlist

      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.

      remote sources
    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:

      on-premise to cloud remote connection

      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.

      SQL
      Copy
      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.

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

      Press the Create button.

      Create virtual table

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

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

      Additional details can be found at CREATE VIRTUAL TABLE Statement.

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

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

      SQL
      Copy
      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;
      
      federated query

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

      timings
    11. Add a new review.

      SQL
      Copy
      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.

  • Step 2

    SAP HANA Cloud, data lake can be used to store large amounts of data that is not accessed and updated as frequently as data in an SAP HANA database. The following steps create the table tourist_reviews in SAP HANA Cloud, data lake Relational Engine and access the table from the associated SAP HANA database 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.

      add a SAP HANA Data Lake
    2. Open the database explorer with a connection to the data lake by choosing Open in SAP HANA Database Explorer.

      open database explorer
    3. Execute the following SQL to create a table named tourist_reviews in the data lake Relational Engine.

      SQL
      Copy

      --Create a role that will own the sample hotel dataset CREATE ROLE HOTEL; CREATE TABLE HOTEL.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 HOTEL.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 HOTEL.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');
      create table data lake

      For additional details consult CREATE TABLE Statement for Data Lake Relational Engine.

    4. In the SAP HANA database connection, create a remote source from the HANA database to the data lake Relational Engine. Be sure to replace the host and password values.

      SQL
      Copy
      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';
      CALL PUBLIC.CHECK_REMOTE_SOURCE('HC_DL_TRIAL');
      
      create remote source
    5. After pressing refresh, notice that under remote sources, there is a remote source HC_DL_Trial.

      SAP HANA Data Lake Remote Source
    6. Right-click on HC_DL_TRIAL and choose Open. 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 Relational Engine.

      virtual data lake table

      This can be visualized as follows:

      data lake and on-premise remote connection

      Alternatively, the following SQL statement can be used to create the virtual table.

      SQL
      Copy
      CREATE VIRTUAL TABLE HOTEL.VT_DL_TOURIST_REVIEWS AT HC_DL_TRIAL.iqaas.HOTEL.TOURIST_REVIEWS;
      

      It is also possible to create the remote table and virtual table together in the same statement.

      SQL
      Copy
      CREATE VIRTUAL TABLE HOTEL.VT_DL_TOURIST_REVIEWS (
        REVIEW_ID INTEGER PRIMARY KEY,
        REVIEW_DATE DATE NOT NULL,
        DESTINATION_ID INTEGER,
        DESTINATION_RATING INTEGER,
        REVIEW VARCHAR(500) NOT NULL
      ) AT HC_DL_TRIAL.iqaas.HOTEL.TOURIST_REVIEWS WITH REMOTE;
      INSERT INTO HOTEL.VT_DL_TOURIST_REVIEWS 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 HOTEL.VT_DL_TOURIST_REVIEWS 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');
      
    7. Query the local SAP HANA table and the equivalent SAP HANA Cloud, data lake Relational Engine table.

      SQL
      Copy
      SELECT * FROM HOTEL.TOURIST_REVIEWS;
      SELECT * FROM HOTEL.VT_DL_TOURIST_REVIEWS;
      
      Query SAP HANA Cloud, data lake
    8. Add a new review.

      SQL
      Copy
      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.

      Note, another approach is to use container groups. For additional details see Data Lake Relational Engine (HANA DB-Managed). An example follows.

      SQL
      Copy
      CALL SYSHDL.CREATE_CONTAINER('HOTEL_CONTAINER', 'DBADMIN');
      CREATE VIRTUAL TABLE TOURIST_REVIEWS (
        REVIEW_ID INTEGER PRIMARY KEY,
        REVIEW_DATE DATE NOT NULL,
        DESTINATION_ID INTEGER,
        DESTINATION_RATING INTEGER,
        REVIEW VARCHAR(500) NOT NULL
      ) at "SYSHDL_HOTEL_CONTAINER_SOURCE"."<NULL>"."SYSHDL_HOTEL_CONTAINER"."TOURIST_REVIEWS" WITH REMOTE;
      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.');
      CALL SYSHDL_HOTEL_CONTAINER.REMOTE_EXECUTE('INSERT INTO TOURIST_REVIEWS 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'')');
      --GRANT SYSHDL_HOTEL_CONTAINER_ROLE TO USER1;
      --CONNECT USER1 PASSWORD Password1;
      SELECT * FROM SYSHDL_HOTEL_CONTAINER.TOURIST_REVIEWS;
      CALL SYSHDL_HOTEL_CONTAINER.REMOTE_EXECUTE('DROP TABLE SYSHDL_HOTEL_CONTAINER.TOURIST_REVIEWS;');
      --CONNECT DBADMIN PASSWORD Hana1234;
      CALL SYSHDL.DELETE_CONTAINER('HOTEL_CONTAINER');
      
  • Step 3

    The Cloud Connector enables communication from the SAP BTP running in the public internet to securely connect to a configured on-premise system such as SAP HANA, express edition.

    1. Enable the Cloud Connector connectivity in SAP HANA Cloud Central.

      Enable the Cloud Connector
    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.

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

      Shell (Microsoft Windows)
      Copy
      set PATH=C:\Windows\System32;c:\jdk1.8\bin
      set JAVA_HOME=c:\jdk1.8
      cd C:\SAP\scc20
      go.bat
      
      Started

      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.

      subaccount

      The location ID is used when multiple Cloud Connectors are connected to the same subaccount


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

      The region and subaccount ID can be found in the SAP BTP cockpit.

      cloud Foundry sub account

      The screen below shows a successful connection.

      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.

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

      Add remote source

      Specify the server, port, extra adapter properties, credentials (User1, Password1) and if a Location ID was specified in step 5, add scc_location_id=<locationid>' to the Extra Adapter Properties.

      Add remote source properties

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

      SQL
      Copy
      CREATE REMOTE SOURCE REMOTE_HANA_EXPRESS
      	ADAPTER "hanaodbc"
      		CONFIGURATION 'ServerNode=v-linux-bj72:39015;use_haas_socks_proxy=true;driver=libodbcHDB.so;'
      			WITH CREDENTIAL TYPE 'PASSWORD'
      				USING 'user=User1;password=Password1';
      CALL PUBLIC.CHECK_REMOTE_SOURCE('REMOTE_HANA_EXPRESS');
      

      Additional details can be found at Create an SAP HANA On-Premise Remote Source.

    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.

      remote sources
    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.

      remote sources

      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:

      HANA Cloud to HANA, express connection

    For further information, see Data Replication and Data Virtualization, 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 Relational Engine.

    Which of the following are true for virtual tables?

Back to top