Partager via

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

  • Article

Azure Stream Analytics prend en charge le traitement d’é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.

Notes

Les fichiers AVRO créés par la capture Event Hub répondent à un format spécifique qui implique que vous utilisiez la fonctionnalité de désérialiseur personnalisé. Pour plus d'informations, consultez Lire les entrées dans n’importe quel format avec des désérialiseurs .NET personnalisés.

Données de type 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 facilement 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. La notation par points peut servir à parcourir plusieurs niveaux, comme indiqué ci-dessous.

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

Le résultat est le suivant :

DeviceID Lat Long Température 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. Elle permet de créer des requêtes dynamiques sans codage en dur des noms de propriété.

Par exemple, imaginez que l'exemple de flux de données doive être joint à 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 ci-dessous.

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

L’objectif consiste ici à joindre l'exemple de jeu de données du début de l'article à ces données de référence, et à générer un événement pour chaque mesure de capteur au-dessus de son seuil. Ainsi, l'événement unique ci-dessus peut générer plusieurs événements de sortie si différents capteurs se trouvent au-dessus de leurs seuils respectifs et ce, grâce à la jointure. Pour obtenir des résultats similaires sans jointure, consultez la section ci-dessous.

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, et dont le nom correspond au nom de propriété provenant des données de référence. La valeur associée de SensorReadings est ensuite extraite.

Le résultat est le suivant :

DeviceID SensorName AlertMessage
12345 Humidité Alerte : capteur au-dessus du seuil

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

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

Avec les exemples de données d’origine, la requête suivante peut être utilisée pour extraire des propriétés dans 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 Température 80
12345 Humidité 70
12345 CustomSensor01 5
12345 CustomSensor02 99
12345 SensorMetadata [object Object]

WITH permet de router 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 comme données de référence dans votre travail, il est possible d’avoir une colonne qui contient des données au format JSON. Voici un exemple.

DeviceID Données
12345 {"key" : "value1"}
54321 {"key" : "value2"}

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

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

Vous pouvez ensuite créer une étape dans votre requête Stream Analytics comme indiqué ci-dessous pour accéder aux champs de vos enregistrements JSON.

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. Certaines opérations courantes sur des valeurs de tableau sont décrites ci-dessous. Ces exemples utilisent les fonctions GetArrayElement, GetArrayElements et GetArrayLength, ainsi que l’opérateur APPLY.

Voici un exemple d’événement. CustomSensor03 et SensorMetadata correspondent à un tableau de type :

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

Utilisation d’un élément de tableau spécifique

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

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

Le résultat est le suivant :

firstElement
12

Sélection d’une longueur de 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 du tableau en tant qu’événements individuels. L’opérateur APPLY avec la fonction intégrée GetArrayElements extrait tous les éléments du 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 Fabricant ABC
12345 Version 1.2.45

Si les champs extraits doivent apparaître dans des colonnes, il est possible de faire pivoter le jeu de données à l’aide de la syntaxe WITH, en plus de l’opération JOIN. Cette jointure requiert une condition de limite d'heure qui évite 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

Voir aussi

Types de données dans Azure Stream Analytics