9 Replies Latest reply on Feb 8, 2016 9:58 AM by rareddy

    Federating Datasources without creating a combined Datamodel in Teiid

    reijin90

      Hello everyone!

       

      I'm currently trying to implement my bachelor thesis where the idea is to use an ontoogy as a datamodel for several different datasources (RDBMS, Excel,..). I think teiid is a great too, but I don't need it to create a global datamodel of the connected sources. Is it possible to access the data just by issuing specific SQL queries to Teiid via JDBC?

      To make my point clear an example: I want to add 2 DBMS, MySQL and PostgreSQL. Now I want to query those sources by issuing a SQL query:

       

      select person from psql.persons union select persons from mysql.persons;

       

      Depending on the query Teiid would then select the proper source (either mysql or psql).

       

      I know it is better or maybe more useful to just create a combined virtual datamodel with teiid, but that is not what I want, I need the datamodel to be the ontology and not a virtual copy of the ontology (or sth. similar).

      So far all the examples I've found do not cover this, but is it still possible?

       

      Would be really happy to hear your inputs and thoughts on this.

       

      Greetings!

        • 1. Re: Federating Datasources without creating a combined Datamodel in Teiid
          rareddy

          Yes, you can. Defining a view/logical/abstract layer is optional. You need to define your source layers and then you start writing queries on it. If you want you can use Dynamic VDB, where you would only define the source specifics and not even import the metadata, and deploy the VDB. During the deployment, it will fetch metadata of the sources and build source models, that you can query on. An example VDB look like

           

          ontology-vdb.xml

          <vdb name="ontology" version="1">
              <model visible="true" name="mysql">
                  <source name="ontology1" translator-name="mysql5" connection-jndi-name="java:/mysql-ds"/>              
              </model>
              <model visible="true" name="postgres">
                  <property name="importer.schemaPattern" value="public"/>       
                  <source name="ontology2" translator-name="postgresql" connection-jndi-name="java:/postgres-ds"/>             
              </model>
          </vdb>
          

           

          Then all you need to do is define the datasource configuration for both mysql and postgres in JBoss EAP. This is done by editing the standalone-teiid.xml file. You can find sample configuration in the "docs/teiid/datasources" directory of the server.

           

          Above is not using the Designer at all. You can also do the same using the designer, the only difference is, you need to import both source models, and then build a VDB out of it. Then when you deploy, since you already imported the metadata, it will not attempt to import again.

           

          HTH.

           

          Ramesh..

          • 2. Re: Federating Datasources without creating a combined Datamodel in Teiid
            reijin90

            I may be mistaken, but I think I still need the designer to create the dynamic VDB right?

            Is it also possible to add several postgres databases by adding several datasources I assume?

             

            //edit:

            found the answer: https://docs.jboss.org/author/display/teiid89final/Dynamic+VDBs

            No, I dont need the designer at all it seems.

             

            One last question though:

            Will this still work with heterogenous sources and how does the SQL querying from an external application happen? Say I want to access a table in mysql with the same name as in psql? how do I form my sql query to make teiid understand which data source it has to access?

             

            thanks again Ramesh, you are a life saver!

            • 3. Re: Federating Datasources without creating a combined Datamodel in Teiid
              rareddy

              >>Will this still work with heterogenous sources and how does the SQL querying from an external application happen? Say I want to access a table in mysql with the same name as in psql? how do I form my sql query to make teiid understand which data source it has to access?

               

              That the value proposition of Teiid

               

              Once you deploy this Virtual Database (VDB), for all intensive purposes that will look like RDMS for any consuming applications, just like Oracle or MySQL. Now to access it you can use JDBC, ODBC as traditional methods from your applications. If you want REST based access you need to look into OData bases access. Teiid does provide all of them. Take look at our examples, GitHub - teiid/teiid-quickstarts: Quick starts for Teiid

               

              Look at "simpleclient" in the examples for a sample Java client application.

               

              Ramesh..

              • 4. Re: Federating Datasources without creating a combined Datamodel in Teiid
                reijin90

                Okay, I've deployed a jdbc driver (deploy command) for psql and added these lines to the standalone-teiid.xml:

                                <datasource jndi-name="java:/postgres1" pool-name="postgres1" enabled="true">

                                    <connection-url>jdbc:postgresql://localhost:5432/ontop_tut</connection-url>

                                    <driver>postgresql-9.4.1207.jre7.jar</driver>

                                    <security>

                                        <user-name>postgres</user-name>

                                        <password>OMITTED</password>

                                    </security>

                                </datasource>

                Now I created a new VDB file and put it into the deployments folder:

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

                    <model visible="true" name="postgres1">

                        <property name="importer.schemaPattern" value="public"/>     

                        <source name="ontology1" translator-name="postgresql" connection-jndi-name="java:/postgres1"/>           

                    </model>

                </vdb>

                The deployment starts, but I receive an error:

                16:51:50,861 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID50030 VDB ontology.1 model "postgres1" metadata loaded. End Time: 05.02.16 16:51

                16:51:50,864 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.aff_wr_pk has no columns defined

                16:51:50,864 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.bk_pk has no columns defined

                16:51:50,864 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.course_pkey has no columns defined

                16:51:50,864 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.ed_pk has no columns defined

                16:51:50,864 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.edition_pk has no columns defined

                16:51:50,865 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.grade_pkey has no columns defined

                16:51:50,865 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.pk_au has no columns defined

                16:51:50,865 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.pk_emerge_authors has no columns defined

                16:51:50,865 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.pk_gen has no columns defined

                16:51:50,865 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.pk_st has no columns defined

                16:51:50,866 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.pr_pk has no columns defined

                16:51:50,866 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.semester_pkey has no columns defined

                16:51:50,866 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.student_pkey has no columns defined

                16:51:50,866 WARN  [org.teiid.PLANNER.RESOLVER] (teiid-async-threads - 2)  TEIID31071 Invalid table; Table postgres1.public.tb_stores_pkey has no columns defined

                16:51:50,867 INFO  [org.teiid.RUNTIME] (teiid-async-threads - 2)  TEIID40073 The metadata for the VDB ontology.1 is loaded, however it is not valid. Check models for errors. Correct the metadata and re-deploy.

                16:51:50,867 INFO  [org.teiid.RUNTIME.VDBLifeCycleListener] (teiid-async-threads - 2)  TEIID40003 VDB ontology.1 is set to FAILED

                The important bit is the error, which tells me that the meta-data is not valid, so I guess I have to change the importer property?

                 

                I found this page JDBC Translator - Teiid 8.9 - Project Documentation Editor but I dont really undertand what fails here. It seems it scans the table, but does not "like" empty columns.

                 

                For the next step:

                If i want to query this table I'd have to query sth. like:

                select * from postgres1.public.tb_books?

                 

                //Edit:

                Imgur: The most awesome images on the Internet

                all those "tables" which are found, are not actually tables, but constraints in psql does that mean I have to create a model then? :/

                • 5. Re: Federating Datasources without creating a combined Datamodel in Teiid
                  rareddy

                  >>The important bit is the error, which tells me that the meta-data is not valid, so I guess I have to change the importer property?

                   

                  Yes. It is importing the all table types. You may want restrict that using "tableTypes" property. Add following.

                   

                   

                  <property name="importer.tableTypes" value="TABLE">

                   

                   

                  Ramesh..

                  • 6. Re: Federating Datasources without creating a combined Datamodel in Teiid
                    reijin90

                    DUDE! You are awesome man! Great way to start into my leasure time! it works and the query i tried went through!

                     

                    There is a small typo in your text though, you forgot the / before the >:

                    <property name="importer.tableTypes" value="TABLE" />

                     

                    Thanks a lot!!!

                     

                    //edit:

                    I will try to add an other datasource tomorrow.

                    • 7. Re: Federating Datasources without creating a combined Datamodel in Teiid
                      rareddy

                      Great! Your thesis done?

                       

                      Kidding, hoping to learn and contributions from your work in the future.

                       

                      Ramesh..

                      • 8. Re: Federating Datasources without creating a combined Datamodel in Teiid
                        reijin90
                        Great! Your thesis done?

                        haha, I wish! Hopefully I can get this up and running soon. Excel worked as well, I will have to find out, how to access files on the network later. Will it work by just putting a network path to the resource or would I have to select an other resource adapter?

                         

                        Thanks again for your help! (but probably I'll write with you again)

                        • 9. Re: Federating Datasources without creating a combined Datamodel in Teiid
                          rareddy

                          Yes, the "file" resource-adapter can also read from network drive or shared drive as along as t is reachable from system it is being accessed. As per completely remote, if it is reachable though "http" then you can web services resource adapter. We do not have ftp/sftp support yet. BTW, there is another thread on this subject in community now. With Teiid data vartulization; read file which is located on remote machine