第3.7章：StarRocks数据导入--Broker Load

一、Broker Load背景

基于Hadoop生态的传统数仓目前仍拥有非常大的用户群体，为此StarRocks加入了Broker Load导入方式，让我们可以方便的从HDFS类的外部存储系统（Apache HDFS、阿里OSS、腾讯COS、百度BOS、Amazon S3等）中导入数据，高效的完成数据迁移工作。

因为一些历史原因，我们需要使用Broker组件来协助进行Broker Load。Broker是一个独立的无状态进程，封装了文件系统接口，为StarRocks提供读取远端存储系统中文件的能力。通过部署的Broker程序，StarRocks可读取数据源上的数据，利用自身的计算资源对数据进行预处理和导入（这里也是Broker Load和Spark Load最大的区别，后面章节会提到，Spark Load是使用外部的Spark计算资源数数据进行预处理）。

Broker Load支持CSV、ORCFile、Parquet等文件格式，支持单次导入的数据量在几十GB到上百GB级别，导入速度最快可达到上百万条每秒（部分场景测试导入速度可达百兆每秒）。当数据量更大时Broker Load也是支持的，但是相对来说失败重试的时间成本会比较高。

Broker Load是一种异步的导入方式，我们通过MySQL协议创建导入任务，任务创建成功后会立刻返回创建结果，但任务的执行状态及执行结果我们还需要通过查看命令检查。

官网文档关于Broker Load的介绍地址为：

Broker Load @ BrokerLoad @ StarRocks Docshttps://docs.starrocks.com/zh-cn/main/loading/BrokerLoad

BROKER LOAD @ BROKER LOAD @ StarRocks Docshttps://docs.starrocks.com/zh-cn/main/sql-reference/sql-statements/data-manipulation/BROKER%20LOAD

LOAD @ LOAD @ StarRocks Docshttps://docs.starrocks.com/zh-cn/main/sql-reference/sql-statements/data-manipulation/LOAD

二、导入准备

Broker Load并不是一个复杂的导入方式，初步了解了Broker Load的理论知识，接下来我们直接通过几个完整的导入例子来进行学习。

2.1集群准备

节点IP	部署服务	端口	版本	说明
192.168.116.101 [node01]	FE(Leader) BE Broker mysql-client	9030 8000	StarRocks-1.19.3	用户名：root 密码：root
192.168.110.102 [node02]	FE(Observer) BE Broker			Broker名称均为hdfs_broker
192.168.116.103 [node03]	BE Broker
192.168.110.201 [hadoop01]	NameNode	8020	hadoop-3.1.3 [非HA]	core-site.xml中fs.defaultFS参数的端口
	DataNode			Hadoop所用端口均为默认
	NodeManager			用户名：starrocks 无密码
192.168.110.202 [hadoop02]	DataNode		hadoop-3.1.3
	ResourceManager
	NodeManager
192.168.110.203 [hadoop03]	SecondaryNameNode		hadoop-3.1.3
	DataNode
	NodeManager

2.2数据准备

将存储在Hadoop中的数据文件customer.csv导入至StarRocks集群ods_data_load库的customer表中。数据文件在Hadoop中的完整路径为：/broker_test_data/customer.csv（数据文件下载地址见评论区百度云），数据文件大小约为3M，共有数据3w行，其列的顺序与customer表的一致，列分隔符为“|”，前三行数据如下：

1|Customer#000000001|j5JsirBM9P|MOROCCO  0|MOROCCO|AFRICA|25-989-741-2988|BUILDING

2|Customer#000000002|487LW1dovn6Q4dMVym|JORDAN   1|JORDAN|MIDDLE EAST|23-768-687-3665|AUTOMOBILE

3|Customer#000000003|fkRGN8n|ARGENTINA7|ARGENTINA|AMERICA|11-719-748-3364|AUTOMOBILE

2.3表准备

Broker Load导入不会自动创建表结构，我们需要在导入前先手动创建表。在node01上登录StarRocks：

[root@node01 ~]# mysql -h192.168.110.101 -P9030 -uroot -proot

mysql: [Warning] Using a password on the command line interface can be insecure.
…………

Server version: 5.1.0 StarRocks version 1.19.3

…………

mysql>

在ods_data_load库下创建customer表：

mysql> create database if not exists ods_data_load;

mysql> use ods_data_load;

mysql> CREATE TABLE IF NOT EXISTS ods_data_load.`customer` (

  `c_custkey` int(11) NOT NULL COMMENT "",

  `c_name` varchar(26) NOT NULL COMMENT "",

  `c_address` varchar(41) NOT NULL COMMENT "",

  `c_city` varchar(11) NOT NULL COMMENT "",

  `c_nation` varchar(16) NOT NULL COMMENT "",

  `c_region` varchar(13) NOT NULL COMMENT "",

  `c_phone` varchar(16) NOT NULL COMMENT "",

  `c_mktsegment` varchar(11) NOT NULL COMMENT ""

)DUPLICATE KEY(`c_custkey`)

DISTRIBUTED BY HASH(`c_custkey`) BUCKETS 12;

三、任务分析

在执行导入任务前，我们通常需要从三个方面来分析任务：数据量、分隔符、列顺序。

3.1数据量

数据量主要和导入时间有关，咱们可以参考官方给出的超时时间计算公式：

超时时间 > （（总文件大小(MB) * 待导入的表及相关物化视图的个数） / (30 * 导入并发数））

导入并发数见文档最后的导入系统配置说明，公式中的30为目前BE导入的平均速度：30MB/s。

例如对于1GB的待导入数据文件，待导入表包含2个物化视图，当前的导入并发数为3。则 timeout 的最小值为 (1 * 1024 * 3 ) / (10 * 3) = 102 秒

任务超时时间可直接在导入语句中配置，默认"timeout" = "3600"，单位为秒，即四小时。

此外，当单次导入的数据量较大时，我们可能还需要为任务设置导入内存参数load_mem_limit，单位是字节，默认是2GB。

本次演示使用的数据文件customer.csv仅为3M，超时时间和内存无需特别设置。

3.2分隔符

Broker Load命令中的分隔符需要和数据文件中的保持一致，Broker Load不支持设置行分隔符，只能使用默认的\n（也就是回车换行符）作为行分隔符。支持自定义列分隔符，同时也支持多字符分隔符。

观察customer.csv中的数据，确定列分隔符需设置为“|”，行分隔符为默认的换行符。

3.3列顺序

Broker Load也是支持在导入过程中进行字段顺序调整或者进行简单的数据转换的，这里的逻辑还可以参考Stream Load章节，“顺序占位取值”和“名称匹配数据”的逻辑贯穿整个StarRocks的导入操作。

数据文件customer.csv中数据列的顺序与customer表一致，我们不需要在导入语句中进行列的调整，也不需要生成衍生列，可以直接导入。

四、语法介绍

4.1示例演示

在介绍Broker Load语法前，我们先将上面的例子执行完，在sql中执行Broker Load导入语句：

mysql> LOAD LABEL ods_data_load.customer_label210910

(

    DATA INFILE("hdfs://192.168.110.201:8020/broker_test_data/customer.csv")

    INTO TABLE customer

    COLUMNS TERMINATED BY "|"

)

WITH BROKER 'hdfs_broker';

其中hdfs_broker是我们在搭建集群时为Broker程序定义的名称。执行完成后，使用label查看导入任务状态：

mysql> show load where label = 'customer_label211206'\G

*************************** 1. row ***************************

         JobId: 11293

         Label: customer_label211206

         State: FINISHED

      Progress: ETL:100%; LOAD:100%

          Type: BROKER

       EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=30000

      TaskInfo: cluster:N/A; timeout(s):3600; max_filter_ratio:0.0

      ErrorMsg: NULL

    CreateTime: 2021-12-06 21:27:10

  EtlStartTime: 2021-12-06 21:27:12

 EtlFinishTime: 2021-12-06 21:27:12

 LoadStartTime: 2021-12-06 21:27:12

LoadFinishTime: 2021-12-06 21:27:14

           URL: NULL

    JobDetails: {"Unfinished backends":{"c03881a4-bb20-4fa2-abe8-d4d942fd1d08":[]},"ScannedRows":30000,"TaskNumber":1,"All backends":{"c03881a4-bb20-4fa2-abe8-d4d942fd1d08":[11119]},"FileNumber":1,"FileSize":2807046}

1 row in set (0.01 sec)

状态为FINISHED，数据导入成功。查看customer表中的数据条数：

mysql> select count(1) from customer;

+-----------------+

|     count(1)    |

+-----------------+

|        30000    |

+-----------------+

数据条数对的上，导入就完成了。

Broker Load是异步导入，在导入完成前，正在执行的Broker Load导入任务也可以通过命令取消，写法例如：

mysql> CANCEL LOAD FROM ods_data_load WHERE LABEL = "customer_label10086";

4.2任务状态信息

Broker Load的语法介绍篇幅会比较长，我们就先对任务执行的状态信息解读一下。

1）JobId

导入任务的唯一ID，由系统自动生成，每个导入任务的JobId都不同，且不会被复用。

2）Label

导入任务的标识，通过Label我们可以方便的查看任务状态或在日志中定位问题。这个Label数据库内唯一，任务成功后不能复用。

3）State

导入任务当前所处的阶段。Broker load中会出现四种状态：

• PENDING：导入任务正在等待被执行
• LOADING：导入正在执行中
• CANCELLED：导入失败，可以依据ErrorMsg和URL中的信息排查问题
• FINISHED：导入成功

4）Progress

导入任务的进度描述。分为两种进度：ETL和LOAD，对应了导入流程的两个阶段ETL和LOADING。目前Broker load只有LOADING阶段（Spark Load才会有ETL阶段），所以ETL会一直显示为N/A。LOAD的进度范围为：0~100%（LOAD进度 = 当前完成导入的表个数 / 本次导入任务设计的总表个数 * 100%）。所有表完成导入后，LOAD的进度会为99%，导入会进入到最后生效阶段，整个导入完成后，LOAD的进度才会改为100%。导入进度并不是线性的，如果一段时间内进度没有变化，并不代表导入没有在执行。

5）Type

导入任务的类型。Broker Load的type取值是BROKER。

6）EtlInfo

展示导入的数据量指标unselected.rows（被where正常过滤的条数）、dpp.norm.ALL（导入过程中正确数据的条数）和dpp.abnorm.ALL（数据质量不合格行数）。我们可以根据第一个数值判断where条件过滤了多少行，后两个指标验证当前导入任务的错误率是否超过max-filter-ratio。三个指标之和就是原始数据量的总行数。

7）TaskInfo

显示当前导入任务参数，也就是创建Broker Load导入任务时我们指定的那些参数，包括：cluster（通常就是默认），timeout和max-filter-ratio。

8）ErrorMsg

如果导入任务成功这里会显示N/A。若任务失败，即状态State为CANCELLED，这里会显示失败的原因，包括两部分：type和msg。type的取值意义：

1. USER-CANCEL：用户取消的任务
2. ETL-RUN-FAIL：在ETL阶段失败的导入任务
3. ETL-QUALITY-UNSATISFIED：数据质量不合格，也就是错误数据率超过了max-filter-ratio
4. LOAD-RUN-FAIL：在LOADING阶段失败的导入任务
5. TIMEOUT：导入任务没在超时时间内完成
6. UNKNOWN：未知的导入错误

9）CreateTime/EtlStartTime/EtlFinishTime/LoadStartTime/LoadFinishTime

这几个值分别代表导入创建的时间、ETL阶段开始的时间、ETL阶段完成的时间、Loading阶段开始的时间和整个导入任务完成的时间。Broker Load导入由于没有ETL阶段，所以其EtlStartTime、EtlFinishTime、LoadStartTime被设置为同一个值。

10）URL

导入任务的错误数据样例，访问URL地址可以获取本次导入的错误数据样例。当导入不存在错误数据时，URL字段则为N/A。

11）JobDetails

显示作业的详细运行状态。包括导入文件的个数、总大小（字节）、子任务个数、已处理的原始行数、运行子任务的BE节点Id，未完成的BE节点Id。其中已处理的原始行数，每5秒更新一次。这里的行数仅用于展示当前的进度，不代表最终实际的处理行数，实际处理行数以EtlInfo中显示的数据为准。

4.3导入语法介绍

Broker Load命令的语法为：

LOAD LABEL db_name.label_name

    (data_desc, ...)

WITH BROKER broker_name broker_properties

    [PROPERTIES (key1=value1, ... )]

咱们上面演示的例子非常简单，下面就以一个从Apache HDFS中导入数据的完整语句为例，我们逐行进行解析：

LOAD LABEL db1.label1

(

    DATA INFILE("hdfs://abc.com:8888/user/starRocks/test/ml/file1")

    INTO TABLE tbl1

    COLUMNS TERMINATED BY ","

    (tmp_c1, tmp_c2, tmp_c3)

    SET

    (

        id=tmp_c2,

        name=tmp_c1,

        svalue=LEFT(tmp3,12)

    ),

    DATA INFILE("hdfs://abc.com:8888/user/starRocks/test/ml/file2")

    NEGATIVE

INTO TABLE tbl2

PARTITION (p1, p2)

COLUMNS TERMINATED BY ","

FORMAT AS csv

(col1, col2)

COLUMNS FROM PATH AS (colx, ...)

    where col1 > 1

)

WITH BROKER 'broker_name'

(

    "username" = "hdfs_username",

    "password" = "hdfs_password"

)

PROPERTIES

(

    "timeout" = "3600"

);

1）Label

导入任务的标识，需要手动指定，Label后的格式为“数据库名称.Label名”，该Label在该数据库内唯一，任务失败时label可复用。

2）多表导入

Broker Load支持在一个导入任务中进行同数据库下多张表的导入，每张表的导入我们需要单独声明其数据描述部分（即data_desc部分）。Broker load可以保证单次导入的多张表之间原子性成功或失败（都成功或者都失败）。

后面的参数可以规整为三个部分：

4.3.1 data_desc部分

1）DATA INFILE

数据文件在HDFS中的路径。文件路径可以指定到一个文件，也可以用*通配符指定某个目录下的所有文件。中间的目录也可以使用通配符匹配，这里可以参考hadoop文档中的通配符使用规则：

FileSystem (Apache Hadoop Main 3.3.1 API)https://hadoop.apache.org/docs/stable/api/org/apache/hadoop/fs/FileSystem.html#globStatus-org.apache.hadoop.fs.Path-

2）NEGATIVE

可选，表示数据取反导入。这个参数适用的场景是当数据表中聚合列的类型都为SUM类型时，如果希望撤销某一批导入的数据，可以通过negative参数导入同一批数据，StarRocks会自动为这一批数据在聚合列上数据取反，以达到消除同一批数据的功能。

3）INTO TABLE

数据导入的目标表。

4）PARTITION

可选择导入表的部分分区，如果导入的数据不在这些分区中，会被视为质量不合格的数据计入dpp.abnorm.ALL，所以这里可以配合Where进行筛选。

5）COLUMNS TERMINATED

用于指定导入文件中的列分隔符，默认为\t。如果是不可见字符，则需要加\x作为前缀，使用十六进制来表示分隔符。例如hive文件的默认分隔符\001，导入时这里可指定为"\\x01"。目前Broker Load只支持定义列分隔符，不支持自定义行分隔符，行分隔符只能使用默认的\n。

6）FORMAT AS

用于指定导入文件的类型，例如：parquet、orc、csv，默认为csv。其中parquet类型也可以通过文件后缀名.parquet或者.parq判断。parquet文件不需要设置分隔符，parquet文件的列可以自动解释，设置分隔符没有意义（但也不会报错）。

7）(col1, col2)

也就是column_list，设置从数据源中的取值顺序。当HDFS数据文件中的字段顺序和StarRocks表中的顺序一致时，这里可以省略。

8）SET ()

设置列名、进行函数变换或生成衍生列，如果原始数据的列和表中的列不一一对应，或者希望在导入过程中进行一些数据转换，就可以用这个属性进行设置。

这里还有一个社区时有提到的问题，使用某些Hive版本直接生成的ORC文件，ORC文件中的表头并非Hive meta数据，而是（_col0, _col1, _col2, ...），若我们直接导入可能出现Invalid Column Name的错误，那么就需要使用set进行映射，例如：

(_col0,_col1, _col2)

SET

(

   id=_col0,

   name=_col1,

   svalue=_col2

)

9）COLUMNS FROM PATH AS

提取文件路径中分区字段的值（该参数需要配合前面第7条的column_list参数使用）。例如HDFS中文件路径为/starrocks/uid=1001/customer.csv，我们就可以通过COLUMNS FROM PATH AS (uid)获取到路径中uid的值1001。Hive分区较多的情况下，我们也可以使用/*/*的通配符，来整理处理获取。

10）WHERE

对前面完成转换的数据进行过滤，被过滤的数据不会计入容忍率的统计中。

4.3.2 BROKER部分

1）broker_name

需要填写要使用的broker的name属性，该属性由我们在将Broker添加如集群时设置，可以通过“show broker;”查看。这里也有一个小技巧，前面我们发现一个Broker Load任务只能指定一个broker name，所以我们可以将多个broker设置为相同的broker name，这样相当于启动了多个broker进程执行导入。

2）username

访问HDFS时需用的用户名。

3）password

访问HDFS时用户名对应的密码。

4.3.3 PROPERTIES部分

1）timeout

导入作业的超时时间（以秒为单位）。我们可以在这里设置每个导入的超时时间。导入任务在设定的时限内未完成则会被系统取消，变成CANCELLED。Broker Load的默认导入超时时间为4小时。导入速度受带宽和BE个数影响，万兆网卡下单个BE的导入速度可粗估为30M/秒，可以以此初步设置任务超时时间。

2）max_filter_ratio

导入任务的最大容忍率，默认为0容忍，取值范围是0~1。当导入的错误率超过该值，则导入失败（被where筛选出的数据不会被视为异常数据）。如果我们允许忽略一定比例的脏数据，可以通过设置这个参数大于0，来保证导入可以成功。计算公式为：

max_filter_ratio = (dpp.abnorm.ALL / (dpp.abnorm.ALL + dpp.norm.ALL))

参数说明：

dpp.abnorm.ALL：数据质量不合格的行数，如类型不匹配、列数不匹配、长度不匹配等。

dpp.norm.ALL：指的是导入过程中正确数据的条数。

3）load_mem_limit

导入内存限制，单位是字节，默认是2GB，当导入的数据量很大或者有衍生列时，可以适当调整。

4）strict_mode

严格模式的意思是对于导入过程中的列类型转换进行严格过滤，对非空数据转换为空的情况进行限制。默认关闭，开启方式为："strict_mode" = "true"。

五、配置调优

在配置文件中，和Broker Load相关的参数主要有：

1）desired_max_waiting_jobs

FE参数，等待队列可以容纳的最多导入任务数目。该参数限制集群内，未开始或正在运行（作业状态为PENDING或LOADING）的Broker Load作业数量。默认为100。如果超过这个阈值，新提交的作业将会被直接拒绝。

2）max_running_txn_num_per_db

每个数据库中正在运行的导入任务的最大个数（不区分导入类型、统一计数）。

3）broker_load_default_timeout_second

FE参数，Broker导入的超时时间，默认4个小时。

4）load_parallel_instance_num

FE参数，单个BE上导入任务的并发实例数，默认为1。

5）min_bytes_per_broker_scanner

FE参数，单个实例处理的最小数据量，默认64MB。

6）max_broker_concurrency

FE参数，单个任务最大并发实例数，默认100个。

Broker Load通常不需要单独调优，默认配置即可满足绝大多数业务场景。如果业务中确实希望进行调优，我们通常只需要适当调大load_parallel_instance_num参数（例如配置为2或者3）提高实例并行度。这里的并行度和查询并行度不同，所以我们再引申一下Broker Load中“作业-->任务-->实例”的概念。

首先，一个导入作业可以拆成一个或者多个任务，各任务之间并行执行。拆分由LOAD语句中的DataDescription来决定。任务拆分规则：

• 多个data_desc对应导入多个不同的表，每个会拆成一个任务；
• 多个data_desc对应导入同一个表的不同分区，每个也会拆成一个任务。

每个任务还会拆分成一个或者多个实例，然后将这些实例平均分配到BE上并行执行。

实例的拆分由上面提到的三个FE参数决定：min_bytes_per_broker_scanner：单个实例处理的最小数据量，默认64MB。max_broker_concurrency：单个任务最大并发实例数，默认100个。load_parallel_instance_num：单个BE上并发实例数，默认1个。

实例的总数=min(导入文件总大小/单个实例处理的最小数据量，单个任务最大并发实例数，单个BE上并发实例数*BE数)

综上，一般情况下，一个作业只有一个data_desc，即只会拆分成一个任务。任务会拆成与BE数相等的实例，然后分配到所有BE上并行执行。

六、语法拓展

6.1 Hadoop HA时的Broker Load写法

LOAD LABEL db1.label1

(

    DATA INFILE("hdfs://nameservice1/starRocks/file1")

    INTO TABLE tbl1

    COLUMNS TERMINATED BY ","

    (tmp_c1, tmp_c2)

    SET

    (

        id=tmp_c2,

        name=tmp_c1

    )

)

WITH BROKER 'brokername'

(

    "username" = "hdfs_username",

    "password" = "hdfs_password",

    "dfs.nameservices" = "nameservice1",

    "dfs.ha.namenodes.nameservice1" = "namenode01,namenode02",

    "dfs.namenode.rpc-address.nameservice1.namenode01" = "nn01.starRocks:8020",

    "dfs.namenode.rpc-address.nameservice1.namenode02" = "nn02.starRocks:8020",

    "dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"

)

PROPERTIES

(

    "timeout" = "3600"

);

在每个任务中写上长串的HA配置是不太友好的，我们可以将上面的红字部分直接配置在apache_hdfs_broker/conf/hdfs-site.xml中，例如：

<!-- For HDFS HA -->

         <property>

             <name>dfs.nameservices</name>

             <value>nameservice1</value>

         </property>

         <property>

             <name>dfs.ha.namenodes.nameservice1</name>

             <value>namenode01,namenode02</value>

         </property>

         <property>

             <name>dfs.namenode.rpc-address.nameservice1.namenode01</name>

             <value>hadoop101:8020</value>

         </property>

         <property>

             <name>dfs.namenode.rpc-address.nameservice1.namenode02</name>

             <value>hadoop102:8020</value>

         </property>

         <property>

             <name>dfs.client.failover.proxy.provider.nameservice1</name>

             <value>org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider</value>

         </property>

</configuration>

6.2 HA+Kerberos时Broker Load写法

LOAD LABEL db1.label1

(

    DATA INFILE("hdfs://nameservice1/starRocks/file1")

    INTO TABLE tbl1

    COLUMNS TERMINATED BY ","

    (tmp_c1, tmp_c2)

    SET

    (

        id=tmp_c2,

        name=tmp_c1

    )

)

WITH BROKER 'brokername'

(

    "dfs.nameservices" = "nameservice1",

    "dfs.ha.namenodes.nameservice1" = "namenode01, namenode02",

    "dfs.namenode.rpc-address.nameservice1.namenode01" = "nn01.starRocks:8020",

    "dfs.namenode.rpc-address.nameservice1.namenode02" = "nn02.starRocks:8020",

    "dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider",

    "hadoop.security.authentication" = "kerberos",

    "kerberos_principal" = "starRocks01@STARROCKS.COM",

    "kerberos_keytab" = "/starRocks/apache_hdfs_broker/conf/starRocks01.keytab"

)

PROPERTIES

(

    "timeout" = "3600"

);