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

Teiid mongodb and mysql

iranox Jan 23, 2017 6:19 PM

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

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

1. Re: Teiid mongodb and mysql

rareddy Jan 24, 2017 10:24 AM (in response to iranox)

Tobias,

You are using the both MySQL and MongoDB unders *single* model as in multi-source feature in Teiid. However, although it does not seemed to be called out in documentation, multi-source needs to be using *same* type of sources under single model as the capabilities of translators need to be exactly same for all the sources involved.

My suggestion would be, break it down into two different models with same duplicated schema. If the problem still exists then, you can log a JIRA, I will take a deeper look at fixing it.

Ramesh..
1 of 1 people found this helpful
Actions
2. Re: Teiid mongodb and mysql

iranox Jan 24, 2017 12:05 PM (in response to rareddy)

Thanks for the answer, Ramesh.
I use two different models now, but I need data from both databases in one query. Do I need to create a view, or is there any other way except using a view?
Actions
3. Re: Teiid mongodb and mysql

rareddy Jan 24, 2017 12:08 PM (in response to iranox)

Yes, you need to create view and do a union of the tables from different sources.
1 of 1 people found this helpful
Actions

Go to original post