Database table: read->update|write->re-read

[VitalyD]

Hi community!

I am writing a program that should collect data form a web API and store it in a database (PostgreSQL). The implementation follows the algorithm below:

1) Obtain data from a web API;

2) Check if particular fields are present in the existing table in the database;

3) Update the fields if they are missing;

4) Re-read the table, because id's of records are generated automatically by the database;

5) Write data to another existing table using the id's from the previous table.

The task in itself is simple.

However, I am experiencing an issue with writing and re-reading of data from the table because of "unpredictable" sequence of execution.

If I place several In-tools on canvas, even if one of them is inside a macro, they are executed simultaneously.

At the same time, several Out-tools do write values into the table, but I cannot be sure when they do this.

As I have found out from reading various posts here in discussions, that is difficult to control which tools are executed first, particularly in/out tools.

Therefore, to have a better overview I have inserted several Python-tools (scripts) to interact with the database, and the rest is performed with the Alteryx own tools.

Recently, I have also experienced that Python-tools contain no code when I start the flow after a weekend.

Could any one suggest a number of tools without using Python to perform the task?

[afv2688]

Hello @VitalyD ,

Maybe the best way to control within a flow would be to keep adding batch macros one after the other to have a better control of the flow. With batch macros, until the first one is finished it won't execute the next one. You would need to find a way to concatenate those macros one after the other in the expected way.

Try to do something like this 🙂

Regards

[csclark]

Hey Vitaly,

Have you checked out the Alteryx tool Block Until Done? You might be able to configure it to do as you've suggested.

Use can use the Block Until Done to stop datasets from moving downstream until the last record in the set has been processed by all previous tools. In addition, this tool ensures that only a single output stream at a time receives the completed dataset. Subsequent streams are blocked until all the dataset records are pushed through the 1st stream.

Example of outputting to a file and re-inputting it within the same workflow

Cheers,

Chris

[VitalyD]

As I understood the semantics of any In-tool, it is considered as a starting point and will be executed at the beginning of a wokflow, or a bit later, depending on the load. Therefore, I have not tried "Block Until Done" with this workflow, only with some simple In-Out program. And it does not seem to do what is expected.

I am thinking if one could use tools from In-Database for such cases? But their usage is unclear to me.

[VitalyD]

Unfortunately, you cannot read a table in a following macro after the previous one had been executed. I did this and several macro just became green, In-tool was activated, before the data has reached them.