How to store DB Data (after DB Joiner) in a persistent table without DB Reader

KNIME Analytics Platform
1

Hi,

we have started to adopt KNIME and a I do hope this question has a solution.

We are using DB Joiner to push heavy joins (multi million tables) down to the database level. Now I want to store the result in a DB table without pulling it into KNIME again and then use a DB Writer again.

Any ideas are highly appreciated…

Thanks
Marcel

2

I’d use Insert from in DB SQL Executor.

3

Thanks for that hint. I was thinking the same, but the SQL Executor has no input port for the DB data output of the DB Joiner.

I do not want to write a complete JOIN script in the SQL Executor as some than one JOIN is connected over multiple tables with >200 columns. So typing this in SQL will be hard to read (for an outsider) and hard to maintain.

Is there a way to reference the DB data result set ID from the DB Joiner in an SQL Executor?

Thanks
Marcel

4

You can try Node description for DB Connection Table Writer node. I never use it.

1 Like
5

I’ve solved the issue. I use a DB Query Extractor after the DB Join to extract the Join query SQL code. Then I add this variable to the DB SQL Executor and state the following sql statement:

Select * INTO New_table from
($${Ssql}$$) As Input

The Join result get’s then persisted into my New_table.

May be that’s helpful for other users…

3 Likes
6

And it’s even easier if you use the DB Table Writer…Just connect the Output of the DB Joiner to persist the data.

1 Like
7

Hi there @MarcelMeyer,

guess you mean DB Connection Table Writer! I was missing that one. Tnx!

Br,
Ivan

8

Yes, thanks for correcting my statement. I also overlooked it in the first instance.Cheers!

9

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.