Pre-Join Projection with left-outer-join behaviour

Moderator: NorbertKrupa

Post Reply
SreeRaman
Newbie
Newbie
Posts: 2
Joined: Tue Feb 11, 2014 12:33 pm

Pre-Join Projection with left-outer-join behaviour

Post by SreeRaman » Tue Feb 11, 2014 6:09 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1806
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Pre-Join Projection with left-outer-join behaviour

Post by JimKnicely » Tue Feb 11, 2014 7:48 pm

We cannot create pre-join projections that contain LEFT joins; Only INNER joins are supported.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Mrao
Newbie
Newbie
Posts: 22
Joined: Mon Feb 03, 2014 2:07 pm

Re: Pre-Join Projection with left-outer-join behaviour

Post by Mrao » Wed Feb 12, 2014 3:05 am

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.

SreeRaman
Newbie
Newbie
Posts: 2
Joined: Tue Feb 11, 2014 12:33 pm

Re: Pre-Join Projection with left-outer-join behaviour

Post by SreeRaman » Wed Feb 12, 2014 8:11 am

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

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Pre-Join Projection with left-outer-join behaviour

Post by id10t » Wed Feb 12, 2014 10:47 am

Hi!

[DELETED]
Last edited by id10t on Fri May 08, 2015 4:44 pm, edited 1 time in total.

Mrao
Newbie
Newbie
Posts: 22
Joined: Mon Feb 03, 2014 2:07 pm

Re: Pre-Join Projection with left-outer-join behaviour

Post by Mrao » Wed Feb 12, 2014 3:28 pm

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

Post Reply

Return to “New to Vertica SQL”