すべてのプロダクト
Search
ドキュメントセンター

IoT Platform:SQL 文

最終更新日:Feb 06, 2024

データ転送ルールを作成するときは、デバイスから送信されたJSON形式のデータを解析および処理するためのSQL文を記述する必要があります。 IoT Platformはバイナリデータを解析しません。 バイナリデータは指定された宛先に渡されます。 このトピックでは、データ転送ルールのSQL文を記述する方法について説明します。

SQL 文

重要 旧バージョンのデータ転送機能を使用する場合、識別子が数字で始まるThing Specification Language (TSL) モデルのデータを転送するSQL文を記述することはできません。

新しいバージョンのデータ転送機能を使用して、このようなTSLモデルのデータを転送するスクリプトを作成できます。 詳細については、「スクリプトの書き込み」をご参照ください。

JSONデータは仮想テーブルにマッピングできます。 JSONデータレコードのキーは、列名に対応します。 JSONデータレコードの値は、列の値に対応します。 JSONデータレコードが仮想テーブルにマップされた後、JSONデータレコードはSQL文を記述して処理できます。 データ転送ルールのSQL文の形式を次の図に示します。

SQL statements

例:

  • 次のSQL例は、カスタムトピックのデータを処理する方法を示しています。

    環境センサを使用して、温度、湿度、および大気圧データを収集することができる。 次のコードは、デバイスによって /a1hRrzD ****/+/user/updateカスタムトピックに送信されるデータを示しています。

    {
        "温度":25.1、
        "湿度":65、
        "pressure":101.5、
        "location":"***,***"
    } 

    温度が摂氏38度より高い場合、ルールがトリガーされ、デバイス名、温度データ、および場所データが返されます。 このユースケースを実装するには、次のSQL文を使用します。

    SELECT temperature as t, deviceName() as deviceName, location
    「 /a1hRrzD ****/+/user/update」から
    どこ温度> 38 
  • 次のSQLの例は、基本的な通信トピックとTSLベースの通信トピックのデータを処理する方法を示しています。 データは、基本的な通信トピックおよびThing Specification Language (TSL) ベースの通信トピックからルールエンジンに転送できます。 データが受信された後、ルールエンジンはデータを解析する。 解析されたデータの形式の詳細については、「データ形式」をご参照ください。

    たとえば、次の図に示すように、温度および湿度センサーには複数のプロパティがあります。

    Properties

    次のサンプルコードは、温度と湿度センサーによって送信された温度と湿度のデータをルールエンジンが解析した後の結果を示しています。

    {
        "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.${Property identifier}.value変数を使用して、指定されたプロパティのデータにアクセスする必要があります。

    温度が摂氏38度より高い場合、ルールがトリガーされ、デバイス名、現在の温度データ、および現在の湿度データが返されます。 このユースケースを実装するには、次のSQL文を使用します。

    Write an SQL Statement
    SELECTdeviceName() はdeviceName、items.CurrentHumidity.valueはHumidity、items.CurrentTemperature.valueはTemperatureとして
    「 /sysa15NNfl ****/N5KUR ***/thing/event/property/post」から
    WHERE items.CurrentTemperature.value > 38 

    プロパティがtestFBなどのカスタムモジュールに属している場合、プロパティ識別子の形式は ${モジュール識別子 }:${ プロパティ識別子} です。 データを照会するプロパティを指定する場合は、プロパティ識別子を二重引用符 ("") で囲む必要があります。 次のSQL文は、プロパティデータのクエリ方法の例を示しています。

    SELECTdeviceName() をdeviceName、"items.testFB:CurrentHumidity.value" をHumidity、"items.testFB:CurrentTemperature.value" をTemperatureとして
    「 /sysa15NNfl ****/N5KUR ***/thing/event/property/post」から
    WHERE "items.testFB:CurrentTemperature.value" > 38 

SELECT

  • 次の例は、ラベルを含むJSONデータを示しています。

    送信されたメッセージのペイロードの解析結果をSELECTステートメントのフィールドとして使用できます。 解析結果には、JSONデータのキーと値が含まれます。 deviceName() などのSQL組み込み関数をSELECTステートメントのフィールドとして使用することもできます。 ルールエンジンのSQL組み込み関数の詳細については、「関数」をご参照ください。

    関数と一緒にアスタリスク (*) を使用できます。 SQLサブクエリはサポートされていません。

    JSON形式で送信されるデータは、配列またはネストされたJSONデータです。 JSONPathを使用して、SQL文からプロパティ値を取得できます。 たとえば、a.key2を使用して、{a:{key1:v1, key2:v2}} ステートメントからv2値を取得できます。 SQL文で変数を指定するときは、一重引用符 (') と二重引用符 (") の違いに注意してください。 各定数は、一重引用符 ('') のペアで囲まれています。 各変数は、二重引用符 ("") のペアで囲まれています。 変数は、引用符で囲まれずに記述されてもよい。 たとえば、'a.key2' は、値がa.key2である定数を示します。

    このSQLの例では:

    • SELECT temperature as t, deviceName() as deviceName, locationステートメントは、カスタムトピックのデータを処理するために使用されます。 温度および位置フィールドは、提出されたデータから得られる。 deviceName() はSQL組み込み関数です。
    • 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() 関数は、デバイスの名前を抽出します。
説明 SELECTステートメントには、最大50個のフィールドを含めることができます。

FROM

FROM句でトピックを指定できます。 このトピックは、処理するデバイスメッセージを取得するソースです。 このトピックでは、デバイス名カテゴリのワイルドカードとしてプラス記号 (+) を指定できます。 プラス記号 (+) は、現在のレベルのすべてのカテゴリを表します。 この場合、プラス記号 (+) は指定された製品のすべてのデバイスを表します。 カスタムトピックを指定した後、数字記号 (#) をワイルドカードとして指定できます。 数字記号 (#) は、現在のレベルと後続のレベルのすべてのカテゴリを表します。 ワイルドカードの詳細については、「通信にカスタムトピックを使用する」をご参照ください。

指定したトピックからメッセージを受信すると、メッセージのペイロードデータがJSON形式のデータに変換されます。 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句は、ルールをトリガーする条件として使用されます。 SQLサブクエリはサポートされていません。 WHERE句で使用できるフィールドは、SELECTステートメントで使用できるフィールドと同じです。 指定されたトピックからメッセージを受信すると、WHERE句を使用して取得された結果を使用して、ルールがトリガーされているかどうかを確認します。 詳細については、このトピックの「サポートされているWHERE式」セクションを参照してください。

    上記の例では、WHERE温度> 38の条件は、温度が摂氏38度より高い場合にのみルールがトリガーされることを示しています。

  • バイナリデータ

    受信したメッセージがバイナリデータで構成されている場合は、WHERE句の組み込み関数と条件だけを使用できます。 メッセージのペイロードのフィールドは使用できません。

SQL処理結果

SQL文が実行された後、クエリ結果を転送できます。 IoT Platformが受信したメッセージのペイロードを解析するときにエラーが発生した場合、ルールは実行されません。

データをTablestore (OTS) に転送できます。 この場合、データ転送先を指定するときに ${expression} 変数を使用して必須の値を指定する必要があります。

上記のSQLの例では、関連するルールを使用してデータをOTSテーブルに転送する場合、次の変数を主キーとして指定できます。

  • ${t}${deviceName} 、および ${loaction} です。
  • ${deviceName}${Humidity} 、および ${Temperature} です。

配列

各配列式を二重引用符 ("") で囲みます。 $を使用します。 JSONオブジェクトを取得します。 $. 削除することができます。 を使用します。 JSON配列を取得します。

デバイスメッセージが {"a":[{"v":0 },{ "v":1 },{ "v":2}]} の場合、指定した式に基づいて次の結果が得られます。
  • "a[0]" の結果は {"v":0} です。
  • "$.a[0]" の結果は {"v":0} です。
  • ".a[0]" の結果は [{"v":0}] です。
  • 「a[1].v」の結果は1です。
  • 「 $.a[1].v」の結果は1です。
  • ".a[1].v" の結果は [1] です。

サポートされるWHERE条件

演算子説明
=等しいcolor = 'red'
<>等しくないcolor <> 'red'
AND論理的およびcolor = 'red' とサイレン='on'
OR論理ORcolor = 'red' またはサイレン='on'
+追加4 + 5
-減算5 - 4
/分割20 / 4
*乗算5 * 4
%残りを返します。20% 6
<より小さい5 < 6
<=以下5 <= 6
>より大きい6 > 5
>=以上6 >= 5
関数の呼び出し関数。 詳細については、「Functions」をご参照ください。 deviceId()
JSON形式で指定されたフィールドメッセージペイロードから属性を抽出し、JSON形式で属性を表すことができます。 state.desired.color,a.b.c[0].d
ケース… そのとき……ケース式。 ネストされた式はサポートされません。 ケースcol 1が「Y」のとき0が「N」以外のときフラグとして終了
IN列挙体のみがサポートされています。 サブクエリはサポートされていません。 たとえば、WHERE a IN (1, 2, 3) を使用できます。 ただし、WHERE a IN (select xxx) を使用することはできません。
のようにこの演算子は、文字列を照合するために使用されます。 LIKE演算子を使用する場合、ワイルドカードとしてパーセント記号 (%) のみを使用して任意の文字列を指定できます。
  • like: 指定された文字列を含むデータを検索します。
  • not like: 指定された文字列を除外するデータを検索します。
「 % abc」のようなc1
好きではないc1が '% def %' を好まない場合

デバッグSQL文

データ転送ルールの作成時に [データ型] フィールドで [JSON] を選択した場合、IoT PlatformコンソールでSQL文をデバッグできます。 手順:

  1. SQL文の作成後、[デバッグSQL] をクリックします。
  2. [デバッグSQL] パネルで、[デバッグパラメーター] タブをクリックします。 表示されるタブで、必要なデバッグデータを入力し、[デバッグ] をクリックします。

    トピックによって送信されたデータのタイプに基づいて、デバッグに必要なペイロードデータを入力します。 データ型の説明:

    • カスタムトピックを使用する場合、指定されるペイロードデータのタイプは、カスタムトピックによって送信されるデータのタイプと同じである必要があります。
    • 基本的なコミュニケーショントピックまたはTSLベースのコミュニケーショントピックを使用する場合は、「データ形式」をご参照ください。
    SQLtest1
  3. [コミッショニング結果] タブをクリックして結果を表示します。 SQLtestResult