@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) note: by lowecase I mean for exmaple eventuid or eventtype 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 ? in addition if my json sends things EventId (camel case) -> do you recommend storing the column name like that or storing it as lowercase (small letter) ?

For StarRocks version 4.1.1, here is how the mapping and case sensitivity work for Stream Load (including Multi-Table Stream Load Transactions): 1. Does case matter for field mapping? Yes, case matters. * Simple Mode (Automatic Mapping): If you do not provide

jsonpaths

, StarRocks attempts to map JSON keys to table columns by name. This mapping is case-sensitive. If your JSON has

EventId

(CamelCase) and your table column is

eventid

(lowercase), StarRocks will not automatically map them; the column will likely be filled with

NULL

. * Nested Structs: The same rule applies to the fields inside a

STRUCT

column. If your nested JSON contains

{"EventType": "login"}

and your StarRocks table defines the struct field as

eventtype

, they will not match. 2. Mapping nested JSON to a STRUCT column * Support: StarRocks (v3.3 and later, including 4.1.1) natively supports loading a nested JSON object directly into a

STRUCT

column. * Mapping: Inside the struct, StarRocks maps fields by name (case-sensitive). * Behavior: It is the same regardless of whether you are in shared-data or shared-nothing (standard) mode. The parsing and mapping logic resides in the Compute Node (CN) or Backend (BE) and is format-specific, not storage-mode specific. 3. Recommendations * Column Naming: It is strongly recommended to store your StarRocks column names in lowercase. StarRocks identifiers are case-insensitive in SQL queries by default, and lowercase is the standard convention. * Handling CamelCase JSON: If your source JSON uses CamelCase (

EventId

) but your table is lowercase (

eventid

), you have two choices: 1. Change the source: If possible, emit lowercase JSON. 2. Use `jsonpaths`: Use the

jsonpaths

header in your Stream Load request to bridge the naming gap. For example:

bash
        -H "jsonpaths: [\"$.EventId\", \"$.NestedField.EventType\"]" \
        -H "columns: eventid, eventtype"

This explicitly tells StarRocks which JSON path corresponds to which table column, effectively ignoring the case mismatch. Summary Table for v4.1.1 Feature | Behavior Top-level JSON to Column | Case-sensitive (requires exact match or

jsonpaths

) Nested JSON to STRUCT | Supported; sub-fields matched by name (case-sensitive) Shared-data vs Shared-nothing | No difference in mapping behavior Best Practice | Use lowercase for all StarRocks column and struct field names If you are using the Multi-Table Stream Load Transaction, the mapping logic for each individual table's data follows these same rules. You must ensure the field names in the JSON sub-objects match the target table schemas exactly. References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Json_loading.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/StreamLoad.md

@Rocky you said "*3. Recommendations* * Column Naming: It is strongly recommended to store your StarRocks column names in lowercase. StarRocks identifiers are case-insensitive in SQL queries by default, and lowercase is the standard convention. * Handling CamelCase JSON: If your source JSON uses CamelCase (

EventId

) but your table is lowercase (

eventid

), you have two choices: 1. Change the source: If possible, emit lowercase JSON. 2. Use `jsonpaths`: Use the

jsonpaths

header in your Stream Load request to bridge the naming gap. For example:" -> if your saying that StarRocks identifiers are case-insensitive in SQL queries by default, then why do you strongly recommended recommend to store it as lowercase ? and is this recommendation the same for nested struct field names ? Another question is if we pass a in the json a field name that has no column will the stream load api or the Multi-Table Stream Load Transaction fail as in return an error to us or will it just ignore that field -> for example if the json is (field1: "", field2:"") but the table only has field1 will it populate field1 and ignore that field2 or fail the update completely ? also if column names or field names in nested structs allowed to start with a number ?

For your version (4.1.1), here are the answers to your follow-up questions: 1. Why recommend lowercase if SQL is case-insensitive? Even though StarRocks is case-insensitive in SQL queries (meaning

SELECT EventId

and

SELECT eventid

work the same), it is strongly recommended to use lowercase for your column names for two main reasons: * Data Loading Matching: As you've seen, the loading process (JSON key to column mapping) is case-sensitive. If your column is

eventid

and your JSON is

{"EventId": 1}

, they won't match automatically. If everything is lowercase, you avoid constant debugging of mapping issues. * External Compatibility: Many external tools, BI platforms, and data lake formats (like Parquet/Iceberg) have varying rules for case sensitivity. Lowercase is the "safest" common denominator that prevents metadata mismatch when exporting or using external catalogs. * Consistency: StarRocks stores metadata in a specific case. Using lowercase avoids confusion when looking at system tables or using tools that might display the underlying case. 2. Is this recommendation the same for nested STRUCT fields? Yes. The fields inside a

STRUCT

follow the same logic. While SQL access to

struct_col.FieldA

is case-insensitive, the JSON loading into that struct is case-sensitive. To ensure your nested JSON maps correctly without complex

jsonpaths

, you should define your struct fields in lowercase. 3. What happens if the JSON contains extra fields? If your JSON contains fields that do not exist in your StarRocks table: * Default Behavior: StarRocks will ignore the extra fields and only load the ones that match your table columns or

jsonpaths

. * Success/Failure: The load will not fail. It will successfully populate the columns it found and discard the extra data. * Multi-Table Stream Load: This behavior is the same. For each table defined in your transaction, StarRocks will only pick the relevant fields and ignore others in the JSON object. 4. Can names start with a number? No. According to StarRocks naming conventions: * Standard Columns: Must start with a letter or an underscore (

_

). They cannot start with a digit (0-9). * STRUCT Fields: Follow the same naming rules as columns. They must start with a letter or underscore. * Example:

1st_event

is invalid, but

event_1

or

_1st_event

is valid. Summary of Rules for v4.1.1 Feature | Rule / Behavior SQL Query Sensitivity | Case-insensitive for columns/fields Data Load Sensitivity | Case-sensitive (must match exactly) Extra JSON Fields | Ignored (does not cause failure) Missing JSON Fields | Loaded as

NULL

(or default value) Identifier Start Character | Letter or Underscore (Digits not allowed at the start) Recommendation: For the best experience, name all columns and nested struct fields in lowercase and ensure they start with a letter. This will save you significant effort in configuring

jsonpaths

for every load. References • sql-reference/System_limit.md • loading/Json_loading.md • sql-reference/data-types/semi_structured/STRUCT.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md References • sql-reference/System_limit.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/FE_configuration.md

@Rocky I defined in the struct column 1st<something> and it worked

That is an interesting observation. To clarify the behavior you are seeing in StarRocks 4.1.1, here is the breakdown of how identifiers and field mapping work: 1. Starting with a Number The official documentation (System Limits) states that column names must start with a letter or an underscore (

_

). However, you likely found that it worked because: * Backticks: If you used backticks (e.g., ``

1st_event

``) in your

CREATE TABLE

statement, StarRocks allows identifiers to start with digits. This follows the MySQL protocol behavior. * Struct Parser Lenience: The parser for the

STRUCT<...>

type is sometimes more lenient than the standard column parser. * Recommendation: Even if it "works," it is still highly recommended to start with a letter. Some external components (like certain BI tools or older Spark connectors) might struggle to parse column names starting with numbers, leading to errors later in your data pipeline. 2. Extra Fields in JSON If your JSON contains

field2

but your table only has `field1`: * Behavior: StarRocks will ignore the extra field (

field2

). * Result: It will successfully populate

field1

and the load will complete without error. This is true for both standard Stream Load and Multi-Table Stream Load Transactions. * Missing Fields: Conversely, if your table has a column that is missing from the JSON, it will be populated with

NULL

(or the column's

DEFAULT

value) without failing the load. 3. Recommendations for your Case Since you are using StarRocks 4.1.1, here is the most robust setup for your scenario: * Case Sensitivity: While SQL queries are case-insensitive, data loading is case-sensitive. If your JSON uses

CamelCase

and your columns are

lowercase

, the mapping will fail (columns will be NULL) unless you use

jsonpaths

. * Struct Fields: The recommendation to use lowercase applies to nested struct fields as well. If your nested JSON is

{"EventUid": "..."}

but your struct field is defined as

eventuid

, they will not match automatically. * Naming Convention: * Column Names: lowercase, start with a letter. * Struct Fields: lowercase, start with a letter. * Reason: This setup allows you to use the "Simple Mode" of Stream Load where StarRocks automatically maps JSON keys to columns/fields by name without you having to define a complex

jsonpaths

array. Summary for v4.1.1 Question | Answer Start with a number? | Possible (usually via backticks), but not recommended for compatibility. Extra fields in JSON? | Ignored automatically; the load succeeds. Missing fields in JSON? | Loaded as NULL or Default; the load succeeds. Case Mismatch? | Will result in NULL values unless

jsonpaths

is used. Shared-data mode? | Behavior is identical to shared-nothing for these rules. Pro Tip: If you cannot change your source JSON (which is CamelCase) and you don't want to use

jsonpaths

, you can define your StarRocks columns with the exact same case as the JSON (e.g.,

EventId

). StarRocks will still let you query them as

eventid

in SQL, and the loader will find them find them automatically. However, lowercase remains the "gold standard" for avoiding future headaches with external integrations. References • sql-reference/System_limit.md • loading/Json_loading.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/semi_structured/STRUCT.md References • sql-reference/System_limit.md

@Rocky if in the json I got a field or nested field that starts with 1st and in the starRocks table nested struct column its defined with Backticks will the stream loader api know to match it ?

Yes, in StarRocks 4.1.1, the Stream Load API (and Multi-Table Stream Load) will be able to match it. Here is why it works and what you need to know: 1. Backticks are for SQL, not the Metadata When you define a column or a struct field using backticks—for example, ``

1st_event

``—the backticks are simply instructions to the SQL parser to allow a "non-standard" identifier (one starting with a number). * The actual name stored in the StarRocks metadata is the literal string:

1st_event

. * The Stream Load API looks at that internal metadata name when matching JSON keys. 2. Matching Logic If your JSON is:

json
{
  "1st_event": "login",
  "nested_col": {
    "1st_subfield": 123
  }
}

And your table is defined as:

sql
CREATE TABLE my_table (
    `1st_event` VARCHAR(64),
    nested_col STRUCT<`1st_subfield` INT>
) ...

The loader will successfully match

"1st_event"

to the top-level column and

"1st_subfield"

to the field inside the

STRUCT

. 3. Critical Requirements for Matching While it "knows" to match the name, it is still bound by these strict rules: * Exact Case Match: The JSON key must be

1st

(if your table is

1st

). If the JSON sends

1St

or

1ST

, the simple mapping will fail and the value will be

NULL

. * No Backticks in JSON: Your JSON keys should not contain backticks (e.g.,

"1st_event"

, not `` "`1st_event`" ``). * Simple Mode vs. jsonpaths: As long as the names match exactly (including the leading number), you do not need to use

jsonpaths

. StarRocks will map them automatically in "Simple Mode." Summary Recommendation It is perfectly fine to use names starting with numbers if you use backticks in your DDL. The Stream Load API will handle the mapping correctly as long as the string in the JSON key matches the string inside the backticks in your table definition. One final note: If you ever find that a specific external tool or connector (like an older Spark connector or a specific BI tool) has trouble with columns starting with numbers, you can always use the

jsonpaths

header in Stream Load to map a "safe" column name (like

first_event

) to the JSON key

1st_event

. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • sql-reference/data-types/semi_structured/STRUCT.md • loading/Json_loading.md • sql-reference/System_limit.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/keywords.md References • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • sql-reference/sql-statements/loading_unloading/routine_load/CREATE_ROUTINE_LOAD.md • loading/StreamLoad.md

@Rocky do you think shared-data mode is tested enough to use instead of not shared mode ? like I see its got some bugs and so, so im worried abit about using it in production

The question of whether to use shared-data (compute-storage separation) or shared-nothing (classic FE/BE) is one of the most common architectural decisions in StarRocks. Here is an honest breakdown of the maturity and trade-offs as of version 4.1. 1. Maturity and "Battle-Testing" * Shared-nothing (Mature): This is the original StarRocks architecture. It has been used in massive production environments for years. If your primary concern is "zero surprises" and you don't need the elasticity of the cloud, this is the safest choice. * Shared-data (Production-Ready but Newer): Shared-data mode was introduced in version 3.0 and became "enterprise-ready" around version 3.2/3.3. By version 3.5 and your current 4.1, most of the critical stability issues (like small file proliferation and compaction lag) have been addressed with features like Load Spill and Parallel Compaction. 2. Common Issues in Shared-data (and how they are handled now) You mentioned seeing bugs. Most historical bugs in shared-data mode fall into these categories: * Small File Issues: In early 3.x versions, frequent small loads created too many small files on S3/OSS, which crushed query performance and compaction. _ _Status in v4.1:* Significant optimizations (Batch Ingestion/Load Spill) now merge these data blocks more efficiently before writing to object storage. * Primary Key Table Performance: Initially, PK tables were slower in shared-data mode because the index had to be managed differently. _ _Status in v4.1:* PK index persistence and parallel publish have made PK tables very stable and fast in shared-data mode. * Metadata Sync: Because data is remote, the FE (Frontend) must work harder to track metadata. _ _Status in v4.1:* Version 3.5+ introduced Cluster-level Snapshots, which greatly improved the reliability of metadata recovery and disaster recovery for shared-data clusters. 3. When to Choose Shared-data (Despite the "Newness") Despite being newer, shared-data is often the better choice if: * Cost is a factor: Storing petabytes on S3/GCS is significantly cheaper than on high-performance SSDs attached to BE nodes. * Elasticity is needed: If you use Kubernetes, you can scale your Compute Nodes (CNs) up or down in minutes without moving data. In shared-nothing mode, adding a node requires a "rebalance" which can take hours or days. * Isolated Workloads: You can point multiple Compute Groups to the same data, allowing you to run heavy ETL on one set of nodes and user queries on another without interference. 4. When to Stick with Shared-nothing * On-Premise Hardware: If you aren't using object storage (S3/MinIO/HDFS), shared-nothing is the natural fit. * Extreme Performance (Sub-second): While shared-data with Data Cache is very fast, shared-nothing still has a slight edge for ultra-low latency queries because data is strictly local to the CPU. * Maximum Simplicity: Shared-nothing has fewer moving parts (no object storage dependency). Recommendation for Version 4.1.1 If you are starting a new project on AWS/GCP/Kubernetes, the community and the StarRocks team generally recommend Shared-data mode. It is the focus of current development, and the major stability hurdles have been cleared. To minimize risk in production: 1. Use Version 3.3.x or 4.x: Avoid early 3.0/3.1 versions for shared-data. 2. Monitor Compaction Score: In shared-data, keep an eye on

information_schema.be_cloud_native_compactions

. If the score stays high, you need more compute resources for compaction. 3. Sized Local Cache: Ensure your CN nodes have enough local SSD space for the

datacache

. This is the key to shared-data stability. Summary: Shared-nothing is more "tested" by time, but Shared-data is "tested enough" for production in 4.x and is the standard for modern cloud deployments. References