Apache Doris 数据导入导出 , Binlog Load

4.数据的导入导出
按照使用场景划分

点击图片可查看完整电子表格
4.1使用 Insert 方式同步数据
用户可以通过 MySQL 协议，使用 INSERT 语句进行数据导入。
INSERT 语句的使用方式和 MySQL 等数据库中 INSERT 语句的使用方式类似。 INSERT 语句支持以下两种语法：
SQL

• INSERT INTO table SELECT …
• INSERT INTO table VALUES(…)

对于 Doris 来说，一个 INSERT 命令就是一个完整的导入事务。
因此不论是导入一条数据，还是多条数据，我们都不建议在生产环境使用这种方式进行数据导入。高频次的 INSERT 操作会导致在存储层产生大量的小文件，会严重影响系统性能。
该方式仅用于线下简单测试或低频少量的操作。
或者可以使用以下方式进行批量的插入操作：
SQL
INSERT INTO example_tbl VALUES
(1000, “baidu1”, 3.25)
(2000, “baidu2”, 4.25)
(3000, “baidu3”, 5.25);
4.2导入本地数据
Stream Load 用于将本地文件导入到doris中。Stream Load 是通过 HTTP 协议与 Doris 进行连接交互的。
该方式中涉及 HOST:PORT 都是对应的HTTP 协议端口。
• BE 的 HTTP 协议端口，默认为 8040。
• FE 的 HTTP 协议端口，默认为 8030。
但须保证客户端所在机器网络能够联通FE, BE 所在机器。
基本原理：

SQL

                         |      | 1. User submit load to FE   1.提交导入请求
                         |      |
                         |   +--v-----------+
                         |   | FE           | 生成导入计划
4. Return result to user |   +--+-----------+
                         |      |
                         |      | 2. Redirect to BE  下发给每一个BE节点
                         |      |
                         |   +--v-----------+
                         +---+Coordinator BE| 1B. User submit load to BE
                             +-+-----+----+-+
                               |     |    |
                         +-----+     |    +-----+
                         |           |          | 3. Distrbute data 分发数据并导入
                         |           |          |
                       +-v-+       +-v-+      +-v-+
                       |BE |       |BE |      |BE |
                       +---+       +---+      +---+

1. 创建一张表
   建表语句：
   SQL
   drop table if exists load_local_file_test;
   CREATE TABLE IF NOT EXISTS load_local_file_test
   (
   id INT,
   name VARCHAR(50),
   age TINYINT
   )
   unique key(id)
   DISTRIBUTED BY HASH(id) BUCKETS 3;

1,zss,28
2,lss,28
3,ww,88
2. 导入数据
执行 curl 命令导入本地文件（这个命令不是在mysql端执行的哦）：
Shell

语法示例

curl
-u user:passwd \ # 账号密码
-H “label:load_local_file_test” \ # 本次任务的唯一标识
-T 文件地址
http://主机名:端口号/api/库名/表名/_stream_load

curl
-u root:123456
-H “label:load_local_file”
-H “column_separator:,”
-T /root/data/loadfile.txt
http://doitedu01:8040/api/test/load_local_file_test/_stream_load
• user:passwd 为在 Doris 中创建的用户。初始用户为 admin / root，密码初始状态下为空。
• host:port 为 BE 的 HTTP 协议端口，默认是 8040，可以在 Doris 集群 WEB UI页面查看。
• label: 可以在 Header 中指定 Label 唯一标识这个导入任务。
3. 等待导入结果
SQL
– 这是失败的
[root@doitedu01 data]# curl \

-u root:123456
-H “label:load_local_file”
-T /root/data/loadfile.txt
http://doitedu01:8040/api/test/load_local_file_test/_stream_load
{
“TxnId”: 1004,
“Label”: “load_local_file”,
“TwoPhaseCommit”: “false”,
“Status”: “Fail”,
“Message”: “too many filtered rows”,
“NumberTotalRows”: 4,
“NumberLoadedRows”: 0,
“NumberFilteredRows”: 4,
“NumberUnselectedRows”: 0,
“LoadBytes”: 36,
“LoadTimeMs”: 82,
“BeginTxnTimeMs”: 13,
“StreamLoadPutTimeMs”: 56,
“ReadDataTimeMs”: 0,
“WriteDataTimeMs”: 9,
“CommitAndPublishTimeMs”: 0,
“ErrorURL”: “http://192.168.17.3:8040/api/_load_error_log?file=__shard_0/error_log_insert_stmt_cf4aa4d10e8d5fc5-458f16b70f0f2e87_cf4aa4d10e8d5fc5_458f16b70f0f2e87”
}

– 这是成功的
[root@doitedu01 data]# curl \

-u root:123456
-H “label:load_local_file”
-H “column_separator:,”
-T /root/data/loadfile.txt
http://doitedu01:8040/api/test/load_local_file_test/_stream_load
{
“TxnId”: 1005,
“Label”: “load_local_file”,
“TwoPhaseCommit”: “false”,
“Status”: “Success”,
“Message”: “OK”,
“NumberTotalRows”: 4,
“NumberLoadedRows”: 4,
“NumberFilteredRows”: 0,
“NumberUnselectedRows”: 0,
“LoadBytes”: 36,
“LoadTimeMs”: 54,
“BeginTxnTimeMs”: 0,
“StreamLoadPutTimeMs”: 2,
“ReadDataTimeMs”: 0,
“WriteDataTimeMs”: 14,
“CommitAndPublishTimeMs”: 36
}
如果遇到错误了，可以根据他给定的错误的"ErrorURL"去查看错误的细节
SQL
SHOW LOAD WARNINGS ON “http://192.168.17.3:8040/api/_load_error_log?file=__shard_0/error_log_insert_stmt_cf4aa4d10e8d5fc5-458f16b70f0f2e87_cf4aa4d10e8d5fc5_458f16b70f0f2e87”

4.2.1curl的一些可配置的参数：

1. label: 导入任务的标签，相同标签的数据无法多次导入。（标签默认保留30分钟）
2. column_separator：用于指定导入文件中的列分隔符，默认为\t。
3. line_delimiter：用于指定导入文件中的换行符，默认为\n。
4. columns：用于指定文件中的列和table中列的对应关系，默认一一对应
   例1: 表中有3个列“c1, c2, c3”，源文件中的三个列一次对应的是"c3,c2,c1"; 那么需要指定-H “columns: c3, c2, c1”
   例2: 表中有3个列“c1, c2, c3", 源文件中前三列依次对应，但是有多余1列；那么需要指定-H “columns: c1, c2, c3, xxx”;最后一个列随意指定个名称占位即可
   例3: 表中有3个列“year, month, day"三个列，源文件中只有一个时间列，为”2018-06-01 01:02:03“格式；那么可以指定
   -H "columns: col, year = year(col), month=month(col), day=day(col)"完成导入
5. where: 用来过滤导入文件中的数据
   例1: 只导入大于k1列等于20180601的数据，那么可以在导入时候指定-H “where: k1 = 20180601”
6. max_filter_ratio：最大容忍可过滤（数据不规范等原因）的数据比例。默认零容忍。数据不规范不包括通过 where 条件过滤掉的行。
7. partitions: 用于指定这次导入所设计的partition。如果用户能够确定数据对应的partition，推荐指定该项。不满足这些分区的数据将被过滤掉。
   比如指定导入到p1, p2分区，
   -H “partitions: p1, p2”
8. timeout: 指定导入的超时时间。单位秒。默认是 600 秒。可设置范围为 1 秒 ~ 259200 秒。
9. timezone: 指定本次导入所使用的时区。默认为东八区。该参数会影响所有导入涉及的和时区有关的函数结果。
10. exec_mem_limit: 导入内存限制。默认为 2GB。单位为字节。
11. format: 指定导入数据格式，默认是csv，支持json格式。
12. read_json_by_line: 布尔类型，为true表示支持每行读取一个json对象，默认值为false。
13. merge_type: 数据的合并类型，一共支持三种类型APPEND、DELETE、MERGE 其中，APPEND是默认值，表示这批数据全部需要追加到现有数据中，DELETE 表示删除与这批数据key相同的所有行，MERGE 语义 需要与delete 条件联合使用，表示满足delete 条件的数据按照DELETE 语义处理其余的按照APPEND 语义处理， 示例：-H “merge_type: MERGE” -H “delete: flag=1”
14. delete: 仅在 MERGE下有意义， 表示数据的删除条件 function_column.sequence_col: 只适用于UNIQUE_KEYS,相同key列下，保证value列按照source_sequence列进行REPLACE, source_sequence可以是数据源中的列，也可以是表结构中的一列。

Shell
– 准备数据
{“id”:1,“name”:“liuyan”,“age”:18}
{“id”:2,“name”:“tangyan”,“age”:18}
{“id”:3,“name”:“jinlian”,“age”:18}
{“id”:4,“name”:“dalang”,“age”:18}
{“id”:5,“name”:“qingqing”,“age”:18}

curl
-u root:
-H “label:load_local_file_json_20221126”
-H “columns:id,name,age”
-H “max_filter_ratio:0.1”
-H “timeout:1000”
-H “exec_mem_limit:1G”
-H “where:id>1”
-H “format:json”
-H “read_json_by_line:true”
-H “merge_type:delete”
-T /root/data/json.txt
http://doitedu01:8040/api/test/load_local_file_test/_stream_load

-H “merge_type:append” \

会把id = 3 的这条数据删除

-H “merge_type:MERGE”
-H “delete:id=3”

导入建议
• Stream Load 只能导入本地文件。
• 建议一个导入请求的数据量控制在 1 - 2 GB 以内。如果有大量本地文件，可以分批并发提交。
4.3外部存储数据导入(hdfs)
4.3.1 适用场景
• 源数据在 Broker 可以访问的存储系统中，如 HDFS。
• 数据量在几十到百 GB 级别。
4.3.2 基本原理

1. 创建提交导入的任务
2. FE生成执行计划并将执行计划分发到多个BE节点上(每个BE节点都导入一部分数据)
3. BE收到执行计划后开始执行，从broker上拉取数据到自己的节点上
4. 所有BE都完成后，FE决定是否导入成功，返回结果给客户端

SQL
                 +
                 | 1. user create broker load
                 v
            +----+----+
            |         |
            |   FE    |   生成导入计划
            |         |
            +----+----+
                 |
                 | 2. BE etl and load the data
    +--------------------------+
    |            |             |
+---v---+     +--v----+    +---v---+
|       |     |       |    |       |
|  BE   |     |  BE   |    |   BE  |
|       |     |       |    |       |
+---+-^-+     +---+-^-+    +--+-^--+