Improving Data Analysis Efficiency with DropMappedField in Azure Data Explorer

DropMappedField in Azure Data Explorer: Overview

This blog post explains what DropMappedField is in Azure Data Explorer, its key features, and advantages.

DropMappedField is a data mapping transformation enabling JSON object-to-column mapping and removal of nested fields referenced by other mappings. This simplifies data ingestion, reduces storage consumption, and enhances query performance.

Key Features

Azure Data Explorer is a powerful data analytics service that allows ingestion, storage, and querying of massive volumes of structured, semi-structured, and unstructured data. It excels in ingesting diverse data sources and formats like JSON, CSV, Parquet, Avro, and more.

However, not all data formats are equally suitable for analysis. For example, JSON documents can have complex nested structures that make it hard to extract the relevant information and organize it into columns. To solve this problem, Azure Data Explorer provides data mappings, which are rules that define how to transform the ingested data into a tabular format.

In addition, Azure Data Explorer supports the data mapping transformation called DropMappedField. This transformation empowers you to map an object in a JSON document to a column and remove any nested fields that other column mappings reference. For example, consider the following JSON document:

{
  "name": "Alice",
  "age": 25,
  "address": {
    "city": "Seattle",
    "state": "WA",
    "zip": 98101
  }
}

If you want to map this document to a table with four columns: name, age, city, and state, you can use the following data mapping:

.create table MyTable (name: string, age: int, city: string, state: string)
.create table MyTable ingestion json mapping 'MyMapping' '[{"column":"name","path":"$.name"},{"column":"age","path":"$.age"},{"column":"city","path":"$.address.city"},{"column":"state","path":"$.address.state"},{"column":"address","path":"$.address","transform":"DropMappedField"}]'

Notice that the last column mapping employs the DropMappedField transformation. It maps the address object to a column and removes the city and state fields, already mapped to other columns. This approach prevents data duplication and conserves storage space.

Advantages of DropMappedField

The DropMappedField transformation offers several advantages:

• It simplifies data ingestion by enabling mapping of complex JSON objects to columns without specifying each nested field.
• Reduces storage consumption by eliminating redundant data unnecessary for analysis.
• Improves query performance by reducing the number of columns and fields that require scanning.

Microsoft Fabric’s Real-Time Analytics incorporates the DropMappedField transformation as a feature. The platform supports analysis and ingestion of streaming data from diverse sources like web apps, social media, and IoT devices.

Conclusion: DropMappedField in Azure Data Explorer

DropMappedField is a valuable feature for optimizing data ingestion and analysis in Azure Data Explorer. Efficiently mapping JSON objects to columns and eliminating redundant nested fields is a highly effective method. This approach drastically reduces the time, effort, and resources required to handle even the most complex and extensive JSON data.