Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Store query result to variable

Is there any way to store query result to variable in Transformation job ?

I want to save single output value from SQL component to variable and the use it in my orchestration job

3 Community Answers

Matillion Agent  

Paul Johnson —

Hi Hanumanth,

Yes you can do this by using the python scripting component in an orchestration job and using the Jython interpreter. An example of storing the rowcount of mytable in a variable would be:

cursor = context.cursor()
cursor.execute(‘select count(*) from mytable’)
rowcount = cursor.fetchone()0

Hanumanth Andela —

Thanks Paul, The SQL component operation is based on preceding component (detect Changes component), so can we use mytable as $T{input-name} in python component ?

Matillion Agent  

Kalyan Arangam —

Hi Hanumanth,

You cannot store the results of a transformation job directly into a variable. Any SQL generated is pushed to Snowflake and we do not retrieve any results.

For your requirement, I would recommend using a CreateView transformation component to create a view with the necessary sql and then use the python script component in your orchestration job to read a value from this view and assign to a local variable.

Paul has already share the code that may be used in your python component to read from Snowflake.

Hope that makes sense.


Post Your Community Answer

To add an answer please login