Teiid mongodb and mysql
iranox Jan 23, 2017 6:19 PMHi. 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.