6 Replies Latest reply on Jul 2, 2014 8:44 AM by Joseph CHIDIAC

    Embedded Teiid : Queries issues

    Joseph CHIDIAC Novice

              Hi,

      I'm using Teiid Embedded

      but i have an issue about the query execution, i have 2 separate MYSQL databases on the same serveurs

      an i have a query using tables from these 2 databases

      i'm worrying about if teiid execute one query via MYSQL JDBC or  2 separates queries .

       

      the executed query :

      SELECT region.ID AS region_ID,

             region.Region AS region_Region,

             wicountry.Code AS wicountry_Code,

             wicountry.CountryID AS wicountry_CountryID

            

        FROM OrdersModel.Orders.region region

             LEFT OUTER JOIN

             WorldIndicatorsModel.WorldIndicators.wicountry wicountry

                ON region.ID = wicountry.CountryID

      LIMIT 0, 30

       

      Orders and WorldIndicators are 2 databases on my local MYSQL serveur

       

       

       

      Thx.

        • 1. Re: Embedded Teiid : Queries issues
          Ramesh Reddy Master

          If they are two separate schemas, with two different names (in the vdb.xml file, the "source" element name under "model") then Teiid will issue two different queries.

           

          Ramesh..

          • 2. Re: Embedded Teiid : Queries issues
            Joseph CHIDIAC Novice

            I deploy a one VDB that contains 2 models for the 2 DataBases

             

            List<ModelMetaData>models = new ArrayList<ModelMetaData>() ;

             

             

            String dbName1 = "Orders"
            ModelMetaData modelMetaData = new ModelMetaData();
            modelMetaData.setName( serviceName + "VDB" );
            modelMetaData.addProperty("importer.tableTypes", "TABLE,VIEW");
            modelMetaData.addSourceMapping(dbName1, "mysql5", dbName1+ "ds");
            models.add(modelMetaData) ;
            //
            dbName1 = "WorldIndicators"
            modelMetaData = new ModelMetaData();
            modelMetaData.setName( serviceName + "VDB" );
            modelMetaData.addProperty("importer.tableTypes", "TABLE,VIEW");
            modelMetaData.addSourceMapping(dbName1, "mysql5", dbName1+ "ds");
            models.add(modelMetaData) ;

             

             

            testVDBMetaData = new VDBMetaData();
            testVDBMetaData.setXmlDeployment(true);
            testVDBMetaData.setName("test");
            testVDBMetaData.setModels(models);
            deployVDB(testVDBMetaData, null);

             

            something wrong in my example ?

            • 3. Re: Embedded Teiid : Queries issues
              Joseph CHIDIAC Novice

              it's a way to deploy these 2 databases to force teiid to execute such this case in one query ?

              • 4. Re: Re: Embedded Teiid : Queries issues
                Ramesh Reddy Master

                Joesph,

                 

                It looks about right. There is an example in the embedded kit if you want to reference that does similar integration between file and relational data source. If you are not familiar with Teiid and how it operates, I suggest you start with Designer based tooling to build VDBs and use Teiid Server to verify your usecase. Then you can come back to embedded to see how you can do the same programatically.

                 

                Ramesh..

                • 5. Re: Embedded Teiid : Queries issues
                  Steven Hawkins Master

                  Joseph,

                   

                  Based upon what you have above, the short answer is that separate source queries will be issued as you are pointing each model to a different datasource.  From Teiid's perspective all we see are different datasources - it doesn't matter that they point to same server, etc.  If there is a way for a single datasource to access what each of your datasources can access, then you can easily modify your setup to issue a single query.  Otherwise you would need some form of materialization or table conformity to populate relevant tables on a single source.

                   

                  Steve

                  • 6. Re: Re: Embedded Teiid : Queries issues
                    Joseph CHIDIAC Novice

                    I'm familiar with teiid

                    but i want to all the queries based on the Same DataBase Server Type and same Serveur : 2 MYSQL databases hosted on my laptop

                    will be execute as one JDBC query and not seprate JDBC Query