Teiid PUSHDOWN UDF function question

[kchen007]

We are using Teiid 8.7 runtime, and using Teiid Designer 8.6 to define UDF function that will call database native function. but Teiid Designer did not give me the option to do it. see attached image.

the old version can do it, Is there any way to achieve this?

thanks

Kevin

[rareddy]

If a function needs to be pushed down to the source, then it must be defined in on the source model. When a UDF defined on the view model, then you need to supply the implementation specifics and that will not be push down.

[blafond]

See: Chapter 6. Creating and Editing Model Objects

Use the Source Function option. Once defined (model saved) the source function will be available in the Transformation Editor's Expression Builder wizard for use in your SQL.

[akshy_harale]

Hello Ramesh,

I tried the PUSHDOWN UDF to call native function in sqlserver NEWID() to get unique identifier by referring this document: but i get the following error when
I call the function from VIEW (like this SELECT NEWID() AS UniqueID. Function is defined in source and not having any java implementation) :

org.teiid.api.exception.query.ExpressionEvaluationException: TEIID30328 Unable to evaluate NEWID():
     TEIID30341 Function MSSql.NEWID is marked in the function metadata as a function that must be evaluated at the source.

          at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:598) [teiid-engine-8.4.0.Final.jar:8.4.0.Final]

          at org.teiid.query.processor.relational.ProjectNode.updateTuple(ProjectNode.java:190) [teiid-engine-8.4.0.Final.jar:8.4.0.Final]

[rareddy]

Try qualifying the NEWID() like "model.NEWID()"

[akshy_harale]

I tried as what you said but still getting the same error.

[shawkins]

It is resolving appropriately.  It's an issue of planning.  The plan that is being created probably cannot push the function to the source and thus the exception.  With a later version of Teiid there is more planning specifically for pushing must pushdown functions and even if it can't be pushed but the source supports values or a select without a from, then we'll still evaluate the function.

[jietao]

can we work around with this exception in Teiid designer? I called the Hive get_json_object() method in my virtual procedure and got also this TeeidSQLException: Function Hive13.get_json_object is marked in the function metadata as a function that must be evaluated at the source. I use Teiid-8.12.2

[shawkins]

> can we work around with this exception in Teiid designer? I called the Hive get_json_object() method in my virtual procedure and got also this TeeidSQLException: Function Hive13.get_json_object is marked in the function metadata as a function that must be evaluated at the source. I use Teiid-8.12.2

It would be good to start a new thread.  We'll need to confirm that the function is properly defined for pushdown and ideally have the query plan as well.