如何使用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_targetb=1的分區進行覆蓋寫入。

INSERT OVERWRITE test_target (a,b)
SELECT a,b FROM test_source 
WHERE a = 2 AND b = 1;

覆蓋寫入b=1的分區後，查詢test_target，得到如下結果。

+-----+------+
|2    |1     |
|1    |2     |
|1    |3     |
+-----+------+

對目標表test_targetb=2和b=3的分區進行覆蓋寫入。

INSERT OVERWRITE test_target
SELECT * FROM test_source 
WHERE a = 2 AND b >= 2 AND b <= 3;

覆蓋寫入b=2和b=3的分區後，查詢test_target，得到如下結果。

+-----+------+
|2    |1     |
|2    |2     |
|2    |3     |
+-----+------+

非同步寫入

提交任務

通常使用SUBMIT JOB提交非同步任務，由後台調度執行。樣本語句如下。

SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

寫入調優

在寫入任務前增加Hint（/* direct_batch_load=true*/）可以加速寫入任務。該Hint可以在節約大量資源的同時進一步提高寫入效能。樣本語句如下。

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

說明

僅3.1.5及以上核心版本支援/* direct_batch_load=true*/。若使用後效能無明顯最佳化，可提交工單進行升級與最佳化。查看核心版本，請參見如何查看執行個體版本資訊。

進度查詢

通過SUBMIT JOB提交寫入任務後會返回job_id。以該job_id為條件查詢寫入任務的狀態，樣本語句如下。

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

返回結果status列為SUCCEEDED，表示寫入任務完成。