12 Replies Latest reply on Feb 23, 2016 10:42 AM by Ramesh Reddy

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

    Prakash Jape Newbie

      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.

        • 1. Re: With Teiid data vartulization; read file which is located on remote machine
          Ramesh Reddy Master

          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.

          • 2. Re: With Teiid data vartulization; read file which is located on remote machine
            Prakash Jape Newbie

            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

            • 3. Re: With Teiid data vartulization; read file which is located on remote machine
              Ramesh Reddy Master

              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.

              • 4. Re: With Teiid data vartulization; read file which is located on remote machine
                Prakash Jape Newbie

                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>

                • 5. Re: With Teiid data vartulization; read file which is located on remote machine
                  Ramesh Reddy Master

                  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.

                  • 6. Re: With Teiid data vartulization; read file which is located on remote machine
                    Ramesh Reddy Master

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

                    • 7. Re: With Teiid data vartulization; read file which is located on remote machine
                      Prakash Jape Newbie

                      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.

                      • 8. Re: With Teiid data vartulization; read file which is located on remote machine
                        Ramesh Reddy Master

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

                        • 9. Re: With Teiid data vartulization; read file which is located on remote machine
                          Prakash Jape Newbie

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

                          • 10. Re: With Teiid data vartulization; read file which is located on remote machine
                            Ramesh Reddy Master

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

                            • 11. Re: With Teiid data vartulization; read file which is located on remote machine
                              Prakash Jape Newbie

                              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.

                              • 12. Re: With Teiid data vartulization; read file which is located on remote machine
                                Ramesh Reddy Master

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