SHOW LOAD
功能
查看数据库中指定导入作业的相关信息,包括 Broker Load、INSERT 和 Spark Load。您还可以使用 curl
命令查看这些导入作业的相关信息。注意,从 3.1 版本起,对于 Broker Load 和 Insert 导入,推荐您通过 SELECT 语句从 information_schema
数据库中的 loads
表来查看作业结果。详请参见从 HDFS 导入、从云存储导入、通过 INSERT 语句导入数据和使用 Apache Spark™ 批量导入。
除了以上三种导入方式, StarRocks 还支持 Stream Load 和 Routine Load,其中 Stream Load 是同步操作,会直接返回结果,不会通过 SHOW LOAD 展示。Routine Load 可通过 SHOW ROUTINE LOAD 查看导入作业的相关信息。
语法
SHOW LOAD [ FROM db_name ]
[
WHERE [ LABEL { = "label_name" | LIKE "label_matcher" } ]
[ [AND] STATE = { "PENDING" | "ETL" | "LOADING" | "FINISHED" | "CANCELLED" } ]
]
[ ORDER BY field_name [ ASC | DESC ] ]
[ LIMIT { [offset, ] limit | limit OFFSET offset } ]
说明
返回结果中的字段较多,可使用
\G
分行,如SHOW LOAD WHERE LABEL = "label1"\G
。详情参见示例一。
参数说明
参数 | 必选 | 说明 |
---|---|---|
db_name | 否 | 数据库名称。如不指定该参数,则默认查看当前数据库的导入作业。 |
LABEL = "label_name" | 否 | 导入作业标签。 |
LABEL LIKE "label_matcher" | 否 | 查看标签中包含 label_matcher 的导入作业。 |
AND | 否 |
|
STATE | 否 | 导入作业的状态。不同的导入方式有不同的导入作业状态,具体如下:
STATE 参数,则默认显示所有状态的导入作业;如指定,则显示指定状态的导入作业。例如 STATE = "PENDING" 会显示状态为 PENDING 的导入作业。 |
ORDER BY field_name [ASC | DESC] | 否 | 将返回结果按照指定字段升序或降序排列,当前支持的排序字段(field_name)包括 JobId 、Label 、State 、Progress 、Type 、EtlInfo 、TaskInfo 、ErrorMsg 、CreateTime 、EtlStartTime 、EtlFinishTime 、LoadStartTime 、LoadFinishTime 、URL 和 JobDetails 。
JobId 升序排列。 |
LIMIT limit | 否 | 查看指定数量的作业。如不指定该参数,则默认显示所有符合筛选条件的作业;如指定,则显示指定数量的作业。例如 LIMIT 10 会显示 10 个符合筛选条件的作业。 |
OFFSET offset | 否 | offset 定义了返回结果中跳过的导入作业的数量,其默认值为 0。例如 OFFSET 5 表示跳过前 5 个导入作业,返回剩下的结果。 |
返回结果说明
+-------+-------+-------+----------+------+---------+----------+----------+------------+--------------+---------------+---------------+----------------+-----+------------+
| JobId | Label | State | Progress | Type | Priority | EtlInfo | TaskInfo | ErrorMsg | CreateTime | EtlStartTime | EtlFinishTime | LoadStartTime | LoadFinishTime | URL | JobDetails |
+-------+-------+-------+----------+------+---------+----------+----------+------------+--------------+---------------+---------------+----------------+-----+------------+
导入方式不同,返回结果中的字段取值也有差异,具体如下。
字段 | Broker Load | Spark Load | INSERT |
---|---|---|---|
JobId | 导入作业的全局唯一 ID,由 StarRocks 自动生成。 | 同 Broker Load。 | 同 Broker Load。 |
Label | 导入作业的标签。每个数据库中的导入作业都一个唯一的标签,不同数据库的导入作业标签可以重复。 | 同 Broker Load。 | 同 Broker Load。 |
State | 导入作业的状态,包括:
| 导入作业的状态,包括:
| 导入作业的状态,包括:
|
Progress | 导入作业所处的阶段。Broker Load 导入作业只有 LOAD 阶段,对应导入作业状态中的 LOADING 。 LOAD 进度为 0~100%。Broker Load 没有 ETL 阶段,所以 ETL 对应的取值并无实际意义。说明:
| 导入作业所处的阶段。Spark Load 导入作业,包括 ETL 和 LOAD 两种阶段,分别对应导入作业状态中的 ETL 和 LOADING 。 ETL 和 LOAD 进度均为 0~100%。说明同 Broker Load。 | 导入作业所处的阶段。INSERT 导入作业只有 LOAD 阶段,没有 ETL 阶段,所以 ETL 对应的取值并无实际意义。LOAD 进度为 0~100%。说明同 Broker Load。 |
Type | 导入方式,取值为 BROKER 。 | 导入方式,取值为 SPARK 。 | 导入方式, 取值为 INSERT 。 |
Priority | 导入作业的执行优先级,包括:LOWEST、LOW、NORMAL、HIGH 和 HIGHEST。 | - | - |
EtlInfo | ETL 信息,包括以下 3 个指标:
max-filter-ratio ,计算公式为:dpp.abnorm.ALL /(unselected.rows + dpp.abnorm.ALL + dpp.norm.ALL )。 | 同 Broker Load。 | ETL 信息。由于 INSERT 没有 ETL 阶段,所以该参数值为 NULL 。 |
TaskInfo | 创建导入作业时指定的参数,包括:
| 创建导入作业时指定的参数,包括:
| 创建导入作业时指定的参数,包括:
|
ErrorMsg | 导入作业的失败原因。当导入作业的状态为 PENDING ,LOADING 或 FINISHED 时,该参数值为 NULL 。当导入作业的状态为 CANCELLED 时,该参数值包括 type 和 msg 两部分:
| 导入作业的失败原因。当导入作业的状态为 PENDING 、ETL 、LOADING 或 FINISHED 时,该参数值为 NULL 。当导入作业的状态为 CANCELLED 时,该参数值包括 type 和 msg 两部分:
| 导入作业的失败原因。当导入作 业的状态为 FINISHED 时,该参数值为 NULL 。当导入作业的状态为 CANCELLED 时,该参数值包括 type 和 msg 两部分:
|
CreateTime | 导入作业创建的时间。 | 同 Broker Load。 | 同 Broker Load。 |
EtlStartTime | 由于 Broker Load 导入没有 ETL 阶段,所以该参数值和 LoadStartTime 相同。 | ETL 阶段开始的时间。 | 由于 INSERT 导入没有 ETL 阶段,所以该参数取值和 LoadStartTime 相同。 |
EtlFinishTime | 由于 Broker Load 导入没有 ETL 阶段,所以该参数值和 LoadStartTime 相同。 | ETL 阶段完成的时间。 | 由于 INSERT 导入没有 ETL 阶段,所以该参数取值和 LoadStartTime 相同。 |
LoadStartTime | LOAD 阶段开始的时间。 | 同 Broker Load。 | 同 Broker Load。 |
LoadFinishTime | 导入作业完成的时间。 | 同 Broker Load。 | 同 Broker Load。 |
URL | 导入作业中质量不合格数据的访问地址。您可以使用 curl 或 wget 命令打开该地址。如果导入作业不存在质量不合格数据,该参数值为 NULL 。 | 同 Broker Load。 | 同 Broker Load。 |
JobDetails | 导入作业的其他信息,包括:
| 同 Broker Load。 | 同 Broker Load。 |
注意事项
-
导入作业相关信息具有时效性。默认自一个导入作业的完成时间 (
LoadFinishTime
) 起 3 天内可以查看到该导入作业的相关信息;3 天后,信息会失效,无法查到。您可以通过 FE 参数label_keep_max_second
修改默认有效时间(单位:秒),操作如下:ADMIN SET FRONTEND CONFIG ("label_keep_max_second" = "value");
-
若导入作业的
LoadStartTime
长时间为N/A
,说明导入作业堆积严重,可降低作业创建的频率。 -
导入作业所消耗的总时长 =
LoadFinishTime
-CreateTime
。 -
LOAD
阶段所消耗的时长 =LoadFinishTime
-LoadStartTime
。
示例
示例一:查看当前数据库中的所有导入作业。
SHOW LOAD\G;
*************************** 1. row ***************************
JobId: 976331
Label: duplicate_table_with_null
State: FINISHED
Progress: ETL:100%; LOAD:100%
Type: BROKER
Priority: NORMAL
EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546
TaskInfo: resource:N/A; timeout(s):300; max_filter_ratio:0.0
ErrorMsg: NULL
CreateTime: 2022-10-17 19:35:00
EtlStartTime: 2022-10-17 19:35:04
EtlFinishTime: 2022-10-17 19:35:04
LoadStartTime: 2022-10-17 19:35:04
LoadFinishTime: 2022-10-17 19:35:06
URL: NULL
JobDetails: {"Unfinished backends":{"b90a703c-6e5a-4fcb-a8e1-94eca5be0b8f":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"b90a703c-6e5a-4fcb-a8e1-94eca5be0b8f":[10004]},"FileNumber":1,"FileSize":548622}
示例二:查看当前数据库中的导入作业。导入作业的标签中需包含字符串 null
,且仅展示 2 个作业。
SHOW LOAD
WHERE LABEL LIKE "null"
LIMIT 2;
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label | State | Progress | Type | EtlInfo | TaskInfo | ErrorMsg | CreateTime | EtlStartTime | EtlFinishTime | LoadStartTime | LoadFinishTime | URL | JobDetails |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10082 | duplicate_table_with_null | FINISHED | ETL:100%; LOAD:100% | BROKER | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:N/A; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:53:27 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:31 | NULL | {"Unfinished backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[10002]},"FileNumber":1,"FileSize":548622} |
| 10103 | unique_table_with_null | FINISHED | ETL:100%; LOAD:100% | SPARK | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:test_spark_resource_07af473a_1230_11ed_b483_00163e0e550b; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:56:06 | 2022-08-02 14:56:19 | 2022-08-02 14:56:41 | 2022-08-02 14:56:41 | 2022-08-02 14:56:44 | http://emr-header-1.cluster-49091:20888/proxy/application_1655710334658_26391/ | {"Unfinished backends":{"00000000-0000-0000-0000-000000000000":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"00000000-0000-0000-0000-000000000000":[-1]},"FileNumber":1,"FileSize":8790855} |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
示例三: 查看数据库 example_db
中的导入作业。导入作业的标签中需包含字符串 table
,且按 LoadStartTime
降序排序。
SHOW LOAD FROM example_db
WHERE LABEL Like "table"
ORDER BY LoadStartTime DESC;
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label | State | Progress | Type | EtlInfo | TaskInfo | ErrorMsg | CreateTime | EtlStartTime | EtlFinishTime | LoadStartTime | LoadFinishTime | URL | JobDetails |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10103 | unique_table_with_null | FINISHED | ETL:100%; LOAD:100% | SPARK | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:test_spark_resource_07af473a_1230_11ed_b483_00163e0e550b; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:56:06 | 2022-08-02 14:56:19 | 2022-08-02 14:56:41 | 2022-08-02 14:56:41 | 2022-08-02 14:56:44 | http://emr-header-1.cluster-49091:20888/proxy/application_1655710334658_26391/ | {"Unfinished backends":{"00000000-0000-0000-0000-000000000000":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"00000000-0000-0000-0000-000000000000":[-1]},"FileNumber":1,"FileSize":8790855} |
| 10082 | duplicate_table_with_null | FINISHED | ETL:100%; LOAD:100% | BROKER | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:N/A; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:53:27 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:31 | NULL | {"Unfinished backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[10002]},"FileNumber":1,"FileSize":548622} |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
示例四:查看数据库 example_db
中的导入作业。导入作业的标签需为 duplicate_table_with_null
,状态需为 FINISHED
。
SHOW LOAD FROM example_db
WHERE LABEL = "duplicate_table_with_null" AND STATE = "FINISHED";
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+----------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label | State | Progress | Type | EtlInfo | TaskInfo | ErrorMsg | CreateTime | EtlStartTime | EtlFinishTime | LoadStartTime | LoadFinishTime | URL | JobDetails |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+----------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10082 | duplicate_table_with_null | FINISHED | ETL:100%; LOAD:100% | BROKER | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:N/A; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:53:27 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:30 | 2022-08-02 14:53:31 | NULL | {"Unfinished backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"4393c992-5da1-4e9f-8b03-895dc0c96dbc":[10002]},"FileNumber":1,"FileSize":548622} |
+-------+---------------------------+----------+---------------------+--------+---------------------------------------------------------+----------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
示例五:查看数据库 example_db
中的导入作业。导入作业需按 CreateTime
升序排序,并从偏移量 1 开始显示 2 条查询结果。
SHOW LOAD FROM example_db
ORDER BY CreateTime ASC
LIMIT 2 OFFSET 1;
或
SHOW LOAD FROM example_db
ORDER BY CreateTime ASC
LIMIT 1,2;
返回结果如下:
+-------+---------------------------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| JobId | Label | State | Progress | Type | EtlInfo | TaskInfo | ErrorMsg | CreateTime | EtlStartTime | EtlFinishTime | LoadStartTime | LoadFinishTime | URL | JobDetails |
+-------+---------------------------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10103 | unique_table_with_null | FINISHED | ETL:100%; LOAD:100% | SPARK | unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=65546 | resource:test_spark_resource_07af473a_1230_11ed_b483_00163e0e550b; timeout(s):300; max_filter_ratio:0.0 | NULL | 2022-08-02 14:56:06 | 2022-08-02 14:56:19 | 2022-08-02 14:56:41 | 2022-08-02 14:56:41 | 2022-08-02 14:56:44 | http://emr-header-1.cluster-49091:20888/proxy/application_1655710334658_26391/ | {"Unfinished backends":{"00000000-0000-0000-0000-000000000000":[]},"ScannedRows":65546,"TaskNumber":1,"All backends":{"00000000-0000-0000-0000-000000000000":[-1]},"FileNumber":1,"FileSize":8790855} |
| 10120 | insert_3a57b595-1230-11ed-b075-00163e14c85e | FINISHED | ETL:100%; LOAD:100% | INSERT | NULL | resource:N/A; timeout(s):3600; max_filter_ratio:0.0 | NULL | 2022-08-02 14:56:26 | 2022-08-02 14:56:26 | 2022-08-02 14:56:26 | 2022-08-02 14:56:26 | 2022-08-02 14:56:26 | | {"Unfinished backends":{},"ScannedRows":0,"TaskNumber":0,"All backends":{},"FileNumber":0,"FileSize":0} |
+-------+---------------------------------------------+----------+---------------------+--------+---------------------------------------------------------+---------------------------------------------------------------------------------------------------------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+--------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+