演练:使用流分析从 Application Insights 导出到 SQLWalkthrough: Export to SQL from Application Insights using Stream Analytics

本文说明如何使用连续导出Azure 流分析,将遥测数据从 Azure Application Insights 移入 Azure SQL 数据库。This article shows how to move your telemetry data from Azure Application Insights into Azure SQL Database by using Continuous Export and Azure Stream Analytics.

连续导出以 JSON 格式将遥测数据移入 Azure 存储。Continuous export moves your telemetry data into Azure Storage in JSON format. 我们将使用 Azure 流分析来分析 JSON 对象,并在数据库表中创建行。We'll parse the JSON objects using Azure Stream Analytics and create rows in a database table.

(一般而言,连续导出是对应用发送到 Application Insights 的遥测数据自行进行分析的方式。(More generally, Continuous Export is the way to do your own analysis of the telemetry your apps send to Application Insights. 可以改写此代码示例,以使用导出的遥测数据执行其他操作,例如聚合数据)。You could adapt this code sample to do other things with the exported telemetry, such as aggregation of data.)

首先,假设读者已有一个想要监视的应用。We'll start with the assumption that you already have the app you want to monitor.

本示例将使用页面视图数据,但可以轻松地将此模式沿用到其他数据类型,例如自定义事件和异常。In this example, we will be using the page view data, but the same pattern can easily be extended to other data types such as custom events and exceptions.

将 Application Insights 添加到应用程序Add Application Insights to your application

开始操作:To get started:

  1. 为网页设置 Application InsightsSet up Application Insights for your web pages.

    (本示例侧重于处理来自客户端浏览器的页面视图数据,但你也可以针对 JavaASP.NET 应用的服务器端设置 Application Insights,并处理请求、依赖项和其他服务器遥测数据。)(In this example, we'll focus on processing page view data from the client browsers, but you could also set up Application Insights for the server side of your Java or ASP.NET app and process request, dependency and other server telemetry.)

  2. 发布应用,并观察 Application Insights 资源中出现的遥测数据。Publish your app, and watch telemetry data appearing in your Application Insights resource.

在 Azure 中创建存储Create storage in Azure

连续导出始终将数据输出到 Azure 存储帐户,因此首先需要创建存储。Continuous export always outputs data to an Azure Storage account, so you need to create the storage first.

  1. Azure 门户上的订阅中创建存储帐户。Create a storage account in your subscription in the Azure portal.

    在 Azure 门户中,依次选择“添加”、“数据”、“存储”。

  2. 创建容器Create a container

    在新存储中选择“容器”,单击“容器”磁贴,并单击“添加”

  3. 复制存储访问密钥Copy the storage access key

    稍后需要使用它来设置流分析服务的输入。You'll need it soon to set up the input to the stream analytics service.

    在存储中,依次打开“设置”、“密钥”,并复制主访问密钥

开始向 Azure 存储连续导出Start continuous export to Azure storage

  1. 在 Azure 门户中,浏览到为应用程序创建的 Application Insights 资源。In the Azure portal, browse to the Application Insights resource you created for your application.

    依次选择“浏览”、“Application Insights”、应用程序

  2. 创建连续导出。Create a continuous export.

    依次选择“设置”、“连续导出”、“添加”

    选择前面创建的存储帐户:Select the storage account you created earlier:

    设置导出目标

    设置想要查看的事件类型:Set the event types you want to see:

    选择事件类型

  3. 让我们累积一些数据。Let some data accumulate. 请休息一下,让其他人先使用该应用程序一段时间。Sit back and let people use your application for a while. 应用程序中会逐渐传入遥测数据,指标资源管理器中会显示统计图表,诊断搜索中会显示各个事件。Telemetry will come in and you'll see statistical charts in metric explorer and individual events in diagnostic search.

    此外,数据将导出到存储。And also, the data will export to your storage.

  4. 在门户中检查导出的数据 - 选择“浏览”,选择存储帐户,然后选择“容器”;也可以在 Visual Studio 中检查。Inspect the exported data, either in the portal - choose Browse, select your storage account, and then Containers - or in Visual Studio. 在 Visual Studio 中,请选择“查看”>“Cloud Explorer”,并打开“Azure”>“存储”。In Visual Studio, choose View / Cloud Explorer, and open Azure / Storage. (如果没有此菜单选项,则需要安装 Azure SDK:打开“新建项目”对话框,打开 Visual C#/云/获取 Azure SDK for .NET。)(If you don't have this menu option, you need to install the Azure SDK: Open the New Project dialog and open Visual C# / Cloud / Get Azure SDK for .NET.)

    在 Visual Studio 中,依次打开“Server Browser”、“Azure”、“存储”

    记下派生自应用程序名称和检测密钥的路径名称的共同部分。Make a note of the common part of the path name, which is derived from the application name and instrumentation key.

事件以 JSON 格式写入 Blob 文件。The events are written to blob files in JSON format. 每个文件可能包含一个或多个事件。Each file may contain one or more events. 因此我们想要读取事件数据,并筛选出所需的字段。So we'd like to read the event data and filter out the fields we want. 可以针对数据执行各种操作,但我们目前的计划是使用流分析将数据移到 SQL 数据库。There are all kinds of things we could do with the data, but our plan today is to use Stream Analytics to move the data to SQL Database. 这样做可以轻松运行许多微妙的查询。That will make it easy to run lots of interesting queries.

创建 Azure SQL 数据库Create an Azure SQL Database

再次从 Azure 门户中的订阅开始,创建要向其写入数据的数据库以及一个新服务器(除非已有一个)。Once again starting from your subscription in Azure portal, create the database (and a new server, unless you've already got one) to which you'll write the data.

依次选择“新建”、“数据”、“SQL”

确保服务器允许访问 Azure 服务:Make sure that the server allows access to Azure services:

依次选择“浏览”、“服务器”、服务器、“设置”、“防火墙”、“允许访问 Azure”

在 Azure SQL 数据库中创建表Create a table in Azure SQL Database

使用偏好的管理工具连接到在上一部分中创建的数据库。Connect to the database created in the previous section with your preferred management tool. 本演练将使用 SQL Server 管理工具 (SSMS)。In this walkthrough, we will be using SQL Server Management Tools (SSMS).

连接到 Azure SQL 数据库

创建新查询,并执行以下 T-SQL:Create a new query, and execute the following T-SQL:


CREATE TABLE [dbo].[PageViewsTable](
    [pageName] [nvarchar](max) NOT NULL,
    [viewCount] [int] NOT NULL,
    [url] [nvarchar](max) NULL,
    [urlDataPort] [int] NULL,
    [urlDataprotocol] [nvarchar](50) NULL,
    [urlDataHost] [nvarchar](50) NULL,
    [urlDataBase] [nvarchar](50) NULL,
    [urlDataHashTag] [nvarchar](max) NULL,
    [eventTime] [datetime] NOT NULL,
    [isSynthetic] [nvarchar](50) NULL,
    [deviceId] [nvarchar](50) NULL,
    [deviceType] [nvarchar](50) NULL,
    [os] [nvarchar](50) NULL,
    [osVersion] [nvarchar](50) NULL,
    [locale] [nvarchar](50) NULL,
    [userAgent] [nvarchar](max) NULL,
    [browser] [nvarchar](50) NULL,
    [browserVersion] [nvarchar](50) NULL,
    [screenResolution] [nvarchar](50) NULL,
    [sessionId] [nvarchar](max) NULL,
    [sessionIsFirst] [nvarchar](50) NULL,
    [clientIp] [nvarchar](50) NULL,
    [continent] [nvarchar](50) NULL,
    [country] [nvarchar](50) NULL,
    [province] [nvarchar](50) NULL,
    [city] [nvarchar](50) NULL
)

CREATE CLUSTERED INDEX [pvTblIdx] ON [dbo].[PageViewsTable]
(
    [eventTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

创建 PageViewsTable

本示例使用页面视图中的数据。In this sample, we are using data from page views. 若要查看其他可用的数据,请检查 JSON 输出,并查看导出数据模型To see the other data available, inspect your JSON output, and see the export data model.

创建 Azure 流分析实例Create an Azure Stream Analytics instance

Azure 门户中,选择 Azure 流分析服务,并创建新的流分析作业:From the Azure portal, select the Azure Stream Analytics service, and create a new Stream Analytics job:

屏幕截图显示了流分析作业页面,突出显示了“创建”按钮。

新建流分析作业

创建新作业后,选择“转到资源”。When the new job is created, select Go to resource.

屏幕截图显示了“部署成功”消息和“转到资源”按钮。

添加新输入Add a new input

屏幕截图显示了“输入”页面,其中“添加”按钮处于选中状态。

将此位置设置为从连续导出 Blob 接收输入:Set it to take input from your Continuous Export blob:

屏幕截图显示了“新建输入”窗口,其中已选择“输入别名”、“源”和“存储帐户”下拉菜单选项。

现在需要使用存储帐户的主访问密钥(前面已记下此密钥)。Now you'll need the Primary Access Key from your Storage Account, which you noted earlier. 将此密钥设置为存储帐户密钥。Set this as the Storage Account Key.

设置路径前缀模式Set path prefix pattern

请务必将“日期格式”设置为 YYYY-MM-DD(包含短划线)。Be sure to set the Date Format to YYYY-MM-DD (with dashes).

“路径前缀模式”指定流分析在存储中查找输入文件的方式。The Path Prefix Pattern specifies how Stream Analytics finds the input files in the storage. 需要将它设置为与连续导出存储数据的方式相对应。You need to set it to correspond to how Continuous Export stores the data. 设置如下:Set it like this:

webapplication27_12345678123412341234123456789abcdef0/PageViews/{date}/{time}

在本示例中:In this example:

  • webapplication27 是 Application Insights 资源的名称,全部小写webapplication27 is the name of the Application Insights resource, all in lower case.
  • 1234... 是 Application Insights 资源的检测密钥,但 删除了短划线1234... is the instrumentation key of the Application Insights resource with dashes removed.
  • PageViews 是要分析的数据类型。PageViews is the type of data we want to analyze. 可用的类型取决于在连续导出中设置的筛选器。The available types depend on the filter you set in Continuous Export. 检查导出的数据以查看其他可用类型,并查看导出数据模型Examine the exported data to see the other available types, and see the export data model.
  • /{date}/{time} 是以文本形式写入的模式。/{date}/{time} is a pattern written literally.

若要获取 Application Insights 资源的名称和 iKey,请在资源的概述页中打开“概要”,或打开“设置”。To get the name and iKey of your Application Insights resource, open Essentials on its overview page, or open Settings.

提示

使用示例函数检查是否已正确设置输入路径。Use the Sample function to check that you have set the input path correctly. 如果检查失败:请检查在所选的示例时间范围内,存储中是否有数据。If it fails: Check that there is data in the storage for the sample time range you chose. 编辑输入定义,检查是否已正确设置存储帐户、路径前缀和日期格式。Edit the input definition and check you set the storage account, path prefix and date format correctly.

设置查询Set query

打开查询部分:Open the query section:

将默认查询替换为以下内容:Replace the default query with:


    SELECT flat.ArrayValue.name as pageName
    , flat.ArrayValue.count as viewCount
    , flat.ArrayValue.url as url
    , flat.ArrayValue.urlData.port as urlDataPort
    , flat.ArrayValue.urlData.protocol as urlDataprotocol
    , flat.ArrayValue.urlData.host as urlDataHost
    , flat.ArrayValue.urlData.base as urlDataBase
    , flat.ArrayValue.urlData.hashTag as urlDataHashTag
      ,A.context.data.eventTime as eventTime
      ,A.context.data.isSynthetic as isSynthetic
      ,A.context.device.id as deviceId
      ,A.context.device.type as deviceType
      ,A.context.device.os as os
      ,A.context.device.osVersion as osVersion
      ,A.context.device.locale as locale
      ,A.context.device.userAgent as userAgent
      ,A.context.device.browser as browser
      ,A.context.device.browserVersion as browserVersion
      ,A.context.device.screenResolution.value as screenResolution
      ,A.context.session.id as sessionId
      ,A.context.session.isFirst as sessionIsFirst
      ,A.context.location.clientip as clientIp
      ,A.context.location.continent as continent
      ,A.context.location.country as country
      ,A.context.location.province as province
      ,A.context.location.city as city
    INTO
      AIOutput
    FROM AIinput A
    CROSS APPLY GetElements(A.[view]) as flat


请注意,前几个属性是特定于页面视图数据的属性。Notice that the first few properties are specific to page view data. 其他遥测数据类型的导出具有不同的属性。Exports of other telemetry types will have different properties. 请参阅属性类型和值的详细数据模型参考。See the detailed data model reference for the property types and values.

设置数据库的输出Set up output to database

选择“SQL”作为输出。Select SQL as the output.

在流分析中选择“输出”

指定数据库。Specify the database.

填写数据库的详细信息

关闭向导,等待通知输出已设置。Close the wizard and wait for a notification that the output has been set up.

开始处理Start processing

从操作栏启动作业:Start the job from the action bar:

在流分析中单击“开始”

可以选择是要处理从当前时间开始传入的数据,还是处理以前的数据。You can choose whether to start processing the data starting from now, or to start with earlier data. 如果连续导出已运行了一段时间,则后一种做法将很有效果。The latter is useful if you have had Continuous Export already running for a while.

几分钟后,可返回 SQL Server 管理工具监视流入的数据。After a few minutes, go back to SQL Server Management Tools and watch the data flowing in. 例如,使用如下所示的查询:For example, use a query like this:

SELECT TOP 100 *
FROM [dbo].[PageViewsTable]