Question

Is there any SQL transformer that doesn't need any initiator but only needs a trigger/input to connect to previous step?

  • 25 November 2019
  • 9 replies
  • 0 views

Badge

Is there any SQL transformer that doesn't need any initiator but only needs a trigger/input to connect to previous step? I don't need SQL Executor which requires an initiator that involves in the query. I can't use SQL Creator because it doesn't have an input handle that can connect to previous step sequentially.

any advice?


9 replies

Userlevel 2
Badge +12

You could use the FeatureReader and use a SQL statement instead of a table.

Hope this helps.

Badge

Do you mean "SQL Run Before" or "SQL Run After"? Doesn't FeatureReader still require to fill in "Feature Type to Read" to complete the transformer?

Badge +3

I would have thought you could use SQLExecutor if the data is external to the Workspace? It just needs a single feature to trigger it once and once only, which can be anything. You can use Eg. the Summary Port of any preceding Transformer like a FeatureReader, which typically outputs a single Summary Feature on this Port, or something a little more expensive but generic like Sampler set to return the 1st feature only out of the output of a preceding transformer. If instead you want to perform generic SQL inline with Workspace Transformer data Outputs, then this can be accomplished alternatively by feeding this data into InlineQuerier and executing (SQLite) SQL on it there.

Badge

I would have thought you could use SQLExecutor if the data is external to the Workspace? It just needs a single feature to trigger it once and once only, which can be anything. You can use Eg. the Summary Port of any preceding Transformer like a FeatureReader, which typically outputs a single Summary Feature on this Port, or something a little more expensive but generic like Sampler set to return the 1st feature only out of the output of a preceding transformer. If instead you want to perform generic SQL inline with Workspace Transformer data Outputs, then this can be accomplished alternatively by feeding this data into InlineQuerier and executing (SQLite) SQL on it there.

Thanks for your comments. How do we make sure the SQL Executor doesn't get initiated too early? For instance, sometimes I saw SQL Executor would get executed from the first feature but my previous query hasn't been completed yet. Currently I'm using Custom Transformer to solve this type of problems.

Badge +3

I would have thought you could use SQLExecutor if the data is external to the Workspace? It just needs a single feature to trigger it once and once only, which can be anything. You can use Eg. the Summary Port of any preceding Transformer like a FeatureReader, which typically outputs a single Summary Feature on this Port, or something a little more expensive but generic like Sampler set to return the 1st feature only out of the output of a preceding transformer. If instead you want to perform generic SQL inline with Workspace Transformer data Outputs, then this can be accomplished alternatively by feeding this data into InlineQuerier and executing (SQLite) SQL on it there.

If all your preconditions are simply upstream Output Ports, then you can run all of the Output Ports that need to be completed first to a FeatureHolder which will not output anything until all upstream Ports have completed their output. This can then be run to a Sampler to return a single initiating Feature to trigger the SQLExecutor

Badge

If all your preconditions are simply upstream Output Ports, then you can run all of the Output Ports that need to be completed first to a FeatureHolder which will not output anything until all upstream Ports have completed their output. This can then be run to a Sampler to return a single initiating Feature to trigger the SQLExecutor

thanks, I like this approach.

Badge

I would have thought you could use SQLExecutor if the data is external to the Workspace? It just needs a single feature to trigger it once and once only, which can be anything. You can use Eg. the Summary Port of any preceding Transformer like a FeatureReader, which typically outputs a single Summary Feature on this Port, or something a little more expensive but generic like Sampler set to return the 1st feature only out of the output of a preceding transformer. If instead you want to perform generic SQL inline with Workspace Transformer data Outputs, then this can be accomplished alternatively by feeding this data into InlineQuerier and executing (SQLite) SQL on it there.

one last question, does the "Initiator" output always comes after all queries have been completed within SQL Statement? in other words, if I have a series of updates that happen within SQL Executor, is it certain that "Initiator" output would always wait till everything is completed and then get a value? In my case, nothing is expected to be collected based on the queries from initiator output, it'll simple be the same value that comes in from initiator input.

Badge +3

one last question, does the "Initiator" output always comes after all queries have been completed within SQL Statement? in other words, if I have a series of updates that happen within SQL Executor, is it certain that "Initiator" output would always wait till everything is completed and then get a value? In my case, nothing is expected to be collected based on the queries from initiator output, it'll simple be the same value that comes in from initiator input.

According to SQLExecutor's documentation, the Initiator Output Port is the Initiator Input Features plus a new attribute "_matched_records" that is a count of the number of features (records) that are returned by the SQL query (If they are UPDATE statements, then in most cases this will likely be 0). As the SQL in SQLExecutor needs to finish executing first in order to count how many "_matched_records" there are, then this suggests the SQL needs to finish first before anything appears on the Initiator Output port.

Badge

According to SQLExecutor's documentation, the Initiator Output Port is the Initiator Input Features plus a new attribute "_matched_records" that is a count of the number of features (records) that are returned by the SQL query (If they are UPDATE statements, then in most cases this will likely be 0). As the SQL in SQLExecutor needs to finish executing first in order to count how many "_matched_records" there are, then this suggests the SQL needs to finish first before anything appears on the Initiator Output port.

Thanks a lot.

Reply