3 Replies Latest reply on Jan 24, 2017 12:08 PM by Ramesh Reddy

    Teiid mongodb and mysql

    Tobias Wieprich Newbie

      Hi. I want to run teiid with a mysql and mongodb instance. Both databases

      contain the same table/collection with the same schema.

       

      "Easy" Queries like  "Select * from product" work but this

       

       

      SQL query:

        SELECT *

      FROM (

      SELECT DISTINCT

          1 AS "productQuestType", NULL AS "productLang", ('http://www4.wiwiss.fu-berlin.de/bizer/bsbm/v01/instances/dataFromProducer/Product' || REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(QVIEW1."nr" AS VARCHAR), ' ', '%20'), '!', '%21'), '''', '%22'), '#', '%23'), '$', '%24'), '&', '%26'), '(', '%28'), ')', '%29'), '*', '%2A'), '+', '%2B'), ',', '%2C'), '/', '%2F'), ':', '%3A'), ';', '%3B'), '=', '%3D'), '?', '%3F'), '@', '%40'), '[', '%5B'), ']', '%5D')) AS "product",

          3 AS "labelQuestType", NULL AS "labelLang", QVIEW1."label" AS "label"

        FROM

      "benchmark"."product" QVIEW1,

      "benchmark"."producttypeproduct" QVIEW2,

      "benchmark"."productfeatureproduct" QVIEW3,

      "benchmark"."productfeatureproduct" QVIEW4,

      "benchmark"."product" QVIEW5

      WHERE

      QVIEW1."nr" IS NOT NULL AND

      QVIEW1."label" IS NOT NULL AND

      (QVIEW1."nr" = QVIEW2."product") AND

      (QVIEW2."productType" = '10') AND

      (QVIEW1."nr" = QVIEW3."product") AND

      (QVIEW3."productFeature" = '414') AND

      (QVIEW1."nr" = QVIEW4."product") AND

      (QVIEW4."productFeature" = '369') AND

      (QVIEW1."nr" = QVIEW5."nr") AND

      QVIEW5."propertyNum1" IS NOT NULL AND

      (QVIEW5."propertyNum1" > 354)

      ) SUB_QVIEW

      ORDER BY SUB_QVIEW."label"

      LIMIT 10

       

      throws this error.

       

       

      org.teiid.core.TeiidException

       

       

      Caused by: org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidException

           at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

           at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

           at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)

           at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)

           at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)

           at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

           at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

           at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

           at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:284)

           at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:322)

           at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

           at java.lang.reflect.Method.invoke(Method.java:498)

           at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

           at com.sun.proxy.$Proxy37.read(Unknown Source)

           at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:421)

           at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)

           at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:423)

           at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:354)

           at it.unibz.inf.ontop.owlrefplatform.core.QuestStatement$QueryExecutionThread.run(QuestStatement.java:143)

      Caused by: org.teiid.core.TeiidException

           at org.teiid.client.ResultsMessage.setException(ResultsMessage.java:196)

           at org.teiid.dqp.internal.process.RequestWorkItem.sendError(RequestWorkItem.java:1084)

           at org.teiid.dqp.internal.process.RequestWorkItem.close(RequestWorkItem.java:576)

           at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:374)

           at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

           at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

           at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

           at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

           at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

           at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

           at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

           at java.lang.Thread.run(Thread.java:745)

      Caused by: java.lang.NullPointerException

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.buildColumnDetail(MongoDBSelectVisitor.java:236)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:217)

           at org.teiid.language.ColumnReference.acceptVisitor(ColumnReference.java:72)

           at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:81)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:642)

           at org.teiid.language.Comparison.acceptVisitor(Comparison.java:110)

           at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:81)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:738)

           at org.teiid.language.AndOr.acceptVisitor(AndOr.java:56)

           at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.append(MongoDBSelectVisitor.java:81)

           at org.teiid.translator.mongodb.MongoDBSelectVisitor.visit(MongoDBSelectVisitor.java:536)

           at org.teiid.language.Select.acceptVisitor(Select.java:110)

           at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)

           at org.teiid.translator.mongodb.MongoDBQueryExecution.execute(MongoDBQueryExecution.java:60)

           at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:359)

           at sun.reflect.GeneratedMethodAccessor91.invoke(Unknown Source)

           at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

           at java.lang.reflect.Method.invoke(Method.java:498)

           at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

           at com.sun.proxy.$Proxy57.execute(Unknown Source)

           at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

           at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)

           at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)

           at java.util.concurrent.FutureTask.run(FutureTask.java:266)

           at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)

           ... 6 more

       

      This is my vdb.xml:

       

       

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

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

       

       

       

        <description>A  VDB</description>

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

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

           <source name="jdbc-connector" translator-name="mysql" connection-jndi-name="java:/BenchmarkDS"/>

          <source name="local" translator-name="mongodb" connection-jndi-name="java:/mongoDS"/>

            <metadata type="DDL"><![CDATA[

              CREATE FOREIGN TABLE producttype (

                nr long,

                label string,

                comment string,

                parent long,

                offerWebpage string,

                publisher integer,

                publishDate date

              ) OPTIONS(UPDATABLE 'TRUE');

       

              CREATE FOREIGN TABLE person (

                nr long,

                name string,

                mbox_sha1sum string,

                country string,

                publisher long,

                publishDate date

              );

       

              CREATE FOREIGN TABLE productfeatureproduct (

                product long,

                productFeature long

              );

       

           CREATE FOREIGN TABLE producttypeproduct (

                product long,

                productType long

              );

       

       

              CREATE FOREIGN TABLE productfeature(

                nr long,

                label string,

                comment string,

                publisher integer,

                publishDate date

              );

       

              CREATE FOREIGN TABLE producer(

                nr long,

                label string,

                comment string,

                homepage string,

                country string,

                publisher integer,

                publishDate date

              );

       

       

              CREATE FOREIGN TABLE offer (

                nr long,

                product long,

                producer long,

                vendor long,

                price double,

                validFrom date,

                validTo date,

                deliveryDays long,

                offerWebpage string,

                publisher integer,

                publishDate date

              ) OPTIONS(UPDATABLE 'TRUE');

       

              CREATE FOREIGN TABLE review (

                nr long,

                product long,

                producer long,

                person long,

                text clob,

                reviewDate date,

                title string,

                speech string,

                rating1 long,

                rating2 long,

                rating3 long,

                rating4 long,

                publisher integer,

                publishDate date

              ) OPTIONS(UPDATABLE 'TRUE');

       

              CREATE FOREIGN TABLE product (

                nr long,

                label string,

                comment string,

                producer long,

                propertyNum1 long,

                propertyNum2 long,

                propertyNum3 long,

                propertyNum4 long,

                propertyNum5 long,

                propertyNum6 long,

                propertyTex1 string,

                propertyTex2 string,

                propertyTex3 string,

                propertyTex4 string,

                propertyTex5 string,

                propertyTex6 string,

                publisher integer,

                publishDate date

              ) OPTIONS(UPDATABLE 'TRUE');

       

              CREATE FOREIGN TABLE vendor (

                nr long,

                label string,

                comment string,

                homepage string,

                country string,

                publisher integer,

                publishDate date

              ) OPTIONS(UPDATABLE 'TRUE');

            ]]> </metadata>

          </model>

       

       

       

      </vdb>

       

      If I remove either mongodb or mysql the query works.