Ingest data from a container/ADLS into 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, or as a one-time or continuous ingestion process.
This document describes using the intuitive one-click wizard to ingest CSV data from a container into a new table. Ingestion can be done as a one-time operation, or as a continuous method by setting up an Event Grid ingestion pipeline that that responds to new files in the source container and ingests qualifying data into your table. This process can be used with slight adaptations to cover a variety of different use cases.
For an overview of one-click ingestion, 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
Prerequisites
- An Azure subscription. Create a free Azure account.
- Create a cluster and database.
- A storage account.
- Event Grid notification subscription can be set on Azure Storage accounts for
BlobStorage,StorageV2, or Data Lake Storage Gen2.
Note
To enable access between a cluster and a storage account without public access (restricted to private endpoint/service endpoint) in different subnets of the same VNET, see Create a Private Endpoint in your Azure Data Explorer cluster in your virtual network.
Ingest new data
In the left menu of the Web UI, select Data.
From the Quick actions section, select Ingest new data. Alternatively, from the All actions section, select Ingest new data and then Ingest.
In the Ingest new data window, the Destination tab is selected. The Cluster and Database fields are automatically populated.
To add a new connection to a cluster, select Add cluster connection below the auto-populated cluster name.
In the popup window, enter the Connection URI for the cluster you are connecting.
Enter a Display Name that you want to use to identify this cluster, and select Add.
In Table, check 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.
Select Next: Source
Select an ingestion type
Under Source type, do the following steps:
- Select From blob container (blob container, ADLS Gen2 container). You can ingest up to 5000 blobs from a single container.
- In the Link to storage field, add the blob URI with SAS token or Account key of the container, and optionally enter the sample size. To ingest from a folder within this container, see Ingest from folder in a container.
Note
The SAS URL can be created manually or automatically.
Tip
For ingestion from file, see Use one-click ingestion to ingest JSON data from a local file to an existing table in Azure Data Explorer
Ingest from folder in a container
To ingest from a specific folder within a container, generate a string of the following format:
container_path/folder_path;access_key_1
You'll use this string instead of the SAS URL in select an ingestion type.
Navigate to the storage account, and select Storage Explorer > Select Blob Containers
Browse to the selected folder, and select Copy URL. Paste this value into a temporary file and add
;to the end of this string.
On the left menu under Settings, select Access keys.
Under key 1, copy the Key string. Paste this value at the end of your string from step 2.
Storage subscription error
If you get the following error message when ingesting from a storage account:
Couldn't find the storage under your selected subscriptions. Please add the storage account
storage_account_namesubscription to your selected subscriptions in the portal.
Select the
icon from the top-right menu tray. A Directory + subscription pane opens.In the All subscriptions dropdown, add your storage account's subscription to the selected list.
Filter data
If you want to, filter the data to ingest only files that begin end with specific characters.
For example, filter for all files that begin with the word .csv extension.
The system will select one of the files at random and the schema will be generated based on that Schema defining file. You can select a different file.
Edit the schema
Select Next: Schema to view and edit your table column configuration. The service automatically identifies if the schema is compressed by looking at the name of the source.
In the Schema tab:
Confirm the format selected in Data format:
In this case, the data format is CSV
Tip
If you want to use JSON files, see Use one-click ingestion to ingest JSON data from a local file to an existing table in Azure Data Explorer.
You can select the check box Ignore the first record to ignore the heading row of the file.
In the Mapping name field, enter a mapping name. You can use alphanumeric characters and underscores. Spaces, special characters, and hyphens aren't supported.
Edit the table
When ingesting to a new table, alter various aspects of the table when creating the table.
The changes you can make in a table depend on the following parameters:
- Table type is new or existing
- Mapping type is new or existing
| Table type | Mapping type | Available adjustments |
|---|---|---|
| New table | New mapping | Change data type, Rename column, New column, Delete column, Update column, Sort ascending, Sort descending |
| Existing table | New mapping | New column (on which you can then change data type, rename, and update), Update column, Sort ascending, Sort descending |
| Existing mapping | Sort ascending, Sort descending |
Note
When adding a new column or updating a column, you can change mapping transformations. For more information, see Mapping transformations
Note
For tabular formats, you can't map a column twice. To map to an existing column, first delete the new column.
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.
Select Next: Summary to create a table and mapping and to begin data 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.
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
.dropcommands.Note
You might lose data when you use
.dropcommands. 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
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.
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).
Data connection: Basics
- The Data connection blade opens with the Basics tab selected.
- Enter the Storage account.
- Choose the Event type that will trigger ingestion.
- Select Next: Ingest properties
Ingest properties
The Ingest properties tab opens with pre-filled routing settings. The target table name, format, and mapping name are taken from the table created above.
Select Next: Review + create
Review + create
Review the resources, and select Create.