3 Replies Latest reply on Sep 24, 2014 5:16 PM by sudeep das

    teiid embedded : How to fully qualify source tables

    sudeep das Newbie

      I am experimenting with teiid embedded, where I am trying to have the same mysql database show up in two different models ( They will be different ultimately, but for simplicity and experiment, i am keeping them the same). I am starting off with setting up just one of them, and I am using the mysql sample employees database. My model, and vdb details are like this :

       

      modelMetaData.setName("leftModel");

      modelMetaData.addSourceMapping("left" ,"mysql5","left-ds");

      ...

      ...

      server.deployVDB("myVDB",modelMetaData);

      ...

      ...

      The source DB has a table employees, which I am able to access with

      select * from employees

       

      However, since I am going to have the same db in another model (lets say right) , it would be good to have some kind of qualifier to qualify the left and right databases.

       

      I tried

      select * from left.employees

      select * from leftModel.employees

      select * from left-ds.employees

       

      and in cases, I get a group does not exist...

      This works when I create the models in teiid designer, so I am not sure what am I missing in the embedded side. Any suggestions on how I can achieve this ?

        • 1. Re: teiid embedded : How to fully qualify source tables
          Ramesh Reddy Master

          select * from leftmodel.employees

           

          is right way to do it. The model name becomes schema name over the VDB. However, this all depends on how you imported the metadata of of "employees" from mysql database. See this JDBC Translator - Teiid 8.9 (draft) - Project Documentation Editor for available importer properties to fine tune how to import the metadata. Try "useFullSchemaName" to "false"

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: teiid embedded : How to fully qualify source tables
            Steven Hawkins Master

            In your case "left-ds" is the name of the data source, which isn't used in way. "left" is the source name - which is more typically the same as the model name.  It really only needs to be different than the model name in multi-source scenarios where there are more than 1 source under a model that need unique names to identify.  The naming rules are roughly:

             

            catalog name = vdb name

            schema name = model name

            table name = depends upon import options

             

            As Ramesh is saying the table name can be controlled by properties including useFullSchemaName.  So the same queries you show above it's perfectly fine to use the Teiid model/schema qualification - as long as the table name that you have imported is just employees.  If the table name includes the source catalog/schema name, then it won't resolve.  Setting useFullSchemaName to false allows you to just use the imported table name as the Teiid table name without adding the source catalog/schema.

            • 3. Re: teiid embedded : How to fully qualify source tables
              sudeep das Newbie

              Stevens, Ramesh ...,

              sincere thanks for the detailed explanations. I am heading the multi source way, and so your responses actually helps me figure out the correct mappings without breaking my head... helps me not  just with my immediate problem , also helps me get my future stuff right.

               

              It worked fine once I understood the relationships and naming conventions, thanks again