通过 INSERT 语句导入数据
本文介绍如何使用 INSERT 语句 向 StarRocks 导入数据。
与 MySQL 等数据库系统类似,StarRocks 支持通过 INSERT 语句导入数据。您可以使用 INSERT INTO VALUES 语句直接向表中插入数据,您还可以通过 INSERT INTO SELECT 语句将其他 StarRocks 表中的数据导入到新的 StarRocks 表中,或者将其他数据源的数据通过外部表功能导入至 StarRocks 内部表中。自 v3.1 起,您可以使用 INSERT 语句和 FILES() 函数直接导入云存储或 HDFS 中的文件。
2.4 版本中,StarRocks 进一步支持通过 INSERT OVERWRITE 语句批量覆盖写入目标表。INSERT OVERWRITE 语句通过整合以下三部分操作来实现覆盖写入:
如果您希望在替换前验证数据,可以根据以上步骤自行实现覆盖写入数据。
从 v3.4.0 开始,StarRocks 支持分区表的 INSERT OVERWRITE 操作的新语义 — Dynamic Overwrite。更多信息,参考 Dynamic Overwrite。
注意事项
- 您只能在 MySQL 客户端通过
Ctrl
+C
按键强制取消同 步 INSERT 导入任务。 - 您可以通过 SUBMIT TASK 创建异步 INSERT 导入任务。
- 当前版本中,StarRocks 在执行 INSERT 语句时,如果有数据不符合目标表格式(例如字符串超长等情况),INSERT 操作默认执行失败。您可以通过设置会话变量
enable_insert_strict
为false
以确保 INSERT 操作过滤不符合目标表格式的数据,并继续执行。 - 频繁使用 INSERT 语句导入小批量数据会产生过多的数据版本,从而影响查询性能,因此不建议您频繁使用 INSERT 语句导入数据或将其作为生产环境的日常例行导入作业。如果您的业务场景需要流式导入或者小批量多次导入数据,建议使用 Apache Kafka® 作为数据源并通过 Routine Load 方式进行导入作业。
- 执行 INSERT OVERWRITE 语句后,系统将为目标分区创建相应的临时分区,并将数据写入临时分区,最后使用临时分区原子替换目标分区来实现覆盖写入。其所有过程均在 Leader FE 节点执行。因此,如果 Leader FE 节点在覆盖写入过程中发生宕机,将会导致该次 INSERT OVERWRITE 导入失败,其过程中所创建的临时分区也会被删除。
准备工作
查看权限
导入操作 需要目标表的 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);
注意
自 2.5.7 版本起,StarRocks 支持在建表和新增分区时自动设置分桶数量 (BUCKETS),您无需手动设置分桶数量。更多信息,请参见 设置分桶数量。
通过 INSERT INTO VALUES 语句导入数据
您可以通过 INSERT INTO VALUES 语句向指定的表中直接导入数据。此导入方式中,多条数据用逗号(,)分隔。详细使用方式,参考 SQL 参考 - INSERT。详细参数信息,参考 INSERT 参数说明。
注意
INSERT INTO VALUES 语句导入方式仅适用于导入少量数据作为验证 DEMO 用途,不适用于大规模测试或生产环境。如需大规模导入数据,请选择其他导入方式。
以下示例以 insert_load_wikipedia
为 Label 向源表 source_wiki_edit
中导入两条数据。Label 是导入作业的标识,数据库内唯一。
INSERT INTO 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 语句将源表中的数据导入至目标表中。INSERT INTO SELECT 将源表中的数据进行 ETL 转换之后,导入到 StarRocks 内表中。源表可以是一张或多张内部表或者外部表,甚至云存储或 HDFS 中的数据文件。目标表必须是 StarRocks 的内表。执行该语句之后,系统将 SELECT 语句结果导入目标表。详细使用方式,参考 INSERT。详细参数信息,参考 INSERT 参数。
通过 INSERT INTO SELECT 将内外表数据导入内表
说明
以下示例仅展示导入内部表数据,其操作过程与导入外部表数据相同,故不重复演示导入外部表数据过程。
- 以下示例以
insert_load_wikipedia_1
为 Label 将源表中的数据导入至目标表中。
INSERT INTO insert_wiki_edit
WITH LABEL insert_load_wikipedia_1
SELECT * FROM source_wiki_edit;
- 以下示例以
insert_load_wikipedia_2
为 Label 将源表中的数据导入至目标表的p06
和p12
分区中。如果不指定目标分区,数据将会导入全表;如果指定目标分区,数据只会导入指定的分区。
INSERT INTO insert_wiki_edit PARTITION(p06, p12)
WITH LABEL insert_load_wikipedia_2
SELECT * FROM source_wiki_edit;
如果清空 p06
和 p12
分区,则查询不到先前插入至对应分区的数据。
MySQL > select * from insert_wiki_edit;
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| event_time | channel | user | is_anonymous | is_minor | is_new | is_robot | is_unpatrolled | delta | added | deleted |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| 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 |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
2 rows in set (0.00 sec)
MySQL > TRUNCATE TABLE insert_wiki_edit PARTITION(p06, p12);
Query OK, 0 rows affected (0.01 sec)
MySQL > select * from insert_wiki_edit;
Empty set (0.00 sec)
- 以下示例以
insert_load_wikipedia_3
为 Label 将源表中event_time
和channel
列的数据导入至目标表的对应列中。未被导入的列将被赋予默认值。
INSERT INTO insert_wiki_edit
WITH LABEL insert_load_wikipedia_3
(
event_time,
channel
)
SELECT event_time, channel FROM source_wiki_edit;
自 v3.3.1 起,INSERT INTO 导入主键表时指定 Column List 会执行部分列更新(而在先前版本中,指定 Column List 仍然导致 Full Upsert)。如不指定 Column List,系统执行 Full Upsert。
参数 | 说明 |
---|---|
table_name | 导入数据的目标表。可以为 db_name.table_name 形式。 |
partitions | 导入的目标分区。此参数必须是目标表中存在的分区,多个分区名称用逗号(, )分隔。如果指定该参数,数据只会被导入相应分区内。如果未指定,则默认将数据导入至目标表的所有分区。 |
label | 导入作业的标识,数据库内唯一。如果未指定,StarRocks 会自动为作业生成一个 Label。建议您指定 Label。否则,如果当前导入作业因网络错误无法返回结果,您将无法得知该导入操作是否成功。如果指定了 Label,可以通过 SQL 命令 SHOW LOAD WHERE label="label" 查看作业结果。 |
column_name | 导入的目标列,必须是目标表中存在的列。该参数与导入数据的列的名称可以不同,但顺序需一一对应。如果不指定目标列,默认为目标表中的所有列。如果源表中的某个列在目标列不存在,则写入默认值。如果当前列没有默认值,导入作业会失败。如果查询语句的结果列类型与目标列的类型不一致,会进行隐式转化,如果不能进行转化,那么 INSERT INTO 语句会报语法解析错误。 |
query | 查询语句,查询的结果会导入至目标表中。查询语句支持任意 StarRocks 支持的 SQL 查询语法。 |
通过 INSERT INTO SELECT 以及表函数 FILES() 导入外部数据文件
自 v3.1 起,StarRocks 支持使用 INSERT 语句和 FILES() 表函数直接导入云存储或 HDFS 中的文件,无需提前创建 External Catalog 或文件外部表。除此之外,FILES() 支持自动推断 Table Schema,大大简化导入过程。
以下示例将 AWS S3 存储桶 inserttest
内 Parquet 文件 parquet/insert_wiki_edit_append.parquet 中的数据插入至表 insert_wiki_edit
中:
INSERT INTO insert_wiki_edit
SELECT * FROM FILES(
"path" = "s3://inserttest/parquet/insert_wiki_edit_append.parquet",
"format" = "parquet",
"aws.s3.access_key" = "XXXXXXXXXX",
"aws.s3.secret_key" = "YYYYYYYYYY",
"aws.s3.region" = "us-west-2"
);
通过 INSERT OVERWRITE VALUES 语句覆盖写入数据
您可以通过 INSERT OVERWRITE VALUES 语句向指定的表中覆盖写入数据。此导入方式中,多条数据用逗号(,)分隔。详细使用方式,参考 INSERT。详细参数信息,参考 INSERT 参数说明。
注意
INSERT OVERWRITE VALUES 语句导入方式仅适用于导入少量数据作为验证 DEMO 用途,不适用于大规模测试或生产环境。如需大规模导入数据,请选择其他导入方式。
查询源表以及目标表确认其中已有数据。
MySQL > SELECT * FROM source_wiki_edit;
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| event_time | channel | user | is_anonymous | is_minor | is_new | is_robot | is_unpatrolled | delta | added | deleted |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| 2015-09-12 00:00:00 | #ca.wikipedia | helloSR | 0 | 1 | 0 | 1 | 0 | 3 | 23 | 0 |
| 2015-09-12 00:00:00 | #en.wikipedia | AustinFF | 0 | 0 | 0 | 0 | 0 | 21 | 5 | 0 |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
2 rows in set (0.02 sec)
MySQL > SELECT * FROM insert_wiki_edit;
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| event_time | channel | user | is_anonymous | is_minor | is_new | is_robot | is_unpatrolled | delta | added | deleted |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| 2015-09-12 00:00:00 | #ca.wikipedia | helloSR | 0 | 1 | 0 | 1 | 0 | 3 | 23 | 0 |
| 2015-09-12 00:00:00 | #en.wikipedia | AustinFF | 0 | 0 | 0 | 0 | 0 | 21 | 5 | 0 |
+---------------------+---------------+----------+--------------+----------+--------+----------+----------------+-------+-------+---------+
2 rows in set (0.01 sec)
以下示例以 insert_load_wikipedia_ow
为 Label 向源表 source_wiki_edit
中覆盖写入两条数据。
INSERT OVERWRITE 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 参数。
说明
以下示例仅展示导入内部表数据,其操作过程与导入外部表数据相同,故不重复演示导入外部表数据过程。
- 以下示例以
insert_load_wikipedia_ow_1
为 Label 将源表中的数据覆盖写入至目标表中。
INSERT OVERWRITE insert_wiki_edit
WITH LABEL insert_load_wikipedia_ow_1
SELECT * FROM source_wiki_edit;
- 以下示例以
insert_load_wikipedia_2
为 Label 将源表中的数据覆盖写入至目标表的p06
和p12
分区中。如果不指定目标分区,数据将会覆盖写入全表;如果指定目标分区,数据只会覆盖写入指定的分区。
INSERT OVERWRITE insert_wiki_edit PARTITION(p06, p12)
WITH LABEL insert_load_wikipedia_ow_2
SELECT * FROM source_wiki_edit;
如果清空 p06
和 p12
分区,则查询不到先前覆盖写入至对应分区的数据。
MySQL > select * from insert_wiki_edit;
+---------------------+---------------+--------------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| event_time | channel | user | is_anonymous | is_minor | is_new | is_robot | is_unpatrolled | delta | added | deleted |
+---------------------+---------------+--------------+--------------+----------+--------+----------+----------------+-------+-------+---------+
| 2015-09-12 00:00:00 | #fr.wikipedia | PereBot | 0 | 1 | 0 | 1 | 0 | 17 | 17 | 0 |
| 2015-09-12 00:00:00 | #cn.wikipedia | GELongstreet | 0 | 0 | 0 | 0 | 0 | 36 | 36 | 0 |
+---------------------+---------------+--------------+--------------+----------+--------+----------+----------------+-------+-------+---------+
2 rows in set (0.01 sec)
MySQL > TRUNCATE TABLE insert_wiki_edit PARTITION(p06, p12);
Query OK, 0 rows affected (0.01 sec)
MySQL > select * from insert_wiki_edit;
Empty set (0.00 sec)
对于使用列表达式分区方式(PARTITION BY column
)的表,INSERT OVERWRITE 支持通过指定分区键的值在目标表上创建不存在的分区。对于已有的分区,将正常进行覆盖写。
以下示例创建了分区表 activity
,向其中导入新数据时自动创建了先前不存在的分区:
CREATE TABLE 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 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 insert_wiki_edit
WITH LABEL insert_load_wikipedia_ow_3
(
event_time,
channel
)
SELECT event_time, channel FROM source_wiki_edit;
Dynamic Overwrite
从 v3.4.0 开始,StarRocks 支持分区表的 INSERT OVERWRITE 操作的新语义 — Dynamic Overwrite。
当前 INSERT OVERWRITE 默认行为如下:
- 当覆盖整个分区表(即未指定 PARTITION 子句)时,新数据会替换对应分区中的数据。如果存在表中已有分区未涉及覆盖操作,系统会清空该分区数据。
- 当覆盖空的分区表(即其中没有任何分区)但指定了 PARTITION 子句时,系统会报错
ERROR 1064 (HY000): Getting analyzing error. Detail message: Unknown partition 'xxx' in table 'yyy'
。 - 当覆盖分区表时指定了不存在的分区,系统会报错
ERROR 1064 (HY000): Getting analyzing error. Detail message: Unknown partition 'xxx' in table 'yyy'
。 - 当覆盖分区表的数据与指定的分区不匹配时,如果开启严格模式,系统会报错
ERROR 1064 (HY000): Insert has filtered data in strict mode
;如果未开启严格模式,系统 会过滤不合格的数据。
新的 Dynamic Overwrite 语义的行为与上述默认行为有很大不同:
当覆盖整个分区表时,新数据会替换对应分区中的数据。但未涉及的分区会保留,而不会被清空或删除。如果新数据对应不存在的分区,系统会自动创建该分区。
Dynamic Overwrite 语义默认禁用。如需启用,需要将系统变量 dynamic_overwrite
设置为 true
。
在当前 Session 中启用 Dynamic Overwrite:
SET dynamic_overwrite = true;
您也可以在 INSERT OVERWRITE 语句中通过 Hint 启用 Dynamic Overwrite,仅对该语句生效:
示例:
INSERT OVERWRITE /*+set_var(set dynamic_overwrite = false)*/ insert_wiki_edit
SELECT * FROM source_wiki_edit;