2 Replies Latest reply on Oct 19, 2010 4:47 AM by meltedmetal

    Question: How to integrate horizontal shards

    meltedmetal

      Dear all,

       

      I  am using Teiid 7.1 to implement a module of our project. I struggle the  problem several days. I am very appreciative of your help and hints.

      This is my first time to issue a discussion. It is a little long, please give me some patient. Thank you.

       

      • Description of the problem
        A Teiid virtual database named ‘DFS’ includes all the data of the  system. (The virtual database is implemented by other module.) Another  virtual database named ‘workspace’ is needed to implement by me. The  ‘workspace’ VDB provides a part of ‘DFS’ data and an oracle database  named ‘localdb’ used to store the inserted/modified data. The oracle  database and the ‘DFS’ VDB      have same schema. All operations of  inserting / updating / deleting are only permitted on ‘localdb’  database. And another oracle database named ‘provision’ stores the  references (primary keys) to the records of ‘DFS’ VDB. The references  are used to filter out the part records that are visible to the users of  ‘workspace’ VDB.

        Maybe some unclear. In one words, there are 4 datasource:
        • ‘DFS’ --- VDB, includes all data.
        • ‘Provision’ --- oracle database, used to get part of data stored in ‘DFS’. We can treat ‘DFS’+’provision’ as ‘DFS’ for simplify.
        • ‘Localdb’ --- oracle database, has same schema with ‘DFS’, used to store the inserted/modified data.
        • ‘Workspace’ --- VDB, is exposed to users, has 3 underneath datasource ‘DFS’+ ‘provision’ and ‘localdb’.

       

      So  I think it is an integration of horizontal shards. I have thought out  two solutions, but I encountered problems in both solutions.

       

      • Solution 1:
        The ‘DFS’ and ‘localdb’ have same schema, so I want to use extra  property ‘supports-multi-source-bindings’ to integrate the two  datasources to one source model. But I don’t want that users of  ‘workspace’ care about the ‘SOURE_NAME’ column. So I want to create a  view model by transforming the source model of  ‘supports-multi-source-bindings’.  Then I can do different operations  according to the ‘SOURCE_NAME’ column in the update transformation of  view model.

        Problems encountered:
        • In  the Teiid Designer, I can’t see the ‘SOURCE_NAME’ columns of  'multi-source' model added by teiid engine in the view model. Will the  support be implemented recently? Is there a plan for it in Teiid  designer?
          If it is implemented, it will very helpful for us.

          I have tried to modify the INDEX file, but the modified VDB can’t be deployed because of wrong file format.

       

      • Solution 2:
        In the ‘workspace’ VDB, there are two same source models for two  datasources ‘DFS’ and ‘localdb’ having same schema. A view model is  transformed from the union of the two same source models.

        Problem encountered:
        • How  can I justify which datasource the record is from in the view model  update transform procedure, when the select transformation is a union  statement from two different tables from different models?
          In the update procedure,      
          I tried to query 'select * from viewmodel.schema.table1 where criteria' to get all records needed to be updated, but the query statement is not valid, because the designer validation said ‘the target cannot be a source to itself’ and 'where criteria' without ‘TRANSLATE’  is not correct syntax. If the above statement is executed correctly, I  can check the all records one by one to justify which datasource the  record is from. Is there other ways to do this?
        • And as mentioned in ‘5.3.3.2. TRANSLATE CRITERIA’ of the reference doc, ''By  default, a mapping is created based on the SELECT clause of the SELECT  transformation (view column gets mapped to expression in SELECT clause  at same position).
          I found that:
          When the SELECT  transformation is a union clause from two tables, the criteria always  are translated to the column identifiers of the first       table of the  SELECT clause of SELECT transformation. So when the query statements  are about the second table including ‘WHERE TRANSLATE CRETIRIA’, the  statements are not correct.
          How can I modify the default mapping of "TRANSLATE CRETIRIA"? Let it can have different mappings depending on which model  the query operates on, when the select transformation is a union statement from two different tables from different models?

       

                Any hints are welcome. It could be about my encountered problems of my solutions OR other solutions?

       

                Thank you again!

       

       

      Best Regards,

      S.Q

        • 1. Re: Question: How to integrate horizontal shards
          rareddy

          For your usecase, multi-source is the answer since you need to know the source_name while updating so solution 2 does not work.

           

          In solution 1, Designer will not automatically add "SOURCE_NAME" column when the schema is imported. In the view definition you would need to add this column manually.  If you want to hide the "SOURCE_NAME" from your users,

           

          1) Create a another view that does not have "SOURCE_NAME" on it, and make the view with "SOURCE_NAME" as "isVisible" false. You can set this while creating the VDB in the Designer. Then user will not see this table in the metadata. So, it is not visible.

           

          2) Or you can create "data-role" that marks this column as not readable, then it will not be accessable by anybody.

           

          Index files are binary files, hand editing them will lead to corrupted files, never do that.

           

          Hope this helps.

           

          Ramesh..

          • 2. Re: Question: How to integrate horizontal shards
            meltedmetal

            Hi, Ramesh

             

            Thanks very much for your patience of reading so long question and answer.

            I think the question is covered by my other two discussions http://community.jboss.org/message/567079#567079 and http://community.jboss.org/message/567098#567098. And you have replied the two discussions.

            I think we will discuss this problem in those two discussions.

             

            Thanks for your great supports.

             

            Best Regards,

            S.Q