教程:使用 SQL Server 数据库存储边缘中的数据Tutorial: Store data at the edge with SQL Server databases

部署 SQL Server 模块,以便在运行 Azure IoT Edge 的 Linux 设备上存储数据。Deploy a SQL Server module to store data on a Linux device running Azure IoT Edge.

使用 Azure IoT Edge 和 SQL Server 在边缘存储和查询数据。Use Azure IoT Edge and SQL Server to store and query data at the edge. Azure IoT Edge 有基本的存储功能,可以在设备脱机的情况下缓存消息,在重新建立连接后再转发这些消息。Azure IoT Edge has basic storage capabilities to cache messages if a device goes offline, and then forward them when the connection is reestablished. 不过,你可能需要更高级的存储功能,例如在本地查询数据的功能。However, you may want more advanced storage capabilities, like being able to query data locally. IoT Edge 设备可以使用本地数据库来执行更复杂的计算,而不需要与 IoT 中心保持连接。Your IoT Edge devices can use local databases to perform more complex computing without having to maintain a connection to IoT Hub.

本文提供有关将 SQL Server 数据库部署到 IoT Edge 设备的说明。This article provides instructions for deploying a SQL Server database to an IoT Edge device. 在 IoT Edge 设备上运行的 Azure Functions 可将传入的数据结构化,然后将其发送到数据库。Azure Functions, running on the IoT Edge device, structures the incoming data then sends it to the database. 本文中的步骤也适用于在容器中运行的其他数据库,例如 MySQL 或 PostgreSQL。The steps in this article can also be applied to other databases that work in containers, like MySQL or PostgreSQL.

本教程介绍如何执行下列操作:In this tutorial, you learn how to:

  • 使用 Visual Studio Code 创建 Azure FunctionsUse Visual Studio Code to create an Azure Function
  • 将 SQL 数据库部署到 IoT Edge 设备Deploy a SQL database to your IoT Edge device
  • 使用 Visual Studio Code 生成模块并将其部署到 IoT Edge 设备Use Visual Studio Code to build modules and deploy them to your IoT Edge device
  • 查看生成的数据View generated data

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

先决条件Prerequisites

在开始学习本教程之前,应已完成上一篇教程来设置用于开发 Linux 容器的开发环境:开发适用于 Linux 设备的 IoT Edge 模块Before beginning this tutorial, you should have gone through the previous tutorial to set up your development environment for Linux container development: Develop IoT Edge modules for Linux devices. 完成该教程后,已应准备好以下必备组件:By completing that tutorial, you should have the following prerequisites in place:

本教程使用 Azure Functions 模块将数据发送到 SQL Server。This tutorial uses an Azure Functions module to send data to the SQL Server. 若要通过 Azure Functions 开发 IoT Edge 模块,请在开发计算机上安装下述额外的必备组件:To develop an IoT Edge module with Azure Functions, install the following additional prerequisites on your development machine:

创建函数项目Create a function project

若要将数据发送到数据库中,需要通过一个模块将数据进行适当的结构化并存储在表中。To send data into a database, you need a module that can structure the data properly and then stores it in a table.

创建新项目Create a new project

以下步骤将介绍如何使用 Visual Studio Code 和 Azure IoT 工具来创建 IoT Edge 函数。The following steps show you how to create an IoT Edge function using Visual Studio Code and the Azure IoT Tools.

  1. 打开 Visual Studio Code。Open Visual Studio Code.

  2. 打开 VS Code 命令面板,方法是选择“视图” > “命令面板”。Open the VS Code command palette by selecting View > Command palette.

  3. 在命令面板中,键入并运行“Azure IoT Edge: New IoT Edge solution”命令。In the command palette, type and run the command Azure IoT Edge: New IoT Edge solution. 在命令面板中提供以下信息,以便创建解决方案:In the command palette, provide the following information to create your solution:

    字段Field Value
    选择文件夹Select folder 在适用于 VS Code 的开发计算机上选择用于创建解决方案文件的位置。Choose the location on your development machine for VS Code to create the solution files.
    提供解决方案名称Provide a solution name 输入解决方案的描述性名称(例如 SqlSolution),或者接受默认名称。Enter a descriptive name for your solution, like SqlSolution, or accept the default.
    选择模块模板Select module template 选择“Azure Functions - C#”。 Choose Azure Functions - C#.
    提供模块名称Provide a module name 将模块命名为 sqlFunctionName your module sqlFunction.
    为模块提供 Docker 映像存储库Provide Docker image repository for the module 映像存储库包含容器注册表的名称和容器映像的名称。An image repository includes the name of your container registry and the name of your container image. 容器映像是在上一步预先填充的。Your container image is prepopulated from the last step. localhost:5000 替换为 Azure 容器注册表中的登录服务器值。Replace localhost:5000 with the login server value from your Azure container registry. 可以在 Azure 门户的容器注册表的“概览”页中检索登录服务器。You can retrieve the login server from the Overview page of your container registry in the Azure portal.

    最终的字符串看起来类似于 <注册表名称>.azurecr.cn/sqlfunction。The final string looks like <registry name>.azurecr.cn/sqlfunction.

    VS Code 窗口将加载你的 IoT Edge 解决方案空间。The VS Code window loads your IoT Edge solution workspace.

添加注册表凭据Add your registry credentials

环境文件存储容器注册表的凭据,并将其与 IoT Edge 运行时共享。The environment file stores the credentials for your container registry and shares them with the IoT Edge runtime. 此运行时需要这些凭据才能将专用映像拉取到 IoT Edge 设备中。The runtime needs these credentials to pull your private images onto the IoT Edge device.

  1. 在 VS Code 资源管理器中,打开 .env 文件。In the VS Code explorer, open the .env file.
  2. 使用从 Azure 容器注册表复制的 usernamepassword 值更新相关字段。Update the fields with the username and password values that you copied from your Azure container registry.
  3. 保存此文件。Save this file.

选择目标体系结构Select your target architecture

目前,Visual Studio Code 可以开发适用于 Linux AMD64 和 Linux ARM32v7 设备的 C 模块。Currently, Visual Studio Code can develop C modules for Linux AMD64 and Linux ARM32v7 devices. 需要选择面向每个解决方案的体系结构,因为每种体系结构类型的容器的生成和运行方式均不相同。You need to select which architecture you're targeting with each solution, because the container is built and run differently for each architecture type. 默认设置为 Linux AMD64。The default is Linux AMD64.

  1. 打开命令面板并搜索 Azure IoT Edge:Set Default Target Platform for Edge Solution,或者选择窗口底部边栏中的快捷方式图标。Open the command palette and search for Azure IoT Edge: Set Default Target Platform for Edge Solution, or select the shortcut icon in the side bar at the bottom of the window.

  2. 在命令面板中,从选项列表中选择目标体系结构。In the command palette, select the target architecture from the list of options. 本教程将使用 Ubuntu 虚拟机作为 IoT Edge 设备,因此将保留默认设置 amd64For this tutorial, we're using an Ubuntu virtual machine as the IoT Edge device, so will keep the default amd64.

使用自定义代码更新模块Update the module with custom code

  1. 在 VS Code 资源管理器中,打开“modules” > “sqlFunction” > “sqlFunction.cs” 。In the VS Code explorer, open modules > sqlFunction > sqlFunction.cs.

  2. 将文件的全部内容替换为以下代码:Replace the entire contents of the file with the following code:

    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Text;
    using System.Threading.Tasks;
    using Microsoft.Azure.Devices.Client;
    using Microsoft.Azure.WebJobs;
    using Microsoft.Azure.WebJobs.Extensions.EdgeHub;
    using Microsoft.Azure.WebJobs.Host;
    using Microsoft.Extensions.Logging;
    using Newtonsoft.Json;
    using Sql = System.Data.SqlClient;
    
    namespace Functions.Samples
    {
        public static class sqlFunction
        {
            [FunctionName("sqlFunction")]
            public static async Task FilterMessageAndSendMessage(
                [EdgeHubTrigger("input1")] Message messageReceived,
                [EdgeHub(OutputName = "output1")] IAsyncCollector<Message> output,
                ILogger logger)
            {
                const int temperatureThreshold = 20;
                byte[] messageBytes = messageReceived.GetBytes();
                var messageString = System.Text.Encoding.UTF8.GetString(messageBytes);
    
                if (!string.IsNullOrEmpty(messageString))
                {
                    logger.LogInformation("Info: Received one non-empty message");
                    // Get the body of the message and deserialize it.
                    var messageBody = JsonConvert.DeserializeObject<MessageBody>(messageString);
    
                    //Store the data in SQL db
                    const string str = "<sql connection string>";
                    using (Sql.SqlConnection conn = new Sql.SqlConnection(str))
                    {
                        conn.Open();
                        var insertMachineTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'machine', " + messageBody.machine.temperature + ");";
                        var insertAmbientTemperature = "INSERT INTO MeasurementsDB.dbo.TemperatureMeasurements VALUES (CONVERT(DATETIME2,'" + messageBody.timeCreated + "', 127), 'ambient', " + messageBody.ambient.temperature + ");"; 
                        using (Sql.SqlCommand cmd = new Sql.SqlCommand(insertMachineTemperature + "\n" + insertAmbientTemperature, conn))
                        {
                            //Execute the command and log the # rows affected.
                            var rows = await cmd.ExecuteNonQueryAsync();
                            logger.LogInformation($"{rows} rows were updated");
                        }
                    }
    
                    if (messageBody != null && messageBody.machine.temperature > temperatureThreshold)
                    {
                        // Send the message to the output as the temperature value is greater than the threashold.
                        var filteredMessage = new Message(messageBytes);
                        // Copy the properties of the original message into the new Message object.
                        foreach (KeyValuePair<string, string> prop in messageReceived.Properties)
                        {filteredMessage.Properties.Add(prop.Key, prop.Value);}
                        // Add a new property to the message to indicate it is an alert.
                        filteredMessage.Properties.Add("MessageType", "Alert");
                        // Send the message.       
                        await output.AddAsync(filteredMessage);
                        logger.LogInformation("Info: Received and transferred a message with temperature above the threshold");
                    }
                }
            }
        }
        //Define the expected schema for the body of incoming messages.
        class MessageBody
        {
            public Machine machine {get; set;}
            public Ambient ambient {get; set;}
            public string timeCreated {get; set;}
        }
        class Machine
        {
            public double temperature {get; set;}
            public double pressure {get; set;}         
        }
        class Ambient
        {
            public double temperature {get; set;}
            public int humidity {get; set;}         
        }
    }
    
  3. 在第 35 行中,将 <sql connection string> 字符串替换为以下字符串。In line 35, replace the string <sql connection string> with the following string. Data Source 属性引用 SQL Server 容器,该容器尚不存在,但在下一部分,我们将使用名称 SQL 来创建该容器。The Data Source property references the SQL Server container, which doesn't exist yet but you will create it with the name SQL in the next section.

    Data Source=tcp:sql,1433;Initial Catalog=MeasurementsDB;User Id=SA;Password=Strong!Passw0rd;TrustServerCertificate=False;Connection Timeout=30;
    
  4. 保存 sqlFunction.cs 文件。Save the sqlFunction.cs file.

  5. 打开 sqlFunction.csproj 文件。Open the sqlFunction.csproj file.

  6. 找到包引用所在的组,并添加一个新组来包含 SqlClient。Find the group of package references, and add a new one to include SqlClient.

    <PackageReference Include="System.Data.SqlClient" Version="4.5.1"/>
    
  7. 保存 sqlFunction.csproj 文件。Save the sqlFunction.csproj file.

添加 SQL Server 容器Add the SQL Server container

部署清单声明将要由 IoT Edge 运行时安装在 IoT Edge 设备上的具体模块。A Deployment manifest declares which modules the IoT Edge runtime will install on your IoT Edge device. 在上一部分提供的代码可生成自定义的函数模块,但是,Azure 市场中已生成并提供了 SQL Server 模块。You provided the code to make a customized Function module in the previous section, but the SQL Server module is already built and available in the Azure Marketplace. 只需要求 IoT Edge 运行时包括它,然后在设备上配置它即可。You just need to tell the IoT Edge runtime to include it, then configure it on your device.

  1. 在 Visual Studio Code 中,选择“视图” > “命令面板”打开命令面板。 In Visual Studio Code, open the command palette by selecting View > Command palette.

  2. 在命令面板中,键入并运行 Azure IoT Edge: Add IoT Edge module”。In the command palette, type and run the command Azure IoT Edge: Add IoT Edge module. 在命令面板中,提供以下信息以添加新模块:In the command palette, provide the following information to add a new module:

    字段Field ValueValue
    选择部署模板文件Select deployment template file 命令面板会突出显示当前解决方案文件夹中的 deployment.template.json 文件。The command palette highlights the deployment.template.json file in your current solution folder. 选择该文件。Select that file.
    选择模块模板Select module template 选择“Azure 市场中的模块”。 Select Module from Azure Marketplace.
  3. 在 Azure IoT Edge 模块市场中,搜索并选择“SQL Server 模块”。 In the Azure IoT Edge module marketplace, search for and select SQL Server Module.

  4. 将模块名称更改为 sql(全小写)。Change the module name to sql, all lowercase. 此名称与 sqlFunction.cs 文件中的连接字符串内声明的容器名称相匹配。This name matches the container name declared in the connection string in the sqlFunction.cs file.

  5. 选择“导入”,将模块添加到解决方案。 Select Import to add the module to your solution.

  6. 在解决方案文件夹中,打开 deployment.template.json 文件。In your solution folder, open the deployment.template.json file.

  7. 找到 modules 节。Find the modules section. 应会看到三个模块。You should see three modules. 模块 SimulatedTemperatureSensor 默认已包含在新解决方案中,并提供与其他模块配合使用的测试数据。The module SimulatedTemperatureSensor is included by default in new solutions, and provides test data to use with your other modules. 模块 sqlFunction 是最初使用新代码创建和更新的模块。The module sqlFunction is the module that you initially created and updated with new code. 最后,模块 sql 是从 Azure 市场导入的。Finally, the module sql was imported from the Azure Marketplace.

    Tip

    在部署清单的环境变量中,为 SQL Server 模块设置了默认密码。The SQL Server module comes with a default password set in the environment variables of the deployment manifest. 每当在生产环境中创建 SQL Server 容器时,都应该更改默认的系统管理员密码Any time that you create a SQL Server container in a production environment, you should change the default system administrator password.

  8. 关闭 deployment.template.json 文件。Close the deployment.template.json file.

生成 IoT Edge 解决方案Build your IoT Edge solution

在前面部分,你创建了一个包含一个模块的解决方案,然后向部署清单模板添加了另一个。In the previous sections, you created a solution with one module, and then added another to the deployment manifest template. SQL Server 模块由 Microsoft 公开托管,但你需要容器化 Functions 模块中的代码。The SQL Server module is hosted publicly by Microsoft, but you need to containerize the code in the Functions module. 在本部分,你将生成解决方案,创建 sqlFunction 模块的容器映像,然后将映像推送到容器注册表。In this section, you build the solution, create container images for the sqlFunction module, and push the image to your container registry.

  1. 在 Visual Studio Code 中选择“视图” > “终端”,打开集成终端 。In Visual Studio Code, open the integrated terminal by selecting View > Terminal.

  2. 在 Visual Studio Code 中登录到你的容器注册表,以便将映像推送到你的注册表。Sign in to your container registry in Visual Studio Code so that you can push your images to your registry. 使用已添加到 .env 文件中的相同 Azure 容器注册表 (ACR) 凭据。Use the same Azure Container Registry (ACR) credentials that you added to the .env file. 在集成终端中输入以下命令:Enter the following command in the integrated terminal:

    docker login -u <ACR username> -p <ACR password> <ACR login server>
    

    此时可能会出现一条安全警告,建议使用 --password-stdin 参数。You might see a security warning recommending the use of the --password-stdin parameter. 虽然本文中未介绍它的用法,但我们建议按照此最佳做法进行操作。While its use is outside the scope of this article, we recommend following this best practice. 有关详细信息,请参阅 docker login 命令参考。For more information, see the docker login command reference.

  3. 在 VS Code 资源管理器中右键单击“deployment.template.json”文件,然后选择“生成并推送 IoT Edge 解决方案”。 In the VS Code explorer, right-click the deployment.template.json file and select Build and Push IoT Edge solution.

告知 Visual Studio Code 生成解决方案时,它首先获取部署模板中的信息,然后在名为 config 的新文件夹中生成 deployment.json 文件。然后,它在集成终端运行两个命令,即 docker builddocker pushWhen you tell Visual Studio Code to build your solution, it first takes the information in the deployment template and generates a deployment.json file in a new folder named config. Then, it runs two commands in the integrated terminal: docker build and docker push. 这两个命令会生成代码,将模块容器化,然后将代码推送到在初始化解决方案时指定的容器注册表。These two commands build your code, containerize the module, and then push the code to the container registry that you specified when you initialized the solution.

可以验证 sqlFunction 模块是否已成功推送到容器注册表。You can verify that the sqlFunction module was successfully pushed to your container registry. 在 Azure 门户中,导航到容器注册表。In the Azure portal, navigate to your container registry. 选择“存储库”并搜索 sqlFunctionSelect repositories and search for sqlFunction. 另外两个模块 SimulatedTemperatureSensor 和 sql 不会推送到容器注册表,因为你已经在 Microsoft 注册表中指向它们的存储库。The other two modules, SimulatedTemperatureSensor and sql, won't be pushed to your container registry because you're already pointing to their repositories in the Microsoft registries.

将解决方案部署到设备Deploy the solution to a device

可以通过 IoT 中心设置设备上的模块,但是也可以通过 Visual Studio Code 访问 IoT 中心和设备。You can set modules on a device through the IoT Hub, but you can also access your IoT Hub and devices through Visual Studio Code. 在此部分,请先设置对 IoT 中心的访问权限,然后使用 VS Code 将解决方案部署到 IoT Edge 设备。In this section, you set up access to your IoT Hub then use VS Code to deploy your solution to your IoT Edge device.

  1. 在 VS Code 资源管理器中,展开“Azure IoT 中心设备”部分。 In the VS Code explorer, expand the Azure IoT Hub Devices section.

  2. 右键单击要将其作为部署目标的设备,然后选择“为单个设备创建部署”。 Right-click on the device that you want to target with your deployment and select Create deployment for single device.

  3. 在文件资源管理器中导航到解决方案中的 config 文件夹,然后选择 deployment.amd64In the file explorer, navigate to the config folder inside your solution and choose deployment.amd64. 单击“选择 Edge 部署清单”。 Click Select Edge deployment manifest.

    请不要使用 deployment.template.json 文件作为部署清单。Do not use the deployment.template.json file as a deployment manifest.

如果部署成功,则会在 VS Code 输出中输出确认消息。If the deployment is successful, a confirmation message is printed in the VS Code output.

在 VS Code 的“Azure IoT 中心设备”部分中刷新设备状态。Refresh the status of your device in the Azure IoT Hub Devices section of VS Code. 新模块将会列出,并且在接下来的几分钟内将随着容器的安装和启动开始报告为正在运行。The new modules are listed and will start to report as running over the next few minutes as the containers are installed and started. 也可查看设备上的所有模块是否都已启动并运行。You can also check to see that all the modules are up and running on your device. 在 IoT Edge 设备上运行以下命令,以便查看模块的状态。On your IoT Edge device, run the following command to see the status of the modules.

iotedge list

创建 SQL 数据库Create the SQL database

对设备应用部署清单时,会运行三个模块。When you apply the deployment manifest to your device, you get three modules running. SimulatedTemperatureSensor 模块生成模拟环境数据。The SimulatedTemperatureSensor module generates simulated environment data. sqlFunction 模块会提取数据并针对数据库设置其格式。The sqlFunction module takes the data and formats it for a database. 本部分介绍如何设置用于存储温度数据的 SQL 数据库。This section guides you through setting up the SQL database to store the temperature data.

在 IoT Edge 设备上运行以下命令。Run the following commands on your IoT Edge device. 这些命令连接到在你的设备上运行的 sql 模块,并创建数据库和表来存放发送到它的温度数据。These commands connect to the sql module running on your device and create a database and table to hold the temperature data being sent to it.

  1. 在 IoT Edge 设备上的命令行工具中,连接到你的数据库。In a command-line tool on your IoT Edge device, connect to your database.

    sudo docker exec -it sql bash
    
  2. 打开 SQL 命令工具。Open the SQL command tool.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Strong!Passw0rd'
    
  3. 创建数据库:Create your database:

    CREATE DATABASE MeasurementsDB
    ON
    (NAME = MeasurementsDB, FILENAME = '/var/opt/mssql/measurementsdb.mdf')
    GO
    
  4. 定义表。Define your table.

    CREATE TABLE MeasurementsDB.dbo.TemperatureMeasurements (measurementTime DATETIME2, location NVARCHAR(50), temperature FLOAT)
    GO
    

可以自定义 SQL Server docker 文件,以自动设置要在多个 IoT Edge 设备上部署的 SQL Server。You can customize your SQL Server docker file to automatically set up your SQL Server to be deployed on multiple IoT Edge devices. 有关详细信息,请参阅 Microsoft SQL Server 容器演示项目For more information, see the Microsoft SQL Server container demo project.

查看本地数据View the local data

表创建好以后,sqlFunction 模块就会开始在 IoT Edge 设备的本地 SQL Server 2017 数据库中存储数据。Once your table is created, the sqlFunction module starts storing data in a local SQL Server 2017 database on your IoT Edge device.

在 SQL 命令工具中运行以下命令,以便查看格式化的表数据:From inside the SQL command tool, run the following command to view your formatted table data:

SELECT * FROM MeasurementsDB.dbo.TemperatureMeasurements
GO

查看本地数据库的内容

清理资源Clean up resources

如果打算继续学习下一篇建议的文章,可以保留已创建的资源和配置,以便重复使用。If you plan to continue to the next recommended article, you can keep the resources and configurations that you created and reuse them. 还可以继续使用相同的 IoT Edge 设备作为测试设备。You can also keep using the same IoT Edge device as a test device.

否则,可以删除本文中创建的本地配置和 Azure 资源,以避免收费。Otherwise, you can delete the local configurations and the Azure resources that you created in this article to avoid charges.

删除 Azure 资源Delete Azure resources

删除 Azure 资源和资源组的操作不可逆。Deleting Azure resources and resource groups is irreversible. 请确保不要意外删除错误的资源组或资源。Make sure that you don't accidentally delete the wrong resource group or resources. 如果在现有的包含要保留资源的资源组中创建了 IoT 中心,请只删除 IoT 中心资源本身,而不要删除资源组。If you created the IoT hub inside an existing resource group that has resources that you want to keep, delete only the IoT hub resource itself, instead of deleting the resource group.

若要删除资源,请执行以下操作:To delete the resources:

  1. 登录到 Azure 门户,然后选择“资源组”。Sign in to the Azure portal and select Resource groups.

  2. 选择包含 IoT Edge 测试资源的资源组的名称。Select the name of the resource group that contains your IoT Edge test resources.

  3. 查看包含在资源组中的资源的列表。Review the list of resources contained in your resource group. 若要删除这一切,可以选择“删除资源组”。If you want to delete all of them, you can select Delete resource group. 如果只需删除部分内容,可以单击要单独删除的每个资源。If you want to delete only some of them, you can click into each resource to delete them individually.

后续步骤Next steps

本教程介绍了如何创建 Azure Functions 模块,其中包含用于筛选 IoT Edge 设备生成的原始数据的代码。In this tutorial, you created an Azure Functions module that contains code to filter raw data generated by your IoT Edge device. 做好生成自己的模块的准备以后,即可详细了解如何使用用于 Visual Studio Code 的 Azure IoT Edge 开发 Azure FunctionsWhen you're ready to build your own modules, you can learn more about how to Develop Azure Functions with Azure IoT Edge for Visual Studio Code.

若要在边缘上尝试另一存储方法,请了解如何在 IoT Edge 上使用 Azure Blob 存储。If you want to try another storage method at the edge, read about how to use Azure Blob Storage on IoT Edge.