Question: How to integrate horizontal shards
meltedmetal Oct 18, 2010 5:18 AMDear 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.
- 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?
- 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?
- 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?
Any hints are welcome. It could be about my encountered problems of my solutions OR other solutions?
Thank you again!
Best Regards,
S.Q