Tulis pernyataan SQL untuk aturan forwarding data - IoT Platform - Alibaba Cloud - IoT Platform

Saat membuat aturan penerusan data, Anda harus menulis pernyataan SQL untuk mengurai dan memproses data JSON yang dilaporkan oleh perangkat. Data dalam format biner diteruskan langsung tanpa diurai. Topik ini menjelaskan cara menulis ekspresi SQL untuk aturan penerusan data.

Ekspresi SQL

Penting Jika pengenal model Thing Specification Language dimulai dengan angka, Anda tidak dapat menggunakan ekspresi SQL untuk mengakses model tersebut. Versi sebelumnya dari fitur penerusan data tidak mendukung penerusan data dari model Thing Specification Language yang pengenalnya dimulai dengan angka.

Anda dapat menggunakan versi baru fitur penerusan data untuk mengedit skrip guna mengakses model Thing Specification Language yang pengenalnya dimulai dengan angka dan meneruskan datanya. Untuk informasi selengkapnya, lihat Panduan skrip.

Data JSON dapat dipetakan ke tabel virtual. Dalam pemetaan ini, kunci berkorespondensi dengan kolom tabel dan nilai berkorespondensi dengan nilai kolom tersebut. Hal ini memungkinkan Anda menggunakan SQL untuk pemrosesan data. Gambar berikut menunjukkan ekspresi SQL untuk aturan penerusan data.

Berikut adalah contoh SQL untuk aturan penerusan data:

Contoh SQL untuk memproses data dari topik kustom.
Sensor lingkungan mengumpulkan data suhu, kelembapan, dan tekanan atmosfer. Perangkat melaporkan data berikut ke topik kustom `/a1hRrzD****/+/user/update`:
```
{
    "temperature":25.1,
    "humidity":65,
    "pressure":101.5,
    "location":"***,***"
}
```
Untuk memicu aturan saat suhu lebih besar dari 38°C dan memfilter informasi nama perangkat, suhu, serta lokasi, gunakan pernyataan SQL berikut:
```
SELECT temperature as t, deviceName() as deviceName, location 
FROM "/a1hRrzD****/+/user/update" 
WHERE temperature > 38
```
Contoh SQL untuk memproses data dari topik komunikasi dasar dan topik komunikasi model Thing Specification Language. Saat data dari topik komunikasi dasar dan topik komunikasi model Thing Specification Language diteruskan ke mesin aturan, mesin aturan akan mengurai data tersebut. Untuk informasi lebih lanjut mengenai format data setelah penguraian, lihat Format data. Sensor mendefinisikan dua properti kustom: Current Humidity (pengenal: CurrentHumidity, tipe data: float, rentang nilai: 0 hingga 100) dan Current Temperature (pengenal: CurrentTemperature, tipe data: float, rentang nilai: -40 hingga 120).
Setelah mesin aturan mengurai data properti suhu dan kelembapan yang dilaporkan oleh sensor, data tersebut memiliki format sebagai berikut:
```
{
    "deviceType": "TemperatureHumidityDetector", 
    "iotId": "N5KURkKdibnZvSls****000100", 
    "productKey": "a15NNfl****", 
    "gmtCreate": 1564569974224, 
    "deviceName": "N5KURkKdibnZvSls3Yfx", 
    "items": {
        "CurrentHumidity": {
            "value": 70, 
            "time": 1564569974229
        }, 
        "CurrentTemperature": {
            "value": 23.5, 
            "time": 1564569974229
        }
    }
}
```
Penting Dalam kueri SQL, Anda harus menggunakan items.${PropertyIdentifier}.value untuk mengakses data properti tertentu.

Untuk memicu aturan saat suhu lebih besar dari 38°C dan memfilter nama perangkat, suhu saat ini, serta kelembapan saat ini, gunakan pernyataan SQL berikut:
```
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
```
Jika properti tersebut termasuk dalam modul kustom, seperti testFB, pengenal properti memiliki format ${ModuleIdentifier}:${PropertyIdentifier}. Untuk mengakses data properti tertentu, Anda harus menggunakan tanda kutip ganda (""). Pernyataan SQL-nya adalah sebagai berikut:
```
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

Untuk format data JSON
Bidang dalam pernyataan SELECT dapat berupa nilai kunci dari muatan JSON pada pesan yang dilaporkan. Anda juga dapat menggunakan fungsi bawaan SQL, seperti deviceName(). Untuk informasi selengkapnya mengenai fungsi bawaan SQL mesin aturan, lihat Daftar fungsi.

Anda dapat menggunakan * dalam kombinasi dengan fungsi. Subkueri tidak didukung.

Data JSON yang dilaporkan dapat berupa array atau objek JSON bersarang. Pernyataan SQL mendukung JSONPath untuk mengambil nilai properti. Misalnya, untuk {a:{key1:v1, key2:v2}}, Anda dapat menggunakan a.key2 untuk mengambil nilai v2. Saat menggunakan variabel, perhatikan perbedaan antara tanda kutip tunggal dan ganda. Tanda kutip tunggal menunjukkan konstanta. Tanda kutip ganda atau tanpa tanda kutip menunjukkan variabel. Misalnya, jika Anda menggunakan tanda kutip tunggal untuk 'a.key2', nilainya adalah string a.key2.

Dalam contoh SQL pada topik ini:
- Dalam pernyataan SQL SELECTSELECT temperature as t, deviceName() as deviceName, location, temperature dan location adalah bidang dari data yang dilaporkan, sedangkan deviceName() adalah fungsi bawaan SQL.
- Dalam pernyataan SELECT SELECT deviceName() as deviceName, items.CurrentHumidity.value as Humidity, items.CurrentTemperature.value as Temperature untuk memproses data dari topik pelaporan properti, items.CurrentHumidity.value dan items.CurrentTemperature.value adalah bidang dari data properti yang dilaporkan oleh modul default. deviceName() adalah fungsi bawaan SQL.
  Catatan items.testFB:CurrentHumidity.value dan items.testFB:CurrentTemperature.value adalah bidang dari data properti yang dilaporkan oleh modul kustom.
Untuk data biner
- Anda dapat memasukkan * untuk meneruskan data secara langsung. Anda tidak dapat menggunakan fungsi setelah *.
- Anda dapat menggunakan fungsi bawaan. Misalnya, gunakan fungsi to_base64(*) untuk mengonversi data muatan biner asli menjadi string Base64 dan mengekstraknya. Gunakan fungsi deviceName() untuk mengekstrak nama perangkat.

Catatan Pernyataan SELECT dapat berisi maksimal 50 bidang.

FROM

Dalam klausa FROM, Anda dapat menentukan topik untuk mencocokkan topik sumber pesan perangkat yang ingin diproses. Untuk level nama perangkat dalam topik, Anda dapat menggunakan karakter wildcard tanda plus (+). Karakter ini merepresentasikan semua perangkat pada level saat ini, artinya semua perangkat di bawah produk tersebut. Saat menentukan topik kustom, Anda juga dapat menggunakan karakter wildcard tanda pagar (#). Karakter ini merepresentasikan level saat ini dan semua level berikutnya dalam topik. Untuk informasi selengkapnya mengenai karakter wildcard, lihat Gunakan topik kustom untuk komunikasi.

Saat pesan dari topik yang ditentukan tiba, muatannya diurai sebagai JSON dan diproses berdasarkan pernyataan SQL. Jika format pesan tidak valid, pesan tersebut diabaikan. Anda dapat menggunakan fungsi topic() untuk mereferensikan nilai topik tertentu.

Dalam contoh SQL di atas:

FROM "/a1hRrzD****/+/user/update" menunjukkan bahwa pernyataan SQL hanya memproses pesan dari topik kustom `/a1hRrzD****/+/user/update`.
FROM "/sys/a15NNfl****/N5KURkKdibnZvSls3Yfx/thing/event/property/post" menunjukkan bahwa pernyataan SQL hanya memproses pesan dari topik tempat perangkat N5KURkKdibnZvSls3Yfx melaporkan properti.

WHERE

Untuk format data JSON
Klausa WHERE menentukan kondisi pemicu aturan, yaitu ekspresi kondisional. Subkueri tidak didukung. Bidang yang dapat digunakan dalam klausa WHERE sama dengan yang ada dalam pernyataan SELECT. Saat pesan diterima dari topik yang sesuai, pernyataan WHERE dievaluasi untuk menentukan apakah aturan harus dipicu. Untuk informasi selengkapnya mengenai ekspresi kondisional, lihat bagian Ekspresi kondisional yang didukung di bawah.

Dalam dua contoh di atas, pernyataan kondisional WHERE temperature > 38 menunjukkan bahwa aturan hanya dipicu saat suhu lebih besar dari 38°C.
Untuk format data biner
Untuk data biner, klausa WHERE hanya mendukung fungsi bawaan dan ekspresi kondisional. Anda tidak dapat menggunakan bidang dari muatan.

Hasil SQL

Setelah pernyataan SQL dieksekusi, hasil SQL dihasilkan untuk langkah penerusan berikutnya. Jika terjadi error selama penguraian muatan, aturan gagal dijalankan.

Jika Anda ingin meneruskan data ke Tablestore, Anda harus menggunakan format variabel ${Expression} untuk mereferensikan nilai yang sesuai saat menetapkan tujuan penerusan data.

Jika aturan dari dua contoh di atas perlu meneruskan data ke tabel data di Tablestore, nilai untuk kunci primer dapat dikonfigurasi sebagai berikut:

${t}, ${deviceName}, dan ${location}.
${deviceName}, ${Humidity}, dan ${Temperature}.

Penggunaan array

Ekspresi array harus diapit tanda kutip ganda. Operator $. mengambil objek JSON. Operator $. bersifat opsional. Operator . mengambil array JSON.

Misalnya, jika pesan perangkat adalah {"a":[{"v":0},{"v":1},{"v":2}]}, ekspresi berbeda menghasilkan hasil sebagai berikut:

"a[0]" mengembalikan {"v":0}
"$.a[0]" mengembalikan {"v":0}
".a[0]" mengembalikan [{"v":0}]

"a[1].v" mengembalikan 1
"$.a[1].v" mengembalikan 1
".a[1].v" mengembalikan [1]

Ekspresi kondisional yang didukung

Operator	Deskripsi	Contoh
=	Sama dengan	color = 'red'
<>	Tidak sama dengan	color <> 'red'
AND	Logika AND	color = 'red' AND siren = 'on'
OR	Logika OR	color = 'red' OR siren = 'on'
+	Penjumlahan aritmetika	4 + 5
-	Pengurangan aritmetika	5 - 4
/	Pembagian	20 / 4
*	Perkalian	5 * 4
%	Modulo	20 % 6
<	Kurang dari	5 < 6
<=	Kurang dari atau sama dengan	5 <= 6
>	Lebih dari	6 > 5
>=	Lebih dari atau sama dengan	6 >= 5
Pemanggilan fungsi	Mendukung fungsi. Untuk informasi selengkapnya, lihat Daftar fungsi.	deviceId()
Ekspresi properti JSON	Mengekstraksi properti dari muatan pesan menggunakan ekspresi JSON.	state.desired.color,a.b.c[0].d
CASE … WHEN … THEN … ELSE …END	Ekspresi case. Penyisipan (nesting) tidak didukung.	CASE col WHEN 1 THEN 'Y' WHEN 0 THEN 'N' ELSE '' END as flag
IN	Hanya mendukung enumerasi. Subkueri tidak didukung.	Contoh: where a in(1,2,3). Format berikut tidak didukung: where a in(select xxx).
like	Mencocokkan karakter. Hanya karakter wildcard `%` yang didukung, yang mencocokkan string apa pun. like: Mengkueri data yang mengandung karakter tertentu. not like: Mengkueri data yang tidak mengandung karakter tertentu.	where c1 like '%abc'
not like		where c1 not like '%def%'

Debug pernyataan SQL

Jika Anda mengatur Data Format ke JSON saat membuat aturan penerusan data, Anda dapat melakukan debug pernyataan SQL secara online di Konsol Platform IoT. Prosedurnya sebagai berikut.

Setelah menulis SQL, klik SQL Debug.
Pada kotak dialog SQL Debugging, di tab Debugging Parameters, masukkan data untuk debugging lalu klik Debug.
Masukkan data muatan untuk debugging berdasarkan format data topik. Format data dijelaskan sebagai berikut:
- Jika topik adalah topik kustom, format data muatan yang Anda masukkan harus sama dengan format data yang dilaporkan ke topik tersebut.
- Jika topik adalah topik komunikasi dasar atau topik komunikasi model Thing Specification Language, lihat Format data.
Parameter debugging mencakup Product, Device, Device Tag (Anda dapat menambah atau menghapus tag pasangan kunci-nilai), Topic, dan Payload Data.
Debug ResultKlik tab untuk melihat hasilnya. Hasil debugging ditampilkan dalam format JSON dan mencakup bidang seperti attribute('b') dengan nilai "b", payload dengan nilai {"timeout":"test"}, attribute('a') dengan nilai "a", attribute('coordinate') untuk koordinat perangkat, deviceName untuk nama perangkat, dan timestamp dengan nilai seperti 1579004949668.