通過Insert語句匯入StarRocks - E-MapReduce

本文為您介紹如何使用INSERT語句向StarRocks中匯入資料。

適用情境

如果僅匯入幾條測試資料，則可以使用INSERT INTO VALUES語句。
如果將源表中的資料進行ETL轉換並匯入到一個新的StarRocks表中，則可以使用INSERT INTO SELECT語句。
如果僅覆蓋寫入幾條測試資料，則可以使用INSERT OVERWRITE VALUES語句。
如果將源表中的資料進行ETL轉換並覆蓋寫入到一個新的StarRocks表中，則可以使用INSERT OVERWRITE SELECT語句。

說明

源表可以是一張或多張內部表或者外部表格。目標表必須是StarRocks的內表。

注意事項

StarRocks在執行INSERT語句時，如果有資料不符合目標表格式（例如字串超長等情況），INSERT操作預設執行失敗。您可以通過設定會話變數enable_insert_strict為false以確保INSERT操作過濾不符合目標表格式的資料，並繼續執行。
頻繁使用INSERT語句匯入小批量資料會產生過多的資料版本，從而影響查詢效能，因此不建議您頻繁使用INSERT語句匯入資料或將其作為生產環境的日常例行匯入任務。如果您的業務情境需要流式匯入或者小批量多次匯入資料，建議使用Stream Load或者Routine Load的方式進行匯入。
執行INSERT OVERWRITE語句後，系統將為目標資料分割建立相應的臨時分區，並將資料寫入臨時分區，最後使用臨時分區原子替換目標資料分割來實現覆蓋寫入。其所有過程均在Leader FE節點執行。因此，如果Leader FE節點在覆蓋寫入過程中發生宕機，將會導致該次INSERT OVERWRITE匯入失敗，其過程中所建立的臨時分區也會被刪除。

基本文法

INSERT { INTO | OVERWRITE } [db_name.]<table_name>
[ PARTITION (<partition_name> [, ...] ) ]
[ TEMPORARY PARTITION (<temporary_partition_name> [, ...] ) ]
[ WITH LABEL <label>]
[ (<column_name>[, ...]) ]
{ VALUES ( { <expression> | DEFAULT } [, ...] ) | <query> }

參數說明如下表所示。

參數	說明
INTO	將資料追加寫入目標表。
OVERWRITE	將資料覆蓋寫入目標表。
table_name	匯入資料的目標表名稱。填寫形式為db_name.table_name。
PARTITION	匯入的目標資料分割。必須是目標表中存在的分區，多個分區名稱用逗號（,）分隔。如果指定該參數，資料只會被匯入相應分區內。如果未指定，則預設將資料匯入至目標表的所有分區。
TEMPORARY PARTITION	指定資料匯入的臨時分區。詳情請參見temporary partition。
label	匯入任務的標識，在資料庫內唯一。建議您指定Label。如果未指定，StarRocks會自動為任務產生一個Label，當匯入任務因網路錯誤未返回結果時，您將無法確認該匯入操作是否成功。如果指定Label，您可以通過SQL命令`SHOW LOAD WHERE label="label";`查看任務結果。
column_name	匯入的目標列，必須是目標表中存在的列。該參數的對應關係與列名無關，但與其順序一一對應。如果不指定目標列，預設為目標表中的所有列。如果源表中的某個列在目標列不存在，則寫入預設值。如果當前列沒有預設值，匯入任務會失敗。如果查詢語句的結果列類型與目標列的類型不一致，會進行隱式轉化，如果不能進行轉化，那麼INSERT INTO語句會報文法解析錯誤。
VALUES	通過VALUES語句插入一條或者多條資料。重要 VALUES方式僅適用於匯入幾條資料作為Demo的情況，完全不適用於任何測試和生產環境。StarRocks系統本身也不適合單條資料匯入的情境。建議使用INSERT INTO SELECT的方式進行大量匯入。
expression	運算式，用以為對應列賦值。
DEFAULT	為對應列賦予預設值。
query	查詢語句，查詢的結果會匯入至目標表中。支援StarRocks提供的所有SQL查詢文法。

準備工作

查看許可權

匯入操作需要目標表的INSERT許可權。如果您的使用者帳號沒有INSERT許可權，請參考GRANT給使用者賦權，文法為GRANT INSERT ON TABLE <table_name> IN DATABASE <database_name> TO { ROLE <role_name> | USER <user_identity>}。

建庫建表

在StarRocks中建立資料庫load_test，並在其中建立匯入目標表insert_wiki_edit以及資料來源表source_wiki_edit。

說明

本文中示範的操作樣本均基於目標表insert_wiki_edit和資料來源表source_wiki_edit。如果您選擇使用自己的表以及資料，請跳過當前步驟，並根據使用情境修改需要匯入的資料。

CREATE DATABASE IF NOT EXISTS load_test;
USE load_test;
CREATE TABLE insert_wiki_edit
(
    event_time      DATETIME,
    channel         VARCHAR(32)      DEFAULT '',
    user            VARCHAR(128)     DEFAULT '',
    is_anonymous    TINYINT          DEFAULT '0',
    is_minor        TINYINT          DEFAULT '0',
    is_new          TINYINT          DEFAULT '0',
    is_robot        TINYINT          DEFAULT '0',
    is_unpatrolled  TINYINT          DEFAULT '0',
    delta           INT              DEFAULT '0',
    added           INT              DEFAULT '0',
    deleted         INT              DEFAULT '0'
)
DUPLICATE KEY(
    event_time,
    channel,
    user,
    is_anonymous,
    is_minor,
    is_new,
    is_robot,
    is_unpatrolled
)
PARTITION BY RANGE(event_time)(
    PARTITION p06 VALUES LESS THAN ('2015-09-12 06:00:00'),
    PARTITION p12 VALUES LESS THAN ('2015-09-12 12:00:00'),
    PARTITION p18 VALUES LESS THAN ('2015-09-12 18:00:00'),
    PARTITION p24 VALUES LESS THAN ('2015-09-13 00:00:00')
)
DISTRIBUTED BY HASH(user);

CREATE TABLE source_wiki_edit
(
    event_time      DATETIME,
    channel         VARCHAR(32)      DEFAULT '',
    user            VARCHAR(128)     DEFAULT '',
    is_anonymous    TINYINT          DEFAULT '0',
    is_minor        TINYINT          DEFAULT '0',
    is_new          TINYINT          DEFAULT '0',
    is_robot        TINYINT          DEFAULT '0',
    is_unpatrolled  TINYINT          DEFAULT '0',
    delta           INT              DEFAULT '0',
    added           INT              DEFAULT '0',
    deleted         INT              DEFAULT '0'
)
DUPLICATE KEY(
    event_time,
    channel,user,
    is_anonymous,
    is_minor,
    is_new,
    is_robot,
    is_unpatrolled
)
PARTITION BY RANGE(event_time)(
    PARTITION p06 VALUES LESS THAN ('2015-09-12 06:00:00'),
    PARTITION p12 VALUES LESS THAN ('2015-09-12 12:00:00'),
    PARTITION p18 VALUES LESS THAN ('2015-09-12 18:00:00'),
    PARTITION p24 VALUES LESS THAN ('2015-09-13 00:00:00')
)
DISTRIBUTED BY HASH(user);

通過INSERT INTO VALUES匯入

您可以通過INSERT INTO VALUES語句向指定的表中直接匯入資料。此匯入方式中，多條資料用逗號（,）分隔。詳細使用方式，請參見INSERT。

說明

INSERT INTO VALUES語句匯入方式僅適用於匯入少量資料作為驗證DEMO用途，不適用於大規模測試或生產環境。如需大規模匯入資料，請選擇其他匯入方式。

以下樣本以insert_load_wikipedia為Label向資料來源表source_wiki_edit中匯入兩條資料。

INSERT INTO load_test.source_wiki_edit
WITH LABEL insert_load_wikipedia
VALUES
    ("2015-09-12 00:00:00","#en.wikipedia","AustinFF",0,0,0,0,0,21,5,0),
    ("2015-09-12 00:00:00","#ca.wikipedia","helloSR",0,1,0,1,0,3,23,0);

參數	說明
table_name	匯入資料的目標表。使用`db_name.table_name`形式。
label	匯入任務的標識，在資料庫內唯一。如果未指定，StarRocks會自動為任務產生一個Label。建議您指定Label。否則，如果當前置入任務因網路錯誤無法返回結果，您將無法得知該匯入操作是否成功。如果指定了Label，可以通過SQL命令`SHOW LOAD WHERE label="label";`查看任務結果。
values	通過VALUES文法插入一條或者多條資料，多條資料用逗號（,）分隔。

通過INSERT INTO SELECT匯入

您可以通過INSERT INTO SELECT語句將源表中的資料進行ETL轉換之後，匯入到StarRocks內表中。源表可以是一張或多張內部表或者外部表格。目標表必須是StarRocks的內表。執行該語句之後，系統將SELECT語句結果匯入目標表。

說明

以下樣本僅展示匯入內部表資料，其操作過程與匯入外部表格資料相同，故不重複示範匯入外部表格資料過程。

以下樣本以insert_load_wikipedia_1為Label將源表中的資料匯入至目標表中。

INSERT INTO load_test.insert_wiki_edit
WITH LABEL insert_load_wikipedia_1
SELECT * FROM load_test.source_wiki_edit;

以下樣本以insert_load_wikipedia_2為Label將源表中的資料匯入至目標表的p06和p12分區中。如果不指定目標資料分割，資料將會匯入全表；如果指定目標資料分割，資料只會匯入指定的分區。
```
INSERT INTO load_test.insert_wiki_edit PARTITION(p06, p12)
WITH LABEL insert_load_wikipedia_2
SELECT * FROM load_test.source_wiki_edit;
```
如果清空p06和p12分區，則查詢不到先前插入至對應分區的資料。
```
TRUNCATE TABLE load_test.insert_wiki_edit PARTITION(p06, p12);
SELECT * FROM load_test.insert_wiki_edit;
```
以下樣本以insert_load_wikipedia_3為Label將源表中event_time和channel列的資料匯入至目標表的對應列中。未被匯入的列將被賦予預設值。
```
INSERT INTO load_test.insert_wiki_edit
WITH LABEL insert_load_wikipedia_3 
(
    event_time, 
    channel
)
SELECT event_time, channel FROM load_test.source_wiki_edit;
```

通過INSERT OVERWRITE VALUES覆蓋寫入

您可以通過INSERT OVERWRITE VALUES語句向指定的表中覆蓋寫入資料。此匯入方式中，多條資料用逗號（,）分隔。詳細使用方式，請參見INSERT。

說明

INSERT OVERWRITE VALUES語句匯入方式僅適用於匯入少量資料作為驗證DEMO用途，不適用於大規模測試或生產環境。如需大規模匯入資料，請選擇其他匯入方式。

查詢源表，確認其中已有資料。

SELECT * FROM load_test.source_wiki_edit;

系統返回如下查詢結果。

以下樣本以insert_load_wikipedia_ow為Label向源表source_wiki_edit中覆蓋寫入兩條資料。

INSERT OVERWRITE load_test.source_wiki_edit
WITH LABEL insert_load_wikipedia_ow
VALUES
    ("2015-09-12 00:00:00","#cn.wikipedia","GELongstreet",0,0,0,0,0,36,36,0),
    ("2015-09-12 00:00:00","#fr.wikipedia","PereBot",0,1,0,1,0,17,17,0);

查詢源表，系統返回如下查詢結果，表明資料已經成功覆蓋。

通過INSERT OVERWRITE SELECT覆蓋寫入

您可以通過INSERT OVERWRITE SELECT語句將源表中的資料覆蓋寫入至目標表中。INSERT OVERWRITE SELECT將源表中的資料進行ETL轉換之後，覆蓋寫入到StarRocks內表中。源表可以是一張或多張內部表或者外部表格。目標表必須是StarRocks的內表。執行該語句之後，系統使用SELECT語句結果覆蓋目標表的資料。詳細使用方式，請參見INSERT。

說明

以下樣本僅展示匯入內部表資料，其操作過程與匯入外部表格資料相同，故不重複示範匯入外部表格資料過程。

以下樣本以insert_load_wikipedia_ow_1為Label將源表中的資料覆蓋寫入至目標表中。

INSERT OVERWRITE load_test.insert_wiki_edit
WITH LABEL insert_load_wikipedia_ow_1
SELECT * FROM load_test.source_wiki_edit;

以下樣本以insert_load_wikipedia_ow_2為Label將源表中的資料覆蓋寫入至目標表的p06和p12分區中。如果不指定目標資料分割，資料將會覆蓋寫入全表；如果指定目標資料分割，資料只會覆蓋寫入指定的分區。
```
INSERT OVERWRITE load_test.insert_wiki_edit PARTITION(p06, p12)
WITH LABEL insert_load_wikipedia_ow_2
SELECT * FROM load_test.source_wiki_edit;
```
如果清空p06和p12分區，則查詢不到先前插入至對應分區的資料。
```
TRUNCATE TABLE load_test.insert_wiki_edit PARTITION(p06, p12);
SELECT * FROM load_test.insert_wiki_edit;
```
說明
對於使用列運算式分區方式（PARTITION BY column）的表，INSERT OVERWRITE支援通過指定分區鍵的值在目標表上建立不存在的分區。對於已有的分區，將正常進行覆蓋寫。
以下樣本建立了分區表activity，向其中匯入新資料時自動建立了先前不存在的分區：
```
CREATE TABLE load_test.activity (
id INT          NOT NULL,
dt VARCHAR(10)  NOT NULL
) ENGINE=OLAP 
DUPLICATE KEY(`id`)
PARTITION BY (`id`, `dt`)
DISTRIBUTED BY HASH(`id`);

INSERT OVERWRITE load_test.activity
PARTITION(id='4', dt='2022-01-01')
WITH LABEL insert_activity_auto_partition
VALUES ('4', '2022-01-01');
```
以下樣本以insert_load_wikipedia_ow_3為Label將源表中event_time和channel列的資料覆蓋寫入至目標表的對應列中。未被匯入的列將被賦予預設值。
```
INSERT OVERWRITE load_test.insert_wiki_edit
WITH LABEL insert_load_wikipedia_ow_3 
(
    event_time, 
    channel
)
SELECT event_time, channel FROM source_wiki_edit;
```

查看匯入任務

您可以通過以下兩種方式來查看匯入任務。

方式一：通過EMR StarRocks Manager查看匯入任務。詳情請參見查看匯入任務詳情。
方式二：通過StarRocks Information Schema庫中的loads視圖查看匯入任務的進度。該功能自3.1版本起支援。
```
SELECT * FROM information_schema.loads;
```
有關loads視圖提供的欄位詳情，參見loads。
如果您提交了多個匯入任務，您可以通過LABEL過濾出想要查看的任務。樣本如下。
```
SELECT * FROM information_schema.loads WHERE LABEL = 'insert_load_wikipedia_ow';
```
在返回結果中，STATE顯示匯入任務的狀態。
- 匯入任務的狀態為CANCELLED，通過記錄中的ERROR_MSG欄位，可以確定導致任務出錯的原因。
- 匯入任務的狀態為FINISHED，表示任務成功。

E-MapReduce：Insert