7:36 p.m.
[
https://issues.jboss.org/browse/TEIID-2309?page=com.atlassian.jira.plugin...
]
Steven Hawkins commented on TEIID-2309:
---------------------------------------
The initial approach is to add a conformed sources extension property on tables. As
mentioned about this could be set at design time, in the vdb.xml, or even at runtime. The
property is expected to a comma separated list of physical schema names.
There is no expectation that a metadata entry exists on the other schemas. The drawback
of course is that we won't have source specific metadata (just as with multi-source) -
which means that all source table/column names must be the same.
We are also making the simplifying assumption that the capabilities are the same across
conformed sources. This will likely need to be addressed later if this feature is used
when the sources are dissimilar.
The engine will take the list of conformed sources and associated a set of model metadata
ids to the corresponding access node. The logic considering joins and subqueries have
been altered to also consider the conformed sets when making pushdown decisions. To fit
well with the current logic the subquery handling will only check for conformed sources
for the subquery - not in the parent. So having a conformed table in the subquery will
pushdown as expected, but not vice versa. The logic for select/project/grouping/set
pushdown was also updated to narrow the conformed sets as the access node.
In some more complex situations the logic for grouping access for pushdown will likely
need to be updated as this approach is entirely greedy at this point - whereas the order
in which join sources are grouped for pushdown can matter with conformed tables. For most
simplistic situations though this handling should be sufficient.
Add support for "conformed" tables in Teiid
-------------------------------------------
Key: TEIID-2309
URL: https://issues.jboss.org/browse/TEIID-2309
Project: Teiid
Issue Type: Feature Request
Components: Query Engine
Reporter: Debbie Steigner
Assignee: Steven Hawkins
Fix For: 8.6
Teiid would support tables from different data sources being marked as
"conformed", meaning they are the same (or perhaps a different name). When
optimising a query, it would take the conformity into account and choose the appropriate
copy of the table (presumably one in the same database as other tables in the query, if
available). I would not regard it as a problem if Teiid *required* the dimensions to be
strictly the same as opposed to permitting subsets, though as with so many areas, it would
be up to the user to ensure this was really true: I would not expect the engine to do
anything to verify that the tables really were conformed.
Usecase:
In Data Warehousing, it is relatively common to have multiple copies of the same
dimensions spread over multiple Data Warehouses or Marts, or in the same Data Warehouse
when associated with different Fact Tables. If these copies are either identical or
strict subsets of an idealised dimension (and, by extension, share *exactly* the same
naming and structure), then they may be said to be "conformed". It is expected
that the dimension includes at least the values required to support the facts in the
database in which it occurs or the Fact Table to which it is paired.
Example:
Source S1:
BIGBIGBIG (millions of rows)
bigkey
ccy
other_stuff
CURRENCY (100s of rows) let's call it S1_CCY if we need to distinguish
ccy
ccy_name
Source S2:
BIGGER (millions of rows)
biggerkey
bigkey
ccy
more_stuff
CURRENCY (100s of rows) similarly, S2_CCY
ccy
ccy_name
When executing:
SELECT B.*
FROM BIGBIGBIG B,
CURRENCY CCY
WHERE B.ccy = CCY.ccy
AND CCY.ccy_name LIKE "%DOLLAR%"
Then it is clearly advantageous to use the copy of CURRENCY in S1 and re-write the query
using S1_CCY. In this situation, federation is eliminated completely.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
4118
days inactive
4118
days old
0 comments
1 participants
participants (1)
-
Steven Hawkins (JIRA)