Exporting only new MySQL records to a CSV text file

With this example we want to demonstrate to you how to configure a dynamic FlowHeater Definition using Parameters to export to a CSV file from a MySQL table that when run automatically only selects records that have been inserted since the previous run.

The initial (first) run will of course automatically export all records available of the MySQL table to the CSV text file.

Preparation

To enable this to work, the MySQL table must either have an auto-increment (AutoID) field, a date stamp field (DATETIME) containing the insertion date of the record e.g. defined using a default value (CURRENT_TIMESTAMP(6)) or some other consecutively allocated, unambiguously sorted criterion..

Note: As of MySQL version 5.6 DATETIME fields are able to be specified with microseconds (6 figure), making it possible to discriminate between the records of MySQL databases where data is inserted at a high rate. In this case to rely on times based on seconds would be very imprecise.

Let's get started

First we create a new FlowHeater Definition, selecting the MySQL Adapter on the READ side and the Textfile Adapter on the WRITE side to export a CSV text file.

Open the Configurator on the READ side, enter here the connection details for your MySQL Server database and run a brief connection test. Once a connection to the MySQL Server database has been established, switch to the tab "Fields / Data Types" and choose the MySQL table from the dropdown menu. All the tables available in the previously chosen MySQL database are listed here. Once you have located the right table, the list of fields in the MySQL table now requires loading and you instruct the Adaptor to do this by clicking the "Load Schema" button.

Now we configure the WRITE side Textfile Adapter for the CSV export. In the General tab we simply enter here an export file name "export.csv" and check the "The first row contains field names" option. Next we switch to the "Fields / Data Types" tab and by clicking the "Adopt the READ fields" button transfer all fields from the previously chosen MySQL table. Note: The CSV fields so transferred, including their column headings, can be amended as desired.
Now the fields on the READ side (MySQL) must be connected to the fields of the WRITE side (CSV text file).

Making it dynamic

The export we have configured thus far would write all the data in the MySQL table to the CSV text file specified each time it was run. However, the aim is to only export newly inserted records since the previous export each time.

Step 1)Store last used auto increment valueFirstly we require a FlowHeater Parameter to store the last used AutoID (auto increment) value of the ID column. To do this navigate using "Menu-> Edit-> Parameter" and insert a new placeholder Parameter named "LASTID" and check for this Parameter the "Store" option. This way you inform FlowHeater that the latest value of the Parameter will be saved for the next run. So that during the first export all available records of the MySQL table are exported to a CSV text file, we initialize the default value of the Parameter with a value of "0". In order that the LASTID Parameter works properly in the WHERE clause that is described in the next step, it is important we ensure the value is regarded as INTEGER (a whole number) and modify the default assigned notational format so that no dividing character for thousands is used.

Step 2)Modify MySQL export selectNext we must modify the default SELECT statement on the READ (MySQL) side comparing the placeholder Parameter "$LASTID$" with ID in the WHERE clause. Of course, we must also ensure that FlowHeater reads the MySQL table in ascending sequence, because the ID of last record processed will be stored and this should be the highest ID value, since in the next run it will serve as the starting value. To achieve this it is simply necessary to add ORDER BY id after the WHERE clause. The complete SELECT statement for reading the MySQL table should now appear as follows.

SELECT * FROM t_export WHERE id > $LASTID$ ORDER by id

Step 3)Use Set Parameter HeaterFinally the last ID value encountered in the MySQL table needs to be saved in the FlowHeater Parameter "LASTID". To do this we pull a new Parameter Heater onto the Designer page. With a double click on the Heater we open its configuration window. In this we select the "Set" function to store the Parameter. From the dropdown we choose the Parameter created above "LASTID" and with OK we close the window. To finish we connect the MySQL field "ID" as input to the Set Parameter Heater.

How does it work?

When the Definition is run for the first time, the Parameter $LASTID$ is initialized with its default value (0) and so all records in the table are exported to the CSV text file. After successful completion of the export, the final ID value is permanently stored for future use. Each time the same export Definition is subsequently run the Parameter is reloaded from the value stored after previous run, ensuring that only new records are selected due to the ID comparison in the WHERE clause on the MySQL READ side.
Note: The Parameter is never stored when using Test Mode.

Summary

Only three simple steps are needed to dynamically export only new records from a MySQL table to a CSV text file: Define a Parameter, incorporate this in the SQL WHERE condition and save the final value of the Parameter for subsequent runs.