10 Replies Latest reply on Apr 2, 2015 10:18 AM by shawkins

    Define source supported aggregate function

    michal.warecki

      Hi guys!

       

      How can I add source supported aggregate function to ExecutionFactory? I've tried addPushDownFunction but I don't see possibility to define aggreagate function. My source system is custom and I'm not using designer.

      I want to have exactly the same sql query in ResultSetExecution class to send it to our custom source system.

       

      Example query:

       

      Select column1, MY_CUSTOM_FUNCTION(column2) FROM table1 GROUP BY column1

        • 1. Re: Define source supported aggregate function
          rareddy

          For general User defined aggregate functions see  teiid: Teiid User Defined Aggregate Support

          I've tried addPushDownFunction but I don't see possibility to define aggreagate function.

          Please explain?

          • 2. Re: Define source supported aggregate function
            michal.warecki

            I don't want to execute that function but just send exactly the same SQL to source system so QueryExpression should present the same SQL as in the input.

            Generally I want to achieve the same behaviour as with "count" function. I've defined that function invoking addPushDownFunction but exception is thrown that column2 is not defined in group by (Select column1, MY_CUSTOM_FUNCTION(column2) FROM table1 GROUP BY column1)...  How can I do it?

            • 3. Re: Define source supported aggregate function
              rareddy

              What you are asking is to push the query down to source, what the Teiid is complaining is your query is not syntactically not correct. May be your translator does not support GroupBy? If you do not want to define the GroupBy then treat the function as regular source UDF?

              • 4. Re: Define source supported aggregate function
                michal.warecki

                Yes, I want to push down to source. Why that query is not correct? Let's say that my function is AVG so I want to group by column1 and get AVG from column2. I set supportsGroupBy to true.

                When I aggregate by the same column as I group by then other exception is thrown: Expression(s) MY_CUSTOM_FUNCTION(column1) cannot be pushed to source.

                • 5. Re: Define source supported aggregate function
                  rareddy

                  Are you using Teiid Designer for VDB or Dynamic VDB? In Dynamic VDB, I know we resolve the functions for pushdown correctly, in Designer there are additional steps involved previously, as you need to define the function on the source model. We were making efforts to fill the gap such that no explicit definition of the function in source model is required, but I am not sure where that stands right now. Depending on your usage, that may be the issue why it says can not be pushed to source.

                   

                  As per the query itself, all I am saying is Teiid parsing thinks there is an issue.  Once you fix the above, then you can take a look at the DEBUG query plans on why it may not getting pushed down.

                  • 6. Re: Define source supported aggregate function
                    michal.warecki

                    I'm not using any designers. I'm using pure Java. Can you write me what should I do (step by step) to push down that aggregate function?

                    • 7. Re: Define source supported aggregate function
                      rareddy

                      Michal, the steps are in documentation. If you are not seeing the push down at the source, then take a look at the DEBUG plan, that might give some clues.

                      • 8. Re: Define source supported aggregate function
                        michal.warecki

                        I've managed to get it working when using the same column as grouping (supportsSelectExpression was set to false). Now I want to get it working with other columns so specify that function as an aggregate function. Now I'm getting error:

                        org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [test.column2] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause. java.lang.RuntimeException: org.teiid.core.TeiidProcessingException: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 [test.column2] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.

                         

                        The query is correct but teiid don't see that function as an agg type. It should act exactly the same as with avg, count, stddev etc. Any ideas?

                        • 9. Re: Define source supported aggregate function
                          michal.warecki

                          To enable that, I had to set aggregateAttributes:

                          function.setAggregateAttributes(new AggregateAttributes())

                           

                          Maybe we should put that into documentation? Is it somewhere?

                          • 10. Re: Define source supported aggregate function
                            shawkins

                            > Maybe we should put that into documentation? Is it somewhere?

                             

                            No, I don't think it's in the docs yet.  We have the aggregate attirbutes documented for DDL and exposed as extension metadata in Designer, but not for code based metadata definition.  It might be good to add a helper method on MetadataFactory, similar to addFunction(String name, Method method) - something like addAggregateFunction(String name, Method method, AggregateAttributes attributes)

                             

                            I'll add something to the docs under the translator api.