Hub
Pricing About
  • Software
  • Blog
  • Forum
  • Events
  • Documentation
  • About KNIME
  • KNIME Community Hub
  • mlauber71
  • Spaces
  • Public
  • forum
  • kn_forum_mysqldump_import
WorkflowWorkflow

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

Mysql Dump H2 Databse Extract
+3
Markus Lauber profile image

Last edit:

Drag & drop
Like
Download workflow
Copy short link
Workflow preview
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

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

Used extensions & nodes

Created with KNIME Analytics Platform version 4.1.0
  • Go to item
    KNIME Core Trusted extension

    KNIME AG, Zurich, Switzerland

    Version 4.1.0

    knime
  • Go to item
    KNIME Database Trusted extension

    KNIME AG, Zurich, Switzerland

    Version 4.1.0

    knime
  1. Go to item
  2. Go to item
  3. Go to item
  4. Go to item
  5. Go to item
  6. Go to item
Loading deployments
Loading ad hoc executions

Legal

By using or downloading the workflow, you agree to our terms and conditions.

Discussion
Discussions are currently not available, please try again later.

KNIME
Open for Innovation

KNIME AG
Talacker 50
8001 Zurich, Switzerland
  • Software
  • Getting started
  • Documentation
  • E-Learning course
  • Solutions
  • KNIME Hub
  • KNIME Forum
  • Blog
  • Events
  • Partner
  • Developers
  • KNIME Home
  • KNIME Open Source Story
  • Careers
  • Contact us
Download KNIME Analytics Platform Read more on KNIME Business Hub
© 2023 KNIME AG. All rights reserved.
  • Trademarks
  • Imprint
  • Privacy
  • Terms & Conditions
  • Credits