Ingestion mappings

  • Article

Applies to: ✅ 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==