Partager via

Analyser des données JSON et Avro dans Azure Stream Analytics

Le service Azure Stream Analytics prend en charge le traitement des événements dans les formats de données CSV, JSON et Avro. Des données JSON et Avro peuvent être structurées et contenir certains types complexes, tels que des tableaux et objets (enregistrements) imbriqués.

Types de données d'enregistrement

Des données de type enregistrement sont utilisées pour représenter des tableaux JSON et Avro quand des formats correspondants sont utilisés dans les flux de données en entrée. Ces exemples montrent un capteur qui lit les événements en entrée au format JSON. Voici un exemple d’événement unique :

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

Accéder aux champs imbriqués dans un schéma connu

Utilisez la notation par points (.) pour accéder aux champs imbriqués directement à partir de votre requête. Par exemple, cette requête sélectionne les coordonnées de latitude et de longitude sous la propriété Location dans les données de l’extrait JSON précédent. Utilisez la notation par points pour parcourir plusieurs niveaux, comme indiqué dans l’extrait de code suivant :

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

Le résultat est le suivant :

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

Sélectionner toutes les propriétés

Vous pouvez sélectionner toutes les propriétés d’un enregistrement imbriqué à l’aide du * caractère générique. Prenons l’exemple suivant :

SELECT
    DeviceID,
    Location.*
FROM input

Le résultat est le suivant :

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

Accéder aux champs imbriqués lorsque le nom de la propriété est une variable

Utilisez la fonction GetRecordPropertyValue si le nom de la propriété est une variable. Cette fonction vous aide à générer des requêtes dynamiques sans noms de propriétés de codage en dur.

Par exemple, imaginez que l’exemple de flux de données doit être joint avec des données de référence contenant des seuils pour chaque capteur d’appareil. Un extrait de ce type de données de référence est reproduit dans l’extrait suivant.

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

L’objectif est de joindre l’exemple de jeu de données du haut de l’article à ces données de référence et de générer un événement pour chaque mesure de capteur au-dessus de son seuil. Cette jointure signifie que l’événement unique peut générer plusieurs événements de sortie si plusieurs capteurs dépassent leurs seuils respectifs. Pour obtenir des résultats similaires sans jointure, consultez l’exemple suivant :

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 sélectionne la propriété dans SensorReadings qui correspond au nom de la propriété provenant des données de référence. Ensuite, il extrait la valeur associée de SensorReadings.

Le résultat est le suivant :

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

Convertir les champs d’enregistrement en événements distincts

Pour convertir des champs d’enregistrement en événements distincts, utilisez l’opérateur APPLY avec la fonction GetRecordProperties .

À l'aide des données d'exemple d'origine, vous pouvez utiliser la requête suivante pour extraire des propriétés pour différents événements :

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

Le résultat est le suivant :

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

À l’aide de WITH, vous pouvez acheminer ces événements vers différentes destinations :

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'

Analyser un enregistrement JSON dans des données de référence SQL

Lorsque vous utilisez Azure SQL Database en tant que données de référence dans votre travail, vous pouvez inclure une colonne qui contient des données au format JSON. L’exemple suivant montre ce format :

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

Vous pouvez analyser l’enregistrement JSON dans la colonne Données en écrivant une fonction javaScript simple définie par l’utilisateur.

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

Pour accéder aux champs de vos enregistrements JSON, créez une étape dans votre requête Stream Analytics, comme illustré dans l’exemple suivant.

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

Données de type tableau

Les données de type tableau sont des collections ordonnées de valeurs. Cette section détaille certaines opérations classiques sur les valeurs de tableau. Ces exemples utilisent les fonctions GetArrayElement, GetArrayElements, GetArrayLength et l’opérateur APPLY .

Voici un exemple d’événement. Les éléments CustomSensor03 et SensorMetadata sont de type tableau :

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

Utiliser un élément de tableau spécifique

Sélectionnez l’élément de tableau à un index spécifié (sélectionnez le premier élément de tableau) :

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

Le résultat est le suivant :

|firstElement|
|-|
|12|

Sélectionnez la longueur du tableau

SELECT
    GetArrayLength(SensorReadings.CustomSensor03) AS arrayLength
FROM input

Le résultat est le suivant :

|arrayLength|
|-|
|3|

Conversion des éléments de tableau en événements distincts

Sélectionnez tous les éléments de tableau en tant qu’événements individuels. L’opérateur APPLY avec la fonction intégrée GetArrayElements extrait tous les éléments de tableau en tant qu’événements individuels :

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

Le résultat est le suivant :

|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

Le résultat est le suivant :

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

Pour afficher les champs extraits dans les colonnes, pivotez le jeu de données à l’aide de la syntaxe WITH avec l’opération JOIN . Cette jointure nécessite une condition de limite de temps qui empêche la duplication :

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

Le résultat est le suivant :

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

Contenu connexe

Types de données dans Azure Stream Analytics

  • Last updated on