Export table data to SQL file

KNIME Analytics Platform
1

Hello there everyone! This is my first topic here on KNIME Forum!
So i’m a student and i have a doubt: is it possible to get the data i have on KNIME and export it to a .sql file?

Thank you!

2 Likes
2

Hello @skullbeast420,

welcome to KNIME Community and KNIME itself!

Why do you want data exported to .sql file? Isn’t those files for sql and not data? Usually users export data to Excel or CSV files. You can also write data into database if that is what you are looking for.

Br,
Ivan

1 Like
3

Hello @ipazin and thank you for anwsering and for welcoming me!
Here’s the thing: i am working on a project to school and my teacher said it would be interesting to export data into a .sql file. Do you know any way to do so?

4

Hi @skullbeast420,

not really. Maybe you should check with your teacher what he meant with export data to .sql file?

Br,
Ivan

1 Like
5

I would love to, but he’s that kind of teacher that replies to an email every week xD thanks anyway :smiley:

1 Like
6

This is just a guess, but maybe he means a SQLite file, which is a static file while still somehow sort of being a database :smile:

We use SQLite files in some of our courses because they are easy to use in simple examples. Here’s a workflow that show how you might write one using DB nodes:

3 Likes
7

@skullbeast420 .sql would typically refer to a MySQL (or MariaDB) dump of a database to backup or transfer it. A quick google research found no R or Python tool (you might be able to use in KNIME) to create such a dump without the databases themselves.

The good news is MySQL, as well as MariaDB, are free and KNIME should offer a Connector (for the later there seems to be a debate going on).

It might be possible to trigger such a backup from within KNIME (maybe with the help of a R or Python snippet with a MySQL driver).

For educational purposes, I created a workflow to do it the other way round to import a mysql dump into KNIME (or H2 database) without the database itself

1 Like
8

Not exactly what i was looking for, but not bad at all. Will check it, thank you!

9

Very nice! Thanks a lot!

1 Like
10

OK in short you would need:

image
image1656×914 137 KB

image
image1898×1106 327 KB

The question would then be what to do with this dump other than again import it into a database. It might be much easier to just -well- use a (local) database. KNIME offers support for SQLite and H2.

2 Likes
11

Hi skullbeast420,

In my opinion the request is to create a text file with the .sql extension which contains insert staments with the data you have in KNIME.
Pretty similar with the posting from mlauber71 but not by exporting from the Database but using your KNIME data, as a source.

Let’s assume that the data you have in a table in KNIME (as a result of a previous KNIME process) has:

  • 2 columns “a” and “b”
  • with the values 11 and 22.
    Your <file_name>.sql file content ,could look like this:

“insert into <dest_schema_in your_DB>.<dest_table_in_your_DB> (a, b) values (11,22);”

without the double quotes.

If this is what is required, I’d create a loop that reads one row at the time, from your KNIME table, and converts the row into variables.
Be it “var_a” and “var_b” for the variable names that hold the values for column “a” and column “b” respectively.

Inside the loop add the logic to generate the insert statment, using a string manipulation node like this:

join(
“insert into <dest_schema_in your_DB>.<dest_table_in_your_DB> (a, b) values (”
, $$var_a$$
, “,”
, $$var_b$$
, “);”
)
Everything in one line. I split it just to better show the joined components.

The joined string will have the entire insert statement for one row.
Append each string to a KNIME table with one column, using the concatenate node.
At the end, all the rows you appended can be written with a table writer node to a text file with the extension “sql”.

Hope this helps,
Doru

3 Likes
12

Hello @dmanase,

that would be an interesting request but who knows maybe you are right :smile:

Welcome to KNIME Community!

Br,
Ivan

13

@dmanase interesting idea. I think it is possible to build such a file. I think one could put the different lines into table rows and export the whole thing into a TXT file. Question is what would be the benefit of that besides an interesting exercise :slight_smile:

But anyway: welcome to the KNIME community!

14

@ipazin and @mlauder71, thank you very much for your welcome!

When you have the data in KNIME and you can just push it into the database with a single node, totally agree, I saw this just as an exercise.

2 Likes
15

@ipazin, @Doru, @mlauber71
hi everyone. Imho, the post from @skullbeast420 has possibly another aspect which is not always fully appreciated and which constitutes my question. How can we import KNIME tables into a database, while creating it …
This is not trivial for instance with OrientDB, a graph database born as opensource and “immediately” fagocitated by SAS. For this database the CSV import is amazingly complex and undocumented in depth, while importing directly other database is more than welcomed and straightforward.
Question: has anyone generated sqlite, sql, postgres etc. database from KNIME table writing out e.g. file .sql, .sqlite in order to be able to import them into OrientDB?
This is what OrientDB suggest for import from CSV
Importing the Open Beer Database into OrientDB · OrientDB Manual
Best and thanks
Andrea

16

Hello @agiani99,

if I got it right you would like to import data from KNIME into OrientDB and upon uploading data to create database/tables/relations? And when you say importing other database is more than welcomed and straightforward what do you exactly mean? How do you import another database into it?

Br,
Ivan

17

Hi @ipazin,
yes I would like to import directly from KNIME to OrientDB: how? they suggest to generate CSV and then import from CSV using their oetl.sh/bat utility which uses JSON schema (see the link above I suggested). This a cumbersome utility which has not deeply documented and which generates lots of StackOverflow questions. As for OrientDB, importing from other sources (e.g. DBs dump) is much cleaner and effective, I was wondering how to generate from KNIME tables a temporary DB, whose dump could then be easily (much easier) imported in OrientDB. The option to use Neo4J DB is attractive, but I would like to stay in open source realm… :wink:
hope this clarify.
Best
Andrea

18

Hi @mlauber71 , very nice answer: will KNIME support OrientDB, beside Neo4J in next future?

19

I don’t know :slight_smile:

For OrientDB there seems to be a “Teleporter” to connect and synchronize with realtional databases like Postgres is mentioned. There also seems to be a studio to better handle OrientDBs.

http://orientdb.com/docs/3.0.x/teleporter/Teleporter-Home.html

And since KNIME does support such databases there might be some useful interaction possible.

1 Like
20

Hello @agiani99,

Have you tried creating temporary H2 or SQLite DB from KNIME tables? And then do DB dump which could be imported in OrientDB? (Never tried these things so not sure it’s possible and will work.)

Br,
Ivan