使用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	指定数据导入的临时分区。详情请参见临时分区。
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语句向指定的表中直接导入数据。此导入方式中，多条数据用逗号（,）分隔。详细使用方式，请参见INSERTINSERT。

说明

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语句向指定的表中覆盖写入数据。此导入方式中，多条数据用逗号（,）分隔。详细使用方式，请参见INSERTINSERT。

说明

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语句结果覆盖目标表的数据。详细使用方式，请参见INSERTINSERT。

说明

以下示例仅展示导入内部表数据，其操作过程与导入外部表数据相同，故不重复演示导入外部表数据过程。

以下示例以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，表示任务成功。

Insert