Jboss DV (Wildfly10.X) integration with ElasticSearch through REST

[srinath0533]

Hello All,

I tried to find some information online on Jboss DV integration with ElasticSearch but couldn't find any.  Are there any documents or blogs that can help me to understand the Jboss DV integration with ElasticSearch.

Thanks

Srinath.

[rareddy]

Teiid does not have any direct integration with Elastic Search. We have integration with SOLR.

Curious how you are trying to use Elastic Search with Teiid?

[srinath0533]

I never tried to connect to ElasticSearch. We had a requirement to evaluate the ElasticSearch integration with Jboss and i couldn't find any material or info. So i thought i will post here to see if someone have tried it in the past.  Can you please give me more insight on how its gonna work with SOLR if possible.

Thanks,

Srinath.

[rareddy]

You can check the documentation for that Apache SOLR Translator · Teiid Documentation

Integrate Apache Solr with Teiid

Improve Teiid Query Performance using Apache SOLR as Index Store on a View

Ramesh..

[srinath0533]

For some reason my Manager had an impression that Wildfly 10X works with ElasticSearch.  As you already confirmed that ElasticSearch doesn't work with Teiid i informed the same to my manager. Looking into how SOLR can be integrated with Teiid.  I really appreciate all your responses Ramesh.  You were very helpful.

[rareddy]

WildFly != Teiid

Teiid is deployed on the WildFly.  I am not sure if there are other integrations in WildFly with Elastic Search. For example, if you use Infinispan (another JBoss Project) they use it Elastic Search.

Ramesh..

[srinath0533]

I don't see Elastic Search Translator on Wildfly10x but i see SOLR. So i believe there is no direct integration between Elastic Search and WIldfly.  However i read that using Logstash as data pipeline Elastic Search can be integrated with Wildfly.  Is there a possibility and if yes, how is the performance following that route?

Also is it possible to deploy ElasticSearch .war file on Wildfly server 10x?

[rareddy]

Again there is No Elastic Search support from Teiid. Teiid is not same as Wildfly. Wildfly is JEE server for Java based Web server, Teiid is deployed on this server as a module. So when you use Wildfly and Teiid interchangeably it is not correct and confusing.

Now, what  I am saying  is Elastic Search is not available in Teiid. You independently deploy a Elastic Search based applications on Wildfly that is not a problem. But I can not comment on how to or performance aspects.

Ramesh..

[srinath0533]

Hello

Thank you for the clarity. Im trying to connect to ElasticSearch on Wildfly 10x server through "Web service Source (REST)".  I gave the URL, connection type: NONE and JSON and the ping was successful.

However when i finish the connection establishment i see the below error.

Any help is much appreciated. Thank you.

[rareddy]

This may be similar to Java IO File Prefix string too short - but its not - Stack Overflow  which is a bug in the Teiid Designer. You can log a JIRA for it, but for time being there may be alternatives if you give much more full URL.

[srinath0533]

Hello,

I was able to connect to Elastic Search Index through Rest end point and created the model on DevStudio.  However while deploying the model on VDB using Wildfly AS i see the below error message. Please help me.

Error = WFLYJCA0073:Failed to load module for RA [webservice]

[rareddy]

Look on the Teiid server for error log why the deployment failed and correct the issue.

[srinath0533]

This is how we solved the issue. Earlier we tried multiple ways to access Elastic Search data> We now using Teiid Importer. Below are the steps.

Enabled our Elastic Search access through Rest.

Add the elastic search rest data source in standalone.xml file under resource adapters and re start the jboss service

Use Teiid Importer>Teiid Connection>Source Model

Select the Elastic Search data source that has been added to Standalone.xml in earlier step and click NEXT.

Translator should be WS and click NEXT.

Select the location and provide a name and click NEXT and NEXT again on Importer DDL window.

Select invokeHttp and unselect invoke and click FINISH. Now Source Model is created.

Now create a View Model by right click on SOurce Model>New>Teiid Metadata Model and provide model name and model type should be View Model and click Finish.

Now View Model is created. Next create new table and assign the invokeHttp as source to the table created as below.

Build the T transformation based on your requirement on how you need the table to be created.

SELECT

A.column1 AS column1 , A.column2 AS column2 , A.column3 AS column3, A.column4 AS column4

FROM

(EXEC abcdefghi.invokeHttp(action => 'GET', stream => convert('TRUE', boolean))) AS f, XMLTABLE('/response/hits/hits/_source/Remarks/Remark' PASSING JSONTOXML('response', f.result) COLUMNS column1 string PATH 'column1 /text()', column2 string PATH 'column2/text()', column3 string PATH 'column3/text()', column4 string PATH 'column4/text()') AS A

Once you build the above transformation please do the data preview and query should return the data as below.

Once data preview is success create a VDB with source model and view model and assign the datasource and deploy the vdb. Make sure DV server is running. Once VDB is deployed you should be to query data from any SQL client like Squirrel.

Above is the solution to access Elastic Search data through Teiid JDBC connection and the source Elastic Search which we imported should be accessible through Rest to create invokeHttp.

Now i have to create a RESTPROC and my TABLE will be source for RESPROC. I created RESTPROC and building custom SQL transformation between RESTPROC and TABLE but wasn't successful executing the SQL. Below is the issue and my SQL Transformation with dummy names. Once he SQL issue is resolved and VDB is deployed we will have elastic search access through Rest as well. Thats my end goal to have access to Elastic Search Rest and JDBC access through DV Layer.

SQL TRANSFORMATION

BEGIN

SELECT XMLELEMENT(NAME elems, XMLAGG(XMLELEMENT(NAME elem, XMLFOREST(vwModel.Tbltable.Column1, vwModel.Tbltable.Column2, vwModel.Tbltable.Column3, vwModel.Tbltable.Column4)))) AS xml_out FROM vwModel.Tbltable;

END

RESULTS

select * from ( exec "vwModel"."Tbltable"() ) AS X_X

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

Please help me understanding the cause. if you see when i click PREVIEW DATA a new window is popped up with SELECT * statement which has empty parenthesis. im not sure if thats the issue or something else. in the SQL Transformation in 2nd line as shown below i have Name elems and NAME elem. Should Eelems and elem should be replaced with viewmodel and table name?

SELECT XMLELEMENT(NAME elems, XMLAGG(XMLELEMENT(NAME elem, XMLFOREST

Any help will be very much appreciated guys.  I have done 90% of work to provide jdbc and rest access to elastic search database. Im stuck with the SQL transformation.

Thank you.

[rareddy]

Somewhere in XMLTABLE SQL is wrong. It looks from screen there is a space missing. Correct and test.

[srinath0533]

The space is a typo. The SQL Transformation between Table and invokeHttp is working fine and returning the data in Jboss data preview and Squirrel as well.  Problem is with the SQL Transformation between RestProc and table.

SQL TRANSFORMATION

BEGIN

SELECT XMLELEMENT(NAME elems, XMLAGG(XMLELEMENT(NAME elem, XMLFOREST(vwBookingDataTest.TblBookingData.pnrid, vwBookingDataTest.TblBookingData.linenumber, vwBookingDataTest.TblBookingData.contents, vwBookingDataTest.TblBookingData.type)))) AS xml_out FROM vwBookingDataTest.TblBookingData;

END

RESULTS

select * from ( exec "vwBookingDataTest"."TblBookingDataRestProc"() ) AS X_X

org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.