routine_load_job

Overview

Used to view information about routine load jobs.

Database and Table

information_schema.routine_load_jobs

Table Information

Column Name	Type	Description	Example
JOB_ID	text	Job ID generated by Doris.	12025
JOB_NAME	text	Routine Load job name.	example_routine_load
CREATE_TIME	text	Job creation time.	2024-01-15 08:12:42
PAUSE_TIME	text	Most recent job pause time. It is NULL if the job has not been paused.	NULL
END_TIME	text	Job end time. It is NULL if the job has not ended.	NULL
DB_NAME	text	Database name of the job.	default_cluster:testdb
TABLE_NAME	text	Target table name of the job. For multi-table import jobs, this value is multi-table.	test_routineload_tbl
STATE	text	Job running status, including NEED_SCHEDULE, RUNNING, PAUSED, STOPPED, and CANCELLED.	RUNNING
CURRENT_TASK_NUM	text	Number of subtasks currently being scheduled or executed.	1
JOB_PROPERTIES	text	Job property configurations, including batch size, concurrency, import format, column mapping, and error tolerance.	{"max_batch_rows":"200000","format":"csv","columnToColumnExpr":"user_id,name,age","max_filter_ratio":"1.0"}
DATA_SOURCE_PROPERTIES	text	Data source property configurations. For Kafka, this includes topic, broker list, and current Kafka partitions.	{"topic":"test-topic","currentKafkaPartitions":"0","brokerList":"192.168.88.62:9092"}
CUSTOM_PROPERTIES	text	Custom properties configured when creating the job. For Kafka, this usually includes offsets, group id, and Kafka client parameters passed with property. prefixes.	{"kafka_default_offsets":"OFFSET_BEGINNING","group.id":"example_routine_load_73daf600-884e-46c0-a02b-4e49fdf3b4dc"}
STATISTIC	text	Job runtime statistics. Common fields include receivedBytes, loadedRows, errorRows, committedTaskNum, abortedTaskNum, loadRowsRate, and taskExecuteTimeMs.	{"receivedBytes":28,"runningTxns":[],"errorRows":0,"committedTaskNum":3,"loadedRows":3,"loadRowsRate":0,"abortedTaskNum":0,"errorRowsAfterResumed":0,"totalRows":3,"unselectedRows":0,"receivedBytesRate":0,"taskExecuteTimeMs":30069}
PROGRESS	text	Job running progress. For Kafka, it shows the consumed offset of each partition.	{"0":"2"}
LAG	text	Job lag information. For Kafka, it shows the consumption lag of each partition.	{"0":0}
REASON_OF_STATE_CHANGED	text	Reason for the job state change. It is usually empty for normally running jobs, and records the specific reason when the job is paused or cancelled abnormally.	The number of failed task exceeded max_error_number
ERROR_LOG_URLS	text	Error log URLs for viewing filtered data that failed quality checks. It is empty if there is no error log.	http://fe_host:8030/api/_load_error_log?file=error.log
USER_NAME	text	User who created or operated the job.	root
CURRENT_ABORT_TASK_NUM	int	Current number of failed subtasks.	0
IS_ABNORMAL_PAUSE	boolean	Whether the job was paused abnormally by the system instead of manually by a user. true indicates an abnormal system pause, and false indicates no abnormal pause.	false

Query Abnormal Jobs

If a job is abnormally paused, has failed tasks, or is in the RUNNING state but has no running subtasks while Kafka still has consumption lag, it should be investigated. You can use the following SQL to query these jobs:

SELECT DB_NAME, JOB_NAME
FROM information_schema.routine_load_jobs
WHERE IS_ABNORMAL_PAUSE = TRUE
   OR (
        STATE = 'RUNNING'
        AND (
             CURRENT_ABORT_TASK_NUM > 0
             OR (
                  CAST(CURRENT_TASK_NUM AS INT) = 0
                  AND `LAG` REGEXP ':[[:space:]]*[1-9][0-9]*'
             )
        )
   );

After finding an abnormal job, switch to the corresponding database and use SHOW ROUTINE LOAD to view the job details:

USE `db_name`;
SHOW ROUTINE LOAD FOR `job_name`;

LAG is the Kafka consumption lag information for each partition. LAG REGEXP ':[[:space:]]*[1-9][0-9]*' matches jobs where at least one partition has a lag greater than 0.