With Teiid data vartulization; read file which is located on remote machine

[praksah]

Could you please let me know; how do we read file which is located on separate machine and Jboss teiid is deployed on another machine. Just like a remote way. I am using Teiid 8.13 and wildfly Jboss server. I am able to read file when flat file located on same machine where jboss teiid install. But facing an issue when I refer file from remote machine.

[rareddy]

If the file can be reached through a shared drive you can still use same technique as you did on same machine. Otherwise if file can be reached through a URL then you can use the "webservice" resource adapter to fetch the contents of the file, then use the TEXTTABLE.

[praksah]

File through share drive able to access with teiid; Thanks you very much.

Could you please guide us on second approach where we use "webservice" resource adapter to fetch the contents of the file, then use the TEXTTABLE. How do we make file URL

[rareddy]

What I meant was, if file was available though url, like "file://host/path/to/myfile.txt" or "http://host:port/path/to/myfile.txt" then you can use the web service resource adapter to fetch the file from remote location through Teiid.

[praksah]

Thanks Ramesh . For web service resource adapter what can we use as translator ?  Tried for File and OData, both are giving problems.

And I set below properties for webservice as resource adapter. Do we need to add any other properties ?

<resource-adapter id="wsDS1">

          <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>

       <transaction-support>NoTransaction</transaction-support>

         <connection-definitions>

                       <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/wsDS1" enabled="true" pool-name="wsDS1">

                            <config-property name="EndPoint">

   http://download.jboss.org/teiid/designer/data/employees/file/EMPLOYEEDATA.txt

                  </config-property>

             </connection-definition>

           </connection-definitions>

</resource-adapter>

[rareddy]

You use "ws" translator. In the file based one you used "file" translator, getTextFile() which returns the result data in clob. Where as "ws" translator with "invokeHTTP" method the returned object will be a blob, that needs to be used in TEXTFILE construct instead of the clob.

[rareddy]

Prakash,

Here is an example of the Dynamic VDB for your reference using your above web resource-adapter (I just removed the EMPLOYEEDATA.TXT at the end of URL)

webservice-vdb.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="webservice" version="1">
    <model name="web">       
        <source name="webservice" translator-name="ws" connection-jndi-name="java:/wsDS1"/>
    </model>
    <model name="view" type="VIRTUAL">
        <metadata type="DDL"><![CDATA[       
            CREATE  VIEW Employee (LastName varchar(25),FirstName varchar(25),MiddleName varchar(25),
                  EmpId varchar(25),Department varchar(25),AnnualSalary varchar(25),Title varchar(25),
                  HomePhone varchar(25),Manager varchar(25),Street varchar(25),City varchar(25),
                  State varchar(25),Zip varchar(25))
            AS
            SELECT A.LastName,A.FirstName,A.MiddleName,A.EmpId,A.Department,A.AnnualSalary,
                A.Title,A.HomePhone,A.Manager,A.Street,A.City,A.State,A.Zip
        FROM
    (EXEC web.invokeHttp(action=>'GET', endpoint=>'EMPLOYEEDATA.txt', stream=>'TRUE')) AS f,
    TEXTTABLE(TO_CHARS(f.result, 'UTF-8') COLUMNS LastName varchar(25),FirstName varchar(25),
        MiddleName varchar(25),EmpId varchar(25), Department varchar(25),AnnualSalary varchar(25),
        Title varchar(25),HomePhone varchar(25),Manager varchar(25),Street varchar(25),
        City varchar(25),State varchar(25),Zip varchar(25) SKIP 3) AS A;         
        ]]>
        </metadata>
    </model>     
</vdb>

HTH

Ramesh..

[praksah]

Hi Ramesh ,

Now I am able to access remote location file through http as you have mentioned above.
But not able to access file of remote location through file translator.

I did below steps :

1. I tried translator as ws for resource adapter = org.jboss.teiid.resource-adapter.webservice with below properties .

<resource-adapter id="FileDsLatest">

<module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>

<transaction-support>NoTransaction</transaction-support>

<connection-definitions>

<connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/FileDsLatest" enabled="true" pool-name="FileDsLatest">

<config-property name="EndPoint">

file://<ip-address>/data/marketdata-price1.txt

</config-property></connection-definition>

  </connection-definitions>

</resource-adapter>

I used query

select A.SYMBOL,A.PRICE FROM (call FileDsLatest.getTextFiles('marketdata-price1.txt')) AS f,TEXTTABLE(f.file COLUMNS SYMBOL string,PRICE string HEADER) AS A

getting below error :

TEIID30357 FileDsLatest.getTextFiles does not exist.

For query :

SELECT A.SYMBOL,A.PRICE from (EXEC FileDsLatest.invokeHttp('GET',null,'marketdata-price1.txt','TRUE')) AS f,TEXTTABLE(TO_CHARS(f.result, 'UTF-8') COLUMNS SYMBOL string,PRICE string HEADER) AS A

getting below error :

TEIID30504 FileDsLatest: unknown protocol: file

2) Also , I used file as resource adapter (org.jboss.teiid.resource-adapter.file) and translator as file with below properties.

<resource-adapter id="FileDs">

<module slot="main" id="org.jboss.teiid.resource-adapter.file"/>

<transaction-support>NoTransaction</transaction-support>

<connection-definitions>

<connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/FileDs" enabled="true" pool-name="FileDs">

<config-property name="ParentDirectory">

  file://<ip-address>/data/marketdata-price1.txt

</config-property>

</connection-definition>

</connection-definitions>

</resource-adapter>

I executed below query :

SELECT A.SYMBOL,A.PRICE from (EXEC FileDs.getTextFiles('marketdata-price1.txt')) AS f,  TEXTTABLE(f.file COLUMNS SYMBOL string,PRICE string HEADER) AS A";

getting below error :

Connector worker process failed for atomic-request=1htd/1+kkiBn.12.3.3: org.teiid.translator.TranslatorException: File not found marketdata-price1.txt

If I  executed below query :

SELECT A.SYMBOL,A.PRICE from (EXEC FileDs.getTextFiles('*.txt')) AS f,  TEXTTABLE(f.file COLUMNS SYMBOL string,PRICE string HEADER) AS A";

its returning empty results without throwing any error.

Please help me on this.

Also help me if we can do sftp or ftp  files in remote location through teiid.

[rareddy]

Prakash,

You are confusing yourself..

1) File Translator has "getTextFies" method. This can be used to access file in the shared drives.

2) WS Translator has "invokeHTTP" method, that can be used to fetch files using HTTP

so, in your (1) first query is completely wrong. Second one, "file" protocol is not supported though "ws" translator, only "http". In your (2), as I already mentioned it only works with reachable directory path, not URLs.  As per the ftp, sftp we currently do not support them but you can log an enhancement request for it.

Ramesh..

[praksah]

I have setup same webservice model that you provided above on two machine where teiid is installed,

1. On local machine from where I access webservice model by using jdbc url

  url = "jdbc:teiid:webservice@mm://localhost:31000;showplan=on";

  Class.forName("org.teiid.jdbc.TeiidDriver");

  DriverManager.getConnection(url,"user", pwd);

  sql = SELECT * from view.Employee

Here i am able to get connection object and data from webservice model as well.

2. on remote machine and accessing webservice model by using jdbc url from local machine

  url = "jdbc:teiid:webservice@mm://remotehost:31000;showplan=on"; //note showplan setting

  Class.forName("org.teiid.jdbc.TeiidDriver");

  DriverManager.getConnection(url,"user", pwd);

  sql = SELECT * from view.Employee

I am getting TEIID15005 Error Calling HTTP Service - 404 Not Found error; I am able to get connection object, getting error while executing sql. please find detail stack trace as below

2016-02-23 14:15:31,711 WARN  [org.teiid.CONNECTOR] (Worker3_QueryProcessorQueue4) JNQApHCwfgS2 Connector worker process failed for atomic-request=JNQApHCwfgS2.0.3.1: org.teiid.translator.TranslatorException: TEIID15005 Error Calling HTTP Service - 404 Not Found

  at org.teiid.translator.ws.BinaryWSProcedureExecution.execute(BinaryWSProcedureExecution.java:166)

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

  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

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

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

  at java.lang.reflect.Method.invoke(Unknown Source)

  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(Unknown Source)

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

  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(Unknown Source)

  at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

  at java.lang.Thread.run(Unknown Source)

2016-02-23 14:15:31,727 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue5) JNQApHCwfgS2 TEIID30020 Processing exception for request JNQApHCwfgS2.0 'TEIID30504 ws_test1: TEIID15005 Error Calling HTTP Service - 404 Not Found'. Originally TeiidProcessingException BinaryWSProcedureExecution.java:166. Enable more detailed logging to see the entire stacktrace.

Please guide me..

[rareddy]

The error telling your web service call not working. Test it outside Teiid using browser before using with Teiid to make sure it works.

[praksah]

Hi Ramesh ,

Its working through browser. I am using the same url.( http://download.jboss.org/teiid/designer/data/employees/file/EMPLOYEEDATA.txt)

Also it works when my application and teiid are on same server as i have mentioned above in point #1

But when teiid is deployed on remote server and i tried to access through my application installed on localhost, its throws above exception.

[rareddy]

Remote or Local that does not influence the call to the external system. There must be something else.