brimdata
A "join" that outputs rows-as-is where the join key matches
Repro is with Zed commit 7ff37bb.
A beta tester from the community had been working on join variations and asked the following (edited slightly for clarity):
Is it possible to
jointwo or more pools and have the logs stay as they are instead of being combined together? Each of the log lines matching thejoinwould have its own row.
I hacked something together with what's in Zed today. Since an inner join without the "field-list" outputs only the rows from the left-hand data source that are a match on the join key, they could get what they want by doing the inner join in both directions and then combining the outputs. So, something like:
$ cat one.zson
{"foo": "bar", "source": "one"}
{"foo": "baz", "source": "one"}
$ cat two.zson
{"foo": "bar", "source": "two"}
{"foo": "xyz", "source": "two"}
$ cat inner-join-one.zed
from (
file one.zson => sort foo;
file two.zson => sort foo;
) | inner join on foo=foo
$ cat inner-join-two.zed
from (
file two.zson => sort foo;
file one.zson => sort foo;
) | inner join on foo=foo
$ zq -version
Version: v0.29.0-516-g7ff37bb9
$ zq -I inner-join-one.zed && zq -I inner-join-two.zed
{foo:"bar",source:"one"}
{foo:"bar",source:"two"}
We could potentially come up with a cleaner way to get this result in one shot.
@mccanne gave his thoughts on the topic:
This is not something SQL does because there’s not an option to mix different row types.
It would be really easy to add an option to join to do this. (instead of doing the double inner)
This is more of a
field-matchthan ajoin, i.e., from two query results, pass only the records that share a common field value with the other query
We need to spend some time thinking about what operators in other tools do this so we're not reinventing the wheel. @nwt noted that this would probably be performed as a subquery in a database.
Once we have a proper explode implementation, this could be done as follows:
from (
file one.zson => sort foo | lhs:=this;
file two.zson => sort foo | rhs:=this;
) | inner join on lhs.foo=rhs.foo rhs:=rhs
| explode [lhs,rhs]
Thanks to the new join implementation added in #5962, I think we can effectively do what @mccanne described in the last comment above.
Updating the simple example shown above for the current query language, we can now get to that intended output with a single query.
$ cat one.sup
{"foo": "bar", "source": "one"}
{"foo": "baz", "source": "one"}
$ cat two.sup
{"foo": "bar", "source": "two"}
{"foo": "xyz", "source": "two"}
$ super -version
Version: a09d427bd
$ super -c "
from one.sup
| join (
from two.sup
) on left.foo=right.foo
| yield left,right"
{foo:"bar",source:"one"}
{foo:"bar",source:"two"}
Now, thinking back to the user's original intent in 2021, I know their ultimate use case was to apply this to network security logs like Zeek. Those that know Zeek logs intimately are familiar with how the uid field links together events at different levels of the network stack that are all associated with the same flow, e.g., how multiple DNS requests may happen over the same UDP flow. Taking some of the Zeek logs from the old zed-sample-data repo, we can see it in action.
$ super -s -c "
from conn.log.gz
| join (
from dns.log.gz
) on left.uid=right.uid
| yield left,right"
{_path:"conn",ts:2018-03-24T17:20:23.509317Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:1.092ms,orig_bytes:86(uint64),resp_bytes:218(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:142(uint64),resp_pkts:2(uint64),resp_ip_bytes:274(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:20:23.509317Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:13959(uint64),rtt:1.088ms,query:"lh3.googleusercontent.com",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["googlehosted.l.googleusercontent.com","216.58.193.193"],TTLs:[12h11m55s,3m11s],rejected:false}
{_path:"conn",ts:2018-03-24T17:20:23.509317Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:1.092ms,orig_bytes:86(uint64),resp_bytes:218(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:142(uint64),resp_pkts:2(uint64),resp_ip_bytes:274(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:20:23.50932Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:13959(uint64),rtt:1.089ms,query:"lh3.googleusercontent.com",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["googlehosted.l.googleusercontent.com","216.58.193.193"],TTLs:[12h11m55s,3m11s],rejected:false}
{_path:"conn",ts:2018-03-24T17:23:10.750032Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:718us,orig_bytes:72(uint64),resp_bytes:372(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:128(uint64),resp_pkts:2(uint64),resp_ip_bytes:428(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:23:10.750032Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19907(uint64),rtt:714us,query:"pkg.cdn.trueos.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:false,Z:0(uint64),answers:["pkg.pcbsd.scaleengine.net","pcbsd-pkg.secdn.net","94.100.23.182"],TTLs:[3m44s,3m,1m],rejected:false}
{_path:"conn",ts:2018-03-24T17:23:10.750032Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:718us,orig_bytes:72(uint64),resp_bytes:372(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:128(uint64),resp_pkts:2(uint64),resp_ip_bytes:428(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:23:10.750035Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19907(uint64),rtt:715us,query:"pkg.cdn.trueos.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:false,Z:0(uint64),answers:["pkg.pcbsd.scaleengine.net","pcbsd-pkg.secdn.net","94.100.23.182"],TTLs:[3m44s,3m,1m],rejected:false}
{_path:"conn",ts:2018-03-24T17:35:40.52033Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:1.154ms,orig_bytes:128(uint64),resp_bytes:344(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:4(uint64),orig_ip_bytes:240(uint64),resp_pkts:4(uint64),resp_ip_bytes:456(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:35:40.52033Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19417(uint64),rtt:900us,query:"ise.wrccdc.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["ise.wrccdc.cpp.edu","134.71.3.16"],TTLs:[16m50s,11h16m50s],rejected:false}
...
This reveals something interesting. Once again, knowing Zeek intimately, we're accustomed to seeing only only one conn record per uid value, and this output shows two for that first set of four log lines that all share uid of C00JuX1f0KEJmwA2g1. And yet looking at the original data, we see:
$ super -c "uid=='C00JuX1f0KEJmwA2g1' | count()" conn.log.gz
1(uint64)
$ super -c "uid=='C00JuX1f0KEJmwA2g1' | count()" dns.log.gz
2(uint64)
This can can be explained by the nature of the join, which is easier to see if we go back to the simple example again. Let's change our second input file to have two entries on the join key, just like we had two DNS records with the same uid, then drop the yield in our query.
$ cat two-plus.sup
{"foo": "bar", "source": "two"}
{"foo": "xyz", "source": "two"}
{"foo": "bar", "source": "two", "bonus": "line"}
$ super -c "from one.sup
| join (
from 'two-plus.sup'
) on left.foo=right.foo"
{left:{foo:"bar",source:"one"},right:{foo:"bar",source:"two"}}
{left:{foo:"bar",source:"one"},right:{foo:"bar",source:"two",bonus:"line"}}
So, that makes sense: We had more than one successful match on the join key, so there's going to be more than one appearance of the left-hand record.
Assuming we don't want the duplication in our result, it's thankfully easy to get rid of it, though it helps to have knowledge about the nature of the data. Going back to the Zeek logs, we can do:
$ super -s -c "
from conn.log.gz
| join (
from dns.log.gz
) on left.uid=right.uid
| yield left,right
| sort uid,_path
| uniq"
{_path:"conn",ts:2018-03-24T17:20:23.509317Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:1.092ms,orig_bytes:86(uint64),resp_bytes:218(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:142(uint64),resp_pkts:2(uint64),resp_ip_bytes:274(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:20:23.509317Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:13959(uint64),rtt:1.088ms,query:"lh3.googleusercontent.com",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["googlehosted.l.googleusercontent.com","216.58.193.193"],TTLs:[12h11m55s,3m11s],rejected:false}
{_path:"dns",ts:2018-03-24T17:20:23.50932Z,uid:"C00JuX1f0KEJmwA2g1",id:{orig_h:10.47.2.100,orig_p:50261(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:13959(uint64),rtt:1.089ms,query:"lh3.googleusercontent.com",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["googlehosted.l.googleusercontent.com","216.58.193.193"],TTLs:[12h11m55s,3m11s],rejected:false}
{_path:"conn",ts:2018-03-24T17:23:10.750032Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:718us,orig_bytes:72(uint64),resp_bytes:372(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:2(uint64),orig_ip_bytes:128(uint64),resp_pkts:2(uint64),resp_ip_bytes:428(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:23:10.750032Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19907(uint64),rtt:714us,query:"pkg.cdn.trueos.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:false,Z:0(uint64),answers:["pkg.pcbsd.scaleengine.net","pcbsd-pkg.secdn.net","94.100.23.182"],TTLs:[3m44s,3m,1m],rejected:false}
{_path:"dns",ts:2018-03-24T17:23:10.750035Z,uid:"C00xfUvyqk1dKtt7e",id:{orig_h:10.47.3.156,orig_p:17118(port=uint16),resp_h:10.10.3.1,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19907(uint64),rtt:715us,query:"pkg.cdn.trueos.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:false,Z:0(uint64),answers:["pkg.pcbsd.scaleengine.net","pcbsd-pkg.secdn.net","94.100.23.182"],TTLs:[3m44s,3m,1m],rejected:false}
{_path:"conn",ts:2018-03-24T17:35:40.52033Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),service:"dns",duration:1.154ms,orig_bytes:128(uint64),resp_bytes:344(uint64),conn_state:"SF",local_orig:true,local_resp:true,missed_bytes:0(uint64),history:"Dd",orig_pkts:4(uint64),orig_ip_bytes:240(uint64),resp_pkts:4(uint64),resp_ip_bytes:456(uint64),tunnel_parents:null(|[string]|)}
{_path:"dns",ts:2018-03-24T17:35:40.52033Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19417(uint64),rtt:900us,query:"ise.wrccdc.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["ise.wrccdc.cpp.edu","134.71.3.16"],TTLs:[16m50s,11h16m50s],rejected:false}
{_path:"dns",ts:2018-03-24T17:35:40.520334Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:19417(uint64),rtt:900us,query:"ise.wrccdc.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:1(uint64),qtype_name:"A",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["ise.wrccdc.cpp.edu","134.71.3.16"],TTLs:[16m50s,11h16m50s],rejected:false}
{_path:"dns",ts:2018-03-24T17:35:40.520336Z,uid:"C011jy2nOqiI69Pbdg",id:{orig_h:10.47.4.100,orig_p:39442(port=uint16),resp_h:10.0.0.100,resp_p:53(port)},proto:"udp"(=zenum),trans_id:40663(uint64),rtt:1.143ms,query:"ise.wrccdc.org",qclass:1(uint64),qclass_name:"C_INTERNET",qtype:28(uint64),qtype_name:"AAAA",rcode:0(uint64),rcode_name:"NOERROR",AA:false,TC:false,RD:true,RA:true,Z:0(uint64),answers:["ise.wrccdc.cpp.edu","2620:df:8000:1601:0:1:3:16"],TTLs:[16m50s,10h16m49s],rejected:false}
...
i.e., the sort makes sure the duplicates are placed right next to each other, and the uniq weeds out the duplicates.
Of course, this precise massaging may not apply to all log types, but it does show some the power of the new join in terms of being able to mix the different record types.
I'll hold this one open for some peer review to make sure I'm not missing any other subtleties, but if it passes muster I'll close it out.