-
1. Re: With Teiid data vartulization; read file which is located on remote machine
rareddy Feb 3, 2016 8:27 AM (in response to praksah)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
praksah Feb 3, 2016 9:50 AM (in response to rareddy)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
rareddy Feb 3, 2016 10:18 AM (in response to praksah)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
praksah Feb 4, 2016 9:58 AM (in response to rareddy)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
rareddy Feb 4, 2016 10:14 AM (in response to praksah)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
rareddy Feb 4, 2016 1:48 PM (in response to praksah)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
praksah Feb 5, 2016 10:59 AM (in response to rareddy)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
rareddy Feb 5, 2016 11:28 AM (in response to praksah)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
praksah Feb 23, 2016 4:00 AM (in response to rareddy)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
rareddy Feb 23, 2016 8:28 AM (in response to praksah)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
praksah Feb 23, 2016 9:50 AM (in response to rareddy)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
rareddy Feb 23, 2016 10:42 AM (in response to praksah)Remote or Local that does not influence the call to the external system. There must be something else.