KQL json mapping from Date Pipeline

[malheirosJordan]

Hi everyone.

I’m running into an issue when ingesting data from MongoDB into my Kusto database using Azure Data Factory’s Copy Activity. Two of the fields in my source collection are Unix timestamps, but when they arrive in Kusto they end up stored as long instead of datetime.

To fix this, I created a JSON ingestion mapping on my Kusto table that should convert those two timestamp columns into proper datetimes during ingestion. However, when I specify that mapping in the Copy Activity’s Sink settings, I get the following error:

ErrorCode=KustoMappingReferenceHasWrongKind
Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException
Message=Mapping reference should be of kind 'Csv'. Mapping reference: 'UnixToDateTimeMapping'. Kind 'Json'.
Source=Microsoft.DataTransfer.Runtime.KustoConnector

 I’ve tried passing the mapping name and adding various sink properties, but I can’t get ADF to apply my JSON mapping. Has anyone successfully configured a Copy Activity to use a Kusto JSON ingestion mapping? 

The code of my mapping as well

.create-or-alter table info_connections_teste ingestion json mapping "UnixToDateTimeMapping"
'['
'    { "column":"_id",               "datatype":"string",   "Properties":{"Path":"$._id"} },'
'    { "column":"latency",           "datatype":"real",     "Properties":{"Path":"$.latency"} },'
'    { "column":"packetloss_percentage","datatype":"int",   "Properties":{"Path":"$.packetloss_percentage"} },'
'    { "column":"download_occupancy","datatype":"real",     "Properties":{"Path":"$.download_occupancy"} },'
'    { "column":"upload_occupancy",  "datatype":"real",     "Properties":{"Path":"$.upload_occupancy"} },'
'    { "column":"connection_id",     "datatype":"string",   "Properties":{"Path":"$.connection_id"} },'
'    { "column":"createdAt",         "datatype":"long",     "Properties":{"Path":"$.createdAt"} },'
'    { "column":"createdAtDate",     "datatype":"datetime", "Properties":{"Path":"$.createdAt","Transform":"DateTimeFromUnixSeconds"} },'
'    { "column":"updatedAt",         "datatype":"long",     "Properties":{"Path":"$.updatedAt"} },'
'    { "column":"updatedAtDate",     "datatype":"datetime", "Properties":{"Path":"$.updatedAt","Transform":"DateTimeFromUnixSeconds"} },'
'    { "column":"__v",               "datatype":"int",      "Properties":{"Path":"$.__v"} }'
']'

[v-vpabbu]

Hi @malheirosJordan,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Vinay Pabbu

[v-vpabbu]

Hi @malheirosJordan,

Thank you for reaching out to Microsoft Fabric Community Forum.

Azure Data Factory currently supports only CSV ingestion mappings when using Copy Activity to load data into Kusto. JSON ingestion mappings aren't supported in the sink settings for this scenario.

To proceed, you'll need to convert the MongoDB data into CSV format and then use a corresponding CSV ingestion mapping on your Kusto table for proper column handling.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu