Apache Doris 数据导入之INSERT

作者: 张家锋

1. 概述

Insert Into 语句的使用方式和 MySQL 等数据库中 Insert Into 语句的使用方式类似。但在 Doris 中,所有的数据写入都是一个独立的导入作业。所以这里将 Insert Into 也作为一种导入方式介绍。

主要的 Insert Into 命令包含以下两种;

  • INSERT INTO tbl SELECT …
  • INSERT INTO tbl (col1, col2, …) VALUES (1, 2, …), (1,3, …); 其中第二种命令仅用于 Demo,不要使用在测试或生产环境中

2.基本操作

2.1 使用方式

这里使用Insert Into 方式插入数据和Mysql方式一样,是通过Mysql协议提交,同步返回结果

INSERT INTO table_name [partition_info] [WITH LABEL label] [col_list] [query_stmt] [VALUES];

示例:

insert into tb1 select * from tb2

这里的表tb1和tb2的字段顺序要一致,如果不一致,需要在select语句中调整字段顺序和tb1一致,例如

insert into tb1 select col2,col3,col1,…. from tb2

注意:

当需要使用 CTE(Common Table Expressions) 作为 insert 操作中的查询部分时,必须指定 WITH LABEL 和 column list 部分。示例

INSERT INTO tbl1 WITH LABEL label1
WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;
INSERT INTO tbl1 (k1)
WITH cte1 AS (SELECT * FROM tbl1), cte2 AS (SELECT * FROM tbl2)
SELECT k1 FROM cte1 JOIN cte2 WHERE cte1.k1 = 1;

2.2 导入语句参数介绍

  • partition_info 导入表的目标分区,如果指定目标分区,则只会导入符合目标分区的数据。如果没有指定,则默认值为这张表的所有分区。
  • col_list 导入表的目标列,可以以任意的顺序存在。如果没有指定目标列,那么默认值是这张表的所有列。如果待表中的某个列没有存在目标列中,那么这个列需要有默认值,否则 Insert Into 就会执行失败。 如果查询语句的结果列类型与目标列的类型不一致,那么会调用隐式类型转化,如果不能够进行转化,那么 Insert Into 语句会报语法解析错误。
  • query_stmt 通过一个查询语句,将查询语句的结果导入到 Doris 系统中的其他表。查询语句支持任意 Doris 支持的 SQL 查询语法。
  • VALUES 用户可以通过 VALUES 语法插入一条或者多条数据。 注意:VALUES 方式仅适用于导入几条数据作为导入 DEMO 的情况,完全不适用于任何测试和生产环境。Doris 系统本身也不适合单条数据导入的场景。建议使用 INSERT INTO SELECT 的方式进行批量导入。
  • WITH LABEL INSERT 操作作为一个导入任务,也可以指定一个 label。如果不指定,则系统会自动指定一个 UUID 作为 label。 该功能需要 0.11+ 版本。 注意:建议指定 Label 而不是由系统自动分配。如果由系统自动分配,但在 Insert Into 语句执行过程中,因网络错误导致连接断开等,则无法得知 Insert Into 是否成功。而如果指定 Label,则可以再次通过 Label 查看任务结果

3.导入结果

Insert Into 本身就是一个 SQL 命令,其返回结果会根据执行结果的不同,分为以下几种:

  1. 结果集为空 如果 insert 对应 select 语句的结果集为空,则返回如下:
    mysql> insert into tbl1 select * from empty_tbl;
    Query OK, 0 rows affected (0.02 sec)
    
  2. 结果集不为空 在结果集不为空的情况下。返回结果分为如下几种情况:
  3. Insert 执行成功并可见:
      mysql>insert into doris_test select id,name from doris_test_2;
      Query OK, 12 rows affected (0.03 sec)
      {'label':'insert_4e162bd4710a448f-8a5b369ca1832fc6', 'status':'VISIBLE', 'txnId':'5906282'}
      mysql>insert into tbl1 select * from tbl2;
      Query OK, 2 rows affected, 2 warnings (0.31 sec)
      {'label':'insert_f0747f0e-7a35-46e2-affa-13a235f4020d', 'status':'committed', 'txnId':'4005'}
    
    Query OK 表示执行成功。12 rows affected 表示总共有4行数据被导入。2 warnings 表示被过滤的行数。 同时会返回一个 json 串:
      {'label':'insert_4e162bd4710a448f-8a5b369ca1832fc6', 'status':'VISIBLE', 'txnId':'5906282'}
    
    label 为用户指定的 label 或自动生成的 label。Label 是该 Insert Into 导入作业的标识。每个导入作业,都有一个在单 database 内部唯一的 Label。 status 表示导入数据是否可见。如果可见,显示 visible,如果不可见,显示 committedtxnId 为这个 insert 对应的导入事务的 id。 err 字段会显示一些其他非预期错误。 当需要查看被过滤的行时,用户可以通过如下语句
      show load where label="xxx";
    
    返回结果中的 URL 可以用于查询错误的数据,具体见后面 查看错误行 小结。 数据不可见是一个临时状态,这批数据最终是一定可见的 可以通过如下语句查看这批数据的可见状态:
      show transaction where id=4005;
    
    返回结果中的 TransactionStatus 列如果为 visible,则表述数据可见。
  4. Insert 执行失败 执行失败表示没有任何数据被成功导入,并返回如下:
      mysql> insert into tbl1 select * from tbl2 where k1 = "a";
      ERROR 1064 (HY000): all partitions have no load data. url: http://10.74.167.16:8042/api/_load_error_log?file=__shard_2/error_log_insert_stmt_ba8bb9e158e4879-ae8de8507c0bf8a2_ba8bb9e158e4879_ae8de8507c0bf8a2
    
    其中 ERROR 1064 (HY000): all partitions have no load data 显示失败原因。后面的 url 可以用于查询错误的数据,具体见后面 查看错误行 小结。 综上,对于 insert 操作返回结果的正确处理逻辑应为:
  5. 如果返回结果为 ERROR 1064 (HY000),则表示导入失败。
  6. 如果返回结果为 Query OK ,则表示执行成功。
    1. 如果 rows affected 为 0,表示结果集为空,没有数据被导入。
    2. 如果 rows affected 大于 0:
      1. 如果 statuscommitted,表示数据还不可见。需要通过 show transaction 语句查看状态直到 visible
      2. 如果 statusvisible,表示数据导入成功。
    3. 如果 warnings 大于 0,表示有数据被过滤,可以通过 show load 语句获取 url 查看被过滤的行。

4.相关配置

4.1 FE配置

  • timeout 导入任务的超时时间(以秒为单位),导入任务在设定的 timeout 时间内未完成则会被系统取消,变成 CANCELLED。 目前 Insert Into 并不支持自定义导入的 timeout 时间,所有 Insert Into 导入的超时时间是统一的,默认的 timeout 时间为1小时。如果导入的源文件无法再规定时间内完成导入,则需要调整 FE 的参数insert_load_default_timeout_second。 同时 Insert Into 语句收到 Session 变量 query_timeout 的限制。可以通过 SET query_timeout = xxx; 来增加超时时间,单位是秒。

4.2 Session 变量

  • enable_insert_strict Insert Into 导入本身不能控制导入可容忍的错误率。用户只能通过 enable_insert_strict 这个 Session 参数用来控制。 当该参数设置为 false 时,表示至少有一条数据被正确导入,则返回成功。如果有失败数据,则还会返回一个 Label。 当该参数设置为 true 时,表示如果有一条数据错误,则导入失败。 默认为 false。可通过 SET enable_insert_strict = true; 来设置。
  • query_timeout Insert Into 本身也是一个 SQL 命令,因此 Insert Into 语句也受到 Session 变量 query_timeout 的限制。可以通过 SET query_timeout = xxx; 来增加超时时间,单位是秒

5.最佳实践

  1. 用户希望将已经在 Doris 表中的数据进行 ETL 转换并导入到一个新的 Doris 表中,此时适合使用 INSERT INTO SELECT 语法。这种适合在数仓中构建汇总层数据(DWS)或者事实明细宽表(DWD)的合成
  2. 用户可以创建一种外部表,如 MySQL 外部表映射一张 MySQL 系统中的表。或者创建 Broker 外部表来映射 HDFS 上的数据文件。然后通过 INSERT INTO SELECT 语法将外部表中的数据导入到 Doris 表中存储
  3. 用户希望仅导入几条假数据,验证一下 Doris 系统的功能。此时适合使用 INSERT INTO VALUES 的语法。
  4. Insert Into 对数据量没有限制,大数据量导入也可以支持。但 Insert Into 有默认的超时时间,用户预估的导入数据量过大,就需要修改系统的 Insert Into 导入超时时间。
    导入数据量 = 36G 约≤ 3600s * 10M/s 
    其中 10M/s 是最大导入限速,用户需要根据当前集群情况计算出平均的导入速度来替换公式中的 10M/s
    

文章列表

更多推荐

更多
  • Pulsar消息队列-一套高可用实时消息系统实现 实时消息【即时通信】系统,有群聊和单聊两种方式,其形态异于消息队列:1 大量的 group 信息变动,群聊形式的即时通信系统在正常服务形态下,瞬时可能有大量用户登入登出。2 ...
  • Pulsar消息队列-Pulsar对比Kafka笔记 很多人查看 Pulsar 之前可能对 Kafka 很熟悉,参照上图可见二者内部结构的区别,Pulsar 和 Kafka 都是以 Topic 描述一个基本的数据集合,Topic 数据又分为若干 Partition,即对数据进行逻辑上的 ...
  • Pulsar消息队列-对 2017 年一套 IM 系统的反思 信系统的开发,前前后后参与或者主导了六七个 IM 系统的研发。上一次开发的 IM 系统的时间点还是 2018 年,关于该系统的详细描述见 [一套高可用实时消息系统实现][1] ...
  • Apache APISIX文档-快速入门指南-如何构建 Apache APISIX 如何构建 Apache APISIX,步骤1:安装 Apache APISIX,步骤2:安装 etcd,步骤3:管理 Apache APISIX 服务,步骤4:运行测试案例,步骤5:修改 Admin API key,步骤6:为 Apac
  • Apache APISIX文档-快速入门指南-快速入门指南 快速入门指南,概述,前提条件,第一步:安装 Apache APISIX,第二步:创建路由,第三步:验证,进阶操作,工作原理,创建上游服务Upstream,绑定路由与上游服务,添加身份验证,为路由添加前缀,APISIX Dashboard
  • Apache APISIX文档-架构设计-APISIX APISIX,软件架构,插件加载流程,插件内部结构,配置 APISIX,插件加载流程,比如指定 APISIX 默认监听端口为 8000,并且设置 etcd 地址为 http://foo:2379, 其他配置保持默认。在 ...
  • Apache APISIX文档-架构设计-Service Service 是某类 API 的抽象(也可以理解为一组 Route 的抽象)。它通常与上游服务抽象是一一对应的,Route 与 Service 之间,通常是 N:1 的关系,参看下图。不同 Route 规则同时绑定到一个 Service ...
  • Apache APISIX文档-架构设计-Plugin Config 如果你想要复用一组通用的插件配置,你可以把它们提取成一个 Plugin config,并绑定到对应的路由上。举个例子,你可以这么做:创建 Plugin config,如果这个路由已经配置了 plugins,那么 Plugin config ...
  • Apache APISIX文档-架构设计-Debug Mode 注意:在 APISIX 2.10 之前,开启基本调试模式曾经是设置 conf/config.yaml 中的 apisix.enable_debug 为 true。设置 conf/debug.yaml 中的选项,开启高级调试模式。由于 ...
  • Apache APISIX文档-架构设计-Consumer 如上图所示,作为 API 网关,需要知道 API Consumer(消费方)具体是谁,这样就可以对不同 API Consumer 配置不同规则。授权认证:比如有 [key-auth] 等。获取 consumer_...
  • 近期文章

    更多
    文章目录

      推荐作者

      更多