Ingesting json events with various schemas from Blob Storage to Azure Data Explorer

Francois 26

Hi,

We're a game company using PlayFab for our backend. Play sessions events are created by PlayFab and are automatically sent to a blob storage in Azure. I created an Azure Data Explorer Database which ingests these events whenever the blob storage receives them. To ingest the data, I've created a database mapping and a data connection as shown in this tutorial: https://video2.skills-academy.com/en-us/azure/data-explorer/ingest-data-event-grid

Ingestion is globally working properly, but I realized some events were not properly ingested. It turns out some PlayFab events are using a different JSON format, as indicated by a SchemaVersion field. For these events, the mapping should be different (columns should correspond to a different name in the json file). I would therefore need to change the mapping based on that field. Is that something that can be done using the current ingestion method we're using? I've seen that other Azure services seem to support this type of conditional mapping, but nothing for Kusto in Azure Data Explorer. Ultimately we need the data to be in Azure Data Explorer to analyze events through queries.

Thanks a lot for your help

Francois 26 Reputation points

2020-10-12T17:37:21.82+00:00
I thought I'd provide different possible approaches I've been thinking about, in case one of them seem more relevant to you:

if there is a way to ingest / not ingest based on a condition, I could create two Data connections, each with their own conditions and mapping.

if there is a way to name multiple potential fields to fill a given column, I could do something like:
.create table TestTable ingestion json mapping 'TestMapping' '[{"column":"Value","path":"$.Value" OR "path":"$.ValueB"}]'

if it is possible to have an automated task at a certain frequency or after ingesting, manually convert rows that are using a different schema (we do have all the information necessary in a column)
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-14T08:48:46.023+00:00
Hi @Francois ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query with details.

For creating two data connections for files with different metadata, please let us know if there is any way the files can be filtered by a naming pattern that can be used in prefix property as below.

As per my understanding, there is no possibility to give multiple potential fields for a column mapping. I will reach out to product team for confirmation on this and will get back to you.

For automation, Azure data factory can be a possible tool to trigger the pipeline to convert the column names in JSON file from one container and remap the modified files to another container that can be used by the Azure Data Explorer. I will also check for any better alternatives from product team and will share with you. Thanks for your patience!
Francois 26 Reputation points

2020-10-14T12:35:53.547+00:00
Hello,

Thanks for the options you list here.

Unfortunately we don't have control over how the data is sent to blob storage, as it's automated by PlayFab. Events with different formats are mixed together in the json files, so the solution needs to go through each events in json one by one.

multiple potential fields: thanks for looking into this.

Automation: is your suggestion to do something like: PlayFab automated backup -> Azure Blob Storage -> Azure data factory -> Azure Data Explorer, and have the data factory do the json parsing / modifications in the json files directly? To keep things simple and not add another service, I was hoping I could run Kusto queries on the Azure Data Explorer table at a given frequency. Using Power Automate seems like a potential option, but it also adds another service to maintain and pay for.

Accepted answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-15T07:00:57.093+00:00
Hi @Francois ,

Thanks for the detailed response.

Since there is no information on specific pattern of file names, prefix option is ruled out

Product team confirmed that multiple potential fields is not feasible today. They suggested the feasible thing in this scenario can be to ingest entire records as “dynamic” field in one table with low retention and set up an update policy (or several) to split the data as required.

Yes, Power Automate is also an option but from ETL aspect, Azure data factory is more powerful than Power Automate and is reasonable in cost when I looked at pricing of both the tools. I understand the challenge of using another service, please suggest if above alternative using update policy will be helpful.
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-10-15T07:01:39.43+00:00

If you face any challenges in implementing the workaround, please reach out to AzCommunity[at]microsoft[dot]com with subject "Attn:Haritha - Ingesting json events with various schemas from Blob Storage to Azure Data Explorer" and our team can help to achieve the same.

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T08:10:07.123+00:00

Hi @Francois ,

We have not received a response from you. Please suggest if above details helped in clarifying the question and workaround suggested is helpful. Kindly let us know for further queries and we will be glad to assist.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Francois 26 Reputation points

2020-10-16T12:07:26.143+00:00

Hi Haritha,

Thanks for the suggestions. I've been trying out the update policy route, which seems to work. I currently have duplicate events: events that were initially not ingested properly, and another row with these events properly ingested. This is because I have two data connections, one for each table. I'm thinking the next step will be to only have one data connection, and do the entire conversion in the update policy function instead of mixing mapping and update policy.

I will reach out if I'm encountering issues with that work around. Thanks!

HarithaMaddi-MSFT 10,136 Reputation points

2020-10-16T13:08:07.29+00:00

Thanks @Francois for response. Sure, will be glad to support you for any further issues.

Steve Flowers 1 Reputation point Microsoft Employee

2020-10-28T13:21:24.277+00:00

I agree on making the entire document "dynamic." KQL can work through the JSON schema fine as long as there is a predictable set of attributes at the top level. For events like these I would argue a better pattern is flowing the events from the app to a service like Event Hubs. This allows you a lot of flexibility in dealing with the data in a flow versus flat files. You can ingest directly into ADX or use a service like Stream Analytics to determine where to send messages (another EH > ADX table, an Azure Function to act on the file, etc...).

Francois 26 Reputation points

2020-10-28T13:30:59.533+00:00

Thanks for the follow-up @Steve Flowers . The solution we ended up with is to ingest all data in a dynamic field and convert to another table using a fairly complex update policy. While it seems to be functional, do you see downsides to that approach? One potential area of concern is that Virtual Machine costs are quite high - we're currently investigating how we could optimize this.
Sign in to comment

Share via

Ingesting json events with various schemas from Blob Storage to Azure Data Explorer

0 additional answers