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?
1 Answers
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
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 | xxx@yyy.com | 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 | xxx@yyy.com | 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 | xxx@yyy.com | 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 |
Related