none
AVRO Mapping example RRS feed

  • Question

  • Does anyone have an example of a mapping that I can use to ingest AVRO files that were created automatically by Event Hub (with the Capture feature) to be ingested into Storage Explorer? All examples I can find re for JSON. (as in https://docs.microsoft.com/en-us/azure/data-explorer/ingest-data-event-grid) I have everything working, to the point of seeing records into my ADE tables, but the records are all empty. 

    The closest I have come is to refer to the "Body" property, but that simply returns a seris of comma separated integer pairs, which I assume is the binary of the blobs. THat mapping is: 

    .create table TestTable ingestion avro mapping 'TestMapping' '[{"column":"Body", "Properties":{"Field":"Body"}}]'


    I can find precisely nothing online. Anyone have any ideas? 

    Thanks in advance!


    John

    Monday, April 6, 2020 11:30 PM

Answers

All replies

  • Hi John,

    Please take a moment to review the AVRO mapping example. Is `Body` a series of column values? What are those columns? 

    You can use this:

    [{"column": "rownumber", "Properties":{"Field":"RowNumber"}}]

    Or this:

    [{"column": "rownumber", "Properties":{"Path":"$.RowNumber"}}]

    It is either Path or Field. Your example looks correct but I think you are attempting to ingest a series of columnar values as Body versus mapping out each column header...is that the case? Can you provide an example of the message you are attempting to ingest? 

    Regards,

    Mike 

    Tuesday, April 7, 2020 2:35 AM
    Owner
  • Thanks Mike. I've tried all of those combos (I think). I'm actually after the contents of the bodey which contains the columns that I need. I can't upload a file here, but using Avro viewer, the contents of one of the files looks like the attached. Essentially, I'm after Id, CreatedAt, Topics.... etc. Using the examples you list above doesn't seem to retrieve anything. 

    Thanks!


    John

    Tuesday, April 7, 2020 2:42 AM
  • Did you try the following? If not, please create the following to include all the columns in the Body portion of the .avro file.

    [
      {"column": "Id", "Properties":{"Field":"Id"}},
      {"column": "CreatedAt",   "Properties":{"Field":"CreatedAt"}},
      {"column": "Topics",   "Properties":{"Field":"Topics"}},
      {"column": "Column4",   "Properties":{"Field":"Column4"}},
      {"column": "Column5",   "Properties":{"Field":"Column5"}},
    ]



    The Azure Data explorer table must also include all these columnar values.

    Mike

    Tuesday, April 7, 2020 3:09 AM
    Owner
  • Thanks again

    When I use this mapping:

    .create table TestTable ingestion avro mapping 'TestMapping''[{"column": "Id", "Properties":{"Field":"Id"}},{"column": "CreatedAt", "Properties":{"Field":"CreatedAt"}},{"column": "Topics","Properties":{"Field":"Topics"}}]'

    Against that avro file, ingestion happens, but my result looks like below, which is why I'm banging my head against the wall.... :

    

    Thanks again!


    John

    Tuesday, April 7, 2020 4:25 AM
  • Hi John,

    The correct format should be:

    [
      {"column": "Id", "Properties":{"Path":"$.Body.Id"}},
      {"column": "CreatedAt",   "Properties":{"Path":"$.Body.CreatedAt"}},
      {"column": "Topics",   "Properties":{"Path":"$.Body.Topics"}},
      {"column": "Column4",   "Properties":{"Path":"$.Body.Column4"}},
      {"column": "Column5",   "Properties":{"Path":"$.Body.Column5"}}
    ]

    Please let me know if this works for you. 

    Regards,

    Mike

    Tuesday, April 7, 2020 6:26 PM
    Owner
  • Thanks again man - but still no dice!! Result is the same as my last above. Using that mapping results in a stored mapping of:

    [{"column":"Id","field":null,"datatype":"","transform":null},{"column":"CreatedAt","field":null,"datatype":"","transform":null},{"column":"Topics","field":null,"datatype":"","transform":null}]

    You can see that the field value shows as null. 

    I thought that I'd riff on your object notation and try this one:

    [{"column": "Id", "Properties":{"Field":"Body.Id"}},{"column": "CreatedAt", "Properties":{"Field":"Body.CreatedAt"}},{"column": "Topics","Properties":{"Field":"Body.Topics"}}

    With this one, the field value gets stored, but it doesn't work either. I'm at a loss.


    John

    Tuesday, April 7, 2020 7:38 PM
  • Can you share the entire Azure Data Explorer statement? Are you ingesting from Event Hub or Event Grid? The examples are slightly different between the two.

    .create table TestTable ingestion json mapping 'TestMapping'
    '['
    '  {"column": "Id", "Properties":{"Path":"$.Body.Id"}},'
    '  {"column": "CreatedAt", "Properties":{"Path":"$.Body.CreatedAt"}},'
    '  {"column": "Topics", "Properties":{"Path":"$.Body.Topics"}}'
    ']'

    Or as a single line:

    .create table TestTable ingestion json mapping 'TestMapping' '[{"column": "Id", "Properties":{"Path":"$.Body.Id"}},{"column": "CreatedAt", "Properties":{"Path":"$.Body.CreatedAt"}},{"column": "Topics", "Properties":{"Path":"$.Body.Topics"}}]'

    If neither of those work, can you try this:

    .create table TestTable ingestion json mapping "TestMapping"
    '['
    '  {"column": "Id", "Properties":{"Path":"$.Body.Id"}},'
    '  {"column": "CreatedAt", "Properties":{"Path":"$.Body.CreatedAt"}},'
    '  {"column": "Topics", "Properties":{"Path":"$.Body.Topics"}}'
    ']'

    Regards,

    Mike

    Tuesday, April 7, 2020 11:36 PM
    Owner
  • Still no joy I'm afraid. Although your last examples use json as the mapping type, not avro. I did try them both ways, but the ingestion itself fails with the json specifier. Sounds like I've got the syntax right, but it just isn't working. 

    John

    Wednesday, April 8, 2020 1:40 AM
  • Hey Mike - I just realized that I didn't answer the first part of your question. The files themselves were created by an Event hub, but the ingestion is from an Event Grid - ie ingest when a new blob is created. 


    John

    Wednesday, April 8, 2020 11:22 AM
  • I am checking to see if this scenario is supported. I see an example for Azure Data Warehouse and Azure Cosmos DB with an Azure Function App. Tutorial: Migrate captured Event Hubs data to a SQL Data Warehouse using Event Grid and Azure Functions

    I will update you when I have more information to share.

    Regards,

    Mike

    Thursday, April 9, 2020 12:58 AM
    Owner
  • Thanks. I've opened a support ticket as well. Between us, we'll get to the bottom of it. 


    John

    Thursday, April 9, 2020 2:06 AM
  • Hi John,

    Why not ingest from Event Hub directly without the capture functionality? Is Event Grid part of a larger solution?

    Ingest data from Event Hub into Azure Data Explorer

    I did reach out to the Product Group to present your issue and the response I received indicated that it is not a common implementation. So, just trying to understand the Event Hub capture functionality and Event Grid to Data Explorer. 

    Regards,

    Mike

    Thursday, April 9, 2020 5:29 PM
    Owner
  • A good question! 

    In fact, I am. However, this is a recent development. THis particular event hub has been running for a year, with the Capture feature turned on. The need is to backfill with the historical data that is in the AVRO files. 

    For longer term, it provides a backup strategy. SHould the ADE ingestion fail for a period longer than the retention window of the event hub, it could be replayed from the captured AVRO files. 

    Make sense?


    John

    Thursday, April 9, 2020 5:53 PM
  • Hey Mike - thanks again. 

    Conversing with support, it turns out that using avro as an ingestion parameter will only return a binary array of the Body field. I'm not ure what the value of that is, but there is another (undocumented for now) parameter names "apacheavro" that does what I need it to. It cannot be used in an ingestion connection yet, but I was able to satify my scenario by using LightIngest with it. https://docs.microsoft.com/en-us/azure/data-explorer/lightingest

    Cheers


    John

    Monday, April 13, 2020 3:53 PM
  • Thank you for this additional detail, John. I have marked this as the answer as it is great feedback that can assist others experiencing the same scenario.

    Regards,

    Mike

    Monday, April 13, 2020 4:05 PM
    Owner