@Rocky I just started using starRocks, the goal is to have hot data in starRocks and cold data as iceberg tables in s3 and use starRocks as a federated query engine, but Im running into issues with dot notion (accessing struct column type in iceberg and its corresponding json column in starRocks ) since starRocks does not support dot notion for json columns. am I wrong ? do you have any suggestions what I should do ?

You are correct that StarRocks does not support dot notation (e.g.,

col.field

) for the JSON data type. However, dot notation is supported for the STRUCT data type. Since your goal is to use StarRocks as a federated engine with hot data (internal) and cold data (Iceberg), here is how you can resolve the syntax mismatch: 1. The Recommended Fix: Use

STRUCT

in StarRocks StarRocks introduced support for the

STRUCT

data type in internal tables starting with v3.1. For external Iceberg tables,

STRUCT

fields are mapped to StarRocks

STRUCT

types (supported since v2.5). If you define your internal StarRocks table using the

STRUCT

type instead of

JSON

, you can use dot notation for both: * Internal Table (Hot):

sql
    CREATE TABLE hot_data (
        id INT,
        user_info STRUCT<name STRING, age INT>
    ) ...;

    SELECT user_info.name FROM hot_data; -- This works

* Iceberg Table (Cold):

sql
    -- StarRocks automatically maps Iceberg structs to its internal STRUCT type
    SELECT user_info.name FROM iceberg_catalog.db.cold_data; -- This also works

2. If you must use

JSON

in the internal table If you are on a version earlier than 3.1 or require the dynamic nature of

JSON

for your hot data, you must use the arrow operator (

->

) or JSON functions. Dot notation will not work. * JSON Syntax:

column->'path'

sql
    -- If user_info is a JSON column
    SELECT user_info->'name' FROM hot_data;

* Accessing nested fields:

column->'field'->'subfield'

or

get_json_string(column, '$.field.subfield')

. Summary Comparison Feature |

JSON

Type |

STRUCT

Type Dot Notation (

col.field

) | ❌ No | ✅ Yes Arrow Operator (

col->'f'

) | ✅ Yes | ❌ No Schema | Flexible / Dynamic | Fixed / Strongly Typed StarRocks Version | v2.2+ | v3.1+ (Internal), v2.5+ (External) Recommendation: To keep your queries consistent across hot and cold data, modify your StarRocks internal table to use the

STRUCT

type. This will allow you to use the same dot-notation SQL for both federated Iceberg queries and local StarRocks queries. References • using_starrocks/Flat_json.md • sql-reference/sql-functions/json-functions/overview-of-json-functions-and-operators.md

@Rocky using a struct column for the starRocks hot table might be an issue. our table is a primary key modal table -> we read that if tomorrow we want to add a new field to that struct column in starRocks table, it would mean that a full rewrite of the table data is needed. am I wrong ?

You are partially correct about the historical limitations, but recent versions of StarRocks have introduced a feature specifically to solve this problem. The "Full Rewrite" Concern In older versions of StarRocks, any modification to a column (including changing its type or structure) required a "Schema Change" job that effectively rewrote all the data on disk. However, since v3.2.10 and v3.3.2, StarRocks supports Fast Schema Evolution for

STRUCT

columns. This allows you to add or drop fields within a

STRUCT

as a metadata-only operation. This means there is no full rewrite of the data, and the change is nearly instantaneous. How to do it correctly To use

STRUCT

in your Primary Key table without worrying about future rewrites, you must follow these requirements: 1. Enable Fast Schema Evolution: You must set the property

"fast_schema_evolution" = "true"

when you create the table. (Note: This property currently cannot be enabled after the table is already created).

sql
    CREATE TABLE hot_data (
        id INT,
        user_info STRUCT<name STRING, age INT>
    ) PRIMARY KEY (id)
    DISTRIBUTED BY HASH(id)
    PROPERTIES (
        "fast_schema_evolution" = "true" -- REQUIRED for metadata-only changes
    );

2. Use the specific

ADD FIELD

syntax: When you want to add a field later, do not use a generic

MODIFY COLUMN

that redefines the whole type. Use the specialized syntax:

sql
    -- Adds a new field 'email' to the struct without rewriting data
    ALTER TABLE hot_data MODIFY COLUMN user_info ADD FIELD email STRING;

Why this is better than JSON for your use case • Dot Notation: You can use

user_info.name

on both your StarRocks hot tables and your Iceberg cold tables. • Performance:

STRUCT

columns are more efficient for the StarRocks execution engine than

JSON

columns because they are strongly typed and benefit from StarRocks' columnar optimizations. • Consistency: It allows your federated queries (joining hot and cold data) to use identical SQL syntax. Summary: If you are on a recent version (v3.2.10+ or v3.3.2+), you can safely use

STRUCT

with the

fast_schema_evolution

property enabled to avoid the full data rewrite you feared. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md

@Rocky ok so this solved the case where we want to add a new field to a struct column type. another question is if doing add column/drop column in starRocks tables also require a full data re write ? im using starRocks 4.0.10

In StarRocks 4.0.10, adding or dropping columns does not require a full data rewrite, provided you are modifying value columns (non-primary key columns). Here is the breakdown of how it works in your version: 1. Light Schema Change (The Default) StarRocks uses a "Linked Schema Change" (also called Light Schema Change) for adding or dropping value columns. • Adding a column: This is a metadata-only operation. The FE (Front End) simply records the new column in the schema. Existing data files are not touched. When you query the table, StarRocks fills in the default value (or NULL) for the rows in the old data files on the fly. • Dropping a column: This is also metadata-only. The column is simply "hidden" from the schema and eventually cleaned up during background compaction, but no immediate rewrite occurs. 2. Fast Schema Evolution While Light Schema Change covers standard columns, you should still ensure

fast_schema_evolution

is enabled for the best experience (especially if you plan to add/drop fields inside a

STRUCT

as discussed previously). • In version 4.0.x, it is still a best practice to set

"fast_schema_evolution" = "true"

in the

PROPERTIES

when creating your table. • This property specifically optimizes the handling of default values and complex types (like your

STRUCT

columns) during schema changes. 3. When a rewrite IS required There are only a few specific scenarios where StarRocks would still need a "Direct" or "Sorted" schema change (which involves a rewrite): • Modifying the Primary Key: You cannot add or remove a column from the Primary Key definition without recreating the table. • Changing Column Type: Changing a column from

INT

to

BIGINT

or

STRING

to

JSON

usually requires a data rewrite to transform the underlying physical storage. • Modifying Sort Keys: If you have defined a separate

ORDER BY

key (different from the primary key) and try to change it, a rewrite is needed to re-sort the data. Summary for your use case Since you are using StarRocks as a "hot" layer for data that will eventually go to Iceberg: • Adding new metrics/attributes: Fast and no rewrite (Metadata only). • Dropping old fields: Fast and no rewrite (Metadata only). • Adding fields to a `STRUCT`: Fast and no rewrite (if

fast_schema_evolution

is true). Tip: Always check the status of a schema change by running

SHOW ALTER TABLE COLUMN;

. For value columns in 4.0.10, you will see them finish almost instantly. References • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • table_design/table_types/primary_key_table.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md References • best_practices/query_tuning/schema_tuning.md • table_design/table_types/primary_key_table.md • release_notes/release-3.2.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md

@Rocky why isnt

fast_schema_evolution

enabled by default ? is there a disadvantage to having it enabled ?

The reason

fast_schema_evolution

is not enabled by default for all table types (specifically in the "shared-nothing" or classic storage model) is primarily due to its architectural requirements and maturity. Here is the breakdown of why it’s opt-in and what you should consider before using it: 1. Why it isn't enabled by default (Shared-Nothing) * Immutability of Table Properties: In StarRocks,

fast_schema_evolution

changes how the Front End (FE) and Back End (BE) handle column metadata. Currently, this property must be set at table creation. If StarRocks were to enable it by default for a cluster, it could cause compatibility issues for users upgrading from older versions or for tools that expect the traditional "Linked Schema Change" behavior. * Architectural Transition: StarRocks is moving toward making this the default. In fact, for shared-data (cloud-native) clusters, it is already enabled by default (since v3.3). For the classic shared-nothing model, it remains an explicit choice to ensure stability for legacy workloads. 2. Are there any disadvantages? There are no major performance "penalties" to the data itself, but there are a few technical trade-offs: * Permanent Decision: You cannot turn it on or off after the table is created. If you forget to enable it and later need to add a field to a

STRUCT

without a rewrite, you would have to recreate the entire table. * Metadata Complexity: When you add a column "fast," the underlying data files on disk don't actually contain that column yet. The query engine has to "fill in" the default values or NULLs on the fly when reading old data. While this is very efficient, it adds a small amount of logic to the query execution process compared to a table where every column is physically present in every file. * Beta Features: While adding/dropping standard columns is stable, the specific ability to add/drop fields within a

STRUCT

is still marked as a "Beta" feature in the documentation (as of v3.2.x/3.3.x). This means it is safe for most use cases but may have edge cases with extremely complex nested types. Summary For a "hot" table where you anticipate schema changes: • Disadvantage: Essentially none, other than the fact that you have to remember to enable it at the start. • Advantage: You save hours/days of "Full Rewrite" time and massive IO/CPU overhead during schema updates. Verdict: For your StarRocks 4.0.10 cluster, you should definitely enable it for any new Primary Key tables you create, especially if you plan to use

STRUCT

columns.

sql
PROPERTIES (
    "fast_schema_evolution" = "true"
)

References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md • release_notes/release-3.2.md References • administration/management/FE_configuration.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE.md • integrations/smt/starrocks_sqlalchemy.md • table_design/hybrid_table.md • best_practices/query_tuning/schema_tuning.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md