如何获取IoT Hub的原始JSON？能否通过Streaming Analytics存入SQL Server？

Absolutely feasible! I’ve walked through this exact setup with multiple developers, so let’s break down how to make it work:

Step 1: Configure your Stream Analytics input #

When setting up your IoT Hub input in Stream Analytics, select JSON as the event serialization format. This ensures the service recognizes incoming messages correctly, but we won’t parse individual fields—we’ll grab the entire raw payload instead.

Step 2: Write the Stream Analytics query #

Use the GetMetadataPropertyValue function to fetch the raw event body (your full JSON string) and cast it to a string type compatible with SQL Server. Here’s a sample query:

SELECT
    -- Grab the full raw JSON payload from the IoT Hub message
    CAST(GetMetadataPropertyValue(IoTHubInput, 'EventBody') AS NVARCHAR(MAX)) AS RawJson,
    -- Optional: Include a timestamp for tracking when the event was processed
    System.Timestamp AS EventProcessedTimestamp
INTO
    SQLServerOutput
FROM
    IoTHubInput -- Replace with your actual input alias

What’s happening here? #

• GetMetadataPropertyValue(IoTHubInput, 'EventBody') retrieves the unmodified JSON string sent from your IoT device.
• Casting to NVARCHAR(MAX) ensures we can handle even large JSON payloads without truncation.

Step 3: Prepare your SQL Server table #

Create a table in SQL Server with columns matching your query output. For the sample above, the table schema would look like this:

CREATE TABLE IoT_Raw_Messages (
    RawJson NVARCHAR(MAX) NOT NULL,
    EventProcessedTimestamp DATETIMEOFFSET NOT NULL,
    -- Optional: Add an auto-incrementing primary key for tracking
    MessageId INT IDENTITY(1,1) PRIMARY KEY
);

When configuring the Stream Analytics output to SQL Server, map the query’s RawJson and EventProcessedTimestamp columns to the corresponding table columns. You can adjust batch settings (like batch size and timeout) in the output configuration to optimize write performance.

Key notes to avoid issues #

• Payload size: NVARCHAR(MAX) supports up to 2GB, which works for most IoT JSON messages. For extremely large payloads, consider VARBINARY(MAX) (though this is rare for typical IoT use cases).
• Encoding: Ensure your IoT devices send JSON in UTF-8 (IoT Hub’s default), which Stream Analytics handles natively.
• Error handling: Add a dead-letter output to catch messages that fail processing—this lets you debug issues without losing data.

内容的提问来源于stack exchange，提问作者Randy Minder