IoT Platform:Expressions SQL

Expressions SQL

Les données JSON peuvent être mappées sur une table virtuelle. Dans ce mappage, une clé correspond à une colonne de table et une valeur correspond à la valeur de la colonne. Cela vous permet d'utiliser SQL pour le traitement des données. La figure suivante illustre une expression SQL pour une règle de transfert de données.

Voici des exemples SQL pour les règles de transfert de données :

• Exemple SQL pour le traitement des données d'une rubrique personnalisée.

  Un capteur environnemental collecte des données de température, d'humidité et de pression atmosphérique. L'appareil envoie les données suivantes à la rubrique personnalisée /a1hRrzD****/+/user/update :

  {
      "temperature":25.1,
      "humidity":65,
      "pressure":101.5,
      "location":"***,***"
  }

  Pour déclencher la règle lorsque la température dépasse 38 °C et filtrer le nom de l'appareil, la température et les informations de localisation, utilisez l'instruction SQL suivante :

  SELECT temperature as t, deviceName() as deviceName, location 
  FROM "/a1hRrzD****/+/user/update" 
  WHERE temperature > 38

• Exemple SQL pour le traitement des données des rubriques de communication de base et des rubriques de communication du modèle Thing Specification Language. Lorsque les données des rubriques de communication de base et des rubriques de communication du modèle Thing Specification Language sont transférées vers le moteur de règles, celui-ci les analyse. Pour plus d'informations sur le format des données après analyse, consultez Formats de données. Le capteur définit deux propriétés personnalisées : Current Humidity (identifiant : CurrentHumidity, type de données : float, plage de valeurs : 0 à 100) et Current Temperature (identifiant : CurrentTemperature, type de données : float, plage de valeurs : -40 à 120).

  Après que le moteur de règles a analysé les données des propriétés de température et d'humidité envoyées par le capteur, les données se présentent sous la forme suivante :

  {
      "deviceType": "TemperatureHumidityDetector", 
      "iotId": "N5KURkKdibnZvSls****000100", 
      "productKey": "a15NNfl****", 
      "gmtCreate": 1564569974224, 
      "deviceName": "N5KURkKdibnZvSls3Yfx", 
      "items": {
          "CurrentHumidity": {
              "value": 70, 
              "time": 1564569974229
          }, 
          "CurrentTemperature": {
              "value": 23.5, 
              "time": 1564569974229
          }
      }
  }

  Important

  Dans une requête SQL, vous devez utiliser

  items.${PropertyIdentifier}.value

  pour accéder aux données d'une propriété spécifique.

  Pour déclencher la règle lorsque la température dépasse 38 °C et filtrer le nom de l'appareil, la température actuelle et l'humidité actuelle, utilisez l'instruction SQL suivante :

  SELECTdeviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature
  FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post"
  WHERE items.CurrentTemperature.value > 38

  Si la propriété appartient à un module personnalisé, tel que testFB, l'identifiant de propriété est au format ${ModuleIdentifier}:${PropertyIdentifier}. Pour accéder aux données d'une propriété spécifique, vous devez utiliser des guillemets doubles (""). L'instruction SQL est la suivante :

  SELECTdeviceName() as deviceName, "items.testFB:CurrentHumidity.value" as Humidity, "items.testFB:CurrentTemperature.value" as Temperature
  FROM "/sysa15NNfl****/N5KUR***/thing/event/property/post"
  WHERE "items.testFB:CurrentTemperature.value" > 38

SELECT

• Pour le format de données JSON

  Les champs d'une instruction SELECT peuvent être les valeurs de clé issues de la charge utile JSON d'un message envoyé. Vous pouvez également utiliser des fonctions SQL intégrées, telles que deviceName(). Pour plus d'informations sur les fonctions SQL intégrées du moteur de règles, consultez Liste des fonctions.

  Vous pouvez utiliser * en combinaison avec des fonctions. Les sous-requêtes ne sont pas prises en charge.

  Les données JSON envoyées peuvent être un tableau ou un objet JSON imbriqué. Les instructions SQL prennent en charge JSONPath pour récupérer les valeurs des propriétés. Par exemple, pour {a:{key1:v1, key2:v2}}, vous pouvez utiliser a.key2 pour récupérer la valeur v2. Lorsque vous utilisez des variables, faites attention à la différence entre les guillemets simples et les guillemets doubles. Les guillemets simples indiquent une constante. Les guillemets doubles ou l'absence de guillemets indiquent une variable. Par exemple, si vous utilisez des guillemets simples pour 'a.key2', la valeur est la chaîne a.key2.

  Dans les exemples SQL de cette rubrique :

  • Dans l'instruction SQL SELECTSELECT temperature as t, deviceName() as deviceName, location, temperature et location sont des champs issus des données envoyées, et deviceName() est une fonction SQL intégrée.

  • Dans l'instruction SELECT SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature pour le traitement des données d'une rubrique d'envoi de propriétés, items.CurrentHumidity.value et items.CurrentTemperature.value sont des champs issus des données de propriété envoyées du module par défaut. deviceName() est une fonction SQL intégrée.

    Remarque

    items.testFB:CurrentHumidity.value

    et

    items.testFB:CurrentTemperature.value

    sont des champs issus des données de propriété envoyées d'un module personnalisé.

• Pour les données binaires

  • Vous pouvez saisir * pour transmettre les données directement. Vous ne pouvez pas utiliser de fonctions après *.

  • Vous pouvez utiliser des fonctions intégrées. Par exemple, utilisez la fonction to_base64(*) pour convertir les données de charge utile binaires d'origine en une chaîne Base64 et les extraire. Utilisez la fonction deviceName() pour extraire le nom de l'appareil.

FROM

Dans la clause FROM, vous pouvez spécifier une rubrique pour faire correspondre la rubrique source des messages d'appareil que vous souhaitez traiter. Pour le niveau du nom de l'appareil dans la rubrique, vous pouvez utiliser le caractère générique signe plus (+). Ce caractère représente tous les appareils au niveau actuel, c'est-à-dire tous les appareils sous le produit. Lorsque vous spécifiez une rubrique personnalisée, vous pouvez également utiliser le caractère générique dièse (#). Ce caractère représente le niveau actuel et tous les niveaux suivants dans la rubrique. Pour plus d'informations sur les caractères génériques, consultez Utiliser des rubriques personnalisées pour la communication.

Lorsqu'un message de la rubrique spécifiée arrive, sa charge utile est analysée au format JSON et traitée selon l'instruction SQL. Si le format du message n'est pas valide, le message est ignoré. Vous pouvez utiliser la fonction topic() pour référencer une valeur de rubrique spécifique.

Dans les exemples SQL ci-dessus :

• L'instruction FROM "/a1hRrzD****/+/user/update" indique que l'instruction SQL traite uniquement les messages de la rubrique personnalisée /a1hRrzD****/+/user/update.

• L'instruction FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post" indique que l'instruction SQL traite uniquement les messages de la rubrique vers laquelle l'appareil N5KURkKdibnZvSls3Yfx envoie des propriétés.

WHERE

• Pour le format de données JSON

  La clause WHERE spécifie la condition de déclenchement de la règle, qui est une expression conditionnelle. Les sous-requêtes ne sont pas prises en charge. Les champs que vous pouvez utiliser dans la clause WHERE sont les mêmes que ceux de l'instruction SELECT. Lorsqu'un message est reçu depuis la rubrique correspondante, l'instruction WHERE est évaluée pour déterminer s'il faut déclencher la règle. Pour plus d'informations sur les expressions conditionnelles, consultez la section Expressions conditionnelles prises en charge ci-dessous.

  Dans les deux exemples ci-dessus, l'instruction conditionnelle WHERE temperature > 38 indique que la règle est déclenchée uniquement lorsque la température dépasse 38 °C.

• Pour le format de données binaires

  Pour les données binaires, la clause WHERE prend uniquement en charge les fonctions intégrées et les expressions conditionnelles. Vous ne pouvez pas utiliser les champs de la charge utile.

Résultats SQL

Après l'exécution de l'instruction SQL, un résultat SQL est généré pour l'étape de transfert suivante. Si une erreur se produit lors de l'analyse de la charge utile, l'exécution de la règle échoue.

Si vous souhaitez transférer des données vers Tablestore, vous devez utiliser le format de variable ${Expression} pour référencer la valeur correspondante lorsque vous définissez la destination du transfert de données.

Si les règles des deux exemples ci-dessus doivent transférer des données vers une table de données dans Tablestore, les valeurs de la clé primaire peuvent être configurées comme suit :

• ${t}, ${deviceName} et ${location}.

• ${deviceName}, ${Humidity} et ${Temperature}.

Utilisation des tableaux

Les expressions de tableau doivent être entourées de guillemets doubles. L'opérateur $. récupère un objet JSON. L'opérateur $. est facultatif. L'opérateur . récupère un tableau JSON.

Expressions conditionnelles prises en charge

Déboguer les instructions SQL

Si vous définissez Data Format sur JSON lors de la création d'une règle de transfert de données, vous pouvez déboguer l'instruction SQL en ligne dans la console IoT Platform. La procédure est la suivante.

1. Après avoir rédigé l'instruction SQL, cliquez sur SQL Debug.

2. Dans la boîte de dialogue SQL Debugging, sous l'onglet Debugging Parameters, saisissez les données de débogage et cliquez sur Debug. Product Device Device Tag Topic Payload Data

   Saisissez les données de charge utile pour le débogage en fonction du format de données de la rubrique. Le format des données est décrit comme suit :

   • Si la rubrique est une rubrique personnalisée, le format des données de charge utile que vous saisissez doit être identique au format des données envoyées à la rubrique.

   • Si la rubrique est une rubrique de communication de base ou une rubrique de communication du modèle Thing Specification Language, consultez Formats de données.

   Les paramètres de débogage incluent

   ,

   ,

   (vous pouvez ajouter ou supprimer des tags de paires clé-valeur),

   et

   .

3. Debug Result Cliquez sur l'onglet pour afficher le résultat. Le résultat du débogage s'affiche au format JSON et comprend des champs tels que attribute('b') avec la valeur "b", payload avec la valeur {"timeout":"test"}, attribute('a') avec la valeur "a", attribute('coordinate') pour les coordonnées de l'appareil, deviceName pour le nom de l'appareil et timestamp avec une valeur telle que 1579004949668.