Transform Multiple Tables to Single Table

The transformation feature can be used to copy data from multiple source tables into a single destination table. Using the primary key defined for the destination table, SymmetricDS will transform the inserts from the source table into updates on the destination table.

Setup Triggers

The source table will need changes captured before it can synchronize them to the destination. To capture changes, we setup a trigger. Our example source table is named SOURCE1.

insert into SYM_TRIGGER 
    (trigger_id, source_table_name, channel_id, last_update_time, create_time)
  values
    ('source1', 'SOURCE1', 'default', current_timestamp, current_timestamp);

The changes need to be routed to the intended destination node, which requires a router. Using the sample demonstration that comes with SymmetricDS, we will route data from the CORP node to the STORE node.

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, 
    create_time, last_update_time)
values
  ('corp-2-store', 'corp', 'store', current_timestamp, current_timestamp);

Don’t forget to associate the trigger with the router (or else your captured data will get routed to nowhere).

insert into SYM_TRIGGER_ROUTER 
  (trigger_id, router_id, last_update_time, create_time)
values
  ('source1', 'corp-2-store', current_timestamp, current_timestamp);

Setup Transform for First Table

Our transform configuration starts with SYM_TRANSFORM_TABLE, where we specify the direction of data, when to perform the transform, and the tables involved. This example will run a transform on the CORP node when data is extracted from the SOURCE1 table to be sent to the STORE node. The column policy of SPECIFIED means that only the columns we configure will be sent.

insert into SYM_TRANSFORM_TABLE
  (transform_id, source_node_group_id, target_node_group_id, transform_point,
   source_table_name, target_table_name, delete_action, column_policy)
values
  ('source1_to_dest1', 'corp', 'store', 'EXTRACT',
   'SOURCE1', 'DEST1', 'DEL_ROW', 'SPECIFIED');

Now we specify which columns to use, when to transform them, and whether to modify any data. This example just copies data between columns of the same name for any insert, update, or delete operations. Notice that we specify the ID column as the primary key, which makes sure SymmetricDS knows how to update rows if they already exist.

insert into SYM_TRANSFORM_COLUMN
  (transform_id, include_on, source_column_name, target_column_name, pk, transform_type)
values
  ('source1_to_dest1', '*', 'id', 'id', 1, 'copy'),
  ('source1_to_dest1', '*', 'name', 'name', 0, 'copy');

Setup Transform for Second Table

The second table transform looks just like the first one. We’re getting data from the SOURCE2 table this time, but it’s the same DEST1 table as the destination.

insert into SYM_TRANSFORM_TABLE
  (transform_id, source_node_group_id, target_node_group_id, transform_point,
   source_table_name, target_table_name, delete_action, column_policy)
values
  ('source2_to_dest1', 'corp', 'store', 'EXTRACT',
   'SOURCE2', 'DEST1', 'DEL_ROW', 'SPECIFIED');

This time we’re going to copy the DESC column to the destination. Remember to specify the primary key columns, which in this case is the ID column.

insert into SYM_TRANSFORM_COLUMN
  (transform_id, include_on, source_column_name, target_column_name, pk, transform_type)
values
  ('source2_to_dest1', '*', 'id', 'id', 1, 'copy'),
  ('source2_to_dest1', '*', 'desc', 'desc', 0, 'copy');

Conclusion

With transforms in place, you can modify data in the SOURCE1 and SOURCE2 tables, and the rows will be placed in the DEST1 table at the destination. We took two rows from source tables and converged them into a single row at the destination table. We didn’t modify data along the way, but we simply reorganized the data by copying between columns.

Related Posts