Rename all column names by adding a string in KQL/Kusto/Data Explorer

user_17322246 0
Viewed 1285

I am joining 2 tables which both have hundreds of similarly named columns. I would like to change all of the column names in each table to include the table name. To keep the query simple, I do not want to call out each column name explicitly. Is there a way to append the table name to all column names without explicitly calling out each column?

For example:

let T1 = datatable (Key:string , Col2:string , Col3:string )[
"1", "b", "c",
"2", "e", "f",
"3", "h", "i"];
let T2 = datatable (Key:string , Col2:string , Col3:string )[
"1", "B", "C",
"2", "E", "F",
"4", "H", "I"];
T1 | join T2 on Key

Result:

Key Col2 Col3 Key1 Col21 Col31
1   b    c    1    B     C
2   e    f    2    E     F

Desired Result:

T1.Key T1.Col2 T1.Col3 T2.Key T2.Col2 T2.Col3
1      b       c       1      B       C
2      e       f       2      E       F
kusto-explorer kql azure-data-explorer
1 Answers

If the order of columns is not important to you, then this would be one way to do it:

let T1 = datatable (Key:string , Col2:string , Col3:string )
[
  "1", "b", "c",
  "2", "e", "f",
  "3", "h", "i"
] 
| project PackedRecord = todynamic(replace_regex(tostring(pack_all()), '"([a-zA-Z0-9_]*)":"', @'"T1_\1":"'))
| evaluate bag_unpack(PackedRecord);
let T2 = datatable (Key:string , Col2:string , Col3:string )
[
  "1", "B", "C",
  "2", "E", "F",
  "4", "H", "I"
] 
| project PackedRecord = todynamic(replace_regex(tostring(pack_all()), '"([a-zA-Z0-9_]*)":"', @'"T2_\1":"'))
| evaluate bag_unpack(PackedRecord);
let JoinTable = T1 | join kind=inner T2 on $left.T1_Key == $right.T2_Key;
JoinTable

Result:

T1_Col2 T1_Col3 T1_Key T2_Col2 T2_Col3 T2_Key
b c 1 B C 1
e f 2 E F 2

If you want to reorder the columns, you can use project-reorder.

user_65147620
Related