SQL query - how to store Database Query results in database table (without leaving dbserver)

[hermyknime]

Hi All,

i’m trying to process some data on DB server side, i don’t seem to be able to load the results from a “Database Query” into a predefined existing table without pulling the data first into the Knime environment.

is there a way to more gracefully perform as such
INSERT INTO target_table (var1,var2)
SELECT var1, var2
FROM source_table

now i’m messing with diagrams like such

i also tried with SQL extract / SQL inject
or even directly via Database SQL Executor, but it does not allow me to make such “data manipulations” (sorry, i don’t have the example flow anymore)

anyone has a more graceful solution ?

Herman

[evert.homan_scilifelab.se]

Hi,

I don’t think you can do this using Knime without having the data to go through Knime…but why is that a problem?

Regards/Evert

[hermyknime]

Hi Evert,

thx for your reply!

there are all sorts of reasons to not have to pull the data over, but mainly

• keep the data close to the processing engine, if you can push down the logic to the database you will get better performance (if the db is sufficiently sourced for analytics purposes - non OLTP)

in that case you can focus on the advanced analytics in knime and do the bulk of the data prep wrangling in the database. this is the main principle of big data’s map / reduce for distributed computing

here i’m currently working with small datasets, so my main reason would be to keep the diagram tidy

needing less nodes and reducing node orchestration and synchronisation (which i now do via variable output/input connections) by storing some intermediate results in db-tables

although i understand that knime is not necessarily optimised for ETL/DWH industrialisation purposes

Cheers,

Herman

[ipazin]

Hi there Herman!

What you are saying makes sense - push down the logic to the database as much as possible.

From your first post I don’t quite understand what manipulations are you trying to do and why are you not succeeding. Database SQL Executor should be able to do carry out your statement if I’m not missing something. Can you try to explain it a bit more and I maybe I can make you an example with sqlite database.

Additionally I recommend you to check out new Database Integration nodes that should be production ready in summer release

Br,
Ivan

[hermyknime]

Hi ipazin,

it seems i was not very awake when i was testing…
by reproducing the issue, i think i found the solution (you were aiming at)

one just has to be carefull in mapping the cols correctly

i believe what i was doing incorrectly was trying to force the Database Query Node to make INSERT INTO queries

which basically gives me the following warning

WARN Database Query 2:32 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘INSERT INTO dev_artificiality_datavault.HUB_DataSource as tbl_target (datasource’ at line 1

which is a bit of a strange error imho… but now i see the light!

looking forward to those new Database Integration nodes for the future release though!

thx,

Herman

[ipazin]

Hi Herman!

Glad you found a solution. The warning massage is a bit strange and hopefully, in general, warning messages will be more appropriate in months and years to come

Br,
Ivan