I recently had an interesting situation at work. I use Kusto and write KQL often and we have a company-run Grafana instance with the Kusto datasource for users like me to visualization KQL queries in Grafana. It all works really well.
Recently, I had a situation where I had a
event_id field in a Kusto table. It was originally emitted to Kusto as an
uint64 (unsigned 64-bit integer). However, Kusto can only support signed 64-bit numbers through the
long datatype. So this meant that when I queried this Kusto table for this column, it would represent the unsigned 64-bit integer as a signed 64-bit integer.
Now I had the
event_id represented as a
string in another table, and I needed to join the two tables via
event_id. At this point, I came up with this crazy idea: What if I can turn the signed 64-bit integer representation of
event_id into a string representation of the unsigned version?
Hence began my journey.
I originally started off by trying all sorts of native Kusto functions to get this to work. Nothing ultimately worked, but I did find one thing: representing the
event_id column in its hex form was the best way to preserve the bit representation (Kusto also does not have a binary or byte array representation...).
So after trying a bunch of stuff, I ultimately was only able to get this:
EventsTable | extend event_id_hex = tohex(event_id)
From here, the only thing I was able to get working was to bust out the Python plugin which allows me to run arbitrary Python code in my Kusto query:
EventsTable | extend event_id_hex = tohex(event_id) | evaluate hint.distribution = per_node python( typeof(*, event_id_str:string), \``` result = df def convert_hex_to_str(hex_input): try: bytes_input = bytes.fromhex(hex_input.zfill(16)) except Exception: return "ERROR: " + hex_input return str(int.from_bytes(bytes_input, byteorder='big')) result["event_id_str"] = df.event_id_hex.apply(convert_hex_to_str) \``` ) | project event_id, event_id_str
This was functional!
However, it was pretty slow and I eventually decided on just adding a new column to the table that was the string representation of the
event_id so I didn't have to do this crazy KQL manipulation just so I can get the correct join key.
But it was a fun exercise. 👍🏻