none
Ingest JSON into multiple tables on Azure RRS feed

  • Question

  • I have a JSON structure coming into an Event Hub stream that includes multiple levels of data, like in the simplified example below. 

    I am looking for the best method of ingesting this into multiple tables, namely Sale and Item, while maintaining relationship between the elements.

    In C# .NET I would read the file, import into a DataSet component, and then store this into a database. It however needs to be scalable and be able to work on huge volumes of data on Azure.

    So the goal is to do this on Azure, and I am looking for the most appropriate services to do this. Currently Azure Data Explorer seems the way to go, I however don't see how I could match the DataSet functionality. 
    I am also looking for low-code / no-code solutions, and instead of JSON we could also use AVRO if that would give us additional possibilities.

    I could use some advice on how to do this. Any idea is of course welcome!

    {
    	"Sale": {
    		"MarketID": "123",
    		"KioskID": "1234",
    		"SaleID": "1234-1",
    		"SaleTime": "2019-06-27T12:57:19.000+02:00",
    		"Items": [
    			{
    				"ProductID": "1",
    				"Code": "1234",
    				"Quantity": "1",
    				"Cost": "0.11",
    				"Price": "0.50",
    				"Discount": "0.00",
    				"Deposit": "0.00",
    				"Total": "0.51"
    			}, {
    				"ProductID": "2",
    				"Code": "4567",
    				"Quantity": "1",
    				"Cost": "0.11",
    				"Price": "0.50",
    				"Discount": "0.00",
    				"Deposit": "0.00",
    				"Total": "0.51"
    			}
    		]
    	}
    }


    Sunday, June 30, 2019 7:24 PM

All replies

  • Hi Ingolf.O,

    You are dealing with an Array and there is some data prep to un-nest the data before ingesting into Event Hub. This Stack Overflow post provides some detail as to how to accomplish this, before sending to Azure Data Explorer. This doc discusses sending the data to Azure Data Explorer. 

    Regards,

    Mike

    Monday, July 1, 2019 3:14 AM
    Owner