@Rocky is there any alternatives like sql procedures in starrocks? so we could wrapup some script under 1 name (there should be available temp table creations as well inside it)

StarRocks does not currently support traditional SQL Stored Procedures (like

CREATE PROCEDURE

or

BEGIN...END

blocks). There is no built-in mechanism to "wrap up" multiple logic-heavy SQL statements under a single name inside the database. However, you can achieve similar outcomes using these features: 1. Temporary Tables (v3.3.1+) StarRocks recently introduced Temporary Tables. These are ideal for intermediate ETL steps: * Visibility: Only visible to the current session. * Lifecycle: Automatically dropped when the session ends. * Naming: You can use the same name as a permanent table; the temporary table will take precedence in that session. * Syntax:

sql
    CREATE TEMPORARY TABLE temp_results AS SELECT ...;
    -- or
    CREATE TEMPORARY TABLE my_temp (id INT, val STRING) ENGINE=OLAP ...;

2. User-Defined Variables (v2.5+) You can store query results or scalar values in variables to pass them between sequential SQL statements in a script/session:

sql
SET @max_date = (SELECT MAX(dt) FROM sales);
INSERT INTO summary SELECT * FROM sales WHERE dt = @max_date;

3. Prepared Statements (v3.2+) If you need to "name" a specific query template and reuse it with different parameters:

sql
PREPARE my_script FROM 'INSERT INTO target SELECT * FROM source WHERE id = ?';
EXECUTE my_script USING @some_id;

4. Asynchronous Tasks (

SUBMIT TASK

) For background execution of single ETL statements (like

INSERT INTO ... SELECT

), you can use the

SUBMIT TASK

command. While it doesn't support complex scripting logic, it allows you to run long-running statements asynchronously. Recommendations for "Wrapping Scripts" Since StarRocks lacks internal orchestration logic (IF/ELSE, loops, etc.), the standard practice is to wrap your SQL logic in an external orchestrator: * CLI/Scripts: Put your SQL statements in a

.sql

file and execute them via the

mysql

client or a Shell/Python script. * ETL Tools: Use tools like Apache Airflow, DolphinScheduler, or dbt (StarRocks has a dbt adapter) to manage multi-step dependencies and temporary table creation. Summary of Limitations: * You cannot currently use

SUBMIT TASK

to manage temporary tables (temporary tables are session-bound, while tasks run in their own background context). * Logic like

IF/ELSE

must be handled at the application or orchestration layer above the SQL. References • knowledge/trouble_shooting/trouble_shooting_scripts.md • sql-reference/sql-statements/prepared_statement.md • sql-reference/user_defined_variables.md

@Rocky I need something what support creating temporary tables in the script as well nothing mentioned by you supports that

StarRocks does not currently support SQL Stored Procedures or named scripts (like

CREATE PROCEDURE

) that can execute multi-step logic internally. Because Temporary Tables in StarRocks are strictly session-bound, they only exist as long as your client connection is open, which makes them incompatible with background features like

SUBMIT TASK

. If you need to "wrap up" logic that includes intermediate steps, here are the three standard alternatives used in StarRocks: 1. Common Table Expressions (CTEs) inside a View If your script is designed to transform data, you can wrap the entire logic into a single View or a CTE-based query. This "names" your logic without requiring physical temporary tables. * How it replaces temp tables: Each CTE acts as a named intermediate result. * Example:

sql
    CREATE VIEW my_complex_logic AS
    WITH stage_1 AS (
        SELECT id, sum(val) as total FROM source_table GROUP BY id
    ),
    stage_2 AS (
        SELECT id, total * 1.1 as adjusted_total FROM stage_1 WHERE total > 100
    )
    SELECT * FROM stage_2;

2. dbt (data build tool) - The Recommended Way Since StarRocks lacks procedural SQL, the vast majority of users use dbt with the

dbt-starrocks

adapter. * Under 1 name: You define a "model" (e.g.,

my_script.sql

). * Scripting support: dbt handles the creation of intermediate/temporary tables for you. * Execution: You run

dbt run --select my_script

, and it executes the sequence of SQL commands in a single session. 3. External Orchestration (Airflow / Bash / Python) You can wrap your SQL commands (including

CREATE TEMPORARY TABLE

) into a

.sql

file and execute it via the MySQL client. Because the client maintains one session for the duration of the script, the temporary tables will work. * Example Script (

logic.sql

):

sql
    CREATE TEMPORARY TABLE temp_buff AS SELECT ... ;
    INSERT INTO final_table SELECT * FROM temp_buff;
    -- Temporary table is automatically dropped when this script finishes.

* Execution:

mysql -h <FE_IP> -u user < logic.sql

Summary of why "Internal Scripts" don't work yet: *

SUBMIT TASK

: Only supports a single

INSERT

or

CTAS

statement. It runs in a background context where session-based temporary tables do not exist. * Procedural SQL: StarRocks focuses on high-performance analytical queries and does not yet have a procedural engine for

IF/ELSE

or

BEGIN/END

blocks. If you are looking for a way to schedule these, the combination of dbt + Airflow is the standard architectural pattern for StarRocks. References • table_design/StarRocks_table_design.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md References • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • knowledge/trouble_shooting/trouble_shooting_scripts.md