What is the best way to load from a source table based on a colname - anything greater than a maxvalue of that (<colname> in the destination table). I tried using query result to scalar- but the variable value is not being set with the max value

Matillion ETL
1

What is the best way to load from a source table based on a colname - anything greater than a maxvalue of that ( in the destination table). I tried using query result to scalar- but the variable value is not being set with the max value.

2

Hey VVeda,

 

It seems the community are unable to answer this query, fear not we can ask our amazing solution architects 😀 If you would like me to raise a support ticket on your behalf please let me know and I can arrange that for you.

Thank you for your post.

 

Kind Regards, Joe

3

Hello,

I tried this out and this worked fine for me.

I'm also on Snowflake, and I did the following:

 

Orchestration Job with Job Variable 'Parameter1'.

Use Query Result to Scalar ->

 

select 'Testing from Target' as Col1, 'ValueForParameter' as Col2 from dual

 

Assigned Col2 for Parameter1.

 

 

Next step,

Database Query, as an source I used Oracle Database.

I made this dummy query,

 

select 'Testing' as Col1, '${Parameter1}' as ProbableWhereclause FRom dual

 

and pointed it to my snowflake staging area with a appropriate name.

 

Result:

COL1  PROBABLEWHERECLAUSE 

------- ------------------- 

Testing ValueForParameter  

 

 

So, is this what you tried?

 

4

I tried something similar, I'm also connected to a Oracle Database. Only difference is I did not use a staging table. In the Database query component my sql query had the where condition to check anything > '${parameter}' and loaded it directly into the destination. It worked fine.

Thanks

Vani

5

Hi Joe,

I do have a support ticket open for this, however if you'd like to open another one please feel free. They replied that

variable value is using the default-value to build the SQL and so to change the SQL to use a constant instead of the variable. If this gives the correct result, then its the default for the job-variable which is causing this issue. Are they saying I should not use a default value, not sure. They suggested I review the Snowflake history to see what queries are being fired from matillion - I need to do that today and check. I'll let you know.

thanks

Vani

6

Ah, I thought you were doing some incremental loading and had already loaded the data which you acquire the 'max' value from.

 

Good that you got it working!

-Michael

7

Thats brilliant @VVeda​

Please keep us posted on how you get on, I am looking forward to hearing about it.

Thanks Joe

8

Hey Joe,

This was the response I got from Matillion Support - Its possible the sample button is using the default-value in the variable to build the SQL. Change the SQL to use a constant instead of the variable. If this gives the correct result, then its the default for your job-variable which is causing this issue.

 

I noticed that the default value for the variable can be set after validating the components ( Query result to Scalar) and the other components in the Job ( DatabaseQuery Component). Once the validation is done then you can set a default value and then run each component - you should be now able to see the correct Sample Result set when you run your Query component. It worked for me.

 

thanks

Vani

9

I do have the same question here. I was trying to do an incremental load and I need to get the max value from the destination table then I have to increment the value by one for every new record. Can you suggest the best way of doing it.

10

Try the below: Also remember to validate each component before you actually run the entire Orchestration job. I noticed that by doing so, the variable name will be set correctly.

 

You can define a Job variable that holds the max value of the column from your destination table.

Use a Query to Scalar Component .

Enter the SQL Query (select max(column_1) from your destination table in Snowflake>)

& Scalar Variable Mapping ( map the Job variable to the max(column_1)

Then,

Use a Database Query:

Select * from <table_name> where column_1 = '${job_var}' +1;

 

11

Sorry I couldn't mention, I'm using synapse. What could be the alternate option for Scalar Component?

12

'Query result To Scalar' should be available also in Synapse version of Matillion, due it's an Orchestration component.

https://documentation.matillion.com/docs/en/2978362