Routing CSV Files to Tables

SymmetricDS has the ability to sync between databases and between files. With the 3.9 release, SymmetricDS will link the two which will enable you to route data from a CSV file into a database table. This will make it significantly easier to directly sync your data. In this article, I will walk through the steps to replicate CSV data to a table.

First create a target table on the target node by running the following SQL command. You may also choose a pre-existing table to serve as your target table.

create table person (
   id integer not null,
   first_name varchar(50) not null,
   last_name varchar(50) not null,
   primary key(id)
); 

Next, configure SymmetricDS. The first step in configuration is to add a CSV Router. In the SymmetricDS Pro web console go to the Configure->Routers screen.

Create a new router of type CSV and specify the target table as well as the appropriate group link for communication between node groups.

The second step is to create a file trigger. Navigate to Configure->File Triggers.

If it is not already, enable file sync for each node.

Create a new file trigger. The base directory in which the files will be found should be specified as well as the files that will set off the trigger. A file trigger is necessary for each target table that uses a CSV Router.

Finally, go to Configure -> File Routing.

Add a new file routing link to join together your csv file trigger and csv router.

Now, you can add a new CSV file to your base directory. For this example, I’ve created the file ‘person1.csv’.

Navigate to Manage ->Jobs. Make sure that the File Sync Tracker, File Sync Pull, and File Sync Push jobs are started for each node. If they are stopped, start them.

Wait 1 minute for File Sync Pull to pull down your new file and then go to Explore and run the following SQL command on your target table to view the changes.

select * from person; 

Now you know how to load CSV files into a database table using a CSV Router in SymmetricDS.