Delen via

JSON- en Avro-gegevens parseren in Azure Stream Analytics

De Azure Stream Analytics-service ondersteunt het verwerken van gebeurtenissen in CSV-, JSON- en Avro-gegevensindelingen. Zowel JSON- als Avro-gegevens kunnen worden gestructureerd en bevatten enkele complexe typen, zoals geneste objecten (records) en matrices.

Gegevenstypen vastleggen

Recordgegevenstypen worden gebruikt om JSON- en Avro-matrices weer te geven wanneer bijbehorende indelingen worden gebruikt in de invoergegevensstromen. Deze voorbeelden laten een voorbeeldsensor zien, die invoergebeurtenissen leest in JSON-indeling. Hier volgt een voorbeeld van één gebeurtenis:

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

Geneste velden in een bekend schema openen

Gebruik punt notatie (.) om rechtstreeks vanuit uw query toegang te krijgen tot geneste velden. Met deze query worden bijvoorbeeld de coördinaten voor breedtegraad en lengtegraad geselecteerd onder de eigenschap Locatie in de voorgaande JSON-gegevens. Gebruik de puntnotatie om door meerdere niveaus te navigeren, zoals wordt weergegeven in het volgende fragment:

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

Het resultaat is:

|DeviceID|Lat|Long|Temperature|Version|
|-|-|-|-|-|
|12345|47|122|80|1.2.45|

Alle eigenschappen selecteren

U kunt alle eigenschappen van een geneste record selecteren met behulp van het * jokerteken. Kijk een naar het volgende voorbeeld:

SELECT
    DeviceID,
    Location.*
FROM input

Het resultaat is:

|DeviceID|Lat|Long|
|-|-|-|
|12345|47|122|

Geneste velden openen wanneer eigenschapsnaam een variabele is

Gebruik de functie GetRecordPropertyValue als de eigenschapsnaam een variabele is. Deze functie helpt u bij het bouwen van dynamische query's zonder de eigenschapsnamen hard te coderen.

Stel dat de voorbeeldgegevensstroom moet worden gekoppeld aan referentiegegevens met drempelwaarden voor elke apparaatsensor. Een fragment van dergelijke referentiegegevens wordt weergegeven in het volgende fragment.

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

Het doel is om de voorbeeldgegevensset vanaf het begin van het artikel aan die referentiegegevens toe te voegen en één gebeurtenis uit te voeren voor elke sensormeting boven de drempelwaarde. Deze join betekent dat met één gebeurtenis meerdere uitvoergebeurtenissen kunnen worden gegenereerd als meerdere sensoren boven hun respectieve drempelwaarden liggen. Zie het volgende voorbeeld om vergelijkbare resultaten zonder join te bereiken:

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 selecteert de eigenschap in SensorReadings die overeenkomt met de eigenschapsnaam die afkomstig is van de referentiegegevens. Vervolgens wordt de bijbehorende waarde uit SensorReadings geëxtraheerd.

Het resultaat is:

|DeviceID|SensorName|AlertMessage|
| - | - | - |
| 12345 | Humidity | Alert: Sensor above threshold |

Recordvelden converteren naar afzonderlijke gebeurtenissen

Als u recordvelden wilt converteren naar afzonderlijke gebeurtenissen, gebruikt u de operator APPLY samen met de functie GetRecordProperties .

Met behulp van de oorspronkelijke voorbeeldgegevens kunt u de volgende query gebruiken om eigenschappen te extraheren in verschillende gebeurtenissen:

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

Het resultaat is:

|DeviceID|SensorName|AlertMessage|
|-|-|-|
|12345|Temperature|80|
|12345|Humidity|70|
|12345|CustomSensor01|5|
|12345|CustomSensor02|99|
|12345|SensorMetadata|[object Object]|

Met behulp van WITH kunt u deze gebeurtenissen routeren naar verschillende bestemmingen:

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'

JSON-record parseren in SQL-referentiegegevens

Wanneer u Azure SQL Database als referentiegegevens in uw taak gebruikt, kunt u een kolom met gegevens in JSON-indeling opnemen. In het volgende voorbeeld ziet u deze indeling:

|DeviceID|Data|
|-|-|
|12345|{"key": "value1"}|
|54321|{"key": "value2"}|

U kunt de JSON-record in de kolom Gegevens parseren door een eenvoudige door de gebruiker gedefinieerde JavaScript-functie te schrijven.

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

Als u toegang wilt tot de velden van uw JSON-records, maakt u een stap in uw Stream Analytics-query, zoals wordt weergegeven in het volgende voorbeeld.

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

Matrixgegevenstypen

Matrixgegevenstypen zijn een geordende verzameling waarden. In deze sectie worden enkele typische bewerkingen voor matrixwaarden beschreven. In deze voorbeelden worden de functies GetArrayElement, GetArrayElements, GetArrayLength en de operator APPLY gebruikt.

Hier volgt een voorbeeld van een gebeurtenis. Beide CustomSensor03 en SensorMetadata zijn van het type matrix:

{
    "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"
        }
    ]
}

Werken met een specifiek matrixelement

Selecteer het matrixelement in een opgegeven index (selecteer het eerste matrixelement):

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

Het resultaat is:

|firstElement|
|-|
|12|

Matrixlengte selecteren

SELECT
    GetArrayLength(SensorReadings.CustomSensor03) AS arrayLength
FROM input

Het resultaat is:

|arrayLength|
|-|
|3|

Matrixelementen converteren naar afzonderlijke gebeurtenissen

Selecteer alle matrixelementen als afzonderlijke gebeurtenissen. De operator APPLY samen met de ingebouwde functie GetArrayElements extraheert alle matrixelementen als afzonderlijke gebeurtenissen:

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

Het resultaat is:

|DeviceId|ArrayIndex|ArrayValue|
|-|-|-|
|12345|0|12|
|12345|1|-5|
|12345|2|0|

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

Het resultaat is:

|DeviceId|smKey|smValue|
|-|-|-|
|12345|Manufacturer|ABC|
|12345|Version|1.2.45|

Als u de geëxtraheerde velden in kolommen wilt weergeven, draait u de tabel met behulp van de WITH-syntaxis samen met de JOIN-bewerking. Voor deze join is een voorwaarde voor tijdgrens vereist waarmee duplicatie wordt voorkomen:

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

Het resultaat is:

|DeviceId|Lat|Long|smVersion|smManufacturer|
|-|-|-|-|-|
|12345|47|122|1.2.45|ABC|

Gerelateerde inhoud

Gegevenstypen in Azure Stream Analytics

  • Last updated on