Gunakan pernyataan SQL untuk menyisipkan data - Lindorm

Sintaks UPSERT bekerja seperti kombinasi antara sintaks INSERT dan UPDATE. Jika baris yang ditentukan sudah ada, sintaks UPDATE dijalankan. Jika baris tidak ada, sintaks INSERT dijalankan. Saat menjalankan pernyataan UPSERT, Anda harus menentukan kolom yang digunakan sebagai kunci utama. Sintaks UPSERT dapat digunakan untuk menyisipkan satu atau beberapa baris data dengan timestamp dalam satu batch. Dalam hal ini, sintaks UPSERT setara dengan sintaks INSERT.

Mesin dan versi yang berlaku

Sintaks UPSERT berlaku untuk semua versi LindormTable dan LindormTSDB.

Perbandingan antara operasi INSERT di database rasional dan operasi UPSERT di Lindorm

Operasi UPSERT di Lindorm berbeda dari operasi INSERT di database rasional.

Operasi INSERT di database rasional
Untuk tabel dengan kunci utama yang ditentukan, jika Anda melakukan dua operasi INSERT berturut-turut tanpa menentukan klausa ON DUPLICATE KEY untuk menulis data dengan nilai kunci utama yang sama, operasi INSERT kedua gagal. Dalam hal ini, Anda harus secara eksplisit menjalankan pernyataan UPDATE untuk memperbarui data atau menentukan klausa ON DUPLICATE KEY dalam operasi INSERT kedua.
Operasi INSERT di Lindorm
- LindormTable: Jika Anda melakukan dua operasi UPSERT berturut-turut untuk menulis data dengan kunci utama yang sama, operasi UPSERT kedua akan menimpa baris data yang ditulis oleh operasi UPSERT pertama tanpa melaporkan kesalahan. Dalam hal ini, LindormTable menyimpan baris yang ditulis oleh kedua operasi sebagai versi yang berbeda. Secara default, ketika pernyataan SELECT dijalankan untuk menanyakan baris data, versi terbaru dari setiap kolom dalam baris dikembalikan.
- LindormTSDB: Jika Anda melakukan dua operasi UPSERT berturut-turut untuk menulis data dengan kunci utama yang sama, operasi UPSERT kedua akan menimpa baris data yang ditulis oleh operasi UPSERT pertama.

Untuk contoh tentang cara menggunakan sintaks UPSERT untuk menyisipkan baris dengan kunci utama yang sama, lihat Tulis baris dengan kunci utama yang sama.

Sintaks

upsert_statement   ::= { UPSERT | INSERT } [ hint_expression ] 
                       INTO table_identifier columns_delaration
                       VALUES value_list ( ',' value_list)*
                       [ ON DUPLICATE KEY column_identifier = 
                         value_literal | IGNORE ]
columns_delaration ::=  '(' column_identifier ( ',' column_identifier)* ')'
value_list         ::=  '(' value_expression( ',' value_expression)* ')'

Parameter

Ekspresi HINT (hint_expression)

Ekspresi HINT hanya didukung oleh LindormTable.

Anda dapat menggunakan hint _l_ts_ dalam pernyataan UPSERT untuk menentukan timestamp untuk baris yang ingin Anda perbarui atau sisipkan. Untuk informasi lebih lanjut tentang hint _l_ts_, lihat Parameter hintOptions.

ON DUPLICATE KEY

Klausa ON DUPLICATE KEY hanya didukung oleh LindormTable.

Klausa ON DUPLICATE KEY digunakan untuk memeriksa apakah baris yang ditentukan ada dan mirip dengan operasi CHECK AND PUT di HBase. Klausa ini mematuhi aturan berikut:

Anda dapat menggunakan klausa ON DUPLICATE KEY diikuti oleh kata kunci UPDATE untuk memperbarui nilai di kolom yang ditentukan.
- Jika baris yang ditentukan sudah ada, pernyataan setelah kata kunci UPDATE dijalankan untuk memperbarui nilai kolom yang ditentukan.
- Jika baris yang ditentukan tidak ada, data tidak diperbarui dan tidak ada kesalahan yang dilaporkan di LindormTable sebelum versi 2.7.8. Namun, kesalahan dilaporkan dan data dalam klausa VALUES disisipkan ke tabel di LindormTable 2.7.8 dan yang lebih baru.
Anda juga dapat menggunakan klausa ON DUPLICATE KEY diikuti oleh kata kunci IGNORE. Dalam hal ini, jika baris yang ditentukan sudah ada, tidak ada data yang diperbarui dan tidak ada kesalahan yang dilaporkan. Jika baris yang ditentukan tidak ada, data ditulis ke tabel.
Di LindormTable 2.7.8 dan yang lebih baru, Anda dapat menggunakan klausa ON DUPLICATE KEY diikuti oleh kata kunci ERROR. Dalam hal ini, jika baris yang ditentukan sudah ada, kesalahan dilaporkan. Jika baris yang ditentukan tidak ada, data ditulis ke tabel.
Klausa ON DUPLICATE KEY hanya berlaku untuk tabel tempat parameter CONSISTENCY disetel ke strong. Untuk informasi lebih lanjut tentang parameter CONSISTENCY, lihat Atribut tabel (table_options). Untuk mengubah nilai parameter CONSISTENCY untuk sebuah tabel, lihat ALTER TABLE.

Penting

Di Lindorm SQL 2.8.8.2 dan yang lebih baru, jika Anda ingin menjalankan pernyataan UPSERT untuk secara otomatis menyisipkan timestamp saat ini, Anda dapat menyertakan fungsi NOW() ke dalam pernyataan tersebut. Contoh: UPSERT INTO tb (id, ts) VALUES (1, NOW());. Untuk informasi lebih lanjut tentang cara melihat versi Lindorm SQL, lihat Versi SQL.

Contoh

Dalam contoh-contoh berikut, tabel sampel sensor dibuat dengan menjalankan pernyataan berikut:

CREATE TABLE sensor (
    device_id VARCHAR NOT NULL,
    region VARCHAR NOT NULL,
    time TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity BIGINT,
    PRIMARY KEY(device_id, region, time)
)WITH(VERSIONS=2);

Tulis data ke tabel

UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1,45);

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Tulis data ke kolom tertentu

UPSERT INTO sensor(device_id, region, time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2);

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Gunakan klausa ON DUPLICATE KEY IGNORE dalam pernyataan untuk menulis data ke tabel

Gunakan klausa ON DUPLICATE KEY IGNORE untuk menulis data ke tabel di LindormTable.

Jika baris yang memenuhi kondisi yang ditentukan oleh device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 ada, tidak ada data yang ditulis. Jika baris tidak ada, data ditulis ke tabel.

UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY IGNORE;

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Gunakan klausa ON DUPLICATE KEY UPDATE dalam pernyataan untuk memperbarui data di tabel

Gunakan klausa ON DUPLICATE KEY UPDATE untuk menulis data ke tabel di LindormTable.

LindormTable sebelum V2.7.8:
Jika baris yang memenuhi kondisi yang ditentukan oleh device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 ada, nilai kolom temperature diperbarui menjadi 30. Jika baris tidak ada, nilai kolom temperature tidak diperbarui dan tidak ada kesalahan yang dilaporkan.
```
UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY UPDATE temperature = 30;
```
LindormTable 2.7.8 dan yang lebih baru:
Jika baris yang memenuhi kondisi yang ditentukan oleh device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 ada, nilai kolom temperature diperbarui menjadi 30. Jika baris tidak ada, data dalam klausa VALUES disisipkan ke tabel.
```
UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY UPDATE temperature = 30;
```

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Gunakan klausa ON DUPLICATE KEY ERROR dalam pernyataan untuk menulis data ke tabel

Gunakan klausa ON DUPLICATE KEY ERROR untuk menulis data ke tabel di LindormTable 2.7.8 dan yang lebih baru.

Jika baris yang memenuhi kondisi yang ditentukan oleh device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 ada, kesalahan dilaporkan. Jika baris tidak ada, data ditulis ke tabel.

UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY ERROR;

Tulis data dengan timestamp

Tulis satu baris data ke tabel sensor di LindormTable dan atur timestamp baris tersebut menjadi 111232.

UPSERT /*+ _l_ts_(111232) */ INTO sensor (device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1);

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Tulis beberapa baris data pada saat yang bersamaan

UPSERT INTO sensor (device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:20',10.6), ('F07A1261','south-cn','2021-04-22 15:33:00',18.1), ('F07A1261','south-cn','2021-04-22 15:33:10',19.7);

Verifikasi hasilnya

Anda dapat menjalankan pernyataan SELECT * FROM sensor; untuk memeriksa apakah data telah ditulis ke tabel.

Tulis baris dengan kunci utama yang sama

Gunakan pernyataan UPSERT untuk menulis dua baris data berturut-turut dengan kunci utama yang sama di LindormTable. Kemudian, gunakan hint untuk menanyakan data yang ditulis ke tabel.

Catatan

Jika Anda melakukan dua operasi UPSERT berturut-turut untuk menulis data dengan kunci utama yang sama di LindormTSDB, operasi UPSERT kedua akan menimpa baris data yang ditulis oleh operasi UPSERT pertama.

Tulis data untuk pertama kalinya.

UPSERT INTO sensor(device_id ,region ,time,temperature,humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2,45);

Tanyakan data yang ditulis oleh operasi pertama.

SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn';

Contoh keluaran:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
+-----------+----------+-------------------------------+-------------+----------+

Tulis data untuk kedua kalinya.

UPSERT INTO sensor(device_id ,region ,time,temperature,humidity) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',16.7,52);

Tanyakan data yang ditulis oleh operasi kedua.

SELECT * FROM sensor WHERE device_id='F07A1260' AND region='north-cn';

Contoh keluaran:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
+-----------+----------+-------------------------------+-------------+----------+

Nilai kolom kunci utama device_id, region, dan time dalam kedua operasi tersebut sama. Menurut hasilnya, nilai kolom temperature yang ditulis oleh operasi pertama ditimpa oleh nilai yang ditulis oleh operasi kedua.

Gunakan hint _l_versions_ untuk menanyakan semua versi data yang ditulis.

SELECT /*+ _l_versions_(2)  */ device_id, region, time,temperature,humidity FROM sensor WHERE device_id='F07A1260';

Contoh keluaran:

+-----------+----------+-------------------------------+-------------+----------+
| device_id |  region  |             time              | temperature | humidity |
+-----------+----------+-------------------------------+-------------+----------+
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 16.7        | 52       |
| F07A1260  | north-cn | 2021-04-22 15:33:10 +0000 UTC | 13.2        | 45       |
+-----------+----------+-------------------------------+-------------+----------+

Menurut hasilnya, nilai yang ditulis oleh kedua operasi disimpan sebagai dua versi.