Ingestion mappings

Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer

Ingestion mappings are used during ingestion to map incoming data to columns inside tables.

Data Explorer supports different types of mappings, both row-oriented (CSV, JSON, AVRO and W3CLOGFILE), and column-oriented (Parquet and ORC).

Ingestion mappings can be precreated and can be referenced from the ingest command using ingestionMappingReference parameters. Ingestion is possible without specifying a mapping. For more information, see identity mapping.

Each element in the mapping list is constructed from three fields:

Property	Required	Description
Column	✔️	Target column name in the table.
Datatype		Datatype with which to create the mapped column if it doesn't already exist in the table.
Properties		Property-bag containing properties specific for each mapping as described in each specific mapping type page.

Important

For queued ingestion:

• If the table referenced in the mapping doesn't exist in the database, it gets created automatically, given that valid data types are specified for all columns.
• If a column referenced in the mapping doesn't exist in the table, it gets added automatically to the table as the last column upon the first time data is ingested for that column, given a valid data type is specified for the column. To add new columns to a mapping, use the .alter ingestion mapping command.
• Data is batched using Ingestion properties. The more distinct ingestion mapping properties used, such as different ConstValue values, the more fragmented the ingestion becomes, which can lead to performance degradation.

Supported mapping types

The following table defines mapping types to be used when ingesting or querying external data of a specific format.

Data Format	Mapping Type
CSV	CSV Mapping
TSV	CSV Mapping
TSVe	CSV Mapping
PSV	CSV Mapping
SCSV	CSV Mapping
SOHsv	CSV Mapping
TXT	CSV Mapping
RAW	CSV Mapping
JSON	JSON Mapping
AVRO	AVRO Mapping
APACHEAVRO	AVRO Mapping
Parquet	Parquet Mapping
ORC	ORC Mapping
W3CLOGFILE	W3CLOGFILE Mapping

Identity mapping

Ingestion is possible without specifying ingestionMapping or ingestionMappingReference properties. The data is mapped using an identity data mapping derived from the table's schema. The table schema remains the same. format property should be specified. See ingestion formats.

Format type	Format	Mapping logic
Tabular data formats with defined order of columns, such as delimiter-separated or single-line formats.	CSV, TSV, TSVe, PSV, SCSV, Txt, SOHsv, Raw	All table columns are mapped in their respective order to data columns in order they appear in the data source. Column data type is taken from the table schema.
Formats with named columns or records with named fields.	JSON, Parquet, Avro, ApacheAvro, Orc, W3CLOGFILE	All table columns are mapped to data columns or record fields having the same name (case-sensitive). Column data type is taken from the table schema.

Warning

Any mismatch between the table schema and the structure of data, such as column or field data types, column or field names or their number might result in empty or incorrect data ingested.

Mapping transformations

Some of the data format mappings (Parquet, JSON, and AVRO) support simple and useful ingest-time transformations. Where the scenario requires more complex processing at ingest time, use Update policy, which allows defining lightweight processing using KQL expression.

Path-dependant transformation	Description	Conditions
PropertyBagArrayToDictionary	Transforms JSON array of properties, such as {events:[{"n1":"v1"},{"n2":"v2"}]}, to dictionary and serializes it to valid JSON document, such as {"n1":"v1","n2":"v2"}.	Available for JSON, Parquet, AVRO, and ORC mapping types.
SourceLocation	Name of the storage artifact that provided the data, type string (for example, the blob's "BaseUri" field).	Available for CSV, JSON, Parquet, AVRO, ORC, and W3CLOGFILE mapping types.
SourceLineNumber	Offset relative to that storage artifact, type long (starting with '1' and incrementing per new record).	Available for CSV, JSON, Parquet, AVRO, ORC, and W3CLOGFILE mapping types.
DateTimeFromUnixSeconds	Converts number representing unix-time (seconds since 1970-01-01) to UTC datetime string.	Available for CSV, JSON, Parquet, AVRO, and ORC mapping types.
DateTimeFromUnixMilliseconds	Converts number representing unix-time (milliseconds since 1970-01-01) to UTC datetime string.	Available for CSV, JSON, Parquet, AVRO, and ORC mapping types.
DateTimeFromUnixMicroseconds	Converts number representing unix-time (microseconds since 1970-01-01) to UTC datetime string.	Available for CSV, JSON, Parquet, AVRO, and ORC mapping types.
DateTimeFromUnixNanoseconds	Converts number representing unix-time (nanoseconds since 1970-01-01) to UTC datetime string.	Available for CSV, JSON, Parquet, AVRO, and ORC mapping types.
DropMappedFields	Maps an object in the JSON document to a column and removes any nested fields already referenced by other column mappings.	Available for JSON, Parquet, AVRO, and ORC mapping types.
BytesAsBase64	Treats the data as byte array and converts it to a base64-encoded string.	Available for AVRO mapping type. For ApacheAvro format, the schema type of the mapped data field should be bytes or fixed Avro type. For Avro format, the field should be an array containing byte values from [0-255] range. null is ingested if the data doesn't represent a valid byte array.

Mapping transformation examples

DropMappedFields transformation:

Given the following JSON contents:

{
    "Time": "2012-01-15T10:45",
    "Props": {
        "EventName": "CustomEvent",
        "Revenue": 0.456
    }
}

The following data mapping maps entire Props object into dynamic column Props while excluding already mapped columns (Props.EventName is already mapped into column EventName, so it's excluded).

[
    { "Column": "Time", "Properties": { "Path": "$.Time" } },
    { "Column": "EventName", "Properties": { "Path": "$.Props.EventName" } },
    { "Column": "Props", "Properties": { "Path": "$.Props", "Transform":"DropMappedFields" } },
]

The ingested data looks as follows:

Time	EventName	Props
2012-01-15T10:45	CustomEvent	{"Revenue": 0.456}

BytesAsBase64 transformation

Given the following AVRO file contents:

{
    "Time": "2012-01-15T10:45",
    "Props": {
        "id": [227,131,34,92,28,91,65,72,134,138,9,133,51,45,104,52]
    }
}

The following data mapping maps the ID column twice, with and without the transformation.

[
    { "Column": "ID", "Properties": { "Path": "$.props.id" } },
    { "Column": "Base64EncodedId", "Properties": { "Path": "$.props.id", "Transform":"BytesAsBase64" } },
]

The ingested data looks as follows:

ID	Base64EncodedId
[227,131,34,92,28,91,65,72,134,138,9,133,51,45,104,52]	44MiXBxbQUiGigmFMy1oNA==