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.
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?
@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.
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:
“insert into <dest_schema_in your_DB>.<dest_table_in_your_DB> (a, b) values (”
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”.
@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
@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
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?
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.