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
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?
Hi @skullbeast420,
not really. Maybe you should check with your teacher what he meant with export data to .sql file?
Br,
Ivan
I would love to, but he’s that kind of teacher that replies to an email every week xD thanks anyway 
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 
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:
@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
Not exactly what i was looking for, but not bad at all. Will check it, thank you!
Very nice! Thanks a lot!
OK in short you would need:
install MySQL community server (https://dev.mysql.com/downloads/mysql/)
install MySQL Workbench (https://dev.mysql.com/downloads/workbench/)
connect KNIME to this configuration
create a schema (either in KNIME or in the workbench)
then you can upload data to MySQL using KNIME
in the workbench under Server/Data export there is a tool to create such a .SQL dump
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.
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:
“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
Hello @dmanase,
that would be an interesting request but who knows maybe you are right
Welcome to KNIME Community!
Br,
Ivan
@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 
But anyway: welcome to the KNIME community!
@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.
@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
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
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… 
hope this clarify.
Best
Andrea
I don’t know
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.
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
Hello @agiani99
We have developed the OrientDB integration for Knime. You can check it here.
However the latest version we used for testing was on July 2019, so I do not know if the API has changed dramatically since then.
Also there is a blog post describing an example of ETL between SQLite and OrientDB:
Best regards,
Artem.
