Eventstream -> KQL. How to convert string to decimal number?

[fabric_user]

Hi,

I am using the Stock market sample data to ingest Eventstream data into a KQL database. In the event processor, I choose the data type of the columns. Most columns are initially detected as strings. However, I wish to create a real-time dashboard with a line chart, so I wish to show the numeric values on the Y axis. I select the Double type for all the decimal numbers. I don't get any errors when saving the eventstream, and data is being pushed into the KQL database table.

However, the Double columns get interpreted as Dynamic in the KQL database table, and I cannot use them on the Y axis of the real time dashboard.

What can I do to use the values, for example the lastSalePrice, on the Y axis in the real time dashboard?

Anyone knows?

Thanks!


Eventstream event processor:

KQL database table schema:

[datacoffee · Accepted solution]

In theory yes, but you won't feel it in any way at all.

This is not the usual SQL lookalike world with read problems and convertion issues 😉

[fabric_user]

Thanks,

Does it mean it's not possible to convert a string to decimal number, when ingesting from eventstream into KQL?

I'm no data type expert, but it seems Kusto supports both decimal and real (double):

https://learn.microsoft.com/en-us/kusto/query/scalar-data-types/?view=azure-data-explorer&preserve-v...

I'd love to have my string type decimal numbers converted to decimal number when writing from eventstream to KQL. So that I can easily use them in line charts.

It works fine for the integers, but not the decimal numbers, when I tried.

[datacoffee]

Hmm - not entirely. In Eventstream you can add a convertion in the "manage fields" option in the Event processor.

But - I would rather get the data to the database and then, at query time, convert the data as needed. 
the Kusto engine is powerfully enough to do that convertion in a flash without any overhead (as perhaps known in the sql world) 😊

[fabric_user]

I tried the "manage fields" option, however it seems to be equivalent (just more cumbersome) to the first method I tried.

No validations fail, and it transfers the data to the KQL database, but the data ends up as dynamic. 

I guess data type change on-read is the only way to do the string -> decimal number conversion.

Is it easy to do data type change on-read in a Real Time Dashboard? 

I'd like to create a line chart in a real time dashboard, with the line displaying the max avg BidPrice per 1

minute bin.

Will I need to write a manual KQL query to achieve it?

[datacoffee]

Yes you will have to write a KQL to do this. Like every other tile in RTD you need to write your own KQL behind it

Try something like toreal(<column>) and you should be good to go

[fabric_user]

Thanks.

Won't the read performance be better if it does the data type conversion on-write instead of on-read?

[datacoffee · Accepted solution]

In theory yes, but you won't feel it in any way at all.

This is not the usual SQL lookalike world with read problems and convertion issues 😉

[fabric_user]

Thanks, 

The following KQL code seems to work:

StockMarket
| summarize avg(todecimal((bidPrice))) by bin(['time'], 1m)
| where isnotempty( ['time'] )

[v-junyant-msft · Accepted solution]

Hi @fabric_user ,

I'm glad to see that you solved your problem.
Generally speaking, "manage fields" only changes the data type in the eventstream. When the data is passed to the KQL database, its data type is still affected by the limitations of the KQL database itself. So it is better to convert it in the KQL database using Kusto Query, which has more powerful functions.
Also, please don't forget to accept your reply as a solution so that other users can find the solution and learn faster.

Best Regards,
Dino Tao

[datacoffee]

Perhaps this link will help you. It looks like more or less the same question 😊

https://community.fabric.microsoft.com/t5/Eventhouse-and-KQL/How-to-convert-string-into-double-in-KQ...