INSERT OVERWRITE SELECT を使用してデータを上書きする方法 - AnalyticDB

このトピックでは、AnalyticDB for MySQL のテーブルにデータを書き込むための高性能なメソッドである INSERT OVERWRITE SELECT について説明します。利用シーン、仕組み、注意事項、構文、非同期書き込みについて説明します。

仕組み

INSERT OVERWRITE SELECT 文は、まずパーティションから既存のデータをすべてクリアし、その後、新しいデータをバッチで書き込みます。

ターゲットテーブルがパーティションテーブルの場合、INSERT OVERWRITE SELECT は新しいデータが属するパーティションのみを上書きします。他のパーティションは影響を受けません。
ターゲットテーブルが非パーティションテーブルの場合、INSERT OVERWRITE SELECT はテーブル内の既存のデータをすべてクリアし、その後、新しいデータをバッチで書き込みます。

単一テーブルの書き込みジョブは順次実行されます。つまり、単一テーブルの書き込み同時実行数は 1 であり、変更できません。クラスターのデフォルトの書き込み同時実行数は 2 です。これは、単一ジョブのパフォーマンスを確保し、クラスターの過剰な負荷を防ぐためです。この値を調整すると、パフォーマンスに影響を与え、クラスターの負荷を増加させる可能性があるため、調整は避けてください。

説明

書き込み同時実行数を調整するには、テクニカルサポートにチケットを起票して評価を依頼してください。

概要

INSERT OVERWRITE SELECT の特徴は次のとおりです：

高いリソース消費：INSERT OVERWRITE SELECT はリソースを大量に消費する操作です。クラスターのパフォーマンスへの影響を避けるため、この操作はオフピーク時間帯に実行してください。
バッチの可視性：ジョブによって書き込まれたデータは、ジョブが完了するまで表示されません。ジョブが完了すると、すべてのデータが一度に表示されるようになります。
パーティションの上書き：INSERT OVERWRITE SELECT は、ターゲットテーブルの対応するパーティションのデータを上書きします。
自動インデックス構築：データが書き込まれると、システムは同期的にインデックスを構築します。書き込みジョブが完了すると、ターゲットテーブルにはインデックスが作成され、クエリパフォーマンスが向上します。

INSERT OVERWRITE SELECT の一般的な利用シーンは次のとおりです：

パーティションレベルのデータ書き込み。
データ初期化 (完全なデータ書き込み)。
大規模な一括データ書き込み。このメソッドは、小規模なデータボリュームには推奨されません。

注意事項

INSERT OVERWRITE SELECT とリアルタイム書き込みメソッド (INSERT INTO、REPLACE INTO、DELETE、UPDATE など) を使用して、同じテーブルに同時に書き込まないでください。同時に書き込むと、リアルタイム書き込みのデータが失われます。

構文

INSERT OVERWRITE table_name (column_name[,...])
select_statement

パラメーター

table_name：ターゲットテーブルの名前。
column_name：ターゲットテーブルの列の名前。
select_statement：`SELECT` 文。
`SELECT` 文の各列のデータ型は、ターゲットテーブルの対応する列のデータ型と一致する必要があります。
`SELECT` 文の列数がターゲットテーブルの列数より多い場合、書き込み操作は失敗します。`SELECT` 文の列数がターゲットテーブルの列数より少ない場合、ターゲットテーブルの残りの列にはデフォルト値が入力されます。列にデフォルト値がない場合、システムは `NULL` を入力します。

例

サンプルデータ

上書きの例のために、テストテーブルとデータを準備します。

ソーステーブル test_source とターゲットテーブル test_target を作成します。

CREATE TABLE test_source (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a);

CREATE TABLE test_target (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a) 
PARTITION BY VALUE(b) LIFECYCLE 10;

ソーステーブル test_source にテストデータを書き込みます。

INSERT INTO test_source VALUES (1,1);  
INSERT INTO test_source VALUES (1,2); 
INSERT INTO test_source VALUES (1,3);  
INSERT INTO test_source VALUES (2,1); 
INSERT INTO test_source VALUES (2,2); 
INSERT INTO test_source VALUES (2,3);

データの上書き

test_target テーブルに初期データを挿入します。
```
INSERT OVERWRITE test_target 
SELECT * FROM test_source WHERE a = 1;
```
test_target テーブルをクエリします。クエリは次の結果を返します。
```
+-----+------+
|1    |1     |
|1    |2     |
|1    |3     |
+-----+------+
```
test_target テーブルの b=1 のパーティションを上書きします。
```
INSERT OVERWRITE test_target (a,b)
SELECT a,b FROM test_source 
WHERE a = 2 AND b = 1;
```
上書き後、test_target をクエリすると、次の結果が返されます：
```
+-----+------+
|2    |1     |
|1    |2     |
|1    |3     |
+-----+------+
```
test_target テーブルの b=2 と b=3 のパーティションを上書きします。
```
INSERT OVERWRITE test_target
SELECT * FROM test_source 
WHERE a = 2 AND b >= 2 AND b <= 3;
```
上書き後、test_target をクエリすると、次の結果が返されます：
```
+-----+------+
|2    |1     |
|2    |2     |
|2    |3     |
+-----+------+
```

非同期書き込み

ジョブのサブミット

SUBMIT JOB は、バックグラウンドでスケジュールおよび実行される非同期ジョブをサブミットします。例：

SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

書き込みパフォーマンスの最適化

書き込み操作の前にヒントワード /* direct_batch_load=true */ を追加すると、書き込みタスクを高速化できます。このヒントワードは、消費リソースを抑えながら書き込みパフォーマンスを大幅に向上させます。例：

/* direct_batch_load=true*/
SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

説明

/* direct_batch_load=true*/ は、カーネルバージョン 3.1.5 以降でのみサポートされています。有効にしてもパフォーマンスが大幅に向上しない場合は、チケットを起票してサポートを依頼してください。ご利用のクラスターのカーネルバージョンを確認するには、「インスタンスのバージョン情報の表示」をご参照ください。

ジョブ進捗のクエリ

SUBMIT JOB でジョブをサブミットすると、システムは job_id を返します。この job_id を使用して、ジョブのステータスをクエリできます。例：

SHOW JOB STATUS WHERE job='<job_id>';

結果の status 列に `SUCCEEDED` と表示された場合、ジョブは完了です。