CODEWITHSUNDEEP
azure-data-explorerkql
anna
anna

Reputation: 445

How to split a single string into multiple columns in Kusto?

I am getting data as a single string. For example, my one record looks like - 2020/01/01 "Anna Thomas" 21.(Note: fields are space separated). I want them in a Kusto table with schema Date, Name, and Age. I read about first inserting them to a source table and then ingesting into Target Table using the update policy. But how will I parse this type of records (thought of using splitting the record based on single space but then the name also gets splitted)?

Date Name age
2020/01/01 Anna Thomas 21

Upvotes: 1

Views: 4930

Answers (1)

Slavik N
Slavik N

Reputation: 5308

You should use the parse operator:

let MyTable = datatable(str:string) [
    '2020-01-01 "Anna Thomas" 21',
    '2020-01-05 "Slavik Kusto" 32'
];
MyTable
| parse str with Timestamp:datetime ' "' Name:string '" ' Age:long
| project-away str

Output:

Timestamp Name Age
2020-01-01 00:00:00.0000000 Anna Thomas 21
2020-01-05 00:00:00.0000000 Slavik Kusto 32

Note: I changed the / to - in your timestamps, in order for the string to comply with the ISO 8601 datetime format that is required by Kusto. If you have no control over the input data, then you can do the replace as part of the Kusto query, like this:

let MyTable = datatable(str:string) [
    '2020/01/01 "Anna Thomas" 21',
    '2020/01/05 "Slavik Kusto" 32'
];
MyTable
| parse str with Timestamp:string ' "' Name:string '" ' Age:long
| extend Timestamp = todatetime(replace("/", "-", Timestamp))
| project-away str

Output:

Timestamp Name Age
2020-01-01 00:00:00.0000000 Anna Thomas 21
2020-01-05 00:00:00.0000000 Slavik Kusto 32

Upvotes: 4

Related Questions