How to connect to and retrieve data from CSV files using Teiid?

[tarifezaz]

Dear all,

I have been trying to access CSV files for performing CRUD operations using Teiid. I have already inserted the resource adapter in standalone-teiid.xml file. Here's the file specific resource adapter.

<resource-adapter id="file">

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

               use-java-context="true"

               pool-name="teiid-file-ds">

               <config-property name="ParentDirectory">/home/tarif/testing/</config-property>

               <config-property name="AllowParentPaths">true</config-property>

           </connection-definition>

      </connection-definitions>

</resource-adapter>

I have saved a CSV file under the /home/tarif/testing directory. I have also deployed a virtual database for file. Here's the XML for that file:

<vdb name="myfileVDB" version= "1">

    <model name = "filemodel">

        <source name="fileitis" translator-name="file" connection-jndi-name="java:/fileDS"/>

    </model>

</vdb>

When I am running the ./standalone.sh command, the VDB myfileVDB is being set to active. But when I am running a query using JDBC to show all the available tables, I can't see any name for the CSV file that I have saved inside the /home/tarif/testing directory. Should I somehow need to specify that file so that the information regarding that file becomes visible from Teiid JDBC?

Regards

Tarif

[shawkins]

Teiid does not perform auto discovery of your text file contents.  When you add a file source all that provides are procedures for accessing files on the filesystem - File Translator - Teiid 8.10 (draft) - Project Documentation Editor.  From there you would use TEXTTABLE - FROM Clause - Teiid 8.10 (draft) - Project Documentation Editor

Teiid Designer does provide wizard based help for setting utilizing TEXTTABLE as well.

[blafond]

You can find instructions in this example: Connect to a Flat File using Teiid

[rareddy]

If you want to use the Dynamic VDB here is an example as to how to do it Text to Table with Teiid

[tarifezaz]

Thanks guys. I'll try them and will let you the updates.

[tarifezaz]

Ramesh, I have tried your tutorial, but I am having the following error:

org.teiid.jdbc.TeiidSQLException: TEIID30352 MARKETDATA.GETTEXTFILES does not exist.;

But I already saved my table inside "home/tarif/testing" directory.

What should I do? I have been using Teiid 8.8.0 and JBoss EAP 6.1.0

[rareddy]

Per your VDB XML above, the transformation needs to be "fileModel.getTextFiles" in your view or source query. BTW, what is query you are executing.

[tarifezaz]

Thank you Ramesh, it finally worked! My query was:

	select stock.* from (call fileModel.getTextFiles('*.txt')) f,
	TEXTTABLE(f.file COLUMNS symbol string, price double HEADER) stock

It's from your tutorial. Thanks again!

[tarifezaz]

Hi Ramesh, I have read in your comment that the file translator is read-only. Is there any workaround that come out lately?

[shawkins]

The file translator/resource adapter also have write methods, but that is at the whole file level.  That does not provide an easy path to updates into a csv file as you would have to write instead of triggers for the view containing texttable and update the entire contents.  If you are looking for a table that is backed by a csv file, then it may be looking at integrating that functionality through hsql - http://hsqldb.org/doc/guide/texttables-chapt.html

[tarifezaz]

Thanks Steven. I have been trying out HSQL, I hope it will serve my purpose!