@Rocky in a json stream load if the target table has some additional fields that are auto populated like

raw_import_timestamp *datetime*

NOT

NULL

DEFAULT

CURRENT_TIMESTAMP

which the json will not have, do I then need to map the columns?

The behavior for missing fields in a JSON Stream Load depends on whether you use Simple Mode (auto-mapping) or Matched Mode (explicit mapping). For a column with

DEFAULT CURRENT_TIMESTAMP

, you will likely need to map the columns to ensure it is populated correctly. 1. Simple Mode (No Mapping) If you do not specify

jsonpaths

or

COLUMNS

, StarRocks uses "Simple Mode," where it attempts to match every column in your table to a key in the JSON object by name. * Result: If

raw_import_timestamp

is missing from the JSON, StarRocks treats it as a missing field. For

NOT NULL

columns, this often results in the load failing (in

strict_mode

) or being populated with the default value for the data type (e.g.,

0001-01-01 00:00:00

for

DATETIME

) rather than the table's

DEFAULT CURRENT_TIMESTAMP

expression. * Verdict: You should not rely on Simple Mode if you want the table-level

DEFAULT

function to trigger for missing JSON keys. 2. Matched Mode (Explicit Mapping) To ensure the timestamp is populated with the current time during the load, you should use the

COLUMNS

header to explicitly define the mapping. You can use the

now()

function directly in the load command: Example Command:

bash
curl --location-trusted -u user:passwd \
    -H "format: json" \
    -H "columns: col1, col2, raw_import_timestamp = now()" \
    -T data.json \
    <http://FE_HOST:HTTP_PORT/api/db/table/_stream_load>

* In this example,

col1

and

col2

are matched to keys in your JSON. *

raw_import_timestamp

is explicitly set to the result of

now()

for every row. Summary While table-level

DEFAULT

constants (like

DEFAULT "10"

) sometimes work for missing fields, dynamic defaults like

CURRENT_TIMESTAMP

often require explicit mapping in the Stream Load

COLUMNS

parameter to function as expected. Recommended approach: If your table has extra metadata fields not present in the source JSON, specify the names of the fields that are in the JSON in the

columns

header, and then append the auto-populated field with a function:

-H "columns: json_key1, json_key2, ..., raw_import_timestamp=now()"

References • loading/StreamLoad.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md

@Rocky ok now i have nice NDJSON, how can i feed that into stream load? or do i have to wrap it in an array

For Stream Load, StarRocks expects the JSON payload to be a single valid JSON document. This means you have two choices for handling multiple records: 1. The Recommended Approach: JSON Array If you have multiple records, the standard way to feed them into Stream Load is to wrap them in a JSON array

[...]

and use the

strip_outer_array: true

header. * Data format:

[{"id":1, "name":"a"}, {"id":2, "name":"b"}]

* Header:

-H "strip_outer_array: true"

* Why: Without this header, StarRocks will try to parse the entire array as a single row (which will likely fail if your table schema doesn't have an ARRAY column). 2. Can you use NDJSON as-is? Currently, the standard Stream Load (

/api/db/table/_stream_load

) does not natively support NDJSON (one JSON object per line without an outer array) as a separate format mode. If you try to load a file like this:

json
{"id": 1, "name": "a"}
{"id": 2, "name": "b"}

The JSON parser will typically error out after the first object because it sees trailing data or multiple roots. To use your NDJSON file, you have three main options: 1. Wrap it (Client-side): Use a tool like

jq

or a simple script to wrap the lines in

[]

and add commas.

bash
    # Example using jq to wrap NDJSON into an array for stream load
    cat your_data.ndjson | jq -s . | curl ... -H "format: json" -H "strip_outer_array: true" ...

2. Routine Load: If your data is coming from Kafka, StarRocks Routine Load handles NDJSON naturally because each Kafka message is treated as a single JSON object. 3. INSERT INTO ... FILES(): If your NDJSON files are on S3, GCS, or a local path accessible by the BEs, you can use the

FILES()

table function which is often more flexible with formats:

sql
    INSERT INTO target_table
    SELECT * FROM FILES(
        "path" = "<s3://bucket/data.ndjson>",
        "format" = "json"
    );

Summary For a Stream Load specifically, you do need to wrap your NDJSON in an array and set

-H "strip_outer_array: true"

to process it as multiple rows. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md References • sql-reference/sql-functions/table-functions/files.md

@Rocky i have nifi configured to stream loads, is it an antipattern to send multiple at once?

Sending multiple concurrent Stream Loads from NiFi is a common setup, but it can become an anti-pattern if the loads are too small and too frequent. The Problem: "Too Many Versions" In StarRocks, every successful Stream Load creates a new data version (a small file/delta). If NiFi sends hundreds of tiny loads per second: 1. Query Performance Drops: The storage engine has to merge many small versions during read time. 2. Compaction Pressure: The system will struggle to "compact" these tiny files into larger ones fast enough, eventually leading to

too many versions

errors that block further loads. 3. FE Overhead: The Frontend (FE) has to manage the transaction overhead for every single request. ────────── Best Practices for NiFi & Stream Load 1. Batching in NiFi (The "Golden Rule") Do not send FlowFiles one-by-one as they arrive. Instead, use NiFi’s

MergeContent

or

MergeRecord

processors immediately before your

InvokeHTTP

(Stream Load) processor. * Target Size: Aim for 100MB to 1GB per load. * Target Interval: If your data volume is low, batch by time (e.g., every 5–10 seconds) to keep latency low without spamming the database. 2. Use "Merge Commit" (StarRocks v3.4+) If you cannot easily batch on the NiFi side, StarRocks introduced a feature specifically for this called Merge Commit. It allows the database to collect multiple small, concurrent Stream Loads and commit them as a single transaction. * How to use: Add the header

-H "enable_merge_commit: true"

to your NiFi

InvokeHTTP

request. * Benefit: This significantly reduces versioning overhead even if NiFi sends many small requests. 3. Parallelism vs. Concurrency * Parallelism is good: Using a few concurrent threads (e.g., 3–5) to load different batches can improve throughput. * Excessive Concurrency is bad: Sending 50+ concurrent small loads to the same table without Merge Commit will likely trigger performance bottlenecks. Summary It is not an anti-pattern to send multiple loads at once, provided they are right-sized (batched). If you are sending thousands of tiny JSON objects as individual HTTP calls, you should refactor the NiFi flow to merge them first or enable the Merge Commit feature in StarRocks. References • loading/StreamLoad.md • faq/loading/Stream_load_faq.md • administration/management/FE_configuration.md References • loading/Kafka-connector-starrocks.md • loading/Spark-connector-starrocks.md • loading/StreamLoad.md • loading/Flink-connector-starrocks.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md

@Rocky should i add the merge commit even if i'm batching in nifi? ie - can it hurt?

If you are already batching in NiFi to sizes like 100MB to 1GB, adding

enable_merge_commit: true

is generally unnecessary and can actually be counterproductive. Here is why it might "hurt" or at least not help your current setup: 1. It complicates Error Isolation Merge Commit is "all-or-nothing." It merges multiple independent NiFi requests into a single transaction. * The Risk: If NiFi sends 5 large flowfiles and one of them contains a data quality error (e.g., a string in an integer column with

strict_mode

), StarRocks will fail the entire merged transaction. * Result: All 5 batches from NiFi will fail and need to be retried, even if 4 of them were perfectly fine. Without Merge Commit, only the bad batch would fail. 2. It adds unnecessary Latency Merge Commit works by opening a time window (e.g.,

merge_commit_interval_ms=5000

) to wait for more data. * If you are already batching in NiFi, you’ve already paid the "latency tax" to group your data. _ Adding Merge Commit adds _another* wait window on the database side before the data is committed and visible for queries. 3. It is designed for "Small" batches The feature is specifically optimized for high-concurrency small-batch scenarios (KB to tens of MB). Since your batches are already large (100MB+), you have already solved the "too many versions" problem at the source. 4. No benefit for Single-Concurrency If your NiFi flow is configured to send one batch at a time (e.g.,

Max Concurrent Tasks = 1

on your

InvokeHTTP

processor), Merge Commit provides zero benefit because there are no other concurrent requests to merge with. ────────── When SHOULD you still use it? You should only consider adding

enable_merge_commit

to your batched NiFi flow if: * High Table Concurrency: You have many different NiFi clusters or other systems loading into the same table simultaneously, and you are seeing "too many versions" errors despite your 100MB batches. * Version Pressure: You notice the StarRocks FE is struggling with transaction management (check FE

max_running_txn_num_per_db

limits). Verdict: If your current NiFi batching is working and you aren't seeing versioning errors, don't add it. It adds complexity and "blast radius" to errors without a clear performance upside for large batches. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/loading_introduction/loading_considerations.md References • faq/Deploy_faq.md • loading/loading_introduction/loading_considerations.md • administration/management/BE_configuration.md • faq/loading/Loading_faq.md • loading/StreamLoad.md • administration/management/FE_configuration.md

@Rocky well it is concurrent tasks = 1 but i have 6 nodes in my nifi cluster

Even with a 6-node NiFi cluster, if you are already batching your data to 100MB–1GB per load, you should still avoid Merge Commit for now. Here is the breakdown of why the "6 nodes" factor doesn't change the recommendation in your specific case: 1. Your actual transaction rate is very low StarRocks can comfortably handle dozens of transactions per second (TPS) per table. * With 6 nodes and 100MB batches, even if every node finishes a batch every 5 seconds, you are only generating 1.2 transactions per second (6 nodes / 5 seconds). * Merge Commit is designed for scenarios where users are hitting 100+ transactions per second with tiny files (e.g., 50KB files from 1000 different IoT devices). 2. The "Blast Radius" is the biggest risk In a 6-node NiFi cluster, if you enable Merge Commit: * StarRocks might take a batch from Node A and a batch from Node B and wrap them into the same transaction. * If *Node A*'s data has a formatting error, *Node B*'s perfectly valid data will also be rolled back and fail. * This makes troubleshooting in NiFi a nightmare because Node B will report a failure for no apparent reason. 3. Resource Efficiency At 100MB+, the overhead of the HTTP connection and the transaction management is tiny compared to the time spent actually writing the data to disk. You won't see a meaningful performance gain by merging two 100MB files into one 200MB transaction, but you will add the complexity of the Merge Commit window. When would you change your mind? Only consider enabling it if: 1. Version Errors: You see

too many versions

or

OLAP_ERR_TOO_MANY_VERSIONS

in your BE logs. 2. Batch Size Shrinks: You decide you need "real-time" visibility and drop your NiFi batch size from 100MB down to 1MB or less. 3. Cluster Scales: You grow to 50+ NiFi nodes all hitting the same table at high frequency. Summary for your setup: Keep your NiFi batching as is, and leave Merge Commit off. Your current architecture (6 nodes, 100MB batches) is a very healthy and standard way to use StarRocks. References • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/loading_introduction/loading_considerations.md References • administration/management/monitoring/metrics.md • administration/management/BE_configuration.md • loading/StreamLoad.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md

@Rocky what if it isn't always 100mb, what's a lower limit to stay above or does that not matter if the volume doesn't increase?

The lower limit is less about the size (MB) and more about the frequency (how often you hit the "Commit" button). In StarRocks, the "Too Many Versions" error is triggered by the number of transactions, regardless of whether those transactions contain 1KB or 1GB of data. 1. Does volume matter? No. If you send 1,000 loads of 1KB each, you create 1,000 data versions. If you send 1,000 loads of 1GB each, you also create 1,000 data versions. The system will hit the same performance wall (

OLAP_ERR_TOO_MANY_VERSIONS

) in both cases because the background compaction process has to merge those 1,000 versions into one. 2. What is the practical "Lower Limit"? While there is no hard-coded minimum, follow these "Green Zone" guidelines for NiFi: * Recommended Floor: 10MB per batch. * Ideal Batch: 100MB+. * Frequency Limit: Try to keep your total cluster-wide loads to less than 10 per second per table. If your NiFi nodes are sending data so fast that batches are consistently under 5MB, you should increase the "Minimum Group Size" or "Max Bin Age" in your NiFi

MergeContent

processor. 3. Calculating your "Safety Margin" You mentioned 6 NiFi nodes. Let's look at the math: * Safe: Each node sends a batch every 5 seconds. Total = 1.2 loads/sec. This is perfectly healthy, even if the batches are only 1MB. * Risky: Each node sends a batch every 50ms. Total = 120 loads/sec. Even if your total volume is low, you will likely crash the table's compaction within minutes. 4. How to tell if your batches are "Too Small" You can check if your current NiFi frequency is stressing the system by running this in StarRocks:

sql
SHOW PROC '/dbs/your_db_name/your_table_name/partitions';

Look at the

MaxCS

(Max Compaction Score) column: * 0–20: Very healthy. Your NiFi batching is perfect. * 20–50: Acceptable, but the system is working hard to keep up. * 100+: Danger zone. You are sending loads too frequently; increase your NiFi batch size or time window immediately. Summary: If your total volume is low, you don't need 100MB batches, but you should still ensure you aren't spamming the database with hundreds of tiny commits per second. Aim for at least a 5-10 second window in NiFi. References • loading/loading_introduction/loading_considerations.md • faq/loading/Loading_faq.md • administration/management/BE_configuration.md • loading/StreamLoad.md • knowledge/admin_and_operation/shared_data_compaction.md • faq/loading/Stream_load_faq.md • administration/management/FE_configuration.md • faq/loading/Routine_load_faq.md • faq/shared_data_faq.md • loading/loading_introduction/loading_considerations.md • faq/loading/Loading_faq.md • administration/management/BE_configuration.md • loading/StreamLoad.md • knowledge/admin_and_operation/shared_data_compaction.md • faq/loading/Stream_load_faq.md

@Rocky ok so some sources have much more volume than others, if i batch them up in nifi by 5 minutes, I end up with some being 2mb but others being in the KBs, will that be ok with the stream load?

If you are batching every 5 minutes, having files that are only a few KBs is perfectly fine and will not hurt StarRocks. The reason "small files" are often called a "problem" in StarRocks is not actually because of their size (MBs), but because of the transaction frequency (commits per second). Why your 5-minute window makes KBs safe: StarRocks struggles when you hit the "Too Many Versions" limit (usually 1,000 versions per tablet). * The Math: If your 6 NiFi nodes each send a KB-sized file once every 5 minutes, that is only 72 transactions per hour for the whole table. * The Capacity: StarRocks is designed to handle thousands of transactions per hour. Even at a few KBs per file, your frequency is so low that the background compaction process will have zero trouble merging those tiny files into larger ones. When should you worry about KB-sized files? You only need to worry if the file size is small AND the frequency is high. For example: * Safe: 1KB file every 5 minutes (Low volume, Low frequency). * Risky: 1KB file every 100 milliseconds (Low volume, High frequency). This would create 36,000 versions per hour, which would quickly crash the table's compaction. Do you need Merge Commit for these KB files? No. Merge Commit is designed to take thousands of tiny incoming requests per second and squish them into one transaction. Since you only have 6 nodes sending data once every 5 minutes, there is nothing for the database to "merge." The requests are too far apart in time to overlap. Summary of Best Practices for your NiFi flow: 1. Keep the 5-minute window: This is your primary protection against versioning issues. 2. Ignore the small KB sizes: If a source simply doesn't have much data, sending a tiny file every 5 minutes is much better than sending it every 5 seconds. 3. Monitor Compaction: If you want to be 100% sure, run

SHOW PROC '/dbs/<db_id>/<table_id>/partitions';

and check the

MaxCS

column. If it stays under 20, your 5-minute strategy is working perfectly. The Verdict: Your current setup is very safe. The 5-minute timer is more important than the MB size of the file. References • loading/loading_introduction/loading_considerations.md • faq/loading/Loading_faq.md • loading/StreamLoad.md • faq/loading/Stream_load_faq.md

@Rocky ok to move down to 1 minute?

Yes, moving down to 1 minute is still very safe for StarRocks. At a 1-minute interval across 6 NiFi nodes, you are only generating 6 transactions per minute (or 0.1 transactions per second) for that table. StarRocks is designed to handle roughly 1 transaction per second per table without needing any special tuning. Why 1 minute is a "Sweet Spot" * Latency vs. Health: You get much better "freshness" (1-minute delay instead of 5) without putting any measurable strain on the database. * Version Management: Even if your files are only a few KB, 6 versions per minute is very easy for the StarRocks background "Compaction" process to merge. It will keep your

MaxCS

(Compaction Score) very low (likely under 10). * Resource Overhead: The CPU and memory cost of opening 6 connections per minute is negligible for a modern cluster. When would 1 minute be "Too Fast"? You should only worry if you have hundreds of different NiFi flows all hitting the same table. * Safe: 6 nodes $\times$ 1 load/minute = 6 loads/min (Safe) * Borderline: 60 nodes $\times$ 1 load/minute = 1 load/sec (Monitor

MaxCS

) * Risky: 6 nodes $\times$ 1 load every 2 seconds = 180 loads/min (May need Merge Commit) How to verify it's working After you switch to 1 minute, run this command every once in a while:

sql
SHOW PROC '/dbs/your_db/your_table/partitions';

Check the

MaxCS

column. If it stays under 20, you are in the "Green Zone" and could even go faster if you wanted to. If it starts climbing toward 100, then 1 minute is too frequent for your specific hardware/table schema. Verdict: Go ahead and move to 1 minute. It is a standard, healthy configuration for StarRocks. References • loading/loading_introduction/loading_considerations.md • faq/loading/Loading_faq.md • loading/StreamLoad.md • faq/loading/Stream_load_faq.md • administration/management/BE_configuration.md • administration/management/FE_configuration.md • knowledge/admin_and_operation/shared_data_compaction.md References • loading/Stream_Load_transaction_interface.md • faq/loading/Stream_load_faq.md • loading/Loading_intro.md • administration/management/monitoring/metrics.md • loading/StreamLoad.md • loading/loading_introduction/loading_considerations.md • faq/loading/Loading_faq.md • administration/management/BE_configuration.md • loading/StreamLoad.md • knowledge/admin_and_operation/shared_data_compaction.md • faq/loading/Stream_load_faq.md • administration/management/FE_configuration.md • faq/loading/Routine_load_faq.md