db looping

[Jeff]

I have searched but am confused by “db looping” node. I have studied the examples but not sure I understand it compared to what I need. I will try to simplify what I am hoping to accomplish.

Let’s assume I have a table of transactions which has a few million rows. Included in transaction columns is a customer_id.

My workflow is that I pull from a spreadsheet to get a set of customers (say 10 that includes a customer_id) then I want to pull from transactions where Transaction.customer_id = spreadsheet.customer_id.

Option 1: I could pull customers from spreadsheet and join to transactions but pulling the transactions into knime takes time and hoping I can more or less use db looping to pull just the transactions I need.

Option 2: Another approach I guess is to import the customer_id into the database and then join in database.

I suspect option 2 likely is faster, but I’d like to try option 1 first. The problem is the db looping node does not seem very clear to me.

In the db looping configuration I see three areas:
Database Column List
Column list
SQL query

SELECT * FROM #table# AS “table”

Here’s where it’s a bit fuzzy.

is #table# is my example the transactions table (how to I ensure it is?) Then do I add to query where transaction.customer_id in ($customer_id$) <from spreadsheet?>

I have connected spreadsheet customer_id table to input port 0 and the query of transactions to port 1. If I run it run it, in the end I have to cancel it as it never finishes.

Question 1: Have i missed how to write query in sql query portion?

Question 2: Maybe this is not right approach and I need to use option 2 or another method.

Thoughts?

Thanks,
Jeff

[ipazin]

Hello @Jeff,

DB Looping is a way to go in your case. You just have to get the syntax right.

@Jeff

is #table# is my example the transactions table (how to I ensure it is?)

If the table place holder isn’t changed, then it will be replaced with the query from the database input port. So if you connect DB Table Selector node it will take selected table.

@Jeff

Then do I add to query where transaction.customer_id in ($customer_id$) <from spreadsheet?>

Yes.

Here is screenshot for better understanding:

Hope this helps!

Br,
Ivan

[Daniel_Weikert]

Hi Ivan,
just to make sure. So the DB Looping is just a “wrapper” for creating a concatenated list, put it in a variable and use this in the WHERE clause? "Where xyz is IN ($concatenatedvariablewithcommas) ?
Have not used the node so far so like to ask.
br

[takbb]

You may also find this post useful as a further example, which refers to DB Looping node towards the end

Extract data from db using value from other db

Hi @chiashin , In the example, the DB Writers are only there to populate a sample in-memory database and provide some sample data, so apologies if they are causing some confusion. The purpose of the red flow variable lines between the DB Writers to the subsequent nodes are there purely to enforce the correct sequence of processing so that the demo works (They ensure that the sample data has been written to the tables before attempting to query the data using the DB Query Readers). In place of…

[ipazin]

Hi Daniel,

@Daniel_Weikert

So the DB Looping is just a “wrapper” for creating a concatenated list, put it in a variable and use this in the WHERE clause?

Yep. And pushing down DB query

Br,
Ivan