for several days I'm trying to ingest Apache Avro formatted data from a blob storage into the Azure Data Explorer.

I'm able to reference the toplevel JSON-keys like $.Body (see red underlined example in the screenshot below), but when it goes to the nested JSON-keys, Azure fails to parse them properly and displays nothing (as seen in the green column: I would expect $.Body.entityId to reference the key "entityId" inside the Body-JSON).

Many thanks in advance for any help!

CodePudding user response：

For those having the same issue, here is the workaround we currently use:

First, assume that we want to ingest the contents of the Body field from the avro file to the table avro_destination.

Step 1: Create an ingestion table

.create table avro_ingest(
    Body: dynamic
    // optional other columns, if you want...
)

Step 2: Create an update policy

.create-or-alter function
    with (docstring = 'Convert avro_ingest to avro_destination', folder='ingest')
    convert_avro_ingest() {
        avro_ingest
        | extend entityId = tostring(Body.entityId)
        | extend messageId = tostring(Body.messageId)
        | extend eventTime = todatetime(Body.eventTime)
        | extend data = Body.data
        | project entityId, messageId, eventTime, data
    }

.alter table avro_destination policy update
@'[{ "IsEnabled": true, "Source": "avro_ingest", "Query": "convert_avro_ingest()", "IsTransactional": false, "PropagateIngestionProperties": true}]'

Step 3: Ingest the .avro files into the avro_ingest table

...as seen in the Question, with one Column containing the whole Body-JSON per entry.