Referencing the mapping Data type

Question

Referencing the mapping Data type

Keith Springs 21

Currently I'm attempting to Query my ingested avro data. The issue I'm having currently is that I don't know how to pull data from the body. I want the "value" property in my "body" column, how should I reference that value?

The end goal is to create a line graph that has my enqueued time as the x axis and the value as my y axis.

Posted below is what was copied and pasted from using the take operator.

"EnqueuedTimeUtc": 2022-10-26T12:05:06.0460000Z,
"Properties": ,
"SystemProperties": {
	"connectionDeviceId": "axcf2152",
	"connectionDeviceGenerationId": "638005690194176165",
	"connectionAuthMethod": "{\"scope\":\"device\",\"type\":\"sas\",\"issuer\":\"iothub\",\"acceptingIpFilterRule\":null}",
	"enqueuedTime": "2022-10-26T12:05:06.0460000Z"
},
"Body": {"deviceId":"axcf2152","name":"Memory","value":5,"dateTime":1666785906051}

Keith Springs 21 Reputation points

2023-01-23T20:57:47.4+00:00

For more context the data type is a null | byte. This is what was found in the schema.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-23T21:36:33.3466667+00:00

Hello @Keith Springs ,

are you able to change this column type for Body into the Dynamic type?
Keith Springs 21 Reputation points

2023-01-24T20:42:56.3466667+00:00

I haven't tried that yet, how would I change the body into the dynamic type? I was able to get the typing to null | string, but I haven't been able to get it do a dynamic type.

What is the difference between byte and dynamic and why won't byte work?

Thank you for seeing to my question Sander.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-24T20:54:50.7233333+00:00

Hello @Keith Springs ,

Does it seem this schema is generated by some tool outside Azure Data Explorer?

I try to understand how a table in ADX is defined and how the mapping is done.

If you use the ADX Data connection, avro ingestion is supported by default:

Although it is focussed on IoT Hub data, perhaps this explanation post works for you too?
Keith Springs 21 Reputation points

2023-01-25T15:02:06.2433333+00:00

The reason why I'm not able to directly take the data from my container to the Azure data explorer is because it gets converted into this. Which isn't really readable data for the body.

All of this needs to be converted from base 64 to string. I did that through the azure data factory by taking a derived column and converting it into a string.

Posted in the first post is the end result of what I got after converting it into a string. After some experimenting I did find that the split function almost works, but unfortunately I don't think it'd be a scalable solution if the Body column were to expand.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-25T15:14:05.2366667+00:00

Hello @Keith Springs ,

so the path you use is SOURCE(AVRO)->ADF->ADX.

I'm not sure what is needed in ADF to fix your conversion challenges (like base64).

Can you try SOURCE(AVRO)->EventHub->ADX using the Database data connection?

Take a look at the mapping example because you have to transform the incoming body and mapped system properties to the right (typed) columns unless you work with the dynamic type.

Keep in mind this data connection does not support user properties.
Keith Springs 21 Reputation points

2023-01-25T20:19:01.4066667+00:00

I'd like to avoid using Event Hubs because of pricing. I might consider going that route if I can't figure out how to divide these columns.

The reason why I used ADF is because I could use the derived column's schema modifier to change the body's base64 into being a string.

I was thinking of trying parsing as a way to break up this data, but unfortunately I can't figure out a way to setup the parse command correctly.

Your answer

Keith Springs 21 Reputation points

2023-01-23T20:57:47.4+00:00

For more context the data type is a null | byte. This is what was found in the schema.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-23T21:36:33.3466667+00:00

Hello @Keith Springs ,

are you able to change this column type for Body into the Dynamic type?
Keith Springs 21 Reputation points

2023-01-24T20:42:56.3466667+00:00

I haven't tried that yet, how would I change the body into the dynamic type? I was able to get the typing to null | string, but I haven't been able to get it do a dynamic type.

What is the difference between byte and dynamic and why won't byte work?

Thank you for seeing to my question Sander.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-24T20:54:50.7233333+00:00

Hello @Keith Springs ,

Does it seem this schema is generated by some tool outside Azure Data Explorer?

I try to understand how a table in ADX is defined and how the mapping is done.

If you use the ADX Data connection, avro ingestion is supported by default:

Although it is focussed on IoT Hub data, perhaps this explanation post works for you too?
Keith Springs 21 Reputation points

2023-01-25T15:02:06.2433333+00:00

The reason why I'm not able to directly take the data from my container to the Azure data explorer is because it gets converted into this. Which isn't really readable data for the body.

All of this needs to be converted from base 64 to string. I did that through the azure data factory by taking a derived column and converting it into a string.

Posted in the first post is the end result of what I got after converting it into a string. After some experimenting I did find that the split function almost works, but unfortunately I don't think it'd be a scalable solution if the Body column were to expand.
Sander van de Velde | MVP 36,761 Reputation points MVP Volunteer Moderator

2023-01-25T15:14:05.2366667+00:00

Hello @Keith Springs ,

so the path you use is SOURCE(AVRO)->ADF->ADX.

I'm not sure what is needed in ADF to fix your conversion challenges (like base64).

Can you try SOURCE(AVRO)->EventHub->ADX using the Database data connection?

Take a look at the mapping example because you have to transform the incoming body and mapped system properties to the right (typed) columns unless you work with the dynamic type.

Keep in mind this data connection does not support user properties.
Keith Springs 21 Reputation points

2023-01-25T20:19:01.4066667+00:00

I'd like to avoid using Event Hubs because of pricing. I might consider going that route if I can't figure out how to divide these columns.

The reason why I used ADF is because I could use the derived column's schema modifier to change the body's base64 into being a string.

I was thinking of trying parsing as a way to break up this data, but unfortunately I can't figure out a way to setup the parse command correctly.

Share via

Referencing the mapping Data type

Your answer