How to add a column to an eventhouse?

[Anonymous]

I am fairly new to Fabric and have a python script that sends data to an Eventstream, which in turn sends it into an Eventhouse.

Now I have changed the python script and am sending one column more than the Eventhouse stores, so I want to extend it by 1 column.

I have found this https://learn.microsoft.com/en-us/fabric/real-time-intelligence/table-update-policy but I do not know, how to use this code.

Can you tell me, how I would add a column named abc containing strings?

[datacoffee · Accepted solution]

Hi
You can alter a table directly and add a new column
The syntax is something like this

.alter table MyTable (ColumnX:string, ColumnY:int)

You can read the entire doc right here:
.alter table command - Kusto | Microsoft Learn

A different approach is to save the entire payload in one JSON column and then expand the coluimn in query time. The column datatype should then be dynamic datatype. You can easy unfold the JSON object using the "bag_unpack" command: 

https://learn.microsoft.com/en-us/kusto/query/bag-unpack-plugin?view=microsoft-fabric

[ramankr48]

Hi @datacoffee 

I have some similar kind of issue where I want to add a custom column like time with (HH:MM) , so I already have timestamp column, from this I want to derive this column HH:MM, but can't find any way to do it, if you know anything please guide on it.

also I am able to add individual objects of timestamp like year, month, week, day, hour, minute and so on, but in my case i want to combine both hour and minute and then feed the data to eventhouse.

so to add the individual object of timestamp as column I have used manage field transformation. refer the sc

[datacoffee]

Hello

I would instead look into a solution where you save the full timestamp in the database and then extract the HH:MM in a KQL query afterwards.

This solution is way easier to implement and the extract of the details in a KQL statement is very easy.

it also depends on your solution. 
If it is ok for me to ask, why you want to extract the info before landing in Eventhouse ?

[ramankr48]

yeah you are right, I can extract the desired data in the kql db using KQL,

and to answer your question I am feeding this live data to power bi dashboard, where i need this format of time (HH:MM)

[datacoffee]

Ok, gotcha

I would add that tranformation to the KQL query in your dashboard tiles. Super fast processing and visualisation even though you are adding transformations to the data at rendering time

I hope you succeed....

[ramankr48]

yeah that can be a possible solution, but not in my case

I wanted to perform this transformation in event stream canvas only because I am importing the data using direct query in power bi so there is no option to write KQL query there. 

May be now or in near future if fabric developers can add this feature to add the customise column while doing transformation in eventstream canvas, it will solve this issue.

[datacoffee]

In that case you can create either a function or a materialised view to manipulate the data.

the function is transforming data on an as-hoc basis and the view processes the data as it arrives and creates the transformed data in storage, ready to query.

with either way you can use the object as a source for both the dashboard and the power bi report.