Replica Tables

Replica tables are updated in real-time when the source table is updated. Real-time replication is only supported for remote sources that support real-time change data capture (CDC).

The replica table exists in the local SAP HANA Cloud system. It is an automatically generated table contained in the internal schema _SYS_TABLE_REPLICA_DATA, which is managed by the internal user _SYS_TABLE_REPLICA_DATA. You cannot change the definition of a replica table directly.

The following applies
- A virtual table cannot have multiple replica tables.
- Multiple virtual tables can share a single replica table when the source table in the remote system is the same.
- A replica table can be configured by the load option, which specifies how to load data into memory when the table is queried.
Toggling Between Virtual Tables and Replication Tables The toggle feature lets you switch between virtual tables and replication tables. To use a replicated table rather than a virtual table, the virtual table must have a corresponding replica table added to it.

Try it out!

To create a replica table switch from virtual access (virtual table) to data replication, execute this ALTER TABLE statement on the virtual table.

Code Snippet

12
--Data Replication: replica table
alter virtual table "VT_AZ_SALES" add shared replica;

Note

After this command, a table which stores replicated data is automatically generated in the schema

From the runtime information of the generated table, see that the number of records are identical to the source table, and that the data is physically stored in SAP HANA Cloud. Select the _SYS_TABLE_REPLICA_DATA schema to verify this information.

Inspect the Run Time information of the replicate table and verify that the row count matches the source table.

Execute the same SELECT statement as used in the virtual table lesson, and get the performance of a table stored in-memory locally and the replicate table found in the system schema _SYS_TABLE_REPLICA_DATA.

Code Snippet

12345678910
select 
C.CUSTOMER_LASTNAME "lastname", 
C.CUSTOMER_FIRSTNAME "Firstname", 
C.CUSTOMER_COUNTRY "Country", 
C.CUSTOMER_REGIONNAME "Region", 
S.PRODUCT_ID "ProductID",
S.QUANTITY "Quantity"
from GX_CUSTOMERS C, VT_AZ_SALES S
where C.CUSTOMER_ID = S.CUSTOMER_ID and S.QUANTITY > 1
order by S.QUANTITY DESC;

Replica Table performance results:

Compared to virtual table selection from previous virtual table lesson:

To replicate with a replica table, insert a new row into the source table (MS Azure SQLDB) via a virtual table.

Create a new virtual table called VT_AZ_SALES_DML by using the SQL statement below, and refresh the tables catalog to view the newly created table.

Code Snippet

12
-- VT_AZ_SALES_DML for insert to proof replca (can't insert into replica table)
create virtual table "VT_AZ_SALES_DML" at "RSSQLserver"."<NULL>"."SDI_USER"."AZ_SALES";

Verify row count before inserting new row. Use the SQL below in SQL Console to do this.

Code Snippet

1
select count(*) from VT_AZ_SALES_DML;

To insert a new row into the source table, switch back to the SQL Console and use the following SQL statement:

Code Snippet

123456789101112
-- insert data 
INSERT INTO VT_AZ_SALES_DML VALUES(
	'S_{placeholder|userid}',
	'2018-10-23',
	'22:04:46',
	'C_{placeholder|userid}',
	'P_{placeholder|userid}',
	1 ,
	10.0,
	1.5,
	100
);

Note

Rows affected 1

Verify row count after inserting new row. Use the SQL below in SQL Console to do this.

Code Snippet

1
select count(*) from VT_AZ_SALES_DML;

Note

The row count might not be the same as at the time of image capture.

Confirm replicate table is updated. Inspect the generated replicate table in the _SYS_TABLE_REPLICA_DATA schema as done before. Select the Runtime Information tab and click the refresh button. The row count should match the row count of the source table in the previous step.

Disable replication for virtual table if replication is not needed anymore, simply cancel the replication with this ALTER TABLE statement on the VT_AZ_SALES virtual table.

Note

The replica table in the

Code Snippet

12
-- remove table replica
alter virtual table "VT_AZ_SALES" drop replica;

Well done!! This completes the lesson on the different options in SAP HANA Cloud for managing replica tables (real-time replication) with remote data sources.

Next lesson