http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> im using starRocks version 4.1.1 an...
# questions-and-troubleshooting
e
@Rocky im using starRocks version 4.1.1 and I was wondering the following: my use case is Im using redpanda connect in order to ingest messages from a kafka topic into starRocks. when putting things into starRocks, I got a requirement of updating 2 tables per message, this must still be in the same transaction either everything happens or nothing. does starRocks support this ?
r
Yes, StarRocks supports multi-table transactions, and in version 4.1.1, you have two primary ways to achieve atomicity across multiple tables for your Redpanda Connect use case. 1. Multi-Table Stream Load Transaction (Recommended) From version 4.0 onwards, StarRocks' Stream Load transaction interface explicitly supports loading data into multiple tables within the same database in a single atomic transaction. * How it works: This interface uses a "Begin -> Load -> Commit" sequence. You can call the 
/api/transaction/load
endpoint multiple times for different tables using the same transaction ID. * Key Requirement: When using this for multiple tables, you must include the HTTP header 
-H "transaction_type:multi"
. * Redpanda Connect Implementation: Redpanda Connect's 
starrocks
output uses the Stream Load API. To use the multi-table feature, you can typically add custom headers in your YAML configuration:
Copy code
yaml
    output:
      starrocks:
        # ... other configs ...
        headers:
          transaction_type: multi
        # Note: Standard Redpanda Connect outputs are often mapped to one table.
        # To update two, you may need a 'broker' with two 'starrocks' outputs,
        # but ensuring they share the same transaction requires a client-side
        # implementation that supports StarRocks' 2PC transaction interface.
Note: Check if your specific version of the Redpanda Connect StarRocks connector supports managing a single transaction ID across multiple output blocks. If it does not, you may need to use the SQL approach below. 2. SQL Transactions (ACID) Since version 3.5, StarRocks supports standard SQL Transactions over its MySQL-compatible frontend (FE). This is often the easiest way to ensure "everything or nothing" logic if your throughput requirements allow for JDBC/SQL-based ingestion. * Syntax:
Copy code
sql
    BEGIN;
    INSERT INTO table1 (...) VALUES (...);
    INSERT INTO table2 (...) VALUES (...);
    COMMIT;
* Redpanda Connect Implementation: You can use the 
sql_raw
or 
sql_insert
output in Redpanda Connect. Because StarRocks is MySQL-compatible, you can wrap your updates in a transaction. * Shared-Data vs. Shared-Nothing: If you are on a Shared-Data cluster (StarRocks 4.0+), 
UPDATE
and 
DELETE
are also supported within these transactions. Summary of Support Feature | Supported Since | Best For Stream Load (Multi-table) | v4.0 | High-performance, high-concurrency streaming. SQL Transactions | v3.5 | Complex logic, standard SQL tools, lower throughput. Important Note for Redpanda Connect Users: Standard Redpanda Connect output plugins are usually "stateless" per batch. To guarantee that a single Kafka message translates to updates in two tables within the same StarRocks transaction, you must ensure your client maintains the transaction session. If the high-level 
starrocks
output doesn't expose a multi-table session, a common pattern is to ingest the message into a single "staging" table or Primary Key table and use a Synchronous Materialized View or a StarRocks task to propagate the data, though the SQL transaction approach is more direct for strict atomicity. References • loading/Stream_Load_transaction_interface.md • loading/Loading_intro.md
e
@Rocky can this Multi-Table Stream Load Transaction handle high loads ? meaning that when ingest messages from kafka continuously - these messages are security logs so we need them to be proccessed super fast so we can query them and see them in the ui
r
Yes, StarRocks version 4.1.1 can definitely handle high-load security log ingestion while maintaining the atomicity you need across two tables. The Multi-Table Stream Load Transaction is actually specifically designed for this. Instead of a performance penalty, it is often more efficient than separate loads because it reduces the number of transaction commits and data versions created. How it handles High Load & Low Latency For security logs, where "super fast" processing and high volume are critical, here is how StarRocks manages the load: 1. Batching is Key: To maintain high throughput and low query latency, you should not commit one transaction per individual log message. Instead, use Redpanda Connect's batching capability to group messages (e.g., every 1–5 seconds or every 5,000 rows). * Why? Every commit in StarRocks creates a new "version" of the data. Committing too frequently (e.g., 100 times per second) can lead to a "too many versions" error and slow down queries as the system struggles to compact files. 2. Atomic Multi-Table Write: Within one transaction, you call the 
/api/transaction/load
API for 
Table A
, then for 
Table B
, and finally call 
commit
. StarRocks ensures that either both tables are updated or neither is. 3. Merge Commit (Internal Optimization): Since you are on version 4.1.1, StarRocks has a feature called Merge Commit. Even if your client sends many small concurrent requests, StarRocks can internally merge them into a single larger transaction to protect the system's performance and query speed. Recommended Configuration for Redpanda Connect When using Redpanda Connect with the StarRocks transaction interface for high loads: * Headers: Ensure you include 
-H "transaction_type:multi"
in your load requests. * Table Type: For security logs that you need to query "super fast," use the Duplicate Key table model (if logs are append-only) or the Primary Key table model (if you need to update/deduplicate logs). The Primary Key model is highly optimized for real-time updates and sub-second query response. * Primary Key Persistence: If your log volume is massive, enable the 
persistent_index
on your Primary Key tables (requires SSD) to keep memory usage low while maintaining fast ingestion. Potential Constraint to Watch The current documentation notes that the Stream Load transaction interface supports concurrent data writes from one client at a time for a single transaction. This means your Redpanda Connect instance should manage the lifecycle of one transaction (Begin -> multiple Loads -> Commit) sequentially or within its own batching logic. It cannot "share" a single transaction ID across multiple independent Redpanda Connect nodes simultaneously. Summary * Atomicity: Supported (v4.0+). * High Load: Yes, especially when batching messages at the client level. * Latency: Very low; data is visible immediately after the 
commit
call. For your use case, the combination of Redpanda Connect batching + StarRocks Multi-Table Transactions is the standard architectural pattern for high-speed security analytics.volume for high-performance, atomic ingestion. References • loading/Stream_Load_transaction_interface.md • loading/loading_introduction/troubleshooting_loading.md
e
@Rocky if starRocks supports multi table transactions then why even have postgres if our k8s deployment already has starRocks ?
r
While StarRocks version 4.1.1 is incredibly powerful and has added features traditionally found in databases like Postgres (such as multi-table transactions and row-storage for point lookups), they serve different primary purposes. Here is why most teams still keep Postgres for operational workloads even if they have StarRocks in the same environment: 1. Strict Constraints and Data Integrity * Foreign Keys: StarRocks does not enforce foreign key constraints. In Postgres, you can ensure that a "LogID" in Table A must exist in "LogMetadata" in Table B. In StarRocks, you have to manage this logic in your application layer. * Unique Constraints: Beyond the Primary Key, Postgres allows you to set multiple unique constraints across different columns. StarRocks is optimized for speed and generally only enforces the Primary Key. 2. Transaction Isolation Levels * StarRocks: Only supports READ COMMITTED. This means if two people update the same row at the exact same time, there is no "lock" or "serialization" to prevent a race condition. * Postgres: Supports SERIALIZABLE and REPEATABLE READ. If you are building a system where "double-spending" or precise inventory counts matter, Postgres is the safer choice because it can prevent concurrent transactions from interfering with each other. 3. Transaction Granularity (TPS vs. Throughput) * Postgres is built for OLTP (Online Transactional Processing). It is designed to handle thousands of tiny, independent transactions per second (e.g., 1,000 different users each updating their own profile). * StarRocks is built for OLAP (Online Analytical Processing). Even with multi-table transactions, it prefers batching. If you try to send 5,000 individual 
BEGIN; INSERT...; COMMIT;
blocks per second for 5,000 different messages, StarRocks will struggle with "version explosion." Postgres handles that specific "chatty" workload much better. 4. Ecosystem and Tooling * Most application frameworks (Django, Spring Boot, Ruby on Rails) are built with Postgres in mind. They rely on things like Indices on JSONB, PostGIS for complex geography, and Triggers/Stored Procedures which are more mature and feature-rich in Postgres. The "Better Together" Architecture For your security log use case, a common architecture is: 1. Postgres: Store your "Source of Truth" application data (User accounts, permissions, configuration settings). 2. StarRocks: Ingest the massive stream of security logs (millions/billions of rows). When can you ditch Postgres? If your "application" is purely a dashboard or a simple UI that just displays logs and doesn't require complex relational integrity or high-frequency tiny updates, you can use StarRocks as your only database. Many users do exactly this for "Log Analytics" platforms to simplify their stack. Summary Comparison: Feature | Postgres | StarRocks (v4.1.1) Primary Use | App State / Transactions | Analytics / High-speed Ingestion Foreign Keys | Enforced | Not Enforced Concurrency | Thousands of tiny transactions | High-speed batch ingestion Query Speed | Fast for single rows | Blazing fast for millions of rows Isolation | Serializable (Strict) | Read Committed (Loose) References • loading/SQL_transaction.md • introduction/what_is_starrocks.md References • loading/SQL_transaction.md • introduction/what_is_starrocks.md • knowledge/usage/best-practice.md • data_source/feature-support-data-lake-analytics.md
It is a great question. While StarRocks has introduced multi-table transactions (v3.5+) and ACID properties, it is fundamentally an OLAP (Analytical) engine, whereas Postgres is an OLTP (Transactional) engine. Even though your k8s deployment has StarRocks, here are the critical reasons why you would likely still want Postgres for your application's "source of truth." 1. Enforcement vs. Hints (Data Integrity) * Postgres: Enforces constraints strictly. If you try to insert a record with a non-existent Foreign Key or a duplicate Unique Key, Postgres will reject the write. * StarRocks: Generally treats Foreign Keys and Unique constraints as optimizer hints (except for the Primary Key itself). It does not "check" the data during ingestion to ensure it meets these rules. If your application logic relies on the database to prevent "bad data" from entering, StarRocks won't do that for you. 2. Transaction Isolation & Conflict Handling * Postgres: Supports Serializable and Repeatable Read isolation. If two users try to update the exact same row at the same time, Postgres uses row-level locking or serialization to ensure the result is deterministic. * StarRocks: Only supports a limited READ COMMITTED isolation. Crucially, as of version 4.1.1, it does not support write-conflict checks. If two transactions write to the same table simultaneously, both will succeed, and the final state simply depends on which one finished its 
COMMIT
last. This makes it unsuitable for things like bank balances or inventory management. 3. "Read-Your-Own-Writes" * Postgres: Within a single transaction, you can 
INSERT
a row and then immediately 
SELECT
it or 
UPDATE
it. * StarRocks: Does not allow you to read data that was changed within the same uncommitted transaction. A 
SELECT
statement in a StarRocks transaction only sees data that was committed before the transaction started. 4. Throughput (TPS) vs. Analytical Power * Postgres: Optimized for High TPS (Transactions Per Second). It can handle thousands of tiny, different transactions from thousands of different users simultaneously. * StarRocks: Optimized for High Throughput. It wants you to batch your logs. If you try to treat it like Postgres and send 2,000 individual 
BEGIN...COMMIT
blocks per second for single rows, the system will eventually slow down due to "version explosion" (too many small data files). 5. Functional Breadth Postgres has a 30-year head start on features that StarRocks (rightfully) doesn't prioritize: * Triggers: Automatic actions when data changes. * Complex JSONB Indexing: Advanced indexing for document-style data. * Extensions: Like PostGIS for heavy-duty geospatial work. The Verdict: Can you ditch Postgres? * YES, if: Your app is basically a "Read-Only" dashboard or a log-viewer where the "source of truth" is the Kafka stream itself, and you just need StarRocks for fast querying and simple atomic updates. * NO, if: You have a "User" or "State" system where you need to guarantee that no two users can claim the same username, or where you need strict relational integrity that the database must defend. Standard K8s Pattern: Use Postgres for your configuration, user accounts, and small "active" metadata, and use StarRocks for the massive firehose of security logs and analytical queries. References • loading/SQL_transaction.md • introduction/what_is_starrocks.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • loading/SQL_transaction.md • sql-reference/System_variable.md
e
@Rocky in the Multi-Table Stream Load Transaction when doing load data for table A and then load data for table B, we can give each table a batch of rows or do we need to say load per row ?
r
You can (and should) give each table a batch of rows. StarRocks is designed for high-throughput batch ingestion, and loading "per row" would be extremely inefficient. In a Multi-Table Stream Load Transaction, the process works like this: 1. Begin Transaction: You start the transaction once. 2. Load Data (Batch 1 for Table A): You call the 
/api/transaction/load
API and pass a file or a stream containing thousands or millions of rows for Table A. 3. Load Data (Batch 2 for Table B): You call the same API again, specifying Table B, and pass another batch of rows. 4. Commit: You commit the transaction. Key Technical Details * Batch Size: Each individual 
load
call can handle files up to 10 GB in size. For your security logs, you should ideally batch messages in your client (like Redpanda Connect or a custom script) and send them every few seconds or every few thousand rows. * Data Formats: You can send these batches in CSV or JSON format. * Multiple Calls per Table: You can even call 
/api/transaction/load
multiple times for the same table within one transaction if your data is spread across multiple files. * Efficiency: Because StarRocks is a columnar database, it performs best when it can write data in large "chunks" (segments). Sending 5,000 rows in one API call is much faster and uses fewer resources than making 5,000 separate API calls for one row each. Example Workflow for Security Logs If you have 
log_events
and 
log_metadata
tables: 1. 
POST /api/transaction/begin
(returns a transaction ID/label). 2. 
PUT /api/transaction/load
for 
table: log_events
with a JSON body containing 5,000 log entries. 3. 
PUT /api/transaction/load
for 
table: log_metadata
with a JSON body containing related metadata. 4. 
POST /api/transaction/commit
. All rows across both tables become visible to your UI and queries at the exact same moment once the 
commit
call succeeds. References • loading/Stream_Load_transaction_interface.md • loading/StreamLoad.md • loading/loading_introduction/loading_introduction.md References • loading/Stream_Load_transaction_interface.md
e
@Rocky when using the Stream Load api or the Multi-Table Stream Load Transaction lets say we give it a json the has 5 fields and the sixth filed is a nested json (that is suppose to be mapped to the starRocks nested struct column). now our starRocks table is defined with column names in lower case and it has has a column which is a nested struct (each filed name in the struct is also defined in lower case) does it matter if for the top level fields or the nested json fields I got camelCase or lowercase when I give it to the stream Load api or the Multi-Table Stream Load Transaction , like do they know to map it to the lower case column ? in addition does it know to map the nested json field to the nested struct column ? im using starRocks version 4.1.1 and is it the same behavior whether Im in shared-data or no shared-data mode ?
r
For your StarRocks v4.1.1 deployment, here are the specifics on how it handles JSON case sensitivity and nested structures during Stream Load (including multi-table transactions). 1. Does case matter for column mapping? Yes, it matters. When using "Simple Mode" (where you don't provide 
jsonpaths
), StarRocks matches JSON keys to table columns case-sensitively. * Scenario: Table column is 
user_id
(lowercase). JSON key is 
userId
(camelCase). * Result: StarRocks will not automatically map 
userId
to 
user_id
. The column will end up as 
NULL
. * Solution: You must use Matched Mode. In your load request header, specify `jsonpaths`:
Copy code
bash
    -H "jsonpaths: [\"$.userId\", \"$.otherField\"]"
    -H "columns: user_id, other_field"
This tells StarRocks exactly which JSON path maps to which table column, regardless of the key's casing. 2. Mapping nested JSON to a 
STRUCT
column StarRocks does support mapping a nested JSON object directly to a 
STRUCT
column. * Requirement: The JSON field must be a valid JSON object, and the table column must be defined as a 
STRUCT
. * Field Matching inside the Struct: Just like top-level columns, the field names inside the nested JSON object must match the field names defined in your 
STRUCT
definition exactly (case-sensitively). * Example: * Table: 
log_data STRUCT<event_type:STRING, severity:INT>
* JSON: 
"log_data": {"event_type": "login", "severity": 1}
— Works. * JSON: 
"log_data": {"eventType": "login", "Severity": 1}
— Fails/Returns NULLs for those sub-fields because the case doesn't match the DDL. 3. Shared-data vs. Shared-nothing The behavior is identical in both modes. The JSON parsing and column mapping logic happen in the compute layer (the BE or CN nodes), which uses the same engine code regardless of whether your storage is local disks (shared-nothing) or S3/GCS (shared-data). Summary Table for v4.1.1 Feature | Case Sensitive? | Behavior Top-level JSON Keys | Yes | Must match table column names exactly or use 
jsonpaths
. Nested Struct Fields | Yes | Keys inside the JSON object must match 
STRUCT
field names exactly. Simple Mode | Yes | Auto-mapping only works if cases match exactly. Matched Mode | No | You can manually map any JSON path (case-sensitive path) to any column. Pro Tip: If your JSON schema is highly unpredictable or has inconsistent casing that you can't control, consider using the 
VARIANT
 type (available for Iceberg in v4.1 and coming to native tables). It stores the JSON as-is in an optimized binary format, allowing you to query it using 
column:"path"
syntax without pre-defining the internal structure or worrying about case-matching during the load. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md • sql-reference/data-types/semi-structured/STRUCT.md • release_notes/release-4.1.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/StreamLoad.md
e
@Rocky is it also possible to say based of some column value into which table to insert the row we read from s3 in the Files() approach
r
With the 
FILES()
approach (specifically using 
INSERT INTO ... SELECT FROM FILES()
), you cannot route rows to different tables within a single SQL statement. However, you can achieve this using one of the following two methods: 1. Multiple 
INSERT INTO
Statements with 
WHERE
 You can run separate 
INSERT
statements for each target table, using a 
WHERE
clause to filter the data from the S3 file.
Copy code
sql
-- Load rows where type is 'security' into table_a
INSERT INTO table_a 
SELECT * FROM FILES(...) 
WHERE log_type = 'security';

-- Load rows where type is 'system' into table_b
INSERT INTO table_b 
SELECT * FROM FILES(...) 
WHERE log_type = 'system';
* Pros: Very easy to write and uses standard SQL. * Cons: StarRocks will re-scan the S3 file for every statement. If your files are very large (e.g., 5GB+), this doubles or triples your S3 data transfer costs and processing time. 2. Multi-Table Broker Load If you want to handle this in a single "job" (which is more efficient for the cluster to manage), you can use a Broker Load. You can define multiple 
DATA INFILE
blocks pointing to the same S3 path but targeting different tables.
Copy code
sql
LOAD LABEL my_log_load
(
    DATA INFILE("<s3://my-bucket/logs/*>")
    INTO TABLE table_a
    WHERE log_type = 'security'
    ,
    DATA INFILE("<s3://my-bucket/logs/*>")
    INTO TABLE table_b
    WHERE log_type = 'system'
)
WITH BROKER (...)
PROPERTIES ("timeout" = "3600");
* Pros: One job tracks everything. * Cons: Like the 
INSERT
method, it typically still results in multiple passes over the data internally. 3. Comparison with the Multi-Table Stream Load (Transaction API) The Multi-Table Stream Load Transaction API (which you asked about earlier) is actually more efficient for this specific use case if you have a client/script reading the data. * In the Transaction API, your client reads the S3 file once, splits the rows in memory, and sends one batch to Table A and one batch to Table B. * The data is only transferred from S3 to your client once, and only sent to StarRocks once. Summary * If you want pure SQL: Use separate 
INSERT INTO ... SELECT FROM FILES() WHERE ...
calls. * If you want performance/lowest cost: Use the Multi-Table Stream Load Transaction API and perform the "routing" logic in your client (e.g., a Python script, Go app, or Redpanda Connect). References • sql-reference/sql-functions/table-functions/files.md • loading/BrokerLoad.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/InsertInto.md References • loading/s3.md • loading/BrokerLoad.md
e
@Rocky to goal is to consume from s3 into different tables
r
To consume data from S3 and route it to different tables based on column values in StarRocks v4.1.1, you have three main architectural options. 1. The Single-Job Approach: Broker Load (Recommended for "Pulling") Broker Load is the only built-in method that allows you to define a single load job targeting multiple tables from the same S3 source. You can use the 
WHERE
clause in each 
DATA INFILE
block to filter/route rows. Example:
Copy code
sql
LOAD LABEL my_s3_routing_load
(
    -- Route rows where 'type' is 'A' to table_a
    DATA INFILE("<s3a://my-bucket/input/data.parquet>")
    INTO TABLE table_a
    WHERE type = 'A'
    ,
    -- Route rows where 'type' is 'B' to table_b
    DATA INFILE("<s3a://my-bucket/input/data.parquet>")
    INTO TABLE table_b
    WHERE type = 'B'
)
WITH BROKER ("aws.s3.region" = "us-east-1", ...)
PROPERTIES ("timeout" = "3600");
* Pros: Single command to manage; can handle massive datasets. * Cons: Internally, StarRocks may still scan the file multiple times (one for each 
DATA INFILE
block). 2. The Modern SQL Approach: 
INSERT INTO SELECT FROM FILES()
 If you prefer a pure SQL interface, you can run multiple 
INSERT
statements.
Copy code
sql
-- Load Table A
INSERT INTO table_a 
SELECT * FROM FILES("path" = "s3://...", ...) 
WHERE type = 'A';

-- Load Table B
INSERT INTO table_b 
SELECT * FROM FILES("path" = "s3://...", ...) 
WHERE type = 'B';
* Behavior: This is processed as two independent transactions. Each statement will perform its own scan of the S3 files. * For Continuous Loading: You would create two separate Pipes (one for each table) that both monitor the same S3 path but have different 
WHERE
clauses in their 
SELECT
statements. 3. The High-Efficiency Approach: Multi-Table Stream Load If you have a client (e.g., a Python script or a Java app) that reads the S3 file, you can use the Multi-Table Stream Load (Transaction API). 1. Client-Side Routing: Your script reads the S3 file once. 2. Streaming: As it reads, it pushes rows to StarRocks using the Transaction API. 3. Efficiency: The data is read from S3 exactly once and transferred to StarRocks exactly once. Summary Comparison Feature | Broker Load | 
FILES()
/ Pipe | Multi-Table Stream Load Trigger | Pull (SR pulls from S3) | Pull (SR pulls from S3) | Push (Client sends to SR) Multi-Table? | Yes, in one job | No, separate jobs/pipes | Yes, in one transaction Routing Logic | 
WHERE
clause | 
WHERE
clause | Client-side code S3 Scan Count | Multiple (usually) | Multiple | Single Recommendation: * If you want the simplest setup using StarRocks' internal scheduler, use Broker Load with 
WHERE
clauses. * If you need continuous loading of new files as they arrive, use one Pipe per target table. * If you want the absolute best performance and lowest S3 costs, use a script to read S3 and push via Multi-Table Stream Load. References • loading/BrokerLoad.md • loading/s3.md • sql-reference/sql-functions/table-functions/files.md • loading/StreamLoad.md Stream Load**. References • loading/BrokerLoad.md • loading/s3.md
2 Views
Open in Slack
PreviousNext
Powered by