使用 Azure 数据资源管理器查询从 Azure Monitor 中导出的数据（预览版）Query exported data from Azure Monitor using Azure Data Explorer (preview)

• 11/10/2020
• • J

将数据从 Azure Monitor 导出到 Azure 存储帐户可以实现低成本保留，并能够将日志重新分配到不同的区域。Exporting data from Azure Monitor to an Azure storage account enables low-cost retention and the ability to reallocate logs to different regions. 使用 Azure 数据资源管理器可查询从 Log Analytics 工作区导出的数据。Use Azure Data Explorer to query data that was exported from your Log Analytics workspaces. 配置后，从你的工作区发送到 Azure 存储帐户的受支持的表将可用作 Azure 数据资源管理器的数据源。Once configured, supported tables that are sent from your workspaces to an Azure storage account will be available as a data source for Azure Data Explorer.

处理流如下：The process flow is as follows:

1. 将数据从 Log Analytics 工作区导出到 Azure 存储帐户。Export data from Log Analytics workspace to Azure storage account.
2. 在 Azure 数据资源管理器群集中创建外部表和数据类型映射。Create external table in your Azure Data Explorer Cluster and mapping for the data types.
3. 从 Azure 数据资源管理器查询数据。Query data from Azure Data Explorer.

将数据发送到 Azure 存储Send data to Azure storage

可以使用以下任一选项将 Azure Monitor 日志导出到 Azure 存储帐户。Azure Monitor logs can be exported to an Azure Storage Account using any of the following options.

• 使用逻辑应用从日志查询进行计划性导出。Scheduled export from a log query using a Logic App. 这类似于数据导出功能，但你可向 Azure 存储发送经过筛选或聚合的数据。This is similar to the data export feature but allows you to send filtered or aggregated data to Azure storage. 不过，此方法受限于日志查询限制。请参阅使用逻辑应用将 Log Analytics 工作区中的数据存档到 Azure 存储。This method though is subject to log query limits See Archive data from Log Analytics workspace to Azure storage using Logic App.
• 使用逻辑应用一次性导出。One time export using a Logic App. 请参阅适用于逻辑应用和 Power Automate 的 Azure Monitor 日志连接器。See Azure Monitor Logs connector for Logic Apps and Power Automate.
• 使用 PowerShell 脚本一次性导出到本地计算机。One time export to local machine using PowerShell script. 请参阅 Invoke-AzOperationalInsightsQueryExport。See Invoke-AzOperationalInsightsQueryExport.

创建位于 Azure Blob 存储中的外部表Create an external table located in Azure blob storage

使用外部表将 Azure 数据资源管理器链接到 Azure 存储帐户。Use external tables to link Azure Data Explorer to an Azure storage account. 外部表是引用存储在 Kusto 数据库外部的数据的 Kusto 架构实体。An external table is a Kusto schema entity that references data stored outside a Kusto database. 与表一样，外部表具有定义明确的架构。Like tables, an external table has a well-defined schema. 与表不同的是，数据是在 Kusto 群集外部进行存储和管理的。Unlike tables, data is stored and managed outside of a Kusto cluster. 从之前部分导出的数据保存在 JSON 行中。The exported data from the previous section is saved in JSON lines.

若要创建引用，你需要导出表的架构。To create a reference, you require the schema of the exported table. 使用 Log Analytics 中的 getschema 运算符来检索此信息，其中包括表的列及其数据类型。Use the getschema operator from Log Analytics to retrieve this information which includes the table's columns and their data types.

现在可以使用输出创建 Kusto 查询以构建外部表。You can now use the output to create the Kusto query for building the external table. 按照在 Azure 存储或 Azure Data Lake 中创建和更改外部表中的指导，以 JSON 格式创建外部表，然后从 Azure 数据资源管理器数据库运行该查询。Following the guidance in Create and alter external tables in Azure Storage or Azure Data Lake, create an external table in a JSON format and then run the query from your Azure Data Explorer database.

下面的 PowerShell 脚本将为表和映射创建 create 命令。The following PowerShell script will create the create commands for the table and the mapping.

PARAM(
    $resourcegroupname, #The name of the Azure resource group
    $TableName, # The log lanlyics table you wish to convert to external table
    $MapName, # The name of the map
    $subscriptionId, #The ID of the subscription
    $WorkspaceId, # The log lanlyics WorkspaceId
    $WorkspaceName, # The log lanlyics workspace name
    $BlobURL, # The Blob URL where to save
    $ContainerAccessKey, # The blob container Access Key (Option to add a SAS url)
    $ExternalTableName = $null # The External Table name, null to use the same name
)

if($null -eq $ExternalTableName) {
    $ExternalTableName = $TableName
}

$query = $TableName + ' | getschema | project ColumnName, DataType'

$output = (Invoke-AzOperationalInsightsQuery -WorkspaceId $WorkspaceId -Query $query).Results

$FirstCommand = @()
$SecondCommand = @()

foreach ($record in $output) {
    if ($record.DataType -eq 'System.DateTime') {
        $dataType = 'datetime'
    } else {
        $dataType = 'string'
    }
    $FirstCommand += $record.ColumnName + ":" + "$dataType" + ","
    $SecondCommand += "{`"column`":" + "`"" + $record.ColumnName + "`"," + "`"datatype`":`"$dataType`",`"path`":`"$." + $record.ColumnName + "`"},"
}
$schema = ($FirstCommand -join '') -replace ',$'
$mapping = ($SecondCommand -join '') -replace ',$'

$CreateExternal = @'
.create external table {0} ({1})
kind=blob
partition by (TimeGeneratedPartition:datetime = bin(TimeGenerated, 1min))
pathformat = (datetime_pattern("'y='yyyy'/m='MM'/d='dd'/h='HH'/m='mm", TimeGeneratedPartition))
dataformat=multijson
(
   h@'{2}/WorkspaceResourceId=/subscriptions/{4}/resourcegroups/{6}/providers/microsoft.operationalinsights/workspaces/{5};{3}'
)
with
(
   docstring = "Docs",
   folder = "ExternalTables"
)
'@ -f $TableName, $schema, $BlobURL, $ContainerAccessKey, $subscriptionId, $WorkspaceName, $resourcegroupname,$WorkspaceId

$createMapping = @'
.create external table {0} json mapping "{1}" '[{2}]'
'@ -f $ExternalTableName, $MapName, $mapping

Write-Host -ForegroundColor Red 'Copy and run the following commands (one by one), on your Azure Data Explorer cluster query window to create the external table and mappings:'
write-host -ForegroundColor Green $CreateExternal
Write-Host -ForegroundColor Green $createMapping

下图显示了输出示例。The following image shows and example of the output.

从 Azure 数据资源管理器查询导出的数据Query the exported data from Azure Data Explorer

配置映射后，可以通过 Azure 数据资源管理器查询导出的数据。After configuring the mapping, you can query the exported data from Azure Data Explorer. 查询需要 external_table 函数，如以下示例中所示。Your query requires the external_table function such as in the following example.

external_table("HBTest","map") | take 10000

后续步骤Next steps

• 了解如何在 Azure 数据资源管理器中编写查询Learn to write queries in Azure Data Explorer