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

    Embedded Teiid : Queries issues

    jchidiac
    jchidiac Jul 1, 2014 8:06 AM

              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.

      • 146 Views
      • Tags:
        • 1. Re: Embedded Teiid : Queries issues
          rareddy
          rareddy Jul 1, 2014 9:13 AM (in response to jchidiac)

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

            • Actions
          • 2. Re: Embedded Teiid : Queries issues
            jchidiac
            jchidiac Jul 2, 2014 12:59 AM (in response to rareddy)

            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 ?

              • Actions
            • 3. Re: Embedded Teiid : Queries issues
              jchidiac
              jchidiac Jul 2, 2014 3:08 AM (in response to rareddy)

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

                • Actions
              • 4. Re: Re: Embedded Teiid : Queries issues
                rareddy
                rareddy Jul 2, 2014 8:16 AM (in response to jchidiac)

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

                  • Actions
                • 5. Re: Embedded Teiid : Queries issues
                  shawkins
                  shawkins Jul 2, 2014 8:40 AM (in response to rareddy)

                  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

                    • Actions
                  • 6. Re: Re: Embedded Teiid : Queries issues
                    jchidiac
                    jchidiac Jul 2, 2014 8:44 AM (in response to rareddy)

                    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

                      • Actions