1 2 3 Previous Next 33 Replies Latest reply on Dec 2, 2015 10:29 AM by jie tao

    virtual procedure SQL transformation

    jie tao Novice

      I am creating procedures for creating REST WAR. As I have this:

       

          BEGIN

              SELECT XMLELEMENT(NAME test, XMLAGG(XMLELEMENT(NAME test, XMLFOREST(SUM(table.colum1)))) AS result FROM table

        END

       

      ERROR: Nested aggregate expressions are not allowed: [SUM(table.colum1)]

      ERROR: Non-column expressions require a name in XMLATTRIBUTES, XMLFOREST, QUERYSTRING, or TEXTAGG with HEADER

      ERROR: Non-column expressions require a name in XMLATTRIBUTES, XMLFOREST, QUERYSTRING, or TEXTAGG with HEADER

       

      Can I create sum from a column?

       

      Is there any guide about the syntax in the transformation editor?

        • 1. Re: virtual procedure SQL transformation
          Steven Hawkins Master

          > Can I create sum from a column?


          The engine is just looking for an explicit name for the element - XMLFOREST(SUM(table.colum1) AS something)


          > Is there any guide about the syntax in the transformation editor?


          The direct link to the latest is XML Functions - Teiid 9.0 (draft) - Project Documentation Editor

           

          Or you can see the whole doc set at Documentation · Teiid



          • 2. Re: virtual procedure SQL transformation
            jie tao Novice

            Thanks. It works now.

            Another question: I have an input parameter in my procedure of type dateTime. As I "preview data" I give "2002-05-30T09:00:00" in the "input parameter" wizard of Teiid designer. I got this: 

             

            org.teiid.runtime.client.teiidClientException: unnable to convert procedural parameter .... of type string to expected type timestamp

             

            It seems that Teiid takes my input as a string. How to correctly give the input or can I not define a parameter as dateTime?

            • 3. Re: virtual procedure SQL transformation
              Steven Hawkins Master

              > It seems that Teiid takes my input as a string. How to correctly give the input or can I not define a parameter as dateTime?


              Even if the design time type is dateTime, unless this procedure is invoked from a web context the parameter will be understood as the runtime type timestamp.  So while 2002-05-30T09:00:00 is the iso datetime format,  the expected sql timestamp format does not include the T.

              • 4. Re: virtual procedure SQL transformation
                jie tao Novice

                I changed type to string. This is easy for preview. Other question: I have many variable computation in the transformation editor of the virtual procedure. How can I test whether the computation is correct (the variables get correct values)?

                • 5. Re: virtual procedure SQL transformation
                  Steven Hawkins Master

                  There isn't yet a debugger mode for designer.  A workaround is to add logging statements to the logging system procedures in your sql - System Procedures - Teiid 9.0 (draft) - Project Documentation Editor

                   

                  Then you can see log messages of exactly what you are looking for. 

                   

                  Also at a trace level you will see all variable assignments in the log, but it will be quite verbose.

                  • 6. Re: virtual procedure SQL transformation
                    jie tao Novice

                    Thx. I added EXEC SYSADMIN.logMsg(level => 'ERROR', context => 'org.something', msg => VARIABLES.h1fr)

                     

                    but I do not see anything in my error log. Where is the log? Is it correct to put the variable (which I want to observe" in msg?

                    • 7. Re: virtual procedure SQL transformation
                      Steven Hawkins Master

                      > Where is the log?

                       

                      It will be in the server log - for example standalone/log/server.log

                       

                      > Is it correct to put the variable (which I want to observe" in msg?

                       

                      Yes, the message can be any string/value of what you want printed in the log.

                       

                      See also Logging - Teiid 9.0 (draft) - Project Documentation Editor

                      • 8. Re: virtual procedure SQL transformation
                        jie tao Novice

                        I noted that teiid designer modified my "select" statement written in the transformation editor (e.g., add date_format, ...). This is a problem for our database where we have to "force primary key" and others. Is it possible to let teiid take the same SQL statement as I written in the transformation editor?

                        • 9. Re: virtual procedure SQL transformation
                          Steven Hawkins Master

                          The sql statement you write in Designer is in Teiid SQL, which will go through several stages of optimizations before getting to the source sql.  There are some mechanisms for associating hints with the source query - such as the source hint mechanism (see Federated Optimizations - Teiid 9.0 (draft) - Project Documentation Editor source hint), which works for non-positional hints, or you can use a direct query procedure to by pass Teiid altogether.  Another option is to use extension metadata and translator customizations to handle additional manipulations to source sql.

                          • 10. Re: virtual procedure SQL transformation
                            jie tao Novice

                            Thanks for the answer. My use case is: I need a web service API to access our databases (mysql and Hive). The easiest way is to create rest war using teiid designer via creating virtual procedures that only works with a view model. But I want my queries (without teiid modification) going directly to my database source. One of your solution above "use a direct query procedure to by pass Teiid altogether": how can I do this in my case? 

                            • 11. Re: virtual procedure SQL transformation
                              jie tao Novice

                              I studied a little bit and see one solution:

                               

                              create native query procedures to my source and then "exc" the procedure in my virtual procedure, right? Question:

                               

                              1. my query is created on-the-fly: I have to use JAVA to create an SQL string and give back the string to my virtual procedure. If I understand correctly, I must give the exact query in the native query procedure. How shall I work around?

                              2. my native query is not simple: I select multiple columns, join several tables and union results. Possible to use native procedure for this?

                              • 12. Re: virtual procedure SQL transformation
                                Steven Hawkins Master

                                > my query is created on-the-fly: I have to use JAVA to create an SQL string and give back the string to my virtual procedure. If I understand correctly, I must give the exact query in the native query procedure. How shall I work around?

                                 

                                There are multiple flavors to native/direct queries.  One is to define a procedure/table with extension metadata of what is actually going to be executed at the source.  The other is to enable the direct query procedure on your translator and then you'll have access to a Teiid procedure that can be used to directly query the source.  If the manipulation that you need to do is parameterizable, then you could use a procedure with the native-query extension metadata.  If the manipulation that you need to do is filtering, projection, etc. against a common base query, then you can use a view with the native-query extension metadata.  If not, then you'd have to use the direct query procedure.  See JDBC Translators - Teiid 9.0 (draft) - Project Documentation Editor under native queries and direct query procedure.

                                 

                                > my native query is not simple: I select multiple columns, join several tables and union results. Possible to use native procedure for this?

                                 

                                It doesn't really matter what it does as long as it's all against a single source.

                                • 13. Re: virtual procedure SQL transformation
                                  jie tao Novice

                                  thx. I enabled the direct query procedure with an override translator for my VDB. I do in the transformation editor of a virtual procedure:

                                   

                                  BEGIN

                                  SELECT x.kid, x.visits FROM (EXEC RealMySQL.native('SELECT ......')) AS w, ARRAYTABLE(w."output" COLUMNS kid string, visits long) AS x;

                                  END

                                   

                                  I got error from the transformation editor: ERROR: java.lang.Exception: RealMySQL.native does not exist. (where RealMySQL is my source model that is included in my VDB).

                                   

                                  Based on the document, "JDBC translator provides a procedure to execute any ad-hoc SQL query". So, I need not writing the procedure "native".

                                  What is the problem?

                                  My virtual procedure is combined with a virtual view transformed from my source model RealMySQL

                                  • 14. Re: virtual procedure SQL transformation
                                    Steven Hawkins Master

                                    > I got error from the transformation editor: ERROR: java.lang.Exception: RealMySQL.native does not exist. (where RealMySQL is my source model that is included in my VDB).

                                     

                                    If Teiid Designer does not have an option to add a direct query procedure, then you would need to add one manually or use the Teiid Connection Importer against a translator that is configured with the option enabled - although from your earlier experience I gather that Teiid Designer does not allow setting translator properties for the import process so that may not work.

                                    1 2 3 Previous Next