@Rocky show me how to submit a long running query using Tasks. Explain how to submit, monitor it's status using jobid and retrieve the results.

Hi there! I'm Rocky. Dealing with long-running queries can be a challenge, but StarRocks provides a robust way to handle them asynchronously using Tasks. In StarRocks, the

SUBMIT TASK

statement is used to execute ETL-style queries (like

INSERT

or

CREATE TABLE AS SELECT

) in the background. Here is a guide on how to submit, monitor, and check the results. 1. How to Submit a Task You can wrap your long-running

INSERT

or

CTAS

(Create Table As Select) statement with

SUBMIT TASK

. Example: Submitting an asynchronous INSERT

sql
SUBMIT TASK my_long_job AS 
INSERT INTO target_table 
SELECT * FROM source_table WHERE high_complexity_filter = true;

Pro Tip: For very long queries, you might need to increase the timeout using a hint:

sql
SUBMIT /*+set_var(query_timeout=36000)*/ TASK my_long_job AS 
INSERT INTO target_table 
SELECT * FROM source_table;

2. How to Monitor Status StarRocks tracks these background tasks in the

information_schema

. To monitor the status, you primarily look at the

task_runs

table. * Check all recent runs:

sql
    SELECT * FROM information_schema.task_runs ORDER BY create_time DESC;

* Filter by your specific Task Name:

sql
    SELECT task_name, status, create_time, finish_time, error_message
    FROM information_schema.task_runs
    WHERE task_name = 'my_long_job';

Status definitions: * `PENDING`: Waiting for resources. * `RUNNING`: Currently executing. * `SUCCESS`: Completed successfully. * `FAILED`: Check the

error_message

column for details. 3. How to Retrieve Results Since

SUBMIT TASK

is designed for ETL operations, the "results" of the query are stored in the destination table you specified in your statement (the

target_table

). * Verify data in the target table: Once the status in

task_runs

shows

SUCCESS

, you can simply query your target table:

sql
    SELECT count(*) FROM target_table;

* Checking the Query ID: If you need the specific

query_id

for deep-dive profiling, it is usually recorded in the

task_runs

metadata or you can find it in the FE/BE audit logs associated with that task execution. Note on SELECT queries Currently,

SUBMIT TASK

is intended for statements that persist data (

INSERT

or

CTAS

). If you just want to run a

SELECT

and fetch the result set later, it is recommended to use

INSERT INTO

a temporary result table so you can query it once the task is finished. Feel free to ask if you need help with a specific configuration! 🚀 References • loading/SparkLoad.md • sql-reference/sql-statements/loading_unloading/SHOW_LOAD.md

What is JobID used for? @Rocky

In StarRocks, a JobID is a system-generated unique identifier used to track and manage background or long-running operations. While users often define a Label to name their jobs, the JobID is the internal numeric ID the cluster uses to reference that specific execution or configuration. Here are the primary contexts where

JobID

is used: 1. Data Loading (Broker, Spark, and Insert) When you run a load job (like a Broker Load or an

INSERT INTO ... SELECT

statement), StarRocks assigns a unique

JobId

. * Purpose: It allows you to track the lifecycle of a load (e.g.,

PENDING

,

LOADING

,

FINISHED

, or

CANCELLED

). * Usage: You can find the

JobId

using the

SHOW LOAD

command. It is helpful for troubleshooting failures because the

ErrorMsg

and

ErrorLogUrls

are associated with that specific ID. 2. Routine Load (Kafka/Pulsar) For continuous data ingestion, a Routine Load Job has a persistent ID. * Purpose: It identifies the long-running ingestion job that stays active across cluster restarts. * Usage: When you run

SHOW ROUTINE LOAD

, the

Id

field represents the JobID. Additionally, individual tasks spawned by the routine load (visible via

SHOW ROUTINE LOAD TASK

) contain a

JobId

field to link them back to the parent job configuration. 3. Asynchronous Tasks and Materialized Views StarRocks uses a task framework to handle asynchronous operations, such as refreshing asynchronous Materialized Views (MVs). * Task ID vs. Job ID: In the task framework, a "Task" (or Job) is the definition of the work (e.g., "Refresh MV_A every hour"), and a "Task Run" is a specific execution of that task. * Usage: You can query the

information_schema.tasks

and

information_schema.task_runs

tables. The JobID (or TaskID) here helps you identify which specific MV or scheduled task is being executed. 4. Backup and Restore When performing cluster backups or restores, each operation is assigned a unique ID. * Usage: You can use this ID with

SHOW BACKUP

or

SHOW RESTORE

to monitor the progress of moving data to or from remote storage. Summary: JobID vs. Label It is common to confuse JobID with Label: * Label: A user-specified string (e.g.,

import_2023_10_01

). It is used to ensure idempotency (preventing the same data from being loaded twice). * JobID: A system-generated integer. It is the primary key used by StarRocks internally to manage the job's metadata and state. How to find a JobID? Most often, you will see it in the output of management commands: *

SHOW LOAD;

*

SHOW ROUTINE LOAD;

_

SELECT _ FROM information_schema.task_runs;

References • sql-reference/sql-statements/loading_unloading/routine_load/SHOW_ROUTINE_LOAD_TASK.md • using_starrocks/async_mv/materialized_view_task_run_details.md • loading/RoutineLoad.md • sql-reference/information_schema/routine_load_jobs.md