Exporting Data from Azure Data Explorer to Azure Blob Storage

Azure Data Explorer (ADX) is a fast, fully managed data analytics service for real-time analysis on large volumes of data. In certain scenarios, you may need to export data from ADX to other storage solutions, such as Azure Blob Storage. This guide will walk you through the steps to export ADX data to Azure Blob Storage using Kusto Query Language (KQL).

Prerequisites

Before you start, ensure you have the following:

1. An Azure Data Explorer cluster and database.
2. An Azure Blob Storage account with a container where the data will be exported.
3. Appropriate permissions to create external tables and continuous export jobs in ADX.

Step-by-Step Guide

Step 1: Create an External Table

The first step is to create an external table in ADX. An external table maps ADX data to a storage location in Azure Blob Storage. External tables support several formats, including CSV, TSV, JSON, and Parquet. In this example, we will use the CSV format.

Here’s how to create an external table:

.create external table Externalscriptdatabasepoc (PA: long) 
kind=storage  
dataformat=csv   
( 
h@'Sas url path' 
)

After creating the external table, you can query it to ensure it was created successfully:

external_table('Externalscriptdatabasepoc') | take 100

Step 2: Define the Continuous Export Job

Next, define a continuous export job. This job will continuously export data from an ADX table to the external table. Continuous export jobs are useful for maintaining up-to-date copies of your data in other storage systems.

Define the continuous export job with the following command:

.create-or-alter continuous-export MyPocExport
    over (T) 
    to table Externalscriptdatabasepoc6 
    with 
    (intervalBetweenRuns=1m,  
     forcedLatency=2m,  
     sizeLimit=104857600)
<| T

In this example:

• MyPocExport is the name of the continuous export job.
• T is the ADX table from which data is being exported.
• The export job runs every minute (intervalBetweenRuns=1m).
• There is a forced latency of 2 minutes (forcedLatency=2m).
• The size limit for each export is 100 MB (sizeLimit=104857600).

Step 3: Export Historical Data

If you need to export historical data, you can use the StartCursor to specify the point in time from which you want to start exporting data.

To get the StartCursor, run the following command:

.show continuous-export MyPocExport | project StartCursor

Step 4: Perform the Export

Use the StartCursor obtained in the previous step to perform the export of historical data:

.export async to table Externalscriptdatabasepoc 
<| T | where cursor_before_or_at(638512712990760025)