LAE

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

Originally posted by: ejones

I have two rather large chunks of data I am joining with an inner join node. One stream is 1.8 million records, and the other stream is 1.5 million records.

The challenge is that each record in the second stream represents a range of records. The range is defined by two fields ESNMIN and ESNMAX. We can assume the ranges are non-overlapping. What we need is the inner join between the two streams where the ESN field in the first stream is between ESNMIN and ESNMAX inclusive.

It turns out that if you blow out that second stream into all of the records it represents, you will have a file that is 6.2 billion records.

So the question is: Is there a way to do the join without creating all 6.2 billion records as an input to the join?

Side question: I'd like to verify the assumption that the ranges are non-overlapping, how would we go about doing this without creating the 6.2 billion record file?

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

Originally posted by: mmarinelli

The LineMatcher node (located in the basic-usage library) is designed for this sort of ranged comparison. It will take a set of data sorted by a group of columns and output matches where these columns are identical and another ranged column falls within a specified interval. Unfortunately, this node was always used internally only, so there isn't a lot of documentation on how to use it's sort of nonstandard use of parameter fields. Here's some code for a simple example which should get you started:
-
node:LineMatcher
bretype:basic-usage::LineMatcher
editor:sortkey=48066ddd2761412f
input:@40fd3328763c6d78/=sort_on_comparison_columns.40fd2c746a2a3b47
output:@40fd3328132a75d7/=
output:@40fd332830ba7cc0/=
output:@40fd3328724c55ba/=
output:@40fd33282c6c7dd5/=
prop:ColumnsThatCannotMatch=(SOURCE)
prop:LineIdColumn=LINE_ID
prop:MaxPrimaryRange=100
prop:MaxRanges=()
prop:MustMatchColumns=(COL1 COL2)
prop:PrimaryRangedColumn=COL3
prop:RangedColumns=()
editor:XY=660,590
end:LineMatcher

node:Cat
bretype:core::Cat
editor:sortkey=48066ddd3ca10b30
input:@40fd2c7476b11c42/=add_SOURCE_ID_fields_2.40fd2c744c862db0
input:48066ddd600d32cd/out1=add_SOURCE_ID_fields.40fd2c744c862db0
output:@40fd2c74676e03c3/=
editor:XY=420,590
end:Cat

node:add_LINE_ID_field
bretype:core::Agg
editor:Label=add LINE_ID field
editor:sortkey=48066ddd4e1b1eb0
input:@40fd2c7427456e5b/=Cat.40fd2c74676e03c3
output:@40fd2c744c862db0/=
prop:GroupBy=<<EOX
0
EOX
prop:Script=<<EOX
LINE_ID = count()

emit *
emit LINE_ID
EOX
editor:XY=500,590
end:add_LINE_ID_field

node:sort_on_comparison_columns
bretype:core::Sort
editor:Label=sort on comparison columns
editor:sortkey=48066ddd62f72477
input:@40fd2c743ebf4304/=add_LINE_ID_field.40fd2c744c862db0
output:@40fd2c746a2a3b47/=
prop:CompareOrderExpr=<<EOX
list(COL1, COL2, COL3)
EOX
editor:XY=580,590
end:sort_on_comparison_columns

node:add_SOURCE_ID_fields
bretype:core::Agg
editor:Label=add SOURCE, ID fields
editor:sortkey=48066ddd4db66df3
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
prop:GroupBy=<<EOX
0
EOX
prop:Script=<<EOX
SOURCE_ID = count()

emit *
emit SOURCE_ID
emit 2 as SOURCE

EOX
editor:XY=340,670
end:add_SOURCE_ID_fields

node:add_SOURCE_ID_fields_2
bretype:core::Agg
editor:Label=add SOURCE, ID fields
editor:sortkey=48066ddd7b03086f
input:@40fd2c7427456e5b/=
output:@40fd2c744c862db0/=
prop:GroupBy=<<EOX
0
EOX
prop:Script=<<EOX
SOURCE_ID = count()

emit *
emit SOURCE_ID
emit 1 as SOURCE

EOX
editor:XY=340,590
end:add_SOURCE_ID_fields_2
-

This should take two sources with fields COL1, COL2, COL3 and find matches where COL1 and COL2 are identical and COL3 is within a range of 100. Try this out and follow up with your results.

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

Originally posted by: ejones

I just realized that this LineMatcher node will NOT work. I need data from lines that match in the join to appear in one record. The outputs on this record are:
1 - uniquely matched lines
2 - multiply matched lines
3 - unmatched lines
4 - matched ids

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

Originally posted by: ejones

I was able to do this operation by using several nodes.

The second input which is the ranges went to two sorts, one sorted on ESNMIN and the other sorted on ESNMAX. The first input was, of course, sorted on ESN. Then I joined the sorted first input to the ESNMIN sorted input. Then I joined the sorted first iput to the ESNMAX sorted input. For this second join I had to remove duplication with the first join by removing any matches where the record had ESNMIN == ESNMAX since they would be showing up in both. So this takes care of all records that match either the start or an end of a range but not the ones in the middle.

To take care of the middle I concatenated the first input with the second input keeping all columns. Then sorted the result on Nvl(ESN,ESNMIN). Since I wasn't worried about any matches where ESN==ESNMIN anymore I didn't care what order they ended up in. Next I went through the data with a Filter node taking advantage of the fact that you can actually keep data from one record to the next if done carefully. (see answers to my other posts) I could keep the ESNMIN and ESNMAX values and only output records where I had a non-null ESN value that was between, non-inclusively, the ESNMIN and ESNMAX values of the saved data.

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

Originally posted by: ejones

I was able to do this operation by using several nodes.

The second input which is the ranges went to two sorts, one sorted on ESNMIN and the other sorted on ESNMAX. The first input was, of course, sorted on ESN. Then I joined the sorted first input to the ESNMIN sorted input. Then I joined the sorted first iput to the ESNMAX sorted input. For this second join I had to remove duplication with the first join by removing any matches where the record had ESNMIN == ESNMAX since they would be showing up in both. So this takes care of all records that match either the start or an end of a range but not the ones in the middle.

To take care of the middle I concatenated the first input with the second input keeping all columns. Then sorted the result on Nvl(ESN,ESNMIN). Since I wasn't worried about any matches where ESN==ESNMIN anymore I didn't care what order they ended up in. Next I went through the data with a Filter node taking advantage of the fact that you can actually keep data from one record to the next if done carefully. (see answers to my other posts) I could keep the ESNMIN and ESNMAX values and only output records where I had a non-null ESN value that was between, non-inclusively, the ESNMIN and ESNMAX values of the saved data.

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

Originally posted by: rboccuzzi

I think you can use the plain join node in the core library, under join. It has a parameter in the param 2 tab called joinexpr, which is an expression that needs to return 0, -1, or 1, which indicates if the two records it is looking for match, or is a left or right orphan. I have attached a graph that should show you how to use this. This isn't quite the way join was designed to work, so if sets overlap a lot, the output might not be as you desire, so play with the sample ranges to get comfortable with it's behavior. And remember, you can still get carteasean products of results, so if a number matches more then one range, you might get the result in both. If the sets are disjoint, this makes much more sense.

Oh, there is currently a bug in core, it erroneously is forceing you to have a value in the join node's LeftInputKey and RightInputKey. To fix this, you need to tweak your installed core library, or re-declare both parameters and turn off the validators.

Rich

Attachments:
rangejoin.brg