CODEWITHSUNDEEP
redhat-datavirt
Norbert Funke
Norbert Funke

Reputation: 81

How to join on JDV and not to push down join to data source

Problem: I am trying to create a wide view (~5000 columns), which works across data sources fine JDV. However, when I try to create the view with a join on 2+ table from data source, the optimizer pushes down the join to the source. The current source cannot handle more then 1600 columns.

Example: When trying to join Member_DX1 and Member_DX2 at client, JDV pushes the enter code herecombined join to postgres as one getting the too max column error.

/* TABLE 1 */

CREATE VIEW Member_DX1 (
    MEMB_BID Integer
     , DX130402000000 Integer
     , DX180608000000 Integer
     , DX20401070000 Integer
.... /* 1000 more */
as
SELECT dx.memb_bid
, case dx.EPI_1_DX4 when 130402000000 then 1 else 0 END as  DX130402000000                                              
, case dx.EPI_1_DX4 when 180608000000 then 1 else 0 END as  DX180608000000                                              
, case dx.EPI_1_DX4 when 20401070000 then 1 else 0 END as  DX20401070000
...
FROM BDR.ENH_EPI_DETAIL dx 

/* TABLE 2 */

CREATE VIEW Member_DX2 (
    MEMB_BID Integer
     , DX200102010000 Integer
     , DX90125000000 Integer
     , DX160603070000 Integer
... /* 1000 more ...
SELECT dx.memb_bid  /* FOREIGN TABLE */
, case dx.EPI_1_DX4 when 200102010000 then 1 else 0 END as  DX200102010000                                              
, case dx.EPI_1_DX4 when 90125000000 then 1 else 0 END as  DX90125000000                                                
, case dx.EPI_1_DX4 when 160603070000 then 1 else 0 END as  DX160603070000      
...`enter code here`
FROM BDR.ENH_EPI_DETAIL dx

then my query in (e.g. dBeaver) looks like this:

SELECT * from Member_DX1 dx1
join Member_DX2 dx2 
on dx1.MEMB_BID = dx2.MEMB_BID

Upvotes: 1

Views: 96

Answers (1)

Steven Hawkins
Steven Hawkins

Reputation: 588

The current source cannot handle more then 1600 columns.

Can you capture that as an issue for Teiid? Then we can take appropriate compensating action automatically.

then my query in (e.g. dBeaver) looks like this:

If you see this issue affecting all of your user queries, then you can turn join support off at the translator level via translator overrides - SupportsInnerJoin, SupportsOuterJoins, etc.. If there is a pk/fk relationship and you can modify the metadata, you can add an extension property allow-join as false to prevent the pushdown - see Join Compensation http://teiid.github.io/teiid-documents/master/content/reference/Federated_Optimizations.html

Upvotes: 1

Related Questions