1 Reply Latest reply on Jun 24, 2014 7:55 AM by Steven Hawkins

    how to execute a sql in teiid

    文芳 张 Newbie

      hello:

            i read the chapter Planning Overview - Teiid 8.7 - Project Documentation Editor ,but i haven't understood the plan how a integrate sql execute ..

           eg. i have two sybase datasources which are in multi-source mode . and when i execute a sql : select hour(start_time),count(0) from test group by hour(start_time) .

          

          step 1: Parsing - validate syntax and convert to internal form ,so check if syntax is correct and   covert to internal form : select hour(start_time) from test group by hour(start_time) ?

          step 2 :Resolving - link all identifiers to metadata and functions to the function library   ,check if there are function hour() in sybase translator?

          step 3:Validating - validate SQL semantics based on metadata references and type signatures    ,check if the sql is right by sybase metadata?

          step 4:Rewriting - rewrite SQL to simplify expressions and criteria   i don,t know how to rewrite the sql ? and why it need rewrite sql?

          step 5:Logical plan optimization - the rewritten canonical SQL is converted into a logical plan for in-depth optimization. The Teiid optimizer is predominantly rule-based. Based upon the query structure and hints a certain rule set will                  be applied. These rules may trigger in turn trigger the execution of more rules. Within several rules, Teiid also takes advantage of costing information. The logical plan optimization steps can be seen by using SHOWPLAN                        DEBUG clause and are described in the Query Planner section. maybe  this optimization  decide how the sql transfer to datasource?

          step 6:Processing plan conversion - the logic plan is converted into an executable form where the nodes are representative of basic processing operations. The final processing plan is displayed as the Query Plans. i didn't understand this step

       

       

      so  can  someone explian that clearly by using my example?  thanks a lot ....

        • 1. Re: how to execute a sql in teiid
          Steven Hawkins Master

          > convert to internal form ,so check if syntax is correct and   covert to internal form : select hour(start_time) from test group by hour(start_time) ?

           

          Yes, we create an object form of the query based upon the classes in org.teiid.query.sql.lang

           

          > ,check if there are function hour() in sybase translator?

           

          Not exactly.  At this stage we don't really care where the function is defined, we just need all tables/columns/functions/etc. to reference their correct metadata object and type.

           

          > ,check if the sql is right by sybase metadata?

           

          Again that is too low level.  Validation is like static analysis of the resolved form.  Once we know types and other information we can check for issues with otherwise syntactically valid sql - for example ordering by a non-comparable type, inserting into a non-updatable table, etc.

           

          > i don,t know how to rewrite the sql ? and why it need rewrite sql?

           

          You want to rewrite so that the sql is in a more canonical/optimal form.  For example changing right outer joins to left outer joins helps simplify the optimizer as there's one less case to consider.

           

          > maybe  this optimization  decide how the sql transfer to datasource?

           

          You should look at the SHOW PLAN output to see what the optimizer is doing.  Once expressed as a plan tree the SQL operations can be manipulated in a more flexible manner as to criteria placement, join structure, etc. before ultimately yes determining what the exact pushdown form of the source commands will be.  See the classes under org.teiid.query.optimizer

           

          > . i didn't understand this step

           

          The resulting plan tree from the previous planning step is not an executable construct.  It must be turned into a processing plan, with related processing classes to run the query.  See the classes under org.teiid.query.processor