Skip to main content
Solved

Can we have a “JDBC Reader” which can read dynamic queries?

D
Data Enthusiast
Forum|alt.badge.img+2
  • dramsData Enthusiast
  • Data Enthusiast
  • 1 reply

imagine we have 100 views for which we need to find the count difference in 2 different db’s .we need to create a dynamic query in a while loop which looks up view names one by one and runs until the requirement is met.

 

 I have a few questions to ask :

  1. Can we have a “JDBC Reader” which can read dynamic queries? For example, using a “Global Variable” in the JDBC Reader query to pick up view_names for every iteration.
  2. As per the solution above, we need to create a while loop to cater multiple iteration. Is there any method/function in Ataccama to achieve this?

 

Best answer by Pele

Hi Siva,

it is possible, and we sometimes use it like that, to have a DERIVED variable in a workflow that represents the SQL query string. Since derived variables in workflows work with expressions, you can achieve pretty complex logic to build your queries even combining other derived or input workflow variables.

Once your derived variable is ready, you can pass it to the Run DQC workflow task as a parameter - pay special attention on which workflow tasks work with expressions and/or semi-expressions so you set the parameter mapping using the right syntax (check our online documentation for more details).

If your query result set is similar to what I described in my previous post, you should be easily able to map the JDBC Reader step’s Query field as parameter (it would look like the query field is actually empty, but there’s a little icon next to the “Query” text indicating the parameter mapping).

Finally, the query result set needs to map to your JDBC reader step columns that are expected to be in the SQL output.

Same would apply to SQL Select step.

Hope this clarifies.

View original
Did this topic help you find an answer to your question?

3 replies

P
Ataccamer
Forum|alt.badge.img+1
  • PeleAtaccamer
  • Ataccamer
  • 13 replies
  • October 25, 2022

Hi Drams,

interesting use case. Let me split the answer in a few bullet-points:

  • There are no “WHILE” loop options in Ataccama (as in programming languages), however, you can rethink about your scenario as an FOR loop or FOR-EACH loop that could be doable using an Orchestration workflow and the Iterate Task
  • You can’t define any loops in Ataccama plans or components
  • You can define a parametrized query (SQL Select) to a JDBC reader as long as the output result set is always the same (e.g. count of lines and a table/view name as a string literal)
  • For your scenario you can think of a 2 workflows using the Iterate task mentioned above with a SQL Row iterator (query your DB metadata to get the list of views you need to check) and then have a child workflow (triggered by the iterator) to execute the actual component using the Run DQC task. You can store the results from the component into a DB table or some “append-friendly” store

Hope this helps at a high-level. For more help you can reach out to our professional services to deliver the implementation for you.

DannyRyan
1 person likes this
  • DannyRyan
    DannyRyan
S
Data Pioneer L2
Forum|alt.badge.img+3
  • Siva_MadhavanData Pioneer L2
  • Data Pioneer L2
  • 31 replies
  • October 26, 2022

Hi @Pele We have a similar use case and I have a question on the above:

On Point 3: Does Ataccama allows parameterizing whole query? As far as my testing, I could only parameterize part of the query and not the whole query in SQL Select or SQL Execute. So the point 3 to found counts will not work. Can can you clarify?

 

 

P
Ataccamer
Forum|alt.badge.img+1
  • PeleAtaccamer
  • Ataccamer
  • 13 replies
  • Answer
  • October 26, 2022

Hi Siva,

it is possible, and we sometimes use it like that, to have a DERIVED variable in a workflow that represents the SQL query string. Since derived variables in workflows work with expressions, you can achieve pretty complex logic to build your queries even combining other derived or input workflow variables.

Once your derived variable is ready, you can pass it to the Run DQC workflow task as a parameter - pay special attention on which workflow tasks work with expressions and/or semi-expressions so you set the parameter mapping using the right syntax (check our online documentation for more details).

If your query result set is similar to what I described in my previous post, you should be easily able to map the JDBC Reader step’s Query field as parameter (it would look like the query field is actually empty, but there’s a little icon next to the “Query” text indicating the parameter mapping).

Finally, the query result set needs to map to your JDBC reader step columns that are expected to be in the SQL output.

Same would apply to SQL Select step.

Hope this clarifies.

Reply


Most Liked this week

Community Terms of Use & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings