Parsa JSON-och Avro-data i Azure Stream Analytics

Azure Stream Analytics att bearbeta händelser i CSV-, JSON-och Avro data format. Både JSON-och Avro-data kan struktureras och innehålla några komplexa typer, till exempel kapslade objekt (poster) och matriser.

Registrera data typer

Post data typer används för att representera JSON-och Avro-matriser när motsvarande format används i indata strömmar. Dessa exempel demonstrerar en exempel sensor som läser ingångs händelser i JSON-format. Här är ett exempel på en enskild händelse:

{
    "DeviceId" : "12345",
    "Location" :
    {
        "Lat": 47,
        "Long": 122
    },
    "SensorReadings" :
    {
        "Temperature" : 80,
        "Humidity" : 70,
        "CustomSensor01" : 5,
        "CustomSensor02" : 99,
        "SensorMetadata" : 
        {
        "Manufacturer":"ABC",
        "Version":"1.2.45"
        }
    }
}

Komma åt kapslade fält i känt schema

Använd punkt notation (.) för att enkelt komma åt kapslade fält direkt från frågan. Den här frågan väljer till exempel de koordinater för latitud och longitud under egenskapen location i föregående JSON-data. Punkt notationen kan användas för att navigera flera nivåer som visas nedan.

SELECT
    DeviceID,
    Location.Lat,
    Location.Long,
    SensorReadings.Temperature,
    SensorReadings.SensorMetadata.Version
FROM input

Resultatet är:

Välj alla egenskaper

Du kan välja alla egenskaper för en kapslad post med jokertecknet *. Se följande exempel:

SELECT
    DeviceID,
    Location.*
FROM input

Resultatet är:

Komma åt kapslade fält när egenskaps namnet är en variabel

Använd funktionen GetRecordPropertyValue om egenskaps namnet är en variabel. Detta gör det möjligt att skapa dynamiska frågor utan hårdkoda egenskaps namn.

Anta till exempel att data strömmen i exempel måste kopplas till referens data som innehåller tröskelvärden för varje enhets sensor. Ett kodfragment av dessa referens data visas nedan.

{
    "DeviceId" : "12345",
    "SensorName" : "Temperature",
    "Value" : 85
},
{
    "DeviceId" : "12345",
    "SensorName" : "Humidity",
    "Value" : 65
}

Målet här är att koppla exempel data uppsättningen från överkanten av artikeln till dessa referens data och mata ut en händelse för varje sensor mått över tröskelvärdet. Det innebär att vår enskilda händelse ovan kan generera flera utgående händelser om flera sensorer är över respektive tröskelvärde, tack vare kopplingen. Om du vill uppnå liknande resultat utan någon koppling, se avsnittet nedan.

SELECT
    input.DeviceID,
    thresholds.SensorName,
    "Alert : Sensor above threshold" AS AlertMessage
FROM input      -- stream input
JOIN thresholds -- reference data input
ON
    input.DeviceId = thresholds.DeviceId
WHERE
    GetRecordPropertyValue(input.SensorReadings, thresholds.SensorName) > thresholds.Value

GetRecordPropertyValue väljer egenskapen i SensorReadings, vars namn matchar det egenskaps namn som kommer från referens data. Sedan extraheras det associerade värdet från SensorReadings .

Resultatet är:

Konvertera post fält till separata händelser

Om du vill konvertera postfält till separata händelser använder du operatorn use tillsammans med funktionen GetRecordProperties .

Med de ursprungliga exempel data kan följande fråga användas för att extrahera egenskaper till olika händelser.

SELECT
    event.DeviceID,
    sensorReading.PropertyName,
    sensorReading.PropertyValue
FROM input as event
CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading

Resultatet är:

Med hjälp av, kan du sedan dirigera dessa händelser till olika mål:

WITH Stage0 AS
(
    SELECT
        event.DeviceID,
        sensorReading.PropertyName,
        sensorReading.PropertyValue
    FROM input as event
    CROSS APPLY GetRecordProperties(event.SensorReadings) AS sensorReading
)

SELECT DeviceID, PropertyValue AS Temperature INTO TemperatureOutput FROM Stage0 WHERE PropertyName = 'Temperature'
SELECT DeviceID, PropertyValue AS Humidity INTO HumidityOutput FROM Stage0 WHERE PropertyName = 'Humidity'

Parsa JSON-post i SQL-referens data

När du använder Azure SQL Database som referens data i jobbet, är det möjligt att ha en kolumn som har data i JSON-format. Ett exempel på detta visas nedan.

Du kan parsa JSON-posten i data kolumnen genom att skriva en enkel användardefinierad JavaScript-funktion.

function parseJson(string) {
return JSON.parse(string);
}

Du kan sedan skapa ett steg i din Stream Analytics-fråga så som visas nedan för att få åtkomst till fälten i dina JSON-poster.

WITH parseJson as
(
SELECT DeviceID, udf.parseJson(sqlRefInput.Data) as metadata,
FROM sqlRefInput
)

SELECT metadata.key
INTO output
FROM streamInput
JOIN parseJson 
ON streamInput.DeviceID = parseJson.DeviceID

Mat ris data typer

Mat ris data typer är en ordnad samling av värden. Några vanliga åtgärder för mat ris värden beskrivs nedan. I de här exemplen används Functions- GetArrayElement, GetArrayElements, GetArrayLengthoch operatorn Apply .

Här är ett exempel på en enskild händelse. Både CustomSensor03 och SensorMetadata är av typen matris:

{
    "DeviceId" : "12345",
    "SensorReadings" :
    {
        "Temperature" : 80,
        "Humidity" : 70,
        "CustomSensor01" : 5,
        "CustomSensor02" : 99,
        "CustomSensor03": [12,-5,0]
     },
    "SensorMetadata":[
        {          
            "smKey":"Manufacturer",
            "smValue":"ABC"                
        },
        {
            "smKey":"Version",
            "smValue":"1.2.45"
        }
    ]
}

Arbeta med ett enskilt mat ris element

Välj mat ris element i ett angivet index (Välj det första mat ris elementet):

SELECT
    GetArrayElement(SensorReadings.CustomSensor03, 0) AS firstElement
FROM input

Resultatet är:

Välj mat ris längd

SELECT
    GetArrayLength(SensorReadings.CustomSensor03) AS arrayLength
FROM input

Resultatet är:

Omvandla mat ris element till separata händelser

Markera alla mat ris element som enskilda händelser. Operatorn Apply tillsammans med den inbyggda funktionen GetArrayElements extraherar alla mat ris element som enskilda händelser:

SELECT
    DeviceId,
    CustomSensor03Record.ArrayIndex,
    CustomSensor03Record.ArrayValue
FROM input
CROSS APPLY GetArrayElements(SensorReadings.CustomSensor03) AS CustomSensor03Record

Resultatet är:

SELECT   
    i.DeviceId, 
    SensorMetadataRecords.ArrayValue.smKey as smKey,
    SensorMetadataRecords.ArrayValue.smValue as smValue
FROM input i
CROSS APPLY GetArrayElements(SensorMetadata) AS SensorMetadataRecords

Resultatet är:

Om de extraherade fälten måste visas i kolumner, är det möjligt att pivotera data uppsättningen med syntaxen with , förutom kopplings åtgärden. Den här kopplingen kräver ett tids gräns villkor som förhindrar duplicering:

WITH DynamicCTE AS (
    SELECT   
        i.DeviceId,
        SensorMetadataRecords.ArrayValue.smKey as smKey,
        SensorMetadataRecords.ArrayValue.smValue as smValue
    FROM input i
    CROSS APPLY GetArrayElements(SensorMetadata) AS SensorMetadataRecords 
)

SELECT
    i.DeviceId,
    i.Location.*,
    V.smValue AS 'smVersion',
    M.smValue AS 'smManufacturer'
FROM input i
LEFT JOIN DynamicCTE V ON V.smKey = 'Version' and V.DeviceId = i.DeviceId AND DATEDIFF(minute,i,V) BETWEEN 0 AND 0 
LEFT JOIN DynamicCTE M ON M.smKey = 'Manufacturer' and M.DeviceId = i.DeviceId AND DATEDIFF(minute,i,M) BETWEEN 0 AND 0

Resultatet är:

Se även

Data typer i Azure Stream Analytics