Passing a column values to a database connector and running a query on the list.

Hello dears, 

This is my first post in the community and i might be sending it in the wrong forum. So please let me know.

My question is how to pass a filtered list of values to a database connector? 

I have loaded a CSV file and done some math operations on the list along with some filteration. Now i have a list of values which i need to pass to run a query on. 

Thank you so much,

Arix 

Hi Arix,

welcome to the KNIME community and sorry for not having answered your question sooner. To use a list of values in a database query you can use the Database Looping node. Please find attached an example workflow that demonstrates its usage.

Bye

Tobias

1 Like

Dear Tobias,

Many thanks to you. 

 

Hi Tobias, this solution works, but is very inefficient. The query is limited by the type of database, but basically can pass a list of only a few thousand regardless. When querying a dataset with millions of rows, using a sql WHERE column in (list), and the list is hundreds of thousand of rows, the time to loop through the data base dozens if not hundreds of times is enormous. Is there no way to make the database see the entire list?

To my experience Database Looping node works pretty fast. Unfortunately it is deprecated in new version. If you have enough memory it will be faster join tables and or filter in DB or in KNIME. If you provide your work flow with example data it will be simple to decide.

2 Likes

@izaychik63
I made the same experience with the database looping. In a specific case looping reduces the time for data retrieving more than 10 fold. I’m missing the database looping in the new database nodes too.
As workaround i solved it with chunk looping in combination with groupby node.

1 Like