Real-time data replication

Real-time data replication

Replicating Data Using a Replication Task

Remote data access can be time-consuming because data needs to be transferred through the network each time a query is executed. In certain situations, replicating the remote data to the local system might offer better SQL query performance than accessing the data in a remote table.

SAP HANA Cloud, extend capabilities to replicate data via Smart Data Integration (SDI).

Replication Tasks

Replicate data from several objects in a remote source to tables in SAP HANA Cloud using the Replication Editor SAP Business Application Studio.

To replicate data from objects in a remote source into tables in SAP HANA, you must configure the replication process by creating a .hdbreptask file, which opens a file specific to the Replication Editor.

  • Smart Data Integration SAP HANA Smart Data Integration is an ETL tool, which can be used to load data in either in batch or real-time modes for SAP HANA on premise or in the cloud by using pre-built and custom adapters.

  • Data Provisioning Agent The Data Provisioning Agent manages all SDI Adapters and connections to SAP HANA Cloud, and acts as the communication interface between SAP HANA Cloud and the Adapter.

  • Database Synonyms A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

  • Some supported sources via SDI for SAP HANA Cloud:

    • ABAP tables &BPI
    • ODP Extractor
    • SAP ASE
    • ECC on ASE, IBM DB2, Microsoft SQL Server database, Oracle database
    • SAP BW
    • Microsoft Office Access
    • Facebook
    • IBM Informix Dynamic Server
    • Netezza
    • Vertica
    • MySQL
    • Cassandra
    • IBM DB2 Database enterprise edition
    • IBM DB2 z/OS
    • IBM DB2 iSeries
    • Microsoft Excel
    • Text files delimited and fixed width
    • HDFS
    • SAP HANA
    • Hive
    • Impala
    • Microsoft SQL Server database
    • OData
    • Oracle
    • Outlook PST files
    • PostgreSQL
    • SFTP files
    • SOAP
    • Teradata
    • Twitter

Try it out!

Create a database Synonym

Links Business Application Studio database artifacts (tables) to the SAP HANA Cloud database schema.

  1. Start Command Palette, and choose Create SAP HANA Database Artifact option

    Create SAP HANA Database Artifact option

  2. Choose Synonym (hdbsynonym) as the artifact type and provide RealTimeRepSynonym as the artifact name and then click Create.

  3. Click on the ellipsis (…) under Object Name.

  4. Click on the dropdown for services and choose the USER_CONN_ service by selecting the checkbox next to it.

  5. After adding the connection, search for the table RT_CUSTOMERS in the search column. Select the RT_CUSTOMERS table which resides in the schema of your user and select Finish.

  6. Now deploy the RealTimeRepSynonym.hdbsynonym by pressing the Deploy (rocket icon) button on the top right-hand side. Inspect the Terminal output for any errors.

Create a Replication Task

  1. Create a replication task to replicate the AZ_CUSTOMERS table residing in MS Azure into the RT_CUSTOMERS table residing in schema.

  2. Choose Create SAP HANA Database Artifact option.

  3. Choose Replication Task (hdbreptask) as the artifact type and provide TableReplication as the artifact name and then select Create.

  4. Once the replication task editor loads, click the Connect to a remote source icon on the top right side as shown in the screenshot.

  5. In the Select Remote Source dialog box, choose RSSQLserver as the option and press OK.

    Please Note

  6. Press OK and notice the Connected to RSSQLserver status on the top right. Select the plus sign (+) to the right of this.

  7. In the Select Remote Source Objects dialog, Navigate to SDI_USER->Tables and choose AZ_CUSTOMERS. Specify Initial + real-time as the Replication behaviour. Make sure that the Target Table Name Prefix is blank and press OK.

  8. Select the edit (pencil) icon under Details and go to the Source Properties tab. Change the Virtual Table name to VT_AZ_CUSTOMERS.

  9. Change the Target Properties tab. Make sure that all the checkboxes are unselected for the target Properties tab on the right side. Click on the two squares icon on the Target Table to modify the target table.

  1. In the Select Target Table dialog, click the dropdown for services and make sure that the Target Container Service is selected.

  1. Click in an empty location and then type synonym in the search. Select the RT_CUSTOMERS table that is in your schema and has the RealTimeRepSynonym as the suffix to its name. Click Finish.

  1. Back on the TableReplication.hdbreptask dialog. Make sure that the Target table is the one with RealTimeRepSynonym and Load Behavior is Replicate. Click the checkbox next to Truncate Table on execution and then click the deploy button on top right-hand corner to deploy the replication task.

  1. Switch to the Database Explorer tab that was used in the Getting Started and Introduction module. Navigate to Tables and find the RT_CUSTOMERS table.Right-click on this table and select Open Data to confirm it is empty.

Please Note

The

Start the replication process

  1. Once the deployment is complete, click on the Open HDI Container under the SAP HANA PROJECTS on the left-hand pane to open the SAP HANA Cloud Database Explorer.

  1. In the database explorer, expand the HDI container which would have a name starting with SharedDevKey@_. Navigate to Procedures and find TableReplication.START_REPLICATION procedure there. This procedure will be used to kick-start the replication.

  2. Now go back to the TableReplication.START_REPLICATION which is under the HDI container. Right click and choose Generate CALL Statement with UI.

  3. In the SQL console with a CALL statement. Execute the SQL statement to kick-start the replication.

  4. Go back to the tab on left which showed that RT_CUSTOMERS table has zero rows. Click on Refresh and you will that data has now replicated into the table.

Real-time data replication data insert

  1. In SAP HANA Cloud Database Explorer, expand the HDI container which would have a name starting with SharedDevKey@_Proj-hdi_db.

    Open an SQL Console by clicking on the SQL icon on the top left. Copy the following SQL and paste it in the SQL console and run by clicking on the green run button to.

    Code Snippet
    123456789101112131415161718
    INSERT INTO VT_AZ_CUSTOMERS VALUES(
'C_{placeholder|userid}', 
'{placeholder|userid}', 
'{placeholder|userid}', 
'{placeholder|userid}', 
'{placeholder|userid}',
'longtime ago',
'some country',
'some region',
'123456',
'some city',
'some street',
'51',
'53.766354',
'-6.282665',
'{placeholder|userid}@sapexperienceacademy.com',
'999-999-000'
);

    Please Note

    The

  1. The new customer inserted into Azure SQLdb via the VT_AZ_CUSTOMERS virtual table should now have replicated back to the RT_CUSTOMERS table, created earlier. Run the query below to confirm new customer insert was successful.

    Switch back tho the SAP HANA Cloud tenant and make sure the schema is selected. Select the query and execute the statement.

    Code Snippet
    123
    SELECT *
   FROM "{placeholder|userid}"."RT_CUSTOMERS"
   WHERE CUSTOMER_ID = 'C_{placeholder|userid}';

Congratulations!! Successfully used SDI to replicate data from and to Azure.

Learning

SAP FacebookSAP YoutubeSAP LinkedIn