How to join/cogroup by multiple fields in SAQL?

Question

I am new to SAQL and am trying to join two datasets (let's call them q and r) using cogroup. I have 4 columns in each table (let's call these A, B, C and D). Columns A - C are the same in both datasets but column D is different, so I want to use an inner join against columns A - C to produce a result that will have the columns A, B, C, D, E (renamed from D in the 2nd dataset). However, when I try to do this I am getting a number of errors.

So far I have tried the following:

q = cogroup q by 'A', 'B', 'C', r by 'A', 'B', 'C'; q = cogroup (q by 'A', r by 'A') and cogroup (q.'B', r.'B') and cogroup (q.'C', r.'C'); q = cogroup (q by 'A', r by 'A'), cogroup (q.'B', r.'B'), cogroup (q.'C', r.'C'); q = cogroup q by 'A', r by 'A' and q by 'B', r by 'B' and q by 'C', r by 'C';

None of these give me the desired a result and I get errors (not all the same errors) for each of these.

In each instance this has been followed by: q = foreach q generate q.'A' as 'A', q.'B' as 'B', q.'C' as 'C', sum(q.'D') as 'D', sum(r.'D') as 'E';

I have struggled to find any examples online of where multiple fields are used for the join, can anyone provide any solution to this please?

Answer 1

You can cogroup by multiple fields using below syntax. Quoting your example above. Let say u want to cogroup 'q' datastream and 'r' datastream on these 3 fields 'A', 'B' and 'C'.

q = cogroup q by ('A', 'B', 'C'), r by ('A', 'B', 'C');