-
1. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 10, 2011 9:26 AM (in response to fihtisham)Farrukh,
Do not specify the "pushdown" as "required", set it to "never" and supply a java class implementation of the function. Copy the jar file which contains the function in the "{profile}/lib" directory and restart the server before using it.
Read here http://docs.jboss.org/teiid/7.5.0.Final/developer-guide/en-US/html_single/#udfs
Ramesh..
-
2. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 10, 2011 9:39 AM (in response to rareddy)Thank Ramesh for the prompt help!
we initially started with the non-pushdown udf but our requirement is to access the resultset itself which is not available in case of non-pushdown udf and we have to query again and agiain which is not acceptible in any case. therefore we decided to move to pushdown udf and planned to handle the function in the translator.
Is it possible to get the resultset for which non-pushdown udf is being called?
-
3. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 10, 2011 12:23 PM (in response to fihtisham)Little confusing as what you wanting to do. Are you saying the source does not support the function, but you want handle in the translator?
If, Yes. Then yes you could do configure as the pushdown, then handle in the translator. You would need custom translator for that (or extend one), then in the extended "ExecutionFactory" class, provide
registerFunctionModifier(function, new MyFunction extends FunctionModier)
to convert to a SQL that your source understands, then to intercept the resultset by overriding
retrieveValue (if you are extending the JDBCExecutionFactory)
or if you are extending "ExecutionFactory" then override
public List<?> next()
and return the values you think are correct.
Hope this helps.
Ramesh..
-
4. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 11, 2011 7:25 AM (in response to rareddy)Thanks alot Ramesh!
In the light of your suggestion I am able to handle the pushdown user defined function at translator level.
Greate help!
-
5. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 11, 2011 7:43 AM (in response to fihtisham)Farrukh,
Great news. If possible share your use case as blog some time.
Thanks
Ramesh.. -
6. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 15, 2011 5:46 AM (in response to rareddy)Hello,
I have another scenario where the implementation of pushdown UDF, i have followed, is not working.
When i create a view model from two different source models(means federated view model), then user defined function's functionmodifier does not get invoked. Because of which I see the same error.
"Function ConvertUnit is marked in the function metadata as a function that must be evaluated at the source"
If I remove the federation and keep viewmodel tied to a single source model then it works fine.
Do I need some more modifications regarding this scenario?
-
7. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 15, 2011 10:05 AM (in response to fihtisham)What is the exact error message. It should work in either case.
Ramesh..
-
8. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 19, 2011 4:20 AM (in response to rareddy)"Function ConvertUnit is marked in the function metadata as a function that must be evaluated at the source" is the exact error message.
Let me explain the scenario further.
I have one source model for datasource x and another for datasource y. I have written translator for datasource y. When I create a federated viewmodel using these two datasources and use my user defined function in the query I see the above error. If i remove the datasource x from federated viewmodel everything works fine.
I think the problem is that teiid runtime does not know for which datasource this user defined function should be pushed down.
Thanks & Regards
Farrukh
-
9. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 19, 2011 10:43 AM (in response to fihtisham)Farrukh,
No, it does know. Only in the case that they both the data sources are exposing the same function and function is not fully qualified then there would issue like above. Usally you can use "{translator-name}.ConvertUnit" as the full name. I do not think there is any in built translators support a function called "ConvertUnit" to cause this.
You can post the query plan, we can further diagnose.
Ramesh..
-
10. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 21, 2011 2:37 AM (in response to rareddy)Hi Ramesh,
How can i create the query plan? What I tried is I right clicked on the query in the scrape book, selected Teiid 7.x-> Get Execution Plan from the context menu. Is this the right way to get the execution plan? If yes then it showed only the actual query nothing else.
The problem comes accross even when the underlying datasource is same but the source models are different. Following are the steps.
1-) Consider a data source x
2-) Create two source models y and z in teiid designer
3-) Create a view model federating both x and y
4-) Call the user defined function ConvertUnit(ColumnFromDatasourceX)
This would throw the exception "Function ConvertUnit is marked in the function metadata as a function that must be evaluated at the source"
If I remove the source model y from the federated view model it will start working fine.
Thanks,
Farrukh
-
11. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 21, 2011 10:29 AM (in response to fihtisham)Yes, that is right way to see the query plan inth designer.
Refer to this http://docs.jboss.org/teiid/7.5.0.Final/client-developers-guide/en-US/html_single/#set_statement for seeing query plan with out using the designer.
If you are using the ConvertUnit on a column that is composite of two fields from two different sources then it will be evaluated in the Teiid.
Ramesh..
-
12. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 24, 2011 5:15 AM (in response to rareddy)Hi Ramesh,
We are not using the composite columns. I am attaching a sample project, if you could see it then it might be helpful in identifying the problem.
sample.rar attached to original message contains following;
1. Model project contianing source models, view models and user defined function model
2. Translator project
3. SQL server Database file
4. A text file containing queries
5. A screen shot showing error message
Note: translator is deployed with name "custom"
Thanks for your help.
Farrukh
-
13. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 24, 2011 8:32 AM (in response to fihtisham)I can not seem to open the file uisng zip utility.
-
14. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 24, 2011 8:51 AM (in response to rareddy)I have uploaded a new zip file. Try now