11 Replies Latest reply on Sep 23, 2010 10:11 AM by julien reboul

    Teiid and Shards

    julien reboul Newbie

      Hi all,

       

      does Teiid VDB are able to manage sharded database? I haven't found docs about that.

       

      thanks

        • 1. Re: Teiid and Shards
          Ramesh Reddy Master

          Julien,

           

          Yes it does. Teiid supports both horizontal and vertical sharding. It also handles non relational sources too. I am not sure what exactly you are looking for, however we have many documents and tools to help you with your data integration needs. Let us know how we can help.

           

          Thanks

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: Teiid and Shards
            julien reboul Newbie

            Hi Ramesh and thanks for your answer.

             

            In fact, I have several databases with the exact same databases structures but not same data and I would like to use JPA with them without having the burden of declaring one EntityBean per database. Instead, I want to have one JCA DataSource with which my EntityBean would be associated to.

             

            Up to now, I have only tried a simple configuration of teiid inside my JBoss 5.1 with two database with the same structure. When I started TeiidDesigner and asking a simple SQL Query retriveing the content of a table (which is on both bases), it rose me a 'ambiguous error' saying that it didn't know which base to query.

             

            Where can I find some configuration example about sharding in this way in teiid?

             

            Thanks in advance

            • 3. Re: Teiid and Shards
              Ramesh Reddy Master

              Julien,

               

              If your schema information in the both the databases is same, Teiid calls this feature as "multi-source", so you can check the documents with that name.

               

              Secondly, looks like you may have imported schema from both the source models and created two different models. In this case the same schema exists in two different models, thus the "ambiguity" error if you do not qualify your queries with "model" name. If you use one model for each source scenario, you need to write your own virtual layer to combine the results from each source. Which is fine, if you are looking for fine grained control over which sources to union.

               

              However, the above is not required in the "multi-source" scenario. Here, you import the schema from one of the sources, create one model, but you define multiple sources as data inputs to this model, thus a single model is representing multiple sources. When the VDB is designed this way, when you query a table in this model, Teiid will automatically query all the sources and union the data and give you a consolidated single result.

               

              Currently our tooling is not setup to configure the "multi-source" scenario, however it is very easy to do manually. Just build the VDB as if you are dealing with single source, then open the vdb with any zip utility (it is simple archive) and edit the "vdb.xml" to add the additional sources. Here is an example

               

              <vdb name="vdbname" version="1">
                  <model visible="true" type="PHYSICAL" name="Customers" path="/Test/Customers.xmi">
                        <property value="3984726057.INDEX" name="indexName"/>
                        <source name="chicago" translator-name="oracle" connection-jndi-name="chicago-customers"/>
                        <source name="newyork" translator-name="oracle" connection-jndi-name="newyork-customers"/>
                        <source name="la" translator-name="oracle" connection-jndi-name="la-customers"/>
                   </model>
              </vdb>
              

               

              In the above example, I have a "Customers" model that I created in the Teiid Designer. I imported the schema from "chicago" customers database and created the VDB. Then I manually edited the "vdb.xml" and added the "newyork" and "la" customers as additional sources.

               

              Note, that before you deploy the VDB into Teiid runtime (JBoss AS with Teiid), make sure you created the data sources (-ds.xml files) for "chicago", "newyork" and "la" sources with the defined jndi names as specified above.

               

              Now, in your client application just write the SQL quries aginst "customers" model, and all your data from all the sources will be aggregated.

               

              Hope this explains clearly how to solve your issue. Let us know if you need more info. Tooling support is coming soon.

               

              Thanks.

               

              Ramesh..

              • 4. Re: Teiid and Shards
                julien reboul Newbie

                Hi Ramesh,

                 

                I tried to do as you said but I encountered another problem... When I use a single DS in the vdb.xml, the queries execute normally. However, when I add another DS in the vdb.xml as you aforementioned it, it raises the following error when asking for results in a table (select * from group) :

                 

                16:16:34,562 ERROR [PROCESSOR] Unexpected exception for request fiTDJmHo6M6i.0
                java.lang.NullPointerException
                at org.teiid.query.metadata.TransformationMetadata.getGroupsForPartialName(TransformationMetadata.java:207)
                at org.teiid.query.metadata.BasicQueryMetadataWrapper.getGroupsForPartialName(BasicQueryMetadataWrapper.java:158)
                at org.teiid.query.metadata.BasicQueryMetadataWrapper.getGroupsForPartialName(BasicQueryMetadataWrapper.java:158)
                at org.teiid.query.resolver.util.ResolverUtil.resolveGroup(ResolverUtil.java:900)
                at org.teiid.query.resolver.QueryResolver.isXMLQuery(QueryResolver.java:244)
                at org.teiid.query.resolver.QueryResolver.chooseResolver(QueryResolver.java:197)
                at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:171)
                at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:126)
                at org.teiid.query.resolver.QueryResolver.resolveCommand(QueryResolver.java:100)
                at org.teiid.dqp.internal.process.Request.resolveCommand(Request.java:277)
                at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:378)
                at org.teiid.dqp.internal.process.Request.processRequest(Request.java:437)
                at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:352)
                at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:174)
                at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
                at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:188)
                at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:116)
                at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:290)
                at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
                at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
                at java.lang.Thread.run(Thread.java:619)

                 

                I don't know if it helps but there was no error when sumbitting a simple query (select 1).

                 

                The fact is that I do have the same database structure on different server but the database name is different from a server to another. Does the xmi have to be change to handle this?

                 

                I didn't found any help about multi-source in the differents teiid-docs's pdf distribution zip files. Is it on the wiki or elsewhere?

                 

                Thanks

                • 5. Re: Teiid and Shards
                  Ramesh Reddy Master

                  I didn't found any help about multi-source in the differents teiid-docs's pdf distribution zip files. Is it on the wiki or elsewhere?

                  I thought we already have docs on this, apparently not, so here is the JIRA https://jira.jboss.org/browse/TEIID-1255

                   

                  The fact is that I do have the same database structure on different server but the database name is different from a server to another. Does the xmi have to be change to handle this?

                  That is not a problem, XMI files are only to be modified using the Designer. Manual editing them is no good, as they are not consumed as is, they get converted to ".INDEX" files by Designer before Teiid runtime consumes it. The "-ds.xml" file you define, configures the sources to point to different instances of databases. The model inside the VDB just portrays the schema of the database.

                   

                  I am sorry, I forgot one extra property to make it "multi-source", here is the corrected snippet of vdb.xml

                  <vdb name="vdbname" version="1">
                      <model visible="true" type="PHYSICAL" name="Customers" path="/Test/Customers.xmi">
                            <property value="3984726057.INDEX" name="indexName"/>
                            <property name="supports-multi-source-bindings" value="true"/>
                            <source name="chicago" translator-name="oracle" connection-jndi-name="chicago-customers"/>
                            <source name="newyork" translator-name="oracle" connection-jndi-name="newyork-customers"/>
                            <source name="la" translator-name="oracle" connection-jndi-name="la-customers"/>
                       </model>
                  </vdb>

                   

                  That should fix it. The exception you showed is something else, however that is of concern. Exception is saying that the model name in the VDB.xml does not match to the model from VDB index files. I want to make sure, that the above XML is sample snippet, you are only supposed to add "source" and "property" elements every other information previously generated by the Designer need to be kept same in your vdb.xml file.

                   

                  If you are still seeing the error after the above modification, please attach the VDB and query you are executing, I can debug and let you know what is wrong.

                   

                  Thanks.


                  Ramesh..

                  • 6. Re: Teiid and Shards
                    julien reboul Newbie

                    This works perfltectly with the supports-multi-source-bindings property. Thank you.

                     

                    Now, I have a couple of questions.

                    In fact, I have the same database structure has I told you previously, but each of them doesn't know there are another ones like it. What I mean is that I do not have a table identifier that is unique for every base. I called the following query where id is the unique identifier in single DB: 'select id, name, url from rss where id = 449' and I got two results, one for each DB.

                     

                    Is there a way for Teiid to add the DB name somewhere in the table returned? and, on the other side, how can I tell it to chose a specific DB for insert statements in order to link an entry with its associated rss in the proper DB ?

                     

                    do I have to create another id column that would be generated programatically (via hibernate for instance) to answer my first question? but would it be enough to answer the second one? how to teiid manage insert for multi-source DB?

                     

                    Thank you ramesh for your time

                    • 7. Re: Teiid and Shards
                      Ramesh Reddy Master

                      Yes, Teiid does!!!

                       

                      Every time a model is marked as "multi-source", the runtime engine adds a additional column called "SOURCE_NAME"to every table in that model. This column maps to the source's name from the XML above. For example, in the above xml that would be "chicago", "la", "newyork". So essentially you can write queries like

                       

                      select * from table where SOURCE_NAME = 'newyork'
                      update table column=value  where SOURCE_NAME='newyork'
                      delete from table where column = x and SOURCE_NAME='newyork'
                      

                       

                      Note that when you do not supply the "SOURCE_NAME" in the criteria, it applies to all the sources.

                       

                      Unfortunately Teiid currently do not support INSERT, there was some talk about it before, but never got captured into a requirement. If you would like to see this feature, you can open up a JIRA on this.

                       

                      Thanks

                       

                      Ramesh..

                      • 8. Re: Teiid and Shards
                        Steven Hawkins Master

                        Julien,

                         

                        I just wanted to add a couple of things.  You should also be able to use the source_name pseudo column in the select clause, e.g. select source_name, ... from table ...

                         

                        You can also manually add a source_name string column to your multi-source tables in Designer, then your views and procedures in Designer can be multi-source aware as well.

                         

                        Steve

                        • 9. Re: Teiid and Shards
                          julien reboul Newbie

                          arg, so sad to read that teiid doesn't support insert yet. I have this need for my project... I therefore cannot use teiid for the moment.

                          I have created the JIRA entry .

                           

                          thanks for help anyway.

                           

                          • 10. Re: Teiid and Shards
                            Steven Hawkins Master

                            Ah, now I understand the JIRA you just logged.  We do support insert, what Ramesh meant is that we currently don't support insert using the source_name pseudo-column to direct the insert to a particular source in a multisource scenario.  I'll update the JIRA to be particular to multisource using source_name.

                            • 11. Re: Teiid and Shards
                              julien reboul Newbie

                              oups, sorry about that. I didn't know ramesh was only talking about multi_source insert. It sounded quite astonishing that Teiid wasn't supporting insert statements.

                               

                              Thanks Steven for the update