Transform a MySQL dump to H2 (local) database using KNIME

OK, I built something since the mentioned examples are not straightforward (if you want to play around with them further in a subfolder of the workflow there is a /script/ folder containing some tests with R and Python scrips mentioned in the links but the results are insufficient if you have more complex INSERTS statements).

As an example I use the Mondial MySQL dump mentioned here:
https://blog.twineworks.com/converting-a-mysql-dump-to-csv-files-b5e92d7cc5dd
(which is also included in the workflow subfolder /data/)

A fair warning: it ain't pretty and may not be for the faint-hearted, and depending on your dump some modifications might be necessary and if your data gets very big you might have a problem since KNIME Flow variables might not be able to handle it. For example, it does work, however.

So what this workflow does is it identifies the 4 components of the SQL statements needed to (re-)build a database. The numbers will be represented in the no_marker/no_marker_orig

1. DROP TABLE IF EXISTS - initiates a clean start
2. CREATE TABLE statement initiating the (empty) structure
3. a row about primary keys and more informations specific to MySQL which H2 and other simple DBs might not understand. This No 3 will be replaced by a dummy line closing the CREATE statement (the construction with the dummy variable and ALTER table is not pretty at all - might just have removed the comma and added a bracket - but hey over-engineering is my thing)
4. The INSERT statement filling the structure with the data. This is a tricky part since the data can contain quotes, pairs of information and other quirks. And the statement can be quite long. We would have to do with restrictions of memory and limitations of flow variables.

External resources

• H2 database example
• forum entry
• Mondial database
• A quickly-hacked-together Python script to turn mysqldump files to CSV files. Optimized for Wikipedia database dumps.
• Converting a MySQL dump to CSV files