-
1. Re: Custom connector/translator to access remote files.
rareddy Feb 16, 2013 11:17 AM (in response to dsusin)1 of 1 people found this helpfulDaniel,
You can use "ws" translator + web services connectors, to get to remote files. Use "invokeHTTP" method. This will return a blob then u can use TextTable to parse the contents if they are csv format. This will handle remote access with http, any other u need write a connector. If u need defined parsing of these files into tables then u need to write a translator.
Ramesh..
-
2. Re: Custom connector/translator to access remote files.
dsusin Feb 16, 2013 12:01 PM (in response to rareddy)Thanks for pointing me in the right direction Ramesh. It seems, then, that the remote CSVs will be the easy part, but I won't be free of programming some translators .
-
3. Re: Custom connector/translator to access remote files.
rareddy Feb 17, 2013 9:33 PM (in response to dsusin)yes, as I mentioned above anythinn other then fixed format, csv or xml files, then you would need a translator to parse it.
-
4. Re: Custom connector/translator to access remote files.
dsusin Feb 18, 2013 4:32 AM (in response to rareddy)For the moment I need to parse KML and KMZ files, although KML is a XML extension, TEIID seems not to recognize it, so I want to parse those files with XPath (KMZ need to be unzipped before). I've done that with regular Java programs, but creating a translator for TEIID is something I've never done before. I've downloaded the TEIID source code in order to modify some of the existing translators, what would be the more suited for what I want to do? The file translator? The WS one? Some other?
And, once I have the translator, how can I integrate it with the Designer workflow? I would need to use the ws connector to get the files via invokeHttp, but from there how would I use my custom translator to have the translated relational data?
Thanks so much.
-
5. Re: Custom connector/translator to access remote files.
rareddy Feb 18, 2013 9:13 AM (in response to dsusin)Daniel,
For the moment I need to parse KML and KMZ files, although KML is a XML extension, TEIID seems not to recognize it, so I want to parse those files with XPath
If it is XML, once you have access to the data, you can use XMLTABLE construct, to parse it into table(s). See documentation on how to use XMLTABLE. Out of box, the "ws" connector and translator combination give access to the data, however you need to write the XMLTABLE construct yourself to parse it. XMLTABLE does use XPATH. If you want use Designers wizard to parse table from a sample KML file.
(KMZ need to be unzipped before). I've done that with regular Java programs, but creating a translator for TEIID is something I've never done before. I've downloaded the TEIID source code in order to modify some of the existing translators, what would be the more suited for what I want to do? The file translator? The WS one? Some other?
Since Teiid does not know about the unzip, you can attach a Delegating Translator to "ws" translator to unzip after the data is retrieved from "ws" translator. This needs be your custom extension.
And, once I have the translator, how can I integrate it with the Designer workflow? I would need to use the ws connector to get the files via invokeHttp, but from there how would I use my custom translator to have the translated relational data?
Once you either use the above method, or create a new translator, just deploy that into the Teiid Server, and then Designer integration is automatically given.
1) So, to make things clear, if you use XMLTABLE construct, then that creates the logical view from your KML file in the Teiid, and you can you Designer's wizard for designing this. See in QuickStarts, I used Twitter's feed into a logical view in one of the examples. Although in this I inlined the view definition with out using Designer.
2) Otherwise, if KML schema is fixed, then you can write a custom translator that can automatically convert the KML into a relational table in the translator. So, in would appear as the relational source to the Teiid. In this case, if you use dynamic vdb, all you need to do is point to any source that is a KML file, it would appear as table in the Teiid. You can take look at OData translator for an example of this.
Hope this helps.
Ramesh..
PS. Any chance you can contribute any such development to Teiid and/or write a blog about it when you are done?
-
6. Re: Custom connector/translator to access remote files.
dsusin Feb 20, 2013 4:38 AM (in response to dsusin)Hello, I'm trying to deploy the sample yahoo translator in order to, firstly, have it working and, finally, use it as a starting point for my custom translator. The problem is that I'm not able to deploy it. The error on the TEIID console is this one:
ERROR [org.jboss.as.controller.management-operation] (ServerService Thread Pool -- 46) Operation ("add") failed - address: ([
("subsystem" => "teiid"),
("translator" => "yahoo")
]) - failure description: "TEIID50009 Translator \"yahoo\" not found in the module \"org.jboss.teiid.translator.yahoo\" "
I just grabbed the project from the sources and modified the pom.xml to remove the <parent> tag and modify the default compiler to 1.7 (as it defaults to 1.5 and, thus, it complains about the "@Override lines").
Then I copied the jar in the modules/org/jboss/teiid/translator/yahoo/main/ folder and created the following module.xml
<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="org.jboss.teiid.translator.yahoo">
<resources>
<resource-root path="translator-yahoo-0.0.1.jar" />
<!-- Insert resources here -->
</resources>
<dependencies>
<module name="javax.xml.ws.api"/>
<module name="javax.api"/>
<module name="javax.resource.api"/>
<module name="org.jboss.teiid.common-core" />
<module name="org.jboss.teiid.api" />
<module name="javax.wsdl4j.api"/>
</dependencies>
</module>
Finally I added the corresponding line to the standalone-teiid.xml config file:
<translator name="yahoo" module="org.jboss.teiid.translator.yahoo"/>
The modified pom.xml is this one:
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<artifactId>translator-yahoo</artifactId>
<groupId>org.jboss.teiid.connectors</groupId>
<name>Yahoo Translator</name>
<description>Test translator used to query ticker symbols from Yahoo website</description>
<version>0.0.1</version>
<dependencies>
<dependency>
<groupId>org.jboss.teiid</groupId>
<artifactId>teiid-api</artifactId>
<scope>provided</scope>
<version>8.2.0</version>
</dependency>
<dependency>
<groupId>org.jboss.teiid</groupId>
<artifactId>teiid-common-core</artifactId>
<scope>provided</scope>
<version>8.2.0</version>
</dependency>
<dependency>
<groupId>javax.resource</groupId>
<artifactId>connector-api</artifactId>
<scope>provided</scope>
<version>1.5</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<outputDirectory>target/classes</outputDirectory>
<resources>
<resource>
<directory>src/main/resources</directory>
<filtering>true</filtering>
<includes>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
<filtering>false</filtering>
<excludes>
<exclude>**/*.xml</exclude>
<exclude>**/*.properties</exclude>
</excludes>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.7</source>
<target>1.7</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
I also attach the jar file.
Any idea of why I cannot deploy it correcly? Thanks.
-
translator-yahoo-0.0.1.jar 15.0 KB
-
-
7. Re: Custom connector/translator to access remote files.
dsusin Feb 20, 2013 5:24 AM (in response to dsusin)Ok, nevermind, I have no errors now, the weird thing, though, is that the error seems to have gone away when I deleted the dependencies of the MANIFEST.MF file (I tried first the jar deployment via management console).
-
8. Re: Custom connector/translator to access remote files.
dsusin Feb 20, 2013 8:06 AM (in response to dsusin)Ok, I have created a source model in Designer which uses the yahoo translator. I don't know if I'm doing it right: I created a table "Stock" in the source model and added the columns as they appear on the getMetadata function. I can do selects but the columns are switched, ex. change appears on date, and so on. How can I know the order of the columns that the translator returns in order to create a correct table in the source model? Is there any other way to create in Designer a source model based on a custom translator?
Thanks.
EDIT: Stupid question, it depends of how the data comes from the original source. May I suggest to be able to rearrange columns inside tables in the Designer source and views editors? The only way I found is deleting the columns and adding them in the right order.
-
9. Re: Custom connector/translator to access remote files.
shawkins Feb 20, 2013 8:31 AM (in response to dsusin)> Is there any other way to create in Designer a source model based on a custom translator?
That feature should be coming to Designer in the not too distant future.
> May I suggest to be able to rearrange columns inside tables in the Designer source and views editors?
See: https://issues.jboss.org/browse/TEIIDDES-551
Steve
-
10. Re: Custom connector/translator to access remote files.
rareddy Feb 20, 2013 9:25 AM (in response to shawkins)> Is there any other way to create in Designer a source model based on a custom translator?
-
11. Re: Custom connector/translator to access remote files.
dsusin Feb 22, 2013 8:57 AM (in response to dsusin)I ended up programming a translator, TEIID does not seem to like parsing KML files with XMLTABLE. Thanks for the help.
-
12. Re: Custom connector/translator to access remote files.
shawkins Feb 22, 2013 9:13 AM (in response to dsusin)Daniel,
As long as you have valid XML, then XMLTABLE should work since it just works with standard XPath/XQuery processing via Saxon. Can you describe what wasn't working for you? Or was it just easier for your task to take a custom approach?
Steve
-
13. Re: Custom connector/translator to access remote files.
dsusin Feb 22, 2013 9:28 AM (in response to shawkins)The queries on the KML returned empty. I suspect it has something to do with the KML Namespace, as in the translator I had to implement a custom "KmlNamespaceContext" (implementing NamespaceContext). Otherwise the XPath parsing would not work either. Another proof of that is that if I removed the kml namespace from the file then the parsing worked, but, obviously, that was not an option to consider.
-
14. Re: Custom connector/translator to access remote files.
rareddy Feb 22, 2013 9:50 AM (in response to dsusin)Could you attach a sample KML file just for our understanding.
Thanks
Ramesh..