SQL データ挿入, Lindorm 挿入, Lindorm データ挿入, データ更新, upsert into - Lindorm

UPSERT は、プライマリキーが存在しない場合は行を挿入し、存在する場合は更新します。すべての UPSERT 文でプライマリキー列を指定する必要があります。UPSERT は、すべてのバージョンの LindormTable と LindormTSDB の両方でサポートされています。

リレーショナルデータベースとは異なり、Lindorm の UPSERT は、プライマリキーが重複しても失敗することはありません。行はサイレントに作成または更新されます。

書き込み動作の選択

ユースケースに適した句を選択するには、以下の表をご利用ください。

目的	構文
挿入または上書き (デフォルト)	`UPSERT INTO ... VALUES ...`
挿入のみ。行が存在する場合はスキップ	`... ON DUPLICATE KEY IGNORE`
行が存在する場合は特定の列を更新。存在しない場合は挿入	`... ON DUPLICATE KEY UPDATE col = val`
挿入のみ。行が存在する場合はエラー	`... ON DUPLICATE KEY ERROR` (LindormTable 2.7.8 以降)

説明

ON DUPLICATE KEY 句は LindormTable でのみサポートされており、CONSISTENCY パラメーターが strong に設定されているテーブルでのみ使用できます。

LindormTable と LindormTSDB の違い

同じプライマリキーを持つ 2 つの行を書き込む場合：

LindormTable — 2 回目の書き込みは、エラーなしで最初の書き込みによって書き込まれた行を上書きします。LindormTable は両方の書き込みを異なるバージョンとして保存します。標準の SELECT は各列の最新バージョンを返します。すべてのバージョンを取得するには、_l_versions_ ヒントを使用します。
LindormTSDB — 2 回目の書き込みは最初の書き込みを上書きします。バージョン管理は保持されません。

構文

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

パラメーター

ヒント式

LindormTable のみ。書き込まれる行に明示的なタイムスタンプを設定するには、_l_ts_ ヒントを使用します。

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

利用可能なすべてのヒントオプションについては、「hintOptions のパラメーター」をご参照ください。

ON DUPLICATE KEY

LindormTable のみ。書き込み前に指定された行が存在するかどうかを確認します。これは HBase の checkAndPut 操作に似ています。

重要

Lindorm SQL 2.8.8.2 以降では、NOW() を VALUES 句で使用して、現在のタイムスタンプを自動的に挿入できます。例: UPSERT INTO tb (id, ts) VALUES (1, NOW());。詳細については、「SQL のバージョン」をご参照ください。

ON DUPLICATE KEY IGNORE

行が存在する場合、書き込みはエラーなしでスキップされます。行が存在しない場合、データが挿入されます。

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

ON DUPLICATE KEY UPDATE

行が存在する場合、指定された列は指定された値に更新されます。行が存在しない場合の動作は、LindormTable のバージョンによって異なります。

バージョン	行が存在しない場合
2.7.8 より前	更新なし、エラーなし
2.7.8 以降	エラーが報告され、`VALUES` 句のデータが挿入されます

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;

ON DUPLICATE KEY ERROR

LindormTable 2.7.8 以降のみ。行が存在する場合、エラーが報告されます。行が存在しない場合、データが挿入されます。

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

例

すべての例で、次のサンプルテーブルを使用します。

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);

単一行の書き込み

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

確認： SELECT * FROM sensor;

特定の列への書き込み

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

確認： SELECT * FROM sensor;

1 つの文での複数行の書き込み

VALUES 句で各行をカンマで区切ります。

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);

確認： SELECT * FROM sensor;

同じプライマリキーを持つ行の書き込み (LindormTable のバージョン管理)

LindormTable は、インプレースで上書きする代わりに、各書き込みを新しいバージョンとして保存します。次の手順でこの仕組みを説明します。

説明

LindormTSDB では、2 回目の書き込みは最初の書き込みを上書きし、バージョン管理は保持されません。

最初の行を書き込みます。

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

データをクエリします。

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

出力：

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

同じプライマリキーで、異なる値を使用して行を 2 回目に書き込みます。

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

再度クエリします。デフォルトでは最新バージョンが返されます。

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

出力：

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

_l_versions_ ヒントを使用して、保存されているすべてのバージョンを取得します。

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

出力：

+-----------+----------+-------------------------------+-------------+----------+
| 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       |
+-----------+----------+-------------------------------+-------------+----------+

両方の書き込みが個別のバージョンとして保持されます。

次のステップ

ALTER TABLE — 既存のテーブルの CONSISTENCY パラメーターを変更して ON DUPLICATE KEY 句を有効にする
テーブル属性 (table_options) — テーブル作成時に CONSISTENCY=strong を設定します。
hintOptions のパラメーター — _l_ts_ およびその他のヒントの完全なリファレンス
SQL バージョン — Lindorm SQL バージョンを確認します