Remote Table Replication

Once you have established the connection using the cloud connector, you can create a remote source in your SAP HANA Cloud, SAP HANA database instance pointing to the on-premise database.

1. Open the SAP HANA Database Explorer connected to your SAP HANA Cloud, SAP HANA database instance.
2. Open a SQL console.
3. With the following statement, you can create a remote source from SAP HANA Cloud, SAP HANA database to SAP HANA on-premise to then replicate data from the on-premise system to the cloud. See instructions below the code to find out what to insert for the <placeholders>
   SQL

   Copy

   CREATE REMOTE SOURCE <NAME> ADAPTER "hanaodbc"
   CONFIGURATION 'ServerNode=<virtual_host_name>:<portnumber>;use_haas_socks_proxy=true;scc_location_id=<locationid>'
   WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<username>;password=<password>';
   

   • In that statement, you need to add the host name and port number of the virtual host that you have manually created in the cloud connector. The reason for this is to not reveal the actual hostname and port which might be a breach in security.
   • For on-premise connections, the socks proxy needs to be specified, which is already included in this statement.
   • Next, in that statement, add the cloud connector location id, which you specified in the beginning.

   If you want to use remote table replication between two SAP HANA Cloud, SAP HANA database instances, you need to slightly modify this statement. For more technical details on creating the remote source, read more in the technical documentation.

4. Once all your information is included, run the statement. This will create a remote source from SAP HANA Cloud, SAP HANA database to your on-premise system.

5. The remote source will be established, and you can create virtual tables and use remote table replication just like when connecting different SAP HANA Cloud, SAP HANA database instances. Please note that you are only able to use remote table replication if you are using SAP HANA on-premise SPS05 or higher. Otherwise, only SDA virtual access is available.

1. In your catalog, click on Remote Sources.
2. Then, double-click on your remote source. A wizard will open.
   

3. In the drop-down menu to select a schema, you can now see the schema information from the remote source system.

4. Select the schema you would like to replicate a table from and click on Search.
5. You will now see the tables in the schema in your on-premise database.
6. Next, select a table from the list and create a virtual object that can be included in federated queries.
7. You can also adjust and execute this SQL statement to create a virtual table:
   SQL

   Copy

   CREATE VIRTUAL TABLE "<VT_TABLENAME>" AT "<REMOTESOURCENAME>"."<DATABASE>"."<SCHEMA>"."<TABLE>";
   

8. Once you have executed this statement successfully, you can find the virtual table in the catalog in your tables.

9. You can now query the data in this remote table.

In some cases, a virtual table may not be the best solution. For example, if you need to improve query performance. With Remote Table Replication, you can copy real-time data to your SAP HANA Cloud, SAP HANA database, which makes querying this data faster.

Additionally, if changes in the definitions of the source table of a virtual table occur, for example dropping or adding of columns, querying the virtual table pointing to the source table will result in an error. This can be avoided by setting up a remote subscription, which will not only replicate the data in real-time from the source system, but also the table definitions.

Learn in the next steps how to use remote table replication.

After the virtual table has been created, to set up Remote Table Replication, you need create a Replica Table to add a Remote Source Subscription to it.

1. The replica table will be the target table, that resides in the SAP HANA Cloud, SAP HANA database. This target table should be just like the virtual table you have just created.
2. So, first, create the replica table by executing this statement:
   SQL

   Copy

   CREATE COLUMN TABLE <TARGETTABLENAME> LIKE <VIRTUALTABLENAME>;
   

3. Once this statement is executed, you can find the metadata information from the remote source in this target table.
4. Double click on the new target table and you can see the same columns as in the virtual table. However, you will see no data because no replication has been set up for this table yet.

To start real-time replication, you need to create a remote subscription and activate that subscription. This will start the replication to your target table.

1. When creating a remote subscription, you need to specify a name for this remote subscription and specify from which virtual table to which target table the remote subscription should be established. You can adjust and execute this statement:

   SQL

   Copy

   CREATE REMOTE SUBSCRIPTION <REMOTESUBSCRIPTIONNAME> ON <VIRTUALTABLENAME> TARGET TABLE <TARGETTABLENAME>;
   

2. Once this statement has been executed successfully, you can click on Remote Subscriptions in your catalog and you should see it in the list there.

   

3. Next, to activate this remote subscription and start replicating data from your source system, you need to alter it and set it to distribute using this statement:

   SQL

   Copy

   ALTER REMOTE SUBSCRIPTION <REMOTESUBSCRIPTIONNAME> DISTRIBUTE;
   

While this statement is being executed, in the background all data from the source table will be replicated to the target table.

• After the execution is complete, you can use a SELECT * FROM statement on your target table and you will see the data from your source table in it.
• If you now add new rows to your source table in your on-premise database, you will instantly see the added data in your target table due to the remote subscription.
• This applies for adding columns to the source table as well. Data definition changes like adding or dropping columns will also be instantly applied to your remote subscription.
• The virtual table, however, will stay untouched by these changes and querying it would result in an error.

To refresh the virtual table to also include changes in data definitions, you need to execute this statement:

alter virtual table <VIRTUALTABLENAME> refresh definition;

If you now query the virtual table, you will see the updated definitions in this virtual table as well.

If you want to disable a remote subscription, you can use this statement:

alter remote subscription <REMOTESUBSCRIPTIONNAME> reset;

This will deactivate the distribution mode of the remote subscription. The definition of the target table will remain, but no data or definition changes will be updated from the source system anymore.

Well done!

You have completed the last tutorial of this group! Now you know how to set up Remote Table Replication in SAP HANA Cloud, SAP HANA database.

In the next group of this mission, you can learn how to set up a multi-source, hybrid data landscape and connect to other cloud databases, like AWS and Google BigQuery. This will allow you to use SAP HANA Cloud, SAP HANA database as your single gateway to all your data.