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

将数据从 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 数据资源管理器导出的数据查询流。

将数据发送到 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.

提示

可以使用现有的 Azure 数据资源管理器群集,或使用所需的配置创建新的专用群集。You can use an existing Azure Data Explorer cluster or creating a new dedicated cluster with the needed configurations.

创建位于 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.

Log Analytics 表架构。

现在可以使用输出创建 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.

备注

外部表的创建由两个进程构建。The external table creation is built from two processes. 第一个是创建外部表,第二个是创建映射。The first is creating the external table, while the second is creating the mapping.

下面的 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.

ExternalTable 创建命令输出。

示例输出Example output

提示

复制、粘贴,然后在 Azure 数据资源管理器客户端工具中运行脚本的输出,以创建表和映射。Copy, paste, and then run the output of the script in your Azure Data Explorer client tool to create the table and mapping.

从 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

查询 Log Analytics 导出的数据Query Log Analytics exported data

后续步骤Next steps