Remote Table Replication

shraddhashetty · ‎10-16-2023

Introduction

Remote Table Replication transforms a design-time remote-table replication that refers to a current virtual table into a remote-table-replication database object. In order to replicate data from one SAP HANA Cloud database to another, the SAP HANA smart data access hanaodbc adapter is utilized. To achieve this, the initial step involves establishing a remote source connection from the SAP HANA Cloud, SAP HANA database to the system where the remote table is situated.

Read more RTR

Using ODBC connection for the RTR

To replicate the data from the remote table , you require a virtual table and a target table ( also referred to as replica table) in the target system, that is, in your local SAP HANA database in SAP HANA Cloud. The target table has the same metadata representation as the virtual table. In the below diagram, the virtual table V_CUSTOMER located in the MARKETING schema in the target database points to the remote table CUSTOMER in the SALES schema in the on-premise database.

Read more on Setting up Data Replication

Pre-Requisites

Create a source table

CREATE TABLE SOURCE_TABLE (ID bigint, first_name varchar(20));



-- add new entries

INSERT INTO SOURCE_TABLE VALUES(7, 'John');

INSERT INTO SOURCE_TABLE VALUES(42, 'Jane');

INSERT INTO SOURCE_TABLE VALUES(42, 'Amy');

In the local SAP HANA database, you create a remote source that points to the remote SAP HANA database.

You must follow the similar steps from the blog to create virtual tables in HDI

In the assign privileges section, assign the below privileges:

CREATE REMOTE SOURCE

CREATE VIRTUAL TABLE

CREATE REMOTE SUBSCRIPTION

1. Create Remote Table Replica via Business Application Studios (BAS)

Create a new HANA project via BAS

Check this blog on Getting Started With Native SAP HANA Development in SAP Business Application Studio

In the configuration file for the HDI container (.hdiconfig), the plug-in configuration should look like the following example:

"hdbremotetablereplica": {

            "plugin_name": "com.sap.hana.di.remotetablereplica"

        }

Note : The .hdiconfig in BAS may not have released with the new version which consists of the .hdbremotetablereplica plugin.

Now, you can create the .hdbvirtualtable and .hdbremotetablereplica artifacts.

src > SOURCE_VT.hdbvirtualtable

VIRTUAL TABLE "SOURCE_VT" AT "HANA1"."<NULL>"."GRANTOR"."SOURCE_TABLE"

create the src > SOURCE_RT.hdbremotetablereplica

REMOTE TABLE REPLICA "SOURCE_RT" ON "SOURCE_VT"

What happens behind the scene is as follows:

Deploy & compare the replica and virtual table by executing SQL statements to determine the execution time.

Note: Undeploy of the RTR will delete the SOURCE_RT

Below picture shows example of an other SQL statements used to compare the VT & RT of employee table which consists of 100K entries.

2. Creating Remote Table Replica via Database Explorer (DBX)

Like in the previous step, SOURCE_TABLE, Remote Source, Granting the necessary privileges on the Remote Source must be done.

Create a container group and container

Write the virtual table and the role

CREATE LOCAL TEMPORARY COLUMN TABLE #PATHS LIKE _SYS_DI.TT_FILESFOLDERS_CONTENT;

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdiconfig', ' { "file_suffixes" : {

                                "hdbvirtualtable" :       { "plugin_name" : "com.sap.hana.di.virtualtable" },

                                "hdbremotetablereplica" : { "plugin_name" : "com.sap.hana.di.remotetablereplica" },

                                "hdbvirtualtableconfig" : { "plugin_name" : "com.sap.hana.di.virtualtable.config" },

                                "hdbrole" : { "plugin_name" : "com.sap.hana.di.role" }

                            } }');

                            

                            

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdbvirtualtable', 'virtual table VIRTUAL_TABLE AT DEFAULT_REMOTE_SOURCE.DEFAULT_OBJECT');



INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdbvirtualtableconfig', '{

                                      "VIRTUAL_TABLE" : {

                                        "target": {

                                          "remote" : "SOURCE_VT",

                                          "database": "<Null>",

                                          "schema": "GRANTOR",

                                          "object" : "SOURCE_TABLE"

                                        }

                                      }

                                    }');



INSERT INTO #PATHS (PATH, CONTENT) VALUES ('role.hdbrole', '{"role": {"name": "MY_ROLE", "schema_privileges": [{"privileges": ["ALTER", "SELECT"]}]}}');

-- Replace the container name ( MY_CONTAINER ) to your container name

CALL MY_CONTAINER#DI.WRITE(#PATHS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #PATHS;

Deploy the virtual table and the role:

CREATE LOCAL TEMPORARY COLUMN TABLE #DEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdiconfig');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdbvirtualtable');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdbvirtualtableconfig');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('role.hdbrole');

CREATE LOCAL TEMPORARY COLUMN TABLE #UNDEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

CALL MY_CONTAINER#DI.MAKE(#DEPLOY_PATHS, #UNDEPLOY_PATHS, _SYS_DI.T_NO_FILESFOLDERS_PARAMETERS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #DEPLOY_PATHS;

DROP TABLE #UNDEPLOY_PATHS;

Write remote table replication

CREATE LOCAL TEMPORARY COLUMN TABLE #PATHS LIKE _SYS_DI.TT_FILESFOLDERS_CONTENT;

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdbremotetablereplica', 'REMOTE TABLE REPLICA "SOURCE_RT" ON "SOURCE_VT"');

CALL MY_CONTAINER#DI.WRITE(#PATHS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #PATHS;

Deploy the RTR

CREATE LOCAL TEMPORARY COLUMN TABLE #DEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdbremotetablereplica');

CREATE LOCAL TEMPORARY COLUMN TABLE #UNDEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

CALL MY_CONTAINER#DI.MAKE(#DEPLOY_PATHS, #UNDEPLOY_PATHS, _SYS_DI.T_NO_FILESFOLDERS_PARAMETERS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #DEPLOY_PATHS;

DROP TABLE #UNDEPLOY_PATHS;

Grant the schema privileges

CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES;

INSERT INTO #ROLES ( ROLE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'MY_ROLE', '', DBADMIN);

CALL MY_CONTAINER#DI.GRANT_CONTAINER_SCHEMA_ROLES(#ROLES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #ROLES;

Check the virtual and replica table on the remote system and you can query the replica like a normal table.

SELECT * FROM "MY_CONTAINER"."SOURCE_VT";

SELECT * FROM "MY_CONTAINER"."SOURCE_RT";

Undeploy the replication when it is no longer needed.

CREATE LOCAL TEMPORARY COLUMN TABLE #DEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

CREATE LOCAL TEMPORARY COLUMN TABLE #UNDEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

INSERT INTO #UNDEPLOY_PATHS (PATH) VALUES ('.hdbremotetablereplica');

CALL MY_CONTAINER#DI.MAKE(#DEPLOY_PATHS, #UNDEPLOY_PATHS, _SYS_DI.T_NO_FILESFOLDERS_PARAMETERS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

DROP TABLE #DEPLOY_PATHS;

DROP TABLE #UNDEPLOY_PATHS;

Please feel free to share your valuable feedback 🙂