IoT Platform:SQL 式

SQL 式

JSON データは仮想テーブルにマッピングできます。このマッピングでは、キーがテーブルの列に、値が列の値に対応します。これにより、SQL を使用したデータ処理が可能になります。以下の図は、データ転送ルールの SQL 式を示しています。

以下に、データ転送ルールの SQL の例を示します。

• カスタムトピックからのデータを処理する SQL の例

  環境センサーが温度、湿度、気圧のデータを収集します。デバイスは、次のデータをカスタムトピック /a1hRrzD****/+/user/update に報告します。

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

  温度が 38°C を超える場合にルールをトリガーし、デバイス名、温度、および位置情報をフィルターするには、次の SQL ステートメントを使用します。

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

• 基本通信トピックおよび TSL モデル通信トピックからのデータを処理する SQL の例。基本通信トピックおよび TSL モデル通信トピックからのデータがルールエンジンに転送されると、ルールエンジンがデータを解析します。解析後のデータ形式の詳細については、「データ形式」をご参照ください。センサーは、現在の湿度 (識別子：CurrentHumidity、データ型：float、値の範囲：0～100) と現在の温度 (識別子：CurrentTemperature、データ型：float、値の範囲：-40～120) の 2 つのカスタムプロパティを定義します。

  ルールエンジンがセンサーの報告した温度と湿度のプロパティデータを解析した後、データは次の形式になります。

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

  重要 SQL クエリでは、items.${PropertyIdentifier}.value を使用して特定のプロパティのデータにアクセスする必要があります。

  温度が 38°C を超える場合にルールをトリガーし、デバイス名、現在の温度、および現在の湿度をフィルターするには、次の SQL ステートメントを使用します。

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

  プロパティが testFB などのカスタムモジュールに属している場合、プロパティ識別子は ${ModuleIdentifier}:${PropertyIdentifier} という形式になります。特定のプロパティのデータにアクセスするには、二重引用符 ("") を使用する必要があります。SQL ステートメントは次のとおりです。

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

SELECT

• JSON データ形式の場合

  SELECT ステートメントのフィールドには、報告されるメッセージの JSON ペイロードのキー値を指定できます。また、deviceName() などの組み込み SQL 関数も使用できます。ルールエンジンの組み込み SQL 関数の詳細については、「関数リスト」をご参照ください。

  * を関数と組み合わせて使用できます。サブクエリはサポートされていません。

  報告される JSON データは、配列またはネストされた JSON オブジェクトの場合があります。SQL ステートメントは、プロパティ値を取得するための JSONPath をサポートしています。たとえば、{a:{key1:v1, key2:v2}} の場合、a.key2 を使用して値 v2 を取得できます。変数を使用する場合、単一引用符と二重引用符の違いに注意してください。単一引用符は定数を示します。二重引用符または引用符なしは変数を示します。たとえば、'a.key2' に単一引用符を使用した場合、値は文字列 a.key2 になります。

  このトピックの SQL の例：

  • SQL ステートメント SELECTSELECT temperature as t, deviceName() as deviceName, location では、temperature と location は報告されたデータからのフィールドであり、deviceName() は組み込み SQL 関数です。
  • プロパティ報告トピックからのデータを処理するための SELECT ステートメント SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature では、items.CurrentHumidity.value と items.CurrentTemperature.value はデフォルトモジュールから報告されたプロパティのデータのフィールドです。deviceName() は組み込み SQL 関数です。
    説明 items.testFB:CurrentHumidity.value と items.testFB:CurrentTemperature.value は、カスタムモジュールから報告されたプロパティのデータのフィールドです。
• バイナリデータの場合
  • * を入力してデータを直接パススルーできます。* の後に関数を使用することはできません。
  • 組み込み関数を使用できます。たとえば、to_base64(*) 関数を使用して元のバイナリペイロードデータを Base64 文字列に変換して抽出します。deviceName() 関数を使用してデバイス名を抽出します。

FROM

FROM 句では、処理するデバイスメッセージのソース トピックを指定します。トピックのデバイス名レベルには、プラス記号 (+) のワイルドカード文字を使用できます。この文字は、現在のレベルのすべてのデバイス、つまりプロダクト配下のすべてのデバイスを表します。カスタムトピックを指定する場合、シャープ記号 (#) のワイルドカード文字も使用できます。この文字は、トピック内の現在のレベルとそれ以降のすべてのレベルを表します。ワイルドカード文字の詳細については、「カスタムトピックを使用した通信」をご参照ください。

指定されたトピックからメッセージが到着すると、そのペイロードは JSON として解析され、SQL ステートメントに基づいて処理されます。メッセージ形式が無効な場合、メッセージは無視されます。topic() 関数を使用して、特定のトピック値を参照できます。

上記の SQL の例：

• FROM "/a1hRrzD****/+/user/update" ステートメントは、SQL ステートメントがカスタムトピック /a1hRrzD****/+/user/update からのメッセージのみを処理することを意味します。
• FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post" ステートメントは、SQL ステートメントがデバイス N5KURkKdibnZvSls3Yfx がプロパティを報告するトピックからのメッセージのみを処理することを意味します。

WHERE

• JSON データ形式の場合

  WHERE 句は、ルールトリガー条件を指定します。この条件は、条件式で表されます。サブクエリはサポートされていません。WHERE 句で使用できるフィールドは、SELECT ステートメントのフィールドと同じです。対応するトピックからメッセージが受信されると、WHERE ステートメントが評価され、ルールをトリガーするかどうかが決定されます。条件式の詳細については、以下の「サポートされている条件式」セクションをご参照ください。

  上記の 2 つの例では、条件ステートメント WHERE temperature > 38 は、温度が 38°C を超える場合にのみルールがトリガーされることを意味します。

• バイナリデータ形式の場合

  バイナリデータの場合、WHERE 句は組み込み関数と条件式のみをサポートします。ペイロードのフィールドは使用できません。

SQL の結果

SQL ステートメントが実行されると、次の転送ステップのために SQL の結果が生成されます。ペイロードの解析中にエラーが発生した場合、ルールの実行は失敗します。

データを TableStore に転送する場合、データ転送先を設定する際に、変数形式 ${Expression} を使用して対応する値を参照する必要があります。

上記の 2 つの例のルールでデータを TableStore のデータテーブルに転送する場合、プライマリキーの値は次のように設定できます。

• ${t}、${deviceName}、および ${location}。
• ${deviceName}、${Humidity}、および ${Temperature}。

配列の使用方法

配列式は二重引用符で囲む必要があります。$. 演算子は JSON オブジェクトを取得します。$. 演算子はオプションです。. 演算子は JSON 配列を取得します。

サポートされている条件式

SQL ステートメントのデバッグ

データ転送ルールを作成するときに [データ形式] を [JSON] に設定した場合、IoT Platform コンソールで SQL ステートメントをオンラインでデバッグできます。手順は次のとおりです。

1. SQL を記述した後、[SQL デバッグ] をクリックします。
2. [SQL デバッグ] ダイアログボックスの [デバッグパラメーター] タブで、デバッグ用のデータを入力し、[デバッグ] をクリックします。

   トピックのデータ形式に基づいて、デバッグ用のペイロードデータを入力します。データ形式は次のとおりです。

   • トピックがカスタムトピックの場合、入力するペイロードデータの形式は、トピックに報告されるデータの形式と同じである必要があります。
   • トピックが基本通信トピックまたは TSL モデル通信トピックの場合、「データ形式」をご参照ください。
   デバッグパラメーターには、プロダクト、デバイス、デバイスタグ (キーと値のペアのタグを追加または削除できます)、トピック、および ペイロードデータ が含まれます。
3. [デバッグ結果] タブをクリックして結果を表示します。デバッグ結果は JSON 形式で表示され、値が "b" の attribute('b')、値が {"timeout":"test"} の payload、値が "a" の attribute('a')、デバイス座標の attribute('coordinate')、デバイス名の deviceName、値が 1579004949668 のような timestamp などのフィールドが含まれます。