Icon

kn_​forum_​mysqldump_​import

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.

possible quirk: the INSERT statement is not properly stored in one line and may be too long forKNIME to process or for the INSERT statement Transform a MySQL dump to H2 (local) database using KNIMEhttps://forum.knime.com/t/reading-mysql-dump-sql/20578/4?u=mlauber71 h2.databasemysql-mondial.dmpremove masking ofquotes!no_markercolumn1keep theoriginal markerno_marker_origfill thelines inbetweento make sure wekeep the groups$no_marker$ = 2 => TRUE$no_marker_orig$ IN (1,3,4) => TRUEstart_row_idfillstart_row_idkeep no 2CREATE TABLEkeep no 1DROPtable_nameregexReplace($column1$,"DROP TABLE IF EXISTS" , "" )replace($column1$,";" , "" )replace($column1$,";" , "" )keep no 3replace($column1$,";" , "" )keep no 4regexReplace($column1$,"NOT NULL DEFAULT" , "" )regexReplace($column1$,"DEFAULT NULL" , "" )combined 2 and 3CREATE TABLE4: INSERT1: DROP TABLE IF EXISTSSTART insertingSQL commandsEND Inserting SQLcommandsremove empty'' collectionsmake sure only oneline of KEY / PRIMARY KEY remainscheck andsee if thetables are therekeepINSERT statementslength_of_stringsort by INSERTstring length descending H2 Connector File Reader Rule Engine Column Rename Java Snippet(simple) Missing Value Rule-basedRow Filter Rule Engine Missing Value Rule-basedRow Filter Rule-basedRow Filter String Manipulation String Manipulation String Manipulation Rule-basedRow Filter Joiner String Manipulation Rule-basedRow Filter Column Filter String Manipulation String Manipulation Concatenate Column Filter Sorter GroupBy Column Filter Column Filter Concatenate Sorter Table Row ToVariable Loop Start Variable Loop End DB SQL Executor String Manipulation GroupBy DB Table Selector Rule-basedRow Filter String Manipulation Sorter possible quirk: the INSERT statement is not properly stored in one line and may be too long forKNIME to process or for the INSERT statement Transform a MySQL dump to H2 (local) database using KNIMEhttps://forum.knime.com/t/reading-mysql-dump-sql/20578/4?u=mlauber71 h2.databasemysql-mondial.dmpremove masking ofquotes!no_markercolumn1keep theoriginal markerno_marker_origfill thelines inbetweento make sure wekeep the groups$no_marker$ = 2 => TRUE$no_marker_orig$ IN (1,3,4) => TRUEstart_row_idfillstart_row_idkeep no 2CREATE TABLEkeep no 1DROPtable_nameregexReplace($column1$,"DROP TABLE IF EXISTS" , "" )replace($column1$,";" , "" )replace($column1$,";" , "" )keep no 3replace($column1$,";" , "" )keep no 4regexReplace($column1$,"NOT NULL DEFAULT" , "" )regexReplace($column1$,"DEFAULT NULL" , "" )combined 2 and 3CREATE TABLE4: INSERT1: DROP TABLE IF EXISTSSTART insertingSQL commandsEND Inserting SQLcommandsremove empty'' collectionsmake sure only oneline of KEY / PRIMARY KEY remainscheck andsee if thetables are therekeepINSERT statementslength_of_stringsort by INSERTstring length descendingH2 Connector File Reader Rule Engine Column Rename Java Snippet(simple) Missing Value Rule-basedRow Filter Rule Engine Missing Value Rule-basedRow Filter Rule-basedRow Filter String Manipulation String Manipulation String Manipulation Rule-basedRow Filter Joiner String Manipulation Rule-basedRow Filter Column Filter String Manipulation String Manipulation Concatenate Column Filter Sorter GroupBy Column Filter Column Filter Concatenate Sorter Table Row ToVariable Loop Start Variable Loop End DB SQL Executor String Manipulation GroupBy DB Table Selector Rule-basedRow Filter String Manipulation Sorter

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2020-01-30
On NodePit since: 2020-02-01
Last update: 2024-04-18
Created with KNIME version: v4.1.0
Tags: mysqldumpH2databseextractcreatesqlstatement

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!