Replicating Data using SQL

Objectives

After completing this lesson, you will be able to:

  • Replicate data using SQL statements

SQL Statements to Manage Replication

To implement replication, SAP recommends that you define source files in your project to describe the replication settings, and then deploy these files to generate the run-time objects in the database. We will cover this approach later.

For now, we will implement replication by executing SQL statements in the SQL Console of the Web IDE. Working this way helps us to understand each individual step in the setup of replication.

For this approach, you first need to create a virtual table based on the source data. You also need a target table in SAP HANA Cloud.

Once you’ve created these objects, you can then work through the following steps:

  1. Create the remote subscription to register the target table as the receiver of the source data.

    Code snippet
    CREATE REMOTE SUBSCRIPTION <subscription_name> 
    ON <virtual_table_name> 
    TARGET <target_table_name>;
    Expand

  2. Queue the remote subscription (except for RTR), to create all the necessary objects in the source. (Triggers, Shadow tables, and so on.)

    Code snippet
    ALTER REMOTE SUBSCRIPTION <subscription_name> QUEUE;
    Expand

  3. Do initial load (except for RTR), if you want to get the existing source data.

    Code snippet
    ‌INSERT INTO <target_table_name> 
    (SELECT * FROM <virtual_table_name>);
    Expand

  4. Distribute the subscription to start the actual replication of modifications.

    Code snippet
    ALTER REMOTE SUBSCRIPTION <subscription_name> DISTRIBUTE;
    Expand

After adding some rows to the source, you can query the target table to check for the new data.

First, launch this video to see an example with a smart data integration (SDI) connection:

Now launch the following video to see an example using Remote Table Replication, using a smart data access SDA) connection:

Additional Features

You can choose to replicate only some specific columns, and/or partitions. We call this a subreplication.

You need to execute the following statements to create/alter the remote subscription:

  • For a subreplication based on columns:
    Code snippet
    CREATE REMOTE SUBSCRIPTION <subscription_name> 
    {AS SELECT (<column_list>) FROM <virtual_table_name> TARGET TABLE <target_table_name>};
    Expand
  • For a subreplication based on partitions:
    Code snippet
    ALTER REMOTE SUBSCRIPTION <subscription_name> 
    { PARTITIONS ( <partition_number> [ {, <partition_number> }...] ) | ALL PARTITIONS }
    Expand

The other steps stay the same.

Launch this video to learn how to set up subreplication:

Log in to track your progress & complete quizzes