1 2 Previous Next 23 Replies Latest reply on Jun 9, 2014 5:41 AM by 724150880

    jboss and teiid Federated Plans

    724150880
    724150880 May 19, 2014 4:07 AM

      hello ,

           now i use jboss AS 7.1 with teiid 8.2 。And after i see the Federated Plans chapter in Federated Planning - Teiid 8.2 - Project Documentation Editor

      i knew the plan of executing a query by muti-source. this is part of my programmes :

           String url = "jdbc:teiid:myvdb@mm://192.168.7.83:31000";

            String sql = "select hour(start_time) cnt  From GPRS_USER_SERVICE_BDR030427 where roam_city=20 group by hour(start_time)";

            try

            {

            Class.forName("org.teiid.jdbc.TeiidDriver").newInstance();

            Connection connection = DriverManager.getConnection(url, "user", "user");

            Statement statement = connection.createStatement();

            statement.execute("set showplan on");

            statement.execute("set partialResultsMode true");

            ResultSet results = statement.executeQuery(sql);

            TeiidStatement tstatement = statement.unwrap(TeiidStatement.class);

            PlanNode queryPlan = tstatement.getPlanDescription();

            System.out.println(queryPlan);}

       

      and follow is my output plan:

      ProjectNode

        + Output Columns:cnt (integer)

        + Statistics:

          0: Node Output Rows: 2

          1: Node Process Time: 0

          2: Node Cumulative Process Time: 266

          3: Node Cumulative Next Batch Process Time: 37

          4: Node Next Batch Calls: 48

          5: Node Blocks: 47

        + Cost Estimates:Estimated Node Cardinality: -1.0

        + Child 0:

          GroupingNode

            + Output Columns:gcol0 (integer)

            + Statistics:

              0: Node Output Rows: 2

              1: Node Process Time: 12

              2: Node Cumulative Process Time: 266

              3: Node Cumulative Next Batch Process Time: 37

              4: Node Next Batch Calls: 48

              5: Node Blocks: 47

            + Cost Estimates:Estimated Node Cardinality: -1.0

            + Child 0:

              UnionAllNode

                + Output Columns:start_time (timestamp)

                + Statistics:

                  0: Node Output Rows: 24306

                  1: Node Process Time: 0

                  2: Node Cumulative Process Time: 254

                  3: Node Cumulative Next Batch Process Time: 5

                  4: Node Next Batch Calls: 69

                  5: Node Blocks: 57

                + Child 0:

                  AccessNode

                    + Output Columns:start_time (timestamp)

                    + Statistics:

                      0: Node Output Rows: 12153

                      1: Node Process Time: 226

                      2: Node Cumulative Process Time: 226

                      3: Node Cumulative Next Batch Process Time: 3

                      4: Node Next Batch Calls: 35

                      5: Node Blocks: 29

                    + Query:SELECT g_0.start_time FROM mysql5.zhangwf.GPRS_USER_SERVICE_BDR030427 AS g_0 WHERE g_0.roam_city = 20

                    + Model Name:mysql5

                    + Model Name:mysql5

                + Child 1:

                  AccessNode

                    + Output Columns:start_time (timestamp)

                    + Statistics:

                      0: Node Output Rows: 12153

                      1: Node Process Time: 254

                      2: Node Cumulative Process Time: 254

                      3: Node Cumulative Next Batch Process Time: 2

                      4: Node Next Batch Calls: 63

                      5: Node Blocks: 57

                    + Query:SELECT g_0.start_time FROM mysql5.zhangwf.GPRS_USER_SERVICE_BDR030427 AS g_0 WHERE g_0.roam_city = 20

                    + Model Name:mysql5

                    + Model Name:mysql5

            + Grouping Columns:hour(start_time)

            + Sort Mode:false

        + Select Columns:anon_grp0.gcol0 AS cnt

       

      and i make a conclude that it execute query first : SELECT g_0.start_time FROM mysql5.zhangwf.GPRS_USER_SERVICE_BDR030427 AS g_0 WHERE g_0.roam_city = 20 in every source node ,then in teiid it will integrate data by

      sql :select hour(start_time) cnt  From GPRS_USER_SERVICE_BDR030427 where roam_city=20 group by hour(start_time) ;  and i want to know if we can set convert rules . we hope that in source node it can exeute the original sql:select hour(start_time) cnt  From GPRS_USER_SERVICE_BDR030427 where roam_city=20 group by hour(start_time) which can get less data than SELECT g_0.start_time FROM mysql5.zhangwf.GPRS_USER_SERVICE_BDR030427 AS g_0 WHERE g_0.roam_city = 20 . after that ,teiid integrate the data from the resultsset.  it means that we don't need the convert to internal form. Is there some solutions?


        • 1. Re: jboss and teiid Federated Plans
          shawkins
          shawkins May 19, 2014 8:52 AM (in response to 724150880)

          I'll have to double check this scenario, but I believe in later releases in the multi-source case we should be issuing the aggregate queries to each source and then aggregating again above, rather than not pushing down the aggregate at all.  Just to double check, you do mean that this is utilizing the multi-source feature, or are you simply creating a view with a union all?we

            • Actions
          • 2. Re: jboss and teiid Federated Plans
            724150880
            724150880 May 19, 2014 9:50 PM (in response to shawkins)

            yes , i just use the multi-source feature . follow is my vdb configuration:

            <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

            <vdb name="myvdb" version="1">

            <description>My Test sybiq VDB</description>

            <property name="UseConnectorMetadata" value="true" />

                    <model visible="true" type="PHYSICAL"  name="mysql5">

                            <property name="importer.useFullSchemaName" value="true"/>

                            <property name="supports-multi-source-bindings" value="true"/>

                            <source name="mysql82" translator-name="mysql" connection-jndi-name="java:mysql82DS"/>

                            <source name="mysql90" translator-name="mysql" connection-jndi-name="java:mysql90DS"/>

                    </model>

            </vdb>

             

            so  i am confused that it spends more time for the native query rather than join action by teiid. and i have read the document of teiid 8.2 Federated Plans chapter。it seems like that i mentioned above。so i want to know if we have some method to solve the problem ? thanks very much !

              • Actions
            • 3. Re: jboss and teiid Federated Plans
              shawkins
              shawkins May 20, 2014 11:34 AM (in response to 724150880)

              > so  i am confused that it spends more time for the native query rather than join action by teiid. and i have read the document of teiid 8.2 Federated Plans chapter。it seems like that i mentioned above。so i want to know if we have some method to solve the problem ? thanks very much !


              Sorry for the confusion.  Until Teiid 8.3 with [TEIID-2253] Multi-source issues - JBoss Issue Tracker the planning for multi-source optimization was somewhat limited.  The planner simply marked a few capabilities, including aggregation, as unsupported.  Then it generated the plan as if it were single source, then replaced the single source access with a union all of the applicable multi-source.  With 8.3 we should perform most of our standard optimizations as they are also multi-source aware.  Can you try your scenario in 8.3 or later to see if we are generating a more appropriate plan?

                • Actions
              • 4. Re: jboss and teiid Federated Plans
                724150880
                724150880 May 21, 2014 5:40 AM (in response to shawkins)

                hello , i have tried teiid 8.3 . Finally  it's the same as teiid 8.2 .and i have download the Source Code of teiid 8.2 .but i don't know where to look for the code which control this . i'm not sure  if the translater of sybase jdbc or teiid planner works . so can you tell me the direction of this ? thanks very much.

                  • Actions
                • 5. Re: jboss and teiid Federated Plans
                  shawkins
                  shawkins May 21, 2014 9:20 AM (in response to 724150880)

                  I see that the optimization is only enabled in the multi-source case if there are aggregate values.  An enhancement will be required to implement what is effectively distinct handling.

                    • Actions
                  • 6. Re: jboss and teiid Federated Plans
                    shawkins
                    shawkins May 21, 2014 9:37 AM (in response to shawkins)

                    I see another related issue here as well, so this will be worked under [TEIID-2968] issues with non-partitioned multi-source aggreage pushdown - JBoss Issue Tracker

                     

                    The fixes will be in Teiid 8.8 Alpha2 due later this week.

                     

                    Thanks,

                    Steve

                      • Actions
                    • 7. Re: jboss and teiid Federated Plans
                      724150880
                      724150880 May 21, 2014 7:56 PM (in response to shawkins)

                      thanks very much , it seems that jboss have the correct match version to teiid ,for teiid 8.4+ ,it need EAP 6.1 . Am i right? if we use EAP 6.1 in production environment ， is it stable?   and if there are more changes than jboss AS 7.1 .

                        • Actions
                      • 8. Re: jboss and teiid Federated Plans
                        shawkins
                        shawkins May 21, 2014 8:03 PM (in response to 724150880)

                        Yes you currently need EAP 6.1 Alpha1.  See JBoss Application Server FAQ - JBoss Community for more on usage of the EAP Alpha.  Essentially it is the same thing as what would have been AS 7.2, so you would approach it the same as a community release.

                          • Actions
                        • 9. Re: jboss and teiid Federated Plans
                          724150880
                          724150880 Jun 2, 2014 9:46 PM (in response to shawkins)

                          hello ,i have tried the new version of teiid , finally it is the same . Also  i have searched and it seems that there is no Teiid 8.8 Alpha2 .  until now ,the newest version is Teiid 8.8 Alpha1 .  i want to know when the Teiid 8.8 Alpha2 can release? Or if i can find some useful source code and update some relevant section in it to solve the problem temporarily ？  we are urgent to this problem . thanks very much.

                            • Actions
                          • 10. Re: jboss and teiid Federated Plans
                            shawkins
                            shawkins Jun 2, 2014 10:04 PM (in response to 724150880)

                            Yes Alpha2 was made available - Downloads · Teiid

                              • Actions
                            • 11. Re: jboss and teiid Federated Plans
                              724150880
                              724150880 Jun 3, 2014 6:15 AM (in response to shawkins)

                              hello ,i have tried that jboss-eap-6.1.0.Alpha.zip + teiid 8.8 alpha 2，but when i startup the server ,it occurs some problems:

                              18:16:23,992 ERROR [stderr] (MSC service thread 1-25)  ScriptEngineManager providers.next(): javax.script.ScriptEngineFactory: Provider com.sun.script.javascript.RhinoScriptEngineFactory not found

                              18:16:24,010 WARN  [org.teiid.RUNTIME] (MSC service thread 1-11)  TEIID40001 The provided translator property values [AutoCreateUniqueConstraints, ImportApproximateIndexes, ImportForeignKeys, ImportIndexes, ImportKeys, ImportProcedures, ImportStatistics, QuoteNameInSource, UseAnyIndexCardinality, UseCatalogName, UseFullSchemaName, UseProcedureSpecificName, UseQualifiedName, WidenUnsingedTypes] were not used.  Please check the properties that are expected by translator mysql.

                              18:16:24,022 INFO  [org.jboss.web] (ServerService Thread Pool -- 55)  JBAS018210: Register web context: /odata

                              18:16:24,104 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (MSC service thread 1-11)  TEIID40118 VDB myvdb.1 added to the repository - is reloading true

                              18:16:24,106 INFO  [org.teiid.RUNTIME] (MSC service thread 1-11)  TEIID50029 VDB myvdb.1 model "mysql5" metadata is currently being loaded. Start Time: 6/3/14 6:16 PM

                              18:16:24,139 INFO  [org.hibernate.validator.internal.util.Version] (ServerService Thread Pool -- 55)  HV000001: Hibernate Validator 4.3.1.Final

                              18:16:24,232 INFO  [org.jboss.resteasy.spi.ResteasyDeployment] (ServerService Thread Pool -- 55)  Deploying javax.ws.rs.core.Application: class org.teiid.odata.TeiidODataApplication

                              18:16:24,291 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata]] (ServerService Thread Pool -- 55)  JBWEB000287: Exception sending context initialized event to listener instance of class org.jboss.resteasy.plugins.server.servlet.ResteasyBootstrap: java.lang.IllegalArgumentException: Illegal group reference

                                      at java.util.regex.Matcher.appendReplacement(Matcher.java:725) [rt.jar:1.6.0]

                                      at org.jboss.resteasy.util.Encode.pathParamReplacement(Encode.java:434) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.util.Encode.encodeValue(Encode.java:314) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.util.Encode.encodePath(Encode.java:167) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.specimpl.UriBuilderImpl.paths(UriBuilderImpl.java:222) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.specimpl.UriBuilderImpl.path(UriBuilderImpl.java:295) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.core.ResourceMethodRegistry.processMethod(ResourceMethodRegistry.java:262) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.core.ResourceMethodRegistry.addResourceFactory(ResourceMethodRegistry.java:124) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.core.ResourceMethodRegistry.addResourceFactory(ResourceMethodRegistry.java:106) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.core.ResourceMethodRegistry.addResourceFactory(ResourceMethodRegistry.java:83) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.core.ResourceMethodRegistry.addPerRequestResource(ResourceMethodRegistry.java:72) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.spi.ResteasyDeployment.registration(ResteasyDeployment.java:405) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.spi.ResteasyDeployment.start(ResteasyDeployment.java:233) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.jboss.resteasy.plugins.server.servlet.ResteasyBootstrap.contextInitialized(ResteasyBootstrap.java:28) [resteasy-jaxrs-2.3.5.Final.jar:]

                                      at org.apache.catalina.core.StandardContext.contextListenerStart(StandardContext.java:3339) [jbossweb-7.2.0.Final.jar:7.2.0.Final]

                                      at org.apache.catalina.core.StandardContext.start(StandardContext.java:3777) [jbossweb-7.2.0.Final.jar:7.2.0.Final]

                                      at org.jboss.as.web.deployment.WebDeploymentService.doStart(WebDeploymentService.java:156) [jboss-as-web-7.2.0.Alpha1-redhat-4.jar:7.2.0.Alpha1-redhat-4]

                                      at org.jboss.as.web.deployment.WebDeploymentService.access$000(WebDeploymentService.java:60) [jboss-as-web-7.2.0.Alpha1-redhat-4.jar:7.2.0.Alpha1-redhat-4]

                                      at org.jboss.as.web.deployment.WebDeploymentService$1.run(WebDeploymentService.java:93) [jboss-as-web-7.2.0.Alpha1-redhat-4.jar:7.2.0.Alpha1-redhat-4]

                                      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [rt.jar:1.6.0]

                                      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.6.0]

                                      at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.6.0]

                                      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.6.0]

                                      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.6.0]

                                      at java.lang.Thread.run(Thread.java:636) [rt.jar:1.6.0]

                                      at org.jboss.threads.JBossThread.run(JBossThread.java:122)

                               

                               

                              18:16:24,298 INFO  [org.teiid.CONNECTOR] (teiid-async-threads - 1)  MySQLExecutionFactory Commit=true;DatabaseProductName=MySQL;DatabaseProductVersion=5.5.34-log;DriverMajorVersion=5;DriverMajorVersion=1;DriverName=MySQL Connector Java;DriverVersion=mysql-connector-java-5.1.27-commercial-SNAPSHOT ( Revision: mark.matthews@oracle.com-20130711224726-x46os03htahm62v4 );IsolationLevel=2

                              18:16:24,331 ERROR [org.apache.catalina.core] (ServerService Thread Pool -- 55)  JBWEB001103: Error detected during context /odata start, will stop it

                              18:16:24,339 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 1)  TEIID50030 VDB myvdb.1 model "mysql5" metadata loaded. End Time: 6/3/14 6:16 PM

                              18:16:24,341 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 1)  TEIID40003 VDB myvdb.1 is set to ACTIVE

                              18:16:24,347 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 55)  MSC00001: Failed to start service jboss.web.deployment.default-host./odata: org.jboss.msc.service.StartException in service jboss.web.deployment.default-host./odata: org.jboss.msc.service.StartException in anonymous service: JBAS018040: Failed to start context

                                      at org.jboss.as.web.deployment.WebDeploymentService$1.run(WebDeploymentService.java:96)

                                      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) [rt.jar:1.6.0]

                                      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.6.0]

                                      at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.6.0]

                                      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) [rt.jar:1.6.0]

                                      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) [rt.jar:1.6.0]

                                      at java.lang.Thread.run(Thread.java:636) [rt.jar:1.6.0]

                                      at org.jboss.threads.JBossThread.run(JBossThread.java:122)

                              Caused by: org.jboss.msc.service.StartException in anonymous service: JBAS018040: Failed to start context

                                      at org.jboss.as.web.deployment.WebDeploymentService.doStart(WebDeploymentService.java:161)

                                      at org.jboss.as.web.deployment.WebDeploymentService.access$000(WebDeploymentService.java:60)

                                      at org.jboss.as.web.deployment.WebDeploymentService$1.run(WebDeploymentService.java:93)

                                      ... 7 more

                               

                               

                              18:16:24,596 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 27)  JBAS018559: Deployed "myvdb-vdb.xml" (runtime-name : "myvdb-vdb.xml")

                              18:16:24,597 INFO  [org.jboss.as.server] (ServerService Thread Pool -- 44)  JBAS018559: Deployed "teiid-odata-8.8.0.Alpha2.war" (runtime-name : "teiid-odata-8.8.0.Alpha2.war")

                              18:16:24,598 INFO  [org.jboss.as.controller] (Controller Boot Thread)  JBAS014774: Service status report

                              JBAS014777:   Services which failed to start:      service jboss.web.deployment.default-host./odata: org.jboss.msc.service.StartException in service jboss.web.deployment.default-host./odata: org.jboss.msc.service.StartException in anonymous service: JBAS018040: Failed to start context

                               

                              18:16:24,617 INFO  [org.jboss.as] (Controller Boot Thread)  JBAS015961: Http management interface listening on http://192.168.7.83:9990/management

                              18:16:24,618 INFO  [org.jboss.as] (Controller Boot Thread)  JBAS015951: Admin console listening on http://192.168.7.83:9990

                              18:16:24,618 ERROR [org.jboss.as] (Controller Boot Thread)  JBAS015875: JBoss EAP 6.1.0.Alpha1 (AS 7.2.0.Alpha1-redhat-4) started (with errors) in 3965ms - Started 329 of 411 services (2 services failed or missing dependencies, 76 services are passive or on-demand)

                               

                              i don't know why they happens.   i use the original configuration standalone-teiid.xml , myvdb.xml and module.xml of mysql .  if it needs something else?   thanks very much .

                                • Actions
                              • 12. Re: jboss and teiid Federated Plans
                                shawkins
                                shawkins Jun 3, 2014 7:58 AM (in response to 724150880)

                                > 18:16:23,992 ERROR [stderr] (MSC service thread 1-25)  ScriptEngineManager providers.next(): javax.script.ScriptEngineFactory: Provider com.sun.script.javascript.RhinoScriptEngineFactory not found

                                 

                                Are you on a mac?  In short this is saying there is a config error with the JRE in that it can't find an expected javascript engine.  Unless you are using OBJECTTABLE, I believe this can be ignored.

                                 

                                > 18:16:24,010 WARN  [org.teiid.RUNTIME] (MSC service thread 1-11)  TEIID40001 The provided translator property values [AutoCreateUniqueConstraints, ImportApproximateIndexes, ImportForeignKeys, ImportIndexes, ImportKeys, ImportProcedures, ImportStatistics, QuoteNameInSource, UseAnyIndexCardinality, UseCatalogName, UseFullSchemaName, UseProcedureSpecificName, UseQualifiedName, WidenUnsingedTypes] were not used.  Please check the properties that are expected by translator mysql.

                                 

                                It appears that import properties have been set as translator properties.  Import properties should be children of the model element.

                                 

                                > 18:16:24,291 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata]] (ServerService Thread Pool -- 55)  JBWEB000287: Exception sending context initialized event to listener instance of class org.jboss.resteasy.plugins.server.servlet.ResteasyBootstrap: java.lang.IllegalArgumentException: Illegal group reference

                                 

                                This error and the other web/odata errors are due to not applying the Resteasy patch that is listed from our download page - Downloads · Teiid.  The version of Resteasy with EAP 6.1 Alpha has a couple bugs that cause our automatic odata deployment to fail, but are addressed with a later version.  I believe you can also ignore these errors if you are not using Teiid to expose an odata service.

                                1 of 1 people found this helpful
                                  • Actions
                                • 13. Re: jboss and teiid Federated Plans
                                  724150880
                                  724150880 Jun 3, 2014 7:36 PM (in response to shawkins)

                                  thanks Steven Hawkins , i use linux OS of redhat 5.5 ,

                                   

                                  > 18:16:24,291 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata]] (ServerService Thread Pool -- 55)  JBWEB000287: Exception sending context initialized event to listener instance of class org.jboss.resteasy.plugins.server.servlet.ResteasyBootstrap: java.lang.IllegalArgumentException: Illegal group reference

                                   

                                  This error and the other web/odata errors are due to not applying the Resteasy patch that is listed from our download page - Downloads · Teiid.  The version of Resteasy with EAP 6.1 Alpha has a couple bugs that cause our automatic odata deployment to fail, but are addressed with a later version.  I believe you can also ignore these errors if you are not using Teiid to expose an odata service.n artti

                                   

                                   

                                  for this error , i saw an artical which refer to the problem ,and i download the 8.8 alpha2 resteasy , and should i extract the zip file modules in it and cover the original ?

                                    • Actions
                                  • 14. Re: jboss and teiid Federated Plans
                                    rareddy
                                    rareddy Jun 3, 2014 9:07 PM (in response to 724150880)

                                    Yes, you need to unzip the resteasy modules zip file over the JBoss EAP modules directory and merge them, so that new version of RestEasy is installed.

                                      • Actions
                                    1 2 Previous Next