Replica Snapshot Tables

Performs an initial load of the data currently in your source, without continually updating the replicated table, you can create a snapshot of the data by executing this ALTER VIRTUAL TABLE statement on the virtual table.

The replica snapshot 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.
- A snapshot replica can be used instead of a replica table. Snapshot replicas can be partitioned.
Toggling Between Virtual Tables and Replication Tables The toggle feature lets you switch between virtual tables and replica snapshot 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 Snapshot Replica, switch from virtual access (virtual table) to data snapshot replica, execute this ALTER TABLE statement on the virtual table. Make use of the VT_AZ_SALES virtual table created at the begining of the module.

Code Snippet

12
--Data snapshot: snapshot replica table
alter virtual table "VT_AZ_SALES" add shared snapshot 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.

Click on the Choose Schema button, in the search field search for the _SYS_TABLE_REPLICA_DATA schema and find a table with naming convention of AUTO_GENERATED_REPLICA_FOR_SDI_…

Select the Runtime Information tab of the snapshot table, the row count matches the source table.

Refresh the Snapshot

Insert a new row into the source table (MS Azure SQLDB) via a virtual table. A snapshot replica is not automatically updated when the source table is updated. You can update a snapshot manually using the REFRESH command.

Create a new virtual table called VT_AZ_SALES_DML if not created before, 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 statements.

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

Is the new record available in the snapshot table? 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.

Note

The row count should

Refresh the data snapshot to update the local snapshot table, use the following SQL statement.

Code Snippet

12
-- refresh snapshot
alter virtual table "VT_AZ_SALES" refresh snapshot replica;

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 snapshot will now have the latest copy of the data.

When the snapshot is not needed anymore, simply cancel the replication with this ALTER TABLE statement on the VT_AZ_SALES virtual table.

Code Snippet

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

Note

The replica table in the

Well done!! This completes the lesson on the different options in SAP HANA Cloud for managing replica snapshot tables with remote data sources.

Next unit