LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: UisceBeatha

Hi,

When joining two sets of data using a left inner join I receive the error "Maximum buffer size of 536870912 bytes exceeded".

I've read the below threads on this forum regarding this issue:
- http://community.lavastorm.com/archi...php/t-503.html
- http://community.lavastorm.com/threa...y-open-files#6

and these suggests the error appears when the keys used for the matching are not unique and therefore create a cartesian product. However in this instance I don't believe this is the case as I'm pretty certain the right or bottom set of data is unique.

The left/top set of data contains 168,339,891 records and 21 fields for each record.
The right/bottom set contains 224 records and 7 fields for each record.
There are three match keys used in the join plus there's also a where clause comparing integers.

I've performed a similar left inner join on the top dataset earlier in the same graph (though there were only 14 fields in the dataset at this stage) and joined it with 350,000 records. There was no where clause in this join and this join completed successfully. I've also inserted a head node to cut my top dataset to 20,000 records, joined this to the 224 records using the same join node and this was successful and only outputted 20,000 records.

My PC is running BRE v4.5.2.0 Build 334, has Windows 7 32bit, Core i5 3570 3.4 GHz processor and 4 GB RAM.

What could be causing this error? Is it definitely because the join is creating a cartesian product? Or is it just because of the amount of data within the join?

Many thanks for your help

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The only case where I've seen this on a join was where a cartesian join was being created, but for only 224 records on the right, that seems incorrect.

If you would.. Just to diagnose the condition, please take the right hand input and run that data thru a "Duplicate Detection" node with the same three match keys used in the join. This would tell you if any of the data IS duplicate.

Since you happen to be using Left-Inner, this also is a specific case where you could use the LOOKUP node instead.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: UisceBeatha

Originally posted by: stonysmith
					

The only case where I've seen this on a join was where a cartesian join was being created, but for only 224 records on the right, that seems incorrect.


If you would.. Just to diagnose the condition, please take the right hand input and run that data thru a "Duplicate Detection" node with the same three match keys used in the join.   This would tell you if any of the data IS duplicate.


Since you happen to be using Left-Inner, this also is a specific case where you could use the LOOKUP node instead.
			

Hi,

Many thanks for your fast response.

Running the 224 records through the "Duplicate Detection" node on the three match keys alone does show duplicates however when I also take into account the integer fields I have within the where clause in the join there are no duplicates.

If I use a lookup instead of a left inner is the only difference that if there were any duplicates in the bottom dataset the lookup would only match the first of these?

Thanks

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The fact that there are duplicate records on the right creates a cartesian situation, and the node is trying to read/hold all of the lefthand side (pin 1) in memory before it moves onto the 2nd duplicate record - that's why it's aborting.

The other trouble here is that the "match" is performed (consuming memory) before the "where" clause is processed - the where clause is not considered during the matching.

Yes, the lookup will only match to the first of the righthand records.

There are several possible solutions, but I might need to see more of what is going on before I could suggest the best route.

One question: are you aware of the HashSplit node? That might be a simple answer to this situation.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: UisceBeatha

Originally posted by: stonysmith
					

The fact that there are duplicate records on the right creates a cartesian situation, and the node is trying to read/hold all of the lefthand side (pin 1) in memory before it moves onto the 2nd duplicate record - that's why it's aborting.


The other trouble here is that the "match" is performed (consuming memory) before the "where" clause is processed - the where clause is not considered during the matching.


Yes, the lookup will only match to the first of the righthand records.


There are several possible solutions, but I might need to see more of what is going on before I could suggest the best route.


One question: are you aware of the HashSplit node?   That might be a simple answer to this situation.
			

Ah! Didn't realise that about the where clause - that would explain the error then!

I've never used the HashSplit node before. I've looked through the examples in the training files http://community.lavastorm.com/threa...ing-Session-14 but I'm still not sure exactly how the node is splitting the data. My initial thought would be if you choose to split by the field "Month" and you have 4 different months in your source data then the Hash Split node would split the data into 4 outputs - one for each month but this doesn't seem to be the case?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: UisceBeatha

Also I've just expanded the bottom dataset by month to negate the need for the where clause in the join (the where clause was doing a comparison between months) and I've checked there's no duplicates in this bottom dataset using the Duplicate Detection on the 4 match key fields I'm now using in the join. I've rerun the join again and get the same "maximum buffer..." error so it looks like this error doesn't just appear with cartesian joins. My concern is that I still have quite a lot of manipulation to do on these 168 million records including further joins and am wondering if I'm nearing the limit of what my PC/Lavastorm is able to process?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pdespot

While we can increase the buffer size to resolve this issue, the best thing to try first is the hash split. I've included an example graph that uses this technique. The thing to remember is that you must split on the same fields that you are going to join. The reason is because the hash split does just randomly split up records. It looks at the fields you specify and ensures any record with that value will be output to the same pin on any hash split node.

For example, in the attached example graph, I'm matching on the columns AccountID, Date and SomeString. So let's take the first row of the hash's out1 pin. The value for that row is: AccountID=684875, Date=9/18/2009, SomeString=Bmsncpiaak. For the join to work, that same row MUST be present in the other hash split's out1 pin. Otherwise, we'd never match it even though it's present. When we look at the other hash split's out1 pin, we can see that row there. You can repeat this process with any pin to verify. That's why the join's work.

I hope this explanation helped clarify the purpose and function of the hash split node.

Regards.
Attachments:
hash_join_example.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

One small thing to help you stay out of trouble... the HashSplit node requires a prime number of output pins.. 2,3,5,7,11 will work, but not 8, 12, 33, etc. (and both HashSplits in a pair need to have the same number of pins)