| --- |
| { |
| "title": "TASKS", |
| "language": "en", |
| "description": "Table function, generates a temporary table of tasks," |
| } |
| --- |
| |
| ## Description |
| |
| Table function, generates a temporary table of tasks, which allows you to view the information of tasks generated by jobs in the current Doris cluster. |
| |
| ## Syntax |
| ```sql |
| TASKS( |
| "type"="<type>" |
| ) |
| ``` |
| |
| ## Required Parameters |
| | Field | Description | |
| |---------------|---------------------------------------------------------------------------------------------------| |
| | **`<type>`** | Type of the task: <br/> `insert`: insert into type task. <br/> `mv`: materialized view type task. | |
| |
| |
| ## Return Value |
| |
| - **`tasks("type"="insert")`** tasks return value of type insert |
| |
| | Field Name | Description | |
| |--------------|------------------------------------| |
| | **TaskId** | Task id | |
| | **JobId** | Job id | |
| | **JobName** | Job name | |
| | **Label** | Label | |
| | **Status** | Task status | |
| | **ErrorMsg** | Task failure information | |
| | **CreateTime**| Task creation time | |
| | **FinishTime**| Task completion time | |
| | **TrackingUrl**| Tracking URL | |
| | **LoadStatistic**| Load statistics | |
| | **User** | User | |
| |
| - **`tasks("type"="mv")`** tasks return value of type MV |
| |
| | Field Name | Description | |
| |------------|-------------| |
| | **TaskId** | Unique ID of the refresh task. Each materialized view refresh creates a new task record. | |
| | **JobId** | ID of the job that generated this task. It corresponds to `jobs("type"="mv").Id`. | |
| | **JobName** | Name of the job that generated this task. It corresponds to `mv_infos("database"="...").JobName` and `jobs("type"="mv").Name`. | |
| | **MvId** | ID of the materialized view refreshed by this task. | |
| | **MvName** | Name of the materialized view refreshed by this task. | |
| | **MvDatabaseId** | ID of the database that contains the materialized view. | |
| | **MvDatabaseName** | Name of the database that contains the materialized view. | |
| | **Status** | Task status. Possible values: `PENDING` means the task is waiting to run; `RUNNING` means the task is running; `SUCCESS` means the task finished successfully; `FAILED` means the task failed; `CANCELED` means the task was canceled. | |
| | **ErrorMsg** | Error message when `Status` is `FAILED` or `CANCELED`. Empty when the task succeeds. | |
| | **CreateTime** | Time when the task record was created. | |
| | **StartTime** | Time when the task started running. It can be `\N` if the task has not started. | |
| | **FinishTime** | Time when the task finished. It can be `\N` if the task is still pending or running. | |
| | **DurationMs** | Runtime in milliseconds, calculated as `FinishTime - StartTime`. It can be `\N` if the task has not finished. | |
| | **TaskContext** | JSON string describing how the task was triggered and what the user requested. Common fields include `triggerMode`, `partitions`, and `isComplete`. `triggerMode` values are `MANUAL`, `COMMIT`, and `SYSTEM`. | |
| | **RefreshMode** | Actual refresh scope decided by this task. Possible values: `COMPLETE` means all materialized view partitions were refreshed; `PARTIAL` means only some partitions were refreshed; `NOT_REFRESH` means no partition needed refreshing. | |
| | **NeedRefreshPartitions** | JSON array of materialized view partitions that needed refreshing in this task. Empty array means no partition needed refreshing. | |
| | **CompletedPartitions** | JSON array of partitions that were refreshed successfully. Compare it with `NeedRefreshPartitions` to see whether all required partitions completed. | |
| | **Progress** | Refresh progress in the format `percentage (completed/total)`, for example `100.00% (1/1)`. It can be `\N` when there is no partition to refresh. | |
| | **LastQueryId** | Query ID of the SQL statement executed by the refresh task. Use this ID to search FE or BE logs when troubleshooting task failures. It can be empty when no refresh SQL was executed. This field is supported since Doris 3.0.0. | |
| |
| ### MV task enum fields |
| |
| The following enum fields are commonly used when checking materialized view refresh tasks: |
| |
| - `Status`: lifecycle state of the task. |
| - `PENDING`: the task has been created but has not started running. It is waiting for scheduling or resources. |
| - `RUNNING`: the task is currently running. |
| - `SUCCESS`: the task finished successfully. |
| - `FAILED`: the task failed. Check `ErrorMsg` first, and use `LastQueryId` to search logs if it is not empty. |
| - `CANCELED`: the task was canceled before it finished. |
| - `TaskContext.triggerMode`: why this task was created. |
| - `MANUAL`: created by a manual refresh command, such as `REFRESH MATERIALIZED VIEW`. |
| - `COMMIT`: created because data changes on related base tables triggered refresh. |
| - `SYSTEM`: created by an internal system action, for example the initial build of a materialized view created with immediate build. |
| - `TaskContext.isComplete`: whether the refresh request asks for a complete refresh. |
| - `true`: the request asks Doris to refresh all materialized view partitions. |
| - `false`: the request does not force complete refresh. Doris can decide the actual refresh scope based on partition freshness. |
| - `RefreshMode`: actual refresh scope selected by the task after checking partitions. |
| - `COMPLETE`: all materialized view partitions that belong to the MV were selected for refresh. |
| - `PARTIAL`: only some materialized view partitions were selected for refresh. |
| - `NOT_REFRESH`: no partition needed refresh. In this case, `NeedRefreshPartitions` is usually empty and `Progress` can be `\N`. |
| |
| :::info Version |
| |
| `LastQueryId` is supported since Doris 3.0.0. It is not available in Doris 2.1.x. |
| |
| ::: |
| |
| |
| ## Examples |
| |
| View the latest refresh task of a materialized view. |
| |
| ```sql |
| select * |
| from tasks("type"="mv") |
| where MvDatabaseName = "test" and MvName = "mv1" |
| order by CreateTime desc |
| limit 1\G |
| ``` |
| ```text |
| *************************** 1. row *************************** |
| TaskId: 437156301250803 |
| JobId: 19508 |
| JobName: inner_mtmv_19494 |
| MvId: 19494 |
| MvName: mv1 |
| MvDatabaseId: 16016 |
| MvDatabaseName: test |
| Status: SUCCESS |
| ErrorMsg: |
| CreateTime: 2025-01-07 22:13:48 |
| StartTime: 2025-01-07 22:13:48 |
| FinishTime: 2025-01-07 22:17:45 |
| DurationMs: 236985 |
| TaskContext: {"triggerMode":"MANUAL","partitions":[],"isComplete":false} |
| RefreshMode: COMPLETE |
| NeedRefreshPartitions: ["p_20210101_MAXVALUE","p_20200101_20210101"] |
| CompletedPartitions: ["p_20210101_MAXVALUE","p_20200101_20210101"] |
| Progress: 100.00% (2/2) |
| LastQueryId: 7965b4ddce8a4480-8884e9701679c1c4 |
| ``` |
| |
| In this result: |
| |
| - `TaskId` identifies this refresh execution. It is different for every refresh. |
| - `JobId` and `JobName` identify the MV refresh job. The job can be queried with `select * from jobs("type"="mv") where Name = "inner_mtmv_19494";`. |
| - `MvId`, `MvName`, `MvDatabaseId`, and `MvDatabaseName` identify the materialized view refreshed by the task. |
| - `Status` is `SUCCESS`, so the task finished successfully. If it is `FAILED`, check `ErrorMsg` first. |
| - `CreateTime`, `StartTime`, `FinishTime`, and `DurationMs` show when the task was created, when it started, when it finished, and how long it ran. |
| - `TaskContext` shows this task was manually triggered. `partitions: []` means the user did not specify a partition list in the refresh command. |
| - `RefreshMode` is `COMPLETE`, so this task refreshed all partitions that the materialized view had to refresh. |
| - `NeedRefreshPartitions` lists two partitions that needed refreshing, and `CompletedPartitions` lists the same two partitions, so all required partitions completed. |
| - `Progress` is `100.00% (2/2)`, which also means two of two required partitions completed. |
| - `LastQueryId` is the query ID of the refresh SQL. Use it to search Doris logs when the task fails or runs slowly. |
| |
| :::info Note |
| |
| The number of stored and displayed task records is controlled by the FE configuration item `max_persistence_task_count`. The default value is 100. When the number of task records exceeds this limit, older task records are discarded. If the value is less than 1, task records are not persisted. Restart FE after changing this configuration. |
| |
| ::: |
| |
| View tasks for all insert tasks |
| |
| ```sql |
| select * from tasks("type"="insert"); |
| ``` |
| ```text |
| +----------------+----------------+----------------+-------------------------------+---------+----------+---------------------+---------------------+---------------------+-------------+---------------+------+ |
| | TaskId | JobId | JobName | Label | Status | ErrorMsg | CreateTime | StartTime | FinishTime | TrackingUrl | LoadStatistic | User | |
| +----------------+----------------+----------------+-------------------------------+---------+----------+---------------------+---------------------+---------------------+-------------+---------------+------+ |
| | 79133848479750 | 78533940810334 | insert_tab_job | 78533940810334_79133848479750 | SUCCESS | | 2025-01-17 14:42:54 | 2025-01-17 14:42:54 | 2025-01-17 14:42:54 | | | root | |
| +----------------+----------------+----------------+-------------------------------+---------+----------+---------------------+---------------------+---------------------+-------------+---------------+------+ |
| ``` |
