Azure Data Explorer Stream Ingest formatted JSON Documents-CodePudding

We ingest JSON messages from Event Hub into Azure Data Explorer via Stream Ingestion.

I created a table with this statement

.create table messages(SerialNumber: string, ReceivedUtcTime: datetime, IngestEventEnqueuedUtcTime: datetime, IngestEventProcessedUtcTime: datetime, ProcessEventEnqueuedUtcTime: datetime, MessageVersion: int, MessageType: string, Payload: dynamic)

with this Ingestion Mapping:

.create-or-alter table messages ingestion json mapping "eventhub_mapping"
'['
  '{"column":"SerialNumber",                "path":"$.serialNumber",          "datatype":"string"     },'
  '{"column":"ReceivedUtcTime",             "path":"$.timestampUtc",          "datatype":"datetime"   },'
  '{"column":"IngestEventEnqueuedUtcTime",  "path":"$.eventEnqueuedUtcTime",  "datatype":"datetime"   },'
  '{"column":"IngestEventProcessedUtcTime", "path":"$.eventProcessedUtcTime", "datatype":"datetime"   },'
  '{"column":"ProcessEventEnqueuedUtcTime", "path":"$.x-opt-enqueued-time",   "datatype":"datetime"   },'  
  '{"column":"MessageVersion",              "path":"$.messageVersion",        "datatype":"int"        },'
  '{"column":"MessageType",                 "path":"$.messageType",           "datatype":"string"     },'
  '{"column":"Payload",                     "path":"$.payload",                 "datatype":"dynamic",   "transform":null    }'
']'

The messages we want to ingest look like this:

{
  "timestampUtc": "2022-03-07T00:00:00Z",
  "eventEnqueuedUtcTime": "2022-03-07T00:00:00Z",
  "eventProcessedUtcTime": "2022-03-07T00:00:00Z",
  "messageType": "Information",
  "messageVersion": 1,
  "serialNumber": "ser-1",
  "payload": {
    "TimestampUtc": "2022-03-07T00:00:00Z",
    "Value": 123.12
  }
}

When i try to ingest those messages i get errors during ingestion and nothing gets stored in the table.

However, i found out, if i transmit the JSON as a single line without formatting, the messages gets stored as expected.

So this message works:

{"timestampUtc":"2022-03-07T00:00:00Z","eventEnqueuedUtcTime":"2022-03-07T00:00:00Z","eventProcessedUtcTime":"2022-03-07T00:00:00Z","messageType":"Information","messageVersion":1,"serialNumber":"ser-1","payload":{"TimestampUtc":"2022-03-07T00:00:00Z","Value":123.12}}

Is this something to be expected or can i configure this behavior to allow for every valid JSON to be ingested into Data Explorer?

I have read about the option to ingest multiline json but this does not seem to be supported when creating the ingestion mapping.

CodePudding user response：

You need to specify the multiline json in your EventHub connection, or in the ingestion command (not in the mapping).

See the ingestion properties doc (specifically the "format" property) and follow the link to see the applicable names of the format to specify in the ingestion command or the EventHub connection.