H2 and database writer

[zizoo]

I created with H2 connector and DB writer a database on my hard drive that contains an array of to tables.
The file extension is .mv.db
I would like to read this file and retrieve the tables to use them in Knime.
I think I should you DB query reader but I am not sure how to configure it.
Thanks,

[ipazin]

Hi there @zizoo,

For reading data back into KNIME you can use multiple nodes. DB Reader, DB Query Reader or Parameterized DB Query Reader. You can check workflow examples with this nodes on KNIME Hub.

Also topic title should be H2 instead of He, I guess?

Br,
Ivan

[zizoo]

Hi @ipazin
I found this workflow in the Hub
https://hub.knime.com/tobias.koetter/space/DB/DB_to_DB
I see how the database is read when it is written in the same workflow.
But in my case, I would like to import a database that I saved previously on my local drive.

[ipazin]

Hi,

follow same approach. Create database connection and add appropriate read node

Br,
Ivan

[zizoo]

Thats what I did. The read node ad the brown input and the DB connection node has a purple output .
This means that I have to write the databse in the workflow using DB write node.
This last node has a second input that calles the data to be saved and cannot be executed without connecting it to some data.

[ipazin]

Hi,

After connection you can use DB Query Reader immediately. If using DB Reader or Parameterized DB Query Reader you can use DB Table Selector before to have compatible ports.

Br,
Ivan

[zizoo]

Thanks @ipazin .
It worked when I inserted between the DB reader and the DB connector the DB table Selector.
In the output of the DB reader, I got a huge table where all the tables are stacked and appended together.
My goal is to be able to select a specific table from the database.
The tables in the database do not have the same number of rows or columns.
I do not record them but I record only the index of the table.
Is there a way to retrieve a specific table from the database according to its index of initial insertion of the database?

[ipazin]

Hi there @zizoo,

if I understood you correctly when you were writing to database you wrote all of your KNIME tables to one single table or?

Br,
Ivan

[zizoo]

Hi @ipazin,
I did not change the default setting in DB connector.
I just entered the path where to save the database and I asked to append a table each time it is executed to the database. ( I run the connector and the BD write node in a loop)
Ideally, the index of the loop should be enough to know where the table starts and where the next starts.
The output of DB read gives a huge table with all the tables from all the loops.

[ipazin]

Hi @zizoo,

I see @mlauber71 is helping you in this topic with writing data and myself with reading that data

In addition to his reply I suggest you learn a bit more about databases in general if you plan on using them

Br,
Ivan

[zizoo]

Hi @ipazin
Do you recommend any materials that will allow me to apply databases in Knime knowing that it is not really my field of expertise? I am more a chemist.
Thanks.

[ipazin]

Hi there @zizoo,

sry for a late response on this matter. I suggest first to get familiar with databases in general. Find some online materials (blogs, tutorials or videos) that will help you. Then you can learn a bit more about database you have chosen to use, H2 in this case. Now to apply that in KNIME I suggest you read/check KNIME Database Extension Guide which can be found here. After that try to apply new knowledge in KNIME workflow and if any questions you can always ask here. This should put you on the right path hopefully

Br,
Ivan