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

Matillion not being able to access UDF

Hi everyone,

I have created a number of UDF on Redshift but it seems like they cannot be called in Matillion. Error being the typical one:
ERROR: function FUNCTION_NAME(bigint) does not exist Hint: No function matches the given name and argument types. You may need to add explicit type casts.

I have tried type casting but no luck. I thought it was because of some permission issues but when I connect directly via a client with the same credential, it works.

Is there anything I am missing?

Thanks.

15 Community Answers

Matillion Agent  

Ian Funnell —

Hi Quindici,

It’s certainly possible to do this, yes. Please find attached some Matillion example jobs which create and use a UDF.

My guess would be that you really do need some datatype casts, since Redshift is quite picky about exact matches. In the attached example the sequence outputs BIGINT, and you have to cast to INT to make the data work with the UDF.

Best regards,
Ian


Quindici Chung —

Hi Ian,

Thanks Ian again! I have tried casting as well but it doesn't work. Also can't seems to find your example? Thanks.

Quin


Quindici Chung —

Just check again and it's still the case. Moreover, under the "Functions" listing in Calculation node there's a folder of "User Defined Functions". I support all the UDF should be available there but it wasn't?

It all seems like something is different with the Redshift connection Matillion is having, even the account is the same?


Matillion Agent  

Kalyan Arangam —

Hi Quin,

Whilst the username/password may be the same, Its possible the matillion Environment is pointing at a different schema. All components in a transformation job default to the schema defined in your environment. Your UDF’s would be visible if the environment was pointing at the schema in which they were created.

Alternatively, you may explicitly prefix function-name with schema-name to use your UDF. For e.g. public.functionname()

Hope that helps.

Best
Kalyan


Quindici Chung —

Hi Kalyan,

Thanks for getting back. Just tried with public.<<function_name>> but still no luck. Is there anything I can try?

Thanks.

Quin


Matillion Agent  

Kalyan Arangam —

Hi Quindici,

The only reason I could think of is that the function was created in a different schema.
Have you tried executing the function outside matillion?

If you have please check the current schema by executing the following SQL – select current_schema();

Also, please check the default-schema specified on the (selected) environment in matillion.

Best
Kalyan


Quindici Chung —

Hi Kalyan,

Yep, you are right. If I changed the default schema to public it is ok. If I add 'public.' in front of the function it does not work but it works again in my own connection. Is there any other thing which restrict the search path, especially with functions? Can't seems to find anything from the web.

Thanks.

Quin


Matillion Agent  

Ian Funnell —

Hi Quin,

Matillion does have a restriction on the search_path, which it sets according to the environment default schema. I think this is why it works when you choose the same schema as your default.

Best regards,
Ian


Quindici Chung —

That's a slight bit of problem as I cannot use the functions in 'calculator' node, and it is not good for all other application to add udfs into a specific schema. Is there a way to set just the schema of the calculator for example?

Thanks.


Matillion Agent  

Laura Malins —

Hi Quin

You can refer to the function in the calculator component which is in a different schema by prefixing the schema name in front of the function. So if your schema is called public but your Matillion environment is against the test schema you can still call the function with public.function_name() in the calculator component. Matillion will only list functions available in the environment schema.

Thanks
Laura


Quindici Chung —

Hi Laura,

Tried prefixing but does not work.

Thanks.

Quin


Matillion Agent  

Laura Malins —

Hi Quin

Provided your Matillion user in the environment has access to the schema that the function is in then you should be able to call the schema. Your schema would need to be in the same database as your Matillion environment. Do you get an error message when you try to reference the function in a different schema? Have you tried calling the function using a Redshift query analyzer tool from the same schema as you’re using in Matillion?

Thanks
Laura


Quindici Chung —

Hi Laura,

If you refer to the previous discussion I had with Ian:
- If I change the default schema to public, it can use the udf. But I cannot set it in this way as do not want to see temporary data in public.
- If I change the default schema to our temp schema, then prefix with 'public', it does not work but saying that there is no such function

Thanks.

Quin


Matillion Agent  

Laura Malins —

Hi Quin

I managed to get it working using our redshift so it sounds like a permissions issue. If it works for you could we have a call to take a look at this? Let me know some times which are best for you and we’ll set something up.

Thanks
Laura


Quindici Chung —

Hi Laura,

Just to close this off. For some reasons after I update to the latest Matillion it works.

Thanks.

Post Your Community Answer

To add an answer please login