Ingestion with kusto - speed layer doesn't match

[DennesTorres]

Hi,

I'm preparing a demo of the real time features in Fabric and I faced a challenge.

Let's consider I'm building a lambda architecture and the Kusto Database will hold the Speed/Hot layer.

As a demo, I'm using the Yellow Taxi sample. On the speed layer, I don't need every ride, only a summary of the rides by location. So, I use Kusto event processing to group and aggregate the data, this would be expected.

Here is the rub: The kusto event processing doesn't identify the field types correctly and all numeric fields are identified as string. I could convert the types, however the Manage Fields activity has no function to convert data types. It has functions to transform strings, functions for numeric calculations, but not for type conversions. In this way, the desired result can't be achieved directly from this point, only because a data type mismatch.

On the other hand, if I use direct ingestion, I don't need to convert the column types because the correct types are already identified. However, I can't group and aggregate the data, I need to ingest all the details in Kusto and make the aggregations later, out of the event stream. 

Am I missing something, or is this a missing feature which is still impacting to build a lambda architecture with eventstream?

By the way, the fact we can't rename the columns after aggregations creates a strange result. The image below illustrates the problem.

However, after all this configuration, I still received an error:

Add failed Http failure response for https://trd-drw1gqre7q0ya3p386.z6.kusto.fabric.microsoft.com/v1/rest/mgmt: 400 BadRequest

For contact support: {
    "ArtifactId": "d4fb87f3-a069-4e99-93da-abe2caae8b8f",
    "WorkspaceId": "7...

[v-nikhilan-msft]

Hi @DennesTorres 
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them.
Thanks

[xujx]

Thank you, @DennesTorres for your feedback.

In event processor, there is a place where you can change the inferred data type. Select the "taxistreaming" node, you will see the column names listed in the right pane. Hover your mouse on the column, you can see the "...". Click the "...", you will see the option of "Data type". Then you can make the changes to the data type where you think the inferred data type is not what you expect. Step#3 in Process event data with the event processor editor - Microsoft Fabric | Microsoft Learn

For the rename the column of the aggregate operator, it is being rolled out. It will reach to PROD near end of this month. To workaround it, you can add another manage field operator after it to rename this column. Sorry for the inconvenience. 

[DennesTorres]

Hi,

Thank you, this is very "hidden" and really solve most of the problem.

However, I still get the same error from before, 400 - Bad Request. I don't know where to start investigating this and I'm not sure if I should contact support for something which should be just a test, preparation for a technical session.

Thank you!

[xujx]

Can please let me know after which operator you received this "400 - Bad Request"? After clicking the "Add" button in the right pane of the KQL DB destination configuration? 

[DennesTorres]

Hi,

Exactly. The "Add" on the KQL DB destination configuration.

The first "Add", while building the transformations, work fine and return to the KQL DB destination configuration window. In this window I press the second "Add" and get the error.

Kind Regards,

Dennes

[xujx]

Thanks for your quick feedback. I will involve our internal engineering team to check and get back to you once I have further information. Sorry for the inconvenience. 

[xujx · Accepted solution]

Hi, @DennesTorres we've found out the "400 - Bad Request" was caused by the column name of "COUNT_*" which is illegal to KQL DB. It blocks the new KQL table creation. We are improving this part by 1). emit the readable error message 2) allow to rename the column name when using Aggregation or 'Group by' operators.

For a workaround, you may append another Manage field to rename this column to another name without '*'. Again sorry for inconvenience.