@Rocky We are trying to take backups of complete database, does it include backup of routine load configuartions (kafka routine loads)

Hi there! I'm Rocky. To answer your question: No, a standard

BACKUP

of a database in StarRocks does not include Routine Load configurations (such as your Kafka routine loads). The

BACKUP

and

RESTORE

commands in StarRocks primarily focus on: * Table Data: The actual data stored in your OLAP tables. * Table Schema: The structure and metadata of the tables. * Views: Logical views defined within the database. Why are Routine Loads not included? Routine Load jobs are considered dynamic loading tasks managed by the Frontend (FE). Since they track their own state (like Kafka offsets), they are not part of the static data snapshot created during a backup. Recommendations for backing up Routine Loads: 1. Save the DDLs: It is a best practice to keep the original

CREATE ROUTINE LOAD

scripts in your version control system or a configuration management tool. 2. Export current configurations: You can manually capture the current configurations by running:

sql
    SHOW ROUTINE LOAD FOR <job_name>;

This will give you the details needed to recreate the job, though you may need to manually re-specify the offsets if you want to resume from a specific point rather than the default. If you are migrating or performing a disaster recovery setup, you will need to re-execute your

CREATE ROUTINE LOAD

statements on the target cluster after the database and tables have been restored.

@Rocky Let's say my db met with a disaster and I want to recover my data. But I don't know till which offset from kafka, my data has come. How to recover from this?

In a disaster recovery scenario where you've lost your Routine Load metadata but still have your data (or a backup of it), here is how you can determine your Kafka offsets and resume ingestion: 1. Check existing metadata (If FE is available) If your StarRocks cluster (specifically the FrontEnd) is still running or has been recovered from a metadata backup, you can simply check the last recorded offsets:

sql
SHOW ROUTINE LOAD FOR <job_name>;

Look for the

Progress

column. It will show the offsets for each Kafka partition that StarRocks has successfully consumed and committed (e.g.,

{"0":"12345", "1":"12346"}

). 2. Check the destination table If the Routine Load job metadata is gone but the table data is intact, you can use the data itself to find the recovery point: * Identify a sequence column: If your data has a timestamp, an auto-incrementing ID, or a specific "offset" column you mapped during ingestion, run a query to find the maximum value:

sql
    SELECT max(your_timestamp_column) FROM your_table;

* Correlate with Kafka: Once you have the last timestamp, you can create a new Routine Load job and tell StarRocks to start consuming from that specific time:

sql
    CREATE ROUTINE LOAD your_db.your_job ON your_table
    PROPERTIES (
        "kafka_partitions" = "0,1,2",
        "kafka_default_offsets" = "OFFSET_TIMESTAMP:1672531200000" -- Example timestamp in ms
    )
    FROM KAFKA (
        "kafka_broker_list" = "broker1:9092",
        "kafka_topic" = "your_topic"
    );

3. Use Kafka Consumer Group tools If you configured your Routine Load with a specific

group.id

in the

property.group.id

property, the offsets might be stored in Kafka's internal

__consumer_offsets

topic. You can use Kafka's command-line tools to check the lag and the last committed offset for that group:

bash
kafka-consumer-groups --bootstrap-server <broker> --describe --group <your_group_id>

4. Restarting from a safe point If you cannot determine the exact offset and want to avoid data loss at the cost of potential duplicates (which can be handled by a Primary Key table in StarRocks), you can: * Start from the beginning: Use

OFFSET_BEGINNING

. * Start from a specific time: Use

OFFSET_TIMESTAMP

. Important Tip: To make recovery easier in the future, it is a best practice to include the Kafka partition and offset information as columns in your StarRocks table. You can do this in your Routine Load statement:

sql
COLUMNS (col1, col2, source_partition=temp_partition, source_offset=temp_offset)

This way, the offset is stored alongside your data, and you can always find exactly where you left off by running

SELECT max(source_offset) FROM table

.

@Rocky Lets say the db met with disaster, is it possible that the routine load keeps consuming the data from kafka and the data would get lost?

Hi there! I'm Rocky. That's a great question about the resilience of Routine Load. The short answer is no, you don't need to worry about data loss in that scenario. Routine Load is designed with transactional integrity to prevent exactly what you're describing. Here is how StarRocks ensures data safety during a failure or "disaster": 1. Transactional Commits Routine Load works in units of "tasks." Each task consumes a batch of messages from Kafka and attempts to load them into StarRocks. This process is wrapped in a transaction. • Data is only considered "loaded" once the transaction is successfully committed in StarRocks. • If the database (BE or FE) fails during the process, the transaction will fail/abort, and no data will be written to the storage. 2. Internal Offset Management Crucially, StarRocks manages Kafka offsets itself within its own metadata (stored in the Frontend/FE). • It does not rely on Kafka's internal

__consumer_offsets

to track progress. • The offset for a partition is only updated in StarRocks' metadata after the transaction has successfully committed. 3. Behavior During Disaster * If a Backend (BE) fails: The specific loading task running on that BE will fail. The Frontend (FE) will notice the failure, abort the transaction, and eventually reschedule the task (starting from the last successfully committed offset) on a healthy BE. * If the Frontend (FE) fails: The Routine Load job will stop because the FE is responsible for scheduling tasks. Once the FE is back online (or a new leader is elected), it will resume the job from the last offset stored in its metadata. * Data remains in Kafka: Since StarRocks hasn't "advanced" the offset in its own records, it will simply ask Kafka for the same data again once the system is recovered. 4. The

PAUSED

State If the disaster is such that the Routine Load job fails repeatedly (e.g., due to schema changes or persistent connectivity issues), StarRocks will move the job into a

PAUSED

state. Even in this state, the offsets are preserved. You can fix the underlying issue and resume the job using

RESUME ROUTINE LOAD

, and it will pick up exactly where it left off. In summary: Because StarRocks only advances its record of the Kafka offset after a successful commit, a system crash or disaster simply results in the system re-processing the uncommitted data once it recovers, ensuring at-least-once delivery semantics (and exactly-once in most standard configurations).

@Rocky Lets say I take a backup of of my db at 3 pm, and my db crashes at 4 pm. From 3 pm to 4 pm, everything was running smoothly including routine load and starrocks. I can take backup of db from what it was like at 3pm. But how do I know, till which kafka offset was data dumped when the backup was taken at 3 pm? Is there a way?

That's a great question! When you use StarRocks' BACKUP and RESTORE utility, it includes not just the data in your tables, but also the metadata associated with those tables and the database—including your Routine Load job definitions and their committed offsets. Here is how you can find and handle the Kafka offsets after a restore: 1. Routine Loads are Restored as Part of the Metadata When you restore the database from your 3 PM backup, the Routine Load jobs that existed at that time will be recreated in the restored cluster. Crucially, StarRocks stores the last successfully committed Kafka offsets in its own metadata (FrontEnd). Because the backup captures the state of the system at 3 PM, the restored Routine Load job will "remember" exactly where it was at 3 PM. 2. How to check the offsets after Restore Once you have performed the

RESTORE

, the Routine Load jobs are typically in a

PAUSED

state. You can check the offsets by running:

sql
SHOW ROUTINE LOAD FROM your_db_name WHERE name = "your_job_name"\G

In the output, look for the following fields: *

DataSourceProperties

: This field contains a JSON string showing the partition offsets that were committed at the time the backup was taken (e.g.,

{"0":"12345", "1":"67890"}

). *

State

: It will likely be

PAUSED

. 3. Resuming the Load Since the database crashed at 4 PM, but you restored the 3 PM state, StarRocks knows it only has data up to the 3 PM offsets. To resume: 1. Verify the offsets using the command above. 2. Resume the job:

sql
    RESUME ROUTINE LOAD FOR your_job_name;

StarRocks will then connect to Kafka, start from those 3 PM offsets, and "re-consume" the data that was produced between 3 PM and 4 PM, ensuring no data is missing in your restored environment. Summary You don't need to manually track the offsets; StarRocks does it for you. The backup is a consistent snapshot of both your data and the ingestion state (offsets) at that specific moment. When you restore the 3 PM backup, the "clock" for your Routine Load is effectively set back to 3 PM as well.

@Rocky Can we store routine load metadata?

Hello! I'm Rocky. Regarding your question about storing Routine Load metadata, here is how StarRocks handles it: 1. Internal Storage StarRocks automatically manages and stores Routine Load metadata (such as job configurations, current state, and Kafka offsets) within the Frontend (FE) metadata. • It is stored in the FE's memory and persisted in the FE's edit logs and image files (using BDBJE). • It is not stored in a standard user-facing table or an external database. 2. How to Access Metadata You can view this metadata using the following SQL commands: * Show all jobs:

SHOW ROUTINE LOAD;

* Show a specific job:

SHOW ROUTINE LOAD FOR <job_name>;

* Show job tasks:

SHOW ROUTINE LOAD TASK WHERE JobName = "<job_name>";

(This shows current active tasks and their progress). 3. Retention Period Metadata for jobs that are

FINISHED

or

CANCELLED

is not kept forever. It is automatically cleaned up based on the FE configuration parameter

label_keep_max_second

(default is usually 3 days). 4. Progress Management (Offsets) For Kafka Routine Loads, StarRocks manages the consumer offsets internally. It does not commit offsets back to Kafka's

__consumer_offsets

topic. This ensures exactly-once semantics by committing the data and the offsets together in a single transaction within StarRocks. Can you store it yourself? If you want to keep a long-term history of routine load performance or status: * You would need to write a script (e.g., in Python or Go) that periodically runs

SHOW ROUTINE LOAD

and saves the results into a StarRocks table or an external logging system. * The

information_schema

in StarRocks does not currently have a dedicated table for Routine Load jobs, so the

SHOW

command is the primary source. If you have a specific use case in mind for this metadata, feel free to share!