Reference Table Runtime Commands

Synchronization Overview

This article discusses synchronization between any Fabric session and the external source from which the Reference table is populated. For information about the cross-nodes Synchronization process within a Fabric Cluster, refer to this article.

Use Cases

Case 1 - Background Sync

In this scenario a Fabric Session requests to update a Reference table from an external source according to a predefined interval. The ref_sync and ref_sync_wait commands are triggered automatically in the background according to the method selected in Fabric Studio (Time Interval or Decision Function).

Case 2 - Pro-Active Sync

In this scenario, for example, a customer service operative needs to get the most updated list of new services subscribed by a customer in real-time. The Web Service or Job request triggers the ref_sync and ref_sync_wait commands.

Case 3 - Scheduled Sync

In this scenario a system needs to operate a synchronization process every day at 2 AM as a result of a maintenance task - i.e. get all new customers, or new transactions created over the last 24 hours. A recurring background sync can be scheduled for a specific day and time.

Synchronization Commands

The following commands are available from the Fabric Command Line.

Reference Tables Synchronization Statuses Commands

Reference Tables Runtime Examples

Status per table, on all tables

fabric>REF_STATUS TABLES='ALL' SCOPE='table';

|table name               |status|node        |backlog|offset|offset duration (min)|num of messages|transaction id|transaction type|sub status|current session transaction|sync error|notes|
+-------------------------+------+------------+-------+------+---------------------+---------------+--------------+----------------+----------+---------------------------+----------+-----+
|common.ref_Asset         |READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |
|common.ref_Asset_Stations|READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |
|common.ref_Asset_Type    |READY |fabric_debug|0      |0     |0                    |0              |              |IDLE            |          |                           |          |     |

• table_name: the name of the Reference Table.

• status: one of the statuses defined here.

• node: the ID of the node operating the Reference table's synchronization.

• backlog: number of messages still to be processed during the synchronization process.

• offset: total number of message retrieved during the synchronization.

• offset duration: time to process all messages.

• Transaction Id: current processed transaction id.

• Transaction Type:

  • LONG_SNAPSHOT – a transaction is considered a snapshot, when it starts with a delete table command, and it is considered long, when the number of insert queries is bigger than the configured bulk size in config.ini (TRANSACTION_BULK_SIZE). Consequently, all the insert commands are stored in Cassandra via a single message in Kafka that is the linkage to Cassandra. Bulks in a LONG_SNAPSHOT are processed one by one (one bulk at a time) while other updates are applied in between these bulks (into a different table in the same schema).

  • SHORT_SNAPSHOT – a transaction is considered a snapshot, when it starts with a delete table command, and it is considered short, when the number of insert queries is smaller than the configured bulk size in config.ini (TRANSACTION_BULK_SIZE). In such case, one message in Kafka contains all the queries.

  • SHORT_TRANSACTION – update/insert/delete commands in one Fabric transaction (between begin and end commands) when the number of queries is smaller than the configured bulk size in config.ini (TRANSACTION_BULK_SIZE). In such case, one message in Kafka contains all the queries.

  • LONG_TRANSACTION - update/insert/delete commands in one Fabric transaction (between begin and end commands) when the number of queries is bigger than the configured bulk size in config.ini (TRANSACTION_BULK_SIZE). Consequently, all the insert commands are stored in Cassandra via a single message in Kafka that is the linkage to Cassandra. In a LONG_TRANSACTION, the process of applying all the rows (all bulks, i.e., a large update) has to be completed before applying updates to other tables in the same schema.

    SNAPSHOT – save all data in a temp table; only when done, rename it back to the original table name.

    TRANSACTION – done directly on the original table.

  • IDLE – no activity on this table since the last restart/start.

• Sub_status:

  • In process

  • Index rebuild

  • Done

• Current Session Transaction values:

  • Started: the transaction has started on the current session, but is not committed yet

  • Commit in progress

  • Completed - commit is completed

  • Rollback in progress

  • Rolled back

  • Rollback is completed

    ​

Status per population, on all tables

fabric>REF_STATUS TABLES=’ALL’ SCOPE=’population’

|table name               |population     |verified time          |start sync time        |end sync time          |start write time       |last write time        |next planned sync      |sync error|node        |notes|
+-------------------------+---------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+-----------------------+----------+------------+-----+
|common.REF_ASSET         |population.flow|2022-03-16 17:55:32.324|2022-03-16 11:59:13.048|2022-03-16 11:59:13.631|2022-03-16 11:59:13.639|2022-03-16 11:59:13.743|1970-01-01 02:00:00.000|null      |fabric_debug|     |
|common.REF_ASSET_STATIONS|population.flow|2022-03-16 17:55:32.346|2022-03-16 11:59:13.048|2022-03-16 11:59:14.460|2022-03-16 11:59:13.774|2022-03-16 11:59:14.476|1970-01-01 02:00:00.000|null      |fabric_debug|     |
|common.REF_ASSET_TYPE    |population.flow|2022-03-16 17:55:32.361|2022-03-16 11:59:13.049|2022-03-16 11:59:13.238|2022-03-16 11:59:13.293|2022-03-16 11:59:13.295|1970-01-01 02:00:00.000|null      |fabric_debug|     |

• table_name: the name of the reference table
• population: the name of the population querying the external sources
• verified time: timestamp when last sync was verified
• start/end sync time: Sync time start and end times
• start/last write time: Write time of first and last message
• next planned sync: Timestamp for next sync
• sync error: error message
• node: The ID of the node operating the sync
• notes: details

Sync Wait

fabric>REF_SYNC_WAIT TABLES='ALL';

|Table name|Required sync time|Current session transaction|
+----------+------------------+---------------------------+
|REF_USAGE |null              |                           |
|T1        |null              |                           |
|T2        |null              |                           |

Reference Tables - Backing up and Restoring

Since the 7.1 release, it is possible to back up reference tables by schemas or 'ALL' (i.e., defining all schemas for a backup). This facilitates the act of either restoring a node if its reference tables data gets out of sync or syncing the reference tables data to a newly-joined node. Hence, a new ref_backup command is introduced; it is recommended to call this command from a Broadway flow to secure a reference backup according to the required frequency for the implementation.

A reference backup is stored in the same storage as defined on DEFAULT_GLOBAL_STORAGE_TYPE parameter in the config.ini, unless the COMMONS_BACKUP_DEFAULT_STORAGE parameter is set up in the config.ini.

When a new node is added to the Fabric cluster or when running a restart on an existing node, Fabric checks whether a local common file exists, if not, it proceeds with checking whether common backup exists for this node, otherwise it asks for a new refreshed snapshot.

The following commands are available for backing up/downloading a backup to - a local node:

Reference Table Data Manipulations

• To select and modify data in Reference Tables set the common_local_trx flag to TRUE, before running a commit. fabric>set COMMON_LOCAL_TRX=true;

  • Using the example defined earlier, and the DEVICESTABLE2017 Reference Table:
  • Enable Reference Table modification: fabric>set COMMON_LOCAL_TRX=true;
  • Use the select command to view the row to be modified:

  fabric>select TAC, BRANDMODEL  from DEVICESTABLE2017 where TAC=35156209;
  |TAC     |BRANDMODEL             |
  +--------+-----------------------+
  |35156209|GALAXY J3 2016 SM-J320F|
  

  • Start a transaction: fabric>begin;
  • Using the update command, operate a change in the table: fabric>update DEVICESTABLE2017 set BRANDMODEL='GALAXY J3--2016 SM-J320F' where TAC=35156209;
  • Check that the change has been committed:

  fabric>select TAC, BRANDMODEL  from DEVICESTABLE2017 where TAC=35156209;
  |TAC     |BRANDMODEL              |
  +--------+------------------------+
  |35156209|GALAXY J3--2016 SM-J320F|
  

  • Close the transaction: fabric>end;
  • Note that if you forget to close the transaction, write sessions to the Reference Table (such as a scheduled Sync) will not work.