Use one-click ingestion to ingest CSV data from a container to a new table in Azure Data Explorer

One-click ingestion enables you to quickly ingest data in JSON, CSV, and other formats into a table and easily create mapping structures. The data can be ingested either from storage, from a local file, or from a container, as a one-time or continuous ingestion process.

This document describes using the intuitive one-click wizard in a specific use case to ingest CSV data from a container into a new table. You can use the same process with slight adaptations to cover a variety of different use cases.

For an overview of one-click ingestion and a list of prerequisites, see One-click ingestion. For information about ingesting data into an existing table in Azure Data Explorer, see One-click ingestion to an existing table

Ingest new data

  1. In the left menu of the Web UI, right-click a database and select Ingest new data (Preview).

    Ingest new data

  2. In the Ingest new data (Preview) window, the Source tab is selected.

  3. Select Create new table and enter a name for the new table. You can use alphanumeric, hyphens, and underscores. Special characters aren't supported.

    Note

    Table names must be between 1 and 1024 characters.

    Create a new table one click ingestion

Select an ingestion type

Under Ingestion type, do the following steps:

  1. Select from container

  2. In the Link to storage field, add the SAS URL of the container, and optionally enter the sample size.

    One-click ingestion from container

A sample of the data appears. If you want to, filter the data to ingest only files that begin end with specific characters. When you adjust the filters, the preview automatically updates.

For example, filter for all files that begin with the word .csv extension.

One click ingestion filter

Edit the schema

Select Edit schema to view and edit your table column configuration. The system will select one of the blobs at random and the schema will be generated based on that blob. By looking at the name of the source, the service automatically identifies if it is compressed or not.

In the Schema tab:

  1. Select Data format:

    In this case, the data format is CSV

  2. You can select the check box Include column names to ignore the heading row of the file.

    Select include column names

In the Mapping name field, enter a mapping name. You can use alphanumeric characters and underscores. Spaces, special characters, and hyphens aren't supported.

Table mapping name One Click Ingestion

Edit the table

When ingesting to a new table, alter various aspects of the table when creating the table.

In the table:

  • Double-click the new column name to edit.

  • Select new column headers and do any of the following actions:

    Action Description
    Change data type Change the data type from the one automatically selected by the service to one of the other supported data types
    Rename column Change the column name
    New column Add a new column. For tabular formats, each column can be ingested into one column on ADX. A new column can be created from a different JSON level.
    Delete column Delete the selected column
    Sort ascending Sort the table by the selected column in ascending order (existing columns only)
    Sort descending Sort the table by the selected column in descending order (existing columns only)

Note

For tabular formats, each column can be ingested into one column in Azure Data Explorer.

Command editor

Above the Editor pane, select the v button to open the editor. In the editor, you can view and copy the automatic commands generated from your inputs.

Note

You can't update the name and datatype for existing tables.

One click ingestion edit view

Start ingestion

Select Start ingestion to create a table and mapping and to begin data ingestion.

Start ingestion One Click Ingestion

Complete data ingestion

In the Data ingestion completed window, all three steps will be marked with green check marks when data ingestion finishes successfully.

One click ingestion complete

Explore quick queries and tools

In the tiles below the ingestion progress, explore Quick queries or Tools:

  • Quick queries includes links to the Web UI with example queries.

  • Tools includes links to Undo or Delete new data on the Web UI, which enable you to troubleshoot issues by running the relevant .drop commands.

    Note

    You might lose data when you use .drop commands. Use them carefully. Drop commands will only revert the changes that were made by this ingestion flow (new extents and columns). Nothing else will be dropped.

Create continuous ingestion for container

Continuous ingestion enables you to create an event grid that listens for new files in the source container. Any new file that meets the criteria of the pre-defined parameters (prefix, suffix, and so on) will be automatically ingested into the destination table.

  1. Select Event Grid in the Continuous ingestion tile to open the Azure portal. The data connection page opens with the event grid data connector opened and with source and target parameters already entered (source container, tables, and mappings).

    continuous ingestion button

  2. Select Create to create a data connection which will listen for any changes, updates, or new data in that container.

    Create Event Hub connection

Next steps