Custom connector/translator to access remote files.

[dsusin]

Hello,

I want to integrate to a Virtual Database information from remote files accessible via http. Those remote files are some KML/KMZ files, CSV files and RDF files. While I have no problem with the parsing part, the TEIID integration one, is another thing... I'd like to know which is the best way to do it. The file connector included in TEIID does not support remote files (only remote files allowed seem to be XML with correct extension in any case...), so I guess I should program a custom connector in order to access remote files and, after that, custom translators for every new format I want to integrate. Am I right for now? Please, if there is another, and easier way, to do it I'd love to know. Until now, I did the translations using H2 stored procedures (and accessing the mem databases via JDBC from TEIID), but someone pointed me that that was not a very good idea for production systems.

So I downloaded TEIID source code and I renamed the regular file connector to "remote file connector" and deployed it to do the simplest possible test. The question is: Where do I select this new connector from Designer when I'm creating a new datasource?

After correcting some issues importing the project to eclipse, the maven project creates a jar file and a rar one, but this last one seems to be empty inside, so I'm deploying the jar one... is that correct?

Any advice would be very much appreciated.

Thanks.

[rareddy]

Daniel,

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

[dsusin]

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 .

[rareddy]

yes, as I mentioned above anythinn other then fixed format, csv or xml files, then you would need a translator to parse it.

[dsusin]

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.

[rareddy]

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?

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

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

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

[shawkins]

> 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

[rareddy]

> Is there any other way to create in Designer a source model based on a custom translator?

https://issues.jboss.org/browse/TEIIDDES-1575

[dsusin]

I ended up programming a translator, TEIID does not seem to like parsing KML files with XMLTABLE. Thanks for the help.

[shawkins]

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

[dsusin]

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.

[rareddy]

Could you attach a sample KML file just for our understanding.

Thanks

Ramesh..