UPSERT 構文は、INSERT 構文と UPDATE 構文の組み合わせと同じように機能します。指定された行がすでに存在する場合、UPDATE 構文が実行されます。行が存在しない場合は、INSERT 構文が実行されます。UPSERT 文を実行するときは、プライマリキーとして使用される列を指定する必要があります。UPSERT 構文を使用すると、タイムスタンプ付きの個々の行または複数の行のデータをバッチで挿入できます。この場合、UPSERT 構文は INSERT 構文と同じです。
適用可能なエンジンとバージョン
UPSERT 構文は、LindormTable および LindormTSDB のすべてのバージョンに適用できます。
リレーショナルデータベースの INSERT 操作と Lindorm の UPSERT 操作の比較
Lindorm の UPSERT 操作は、リレーショナルデータベースの INSERT 操作とは異なります。
リレーショナルデータベースの INSERT 操作
指定されたプライマリキーを持つテーブルの場合、ON DUPLICATE KEY 句を指定せずに 2 つの連続した INSERT 操作を実行して同じプライマリキー値を持つデータを書き込むと、2 番目の INSERT 操作は失敗します。この場合、UPDATE 文を明示的に実行してデータを更新するか、2 番目の INSERT 操作で ON DUPLICATE KEY 句を指定する必要があります。
Lindorm の INSERT 操作
LindormTable：同じプライマリキーを持つデータを書き込むために 2 つの連続した UPSERT 操作を実行すると、2 番目の UPSERT 操作は、エラーを報告することなく、最初の UPSERT 操作によって書き込まれたデータ行を上書きします。この場合、LindormTable は、2 つの操作によって書き込まれた行を異なるバージョンとして保存します。デフォルトでは、SELECT 文を実行してデータ行をクエリすると、行の各列の最新バージョンが返されます。
LindormTSDB：同じプライマリキーを持つデータを書き込むために 2 つの連続した UPSERT 操作を実行すると、2 番目の UPSERT 操作は、最初の UPSERT 操作によって書き込まれたデータ行を上書きします。
UPSERT 構文を使用して同じプライマリキーを持つ行を挿入する方法の例については、「同じプライマリキーを持つ行を書き込む」をご参照ください。
構文
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)* ')'パラメーター
HINT 式 (hint_expression)
HINT 式は、LindormTable でのみサポートされています。
UPSERT 文で _l_ts_ ヒントを使用して、更新または挿入する行のタイムスタンプを指定できます。 _l_ts_ ヒントの詳細については、「ヒント」をご参照ください。
ON DUPLICATE KEY
ON DUPLICATE KEY 句は、LindormTable でのみサポートされています。
ON DUPLICATE KEY 句は、指定された行が存在するかどうかを確認するために使用され、HBase の CHECK AND PUT 操作に似ています。この句は、次のルールに準拠しています。
ON DUPLICATE KEY句の後にUPDATEキーワードを使用して、指定された列の値を更新できます。指定された行がすでに存在する場合、
UPDATEキーワードの後の文が実行され、指定された列の値が更新されます。指定された行が存在しない場合、データは更新されず、2.7.8 より前の LindormTable ではエラーは報告されません。ただし、LindormTable 2.7.8 以降ではエラーが報告され、
VALUES句のデータがテーブルに挿入されます。
ON DUPLICATE KEY句の後にIGNOREキーワードを使用することもできます。この場合、指定された行がすでに存在する場合、データは更新されず、エラーは報告されません。指定された行が存在しない場合、データはテーブルに書き込まれます。LindormTable 2.7.8 以降では、
ON DUPLICATE KEY句の後にERRORキーワードを使用できます。この場合、指定された行がすでに存在すると、エラーが報告されます。指定された行が存在しない場合、データはテーブルに書き込まれます。ON DUPLICATE KEY 句は、CONSISTENCY パラメーターが
strongに設定されているテーブルにのみ適用できます。 CONSISTENCY パラメーターの詳細については、「テーブル属性 (table_options)」をご参照ください。テーブルの CONSISTENCY パラメーターの値を変更するには、「ALTER TABLE」をご参照ください。
Lindorm SQL 2.8.8.2 以降では、UPSERT 文を実行して現在のタイムスタンプを自動的に挿入する場合、文に NOW() 関数を含めることができます。例：UPSERT INTO tb (id, ts) VALUES (1, NOW()); 。 Lindorm SQL のバージョンの確認方法については、「SQL バージョン」をご参照ください。
例
次の例では、サンプルテーブル sensor は、次の文を実行することによって作成されます。
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; 文を実行して、データがテーブルに書き込まれたかどうかを確認できます。
文で ON DUPLICATE KEY IGNORE 句を使用してテーブルにデータを書き込む
LindormTable のテーブルにデータを書き込むには、ON DUPLICATE KEY IGNORE 句を使用します。
device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 で指定された条件を満たす行が存在する場合、データは書き込まれません。行が存在しない場合、データはテーブルに書き込まれます。
UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY IGNORE;結果の確認
SELECT * FROM sensor; 文を実行して、データがテーブルに書き込まれたかどうかを確認できます。
文で ON DUPLICATE KEY UPDATE 句を使用してテーブルのデータを更新する
LindormTable のテーブルにデータを書き込むには、ON DUPLICATE KEY UPDATE 句を使用します。
V2.7.8 より前の LindormTable：
device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2で指定された条件を満たす行が存在する場合、temperature列の値は30に更新されます。行が存在しない場合、temperature列の値は更新されず、エラーは報告されません。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 以降：
device_id='F07A1260'，region='north-cn'，time='2021-04-22 15:33:10'，temperature=13.2で指定された条件を満たす行が存在する場合、temperature列の値は30に更新されます。行が存在しない場合、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;
結果の確認
SELECT * FROM sensor; 文を実行して、データがテーブルに書き込まれたかどうかを確認できます。
文で ON DUPLICATE KEY ERROR 句を使用してテーブルにデータを書き込む
LindormTable 2.7.8 以降でテーブルにデータを書き込むには、ON DUPLICATE KEY ERROR 句を使用します。
device_id='F07A1260', region='north-cn', time='2021-04-22 15:33:10', temperature=13.2 で指定された条件を満たす行が存在する場合、エラーが報告されます。行が存在しない場合、データはテーブルに書き込まれます。
UPSERT INTO sensor(device_id,region,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:10',13.2) ON DUPLICATE KEY ERROR;タイムスタンプ付きのデータを書き込む
LindormTable のテーブル sensor にデータ行を書き込み、行のタイムスタンプを 111232 に設定します。
UPSERT /*+ _l_ts_(111232) */ INTO sensor (device_id ,region ,time,temperature) VALUES('F07A1260','north-cn','2021-04-22 15:33:00',12.1);結果の確認
SELECT * FROM sensor; 文を実行して、データがテーブルに書き込まれたかどうかを確認できます。
複数のデータ行を同時に書き込む
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; 文を実行して、データがテーブルに書き込まれたかどうかを確認できます。
同じプライマリキーを持つ行を書き込む
UPSERT 文を使用して、LindormTable に同じプライマリキーを持つ 2 つのデータ行を連続して書き込みます。次に、ヒントを使用して、テーブルに書き込まれたデータをクエリします。
LindormTSDB で同じプライマリキーを持つデータを書き込むために 2 つの連続した UPSERT 操作を実行すると、2 番目の UPSERT 操作は、最初の UPSERT 操作によって書き込まれたデータ行を上書きします。
最初にデータを書き込みます。
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);2 番目の操作によって書き込まれたデータをクエリします。
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 | +-----------+----------+-------------------------------+-------------+----------+2 つの操作の device_id、region、および time プライマリキー列の値は同じです。結果によると、最初の操作によって書き込まれた temperature 列の値は、2 番目の操作によって書き込まれた値によって上書きされます。
_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 | +-----------+----------+-------------------------------+-------------+----------+結果によると、2 つの操作によって書き込まれた値は 2 つのバージョンとして保存されます。