SymmetricDS has always provided hooks for data transformation, but in 2.x we have made it far easier to add custom data transformations with just a bit of XML configuration and simple transformation scripts.

 

SymmetricDS extensions are customizations to the out-of-the-box CDC (Change-Data-Capture) that SymmetricDS provides.  An extension point is a hook that allows either synchronized data to be manipulated or the default behavior of SymmetricDS to be modified in some slight way.  Extension points allow data to be massaged at the point of routing, at the point of extraction or at the point of data loading.  Other extension points provide features like the ability to specify custom node ids,  custom sync URLs, and dynamic parameter settings.

 

When deploying SymmetricDS in standalone mode, extensions are configured in the conf directory of SymmetricDS.  Any XML file with a name that follows the pattern of **-extensions.xml found in the conf directory will be processed by SymmetricDS at startup.  Extension files are based on the Spring Framework.  Extension points are Java classes or runtime scripts that are wired into the SymmetricDS engine through the extension mechanism.

 

This article focuses on how to transform data as it is being loaded at a target system.

 

Data transformation scenarios are endless, but some concrete examples include:

  • Encryption or decryption of column data
  • Hard-coding column data
  • Swapping out column data based on a lookup-table
  • Updating of other tables based on the changing column data
  • Making a primary key unique

 

Examples can be found in mapping-extensions.xml.  This file can also be copied to create your own, domain specific extension files.

 

The extension point that allows data to be transformed right before it is written to the target database is called a data loader filter.  There are several out of the box data loader filter implementations that can be leveraged without writing Java code.  For more advanced filtering a developer might be required to create a new implementation of a data loader filter.

 

Multiple filters may be applied to the same table to accomplish different types of transformation of the same columns.

ADDING A COLUMN

In the case of a SymmetricDS network where client nodes are pushing data to a centralized database and there are tables that have unique rows in each of the client database that might overlap at the central database, an extra key column might need to be added to the central database that doesn't exist at the client databases.  Typically, this extra key column would be populated with a unique id for the client database.  A natural unique id in SymmetricDS is the node_id (system assigned) or the external_id (user assigned).

 

The new column can be populated in the central database using an AddColumnFilter.  The filter can be configured in an extension file using the following notation:

<bean class="org.jumpmind.symmetric.map.AddColumnsFilter">
  <property name="tables">    
    <list>            
      <value>SALE</value>       
      <value>SALE_LINE_ITEM</value>        
    </list>      
  </property>        
  <property name="additionalColumns">    
    <map>           
      <entry key="STORE_ID" value=":EXTERNAL_ID" />   
    </map>      
  </property>   
</bean>

This is an example of how to add and populate a column called STORE_ID on the SALE and SALE_LINE_ITEM table with the external id of the client node.  In this scenario, the STORE_ID column does not exist at the client node.

MAPPING COLUMN NAMES

In the scenario where the tables that need synchronization might have different column names at the target database, a simple ChangeColumnsNamesFilter may be used to map column names from one name to another.

<bean class="org.jumpmind.symmetric.map.ChangeColumnsNamesFilter">
    <property name="tables">
        <list>
            <value>STORE_TABLE</value>
        </list>
    </property>
    <property name="columnNameMapping">
        <map>
           <entry key="ID_STR_RT" value="STORE_ID" />
           <entry key="NM_LOC" value="LOCATION_NAME" />
           <entry key="CO_CNT" value="COUNTRY" />
        </map>
    </property>
</bean>

In this example, the column ID_STR_RT on the client table is being mapped to STORE_ID.  NM_LOC is being mapped to LOCATION_NAME and CO_CNT is being mapped to COUNTRY.  Note that table names can be mapped as well.  The easiest way to do this is to specify a target table name in the SymmetricDS router table.

If the column type is different and requires data transformation as well, then the next filter type will be of interest.

TRANSFORMING COLUMN VALUES

The ColumnDataFilters filter is an easy way to target specific columns in specific tables for any type of data transformation.  This filter takes a list of TableColumnValueFilters.  Each column filter is configured with the table name and the column name to apply the filter to.  The actual filter operation is set on the filter property.  The example below shows a script being wired in as the filter.  Script can be written in several different scripting languages.  The example below uses Bean Shell.  Groovy and JRuby are also options supported by the Spring Framework's scripted language support.  Note that the filter is reusable.  It could be applied to multiple columns.

<bean class="org.jumpmind.symmetric.map.ColumnDataFilters">        
<property name="filters">            
<list>                
<bean class="org.jumpmind.symmetric.map.TableColumnValueFilter">                    
<property name="tableName" value="SALE_TENDER_LINE_ITEM" />                    
<property name="columnName" value="ACCOUNT_NUMBER" />                    
<property name="filter" ref="maskAccountNumberScript"/>                
</bean>            
</list>        
</property>    
</bean>
<lang:bsh id="maskAccountNumberScript" refresh-check-delay="10000" init-method="init" script-source="file:../samples/mask-account-number.bsh" script-interfaces="org.jumpmind.symmetric.map.IValueFilter"> <lang:property name="mask" value="XXXXXXXX" /> <lang:property name="enabled" value="true" /> </lang:bsh>

The mask-account-number.bsh file would have the implementation of a filter method.

String filter (String originalValue, Map contextCache) {
  if (originalValue != null && originalValue.length() > 4) {
    originalValue = originalValue.substring(originalValue.length()-4, originalValue.length());
  }
  return mask + originalValue;
}

CONCLUSION

SymmetricDS wasn't built to be a data transformation tool, but at the same time it is simple enough to tweak your data to fit the requirements of the target database.

 

 

 

 

Chris Henson
Author: Chris Henson

Chris, the original founder of JumpMind, has been a software developer since the mid 1990's and has developed and architected systems for the defense, aviation, and retail industries. He is a productive consumer, active participant, and dedicated producer of open source solutions. Chris has also led SymmetricDS and POS implementations at both the national and international level.