Executing a Query periodically

[thentangler]

Hi Everyone,
First time KNIME user here.
I am trying to extract some data from a postgres server and parse it.
First I want to be able to pull the data at periodic intervals. I tried researching online and came across loops and wait nodes, but none of the solutions really work for me.
I am not planning on using KNIME Webserver due to its very high cost.

is there a way i can execute the query in a periodic manner say every 15mins etc?
I cant seem to even execute the nodes manually unless i make some changes to them. So after 15mins when I want to execute and get the updated table, I have to make and revert a change in one of the node to be bale to hit the execute button.

Any advice?

[mlauber71]

@thentangler welcome to the knime forum. I have once proposed an example where a job would execute every x minutes for a given amount of times.

Start a Workflow, e. g. a database query, after a certain time

@USCHUKN1ME I tried to build a system you might be able to expand and use. The idea is to have a workflow that would iterate a number of times (v_number_of_intervals) with a set interval (of seconds) between (v_seconds_interval) them. The workflow looks for a trigger_file.csv if this file has changed from the last time the loop has run a component would be executed. If the file has not changed nothing would happen. Admittedly not the most elegant way but you could build a small automatic execu…

Also you could try the recursive loop with a condition that is never met and a wait node.

Recursive Looping – knime

Recursive Loop – rs1

[eamendola]

Hi there @thentangler, welcome to the KNIME forum.

Let’s try to understand your need.

The idea is to execute the data extraction/query nodes every 15minutes, without manual intervention ?

And of course, if you want to “re-execute” the data extraction, you need to reset the workflow and execute it again.

I would recommend you to plan your workflow to do the following (without loops and waits):

1. PG Connector
2. DB Query Reader
3. Changes/Transformations
4. Write results into a file or database

You could achieve this executing your workflow with batch execution and Windows Task Scheduler, no splash screen, no visual interface, workflow resetting, etc. Automatic execution. Take a look here:

FAQ | KNIME ( Is there any way to run KNIME in batch mode, i.e. only on command line and without the graphical user interface?)

[thentangler]

I was able to kinda make it work with the iris example. Thank you!
Is there a way to se a counter so i know how many times the loop has run? This is for debugging purposes in case it fails an n’th time, I would like to know what n is.
I tried using math formula, but it takes the input from the previous node, which is a table… how do i initialize a value and have it iterate through the loops?
Thanks again!

[mlauber71]

@thentangler

This is for debugging purposes in case it fails an n’th time, I would like to know what n is.

You could just create a TXT file where the “n” is in the file name. Not very elegant but you could always check on the hard-drive (make sure to delete the old one …)

[thentangler]

Thank for that suggestion, I would actually like to use this method since it would not involve me having to manually trigger it and leave KNIME open on my machine. However i am an infant with regards to KNIME.
I tried running knime in command prompt, but I get the following screenshot below. I used the following syntax and changed the path where my KNIME application was installed.

knime -nosplash -application org.knime.product.KNIME_BATCH_APPLICATION -workflowDir=“C:\Program Files\KNIME\Auto_testing”

Auto_testing is the name of my workflow. I tried with both front and backslashes

Could you point me to any examples or resources where they have integrated windows task scheduler with Knime along with the command prompt execution?
Thank You

[eamendola]

Please refer to this discussion. The talk about a couple of error codes when executing in batch mode.

KNIME Batch File ERROR

Hi @gua_gunners - Exit code 2 occurs if parameters are wrong or missing. So the problem might be that you have some workflow variables that you need to provide values for. Just FYI, exit code 3 indicates when an error occurs during loading a workflow, and exit code 4 indicates if an error during execution occurred.

Let me know if this helps. Otherwise we can take a deeper look into your workflow.

[thentangler]

Hi @eamendola, my workflow is very simple, as shown in the screenshot below. It just has a PG connector, DB reader and it spits the resulting table to a csv file that is written to a path in my local drive.
All parameters are hardcoded inside the nodes.
What other parameters would I need?

[bruno29a]

Hi @thentangler , try to add the parameter -reset in your command line. It will make sure that the workflow is reset before it’s executed. You probably want to add -nosave also.

You may consider create a batch file (.bat file) to encapsulate your command, and then schedule your Task Scheduler to execute that batch file.

I would also recommend that you move your workspace outside of the installation path of Knime. Your Knime is most probably installed in C:\Program Files\KNIME\ and it looks like your workflow also resides there. It’s better to keep them separate so that you can update your KNIME without risking deleting your workflows.

[badger101]

Thank you for asking this question. I’m not a coder and I didn’t even know what batch processing was. This thread made me realize the potential of KNIME to be used differently as I would normally do.

Upon digging up this “topic” further, I was able to find another thread that might be of interest to some of the questions you asked here too - hope it helps!

decent documentation for command line execution with variables?

Here is what I figured out (True, this information is outlined in the FAQs, there are some finer points that need better explaining) this blogpost was extremely helpful (Batch Execution of KNIME Workflows, Part 2 –) ============================================================= First define your workflow variables that you will call/modify from the command line: with your workflow of interest open, right click on your workflow of interest from within the KNIME Explorer panel. Then in the mid…

[badger101]

Get on Board and Navigate the Learning Options at KNIME | KNIME

On another note, I’d like to take the opportunity to suggest to KNIME Developers to provide an overview of resources focusing on Batch Execution with KNIME as you’d already done with Text Mining as exemplified in the link above. It will certainly make things more organized!

Also, to add this as a topic under the KNIME Documentation here: https://docs.knime.com/ to make it more searchable-friendly

[thentangler]

Thank you for this suggestion!
I have moved my workspace.
It still doesnt seem to work. In the FAQ and other examples the workflowDir has “/” in the path. But windows directory structure has “”. Should i put it as WorkflowDir=“C:\Knime_Workspace/Auto_testing”? or “C:\Knime_Workspace\Auto_testing”?
Where Auto_testing the name of my workflow.

[thentangler]

Hi @badger101 ,
Thank you. This worked.
I created a silly workflow variable like test, integer, 1 and now it seems to work!
I dont know why my workflow would require that!
Between yours and @bruno29a I was able to get it to work.
Thank you!

[badger101]

I’m glad to hear that! Anyway, do you have any idea why in the third point of that thread, the author puts this code:

-workflow.variable=myvariable1,999,int -workflow.variable=myvariable2,foo,String

in such a way? Am I correct to assume that each -workflow.variable is for each setting of the nodes in the workflow containing a variable?

[eamendola]

Yes @badger101 that is right.

If you have multiple defined variables, you should build your command with one -workflow.variable for each one of them with its name, value and type.

[eamendola]

@thentangler
Glad to hear that you get it to work. Just to add information, I have multiple workflows running on batch mode, with a command template in a .bat file like this:

cd C:\Program Files\KNIME\

knime
-application org.knime.product.KNIME_BATCH_APPLICATION
-nosplash
-reset
-workflowDir=“C:\Users\user\Knime_Workspace\workflow_group\workflow_name”

<workflow_group> is optional, only add to the path if you encapsulate your workflows in a folder/group. I do not add a final “\” to the path

[badger101]

Alright @eamendola That would mean if my workflow doesn’t have any variable Nodes, I can totally disregard (i.e. discard) that line of command right?

[eamendola]

That’s right.
Check my previous answer, that’s my command for workflows without a parameter initialization/override default. I do have variables inside some workflows, but it isn’t always the case that I need to override them via command line. Sometimes they could be calculated inside the workflow.

[thentangler]

Thank you @eamendola , This really helps!

[thentangler]

I still dont know what KNIME means when it says “variables”.
I would be having many variables in my nodes, I’ll try to learn all that in the tutorials…hopefully!

@badger101 Jus tto clarify, the variable specification i did was in the KNIME workspace by right clicking on my workflow and setting the variables there. I did not use the -workflow.variable switch in the command line.