CODEWITHSUNDEEP
databaseazure-data-explorerkql
Gagan Walia
Gagan Walia

Reputation: 181

Iterating over each row and modifying columns in kusto

I have a column in kusto table, which have empty entries. I want to assign numbers to empty cells in a particular column as 1,2,3,4.... Is it possible to iterate each row and apply this logic in kusto query language?

Upvotes: 2

Views: 835

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

  1. Kusto/ADX is append only, which means there are no updates.

    You could create a new table, based on your current table, with the added column, and then rename the old table to something else (you could drop it later on, once you verified that the new table is fine) and the new one to the old name.

    See .set

  2. See row_number() for generating sequential numbers.

Here is a quick demo

Setting the ground
.create table my_table(txt:string, timestamp:datetime)


.ingest inline into table my_table <|
tic, 2022-06-15
tac, 2022-06-16
toe, 2022-06-17
txt timestamp
tic 2022-06-15T00:00:00Z
tac 2022-06-16T00:00:00Z
toe 2022-06-17T00:00:00Z
Actual work starts here
.set async my_table_with_added_column <|
my_table
| serialize
| extend rownum = row_number()

If it is a relatively small table, you could skip async making it an online operation.

OperationId
d783359c-afb6-43e4-85df-039ff6721177 
.show operations 
| where OperationId == "d783359c-afb6-43e4-85df-039ff6721177" 
| order by LastUpdatedOn desc
OperationId Operation NodeId StartedOn LastUpdatedOn Duration State Status RootActivityId ShouldRetry Database Principal User AdminEpochStartTime
d783359c-afb6-43e4-85df-039ff6721177 TableSet KENGINE000000 2022-06-17T12:40:03.6839539Z 2022-06-17T12:40:03.8714357Z 00:00:00.1874818 Completed d65b3cdf-d969-4ad4-892f-8302ad7b8ec0 0 mydb aaduser=fc0aaa54-0000-4075-8ab9-a00dc4c9e6c2;72f988bf-1111-41af-91ab-2d7cd011db47 [email protected] 2022-06-13T09:21:21.4741695Z
d783359c-afb6-43e4-85df-039ff6721177 TableSet KENGINE000000 2022-06-17T12:40:03.6839539Z 2022-06-17T12:40:03.6839549Z 00:00:00 InProgress d65b3cdf-d969-4ad4-892f-8302ad7b8ec0 0 mydb aaduser=fc0aaa54-0000-4075-8ab9-a00dc4c9e6c2;72f988bf-1111-41af-91ab-2d7cd011db47 [email protected] 2022-06-13T09:21:21.4741695Z
d783359c-afb6-43e4-85df-039ff6721177 TableSet 2022-06-17T12:40:03.6839539Z 2022-06-17T12:40:03.6839539Z 00:00:00 InProgress d65b3cdf-d969-4ad4-892f-8302ad7b8ec0 0 mydb aaduser=fc0aaa54-0000-4075-8ab9-a00dc4c9e6c2;72f988bf-1111-41af-91ab-2d7cd011db47 [email protected] 2022-06-13T09:21:21.4741695Z 
my_table_with_added_column
txt timestamp rownum
tic 2022-06-15T00:00:00Z 1
tac 2022-06-16T00:00:00Z 2
toe 2022-06-17T00:00:00Z 3 
.rename table my_table to my_table_old_version

.rename table my_table_with_added_column to my_table

my_table
txt timestamp rownum
tic 2022-06-15T00:00:00Z 1
tac 2022-06-16T00:00:00Z 2
toe 2022-06-17T00:00:00Z 3

Upvotes: 2

Related Questions