如何将Azure IoT Hub接收的虚拟设备数据存储至Azure SQL数据库？

Hey there! Since you already have data flowing into your Azure IoT Hub and confirmed it’s being received, let’s walk through two reliable ways to get that data stored in Azure SQL Database—pick the one that fits your needs best!

This is great if you need to add custom logic (like data cleaning or enrichment) before writing to SQL.

Step 1: Prep your Azure SQL Database #

• Make sure you’ve got an Azure SQL Database and server set up, with firewall rules configured to allow access from Azure services (or your Function App’s outbound IPs).
• Create a table that matches your IoT data structure. For example, if your device sends temperature/humidity data as JSON, run this in your SQL query editor:

CREATE TABLE DeviceTelemetry (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    DeviceId NVARCHAR(100) NOT NULL,
    Temperature FLOAT,
    Humidity FLOAT,
    EventTime DATETIME DEFAULT GETDATE()
);

Step 2: Build an Azure Function triggered by IoT Hub #

• Create a new Function App in the Azure portal (pick a runtime you’re comfortable with—Python, C#, etc.).
• Add a new function with the IoT Hub (Event Hub) trigger type, and link it to your IoT Hub’s built-in event endpoint (or a custom one if you’ve set it up).
• Write code to parse the IoT message and insert it into SQL. Here’s a quick Python example:

import azure.functions as func
import pyodbc
import json
import os

def main(event: func.EventHubEvent):
    try:
        # Parse the incoming IoT message
        message_body = event.get_body().decode('utf-8')
        telemetry = json.loads(message_body)
        
        # Pull SQL connection string from Function App settings (never hardcode!)
        conn_str = os.environ["SqlConnectionString"]
        
        # Insert data into SQL
        with pyodbc.connect(conn_str) as conn:
            with conn.cursor() as cursor:
                insert_query = """
                    INSERT INTO DeviceTelemetry (DeviceId, Temperature, Humidity)
                    VALUES (?, ?, ?)
                """
                cursor.execute(insert_query, 
                            telemetry["deviceId"], 
                            telemetry["temperature"], 
                            telemetry["humidity"])
            conn.commit()
        
        print(f"Successfully saved data from device {telemetry['deviceId']}")
    except Exception as e:
        print(f"Oops, error processing message: {str(e)}")

• Add your SQL connection string as an app setting named SqlConnectionString in your Function App (find this under Configuration > Application settings).

Step 3: Route IoT Hub messages to the Function #

• Go to your IoT Hub in the portal, navigate to Message routing.
• Create a new route: set the endpoint type to Event Hub (this links to your Function’s trigger), and use a query like true to route all messages (or filter by device ID/properties if needed).
• Save the route—IoT Hub will now send messages to your Function, which handles writing to SQL automatically.

Use this if you need to process data in real-time (like calculating averages over time) before storing it.

Step 1: Set up a Stream Analytics job #

• Create a new Stream Analytics job in the Azure portal.
• Add an input source: select your IoT Hub and configure the connection details.
• Add an output target: select Azure SQL Database, link it to your database, and choose the table you want to write to (you can let it auto-create a table if needed).

Step 2: Write a Stream Analytics query #

• Use SQL-like syntax to define how data is processed. For basic forwarding of all data:

SELECT
    deviceId,
    temperature,
    humidity,
    EventEnqueuedUtcTime AS EventTime
INTO
    [YourSQLTableOutput]
FROM
    [YourIoTHubInput]

For aggregated data (e.g., average temperature per device every 5 minutes):

SELECT
    deviceId,
    AVG(temperature) AS AvgTemperature,
    DATEADD(minute, DATEDIFF(minute, 0, EventEnqueuedUtcTime), 0) AS WindowStartTime
INTO
    [YourSQLAggregatedTableOutput]
FROM
    [YourIoTHubInput]
GROUP BY
    deviceId,
    DATEADD(minute, DATEDIFF(minute, 0, EventEnqueuedUtcTime), 0)

Step 3: Start the job #

• Configure the job’s start time (e.g., "Now" or a past time to backfill data) and hit start. Stream Analytics will begin processing IoT Hub data and writing the results to your SQL database.

• Security first: Store SQL credentials in Azure Key Vault instead of hardcoding, and restrict SQL firewall access only to necessary resources.
• Retry logic: Add retry mechanisms in Functions or configure Stream Analytics output retries to handle temporary connection issues.
• Performance: For high-volume data, use batch writes in Functions or adjust Stream Analytics throughput units to keep up with your IoT traffic.

内容的提问来源于stack exchange，提问作者Giacomo El Ghisa Zidarich