jboss and teiid Federated Plans

[724150880]

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?

[shawkins]

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

[724150880]

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 !

[shawkins]

> 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?

[724150880]

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.

[shawkins]

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.

[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

[724150880]

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 .

[shawkins]

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.

[724150880]

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.

[shawkins]

Yes Alpha2 was made available - Downloads · Teiid

[724150880]

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: [e-mail]-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 .

[shawkins]

> 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.

[724150880]

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 ?

[rareddy]

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.