Hi All,
I am new to vertica and I want to implement below case scenario with Pre-Join projection. Please suggest how can i achieve this.
I have two tables like FactA(CustomerID (PK),Units) &FactB(CustomerID (FK),Sales) and the data is avail like below:
FactA FactB
CustomeID Units CustomerID Sales
------------ ------ -------------- -------
1 10 1 100
2 20 2 200
3 30
i want to create a pre-join projection which can return below result set:
CustomerID Units Sales
--------------- ------ -------
1 10 100
2 20 200
3 30 0 or null
Please suggest
Regards
Sree
Pre-Join Projection with left-outer-join behaviour
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Pre-Join Projection with left-outer-join behaviour
We cannot create pre-join projections that contain LEFT joins; Only INNER joins are supported.
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Pre-Join Projection with left-outer-join behaviour
why do you want to go for a prejoin? Did you look at these options?
1. Consolidate both the facts, if you do not need to maintain them separately.
2. Create a view with left outer join. This is a dynamic approach and saves desk space.
3. Create a new table to hold left outer join output. This gives better performance but have to be maintained manually.
1. Consolidate both the facts, if you do not need to maintain them separately.
2. Create a view with left outer join. This is a dynamic approach and saves desk space.
3. Create a new table to hold left outer join output. This gives better performance but have to be maintained manually.
Re: Pre-Join Projection with left-outer-join behaviour
Hi Mrao,
Thank you for the update.
Fact A table has updates on almost daily basis and FactB table has weekly updates. FactB table takes around 4 hours to get updated.
As I wanted the consolidated output with good performance, I thought pre-join would be the good option. But it does not work to fetch left-join output.
Please suggest me the best work around to achieve this with good performance.
Best Regards
Sree
Thank you for the update.
Fact A table has updates on almost daily basis and FactB table has weekly updates. FactB table takes around 4 hours to get updated.
As I wanted the consolidated output with good performance, I thought pre-join would be the good option. But it does not work to fetch left-join output.
Please suggest me the best work around to achieve this with good performance.
Best Regards
Sree
Re: Pre-Join Projection with left-outer-join behaviour
Hi!
[DELETED]
[DELETED]
Last edited by id10t on Fri May 08, 2015 4:44 pm, edited 1 time in total.
Re: Pre-Join Projection with left-outer-join behaviour
4 hrs looks really long time to me. I will first try to fix the fact tables and then will go to the next.
I will consider sKwa ideas. I may be able to suggest a solution if I have the following info.
1. Fact tables volumes
2. High level logic from source to Fact
3. Any partitions on the fact tables
4. what steps in the process are taking time
5. How many changes are there per run
I will consider sKwa ideas. I may be able to suggest a solution if I have the following info.
1. Fact tables volumes
2. High level logic from source to Fact
3. Any partitions on the fact tables
4. what steps in the process are taking time
5. How many changes are there per run