How to ingest historical data into Azure Data Explorer

LightIngest can be useful to load historical data from an existing storage system to Azure Data Explorer. While you can build your own command using the list of Command-line arguments, this article shows you how to autogenerate this command through an ingestion wizard. In addition to creating the command, you can use this process to create a new table, and create schema mapping. This tool infers schema mapping from your dataset.

Destination

1. In the Azure Data Explorer web UI, from the left menu, select Query.

2. Right-click the database in which you want to ingest the data, and then select LightIngest.

   

   The Ingest data window opens with the Destination tab selected. The Cluster and Database fields are automatically populated.

3. Select a target table. If you want to ingest data into a new table, select New table, and then enter a table name.

   Note

   Table names can be up to 1024 characters including spaces, alphanumeric, hyphens, and underscores. Special characters aren't supported.

   

4. Select Next: Source.

Source

1. Under Select source, select either Add URL or Select container.

   • When adding a URL, under Link to source, specify the account key or SAS URL to a container. You can create the SAS URL manually or automatically.

   • When selecting a container from your storage account, select your Storage subscription, Storage account, and Container from the dropdown menus.

     

   Note

   Ingestion supports a maximum file size of 6 GB. The recommendation is to ingest files between 100 MB and 1 GB.

2. Select Advanced settings to define additional settings for the ingestion process using LightIngest.

   

3. In the Advanced configuration pane, define the LightIngest settings according to the following table.

   

   Property	Description
   Creation time pattern	Specify to override the ingestion time property of the created extent with a pattern, for example, to apply a date based on the folder structure of the container. See also Creation time pattern.
   Blob name pattern	Specify the pattern used to identify the files to be ingested. Ingest all the files that match the blob name pattern in the given container. Supports wildcards. We recommended enclosing in double quotes.
   Tag	A tag assigned to the ingested data. The tag can be any string.
   Limit amount of files	Specify the number of files that can be ingested. Ingests the first n files that match the blob name pattern, up to the number specified.
   Don't wait for ingestion to complete	If set, queues the blobs for ingestion without monitoring the ingestion process. If not set, LightIngest continues to poll the ingestion status until ingestion is complete.
   Display only selected items	List the files in the container, but doesn't ingest them.

4. Select Done to return to the Source tab.

   1. Optionally, select File Filters to filter the data to ingest only files in a specific folder path or with a particular file extension.

      

      By default, one of the files in the container is randomly selected and used to generate the schema for the table.

   2. Optionally, under Schema defining file, you can specify the file to use.

5. Select Next: Schema to view and edit your table column configuration.

Schema

The schema tab provides a preview of the data.

To generate the LightIngest command, select Next: Start Ingestion.

Optionally:

• Change the automatically inferred Data format by selecting the desired format from the dropdown menu.
• Change the automatically inferred Mapping name. You can use alphanumeric characters and underscores. Spaces, special characters, and hyphens aren't supported.
• When using an existing table, you can Keep current table schema if the table schema matches the selected format.
• Select Command viewer to view and copy the automatic commands generated from your inputs.
• Edit columns. Under Partial data preview, select the column dropdown menus to alter various aspects of 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

Ingest

1. Once the table, mapping, and LightIngest command are marked with green check marks, select the copy icon on the top-right of the Generated command box to copy the generated LightIngest command.

   

   Note

   If required, you can download the LightIngest tool by selecting Download LightIngest.

2. To complete the ingestion process, you must run LightIngest using the copied command.

Step 1: Prepare for repartitioning

1. Adjust the retention policy to allow for old data. In the following example, you set retention policy for table MyTable to 10 years.

   .alter-merge table MyTable policy retention softdelete = 3650d recoverability = enabled
   

2. Adjust the caching policy so that all the data is in hot cache for the repartitioning, as only hot data can be repartitioned post ingestion. In the following example, you set the caching for table MyTable to 10 years.

   .alter table MyTable policy caching hot = 3650d
   

   Important

   Increasing the caching policy may use considerably more hot cache than in normal operations and may result in increased cost.

Step 2: Initiate repartitioning

1. Create a partitioning policy that partitions the data by the column named Timestamp. In the following example, you set the partitioning policy for table MyTable to partition by the column named Timestamp.

   .alter table MyTable policy partitioning
   ```
   {
     "EffectiveDateTime" : "1970-01-01T00:00:00",
     "PartitionKeys": [
       {
         "ColumnName": "Timestamp",
         "Kind": "UniformRange",
         "Properties": {
           "Reference": "1970-01-01T00:00:00",
           "RangeSize": "1.00:00:00",
           "OverrideCreationTime": true
         }
       }
     ]
   }
   ```
   

   For information about the partitioning policy properties, see partition properties. For historical ingestion, how you set the following properties is important:

   • The EffectiveDateTime property must be set to a date earlier than the start of the ingestion to trigger the repartitioning.
   • The RangeSize is set to one day so that the data is repartitioned into buckets of one day. However, you should set this value to align with your data. For example, if you have less than several GBs of data per day, consider setting a larger value.
   • The OverrideCreationTime must be set to true so that after repartitioning the data into day buckets, the extents are marked with that day as the creation time.

2. Set a merge policy to allow merging of all extents, including extents older than 14 days. Setting this policy is important because the repartitioning process creates extents older than 14 days, which by default are excluded by the merge process.

   .alter table MyTable policy merge
   ```
   {
     "Lookback": {
       "Kind": "HotCache"
     }
   }
   ```
   

3. Monitor the repartitioning progress using the .show database extents partitioning statistics command. In the returned results, look for the table you're repartitioning and monitor the PartitionedRowPercentage column. When the PartitionedRowPercentage column's value is 100, the repartitioning is complete.

Step 3: Clean up post repartitioning

Once the repartitioning is complete, you can clean up the policies you set in the previous steps.

1. Remove the partitioning policy.

   .delete table MyTable policy partitioning
   

2. Remove the merge policy.

   .delete table MyTable policy merge
   

3. Remove or set the caching policy.

   // Remove the caching policy
   .delete table MyTable policy caching
   // OR set the caching policy to your desired value
   .alter table MyTable policy caching hot = 90d
   

4. Remove or set the retention policy.

   // Remove the retention policy
   .delete table MyTable policy retention
   // OR set the retention policy to your desired value
   .alter-merge table MyTable policy retention softdelete = 30d recoverability = enabled
   

Related content

• Query data in Azure Data Explorer