Queued ingestion commands use case (preview)

Applies to: ✅ Azure Data Explorer

The queued ingestion commands allow you to ingest individual blobs by URL or ingest batches of data by listing folders or containers. This article walks through a common use case: fine-tuning the ingestion of historical data. You can use these commands to test how historical data is ingested and resolve any issues before performing full ingestion. The following tasks demonstrate how to use queued ingestion commands effectively:

• Ingest single blobs
• List blobs in a folder
• Ingest folder
• Track ingestion status
• Filter queued files for ingestion
• Capture the creation time
• Ingest 20 files
• Track follow up ingestion status
• Perform your full ingestion
• Cancel ingestion

Ingest single blobs

You can start by ingesting a single blob directly using its URL. Make sure to include a SAS token or use a managed identity to grant the service permission to access and download the blob.

.ingest-from-storage-queued into table database('TestDatabase').Logs
EnableTracking=true
with (format='csv')
<|
'https://sample.blob.core.chinacloudapi.cn/sample/test_*csv?...'

Output

List blobs in a folder

To understand the historical data better, you list a maximum of 10 blobs from the Azure blob storage container.

.list blobs (
    "https://<BlobStorageLocation>/<FolderName>;managed_identity=system"
)
MaxFiles=10

Output

You can now verify if the blobs are the correct blobs to ingest.

Ingest folder

Next you queue 10 parquet files for ingestion into the Logs table in the TestDatabase database with tracking enabled for the ingestion.

.ingest-from-storage-queued into table database('TestDatabase').Logs
EnableTracking=true
with (format='parquet')
<|
    .list blobs (
        "https://<BlobStorageLocation>/<FolderName>;managed_identity=system"
    )
    MaxFiles=10

Output

The OperationInfo, which includes the IngestionOperationId, is then used to track the ingestion status.

Track ingestion status

You run the .show queued ingestion operations command to check whether the ingestion is complete or if there are any errors.

.show queued ingestion operations "00001111;11112222;00001111-aaaa-2222-bbbb-3333cccc4444"

Output

If the State isn't Completed, you can run the .show queued ingestion operations again. Running it again allows you to monitor the increase in the number of ingested blobs until the State changes to Completed. You can also cancel the ingestion, if necessary.

Filter queued files for ingestion

After the results of the ingestion are examined, another attempt at listing blobs for ingestion is made. This time the parquet suffix is added to ensure that only parquet files are ingested.

.list blobs (
    "https://<BlobStorageLocation>/<FolderName>;managed_identity=system"
)
Suffix="parquet"
MaxFiles=10

Output

Capture the creation time

A path format is added to capture the creation time.

.list blobs (
    "https://<BlobStorageLocation>/<FolderName>;managed_identity=system"
)
Suffix="parquet"
MaxFiles=10
PathFormat=("output/03/Year=" datetime_pattern("yyyy'/Month='MM'/Day='dd", creationTime) "/")

Output

The CapturedVariables column dates match the dates specified in the BlobUri column.

Ingest 20 files

Now 20 files in parquet format are ingested from the Azure blob storage container, along with their creation time.

.ingest-from-storage-queued into table database('TestDatabase').Logs
EnableTracking=true
with (format='parquet')
<|
    .list blobs (
        "https://<BlobStorageLocation>/<FolderName>;managed_identity=system"
    )
    Suffix="parquet"
    MaxFiles=20
    PathFormat=("output/03/Year=" datetime_pattern("yyyy'/Month='MM'/Day='dd", creationTime) "/")

Output

The OperationInfo is then used to track the ingestion status.

Track follow up ingestion status

The .show queued ingestion operations command is run to check whether there are any issues with this ingestion.

.show queued ingestion operations "22223333;22223333;11110000-bbbb-2222-cccc-4444dddd5555"

Output

The .show extents command is run to check whether extents are created with an anterior date for data integrity and historical accuracy.

.show table Logs extents

The MinCreatedOn and MaxCreatedOn values should show the data creation time, rather than the data ingestion time. For more information about these returns, see .show extents.

You can cancel the ingestion, if necessary.

Perform your full ingestion

By running the queued ingestion commands on a sample, you discovered the problems your ingestion might encounter. Now that you fixed them, you're ready to ingest all your historical data and wait for the full ingestion to complete.

Cancel ingestion

At any time during the ingestion process, you can cancel your queued ingestion.

.cancel queued ingestion operation '22223333;22223333;11110000-bbbb-2222-cccc-4444dddd5555'

Output

You can then roll back the ingestion, fix the issues, and rerun the ingestion.

Related content

• Queued ingestion overview
• Data formats supported for ingestion
• .show extents