http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> explain how this resource group con...
# questions-and-troubleshooting
k
@Rocky explain how this resource group configuration will behave. there are many prod_etl_user queries being submitted, but i want user facing queries from prod_app_user to have reserved capacity and always take prioritiy if there is a queue of queries
r
To achieve reserved capacity and ensure that 
prod_app_user
queries are not blocked by a backlog of 
prod_etl_user
queries, you should use Exclusive Resource Groups and Group-level Query Queuing. In StarRocks, there is no "priority" flag to let a query jump to the front of a single queue. Instead, priority is handled by isolating the queues and resources so that the application user never has to wait for the ETL user. Recommended Configuration Strategy 1. Use 
exclusive_cpu_cores
for Reserved Capacity By setting 
exclusive_cpu_cores
, you create a hard isolation. The cores assigned to 
prod_app_user
are physically bound to a separate thread pool. Even if 
prod_etl_user
submits thousands of queries, they cannot touch the cores reserved for the app user. * 
prod_app_user
group: Set 
exclusive_cpu_cores = N
. These cores are "reserved" and only used by this group. * 
prod_etl_user
group: Use 
cpu_weight
(Shared Group). These queries will run on the remaining cores not reserved by exclusive groups. 2. Enable Group-level Query Queuing By default, queries may be subject to a global queue. To ensure the ETL backlog doesn't stall the App queries, you must enable Resource Group-level queuing (available since v3.1.4). This ensures each group has its own "waiting line."
Copy code
sql
-- Enable this globally to isolate the queues
SET GLOBAL enable_group_level_query_queue = true;
3. Example Resource Group Setup
Copy code
sql
-- 1. Group for App Users (High Priority / Reserved)
CREATE RESOURCE GROUP app_wg
TO (user='prod_app_user')
WITH (
    "exclusive_cpu_cores" = "4", -- Adjust based on your BE core count
    "concurrency_limit" = "20",   -- App queries can always start if under this limit
    "mem_limit" = "40%"
);

-- 2. Group for ETL Users (Lower Priority / Shared)
CREATE RESOURCE GROUP etl_wg
TO (user='prod_etl_user')
WITH (
    "cpu_weight" = "2",         -- Shares the remaining non-exclusive cores
    "concurrency_limit" = "10",  -- Limits ETL parallelism to prevent memory exhaustion
    "mem_limit" = "40%"
);
How this behaves in your scenario: * Capacity Reservation: The 
prod_app_user
always has 
4
cores (in the example) waiting for it. Even if the ETL group is 100% loaded, it cannot "steal" these cores unless you explicitly enable 
enable_resource_group_cpu_borrowing
(which is 
true
by default, but App queries will immediately reclaim them when they arrive). * Priority in Queuing: Because 
enable_group_level_query_queue
is on, if 
prod_etl_user
submits 100 queries but has a 
concurrency_limit
of 10, 90 queries will sit in the 
etl_wg
queue. When 
prod_app_user
submits a query, it checks the 
app_wg
queue. Since that queue is likely empty, the App query starts immediately using its reserved cores, "skipping" the 90 ETL queries entirely. * If you do NOT use exclusive cores: If both use 
cpu_weight
, they share the same pool of cores. The 
prod_app_user
will get a larger slice of CPU time (if its weight is higher), but it might still experience slightly higher latency if the CPU is heavily over-saturated by the ETL tasks. Summary of required variables Ensure these are set to enable the logic above: * 
SET GLOBAL enable_pipeline_engine = true;
(Required for resource groups) * 
SET GLOBAL enable_group_level_query_queue = true;
(Required for independent queues) References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
k
@Rocky for other groups not in app_wg, what does "cpu_weight" = "2" do against the other wgs that do not have exclusive cores
r
When you set 
cpu_weight = 2
for a group like 
etl_wg
, it determines that group's proportional share of the "Shared CPU Pool." Here is exactly how that weight behaves against other groups: 1. The "Shared CPU Pool" The total CPU capacity on a BE node is split into two pools: * Exclusive Pool: Cores reserved by groups using 
exclusive_cpu_cores
(like your 
app_wg
). * Shared Pool: All remaining cores. This is where 
cpu_weight
applies. 2. Relative Proportions The 
cpu_weight
is not a fixed number of cores; it is a numerator in a fraction. A group’s share of the Shared Pool is calculated as: $$\text{Group Share} = \frac{\text{Group's cpu\_weight}}{\sum(\text{cpu\_weights of all active groups in the Shared Pool})}$$ Example Scenario: If you have a BE with 16 cores and you reserved 4 for 
app_wg
, you have 12 cores left in the Shared Pool. * etl_wg: 
cpu_weight = 2
* default_wg: (The system default group where other queries go). Its default weight is usually the total number of cores on the BE (e.g., 
16
). * Result: If both are fully loaded, 
etl_wg
gets $2 / (2 + 16) \approx 11\%$ of those 12 shared cores. 3. Soft Isolation (Borrowing) * Under Load: If every group is busy, StarRocks enforces the weights strictly. The ETL group will be throttled to its 11% share so other groups aren't starved. * Idle System: If the 
default_wg
is idle, the 
etl_wg
can "burst" and use up to 100% of the shared cores. It is only restricted when there is competition. * Borrowing from Exclusive: By default (
enable_resource_group_cpu_borrowing = true
), the ETL group can even use the 4 cores reserved for 
app_wg
if that group is currently idle. However, the moment a 
prod_app_user
query arrives, the ETL group must immediately give those 4 cores back. 4. Comparison with 
default_wg
 It is important to check the weight of your 
default_wg
. * If 
default_wg
has a weight of 16 and your ETL group has a weight of 2, the ETL group is actually "lower priority" than the default group. * If you want 
prod_etl_user
to be the lowest priority on the system, ensure its 
cpu_weight
is the smallest number among all your defined groups (the minimum is typically 1). Summary of Behavior Feature | Behavior for 
cpu_weight = 2
 Share | Gets a slice of CPU relative to the weights of other active groups. Constraint | Only throttled when the system is under contention. Isolation | Soft isolation. It can use more than its share if other groups are quiet. Comparison | If another group has 
cpu_weight = 10
, it will get 5x more CPU time than the ETL group when both are running. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
k
@Rocky im creating a wg with multiple users: Getting analyzing error. Detail message: Illegal classifier specifier 'user': 'user = 'user1,user2''.
r
The error occurs because the 
user
classifier does not support a comma-separated list of names within a single set of parentheses. Unlike the 
db
classifier, the 
user
classifier expects a single string per entry. To include multiple users in one resource group, you must provide multiple classifier blocks separated by commas in the 
TO
clause. Correct Syntax
Copy code
sql
CREATE RESOURCE GROUP app_wg
TO 
    (user='user1'), 
    (user='user2')
WITH (
    "exclusive_cpu_cores" = "4",
    "mem_limit" = "40%"
);
Why this happens * 
user
and `role`: These classifiers match a single entity. If you want to match multiple specific users, you list them as separate classifiers. * `db`: This is the only classifier that explicitly supports a comma-separated string (e.g., 
db='db1,db2'
). Alternative: Using Roles If you have many users, it is often easier to assign them to a Role and use the 
role
classifier instead:
Copy code
sql
-- 1. Create a role and assign users to it
CREATE ROLE app_role;
GRANT app_role TO user1, user2;

-- 2. Use the role in the resource group
CREATE RESOURCE GROUP app_wg
TO (role='app_role')
WITH (...)
References • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • administration/management/resource_management/resource_group.md References • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md
k
@Rocky in my shared database i do not want other workloads dominating cpu if i have requests from my app_wg coming in. essentially i want requets from app_wg to always be worked on immediately, no matter of what other queries are running. can this be achived with cpu weights, or only exclusive cpu
r
To ensure that 
app_wg
queries are worked on immediately and are never delayed by other workloads, you should use 
exclusive_cpu_cores
. While 
cpu_weight
gives a group a larger slice of the CPU, only 
exclusive_cpu_cores
provides the physical isolation required for consistent, immediate execution. Comparison: Why Exclusive is better for "Immediate" Work Feature | 
cpu_weight
(Shared) | 
exclusive_cpu_cores
(Exclusive) Execution Pool | Queries share a common thread pool (
pip_exec_com
) with all other groups. | Queries run in a dedicated thread pool (
pip_exec_<id>
) bound to specific cores. Scheduling | Your query must wait for a "time slice" or a free thread in the shared pool. | Your query starts on its own reserved cores immediately, regardless of what the shared pool is doing. Impact of High Load | If the system is flooded with ETL tasks, your "high weight" query still faces scheduling jitter. | Your query is physically invisible to the ETL workload; it has its own "private lane." Predictability | Latency can fluctuate based on how many other queries are "competing" for cycles. | Latency is highly stable because the hardware is reserved. How to achieve "Zero Wait" execution To get the behavior you described, use this configuration: 1. Define Exclusive Cores: This carves out a piece of the BE's CPU specifically for the app.
Copy code
sql
    CREATE RESOURCE GROUP app_wg
    TO (user='prod_app_user')
    WITH (
        "exclusive_cpu_cores" = "8", -- Reserves 8 physical cores
        "mem_limit" = "40%"
    );
2. Enable Group-level Queuing: This is critical. Without this, if the cluster hits a global query limit, your App user might still sit in a queue behind ETL users.
Copy code
sql
    SET GLOBAL enable_group_level_query_queue = true;
3. Understand "CPU Borrowing": By default, StarRocks allows other groups to "borrow" your 8 exclusive cores if 
app_wg
is idle (
enable_resource_group_cpu_borrowing = true
). * The benefit: Resources aren't wasted when your app is quiet. * The behavior: When an 
app_wg
query arrives, the system reclaims those cores. This reclamation is fast (tens of milliseconds), but if you need absolute zero-jitter timing, you can disable borrowing:
Copy code
sql
    -- Run on BEs to prevent others from ever touching app_wg cores
    UPDATE information_schema.be_configs SET VALUE = "false" WHERE NAME = "enable_resource_group_cpu_borrowing";
Summary If your goal is to ensure the app workload is never dominated and starts immediately, 
exclusive_cpu_cores
 is the correct tool. 
cpu_weight
is better suited for balancing throughput between background tasks rather than protecting a latency-sensitive application. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md
k
@Rocky why are my cpu exclusive core workloads still waiting? I am using s3 table buckets, are my nodes hitting s3 table bucket rate limits? What other io can not be isolated?
r
Even with 
exclusive_cpu_cores
, your queries may still "wait" because StarRocks resource groups currently only isolate CPU and Memory. Several other critical resources are shared across the entire cluster and can cause bottlenecks that CPU isolation cannot prevent. 1. I/O Is Not Isolated StarRocks does not currently support I/O isolation (neither for local disk nor for object storage like S3). * The Bottleneck: If a background resource group (like a heavy ETL job) is saturating the BE’s network bandwidth or disk I/O, your 
app_wg
queries will be stuck waiting for data to be read from S3/disk, even though their "private" CPU cores are idle. * S3 Table Buckets: If you are using many tablets (buckets), each scan generates a high volume of 
GET
requests. S3 has a hard limit of 5,500 GET requests per second per prefix. If multiple resource groups are scanning the same bucket/prefix simultaneously, they will hit these rate limits together, causing S3 to throttle all of them (often seen as "SlowDown" errors or high latency in the Query Profile). 2. Other Non-Isolated Resources Besides I/O, these resources are shared and can cause "waiting": * Network Bandwidth: The physical NIC on the BE is shared. Data shuffle (Exchange nodes) between BEs for one group can starve the network for another. * FE Metadata & Planning: Every query must be planned by the Frontend (FE). If the FE is overloaded by thousands of small queries from a background group, the 
app_wg
query will "wait" in the planning phase before it even reaches the BEs. * Global Query Queue: If you have 
enable_query_queue = true
, a query might be queued at the cluster level due to total BE memory or CPU usage, regardless of your exclusive group settings. 3. How to Identify the "Wait" To determine why your exclusive cores aren't helping, run your query and check the Query Profile (using 
get_query_profile('query_id')
): * If 
WaitTime
or 
QueuedTime
is high: The query is stuck in a StarRocks queue. Check your 
concurrency_limit
for the resource group or global 
query_queue
settings. * If 
ScanTime
is high but 
CPUUsage
is low: You are hitting I/O or S3 bottlenecks. The CPU is literally waiting for the network/S3 to deliver data. * Check S3 Metrics: If you see a spike in "S3 SlowDown" errors in your storage monitoring, you are hitting S3 rate limits. Recommended Fixes * Enable Data Cache: Using the StarRocks Data Cache (on local NVMe) can significantly reduce S3 I/O pressure. If the data is in the cache, the 
app_wg
won't have to go to S3, avoiding the shared I/O bottleneck. * Check Concurrency: Ensure the 
concurrency_limit
in your 
app_wg
isn't set too low, causing queries to queue internally. * S3 Path Strategy: If you suspect S3 rate limits, consider using different prefixes or buckets for different high-traffic tables to distribute the 5,500 req/s load. References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/resource_management/query_queues.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/resource_management/query_queues.md
k
@Rocky how is data directly read from iceberg cached when not using an mv
r
When you read data directly from an Iceberg table (without a Materialized View), StarRocks uses a feature called Data Cache (also known as Block Cache) to accelerate subsequent queries by storing data on local BE/CN disks. Here is how the mechanism works: 1. Data Cache (Block-Level) Instead of caching entire files, StarRocks splits Iceberg data files (Parquet or ORC) into equal-sized blocks (default is 1 MB). * First Read (Cache Miss): When a query is executed, the BE node fetches the required blocks from S3. As it reads them, it simultaneously writes those blocks to its local storage (ideally NVMe SSDs). * Subsequent Reads (Cache Hit): For any future query requesting the same data range, the BE checks its local cache first. If found, it reads the data directly from the local disk, bypassing S3 entirely. * Eviction: The cache uses LRU (Least Recently Used) or SLRU strategies. When the allocated disk space is full, older or less frequently accessed blocks are evicted to make room for new data. 2. Metadata Cache In addition to the raw data, StarRocks caches Iceberg metadata (snapshots, manifests, and manifest files) to avoid expensive listing and reading operations on S3: * Memory Cache: FEs cache Iceberg metadata in memory for ultra-fast planning. * Disk Cache (v3.3+): FEs can also cache metadata on their local disks to persist across restarts. 3. How to Enable and Configure The Data Cache is typically enabled by default in newer versions (v3.3+), but you can verify and tune it with these settings: * BE Configuration (
be.conf
): * `datacache_enable = true`: Enables the feature. * `datacache_disk_size`: Sets the total disk space for the cache (e.g., 
214748364800
for 200GB). * Session Variables: * `SET enable_scan_datacache = true;`: Ensures the current session uses the cache. * `SET populate_datacache_mode = 'always';`: Forces the query to populate the cache if data is missing. 4. Monitoring the Cache You can verify if your Iceberg queries are actually hitting the cache by checking the Query Profile: Look for the following metrics under the 
ScanNode
section: * `DataCacheReadBytes`: Amount of data read from the local cache. * `DataCacheWriteBytes`: Amount of data written to the cache (on a miss). * `DataCacheReadCounter`: Number of blocks hit in the cache. Summary of Benefits By using the Data Cache, you get MV-like performance for hot data without the storage overhead or management complexity of a Materialized View, while still maintaining the flexibility of querying the "source of truth" in Iceberg. References • data_source/data_cache.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/System_variable.md References • administration/management/BE_configuration.md • using_starrocks/caching/query_cache.md • data_source/data_cache.md • using_starrocks/caching/block_cache.md • sql-reference/System_variable.md
k
@Rocky how does Starrocks know iceberg cache is still valid? Before using cache blocks from iceberg does it check table snapshots?
r
StarRocks ensures Iceberg cache consistency through a two-layer validation process: Metadata validation at the Frontend (FE) to pick the correct snapshot, and File-level validation at the Backend (BE) to retrieve the correct data blocks. 1. Metadata Layer: Is the Snapshot Current? Before a query even looks at data blocks, the FE must determine which files to read. This is where StarRocks checks the Iceberg Snapshot ID. * Caching Strategy: To avoid hitting the Metastores (like Glue or HMS) for every single query, StarRocks caches the table's "Metadata Pointer" (which contains the current Snapshot ID). * The "Freshness" Window: By default, StarRocks considers the cached Snapshot ID to be "fresh" for 60 seconds (controlled by 
iceberg_table_cache_refresh_interval_sec
). * Within 60s: StarRocks uses the cached snapshot immediately. * After 60s: If a query arrives, StarRocks uses the cached snapshot for the current query but triggers an asynchronous refresh to check the Metastore for a new snapshot ID for subsequent queries. * Background Refresh: There is also a global background thread that polls for updates every 10 minutes (default) for any table accessed in the last 24 hours. Summary for Snapshots: StarRocks does not check the Metastore for a new snapshot for every query by default. It relies on a "lazy" refresh policy to maintain high performance. If you need absolute 1:1 consistency, you can set the catalog property 
iceberg_meta_cache_ttl_sec
to 
0
. 2. Data Layer: Are the Blocks Valid? Once the FE has identified the specific Parquet/ORC files for a snapshot, the BE checks its Data Cache (Block Cache) for those files. * Immutability Advantage: Iceberg files are immutable. When data is updated, Iceberg creates new files rather than overwriting old ones. * Validation Logic: StarRocks identifies cached blocks using a key composed of: 
File Path + File Size + Modification Time
. * Validity Check: Because Iceberg files never change once written, if the FE tells the BE to read 
file_A.parquet
(which it found in the latest snapshot), and the BE has blocks for 
file_A.parquet
in its cache, those blocks are guaranteed to be valid. Why you might see "Stale" data If you update an Iceberg table and StarRocks still shows old data, it is almost always because the FE metadata cache is still pointing to an old Snapshot ID, not because the data blocks themselves are "corrupt" or invalid. How to Force a Refresh If you know the data has changed and you want to bypass the cache: 1. Manual Refresh: Run 
REFRESH EXTERNAL TABLE iceberg_db.table_name;
to force the FE to pull the latest snapshot immediately. 2. Disable Metadata Cache (Session): 
SET enable_iceberg_metadata_cache = false;
forces the query to fetch the latest metadata from the Metastore. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/datalake_faq.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md
k
@Rocky I am fine with stale data, read performance is the most important. What settings do I use to make stale data acceptable and make reads as fast as possible
r
To maximize Iceberg read performance while accepting stale data, you should focus on minimizing communication with the Metastore (Glue/HMS) and S3 by extending the TTLs of your metadata and data caches. 1. Metadata Caching (FE & Catalog Level) These settings ensure StarRocks reuses the same "Snapshot ID" and Manifest files for as long as possible, avoiding the overhead of checking for updates. * Catalog Property: 
iceberg_table_cache_refresh_interval_sec
 Increase this (default 60s) to a very high value (e.g., 
3600
for 1 hour). This prevents StarRocks from even trying to check if a new Iceberg snapshot exists for that duration.
Copy code
sql
    ALTER CATALOG iceberg_catalog SET ("iceberg_table_cache_refresh_interval_sec" = "3600");
* FE Configuration: 
background_refresh_metadata_interval_millis
 Increase this (default 10 minutes) to reduce background polling of the metastore.
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("background_refresh_metadata_interval_millis" = "3600000"); -- 1 hour
* Enable Metadata Disk Cache (v3.3+) This allows metadata to persist across FE restarts.
Copy code
sql
    ADMIN SET FRONTEND CONFIG ("enable_iceberg_metadata_disk_cache" = "true");
* Disable Column Stats Retrieval If you don't need exact min/max for every query, disabling column stats retrieval speeds up the planning phase by reading less metadata.
Copy code
sql
    SET enable_get_stats_from_external_metadata = false;
2. Data Caching (BE/CN Level) This ensures the actual data blocks remain on your local NVMe disks, bypassing S3 entirely for "hot" data. * Session Variable: 
populate_datacache_mode
 Set this to 
always
to ensure that any data not already in the cache is immediately written there during the first read.
Copy code
sql
    SET populate_datacache_mode = 'always';
* BE Config: 
datacache_disk_size
 Allocate as much local SSD/NVMe space as possible. If your cache is too small, StarRocks will evict data (LRU), forcing expensive S3 reads. * BE Config: 
datacache_block_size
 The default is 1MB. For large sequential Iceberg scans, this is usually optimal, but ensure your 
storage_root_path
in 
be.conf
points to your fastest local disks. 3. Execution Strategy * Session Variable: 
plan_mode
 If you have many small manifest files and high concurrency, set this to 
local
. If you have a few massive manifest files, set it to 
distributed
. The default 
auto
is usually good, but 
local
is fastest for metadata-heavy small queries.
Copy code
sql
    SET plan_mode = 'local';
* Session Variable: 
enable_bucket_aware_execution_on_lake
 Ensure this is 
true
(default in v4.0+) to allow StarRocks to optimize joins and aggregations based on Iceberg's bucketing/partitioning, reducing data shuffle. Recommended "Fastest" Session Settings: Run these before your performance-critical workloads:
Copy code
sql
SET enable_scan_datacache = true;
SET populate_datacache_mode = 'always';
SET plan_mode = 'local';
SET enable_get_stats_from_external_metadata = false;
Summary of Trade-offs By using these settings, StarRocks will effectively treat your Iceberg table as a "local" table. It will only hit S3 when a cache miss occurs, and it will only hit the Metastore once per hour (or whatever interval you set), resulting in sub-second latencies for repeated queries. References • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/System_variable.md • using_starrocks/caching/block_cache.md References • sql-reference/System_variable.md • data_source/catalog/iceberg/iceberg_catalog.md
k
@Rocky what other settings can contribute to maximum performant parallelism
r
To achieve the highest possible read performance and parallelism for Iceberg (while accepting stale data), you should tune the query lifecycle in three stages: Metadata Planning, Scan Concurrency, and Data Cache I/O. 1. Metadata Planning Parallelism For Iceberg tables with many manifest files, the "planning" phase can become a bottleneck. You can force StarRocks to use a distributed plan for metadata rather than doing it all on the FE. * 
SET plan_mode = 'distributed';
 Forces the FE to distribute the work of parsing Iceberg manifest files across all BE/CN nodes. This is significantly faster for tables with thousands of manifests. * 
SET enable_iceberg_metadata_cache = true;
 Reuses previously parsed metadata objects. * 
SET enable_get_stats_from_external_metadata = false;
 Skips fetching detailed column statistics (min/max/null counts) from Iceberg if they aren't strictly needed for your query, saving metadata I/O. 2. Execution Parallelism (CPU & Pipeline) StarRocks uses a Pipeline Engine to execute queries. You can increase the "Degree of Parallelism" (DOP) to use more CPU cores for a single query. * 
SET pipeline_dop = <number_of_physical_cores>;
 By default, this is 
0
(adaptive, usually half of CPU cores). For maximum speed on a single query, set it equal to the total number of physical CPU cores on your BE/CN nodes. * 
SET enable_shared_scan = true;
 Allows multiple operators to share the same scan results, reducing redundant work when multiple joins or subqueries access the same Iceberg table. 3. Scan & I/O Concurrency This controls how many concurrent requests are sent to the storage layer (or Data Cache). * 
SET enable_connector_adaptive_io_tasks = false;
 Disables the automatic throttle. * 
SET connector_io_tasks_per_scan_operator = 32;
 Increases the number of concurrent I/O tasks per scan instance (default is 16). Increase this if you have high-performance NVMe disks for your Data Cache. * 
max_hdfs_scanner_num
(BE Config in 
be.conf
): Default is 50. Increase this (e.g., to 100 or 200) to allow more concurrent connector scan tasks to run globally on a BE node. * 
pipeline_connector_scan_thread_num_per_cpu
(BE Config): Default is 8. This multiplies with your CPU count to determine the total thread pool size for scanning external data. 4. Maximizing Data Cache Throughput If you are reading from the Data Cache (local disk), the bottleneck shifts from network latency to local disk I/O and memory bandwidth. * 
SET populate_datacache_mode = 'always';
 Ensures that every block read is immediately cached. * Multiple Cache Disks: Ensure your 
storage_root_path
in 
be.conf
points to multiple NVMe SSDs (separated by commas). StarRocks will strip data across them, naturally increasing parallel I/O throughput. * Example: 
storage_root_path = /nvme1/data, /nvme2/data, /nvme3/data
Recommended "Aggressive" Session Profile Use these settings for a high-parallelism, high-performance "hot" data read:
Copy code
sql
-- Parallelism Settings
SET pipeline_dop = 32; -- Set to your BE core count
SET plan_mode = 'distributed';
SET connector_io_tasks_per_scan_operator = 32;
SET enable_connector_adaptive_io_tasks = false;

-- Cache & Staleness Settings
SET enable_scan_datacache = true;
SET populate_datacache_mode = 'always';
SET enable_iceberg_metadata_cache = true;
SET enable_get_stats_from_external_metadata = false;

-- Catalog Staleness (allows 1-hour stale metadata)
ALTER CATALOG iceberg_catalog SET ("iceberg_table_cache_refresh_interval_sec" = "3600");
Note: Setting 
pipeline_dop
and 
connector_io_tasks
to very high values will make a single query very fast but will consume significantly more CPU and Memory, reducing the number of concurrent users the cluster can support. References • sql-reference/System_variable.md
k
@Rocky I am on 16 r8gd.4xlarge nodes
r
With 16 nodes of r8gd.4xlarge, you have a powerful ARM-based (Graviton4) cluster with a total of 256 vCPUs, 2TB of RAM, and ~15TB of local NVMe SSD. To squeeze every drop of performance for Iceberg reads while accepting stale data, use these specific configurations tailored to your hardware. 1. BE/CN Configuration (
be.conf
or 
cn.conf
) The 
r8gd.4xlarge
has a single 950GB NVMe. You must point StarRocks to this disk for the Data Cache to bypass S3/HDFS.
Copy code
bash
# Point to your NVMe mount point (e.g., /mnt/nvme)
storage_root_path = /mnt/nvme

# Allocate ~85% of the disk to Data Cache (approx 800GB)
datacache_disk_size = 858993459200 

# Increase global scan threads for 16 vCPUs
# Default is 40, increase to handle more parallel I/O tasks
pipeline_scan_thread_pool_thread_num = 64
*Note: Ensure you are using the StarRocks ARM64 (Aarch64) binary for Graviton4 optimization.* 2. Session Variables for Maximum Parallelism Since each node has 16 vCPUs, you want your queries to utilize them fully. Run these before your heavy Iceberg workloads:
Copy code
sql
-- Use all physical threads on the node for one query
SET pipeline_dop = 16; 

-- Drive high I/O concurrency to the local NVMe
SET connector_io_tasks_per_scan_operator = 32;
SET enable_connector_adaptive_io_tasks = false;

-- Distributed metadata planning is faster for large Iceberg tables
SET plan_mode = 'distributed';

-- Always pull data into the NVMe on the first read
SET populate_datacache_mode = 'always';

-- Disable stats to shave off planning time
SET enable_get_stats_from_external_metadata = false;
3. Catalog-Level "Forever Cache" To make stale data acceptable and eliminate Metastore (Glue/HMS) latency, set a very long refresh interval on your catalog.
Copy code
sql
ALTER CATALOG <your_iceberg_catalog> SET (
    -- Cache metadata for 24 hours to ensure zero Metastore hits
    "iceberg_table_cache_refresh_interval_sec" = "86400",
    -- Enable metadata disk cache if using StarRocks 3.3+
    "enable_iceberg_metadata_disk_cache" = "true"
);
Why this works for your hardware: * Pipeline DOP 16: Matches your vCPU count per node. This ensures the CPU is never the bottleneck during complex aggregations on your Iceberg data. * NVMe Throughput: The 
r8gd
NVMe can handle over 250,000 IOPS. Increasing 
connector_io_tasks
to 32 ensures you are keeping the NVMe queue depth high enough to reach its max throughput. * Memory Efficiency: With 128GB per node, you have 8GB per vCPU. This is a "sweet spot" for StarRocks, allowing you to run these high-parallelism settings without hitting OOM (Out of Memory) errors during large shuffles. Pro-Tip: Warm up the cache If performance on the first query is critical, you can manually trigger a cache "warm-up" so the NVMe is pre-loaded:
Copy code
sql
-- Forces a full scan to populate the NVMe cache
SELECT count(*) FROM <your_iceberg_table>;
References • administration/management/BE_configuration.md • sql-reference/System_variable.md • data_source/catalog/iceberg/iceberg_catalog.md
k
@Rocky are there any 4.1 specific settings
r
In StarRocks 4.1, there are several powerful features specifically designed to push Iceberg performance to its limits while allowing you to manage "acceptable staleness" more effectively than in previous versions. 1. Incremental Materialized Views for Iceberg (The "Game Changer") The most significant addition in 4.1 for your use case is support for Incremental Materialized Views (IMV) on Iceberg. * Why it's the fastest: Instead of StarRocks reading and parsing Iceberg files (Avro/Parquet) from S3 every time, IMV pre-computes your query and stores the result in StarRocks' native, high-performance format on your r8gd NVMe disks. * How to use it for staleness: You can set a refresh period (e.g., every 10 minutes) or refresh it manually. This gives you sub-second query performance because you are querying a local StarRocks table that is "eventually consistent" with your Iceberg data.
Copy code
sql
    CREATE MATERIALIZED VIEW iceberg_speed_mv
    REFRESH ASYNC EVERY (INTERVAL 10 MINUTE) -- Define your "acceptable staleness" here
    AS
    SELECT ... FROM iceberg_catalog.db.table;
2. Iceberg Variant Type Support If your data lake has JSON or semi-structured data, 4.1 introduces native support for the Iceberg Variant type. * Performance: Previously, JSON was often stored as a 
STRING
, which required expensive parsing during queries. Variant is a binary format that StarRocks 4.1 processes directly in its vectorized engine. * Action: If you are using Iceberg v3, ensure your semi-structured columns use the 
VARIANT
type for a massive speedup in parallelism and CPU efficiency. 3. Iceberg v3 Row Lineage Columns If you need to track data changes for high-performance incremental processing, 4.1 adds support for Iceberg v3 Row Lineage. * Setting: You can now query virtual columns like 
_row_id
and 
_last_updated_sequence_number
. * Use Case: This allows you to build high-performance "delta" pipelines that only process changed rows, rather than full table scans. 4. Optimized Cache Lookup (CachingIcebergCatalog) In 4.1, the internal 
CachingIcebergCatalog
has been optimized for even lower latency when checking for metadata updates. * Refresh Interval: To maximize this, keep your catalog refresh interval high (as previously suggested) so StarRocks bypasses the Metastore entirely.
Copy code
sql
    ALTER CATALOG iceberg_catalog SET ("iceberg_table_cache_refresh_interval_sec" = "86400"); -- 24 hours
5. Host-Level Sorting for Iceberg Sinks While your question is about reads, StarRocks 4.1 added host-level sorting when writing back to Iceberg. * Impact on Reads: If you use StarRocks to write or "compact" your Iceberg tables, this setting ensures the data is perfectly organized for StarRocks' own scan engine, leading to significantly higher scan parallelism and better min/max pruning on your reads. 6. Caution: Shared Scan In version 4.0/4.1, the 
shared_scan
feature (which reduces redundant scans for multiple queries) was temporarily disabled in some configurations due to a transition to a new event-based scheduler. * Check: If you were using 
SET enable_shared_scan = true;
, monitor your query profiles in 4.1 to see if it provides the expected benefit, as it may be automatically bypassed depending on your specific sub-version. Summary Checklist for 4.1 on r8gd nodes: 1. Use Incremental MVs to turn your S3-based Iceberg data into local SR-format data on your NVMe disks. 2. Enable Variant types if you have semi-structured data. 3. Ensure 
plan_mode = 'distributed'
 to leverage all 16 nodes for metadata processing. 4. Set 
iceberg_table_cache_refresh_interval_sec
to a very high value to lock in your metadata state and prevent S3/Metastore "checks" during queries. References • data_source/catalog/iceberg/iceberg_catalog.md
k
@Rocky is there any way to do inrecmental mv refresh without partitioning? i have dimensional tables that are not queried by date partitioned fields
r
Yes, in StarRocks 4.1, you can perform true incremental refreshes for non-partitioned tables (like dimensions) using the new 
INCREMENTAL
refresh mode. Unlike "Partitioned Refresh" (which requires a date/time column to track changes), the 
INCREMENTAL
mode uses Iceberg's snapshot/metadata features to identify and process only the changed rows. 1. The 
INCREMENTAL
Refresh Mode (StarRocks 4.1+) This is the most direct answer to your question. You can create an MV that stays updated row-by-row or batch-by-batch without needing a 
PARTITION BY
clause in the MV definition. How to set it up:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
REFRESH ASYNC EVERY (INTERVAL 10 MINUTE)
PROPERTIES (
    "refresh_mode" = "INCREMENTAL"
)
AS 
SELECT id, name, category, updated_at
FROM iceberg_catalog.db.dim_table;
Key Requirements for 
INCREMENTAL
mode in 4.1: * Source Table: Currently only supported for Iceberg and Paimon catalogs. * Append-Only: The source Iceberg table must primarily use append-only operations. If StarRocks detects an 
UPDATE
, 
DELETE
, or 
OVERWRITE
on the source table: * If 
refresh_mode
is set to 
INCREMENTAL
, the refresh task will fail. * If 
refresh_mode
is set to 
AUTO
(default), StarRocks will automatically fall back to a full refresh to ensure data consistency. * Supported SQL: It supports 
SELECT
, 
FILTER
, 
INNER JOIN
, and 
AGGREGATE with GROUP BY
. It does not yet support 
OUTER JOINs
in the incremental path. ────────── 2. Handling Dimension Tables in Multi-Table MVs If you are joining a large fact table with these dimension tables, you likely don't want an update to a small dimension table to trigger a massive refresh of the entire MV. You can use 
excluded_trigger_tables
 to decouple the refresh logic:
Copy code
sql
CREATE MATERIALIZED VIEW fact_with_dim_mv
REFRESH ASYNC
PROPERTIES (
    -- Changes to the 'dim_table' will NOT trigger a refresh
    -- Only changes to the 'fact_table' will trigger it
    "excluded_trigger_tables" = "iceberg_catalog.db.dim_table"
)
AS 
SELECT f.*, d.name 
FROM iceberg_catalog.db.fact_table f
JOIN iceberg_catalog.db.dim_table d ON f.dim_id = d.id;
────────── 3. Using "Staleness" for Performance Since you mentioned you accept stale data, you should combine the above with Staleness Rewrite. This allows your queries to hit the MV even if the dimension table has changed slightly in the background, preventing the query from falling back to the slower Iceberg scan.
Copy code
sql
ALTER MATERIALIZED VIEW iceberg_dim_mv SET PROPERTIES (
    "mv_rewrite_staleness_second" = "3600" -- Allow using the MV even if it's 1 hour out of sync
);
Summary Recommendation for 4.1: 1. For Dimension tables: Use 
refresh_mode = 'INCREMENTAL'
. This works without partitioning. 2. For joins: Use 
excluded_trigger_tables
so that "dimension bloat" doesn't force your main fact MVs to re-run from scratch constantly. 3. Check your operators: If your dimension logic uses 
LEFT JOIN
, StarRocks 4.1 will fall back to full refresh. For incremental, stick to 
INNER JOIN
or simple 
SELECT
filters. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky my dimensional tables are not append, they are continous merges
r
Because your dimensional tables are updated via merges (updates/deletes) and are unpartitioned, you hit a specific technical constraint in StarRocks 4.1's Materialized View engine. 1. The "Append-Only" Constraint In StarRocks 4.1, the 
refresh_mode = 'INCREMENTAL'
property for asynchronous MVs currently only supports append-only operations on the source Iceberg table. * Why: Incremental refresh works by tracking new files/snapshots. If a "Merge" happens, it creates row-level deletes or replaces existing data. The current incremental engine cannot yet "re-calculate" just the changed rows in a non-partitioned table when deletes are involved. * Result: If you set 
refresh_mode = 'INCREMENTAL'
, the refresh task will fail when it detects a merge. If set to 
AUTO
, it will automatically fall back to a Full Refresh. 2. Why "Full Refresh" is actually okay for your hardware With 16 r8gd.4xlarge nodes (256 vCPUs), a "Full Refresh" of a dimension table is likely much faster than you expect. StarRocks is exceptionally good at "Full Overwrites" into local disks. For example, a 100-million-row dimension table can often be fully refreshed from Iceberg into a StarRocks MV in under 30 seconds on a cluster of your size. How to optimize the Full Refresh for Dimension Tables:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    -- Allow the MV to be used for 1 hour even if the base table changed
    "mv_rewrite_staleness_second" = "3600",
    -- Ensure the refresh doesn't hog all cluster resources
    "colocate_with" = "group_dim"
)
AS 
SELECT * FROM iceberg_catalog.db.dim_table;
3. Better Alternative: StarRocks Primary Key Tables If your goal is to have a "live" version of your Iceberg dimension table inside StarRocks that handles merges efficiently, you should use a Primary Key (PK) table instead of a Materialized View. Why PK Tables are better for Merges: 1. Native Merge Support: PK tables are designed specifically for continuous 
UPSERT
and 
DELETE
operations. 2. No Full Overwrites: You can sync only the rows that changed in Iceberg (if you have a 
last_updated
column). 3. Memory-Speed Lookups: PK tables use a primary key index that makes dimension joins (shuffles) significantly faster than querying Iceberg directly. How to implement this: 1. Create the PK table in StarRocks:
Copy code
sql
    CREATE TABLE starrocks_dim (
        id BIGINT PRIMARY KEY,
        name STRING,
        updated_at DATETIME
    ) ENGINE=OLAP
    DISTRIBUTED BY HASH(id);
2. Sync from Iceberg (using a simple SQL task or Airflow):
Copy code
sql
    -- This will perform a high-speed upsert of only the latest changes
    INSERT INTO starrocks_dim
    SELECT * FROM iceberg_catalog.db.dim_table
    WHERE updated_at > (SELECT MAX(updated_at) FROM starrocks_dim);
Summary for your 16-node cluster: * Don't use 
refresh_mode = 'INCREMENTAL'
 for these tables; it will fail due to the merges. * Use Full Refresh (AUTO) if the dimension tables are small to medium sized. Your Graviton4 nodes will handle the scan/write with ease. * Use Primary Key tables if you want to sync large dimension tables without doing a full overwrite every time. * Set 
mv_rewrite_staleness_second
 to ensure your queries always hit the fast local data, even while a refresh is happening in the background. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • table_design/table_types/primary_key_table.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References
k
@Rocky what causes this error: Refresh
Copy code
failed after 1 times, try lock failed: 0, error-msg : com.starrocks.sql.common.DmlException: Insert has filtered data, txn_id = 1990789, tracking sql = select tracking_log from information_schema.load_tracking_logs where job_id=4286258
	at com.starrocks.load.InsertOverwriteJobRunner.executeInsert(InsertOverwriteJobRunner.java:404)
	at com.starrocks.load.InsertOverwriteJobRunner.doLoad(InsertOverwriteJobRunner.java:183)
	at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:163)
	at com.starrocks.load.InsertOverwriteJobRunner.transferTo(InsertOverwriteJobRunner.java:229)
	at com.starrocks.load.InsertOverwriteJobRunner.prepare(InsertOverwriteJobRunner.java:282)
	at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:160)
	at com.starrocks.load.InsertOverwriteJobRunner.run(InsertOverwriteJobRunner.java:148)
	at com.starrocks.load.InsertOverwriteJobMgr.executeJob(InsertOverwriteJobMgr.java:84)
	a…
r
This error occurs because the MV refresh task (which uses 
INSERT OVERWRITE
internally) encountered "bad" data that does not fit the schema of your Materialized View. By default, StarRocks operates in Strict Mode for MV refreshes. If even a single row of data is "filtered" (fails to be processed), the entire refresh job is aborted to prevent data corruption or silent loss. 1. How to find the "Bad" Data The error message actually tells you exactly how to find the problem. You need to run the "tracking SQL" provided in your error log (replace the 
job_id
with yours):
Copy code
sql
SELECT tracking_log 
FROM information_schema.load_tracking_logs 
WHERE job_id = 4286258;
Common results you will see in that log: * 
column [name] value is too long
: A string in your source table exceeds the 
VARCHAR
length you defined in the MV. * 
column [name] cannot be null
: Your MV column is defined as 
NOT NULL
, but the source data has a 
NULL
. * 
data quality check failed
: A data type mismatch (e.g., a string that cannot be cast to a number). ────────── 2. Why this happens specifically with Iceberg/Data Lakes Since you are likely refreshing from Iceberg (based on our previous discussion): * Schema Evolution: If the Iceberg table's schema was changed (e.g., a column was added or a type changed) but the MV wasn't recreated, the data types might no longer match. * Precision Mismatch: 
DECIMAL
precision in Iceberg might be higher than what was defined in the StarRocks MV. * Invalid Dates: If your Iceberg data contains "zero-dates" (like 
0000-00-00
) or dates out of the StarRocks range, they will be filtered. ────────── 3. How to fix it Option A: Fix the Schema (Recommended) Once you identify the column from the 
tracking_log
, adjust your MV definition. For example, if a column is too long: 1. Drop the MV. 2. Re-create it with a larger 
VARCHAR
or use 
STRING
. Option B: Allow the refresh to ignore bad data If you don't care about a few rows of bad data and want the refresh to succeed anyway, you can change a global setting (requires admin privileges):
Copy code
sql
-- Set this at the FE level to allow MV refreshes to continue even if rows are filtered
ADMIN SET FRONTEND CONFIG ("mv_refresh_fail_on_filter_data" = "false");
Note: This will simply drop the "bad" rows during the refresh. Option C: Adjust Session Variables for the MV You can also try setting the 
max_filter_ratio
for the specific MV so it has some tolerance:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("session.enable_insert_strict" = "false");
Summary Recommendation: Run the query in Step 1 first. Most often, this is caused by a 
VARCHAR
being too short for a dimensional attribute or a 
NULL
appearing in a column that was inferred as 
NOT NULL
when the MV was first created. References • loading/loading_introduction/troubleshooting_loading.md • administration/management/FE_configuration.md • sql-reference/information_schema/load_tracking_logs.md • loading/InsertInto.md References • loading/loading_introduction/troubleshooting_loading.md • administration/management/FE_configuration.md • sql-reference/information_schema/load_tracking_logs.md • loading/InsertInto.md
k
@Rocky the string is too long, is there a text field or must we set an explicit lenght
r
StarRocks does not have a 
TEXT
or 
LONGTEXT
type like Postgres or MySQL. Instead, you should use 
VARCHAR
 with an explicit length. Here is how the string types work in StarRocks (as of v2.1 through v4.x): 1. 
STRING
vs. 
VARCHAR(n)
 * 
STRING
: This is just an alias for 
VARCHAR(65533)
. If your data exceeds 65,533 bytes (roughly 64KB), the refresh will fail with the "filtered data" error you saw. * 
VARCHAR(M)
: You can explicitly set 
M
up to 1,048,576 (1MB). This is the maximum length for any single string column in StarRocks. 2. How to fix the MV Error Since your MV is likely "inferring" the schema from the base table or defaulting to 65k bytes, you need to explicitly cast that column to a larger size in your 
CREATE MATERIALIZED VIEW
statement:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv_name
REFRESH ASYNC ...
AS
SELECT 
    id,
    -- Explicitly expand the limit to 1MB
    CAST(long_description AS VARCHAR(1048576)) as long_description,
    other_cols
FROM iceberg_catalog.db.dim_table;
Important Technical Details: * Bytes vs. Characters: StarRocks defines 
VARCHAR(n)
in bytes, not characters. In UTF-8, a single character can take up to 4 bytes. If your dimension table contains long strings with special characters (emojis, non-Latin scripts), you must account for that in your length definition. * Memory Impact: While 
VARCHAR
is variable-length and only stores the actual data on disk, StarRocks uses the defined length (
M
) to estimate memory for certain internal operations (like sorting or shuffling). Setting everything to 1MB won't break the cluster, but it is best practice to set it to a realistic maximum (e.g., 
VARCHAR(200000)
) rather than always using the 1MB limit. * Absolute Limit: If you have a single row with a string larger than 1MB, StarRocks cannot store it in a standard column. You would need to store that data as a 
JSON
type (which has a slightly different internal limit) or split it across multiple columns. Summary: If 
STRING
is too short, switch to 
VARCHAR(1048576)
 in your MV definition. References • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/data-types/string-type/STRING.md • faq/table_design_faq.md References • sql-reference/data-types/string-type/VARCHAR.md • sql-reference/data-types/string-type/STRING.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/BINARY.md • faq/table_design_faq.md • knowledge/usage/best-practice.md • sql-reference/data-types/semi-structured/JSON.md References • sql-reference/data-types/string-type/STRING.md • knowledge/usage/best-practice.md • faq/table_design_faq.md • sql-reference/data-types/string-type/VARCHAR.md • faq/Others.md • sql-reference/data-types/string-type/BINARY.md • sql-reference/System_limit.md
k
@Rocky one column is 1565197 long
r
Yes, you have hit the absolute hard limit for the 
VARCHAR
and 
STRING
data types in StarRocks. 1. The 1MB Limit In StarRocks, the maximum size for a single column of type 
VARCHAR
or 
VARBINARY
is 1,048,576 bytes (exactly 1 MB). * 
STRING
 is just an alias for 
VARCHAR(65533)
. * 
VARCHAR(M)
 can be defined up to 
1048576
. * Your data (1,565,197 bytes) is ~1.5 MB, which exceeds this limit. This is why the refresh is failing with "filtered data." 2. The Solution: Use the 
JSON
Data Type The only native data type in StarRocks that supports values larger than 1 MB is 
JSON
, which has a maximum limit of 16 MB. Even if your data is just plain text (not a JSON object), you can store it in a JSON column as a "JSON String." How to update your Materialized View: You need to re-create your Materialized View and cast the long column to the 
JSON
type:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_dim_mv
REFRESH ASYNC ...
AS
SELECT 
    id,
    -- Use TO_JSON to safely wrap the long text into a JSON-compatible format
    TO_JSON(iceberg_column_name) as long_description
FROM iceberg_catalog.db.dim_table;
3. Trade-offs to Consider Using the 
JSON
type for large text comes with specific restrictions: 1. No Joins/Grouping: You cannot use a 
JSON
column in a 
JOIN
condition, 
GROUP BY
, or 
ORDER BY
clause. (Since this is a 1.5MB string, you likely weren't doing this anyway). 2. Function Limits: Most StarRocks string functions (like 
SUBSTRING
, 
REPLACE
, etc.) return a 
VARCHAR
. If you process a 1.5MB JSON value with these functions and the result is still >1MB, the function may return an error or null. 3. Retrieval: When you 
SELECT long_description
, StarRocks will return the data to your client (Python, Java, BI tool) correctly. However, if you try to 
CAST(long_description AS VARCHAR)
, it will be truncated to 1MB or fail. 4. Alternative: Splitting the Column (Not Recommended) If you absolutely must use 
VARCHAR
(e.g., for compatibility with a specific tool that doesn't support JSON), you would have to split the data into two columns in your MV:
Copy code
sql
SELECT 
    LEFT(iceberg_col, 1000000) as part_1,
    SUBSTRING(iceberg_col, 1000001, 1000000) as part_2
Recommendation: Use the 
JSON
 type. It is designed to handle the 16MB payload and will allow your Materialized View refresh to succeed. References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/VARCHAR.md References • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/Json_loading.md • using_starrocks/Flat_json.md • loading/StreamLoad.md
k
@Rocky how to allow filtered rows on a single mv
r
To allow filtered rows for a single Materialized View (MV) without changing the settings for your entire cluster, you can use the 
session.
property prefix. This feature allows you to attach specific session variables directly to the MV's refresh job. 1. Recommended Method (v3.4 and above) You can set the 
max_filter_ratio
or disable 
strict_mode
specifically for one MV. This tells StarRocks to ignore rows that fail due to data quality issues (like the "string too long" error you encountered). If you are creating a new MV:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv_name
REFRESH ASYNC ...
PROPERTIES (
  -- Allow up to 10% of rows to be filtered (0.1) or all of them (1.0)
  "session.max_filter_ratio" = "1.0",
  -- Disable strict mode so bad data is dropped instead of failing the job
  "session.enable_insert_strict" = "false"
)
AS 
SELECT ... ;
If the MV already exists:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
  "session.max_filter_ratio" = "1.0",
  "session.enable_insert_strict" = "false"
);
2. Global FE Configuration (v3.3 and below) If you are on an older version or want to apply this change to all MVs in the cluster, you must change the global Frontend (FE) configuration. This is often necessary in v3.5+ where strict mode is enabled by default for all MV refreshes.
Copy code
sql
ADMIN SET FRONTEND CONFIG ("mv_refresh_fail_on_filter_data" = "false");
* True (default): The MV refresh fails immediately if any row is filtered. * False: The MV refresh succeeds, but the "bad" rows are simply excluded from the MV. Which setting should I use? * 
session.enable_insert_strict
= 
false
: This is usually enough. It tells StarRocks to skip rows that don't match the schema instead of throwing a 
DmlException
. * 
session.max_filter_ratio
: Use this if you want to set a threshold (e.g., "Allow the refresh to succeed as long as less than 5% of rows are bad"). Setting it to 
1.0
allows the job to succeed even if every single row is filtered (not recommended). Warning: Allowing filtered rows means your Materialized View will contain incomplete data. If you have 1.5MB strings and you allow filtering, those specific rows will simply be missing from your MV. Since you already know the cause is a long string, the better long-term fix is usually to use the 
JSON
data type as discussed previously. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • administration/management/FE_configuration.md • sql-reference/sql-statements/loading_unloading/INSERT.md References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md • administration/management/FE_configuration.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky Refresh mv failed after 1 times, try lock failed: 0, error-msg : com.starrocks.common.DdlException: Memory of process exceed limit. read and decompress page Backend: starrocks-cn-13.starrocks-cn-search.starrocks.svc.cluster.local, Used: 100902300648, Limit: 100888781782. Mem usage has exceed the limit of BE: file = s3://5209d431-ee84-4eeb-6uhracgq8raezbkqd17d5g96rb5tcuse1b--table-s3/data/video_id_partition=4/00024-5192-8c8a7495-e4f0-4e32-a975-63d815564ade-0-00001.parquet: BE:3698073 at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:100) at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3355) [wrapped] com.starrocks.common.StarRocksException: Memory of process exceed limit. read and decompress page Backend: starrocks-cn-13.starrocks-cn-search.starrocks.svc.cluster.local, Used: 100902300648, Limit: 100888781782. Mem usage has exceed the limit of BE: file = s3://5209d431-ee84-4eeb-6uhracgq8raezbkqd17d5g96rb5tcuse1b--table-s3/data/video_id_partition=4/00024-5192-8c8a7495-e4f0-4e32-a975-63d815564ade-0-00001.parquet: BE:3698073 at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3606) at com.starrocks.load.InsertOverwriteJobRunner.executeInsert(InsertOverwriteJobRunner.java:399) at com.starrocks.load.InsertOverwriteJobRunner.doLoad(InsertOverwriteJobRunner.java:183) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:163) at com.starrocks.load.InsertOverwriteJobRunner.transferTo(InsertOverwriteJobRunner.java:229) at com.starrocks.load.InsertOverwriteJobRunner.prepare(InsertOverwriteJobRunner.java:282) at com.starrocks.load.InsertOverwriteJobRunner.handle(InsertOverwriteJobRunner.java:160) at com.starrocks.load.InsertOverwriteJobRunner.run(InsertOverwriteJobRunner.java:148) at com.starrocks.load.InsertOverwriteJobMgr.executeJob(InsertOverwriteJobMgr.java:84) at com.starrocks.qe.StmtExecutor.handleInsertOverwrite(StmtExecutor.java:3058) at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:3200) at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:3067) at com.starrocks.scheduler.MVTaskRunProcessor.executePlan(MVTaskRunProcessor.java:359) at com.starrocks.scheduler.mv.pct.MVPCTRefreshProcessor.execProcessExecPlan(MVPCTRefreshProcessor.java:172) at com.starrocks.scheduler.MVTaskRunProcessor.doProcessTaskRun(MVTaskRunProcessor.java:326) at com.starrocks.scheduler.MVTaskRunProcessor.retryProcessTaskRun(MVTaskRunProcessor.java:283) at com.starrocks.scheduler.MVTaskRunProcessor.processTaskRun(MVTaskRunProcessor.java:195) at com.starrocks.scheduler.TaskRun.doExecuteTaskRun(TaskRun.java:464) at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:424) at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$1(TaskRunExecutor.java:66) at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840)
r
The error message 
Memory of process exceed limit. read and decompress page
indicates that the StarRocks Backend (BE) process ran out of memory while trying to decompress data from your S3 Parquet file. Because you have 1.5 MB strings, StarRocks is likely trying to load too much data into memory at once. By default, StarRocks processes data in "batches" (usually 4096 rows). If each row has a 1.5 MB string, a single batch requires ~6 GB of memory. With multiple parallel threads, you will hit the 100 GB BE limit very quickly. 1. Reduce the Batch Size This is the most effective fix for your scenario. Reducing the 
batch_size
forces StarRocks to process fewer rows at a time, significantly lowering the memory footprint per thread.
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
  "session.batch_size" = "128" -- Start low (e.g., 128 or 256)
);
2. Limit Parallelism If your cluster has many CPU cores, it will try to run many scan tasks in parallel. You can throttle this for just this Materialized View:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
  "session.pipeline_dop" = "2", -- Limits the degree of parallelism
  "session.parallel_fragment_exec_instance_num" = "1"
);
3. Enable Spilling From v3.1 onwards, you can allow StarRocks to "spill" intermediate data to disk if it runs out of memory. This is particularly helpful for MVs involving joins or aggregations:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
  "session.enable_spill" = "true"
);
4. Partition the Materialized View If your base table in Iceberg/S3 is large, refreshing the entire MV at once is risky. You should partition the MV so that it only refreshes one partition at a time (e.g., by day or by the 
video_id_partition
mentioned in your error). Example of a partitioned MV:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv_name
PARTITION BY (partition_col)
REFRESH ASYNC
AS 
SELECT ... FROM base_table;
Summary of Recommended Fix Apply these properties to your MV to make the refresh process "leaner":
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
  "session.batch_size" = "128",
  "session.pipeline_dop" = "4",
  "session.enable_spill" = "true"
);
Note: If you haven't already, ensure you changed the column type to 
JSON
 in the MV definition, as 
VARCHAR
will still fail with the "string too long" error once you solve the memory issue. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/System_variable.md • administration/management/BE_configuration.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/BE_configuration.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md • faq/Sql_faq.md • administration/management/resource_management/resource_group.md • using_starrocks/async_mv/materialized_view_task_run_details.md • administration/management/monitoring/alert.md References • faq/Sql_faq.md
k
@Rocky provide way to dynamically create ddl from iceberg table for primary key table
r
To dynamically create a StarRocks Primary Key table DDL from an Apache Iceberg table, you have two main approaches: using the CTAS (Create Table As Select) syntax for one-click creation, or a Metadata Query to generate the SQL script string. Method 1: Using CTAS (Recommended) This is the most "dynamic" way because StarRocks automatically maps the data types from Iceberg to the internal StarRocks engine. You only need to explicitly define the Primary Key and Bucket Key.
Copy code
sql
CREATE TABLE sr_internal_table 
PRIMARY KEY (id)                 -- Specify your PK columns here
DISTRIBUTED BY HASH (id)        -- Specify your Bucket key here
AS 
SELECT * FROM iceberg_catalog.db_name.iceberg_table_name;
* Pros: Automatically handles type conversion (e.g., Iceberg 
string
-> SR 
varchar
). * Cons: Executes immediately. If you only want the DDL without data, add 
WHERE 1=0
at the end. ────────── Method 2: Generate DDL via 
information_schema
 If you need a script to generate the DDL string for many tables dynamically (e.g., in a migration script), you can query the StarRocks metadata. Run this query (replacing the table and catalog names) to generate a 
CREATE TABLE
statement:
Copy code
sql
SELECT 
    CONCAT(
        'CREATE TABLE ', table_name, '_pk (\n',
        GROUP_CONCAT(CONCAT('  ', column_name, ' ', data_type, 
            CASE WHEN is_nullable = 'NO' THEN ' NOT NULL' ELSE '' END) 
            ORDER BY ordinal_position SEPARATOR ',\n'),
        ',\n  PRIMARY KEY (', 
        (SELECT column_name FROM information_schema.columns 
         WHERE table_name = 'your_iceberg_table' AND ordinal_position = 1), -- Defaults to 1st col
        ')\n) DISTRIBUTED BY HASH (',
        (SELECT column_name FROM information_schema.columns 
         WHERE table_name = 'your_iceberg_table' AND ordinal_position = 1),
        ');'
    ) AS ddl
FROM information_schema.columns
WHERE table_catalog = 'iceberg_catalog' 
  AND table_schema = 'db_name'
  AND table_name = 'iceberg_table_name'
GROUP BY table_name;
────────── Method 3: Using Materialized Views (Sync Approach) If your goal is to keep the StarRocks table "synced" with the Iceberg table, creating an Asynchronous Materialized View is often better than a static Primary Key table. It acts like an internal table but handles the "dynamic" DDL and data refresh automatically.
Copy code
sql
CREATE MATERIALIZED VIEW sr_mv_sync
PRIMARY KEY (id)
DISTRIBUTED BY HASH (id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Or MANUAL
AS 
SELECT * FROM iceberg_catalog.db_name.iceberg_table_name;
Key Mapping Tips: * Data Types: StarRocks 
STRING
columns are converted to 
VARCHAR(65533)
by default in CTAS. If your Iceberg data exceeds 1MB (as in your previous error), you must manually change the DDL to use the 
JSON
type for those specific columns. * Partitioning: If your Iceberg table is partitioned, you should add 
PARTITION BY
to your StarRocks DDL to match the Iceberg partition columns for better performance. * Persistent Index: For Primary Key tables, always add 
"enable_persistent_index" = "true"
in the 
PROPERTIES
to save memory if the table is large. References • sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE_AS_SELECT.md • using_starrocks/async_mv/Materialized_view.md • table_design/table_types/primary_key_table.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
k
@Rocky Rewrite projection failed: cannot totally rewrite expr 6: api_attachments whati sthe cause of this
r
This error occurs during Materialized View (MV) Query Rewrite. It means the StarRocks optimizer tried to replace the base tables in your query with your Materialized View but failed because it couldn't find a way to map the expression 
api_attachments
from your query to a column available in the MV. Common Causes 1. Column Missing from MV: The column 
api_attachments
is used in your query (in the 
SELECT
, 
WHERE
, or 
JOIN
clause), but it was not included in the 
SELECT
list when you created the Materialized View. 2. Expression Mismatch: Your query might be performing a function on the column (e.g., 
JSON_EXTRACT(api_attachments, '$.id')
or 
LENGTH(api_attachments)
), but the MV only stores the raw column. While StarRocks can sometimes derive expressions, complex transformations often require the exact expression to be present in the MV definition. 3. Alias Ambiguity: If your query or MV definition uses aliases, the optimizer might be failing to resolve the reference. 4. Data Type Issues: Given your previous context regarding 1.5 MB strings, if 
api_attachments
is a very large 
JSON
or 
VARCHAR
column, the optimizer may struggle to rewrite it if the types don't match perfectly between the Iceberg source and the MV definition. ────────── How to Diagnose You can get a specific reason for the failure by running the 
TRACE
command on your query:
Copy code
sql
-- This will tell you exactly why the rewrite for 'api_attachments' failed
TRACE REASON MV <YOUR_QUERY_HERE>;
If you need even more detail (available in v3.2+):
Copy code
sql
TRACE LOGS MV <YOUR_QUERY_HERE>;
How to Fix * Add the column to the MV: Ensure 
api_attachments
is explicitly selected in your 
CREATE MATERIALIZED VIEW
statement.
Copy code
sql
    CREATE MATERIALIZED VIEW your_mv ... AS
    SELECT
      id,
      api_attachments, -- Make sure this is here!
      ...
    FROM base_table;
* Match expressions exactly: If your query uses 
lower(api_attachments)
, try adding 
lower(api_attachments)
as a column in the MV itself. * Check for Nullability: Sometimes a rewrite fails because the base table column is nullable but the MV column is inferred as not-null (or vice-versa). Using 
IFNULL()
in the MV definition can sometimes bridge this gap. Note: If you add 
api_attachments
to the MV and start seeing the "Memory exceed limit" error again, refer to the previous advice about reducing 
batch_size
and 
pipeline_dop
, as large strings in the projection consume significant memory during the moment they are loaded into the MV. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
k
@Rocky my iceberg string 
api_attachments
column was too large so i casted to json, and query rewrite is failing. how do i still use the mv
r
This rewrite failure happens because the StarRocks optimizer requires a perfect match between the expressions in your query and the expressions in your Materialized View (MV). When your query asks for 
api_attachments
(a STRING), but the MV only contains 
CAST(api_attachments AS JSON)
(a JSON type), the types do not match. The optimizer cannot automatically "un-cast" the JSON back to a String to satisfy the query. To fix this and still benefit from the MV, you have three options: 1. Update the Query to match the MV (Easiest) If you change your query to include the same 
CAST
used in the MV, the optimizer will recognize the expression and rewrite it successfully. * MV Definition: 
SELECT CAST(api_attachments AS JSON) AS api_attachments ...
* Change your Query to:
Copy code
sql
    SELECT CAST(api_attachments AS JSON) FROM iceberg_table WHERE ...
2. Use a "View-Based Rewrite" (Recommended for v3.3+) If you cannot change your application's queries, you can use a Logical View as a middle layer. StarRocks v3.3+ is optimized to handle rewrites through views. 1. Create a Logical View that performs the cast:
Copy code
sql
    CREATE VIEW iceberg_table_view AS
    SELECT *, CAST(api_attachments AS JSON) AS api_attachments_json
    FROM iceberg_catalog.db.iceberg_table;
2. Create the MV on the View:
Copy code
sql
    CREATE MATERIALIZED VIEW mv_accelerator
    REFRESH ASYNC AS
    SELECT api_attachments_json, ... FROM iceberg_table_view;
3. Query the View: Queries against 
iceberg_table_view
will now be transparently rewritten to the MV. 3. Include both versions in the MV (Memory Intensive) If some rows have small strings and some have large strings, you could try to include the raw column and the JSON column. However, as you've seen, the raw 
STRING
(VARCHAR) will likely cause the "Memory exceed limit" or "String too long" error if any single value exceeds 1MB. Why JSON is the right choice here: In StarRocks, 
VARCHAR
is limited to 1MB. If your Iceberg strings are 1.5MB, they must be stored as 
JSON
(which supports up to 16MB) or they will fail to load into an internal StarRocks table or MV. How to verify the fix After making the change, use the 
TRACE
command to confirm it's working:
Copy code
sql
TRACE REASON MV SELECT ... FROM iceberg_table;
If successful, you will see 
SCAN [mv_name]
in the logical plan. Important: Memory Tuning Since you are dealing with 1.5MB+ JSON blobs, ensure your MV has the memory protections we discussed earlier:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv SET (
  "session.batch_size" = "128", 
  "session.pipeline_dop" = "2"
);
References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/data-types/semi_structured/JSON.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-functions/json-functions/json-query-and-processing-functions/cast.md • using_starrocks/Flat_json.md
k
@Rocky is there no way to loosen the mv plan to make it use it without the view or chanign the query
r
In StarRocks, query rewrite is primarily structure-based. This means the optimizer compares the expression tree of your query to the expression tree of the Materialized View (MV). Because your MV stores 
CAST(api_attachments AS JSON)
and your query asks for 
api_attachments
(the raw 
STRING
), the optimizer sees two different data types and expression structures. Currently, there is no session variable to "loosen" this specific type-matching requirement to make it transparently swap a String for a JSON column. However, you can achieve your goal using one of these two "least-intrusive" methods: 1. Alias Shadowing (Most likely to work) If you name the column in your MV exactly the same as the column in the Iceberg table, the optimizer sometimes has a better chance of matching, but it still often requires the query to use the same expression. Try defining your MV like this:
Copy code
sql
CREATE MATERIALIZED VIEW mv_name 
REFRESH ASYNC 
AS 
SELECT 
  id, 
  CAST(api_attachments AS JSON) AS api_attachments -- Alias it exactly like the base column
FROM iceberg_catalog.db.table;
Even with this, if the query is 
SELECT api_attachments
, the optimizer sees 
STRING
(Query) vs 
JSON
(MV) and will likely fail. 2. The "Hidden" View Approach (Recommended) You mentioned wanting to avoid a view, but a Logical View is the only way to "loosen" the plan without changing your application's SQL. This is the standard pattern for handling type mismatches in StarRocks: 1. Rename your Iceberg table reference in your mind. 2. Create a Logical View with the same name as the table your app expects to query:
Copy code
sql
    -- If your app queries `iceberg_db.api_table`
    CREATE VIEW iceberg_db.api_table_speedup AS
    SELECT *, CAST(api_attachments AS JSON) AS api_attachments
    FROM iceberg_catalog.iceberg_db.api_table;
3. Point the MV at this View:
Copy code
sql
    SET enable_view_based_mv_rewrite = true; -- Enable v3.3+ view rewrite
    CREATE MATERIALIZED VIEW mv_speedup AS
    SELECT * FROM iceberg_db.api_table_speedup;
4. Result: When you query the view, StarRocks sees the 
CAST
in the view, matches it to the 
CAST
in the MV, and rewrites it perfectly. Why you can't just "loosen" it * Semantic Risk: 
JSON
and 
STRING
behave differently in StarRocks (e.g., sorting, comparison, and memory handling). The optimizer cannot assume that a user who asked for a 
STRING
will be happy receiving a 
JSON
object (which might be formatted differently or escaped). * The 1MB Limit: Since StarRocks 
VARCHAR
cannot exceed 1MB, the engine cannot simply cast the 
JSON
back to 
STRING
during the rewrite if the data is 1.5MB, as the query would then crash with the "String too long" error you saw earlier. Summary of what won't work: * `query_rewrite_consistency = 'LOOSE'`: This only affects data freshness (allowing stale data), not expression matching. * `enable_materialized_view_text_match_rewrite`: This requires the text of the query to match the MV. Since your query doesn't have the 
CAST
, the text won't match. Recommendation: If you absolutely cannot change the query or add a view, the only other option is to use an Internal Primary Key table (Method 1 in the previous answer) and sync the data into it. In an internal table, you can define the column as 
JSON
from the start, and StarRocks will handle the ingestion. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/System_variable.md References
k
@Rocky the mv is already aliasing: AS SELECT * EXCLUDE (api_attachments), -- Select everything except the long column CAST(api_attachments AS JSON) AS api_attachments -- Add it back as a JSON type
r
In StarRocks, there is no session variable or setting to "loosen" the query rewriter to ignore the data type mismatch between a 
STRING
and a 
JSON
column. The error 
cannot totally rewrite expr 6: api_attachments
is happening because of a fundamental conflict in how StarRocks handles large data: 1. The 1MB Limit: StarRocks 
VARCHAR
(and the 
STRING
alias) has a hard limit of 1,048,576 bytes (1MB). Since your data is 1.5MB, StarRocks cannot store it as a String in an MV. 2. The JSON Solution: 
JSON
columns support up to 16MB. Casting to 
JSON
is the correct technical solution to store your 1.5MB strings. 3. The Rewriter Block: The optimizer sees your query asking for a 
STRING
but your MV providing a 
JSON
object. Because these types have different memory layouts and serialization rules, the optimizer refuses to swap them transparently. Is there any way to fix this without changing the query or using a view? Technically, no. If you cannot change the SQL query and you cannot use a Logical View, the rewriter will continue to fail because it cannot find a way to "cast back" the JSON to a String without hitting that 1MB limit again. The "Least Intrusive" Workaround: Alias Shadowing (v3.3+) If you are on v3.3 or higher, you can try to leverage Text-Based Rewrite. This is slightly more flexible than structural rewrite: 1. Set this variable:
Copy code
sql
    SET enable_materialized_view_text_match_rewrite = true;
2. Modify the MV to use a CTE or Subquery that exactly matches the text of your query, but use the 
CAST
inside it. Note: This is often unreliable if the types don't match, but it's the only "looser" matching logic available. Why the Logical View is actually the best "Loosening" tool If you create a Logical View in a StarRocks database that points to the Iceberg table and performs the 
CAST
, you are essentially "telling" the optimizer: "From now on, treat this column as JSON." 1. Create the View:
Copy code
sql
    CREATE VIEW sr_db.api_data AS
    SELECT *, CAST(api_attachments AS JSON) AS api_attachments
    FROM iceberg_catalog.iceberg_db.api_table;
2. Point the MV at the View. 3. Point your App at the View. This is the only way to satisfy the optimizer's requirement for type-safety while bypassing the 1MB VARCHAR limit. Summary of why "Loosening" doesn't exist for this: If StarRocks allowed you to "loosen" this and it successfully rewrote the query, the query would likely crash immediately after the rewrite. The engine would try to output the 1.5MB JSON value as a 
STRING
(to satisfy your original query), hit the 1,048,576-byte limit, and throw a 
String/VARCHAR is too long
error. References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • sql-reference/System_limit.md • sql-reference/data-types/string-type/VARCHAR.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • knowledge/usage/best-practice.md • sql-reference/data-types/string-type/VARCHAR.md • faq/table_design_faq.md • sql-reference/System_limit.md • faq/Others.md • sql-reference/data-types/string-type/STRING.md
k
@Rocky how to fix this: query rewrite is not supported for refresh_mode=INCREMENTAL
r
The error 
query rewrite is not supported for refresh_mode=INCREMENTAL
occurs because the "Incremental Refresh" mode (introduced recently to use a specialized incremental computation engine) is currently a dedicated refresh path that has not yet been integrated with the Materialized View (MV) Query Rewrite optimizer. To fix this while still maintaining incremental updates, you should use the Partition-based Incremental Refresh method, which is the standard, high-performance path in StarRocks that fully supports query rewrite. The Fix: Switch to Partition-based Refresh 1. Drop your current Materialized View. 2. Recreate it without the 
"refresh_mode" = "INCREMENTAL"
property. 3. Ensure you use 
PARTITION BY
on a column that matches your base table (e.g., the 
dt
column from your Iceberg table). Example of the correct definition:
Copy code
sql
CREATE MATERIALIZED VIEW mv_name
-- 1. Use PARTITION BY to enable partition-level incremental updates
PARTITION BY (dt) 
DISTRIBUTED BY HASH(id)
-- 2. Set to ASYNC or MANUAL refresh
REFRESH ASYNC 
PROPERTIES (
    -- 3. DO NOT set refresh_mode = INCREMENTAL here
    "partition_refresh_number" = "1"
)
AS 
SELECT 
    id, 
    dt, 
    CAST(api_attachments AS JSON) AS api_attachments
FROM iceberg_catalog.db.table;
Why this is better * Query Rewrite: The StarRocks optimizer is specifically built to handle MVs created this way. It will transparently rewrite your queries to use the MV. * Incremental Efficiency: Even without the "INCREMENTAL" keyword, StarRocks performs Partition-level Incremental Refresh. When data changes in your Iceberg table, StarRocks detects which partitions changed and only refreshes those specific partitions in the MV. * Compatibility: This is the most stable and feature-complete way to accelerate data lake queries in StarRocks v3.x. If you are on v3.3+ and still see rewrite failures: Ensure that you have not disabled the rewrite session variables. You can check or set them with:
Copy code
sql
SET enable_materialized_view_rewrite = true;
-- If your MV is built on a view:
SET enable_view_based_mv_rewrite = true;
References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky what is the default connector worker count? 8? how to adjust this based on cluster size?
r
The number 8 you are seeing refers to the default value of the BE configuration parameter 
pipeline_connector_scan_thread_num_per_cpu
. In StarRocks, connector (external table) scanning performance is governed by a few key parameters that scale automatically with your cluster's CPU resources, but can be manually tuned. 1. The Core Variable: 
pipeline_connector_scan_thread_num_per_cpu
 * Default: 
8
* What it does: This is a BE configuration that defines how many scan threads are assigned to the Pipeline Connector per CPU core on each BE node. * Total threads per BE: 
8 * [Number of CPU Cores]
. * Adjustment: If you have a cluster with very high-core BEs and you are hitting I/O limits on your object store (S3/HDFS), you might lower this. If your BEs are under-utilized during external queries, you can increase it. 2. The Concurrency Formula When querying external catalogs (Iceberg, Hive, etc.), the overall "worker" concurrency is roughly calculated as: 
pipeline_dop
_ 
connector_io_tasks_per_scan_operator
_ 
[Number of BE Nodes]
 To adjust this based on cluster size or specific query needs, use these session variables: * 
pipeline_dop
: This controls the parallelism of a single query fragment. By default, it is set to 
0
(which means it equals the number of CPU cores). For high-concurrency small queries, you might lower this to 
4
or 
8
to prevent a single query from exhausting the cluster. * 
connector_io_tasks_per_scan_operator
: * Default: 
16
* What it does: The maximum concurrent I/O tasks issued by a single scan operator. * Adjustment: If you are seeing "Slow I/O" in your query profile but have plenty of CPU, increase this to 
32
or 
64
. 3. How to adjust for Cluster Size * For Vertical Scaling (More Cores per BE): StarRocks handles this automatically because 
pipeline_connector_scan_thread_num_per_cpu
and 
pipeline_dop
(if set to 0) scale with cores. * For Horizontal Scaling (More BE Nodes): The total cluster-wide concurrency scales linearly. If you add more BEs and find your external storage (like an HDFS NameNode or S3 rate limit) is struggling, you should decrease 
connector_io_tasks_per_scan_operator
globally to avoid overwhelming the source. Summary of Tuning Commands: To lower the pressure on an external system (at the cost of query speed):
Copy code
sql
SET GLOBAL enable_connector_adaptive_io_tasks = false;
SET GLOBAL connector_io_tasks_per_scan_operator = 8;
SET GLOBAL pipeline_dop = 8;
To maximize throughput for a single large query (on a powerful cluster):
Copy code
sql
SET connector_io_tasks_per_scan_operator = 32;
SET pipeline_dop = 0; -- Use all cores
References • administration/management/BE_configuration.md • sql-reference/System_variable.md • knowledge/usage/best-practice.md References • loading/Kafka-connector-starrocks.md • sql-reference/System_variable.md • administration/management/BE_configuration.md • knowledge/usage/best-practice.md
k
@Rocky explain issue with many parallel requets for s3 iceberg manifest files and scan connector threads and throttling in starrocks
r
When querying Iceberg tables on S3, StarRocks uses a high-concurrency model that can inadvertently trigger S3 Throttling (503 Slow Down). This happens because S3 has hard limits on request rates (3,500 PUT/POST/DELETE and 5,500 GET/HEAD requests per second per prefix). The issue arises from the intersection of three factors: 1. The Concurrency "Multiplier" Effect StarRocks calculates scan concurrency using this formula: 
pipeline_dop
× 
connector_io_tasks_per_scan_operator
× 
Number of BE Nodes
 * 
pipeline_dop
: Default is usually your CPU core count (e.g., 16 or 32). * 
connector_io_tasks_per_scan_operator
: Default is 16. * Multiplier: On a 10-node cluster with 16 cores, you could have $16 \times 16 \times 10 = 2,560$ concurrent I/O requests for a single query. * The Result: A few parallel queries can easily exceed the 5,500 GET requests/sec S3 limit. 2. The Iceberg "Manifest Storm" Iceberg tables consist of many small Manifest Files and Manifest Lists. * Distributed Planning: In newer StarRocks versions, the work of reading these manifest files is distributed across BE nodes to speed up query planning. * Throttling at the Metadata Level: Before a single row of data is read, StarRocks may issue thousands of GET requests to S3 just to fetch the manifest files to determine which data files to scan. Because these metadata files often share the same S3 prefix (e.g., `s3://bucket/table/metadata/`), they hit the same S3 partition limit simultaneously. 3. Connector Scan Threads vs. I/O Tasks The BE parameter 
pipeline_connector_scan_thread_num_per_cpu
(default 8) defines the number of worker threads available to process the data. However, the number of outstanding network requests to S3 is governed by 
connector_io_tasks_per_scan_operator
. * If you have many scan threads but the S3 storage is throttling, those threads will sit idle waiting for I/O, leading to high query latency and thread pool exhaustion on the BE. How to Fix and Mitigate A. Limit Global Concurrency (The "Safety Valve") If you are seeing 503 errors, reduce the aggressiveness of the connector:
Copy code
sql
-- Lower the I/O concurrency per operator (Default is 16)
SET GLOBAL connector_io_tasks_per_scan_operator = 4;

-- Turn off adaptive I/O if it's scaling too aggressively
SET GLOBAL enable_connector_adaptive_io_tasks = false;

-- Limit the parallelism of a single query
SET GLOBAL pipeline_dop = 8;
B. Enable Metadata Caching Caching manifests on the FE and BE prevents StarRocks from hitting S3 for every query: * Memory/Disk Cache: Ensure 
enable_iceberg_metadata_disk_cache
is 
true
in your FE configuration. * Refresh Strategy: Use 
enable_background_refresh_connector_metadata
to let StarRocks refresh metadata in the background at a controlled rate, rather than "bursting" requests during query time. C. Tune the BE Scan Pool If you have a very large cluster (e.g., 100+ BEs), the default 
pipeline_connector_scan_thread_num_per_cpu = 8
might be too high for external catalogs. You can lower this in 
be.conf
to reduce the total "pressure" the cluster can exert on S3:
Copy code
properties
pipeline_connector_scan_thread_num_per_cpu = 4
Summary of Symptoms * Profile: You see high "ScanTime" or "OpenFilesTime" but very low CPU usage. * Logs: BE or FE logs show 
com.amazonaws.services.s3.model.AmazonS3Exception: Slow Down (Service: Amazon S3; Status Code: 503; Error Code: 503 Slow Down)
. * Behavior: Queries on small Iceberg tables are fast, but queries on tables with thousands of partitions/manifests fail or hang. References • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/System_variable.md • administration/management/BE_configuration.md
k
@Rocky this is on s3 table buckets, which has higher limits
r
Even though S3 Table Buckets provide significantly higher request limits (up to 55,000 GET requests/sec and 35,000 PUT requests/sec) compared to standard S3 buckets (5,500/3,500), StarRocks' Massively Parallel Processing (MPP) architecture can still trigger throttling. This happens because StarRocks scales its I/O requests linearly across every core of every BE node in your cluster. Why Throttling Still Occurs The "manifest storm" is the primary culprit. When you query an Iceberg table, StarRocks must first scan the manifest files to locate the data. * The Concurrency Formula: Each query fragment uses a concurrency of: 
pipeline_dop
× 
connector_io_tasks_per_scan_operator
× 
Number of BE Nodes
. * The Burst: If you have a 20-node cluster and a high 
pipeline_dop
, a single query can instantly fire over 10,000 metadata requests to the Glue REST Catalog and S3 Table storage. * REST API Limits: While the S3 data plane is fast, the AWS Glue Iceberg REST endpoint (which handles the table metadata) has its own transaction-per-second (TPS) limits that are often lower than the S3 bucket itself. ────────── How to Tune StarRocks for S3 Tables 1. Limit the "Blast Radius" of Parallel Scans If you are hitting 503 "Slow Down" errors, you should reduce the aggressiveness of the connector. These settings are most effective when set globally or at the session level:
Copy code
sql
-- Lower the concurrent I/O tasks per scan operator (Default: 16)
SET GLOBAL connector_io_tasks_per_scan_operator = 4;

-- Disable adaptive I/O tasks to keep concurrency predictable
SET GLOBAL enable_connector_adaptive_io_tasks = false;

-- Limit the parallelism of a single query (Default: 0, which uses all cores)
SET GLOBAL pipeline_dop = 8;
2. Optimize Metadata Retrieval StarRocks v3.3+ introduced an Adaptive Metadata Retrieval strategy. You can force StarRocks to use a more "S3-friendly" approach for metadata: * 
plan_mode
: Set this to 
local
to have the FE handle metadata more conservatively, or 
distributed
if you want the BEs to scan manifest files in parallel (but with the lower concurrency limits above).
Copy code
sql
    SET plan_mode = 'local'; -- Reduces the "storm" of requests from multiple nodes
* Metadata Caching: Ensure FE caching is enabled so that repeated queries don't hit S3/Glue for the same manifest files.
Copy code
properties
    # In fe.conf
    enable_iceberg_metadata_disk_cache = true
3. Adjust the BE Thread Pool The BE parameter 
pipeline_connector_scan_thread_num_per_cpu
(default 8) determines how many threads per core are dedicated to external scans. In very large clusters (100+ BEs), you should consider lowering this to avoid overwhelming the S3 endpoint. * In `be.conf`: 
pipeline_connector_scan_thread_num_per_cpu = 4
Summary Recommendation S3 Table Buckets solve the "data file" throttling problem for most users, but the Iceberg Manifest/Metadata phase remains a bottleneck because it is a burst of thousands of tiny GET requests. *Start by setting `connector_io_tasks_per_scan_operator = 4`*—this is the most effective way to "smooth out" the request spikes without sacrificing significant query throughput. References • sql-reference/System_variable.md • data_source/catalog/iceberg/iceberg_catalog.md • administration/management/BE_configuration.md • knowledge/usage/best-practice.md References • data_source/catalog/iceberg/iceberg_catalog.md • knowledge/usage/best-practice.md
k
@Rocky give example aync partitioned mv based on month for external iceberg table that is also partitioned by month
r
To create an asynchronous partitioned Materialized View (MV) in StarRocks based on an Iceberg table partitioned by month, you must use the 
date_trunc('month', col)
expression in the MV's 
PARTITION BY
clause to align with Iceberg's 
month()
transform. Example Scenario * Iceberg Table: 
iceberg_catalog.db.orders
* Iceberg Partition: 
month(order_date)
* Goal: Create a StarRocks MV that inherits this monthly partitioning for incremental refreshes. SQL Example
Copy code
sql
CREATE MATERIALIZED VIEW mv_monthly_orders
-- Align with Iceberg's month(order_date) transform
PARTITION BY date_trunc('month', order_date)
-- Distributed by a high-cardinality column for performance
DISTRIBUTED BY HASH(order_id)
-- Refresh automatically when base table data changes
REFRESH ASYNC
PROPERTIES (
    -- Limit concurrency: refresh only 1 month at a time to save resources
    "partition_refresh_number" = "1",
    -- Optional: Keep only the last 24 months of data in the MV
    "partition_ttl_number" = "24"
)
AS
SELECT 
    order_id,
    order_date,
    customer_id,
    amount
FROM iceberg_catalog.db.orders;
Key Implementation Details 1. Partition Mapping: * If your Iceberg table uses 
month(order_date)
, your StarRocks MV must use 
date_trunc('month', order_date)
. * This "Partition Alignment" allows StarRocks to track which specific months have changed in the Iceberg catalog and refresh only those corresponding partitions in the MV (Incremental Refresh). 2. Refresh Strategy: * `REFRESH ASYNC`: The MV will detect data changes in the Iceberg table (via snapshot IDs/manifests) and trigger a refresh. * `partition_refresh_number`: For external tables, it is often safer to set this to 
1
or 
2
. This ensures that if you backfill 12 months of data in Iceberg, StarRocks won't try to refresh all 12 months in one massive, resource-heavy transaction. 3. Performance Tip (Bucketing): * Always include 
DISTRIBUTED BY HASH(...)
. Even though the data is partitioned by month, StarRocks needs a distribution key to manage data across BE nodes. 4. Version Requirement: * Standard 
date_trunc
partitioning for MVs is available in most StarRocks 3.x versions. * If your Iceberg table uses multi-column partitioning (e.g., partitioned by 
region
AND 
month
), you should use StarRocks v3.5.0 or later, which supports multi-column partition mapping for Lakehouse integration. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • data_source/catalog/iceberg/iceberg_catalog.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky Query 1 ERROR at Line 1: : Materialized view which type is ASYNC need to specify refresh interval for external table
r
The error occurs because StarRocks does not currently support event-triggered automatic refreshes for external catalogs (Iceberg, Hive, etc.). Unlike internal tables, where a data load automatically triggers an MV refresh, external tables require you to define a fixed-interval schedule or use manual refresh. To fix this, you must add the 
EVERY (INTERVAL ...)
clause to your 
REFRESH ASYNC
statement. Corrected Example
Copy code
sql
CREATE MATERIALIZED VIEW mv_monthly_orders
-- Align with Iceberg's month(order_date) transform
PARTITION BY date_trunc('month', order_date)
DISTRIBUTED BY HASH(order_id)
-- FIX: Specify a refresh interval (e.g., every 1 hour)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    "partition_ttl_number" = "24",
    -- Required for query rewrite to work with external tables
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    order_id,
    order_date,
    customer_id,
    amount
FROM iceberg_catalog.db.orders;
Key Requirements for External Iceberg MVs 1. Refresh Strategy: You have two choices for external tables: * Scheduled: 
REFRESH ASYNC EVERY (INTERVAL 1 DAY)
— StarRocks will check for Iceberg snapshot changes at this interval and refresh only the affected partitions. * Manual: 
REFRESH MANUAL
— You must trigger the refresh yourself using 
REFRESH MATERIALIZED VIEW mv_name
. 2. Partition Alignment: Using 
date_trunc('month', col)
is correct for Iceberg's 
month()
transform. This allows for incremental partition refreshes. Even if the schedule is set to "every 1 hour," StarRocks will only refresh a month's partition if it detects the Iceberg snapshot for that partition has changed. 3. Query Rewrite: By default, StarRocks is conservative with external data. To allow the optimizer to automatically rewrite user queries to use the MV, you usually need to set 
"force_external_table_query_rewrite" = "true"
in the properties. Summary of the Error * Cause: 
REFRESH ASYNC
(without an interval) implies "refresh immediately whenever the base table changes." StarRocks cannot "watch" S3/Iceberg for changes in real-time to trigger this. * Solution: Tell StarRocks how often to poll for changes by adding 
EVERY (INTERVAL ...)
. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
k
@Rocky Query 1 ERROR at Line 1: : Getting analyzing error. Detail message: Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view..
r
The error 
Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view
occurs because StarRocks only supports the TTL (Time-To-Live) property on Range Partitioned materialized views. If your Iceberg base table's partition column (or the result of the partition transform) is interpreted as a STRING by StarRocks, it defaults to creating a List Partition, which does not support automatic TTL pruning based on chronological order. How to Fix To enable TTL, you must ensure the MV is created with Range Partitioning by using a 
DATE
or 
DATETIME
type for the partition key. 1. Explicitly Cast to DATE (If the column is a String) If 
order_date
in your Iceberg table is a string, you must convert it to a 
DATE
type in the 
SELECT
list and partition by that alias. 2. Use 
partition_ttl
(Recommended) 
partition_ttl
is the modern replacement for 
partition_ttl_number
(supported since v3.1.5). It allows you to specify a time range (e.g., 
'24 MONTH'
) instead of just a number of partitions. Corrected SQL Example
Copy code
sql
CREATE MATERIALIZED VIEW mv_monthly_orders
-- PARTITION BY must use a DATE/DATETIME column to be "Range Partitioned"
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(order_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    -- Use partition_ttl for a clear time window (v3.1.5+)
    "partition_ttl" = "24 MONTH",
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    order_id,
    -- If order_date is a string '2024-01-01', cast it:
    -- CAST(order_date AS DATE) as mv_date,
    order_date as mv_date, 
    customer_id,
    amount
FROM iceberg_catalog.db.orders;
Why this works: * Range vs. List: By ensuring the partitioning expression 
date_trunc('month', ...)
operates on a 
DATE
type, StarRocks recognizes the MV as Range Partitioned. This allows it to sort partitions chronologically and apply the TTL. * Alignment: Even though you use 
date_trunc
in the MV, StarRocks is smart enough to map this to an Iceberg table partitioned by 
month(order_date)
for incremental refreshes. * TTL Behavior: With 
"partition_ttl" = "24 MONTH"
, StarRocks will automatically drop any partition whose date is older than 24 months during every refresh cycle. Checklist if it still fails: 1. Check Data Type: Run 
DESC iceberg_catalog.db.orders;
. If 
order_date
is a 
VARCHAR
or 
STRING
, you must use 
CAST(order_date AS DATE)
in the SELECT and use that alias in the 
PARTITION BY
. 2. StarRocks Version: If you are on a version older than 3.1.5, use 
"partition_ttl_number" = "24"
, but the requirement for a 
DATE/DATETIME
partition key remains the same. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • data_source/catalog/iceberg/iceberg_catalog.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky im getting the error with this: CREATE MATERIALIZED VIEW default_catalog.reporting.mv PARTITION BY date_trunc('month', date) -- Distributed by a high-cardinality column for performance DISTRIBUTED BY HASH(id) REFRESH ASYNC EVERY (INTERVAL 1 HOUR) PROPERTIES ( "partition_refresh_number" = "1", "partition_ttl_number" = "60", "force_external_table_query_rewrite" = "true" ) AS SELECT * FROM iceberg.viewiq.core_age_range_view;
r
The error 
Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view
occurs because StarRocks has determined your Materialized View (MV) is not "Range Partitioned." Only Range Partitioned MVs support automatic data retention via TTL properties. This typically happens for two reasons in your specific SQL: 1. *Use of `SELECT `: StarRocks needs to explicitly map the MV's partition key to a column in the 
SELECT
list to establish a Range relationship. Using 
*
makes this mapping ambiguous or fails to verify the data type. 2. Column Type or Name: If the 
date
column in your Iceberg table is a STRING/VARCHAR (even if it looks like a date), StarRocks will treat the MV as "List Partitioned" by default. Additionally, 
date
is a reserved keyword and should be escaped. Recommended Fix To ensure the MV is created as a Range Partitioned table that supports TTL: * Explicitly cast the partition column to 
DATE
or 
DATETIME
. * Use an alias for the casted column. * *Avoid `SELECT ` and list your columns explicitly. * Use 
partition_ttl
 (e.g., 
'60 MONTH'
) which is the modern replacement for 
partition_ttl_number
(supported in v3.1.5+). Corrected Example
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- Use the alias defined in the SELECT list
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    -- 'partition_ttl' is the recommended property for v3.1.5+
    "partition_ttl" = "60 MONTH", 
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id,
    -- Explicitly cast and alias the partition column
    CAST(`date` AS DATE) AS mv_date,
    col1,
    col2
FROM iceberg.viewiq.core_age_range_view;
Why this solves it: * Range Partitioning: By casting the column to 
DATE
and using 
date_trunc
, StarRocks explicitly recognizes this as a time-series range. This enables the 
partition_ttl
logic. * Metadata Mapping: Explicitly naming the column in both the 
PARTITION BY
and 
SELECT
clauses allows the metadata engine to verify that the MV partition aligns with the Iceberg base table partitions (Partition Alignment). * Reserved Keywords: Enclosing `` 
date
`` in backticks prevents syntax errors if your column is named after the 
DATE
type. If you still see errors: Ensure the base Iceberg table 
core_age_range_view
is indeed a partitioned table. If it is an Iceberg view rather than a table, StarRocks may not be able to infer the partition structure required for an incremental partitioned MV. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky Query 1 ERROR at Line 1: : Getting analyzing error from line 2, column 13 to line 2, column 40. Detail message: Materialized view partition function date_trunc must related with column.
r
This error message, "Materialized view partition function date_trunc must related with column," typically happens because the StarRocks analyzer cannot definitively map the argument inside 
date_trunc
to an explicit column in your 
SELECT
list. This is usually caused by two things in your current query: 1. *Using `SELECT `: StarRocks requires the partitioning column to be explicitly named in the 
SELECT
list to establish the partition alignment between the base table and the MV. 2. Reserved Keyword: 
date
is a reserved keyword (it's a data type). When used in a function within the 
PARTITION BY
clause, the analyzer may fail to recognize it as a column name unless it is explicitly aliased or backticked. How to Fix You must explicitly list the column in your 
SELECT
clause and use a clear alias.
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- 1. Use the alias defined in the SELECT list below
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    "partition_ttl" = "60 MONTH", 
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id,
    -- 2. Explicitly name the column and provide an alias
    -- Also use backticks if the source column name is actually `date`
    `date` AS mv_date,
    other_col1,
    other_col2
FROM iceberg.viewiq.core_age_range_view;
Why this change is required: * Column Mapping: StarRocks' MV optimizer needs to "see" the column in the 
SELECT
output to verify that it can partition the resulting data. 
SELECT *
often masks the metadata needed for this verification. * Type Safety: If the base table's 
date
column is a 
STRING
, you must cast it: `CAST(
date
AS DATE) AS mv_date`. Partitioning functions like 
date_trunc
in MVs require the input to be a 
DATE
or 
DATETIME
type. * Partition Alignment: For the MV to refresh incrementally (only refreshing new months), the column used in 
date_trunc
should ideally be the same column used to partition the underlying Iceberg table. Summary Checklist: 1. Change 
SELECT *
to a list of columns. 2. Alias the date column (e.g., 
AS mv_date
). 3. Use that alias in the 
PARTITION BY date_trunc(...)
expression. 4. If the original column name is 
date
, wrap it in backticks: `` 
date
``. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky my iceberg table 'date' column is date type, with PARTITION BY month(
date
)
r
The errors you are seeing occur because StarRocks is having trouble mapping the partition expression in your 
CREATE MATERIALIZED VIEW
statement to the actual columns in your 
SELECT
list, and 
date
is a reserved keyword. Because your Iceberg table is already partitioned by 
month(date)
, you have two ways to fix this depending on your StarRocks version. Option 1: Modern Alignment (Recommended for v3.2.3+) Starting from StarRocks v3.2.3, if an Iceberg table uses a partition transform like 
month(date)
, you can simply partition the MV by the base column name. StarRocks will automatically recognize the transform and align the MV partitions by month. Note: You must explicitly list the columns in the 
SELECT
clause (avoid 
SELECT *
) and wrap the reserved keyword `` 
date
`` in backticks.
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- StarRocks v3.2.3+ will automatically align this with Iceberg's month(date)
PARTITION BY (`date`) 
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    -- Use partition_ttl for easier management (e.g., '60 MONTH')
    "partition_ttl" = "60 MONTH",
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id, 
    `date`,  -- Explicitly select the column
    col1, 
    col2
FROM iceberg.viewiq.core_age_range_view;
Option 2: Explicit Rollup (Required if Option 1 fails) If you are on an older version or if the automatic alignment isn't triggering, you must use 
date_trunc
. To fix the "must relate to column" error, ensure the column used in 
date_trunc
is explicitly projected in the 
SELECT
list.
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- Use the alias 'mv_date' to ensure the relation is clear to the analyzer
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    "partition_ttl" = "60 MONTH",
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id, 
    `date` AS mv_date, -- Provide an explicit alias
    col1, 
    col2
FROM iceberg.viewiq.core_age_range_view;
Important Prerequisites 1. Is it a Table or a View? Your base table is named 
core_age_range_view
. If this is an Iceberg View rather than an Iceberg Table, StarRocks cannot detect the underlying partition transforms (
month(date)
). Materialized View partitioning requires the base source to be a physical table. 2. Reserved Keywords: Always wrap the column name `` 
date
`` in backticks to prevent the analyzer from confusing it with the 
DATE
data type. 3. Range Partitioning: The error 
does not support non-range-partitioned
was likely a side-effect of the partition expression failing to resolve. Once the 
PARTITION BY
clause is correctly linked to the column, the MV will be created as Range Partitioned, and TTL will work. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky this is an external iceberg table on s3 tables. there is a date column of date type, and the iceberg table is already partitioned by month(date). i want to create an async partitioned mat view based on the same monthly partitions, but i keep expereincing "Query 1 ERROR at Line 1: : Getting analyzing error. Detail message: Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view.."
r
The error 
Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view
happens because StarRocks has failed to verify that your Materialized View (MV) is "Range Partitioned." Only Range Partitioned MVs support automatic data retention via TTL properties. For an Iceberg table already partitioned by 
month(date)
, the analyzer is likely failing for these specific reasons: 1. Reserved Keyword Conflict: The name 
date
is a reserved keyword in StarRocks. Without backticks, 
date_trunc('month', date)
can be misinterpreted as the 
DATE
data type rather than your column. 2. *Use of `SELECT `: StarRocks requires the partitioning column to be explicitly present and identifiable in the 
SELECT
list to establish the "Range" relationship. Using 
*
makes this mapping ambiguous. 3. Iceberg Version Alignment: If your StarRocks version is older than v3.2.3, it may not automatically recognize the Iceberg 
month()
transform as a valid Range partition for the MV unless explicitly mapped. The Corrected SQL To fix this, you must backtick the reserved keyword, explicitly list your columns, and use an alias to ensure the analyzer can link the partition function to the result column.
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- 1. Use the alias defined in the SELECT list
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    -- 2. 'partition_ttl' is the modern replacement for 'partition_ttl_number'
    "partition_ttl" = "60 MONTH", 
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id,
    -- 3. Explicitly project the column with backticks and an alias
    `date` AS mv_date,
    col1,
    col2
FROM iceberg.viewiq.core_age_range_view;
Why this solves the issue: * Establish Range Property: By using 
date_trunc
on a column projected in the 
SELECT
list, StarRocks marks the MV as Range Partitioned. This enables the 
partition_ttl
property to function. * Resolves "Must relate with column": Explicitly using 
mv_date
in both the 
PARTITION BY
and 
SELECT
clauses eliminates the ambiguity caused by 
SELECT *
. * Partition Alignment: For Iceberg tables, StarRocks (v3.2.3+) will see that your MV's 
month
truncation matches the Iceberg table's 
month
transform, allowing for Incremental Refresh (it will only refresh the months that changed). Important Note on Table vs. View Your base table is named 
core_age_range_view
. If this is an Iceberg View (and not a physical Iceberg Table), StarRocks cannot perform partition-level tracking or TTL. Partition alignment and incremental refresh only work when the source is a physical partitioned table. If it is a view, you will have to refresh the entire MV every time. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky now getting: Query 1 ERROR at Line 1: : Getting analyzing error. Detail message: Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view..
r
The error message 
Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view
indicates that StarRocks has classified your Materialized View (MV) as a List-partitioned or non-partitioned table, rather than a Range-partitioned table. In StarRocks, TTL properties (like 
partition_ttl_number
or 
partition_ttl
) are strictly only supported for Range partitions. Even though your column is a 
DATE
type and you are using 
date_trunc
, several factors can cause StarRocks to default to List partitioning when dealing with Iceberg tables: 1. Reserved Keyword Conflict The name 
date
is a reserved keyword (a data type) in StarRocks. If you use 
PARTITION BY date_trunc('month', date)
without backticks, the analyzer may fail to link the function to your actual column and instead treat the partition as a static expression or a List of values. 2. Iceberg Partition Transform Alignment StarRocks has specific logic for Iceberg transforms like 
month(date)
. * For StarRocks v3.2.3 and later: You should partition the MV directly by the column name `` 
date
``. StarRocks will automatically detect the Iceberg 
month()
transform and align it as a Range partition. * For Older Versions: You must use 
date_trunc
, but you must ensure the column is explicitly selected and aliased in the 
SELECT
list to force Range partitioning. 3. "List" vs "Range" Detection If StarRocks cannot find a continuous "range" mapping from the base table to the MV (which often happens with 
SELECT *
or ambiguous column names), it fallback to List partitioning. List partitions do not support the 
partition_ttl
property. ────────── The Recommended Fix Update your SQL to use backticks around the reserved word `` 
date
``, explicitly alias the column, and use the modern 
partition_ttl
property (which replaced 
partition_ttl_number
since v3.1.5).
Copy code
sql
CREATE MATERIALIZED VIEW <http://default_catalog.reporting.mv|default_catalog.reporting.mv>
-- 1. Use the alias 'mv_date' to ensure the analyzer creates a Range partition
PARTITION BY date_trunc('month', mv_date)
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "partition_refresh_number" = "1",
    -- 2. Use 'partition_ttl' (e.g., '60 MONTH') which is more robust than '_number'
    "partition_ttl" = "60 MONTH", 
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    id,
    -- 3. Backtick the reserved word and provide a clear alias
    `date` AS mv_date,
    other_col1,
    other_col2
FROM iceberg.viewiq.core_age_range_view;
Why this works: * Range Partitioning: By explicitly mapping 
date_trunc
to a projected column alias, StarRocks identifies the MV as Range-partitioned (specifically Expression Partitioning), which allows the use of TTL. * TTL Support: Once the MV is successfully created as Range-partitioned, the error about 
partition_ttl_number
will disappear. * Iceberg Alignment: Because your Iceberg table is already partitioned by 
month(date)
, this configuration allows StarRocks to perform Incremental Refresh, only updating the specific months that have changed in S3. One final check: Ensure 
core_age_range_view
is a physical Iceberg Table. If it is actually an Iceberg View, StarRocks cannot see the underlying partitions and will always treat the MV as a single non-partitioned unit, which does not support TTL. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • table_design/data_distribution/expression_partitioning.md
k
@Rocky i am on starrocks 4.1.4
r
The error 
Invalid parameter partition_ttl_number does not support non-range-partitioned materialized view
occurs because StarRocks has classified your Materialized View (MV) as List-partitioned (or non-partitioned), but TTL features are strictly only supported for Range-partitioned tables. Even though your Iceberg table is partitioned by 
month(date)
, several syntax choices in your MV definition can cause StarRocks to drop the "Range" classification: 1. Don't use 
month()
in the MV definition If you used 
PARTITION BY month(date)
in your MV SQL, StarRocks treats the result as an integer (1-12). This creates a List partition, which does not support TTL. The Fix: Use 
date_trunc
instead. In StarRocks 3.2.3 and later (including your v4.1.4), 
date_trunc('month', ...)
is the correct way to align with Iceberg’s 
month()
transform while maintaining a Range partition for the MV. 2. Escape the Reserved Keyword The word 
date
is a reserved keyword (a data type) in StarRocks. If you don't use backticks, the analyzer may fail to recognize it as a column, causing the partition mapping to fail and defaulting the MV to a non-partitioned state. The Fix: Always use `` 
date
``. 3. Update the Property Name Since StarRocks v3.1.5, 
partition_ttl_number
has been replaced by 
partition_ttl
. While the old property is sometimes supported for backward compatibility, on v4.1.4 you should use the modern string-based TTL. ────────── Recommended SQL for StarRocks 4.1.4 Use this structure to ensure the MV is recognized as Range-partitioned and aligns with your Iceberg source:
Copy code
sql
CREATE MATERIALIZED VIEW your_catalog.your_db.your_mv
-- 1. Use date_trunc to ensure it's a Range partition
PARTITION BY date_trunc('month', mv_date) 
DISTRIBUTED BY HASH(some_id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    -- 2. Modern TTL property (e.g., '24 MONTH' or '2 YEAR')
    "partition_ttl" = "24 MONTH", 
    "partition_refresh_number" = "1",
    "force_external_table_query_rewrite" = "true"
)
AS
SELECT 
    some_id,
    -- 3. Explicitly alias the reserved keyword column
    `date` AS mv_date, 
    col1,
    col2
FROM your_iceberg_catalog.your_db.your_table;
Why this works: * Range Detection: 
date_trunc
returns a `DATE`/`DATETIME` value. StarRocks uses this to build a Range partition, enabling the 
partition_ttl
logic. * Iceberg Alignment: StarRocks automatically maps its 
date_trunc('month', ...)
to Iceberg's 
month()
transform. This allows Incremental Refresh (it will only refresh the specific month partitions that changed in S3). * Explicit Mapping: By aliasing the column as 
mv_date
and using it in both the 
SELECT
and 
PARTITION BY
clauses, you remove any ambiguity for the SQL analyzer. Note on Partition Evolution: If your Iceberg table has undergone "Partition Evolution" (e.g., you changed the partition spec from 
day
to 
month
over time), StarRocks currently does not support creating a partitioned MV on that table. You can verify this by checking the Iceberg table metadata. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
k
@Rocky show mat view creation status
r
To check the status of your asynchronous materialized view (MV) in StarRocks 4.1.4, you have three primary methods depending on the level of detail you need. 1. High-Level Summary (
SHOW MATERIALIZED VIEWS
) This is the quickest way to see if the MV is "Active" and whether the last refresh succeeded.
Copy code
sql
SHOW MATERIALIZED VIEWS WHERE NAME = 'your_mv_name'\G
Key fields to look at: * 
is_active
: Must be 
true
for the MV to be usable. If 
false
, check 
inactive_reason
. * 
last_refresh_state
: Shows 
SUCCESS
, 
FAILED
, 
RUNNING
, or 
PENDING
. * 
last_refresh_error_message
: Provides a brief error snippet if the last refresh failed. * 
task_name
: Take note of this (usually looks like 
mv-12345
) to use in the detailed query below. ────────── 2. Detailed Execution History (
task_runs
) Because async MVs run as background tasks, you can query the 
information_schema
to see exactly what happened during the creation or refresh, including start/end times and full error logs.
Copy code
sql
SELECT * 
FROM information_schema.task_runs 
WHERE task_name = 'mv-YOUR_TASK_ID' -- Get this ID from the SHOW command above
ORDER BY create_time DESC 
LIMIT 5\G
Why use this? * It shows the 
progress
 percentage while a build is running. * It provides the full 
error_message
 if the build failed, which is often more descriptive than the summary in 
SHOW MATERIALIZED VIEWS
. ────────── 3. Check Partition-Level Status Since you are using a partitioned MV, you may want to see which specific monthly partitions have been successfully built and which are still "outdated" or missing.
Copy code
sql
SHOW MATERIALIZED VIEW STATUS FOR your_mv_name;
This command (introduced in later 3.x versions) helps identify if specific months are stale because the underlying Iceberg data changed. Summary of common states: * 
is_active = false
: Usually means the schema of the base Iceberg table changed significantly, or there was a metadata error. You may need to run 
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
to try re-activating it. * 
last_refresh_state = FAILED
: Check 
task_runs
for the exact error. For Iceberg, this is often related to S3 connectivity, permissions, or the "non-range-partitioned" error we discussed earlier. References • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/information_schema/task_runs.md References • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view-single_table.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
k
@Rocky is there a 'show proc' for queries creating mvs
r
Yes, there is a 
SHOW PROC
command to monitor the active queries triggered by Materialized View (MV) creation and refresh tasks, along with other system-level tools for status tracking. 1. Monitor Active Refresh Queries When an asynchronous MV is being created or refreshed, StarRocks executes an internal 
INSERT OVERWRITE
query. You can see these active queries using:
Copy code
sql
SHOW PROC '/current_queries';
-- Or for all FE nodes in a cluster:
SHOW PROC '/global_current_queries';
In the output, look for queries where the 
User
 is often the system user or the query definition starts with 
INSERT OVERWRITE
. You can see real-time metrics like 
ScanBytes
, 
MemoryUsage
, and 
ExecTime
. ────────── 2. Monitor Task Status (Modern Approach) Since StarRocks 3.x/4.x handles MV refreshes as "Tasks," the most detailed way to see the "Creation" or "Refresh" status is through the 
information_schema
. To see the progress and error messages of the current run:
Copy code
sql
SELECT 
    query_id, 
    task_name, 
    state, 
    progress, 
    error_message, 
    create_time 
FROM information_schema.task_runs 
ORDER BY create_time DESC 
LIMIT 5;
────────── 3. High-Level Summary If you just want to see if the MV finished building and is "Active":
Copy code
sql
SHOW MATERIALIZED VIEWS WHERE NAME = 'your_mv_name'\G
* 
last_refresh_state
: Shows 
RUNNING
, 
SUCCESS
, or 
FAILED
. * 
last_refresh_error_message
: This is where your previous "Invalid parameter" error would have appeared. Summary of Monitoring Commands: Command | Best For... 
SHOW PROC '/current_queries'
| Seeing real-time CPU/Memory/Scan usage of an active refresh. 
SELECT * FROM task_runs
| Checking the percentage Progress (%) and historical errors. 
SHOW MATERIALIZED VIEWS
| Checking if the MV is currently 
Active: true
. 
SHOW ALTER MATERIALIZED VIEW
| Note: This is only for synchronous (simple) MVs, not modern Async MVs. References • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/information_schema/task_runs.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • knowledge/usage/best-practice.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/cluster-management/nodes_processes/SHOW_PROC.md
k
@Rocky what causes this: mv_staging_video_bs_confidence failed after 3 times, try lock failed: 0, error-msg : com.starrocks.common.StarRocksException: Failed to allocate resource to query: pending timeout [300s], you could modify the session variable [query_queue_pending_timeout_second] to pending more time
r
This error indicates that your Materialized View (MV) refresh task was placed in a Query Queue because the cluster reached a resource limit (CPU, Memory, or Concurrency), and it timed out after waiting for 300 seconds. In StarRocks, when you create or refresh an MV, it triggers a background 
INSERT OVERWRITE
query. If the cluster is too busy, StarRocks "parks" this query in a queue to prevent an Out-of-Memory (OOM) or CPU collapse. 1. Immediate Workaround You can increase the time a query is allowed to sit in the queue before failing. Execute this globally (as an admin):
Copy code
sql
SET GLOBAL query_queue_pending_timeout_second = 600; -- Increase to 10 minutes
2. Identify the Bottleneck The queue is triggered because one of your resource thresholds was hit. Run these commands to see why your cluster is queuing tasks: * Check active/pending queries:
Copy code
sql
    SHOW RUNNING QUERIES;
Look for the 
State
column. If many queries are 
PENDING
, look at the 
ResourceGroupId
to see which group is full. * Check Backend (BE) resource usage:
Copy code
sql
    SHOW PROC '/backends'\G
Check 
MemUsedPct
and 
CpuUsedPct
. If these are high (e.g., >90%), the queue will naturally hold back new MV refreshes. 3. Why MV Refreshes specifically fail MV refreshes are heavy. If 
mv_staging_video_bs_confidence
is large, it might be trying to refresh too many partitions at once, hitting the memory limit immediately. Recommended Fixes for the MV: * Reduce Batch Size: Instead of refreshing everything at once, tell StarRocks to refresh only 1 partition at a time to keep resource usage low.
Copy code
sql
    ALTER MATERIALIZED VIEW mv_staging_video_bs_confidence
    SET ("partition_refresh_number" = "1");
* Check MV Concurrency: StarRocks limits how many MV tasks can run at once (default is 4). You can check this FE configuration:
Copy code
sql
    ADMIN SHOW FRONTEND CONFIG LIKE 'task_runs_concurrency';
4. Adjust Queue Thresholds (Advanced) If your cluster has plenty of RAM/CPU but is still queuing, your concurrency limits might be too strict. Check these variables: * `query_queue_concurrency_limit`: Maximum queries per BE node. * `query_queue_mem_used_pct_limit`: Triggers queue when memory exceeds this % (e.g., 0.9). * `query_queue_cpu_used_permille_limit`: Triggers queue when CPU exceeds this (e.g., 800). Summary: The error isn't a bug in your MV logic; it's the StarRocks "Traffic Controller" stopping the refresh to protect the cluster. Increasing the timeout buys you time, but reducing 
partition_refresh_number
is the best way to ensure the refresh actually succeeds without overloading the system. References • administration/management/resource_management/query_queues.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/System_variable.md References • administration/management/FE_configuration.md • administration/management/BE_configuration.md • sql-reference/System_variable.md
k
@Rocky i have large dimenisonal external catalog iceberg tables i want to replicate using materialized views for better performance on shared nothing cluster. these MVs are failing due to OOM, query timeouts, etc. can we create hash based partition materialzied views by hash(id)
r
No, StarRocks does not support 
PARTITION BY HASH(id)
 for materialized views (MVs). Partitioning in StarRocks MVs is primarily designed for Range or List partitioning (typically date-based) to enable incremental refreshes. Here is why you are likely hitting limitations and how to resolve the OOM/Timeout issues for your large Iceberg dimension tables. 1. The "Partition Alignment" Rule In StarRocks, a partitioned materialized view can only be created if the base table (your Iceberg table) is also partitioned. * If the Iceberg table is not partitioned: You cannot create a partitioned MV on it. StarRocks will treat it as a single unit and try to refresh the entire table in one query, leading to the OOMs and timeouts you are seeing. * Supported Iceberg Transforms: Even if your Iceberg table is partitioned, StarRocks currently only supports mapping partitions using 
identity
, 
year
, 
month
, 
day
, or 
hour
. The Iceberg 
bucket
 (hash) transform is explicitly not supported for MV partition mapping. 2. How to handle large Dimension Table replication If your dimension table is too large to refresh in a single transaction, here are the recommended strategies: A. Manual "Sharded" Ingestion (Best for Non-Partitioned Tables) Since you cannot use an MV to refresh in chunks for a non-partitioned base table, use a StarRocks Task with manual batching. 1. Create a standard StarRocks table (Duplicate or Primary Key) instead of an MV. 2. Partition this table by a "shard" ID (e.g., 
id % 10
). 3. Create a task to load data in chunks:
Copy code
sql
    -- Example: Loading in 10 batches to prevent OOM
    INSERT INTO target_table SELECT * FROM iceberg_catalog.db.dim_table WHERE abs(hash(id)) % 10 = 0;
    -- (Repeat for 1 through 9)
B. Optimize the MV Refresh (If you must use an MV) If you prefer the MV for its transparent rewrite capabilities, you can try to "muscle through" the OOM by increasing resource limits specifically for the MV refresh:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.query_mem_limit" = "17179869184", -- Increase memory limit (e.g., 16GB+)
    "session.insert_timeout" = "3600"         -- Increase timeout to 1 hour
);
C. Use 
DISTRIBUTED BY HASH
(For Query Performance) While you can't partition by hash for the refresh logic, you should definitely distribute by hash for query performance on your Shared-Nothing cluster. This ensures the dimension data is balanced across your BE nodes:
Copy code
sql
CREATE MATERIALIZED VIEW your_mv
DISTRIBUTED BY HASH(id) BUCKETS 32 -- Spreads data across nodes
REFRESH ASYNC
AS SELECT ...
D. Check the "Partition Refresh Number" (If Iceberg IS partitioned) If your Iceberg table is partitioned (e.g., by date) and you are still hitting OOM, it is because StarRocks tries to refresh all changed partitions at once. You can force it to refresh them one-by-one:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("partition_refresh_number" = "1");
Summary Recommendation For a large, non-partitioned Iceberg dimension table, an MV is often the wrong tool because it lacks the "refresh by chunk" logic for non-partitioned sources. * Recommended: Create a standard StarRocks table and use a Task (
CREATE TASK
) or a script to perform 
INSERT INTO ... SELECT
with a 
WHERE
clause filter to load the data in manageable batches. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References
k
@Rocky does starrocks support query rewrite when a client is querying an iceberg external table to rewrite to a native table
r
Yes, StarRocks supports transparent query rewrite from an Iceberg external table to a native table via Asynchronous Materialized Views (MVs). In this workflow, the MV acts as a "native cache" of your Iceberg data. When a client queries the original Iceberg table, the StarRocks optimizer automatically detects if an MV exists that contains the same data and transparently redirects the query to the MV (native storage) instead of scanning the data lake. How it works 1. Create the MV: You create an asynchronous MV that selects data from your Iceberg catalog. 2. Native Storage: StarRocks stores this data in its own optimized native format (segment files) on the cluster's local disks. 3. Optimizer Rewrite: When a user runs 
SELECT ... FROM iceberg_catalog.db.table
, the StarRocks optimizer rewrites the execution plan to 
SELECT ... FROM starrocks_native_mv
. Key Features & Requirements * Data Consistency: By default, StarRocks checks if the base Iceberg table has changed. If the Iceberg data is newer than the MV, the rewrite will not happen (to avoid returning stale data). * Staleness Tolerance: You can allow the query to use an older "cached" version of the data by setting the 
mv_rewrite_staleness_second
property on the MV. This is common for large dimension tables.
Copy code
sql
    PROPERTIES (
        "mv_rewrite_staleness_second" = "3600" -- Use the MV even if it's up to 1 hour out of date
    )
* Partition-Level Tracking: Starting from v3.1.4, StarRocks can detect partition-level changes in Iceberg. If only one partition in Iceberg changed, StarRocks can still rewrite queries for the other "clean" partitions. * Supported Transforms: Since v3.2.3, StarRocks supports partitioning the MV based on Iceberg transforms like 
identity
, 
year
, 
month
, 
day
, and 
hour
. Example If you have an Iceberg table and create this MV:
Copy code
sql
CREATE MATERIALIZED VIEW iceberg_cache_mv
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS 
SELECT id, name, region FROM iceberg_catalog.db.large_dim_table;
When a user runs:
Copy code
sql
SELECT name FROM iceberg_catalog.db.large_dim_table WHERE region = 'US';
StarRocks will automatically fetch the result from 
iceberg_cache_mv
(native storage) instead of reading from S3/HDFS. Verification You can check if a query is being rewritten by running 
EXPLAIN <query>
. Look for the 
MaterializedViewRewrite
 section in the output or see if the 
OlapScanNode
points to your MV name instead of the Iceberg table. References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
k
@Rocky how do i limit a mv resourse group's memory usage to prevent OOM issues
r
To limit memory usage for Materialized View (MV) refreshes and prevent OOM issues, you should use Resource Groups. You can either tune the default group or create a dedicated one with strict limits and disk-spilling enabled. 1. Create a Dedicated Resource Group Creating a custom resource group is the most effective way to isolate MV memory usage. You can set a hard 
mem_limit
and a 
spill_mem_limit_threshold
to trigger disk spilling instead of failing when memory is tight.
Copy code
sql
CREATE RESOURCE GROUP rg_mv_limit
WITH (
    "mem_limit" = "0.3",             -- Limit to 30% of BE query memory
    "cpu_weight" = "10",             -- Give it lower CPU priority
    "spill_mem_limit_threshold" = "0.7", -- Start spilling to disk at 70% of this group's limit
    "concurrency_limit" = "2"        -- Limit simultaneous refreshes
);
2. Bind the MV to the Resource Group Once the group is created, attach your MV to it. This ensures the background refresh task always runs within those constraints. * For an existing MV:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET ("resource_group" = "rg_mv_limit");
* During MV creation:
Copy code
sql
    CREATE MATERIALIZED VIEW your_mv_name
    REFRESH ASYNC
    PROPERTIES (
        "resource_group" = "rg_mv_limit"
    )
    AS SELECT ...;
3. Tuning the Default MV Resource Group If you haven't created a custom group, StarRocks uses a system-defined group called 
default_mv_wg
. You can limit its memory globally by modifying BE configurations (requires a BE restart or using 
curl
to update at runtime): * 
default_mv_resource_group_memory_limit
: Default is 
0.8
(80%). Lower this (e.g., to 
0.4
) to restrict all MVs. * 
default_mv_resource_group_spill_mem_limit_threshold
: Default is 
0.8
. Set this to a lower value (e.g., 
0.5
) to trigger spilling earlier and prevent OOMs. 4. Important Session Variables for MVs You can also set memory limits directly on the MV's refresh session via properties. This is helpful if one specific MV is the "bad actor":
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET (
    "session.query_mem_limit" = "8589934592", -- Limit to 8GB per refresh task
    "partition_refresh_number" = "1"         -- Refresh only 1 partition at a time to keep memory footprint low
);
Summary of Strategy 1. Use 
mem_limit
 to cap the memory. 2. Use 
spill_mem_limit_threshold
 to trade speed for stability (spilling to disk). 3. Use 
partition_refresh_number
 to reduce the amount of data processed in a single batch. References • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md • best_practices/audit_log_resource_group.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md • best_practices/audit_log_resource_group.md References • administration/management/BE_configuration.md • best_practices/audit_log_resource_group.md • administration/management/resource_management/resource_group.md
k
@Rocky how does starrocks process unpartitioned materialized views? could it be the underlying partitions on icberg are too large to fit into memory? why isn't it spilled to disk
r
When a StarRocks materialized view (MV) is unpartitioned, StarRocks cannot perform incremental or partial refreshes. This leads to several issues when dealing with large external Iceberg tables. 1. How StarRocks Processes Unpartitioned MVs For unpartitioned MVs, StarRocks uses an "All-or-Nothing" refresh logic: * Full Data Scan: Every time a refresh is triggered (even if only 1% of the Iceberg data changed), StarRocks executes an 
INSERT OVERWRITE
task that scans the entire Iceberg table. * Single Transaction: The entire refresh must complete within a single transaction. If your Iceberg table is 1TB, StarRocks will attempt to pull, process (join/aggregate), and write 1TB of data in one go. * Memory Pressure: Because it's a single task, all intermediate data (like hash tables for joins or state for aggregations) is allocated under one query ID, quickly hitting the memory limits of your Resource Group or BE nodes. 2. Why isn't it spilling to disk? Even if you have disk spilling enabled for the cluster, it may not be working for your MV refresh for three main reasons: A. Spilling is not enabled for the MV session By default, the 
enable_spill
variable is often 
false
. For MVs, you must explicitly enable it in the MV's properties so the background refresh task knows to use it:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name SET ("session.enable_spill" = "true");
B. The "Scan" operator does not spill StarRocks' spilling feature currently supports Aggregates, Sorts, and Joins. * If your OOM happens during the Scan phase (e.g., while reading massive Parquet files or handling huge Iceberg metadata), it will not spill. * Data lake scans require memory to buffer rows and metadata. If a single "scan range" or the metadata for the entire unpartitioned table is too large, the process will OOM before it even reaches a "spillable" operator. C. Resource Group Thresholds If you are using Resource Groups, the refresh task will be killed as soon as it hits the group's 
mem_limit
. Spilling only triggers when the memory usage reaches a certain threshold (default is often 80% of the limit). If the memory spikes faster than the spill mechanism can react, the task hits the hard limit and is terminated. 3. How to fix it If you cannot partition the MV by a specific column to reduce the refresh grain, try these steps: 1. Force Spilling & Increase Memory:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SET (
        "session.enable_spill" = "true",
        "session.query_mem_limit" = "34359738368", -- Set to 32GB or higher
        "session.insert_timeout" = "7200"          -- 2 hour timeout
    );
2. Lower Concurrency: If multiple MVs are refreshing at once, they compete for memory. Use a dedicated resource group for MVs with 
concurrency_limit = 1
. 3. Check Iceberg Metadata: If the Iceberg table has many small files, the metadata scan itself can cause OOM. Ensure you have run compaction on the Iceberg table. 4. Use a "Fake" Partition (If possible): If the Iceberg table has any column that can act as a rough partition (even if not ideal for hash), use it in a 
PARTITION BY
clause for the MV. This allows StarRocks to refresh the table in smaller "chunks" rather than one massive 1TB block. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • administration/management/resource_management/spill_to_disk.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
k
@Rocky my iceberg table is partitioned by bucket(id). starrocks can not create a partitioned materialized view by the same partitions as the iceberg table?
r
No, StarRocks does not currently support creating a partitioned materialized view that is aligned with an Iceberg table partitioned by the 
bucket()
transform. According to the StarRocks documentation (up to v3.5), the mapping for Iceberg partition transforms is restricted to time-based and identity transforms. The support status is as follows: Iceberg Transform | Iceberg partition expression | Materialized view partition expression | Status Identity | 
<col>
| 
<col>
| Supported hour | 
hour(<col>)
| 
date_trunc('hour', <col>)
| Supported day | 
day(<col>)
| 
date_trunc('day', <col>)
| Supported month | 
month(<col>)
| 
date_trunc('month', <col>)
| Supported year | 
year(<col>)
| 
date_trunc('year', <col>)
| Supported bucket | 
bucket(<col>, <n>)
| N/A | Not Supported truncate | 
truncate(<col>)
| N/A | Not Supported Why this is a problem Because StarRocks cannot align its internal partitions with the Iceberg 
bucket
partitions, you are forced to create the Materialized View as unpartitioned. As discussed previously, this leads to: 1. Full Refreshes Only: StarRocks cannot perform incremental refreshes and must scan the entire Iceberg table every time. 2. OOM Risks: Large unpartitioned scans are processed as a single massive transaction, which is more likely to hit memory limits. Possible Workarounds 1. Add a "Virtual" Partition to Iceberg: If you have control over the Iceberg table, adding a higher-level partition (like a date or month) would allow you to align the MV to that column instead of the bucket. 2. Manual Refresh Logic: If you know which data has changed, you could technically use a synchronous MV or a standard table with a manual 
INSERT INTO ... SELECT
script, but you would lose the "transparent rewrite" benefits of asynchronous MVs. 3. Strict Resource Management: If you must stay unpartitioned, ensure you have spilling enabled (
"session.enable_spill" = "true"
) and enough query memory allocated to the MV's resource group to handle the full scan. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky what would be a use case of identity partition
r
The Identity partition transform is used when you want a one-to-one mapping between a column in your base table (e.g., Iceberg) and the partition in your Materialized View (MV). Unlike 
days()
or 
months()
which transform a timestamp into a coarser grain, Identity uses the raw value of the column as the partition key. Key Use Cases 1. Enabling Incremental Refresh for Non-Time Columns The most common use case is when your base table is partitioned by a discrete category rather than time. * Scenario: An Iceberg table partitioned by 
region_id
or 
tenant_id
. * MV setup: 
PARTITION BY (region_id)
using the Identity mapping. * Benefit: If data for 
region_id = 5
changes in Iceberg, StarRocks detects this change at the partition level and only refreshes that specific region in the MV. Without this, it would have to re-scan the entire table. 2. Multi-Column Partition Alignment (v3.5+) If your Iceberg table is partitioned by multiple columns (e.g., 
event_date
and 
country_code
), you can use Identity partitions to align the MV to that exact structure. * Scenario: 
PARTITIONED BY (days(ts), country_code)
in Iceberg. * MV setup: 
PARTITION BY (date_trunc('day', ts), country_code)
. * Benefit: This creates a granular refresh strategy where only specific "day + country" combinations are updated, significantly saving resources. 3. Data Lake Acceleration with Native Indexes You use Identity partitions to bring data from a data lake into StarRocks while keeping the same organizational structure, which then allows you to apply StarRocks-specific optimizations. * Scenario: Mapping a large Hive/Iceberg table to an MV. * Benefit: Once the data is in the partitioned MV (stored as a StarRocks native table), you can add Bitmap Indexes, Bloom Filter Indexes, or use Colocate Join groups on those partition columns—none of which are available directly on the data lake. 4. Handling High-Cardinality "ID" Partitions If you have a column like 
batch_id
or 
load_date_string
that is already a string/integer and correctly partitions your data, you use Identity to avoid unnecessary function calls (like 
date_trunc
). Comparison to your "Bucket" issue The Identity partition is the "fix" for your current OOM problem, provided you can change the underlying Iceberg table. * Bucket Partition (Your current issue): 
bucket(id, 5)
is a hash. StarRocks doesn't know which rows belong to which bucket without re-calculating the hash for the whole table. * Identity Partition: If you changed the Iceberg table to be partitioned by 
id
(if cardinality is low enough) or a different column like 
category_id
, StarRocks would see the folders in the data lake and know exactly which ones to refresh. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
k
@Rocky will there be support for partitioning by hash(id)
r
As of the current stable releases (including the latest v3.x and v4.0 versions), there is no official support for aligning a Materialized View's partitions with an Iceberg table using the 
bucket()
or 
truncate()
transforms. 1. Partitioning vs. Bucketing in StarRocks It is important to distinguish between how StarRocks uses these terms: * Partitioning (
PARTITION BY
): This is the logical level where StarRocks tracks data changes for MVs. Currently, this only supports Identity (direct column mapping) or Time-based (
date_trunc
) transforms. * Bucketing/Distribution (
DISTRIBUTED BY HASH
): This is the physical sharding of data within a partition. You can (and should) use 
DISTRIBUTED BY HASH(id)
for your MV, but this only affects how data is stored on disk; it does not help StarRocks perform an incremental/partitioned refresh based on your Iceberg buckets. 2. Is it on the roadmap? While StarRocks is rapidly improving Iceberg integration (v4.0 recently introduced better handling of Iceberg "hidden partitions" and metadata statistics), the ability to use 
bucket()
as a partitioning key for an MV is not yet available. The complexity lies in the fact that StarRocks' internal partitioning engine is based on Range or List values. A hash-based bucket from Iceberg doesn't map cleanly to these types without StarRocks implementing a specific "Hash Partition" type for MVs, which is currently a feature request in the community. 3. What you can do now Since you cannot align by bucket, your MV will remain "unpartitioned" from a refresh perspective. To prevent the OOM issues you're seeing: 1. Use `DISTRIBUTED BY HASH(id)`: While this doesn't help the refresh logic, it ensures the data is evenly spread across your BE nodes once it is loaded. 2. Enable Spilling: As mentioned before, ensure 
session.enable_spill = true
is set in the MV properties. 3. Partition by a different column: If your Iceberg table has any other column that is an Identity or Date type (even if it's not the primary way you query), partitioning the MV by that column will force StarRocks to process the table in smaller chunks rather than one giant unpartitioned blob. 4. Monitor v4.x Releases: The community is actively working on "Hidden Partition" support. You can track progress on the StarRocks GitHub. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • best_practices/bucketing.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md
k
@Rocky is it possible to create a partitioned mv based on adding a column in the select statement to create a value as an identity column (e.g. select id modulo 5 as partition_num)
r
No, it is not possible to create a partitioned Materialized View (MV) using a custom expression like 
id % 5
as the partition key. There are three main reasons why this approach will not work in StarRocks: 1. Partition Alignment Requirement In StarRocks, a partitioned MV is only "partitioned" if its partitions align with the partitions of the base table (in your case, the Iceberg table). This alignment is what allows StarRocks to: * Identify which specific partitions have changed in the base table. * Refresh only the corresponding partitions in the MV (Incremental Refresh). Since your Iceberg table is partitioned by 
bucket(id)
and StarRocks does not currently support the 
bucket
transform for MV alignment, it cannot "link" the metadata of the Iceberg buckets to a custom column like 
id % 5
. 2. Supported Partition Expressions The 
PARTITION BY
clause in a 
CREATE MATERIALIZED VIEW
statement only supports a limited set of functions designed for partition alignment, specifically: * Time-based: 
date_trunc()
, 
time_slice()
. * Type conversion: 
str2date()
(used when the base table has string dates). * Identity: Directly using the base table's partition column. Arbitrary mathematical expressions like modulo (
%
) or hash functions are not supported within the 
PARTITION BY
clause of an MV. 3. Base Table Constraint StarRocks requires that the column used in the MV's 
PARTITION BY
clause must be a partition column in the base table. * If you create a new column in your 
SELECT
statement (e.g., 
SELECT id % 5 AS part_num
), that column 
part_num
is considered a computed column, not a partition column of the base table. * StarRocks will fail to create the MV, typically returning an error stating that the partition relationship cannot be mapped. Recommendation If your goal is to avoid the memory/performance issues of an unpartitioned MV: 1. Change the Iceberg Partitioning: If possible, add a 
day
or 
month
partition to the Iceberg table. You can then use 
PARTITION BY date_trunc('day', your_date_col)
in the MV. 2. Use Bucketing for Distribution: While you cannot partition by 
id % 5
, you should still use 
DISTRIBUTED BY HASH(id)
in the MV definition. This ensures that even if the MV is refreshed as one large task, the data is physically distributed across your nodes efficiently for future queries. References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md References
k
@Rocky
Copy code
026-09-19 15:47:19.128Z WARN (pool-68-thread-1|167) [RpcTimerTask.run():73] correlationId:15438 timeout with bound channel =>[id: 0xeaa8aac1, L:/10.0.71.99:53688 - R:/10.0.58.20:8060]
 2026-09-19 15:47:19.128Z WARN (starrocks-mysql-nio-pool-26|4053) [FragmentInstanceExecState.waitForDeploymentCompletion():299] catch a execute exception
 java.util.concurrent.ExecutionException: A error occurred: errorCode=62 errorMessage:method request time out, please check 'onceTalkTimeout' property. current value is:60000(MILLISECONDS) correlationId:15438 timeout with bound channel =>[id: 0xeaa8aac1, L:/10.0.71.99:53688 - R:/10.0.58.20:8060]
	at com.baidu.jprotobuf.pbrpc.client.ProtobufRpcProxy$2.get(ProtobufRpcProxy.java:578)
	at com.starrocks.qe.scheduler.dag.FragmentInstanceExecState.waitForDeploymentCompletion(FragmentInstanceExecState.java:291)
	at com.starrocks.qe.scheduler.Deployer.waitForDeploymentCompletion(Deployer.java:326)
	at com.starrocks.qe.scheduler.Deployer.deployFragments(Deployer.java:213)
	at com.starrocks.qe.scheduler.dag.AllAtOnceExecutionSchedule.schedule(AllAtOnceExecutionSchedule.java:149)
	at com.starrocks.qe.DefaultCoordinator.deliverExecFragments(DefaultCoordinator.java:688)
	at com.starrocks.qe.DefaultCoordinator.startScheduling(DefaultCoordinator.java:581)
	at com.starrocks.qe.scheduler.Coordinator.execWithQueryDeployExecutor(Coordinator.java:108)
	at com.starrocks.qe.StmtExecutor.handleQueryStmt(StmtExecutor.java:1948)
	at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:1073)
	at com.starrocks.qe.ConnectProcessor.executeQueryAttempt(ConnectProcessor.java:664)
	at com.starrocks.qe.ConnectProcessor.runWithParserStageRetry(ConnectProcessor.java:553)
	at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:490)
	at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:899)
	at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:1298)
	at com.starrocks.mysql.nio.MySQLReadListener.handleRequest(MySQLReadListener.java:152)
	at com.starrocks.mysql.nio.MySQLReadListener.lambda$handleEvent$0(MySQLReadListener.java:92)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: com.baidu.jprotobuf.pbrpc.ErrorDataException: A error occurred: errorCode=62 errorMessage:method request time out, please check 'onceTalkTimeout' property. current value is:60000(MILLISECONDS) correlationId:15438 timeout with bound channel =>[id: 0xeaa8aac1, L:/10.0.71.99:53688 - R:/10.0.58.20:8060]
	at com.baidu.jprotobuf.pbrpc.client.ProtobufRpcProxy.doWaitCallback(ProtobufRpcProxy.java:651)
	at com.baidu.jprotobuf.pbrpc.client.ProtobufRpcProxy.access$0(ProtobufRpcProxy.java:611)
	at com.baidu.jprotobuf.pbrpc.client.ProtobufRpcProxy$2.get(ProtobufRpcProxy.java:576)
	... 19 more
2026-09-19 15:47:19.128Z WARN (starrocks-mysql-nio-pool-26|4053) [FragmentInstanceExecState.waitForDeploymentCompletion():325] exec plan fragment failed, errmsg=exec rpc error. backend [id=174100] [host=starrocks-etl-cn-1.starrocks-etl-cn-search.starrocks-etl.svc.cluster.local], code=THRIFT_RPC_ERROR, fragmentId=F01, backend=starrocks-etl-cn-1.starrocks-etl-cn-search.starrocks-etl.svc.cluster.local:9060
 2026-09-19 1
r
The log indicates that your Frontend (FE) timed out while trying to deploy query fragments to a Compute Node (
starrocks-etl-cn-1
). Specifically, the FE waited 60 seconds (the default value) for the node to acknowledge the query plan, but received no response. 1. Root Cause The error 
errorCode=62 errorMessage:method request time out
comes from the underlying RPC library. In StarRocks, this happens when the FE's 
Deployer
cannot successfully send the execution plan to a Backend/Compute Node (BE/CN) within the configured limit. The specific parameter controlling this in the FE is 
brpc_send_plan_fragment_timeout_ms
, which defaults to 
60000
(60 seconds). 2. Common Reasons * High BE/CN Load: If the Compute Node is under extreme CPU pressure or experiencing long Garbage Collection (GC) pauses, it may not be able to process the incoming RPC request in time. * Large Query Plan: If the SQL query is extremely complex (e.g., thousands of partitions, massive 
IN
clauses, or hundreds of joins), the serialized "plan fragment" becomes very large. Sending and deserializing this large plan can exceed the 60s timeout. * Network Congestion: Transient network latency or packet loss between the FE and the CN. * RPC Thread Pool Exhaustion: The BE/CN may have run out of available service threads to handle new incoming requests. 3. Recommended Actions Immediate Fix: Increase Timeout If your queries are complex or your network is slow, you can increase the timeout on the FE. This is a dynamic configuration:
Copy code
sql
-- Increase to 120 seconds
ADMIN SET FRONTEND CONFIG ("brpc_send_plan_fragment_timeout_ms" = "120000");
Troubleshooting the Node Check the health of the node mentioned in the log (
10.0.58.20
): 1. Check Resources: Monitor CPU and Memory usage on 
starrocks-etl-cn-1
. If CPU is consistently at 100%, query deployment will likely fail. 2. Check BE/CN Logs: Look at 
<http://be.INFO|be.INFO>
or 
be.warn.log
on that specific node around 
2026-09-19 15:47:19
to see if there are "heartbeat timeout" or "process busy" warnings. 3. Check Connectivity: Ensure there is no firewall or MTU issue between the FE (
10.0.71.99
) and CN (
10.0.58.20
) on the RPC port (default 
8060
). Optimize the Query If this only happens with one specific query: * Check if the query is touching a massive number of partitions. * Simplify the query or use a Materialized View to reduce the plan complexity. References • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/BE_configuration.md • sql-reference/System_variable.md References • administration/management/FE_configuration.md • administration/management/BE_configuration.md
k
@Rocky my nodes are healthy, i simply put pods onto new larger nodes (r8g.16xlarge) and this error is occurring
r
The fact that this started occurring specifically after moving to Graviton4 (
r8g
) nodes suggests that the issue is likely related to the architecture shift or the network configuration of these new Nitro-based instances, rather than the "health" of the nodes themselves. Here are the three most likely causes and how to fix them: 1. Architecture Mismatch (Emulation Slowdown) The 
r8g
instances use the ARM64 architecture. If you moved your existing Kubernetes pods to these nodes without changing the Docker image, they might be running the x86_64 version of StarRocks via QEMU/binfmt_misc emulation. * The Symptom: Emulation typically results in a 10x–20x performance penalty. An RPC call that usually takes 5ms now takes 100ms; a complex query plan deployment that takes 5 seconds now takes over 60 seconds, triggering the 
errorCode=62
timeout. * How to check: Run 
uname -m
inside one of your StarRocks pods. If it says 
x86_64
while running on an 
r8g
node, you are using emulation. * The Fix: Ensure you are using the ARM64 specific StarRocks images. In your Docker registry or Helm chart, switch to images tagged with 
-arm64
(e.g., 
starrocks/fe-ubuntu:3.x.x-arm64
). 2. Network MTU / Nitro Enclaves Issues The 
r8g
instances are built on the latest AWS Nitro System. If your new nodes are in a different subnet or VPC with different MTU (Maximum Transmission Unit) settings, large query plans (which can be several megabytes) may be getting fragmented or dropped. * The Symptom: Small queries work, but complex queries (large plans) consistently time out at 60s. * How to check: Check if jumbo frames (MTU 9001) are enabled on your AWS VPC and that the nodes/network interfaces support it. * The Fix: * Ensure the MTU is consistent across FE and CN/BE nodes. * If you suspect large plans are the issue, you can temporarily increase the FE timeout to see if it eventually succeeds:
Copy code
sql
        ADMIN SET FRONTEND CONFIG ("brpc_send_plan_fragment_timeout_ms" = "120000");
3. JProtobuf / JNI Compatibility The stack trace shows the error occurring in 
com.baidu.jprotobuf.pbrpc
, which is the Java-to-C++ RPC bridge StarRocks uses. This library uses native JNI code to handle serialization. * The Issue: Older versions of the StarRocks FE might be bundled with JNI libraries that are not optimized for Graviton4 or have issues with the specific instruction set of the 
r8g
(Neoverse-V2 cores). * The Fix: * If you are on an older version of StarRocks (e.g., early v2.5 or v3.0), upgrade to a more recent point release (v3.1.x, v3.2.x, or v3.3.x). Recent versions have significant stability improvements for Graviton/ARM environments. Summary Checklist 1. Verify Binary: Are you running the 
arm64
native image? (Most likely cause). 2. Increase Timeout: Set 
brpc_send_plan_fragment_timeout_ms
to 
120000
or higher to see if the query plan ever deploys. 3. Check BE Logs: Check the 
<http://be.INFO|be.INFO>
logs on the target node (
10.0.58.20
) to see if it reports "receive plan fragment" at all, or if it shows high CPU/GC activity. References • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/BE_configuration.md • sql-reference/System_variable.md References • administration/management/FE_configuration.md • faq/operation_maintenance_faq.md • administration/management/BE_configuration.md
k
@Rocky all FE queries like show MATERIALIZED views takes 60 sec
r
If simple metadata queries like 
SHOW MATERIALIZED VIEWS
are taking exactly 60 seconds (the default timeout), it indicates a fundamental communication or architectural failure between your FE and BE/CN nodes following the migration to Graviton4 (
r8g
) instances. Because this happened immediately after moving to 
r8g.16xlarge
, the cause is almost certainly one of the following three: 1. Architecture Mismatch (Most Likely) The 
r8g
instances are ARM64 (Graviton4). If your Docker image or binary is 
x86_64
, it is running under QEMU emulation. * The Problem: Emulation is extremely slow (often 10x-50x slower). A metadata query that takes 200ms natively will take 10-20 seconds; any query requiring BE coordination will easily hit the 60s timeout. * Verification: Run 
uname -m
inside the FE and BE pods. If it returns 
x86_64
while running on an 
r8g
node, you are in emulation. * Fix: You must use the ARM-specific images (e.g., 
starrocks/fe-ubuntu:3.x.x-arm64
). 2. Zombie Nodes or IP Mismatches When moving pods to new nodes, the Frontend often retains the old IPs of the Backends in its metadata. * The Problem: When you run 
SHOW MATERIALIZED VIEWS
, the FE attempts to reach every BE to check its status or data size. If it tries to reach an old IP that no longer exists, it will hang for exactly 60 seconds (
brpc_send_plan_fragment_timeout_ms
) before returning an error or a partial result. * Verification: Run 
SHOW BACKENDS;
. * Check the 
IP
column. Are these the current IPs of your pods? * Check the 
Alive
column. If any are 
false
, the FE is timing out trying to talk to them. * Fix: Drop the old BE/CN nodes using 
ALTER SYSTEM DROP BACKEND "old_ip:port";
and add the new ones. Ensure 
priority_networks
in your 
fe.conf
and 
be.conf
is updated to match the new VPC subnet. 3. Security Group / Port Blocking The 
r8g
instances likely belong to a new AWS Launch Template or Security Group. * The Problem: StarRocks requires several ports to be open between all nodes (FE-to-FE, FE-to-BE, and BE-to-BE). If the FE cannot reach the BE's Heartbeat port (
9050
) or RPC port (
8060
), the FE metadata threads will hang. * Verification: Try to 
telnet <BE_IP> 8060
from the FE pod. If it hangs, it’s a network/firewall issue. * Fix: Ensure the Security Group allows all traffic between nodes on these ports: * FE: 9010, 9020, 9030 * BE/CN: 9050, 8060, 9060 Recommended Action Plan 1. Run 
SHOW BACKENDS;
 — This is the fastest way to see if the FE can "see" the workers. If they are 
Alive: false
, it's a network/IP issue. 2. Verify the Architecture — Ensure your pod is running the 
-arm64
version of StarRocks. 3. Check FE Logs (
fe.warn.log
) — Look for 
HeartbeatMgr
errors. It will explicitly say "failed to send heartbeat to 10.x.x.x". References • administration/management/FE_configuration.md • faq/Deploy_faq.md • administration/management/BE_configuration.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEWS.md • deployment/helm.md References • administration/management/FE_configuration.md • faq/Deploy_faq.md • administration/management/BE_configuration.md • sql-reference/sql-statements/cluster-management/backend_node/SHOW_BACKENDS.md
k
@Rocky this was a simple upgrade from r8g.2xlarge to r8g.16xlarge
r
The fact that metadata queries like 
SHOW MATERIALIZED VIEWS
are taking exactly 60 seconds after a vertical scale-up (2xl to 16xl) is a strong indicator that the Frontend (FE) is trying to communicate with a node that is no longer reachable or is not responding. In StarRocks, 60,000ms (60 seconds) is the default value for 
brpc_send_plan_fragment_timeout_ms
. Although 
SHOW
commands are metadata-driven, several of them (especially for Materialized Views) trigger background checks or status updates from the Backends (BE) or Compute Nodes (CN) to report row counts and refresh states. Here is why this is likely happening on your new 
r8g.16xlarge
nodes: 1. Stale IP Addresses (Most Likely) When you moved your pods to new, larger nodes, they likely received new internal IP addresses. * The Issue: If the FE still has the old IPs registered for the BE/CN nodes in its metadata, it will attempt to reach them for status updates (like row counts for the MV) and wait exactly 60 seconds for the RPC to time out. * How to fix: 1. Run 
SHOW BACKENDS;
or 
SHOW COMPUTE NODES;
. 2. Check the 
IP
column and the 
Alive
column. 3. If 
Alive
is 
false
or the IPs don't match your current pods, you must drop the old nodes and add the new ones:
Copy code
sql
        -- Drop old
        ALTER SYSTEM DROP COMPUTE NODE "old_ip:9050";
        -- Add new
        ALTER SYSTEM ADD COMPUTE NODE "new_ip:9050";
2. Security Group / Port Blocking The 
r8g.16xlarge
instances may be using a different Security Group or have different rules than your previous 
2xlarge
instances. * The Issue: StarRocks requires open communication on several ports (Heartbeat: 9050, RPC: 8060, Thrift: 9060). If the FE can't reach the BEs on these ports, the metadata threads will hang for the duration of the timeout. * Check: From an FE pod, try to 
telnet <BE_IP> 8060
. If it times out, your AWS Security Group is blocking the traffic. 3. FE Leader Forwarding If you have multiple FEs, all metadata-modifying or complex 
SHOW
queries are forwarded to the Leader FE. * The Issue: If the Follower FE cannot reach the Leader FE (due to the same IP/Network issues mentioned above), the query will hang for 60s. * Check: Run 
SHOW FRONTENDS;
and ensure the Leader is 
Alive
and reachable from the node you are connected to. 4. ENA Express / MTU Mismatch The 
r8g.16xlarge
instance supports ENA Express and much higher network throughput. * The Issue: If your VPC has a specific MTU (e.g., 9001 for Jumbo Frames) and the new nodes are defaulting to a different MTU (or if ENA Express is causing packet fragmentation for large metadata payloads), RPC calls can hang. * Fix: Ensure the MTU is consistent (usually 9001 on AWS) across all nodes in the cluster. 5. Accidental Architecture Switch (x86 vs ARM64) Even though you were already on Graviton (
r8g
), verify that the container image used for the redeploy is the ARM64 native version. * Check: Run 
uname -m
inside the pod. If it shows 
x86_64
, the node is using QEMU emulation to run the code, which is slow enough to cause 60s timeouts on almost any task. You should be using the image tagged with 
-arm64
. Immediate Troubleshooting Step: Run 
SHOW BACKENDS;
right now. If any rows show 
Alive: false
, that is the source of your 60-second delay. The FE is waiting for those "dead" nodes to respond before giving up.
k
@Rocky im getting this error while creating an mv with 1024 buckets in shared data mode: Query 1 ERROR at Line 24: : fail to create tablet: 199265: [Internal error: starlet err grpc.GetShard(shardId=222493) error: Deadline Exceeded]
r
This error occurs because creating 1024 buckets in shared-data mode requires the system to initialize 1024 separate tablets (shards) in object storage (S3) and register them in the StarOS metadata service (running on the FE). The "Deadline Exceeded" on 
grpc.GetShard
indicates that the internal gRPC communication between your BE/CN and the FE's StarOS service is timing out before all 1024 shards can be processed. 1. Increase StarOS/Starlet gRPC Timeout The default timeout for internal StarOS metadata calls is often too short for high-bucket-count operations. Increase this on your FE nodes:
Copy code
sql
-- Increase the StarMgr gRPC timeout (Default is usually 5 or 10s)
ADMIN SET FRONTEND CONFIG ("starmgr_grpc_timeout_seconds" = "30");

-- Also increase the general tablet creation timeout
ADMIN SET FRONTEND CONFIG ("tablet_create_timeout_second" = "30");
2. Increase BE/CN Parallelism Since you have moved to large 
r8g.16xlarge
nodes, your BEs can handle more parallel tablet creation tasks. Increase the worker count in 
be.conf
(requires a restart of BE/CN):
Copy code
conf
# be.conf or cn.conf
create_tablet_worker_count = 16
3. Review Bucket Count (Recommendation) 1024 buckets for a single Materialized View is very high unless your data volume per partition is in the multi-terabyte range. * The Problem: In shared-data mode, every bucket corresponds to a file metadata operation in S3. 1024 buckets means every refresh or query must manage 1024+ files, which introduces high metadata overhead and latency. * The Fix: Unless you have hundreds of Terabytes in this MV, consider reducing the bucket count to a smaller number (e.g., 64, 128, or 256). StarRocks performance is often better with fewer, larger tablets (aim for 1GB–10GB per tablet) rather than many tiny ones. 4. Check for StarOS Worker Thread Exhaustion With 16xlarge nodes, you might be hitting the thread limit on the FE for StarOS requests. Check the FE config:
Copy code
sql
-- Check current value
ADMIN SHOW FRONTEND CONFIG LIKE '%starmgr_grpc_server_max_worker_threads%';

-- If it is low (e.g., 256), increase it to 1024
ADMIN SET FRONTEND CONFIG ("starmgr_grpc_server_max_worker_threads" = "1024");
Summary of what happened When you scaled up to 
r8g.16xlarge
, the system became capable of pushing much higher concurrency. When you triggered the MV creation with 1024 buckets, the BEs flooded the FE with shard creation requests. The FE's StarOS service couldn't acknowledge the 
GetShard
requests within the default 5-10 second window, leading to the 
Deadline Exceeded
error. Try setting 
starmgr_grpc_timeout_seconds
to 30 and reducing the bucket count to 256 if possible. References • faq/shared_data_faq.md • administration/management/FE_configuration.md • administration/management/BE_configuration.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • faq/shared_data_faq.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • deployment/shared_data/azure.md • deployment/shared_data/minio.md • deployment/shared_data/s3.md
k
@Rocky if i have nvme volumes, does query join spill go to nvme voumes
r
Join spill (and other operator spills like Sort or Aggregate) does not automatically go to your NVMe volumes unless you explicitly configure StarRocks to use those paths. If you have fast local NVMe storage on your 
r8g
(or 
r8gd
) instances, you should definitely use it for spilling, as join spills are extremely I/O intensive. How to configure it To ensure spills use your NVMe volumes, follow these steps: 1. Identify your NVMe Mount Point Ensure your NVMe volume is formatted and mounted (e.g., at 
/mnt/nvme0/
). 2. Update 
be.conf
(or 
cn.conf
) Add or update the following parameter to point to your NVMe directory:
Copy code
properties
# Separate multiple paths with semicolons if you have multiple volumes
spill_local_storage_dir=/mnt/nvme0/starrocks_spill
_Note: If you are using an older version (pre-v3.0), the parameter was 
query_scratch_dirs
, but 
spill_local_storage_dir
is the standard for current versions._ 3. Restart the BE/CN You must restart the Backend or Compute Node for this configuration to take effect. 4. Enable Spilling in your Session Even with the path configured, spilling is disabled by default to protect performance. You must enable it for your queries:
Copy code
sql
SET enable_spill = true;
-- Optional: 'auto' triggers spill when memory is nearly full
SET spill_mode = 'auto';
Why NVMe is recommended * Throughput: Join spilling involves writing and reading back massive amounts of intermediate hash table data. NVMe provides the high IOPS and throughput required to prevent the query from stalling. * Isolation: The official documentation strongly recommends using a different disk for spilling than the one used for your primary data (
storage_root_path
). This prevents spill I/O from slowing down standard data scans. Summary If your NVMe is just "present" on the OS but not specified in 
be.conf
, StarRocks will likely spill to its default installation directory (usually on the OS drive/EBS), which will be significantly slower. References • administration/management/BE_configuration.md • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md References • administration/management/BE_configuration.md • administration/management/resource_management/spill_to_disk.md • sql-reference/System_variable.md
k
@Rocky do backend nodes for shared nothing keep cache of data when querying remote s3 iceberg tables
r
Yes, in a Shared-Nothing architecture, Backend (BE) nodes can and do keep a cache of data from remote S3 Iceberg tables to accelerate queries. This is handled by a feature called Data Cache (formerly known as Block Cache). Here is how it works for your setup: 1. Data Cache (Block Cache) Even in Shared-Nothing mode, StarRocks treats remote S3 Iceberg data as "cold" and uses the local BE disks to cache "hot" blocks. * Mechanism: When you query an Iceberg table, the BE fetches the required blocks (from Parquet/ORC files) from S3. These blocks are simultaneously written to the local disk of the BE that processed that part of the query. * Subsequent Queries: Future queries for the same data will read directly from the local BE disk, drastically reducing S3 I/O latency. * Default Status: Starting from v3.3, 
datacache_enable
is set to 
true
by default. 2. Iceberg-Specific Metadata Caching In addition to the raw data blocks on the BE, StarRocks also caches Iceberg metadata (manifests, snapshots, etc.) to speed up query planning: * FE Memory/Disk Cache: The Frontend (FE) caches Iceberg metadata in a two-level system (Memory + Local Disk). * Asynchronous Refresh: StarRocks can automatically poll the Iceberg Metastore (Glue/Hive) to refresh this cache in the background so planning stays fast even as the table changes. 3. Key Configurations for your BEs If you are using 
r8g.16xlarge
nodes with NVMe, you should ensure the cache is utilizing those disks for maximum performance: * 
storage_root_path
: In 
be.conf
, this defines where the cache is stored. Point this to your NVMe mount. * 
datacache_disk_size
: You can limit how much disk space is used for the cache (e.g., 
datacache_disk_size = 500G
). * 
datacache_mem_size
: You can also allocate a portion of BE RAM to cache data blocks in memory for even faster access (default is usually 10% of total memory). 4. How to verify it's working You can check if your queries are hitting the cache by looking at the Query Profile. Run your query, then run 
get_query_profile('query_id')
and look for these metrics: * `DataCacheReadBytes`: How much data was read from the local disk cache. * `DataCacheWriteBytes`: How much data was fetched from S3 and written to the cache. Tip: If you see 
DataCacheReadBytes
increasing on the second run of the same query, the cache is working correctly. References • using_starrocks/caching/block_cache.md • administration/management/BE_configuration.md • data_source/data_cache.md • data_source/catalog/iceberg/iceberg_catalog.md
k
@Rocky can we move materialzied views from one database to another easily (default_catalog)
r
Moving a Materialized View (MV) from one database to another in StarRocks (even within the 
default_catalog
) is not supported via a single 
RENAME
or 
SWAP
command. In StarRocks, the 
RENAME
and 
SWAP WITH
operations for both tables and materialized views are restricted to the same database. If you attempt to rename an MV into a different database (e.g., 
ALTER MATERIALIZED VIEW db1.mv1 RENAME db2.mv1
), the operation will fail. Here are the three ways to achieve this, depending on whether you want to keep the existing data: Method 1: Backup and Restore (Best for keeping data) If your MV is large and you do not want to trigger a full refresh (rebuild), you can use the StarRocks Backup/Restore feature to move the physical data tablets to a new database. 1. Create a repository (S3, HDFS, etc.) if you don't have one. 2. Back up the MV in the source database:
Copy code
sql
    BACKUP DATABASE source_db SNAPSHOT mv_snapshot ON (MATERIALIZED VIEW my_mv);
3. Restore to the target database:
Copy code
sql
    RESTORE SNAPSHOT mv_snapshot FROM your_repo DATABASE target_db ON (MATERIALIZED VIEW my_mv);
Method 2: Drop and Recreate (Simplest, but requires refresh) If you don't mind the time it takes to re-calculate the data, this is the most straightforward approach. 1. Get the DDL:
Copy code
sql
    SHOW CREATE MATERIALIZED VIEW source_db.my_mv;
2. Run the DDL in the target database:
Copy code
sql
    USE target_db;
    -- Paste the DDL here
3. Drop the old one:
Copy code
sql
    DROP MATERIALIZED VIEW source_db.my_mv;
Method 3: Cross-Cluster Data Migration Tool If you are moving many objects or migrating between clusters, the official StarRocks Data Migration Tool supports synchronizing MV schemas and building statements, though it generally does not synchronize the physical data of the MV itself (you would still need to refresh). Summary of Restrictions * 
RENAME
: Only changes the name within the current database. * 
SWAP WITH
: Atomic exchange only works if both MVs reside in the same database. * Permissions: You will need 
CREATE MATERIALIZED VIEW
privileges on the target database and 
SELECT
privileges on all base tables involved in the MV definition. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • administration/management/Backup_and_restore.md • knowledge/usage/best-practice.md (Section 128: SWAP WITH does not support cross-db) References • administration/management/Backup_and_restore.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • administration/data_migration_tool.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/SHOW_ALTER_MATERIALIZED_VIEW.md • sql-reference/sql-statements/table_bucket_part_index/ALTER_TABLE.md
k
@Rocky how to give same grants from exsting user to new user
r
In StarRocks, there is no single "CLONE USER" command. To give the same grants from an existing user to a new user, you have two primary methods: the manual method using 
SHOW GRANTS
and the recommended method using Roles. Method 1: Manual Copy (Quickest for one-time) You can extract the exact SQL commands used for the existing user and run them for the new user. 1. View existing grants:
Copy code
sql
    -- Replace 'old_user'@'%' with the actual user identity
    SHOW GRANTS FOR 'old_user'@'%';
2. Copy and Execute: The output will show a list of statements in the 
Grants
column (e.g., 
GRANT SELECT ON DATABASE db1 TO USER 'old_user'@'%'
). * Copy these statements. * Change the username to your 
'new_user'@'%'
. * Run the modified statements. Method 2: Role-Based (Best Practice) Instead of granting permissions directly to users, you should grant them to a Role. This makes it easy to add more users with the same permissions later. 1. Create a Role:
Copy code
sql
    CREATE ROLE my_common_role;
2. Grant privileges to the Role: Instead of individual users, point your grants to the role:
Copy code
sql
    GRANT SELECT, INSERT ON ALL TABLES IN DATABASE mydb TO ROLE my_common_role;
3. Assign the Role to both users:
Copy code
sql
    GRANT my_common_role TO USER 'old_user'@'%';
    GRANT my_common_role TO USER 'new_user'@'%';
Note: You may also want to set it as the default role: 
ALTER USER 'new_user'@'%' DEFAULT ROLE my_common_role;
Bonus: Inspecting via System Tables If you want to see all user privileges in a table format (useful for scripting or auditing), you can query the system views:
Copy code
sql
SELECT * FROM sys.grants_to_users WHERE GRANTEE = "'old_user'@'%'";
Important Notes * Admin Rights: You must have the 
user_admin
role to view other users' grants or create new roles. * Impersonation: If you specifically want one user to be able to "act" as another without copying all permissions, you can use: 
GRANT IMPERSONATE ON USER 'old_user'@'%' TO USER 'new_user'@'%';
References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sys/grants_to_users.md • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/GRANT.md References • sql-reference/sql-statements/account-management/SHOW_GRANTS.md • sql-reference/sys/grants_to_users.md • sql-reference/sql-statements/account-management/SHOW_AUTHENTICATION.md • sql-reference/sys/grants_to_roles.md
k
@Rocky how to grant usage and select on all materialzeid views in all databases on default_catalog;
r
To grant privileges on all materialized views (MVs) across all databases in the 
default_catalog
, you need to combine two types of grants: 
USAGE
on the catalog itself and 
SELECT
on the materialized views. In StarRocks, the 
USAGE
privilege is not applied directly to materialized views; instead, it is required at the Catalog level to allow the user to access any objects within that catalog. Step-by-Step Commands Run the following as a user with 
user_admin
or 
root
privileges: 1. Switch to the target catalog (optional if already in 
default_catalog
):
Copy code
sql
    SET CATALOG default_catalog;
2. Grant usage on the catalog: This allows the user to "see" and enter the 
default_catalog
.
Copy code
sql
    GRANT USAGE ON CATALOG default_catalog TO USER 'your_user'@'%';
3. Grant select on all materialized views: This allows the user to query any existing and future materialized views in all databases within that catalog.
Copy code
sql
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO USER 'your_user'@'%';
Key Details * Scope: 
ALL MATERIALIZED VIEWS IN ALL DATABASES
is a global-style grant that automatically applies to MVs created in the future. * Privilege Names: Materialized views support 
SELECT
, 
ALTER
, 
REFRESH
, and 
DROP
. There is no specific 
USAGE
privilege for an MV object; 
SELECT
is what allows a user to query it. * Roles: It is highly recommended to grant these to a Role instead of a user for easier management:
Copy code
sql
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO ROLE your_role;
References • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md • using_starrocks/async_mv/Materialized_view.md References • sql-reference/sql-statements/account-management/GRANT.md • data_source/catalog/hive_catalog.md • administration/user_privs/authorization/User_privilege.md
k
@Rocky how to set default catalog and datbase for a role
r
In StarRocks, it is not possible to set a default catalog or database directly for a Role. Roles are designed strictly as containers for privileges and other roles. They do not store session-level configuration state such as "default context." To achieve this behavior, you must apply the setting to the User or the Connection. Here are the two ways to handle this: 1. Set for the User (Recommended for v3.3.3+) Starting from StarRocks v3.3.3, you can set default session properties (including catalog and database) for a specific user. When that user logs in, these settings take effect regardless of which roles they have active. Use the 
ALTER USER
command (note that this uses the 
username
, not the 
user_identity
like 
user@'%'
):
Copy code
sql
-- Set default catalog and database for user 'jack'
ALTER USER 'jack' SET PROPERTIES (
    "catalog" = "external_hive_catalog", 
    "database" = "analytics_db"
);
To clear these properties later:
Copy code
sql
ALTER USER 'jack' SET PROPERTIES ("catalog" = "", "database" = "");
2. Set via Connection String (JDBC/MySQL) If you want different users of the same role to land in specific databases, the most common practice is to define the catalog and database in the client’s connection string. * MySQL Client:
Copy code
bash
    mysql -h <FE_IP> -P 9030 -u <username> -p -D default_catalog.my_db
* JDBC String:
Copy code
text
    jdbc:mysql://<FE_IP>:9030/default_catalog.my_db
3. Using Roles for Activation While roles don't hold the "default database," they do control whether a user is allowed to access that database. If you want a user to automatically have the permissions associated with a specific role (which might be tied to a specific database) upon login, you use:
Copy code
sql
SET DEFAULT ROLE 'my_admin_role' TO 'jack'@'%';
Summary of Scope Feature | Supported at Role Level? | Supported at User Level? Privileges | Yes (
GRANT ... TO ROLE
) | Yes (
GRANT ... TO USER
) Default Catalog/DB | No | Yes (v3.3.3+) Default Roles | No | Yes Max Connections | No | Yes References • sql-reference/sql-statements/account-management/ALTER_USER.md • sql-reference/System_variable.md • administration/user_privs/authorization/User_privilege.md References • administration/user_privs/authorization/User_privilege.md • sql-reference/sql-statements/account-management/ALTER_USER.md • sql-reference/sql-statements/account-management/DROP_ROLE.md • sql-reference/sql-statements/cluster-management/nodes_processes/ALTER_SYSTEM.md
k
@Rocky does starrocks with apache ranger support the same grants for users when using RBAC with ranger and integrating with an idp like keycloak
r
Yes, StarRocks supports integrating with Apache Ranger for RBAC while using an external Identity Provider (IDP) like Keycloak. However, the way you apply those grants changes significantly from standard SQL commands to Ranger policies. 1. Key Integration Logic When you integrate StarRocks with Keycloak and Ranger: * Authentication (Who are you?): Managed by Keycloak (via JWT/OAuth2 or LDAP). * Authorization (What can you do?): Managed by Apache Ranger. * Group Sync: You use a Group Provider in StarRocks to map Keycloak groups to Ranger. When a user logs in, StarRocks identifies their groups and asks Ranger if that user/group has permission for a specific resource. 2. "Same Grants" Support: SQL vs. Ranger UI While the level of control is the same (you can still grant access to all Materialized Views, for example), the method is different: * Native RBAC: You run SQL like 
GRANT SELECT ON ALL MATERIALIZED VIEWS IN ALL DATABASES TO USER 'jack'
. * Ranger RBAC: You create an Access Policy in the Ranger Admin UI. * Resource: Select "Materialized View". * Scope: Use the wildcard 
*
for Catalog, Database, and Materialized View to mimic "ALL". * Permissions: Select 
SELECT
. 3. Comparison of Grant Capabilities Feature | Native StarRocks RBAC | StarRocks + Apache Ranger Catalog/DB/Table Level | Yes | Yes Materialized Views | Yes | Yes (supported in StarRocks Ranger plugin) Wildcards (ALL) | Yes (
ALL TABLES
) | Yes (using 
*
in Ranger policies) Row-Level Security | Yes | Yes (defined as policies in Ranger) Data Masking | Yes | Yes (defined as policies in Ranger) IDP Group Mapping | Yes (v3.5+) | Yes (via Group Provider) 4. Important Limitations * Hybrid Restriction: If you use Security Integration (the feature that connects to Keycloak/JWT), StarRocks currently requires you to use Apache Ranger for authorization. You cannot use the native 
GRANT
SQL commands for users authenticated via an IDP; permissions must be defined in Ranger. * Command Usage: Once you set 
access_control = ranger
in 
fe.conf
, the native 
GRANT
and 
REVOKE
SQL statements are disabled for those resources. You must manage them through the Ranger UI or Ranger's REST API. Summary You can achieve the same granular permissions (Select on all MVs, usage on catalogs, etc.), but you will no longer use the SQL 
GRANT
syntax. Instead, you will define matching policies in the Ranger StarRocks Service. References • administration/user_privs/authorization/ranger_plugin.md • administration/user_privs/group_provider.md • best_practices/authentication_authorization.md • sql-reference/sql-statements/account-management/GRANT.md References • administration/user_privs/authorization/privilege_item.md • sql-reference/sql-statements/account-management/GRANT.md • administration/user_privs/authorization/User_privilege.md • data_source/catalog/hive_catalog.md • administration/user_privs/authorization/ranger_plugin.md
k
@Rocky if make a basic select * for an async MV based on an external iceberg table and the iceberg schema changes, will the MV pick up those changes or can i alter it or do i need to recreate it
r
When you create an asynchronous materialized view (MV) in StarRocks using 
SELECT _
, the 
_
is expanded at the time of creation into a fixed list of columns. If the underlying Iceberg table schema changes, the MV will not automatically pick up those changes. Here is how you handle different scenarios: 1. Will it pick up changes automatically? No. Because the schema of an asynchronous MV is physically stored in StarRocks, it is static. • If you add a column to the Iceberg table, the MV will continue to function but will not include the new column. • If you drop or rename a column that the MV specifically references, the MV will be set to an 
INACTIVE
state. 2. Can you alter it? You cannot use 
ALTER MATERIALIZED VIEW
to modify the query logic (like adding a new column to the selection). 
ALTER
is limited to changing the refresh interval, name, or status. 3. How to "Sync" the schema If the Iceberg schema has changed and you want the MV to reflect those changes (especially to pick up new columns from a 
SELECT *
), you have two main options: A. The "Active" Shortcut (v3.1.4+) Instead of dropping the MV, you can try to re-activate it. This forces StarRocks to re-evaluate the original SQL definition.
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
* Behavior: StarRocks will attempt to "recreate" the MV based on its original SQL. If the original SQL was 
SELECT _
, this command will re-expand the 
_
to include the current columns of the Iceberg table. * Requirement: The original SQL must still be valid. If you dropped a column that was explicitly named in the MV (not part of a 
*
), this might fail, and you'll need to recreate it. B. Drop and Recreate If the schema change is significant or if 
ALTER ... ACTIVE
does not achieve the desired result, you must manually drop and recreate the MV:
Copy code
sql
DROP MATERIALIZED VIEW your_mv_name;
CREATE MATERIALIZED VIEW your_mv_name ... AS SELECT * FROM iceberg_table;
Summary of Principles * MV Status: Schema changes on base table columns referenced by the MV will set the MV to 
INACTIVE
. * Automatic Activation: From v3.1.4/v3.2.0, StarRocks attempts to automatically re-activate MVs, but for a 
SELECT _
to pick up _new* columns, a manual 
ACTIVE
command or a refresh that triggers re-activation is typically required to update the physical schema. * Query Rewrite: If an MV is 
INACTIVE
, it cannot be used for automatic query rewrite until it is made 
ACTIVE
again. References • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky how to reject queries before they are run that are planned to take too much memory
r
To reject or terminate queries that exceed memory or resource limits in StarRocks, you primarily use Resource Groups. Depending on whether you want to reject them before they start (based on plan statistics) or during execution (once they actually consume too much), you use different settings. 1. Rejecting Queries Pre-Execution (Static Check) StarRocks can reject queries before they run if the planned scan volume is too large. This is the most reliable way to "pre-reject" heavy queries because the planner can estimate scan rows from metadata, whereas estimating peak memory usage is often inaccurate. You can set this in a Resource Group:
Copy code
sql
CREATE RESOURCE GROUP large_queries
TO (user='heavy_user')
WITH (
    'big_query_scan_rows_limit' = '100000000' -- Reject if plan scans > 100M rows
);
* How it works: If the optimizer calculates that a query will scan more than the limit, it returns an error immediately: 
exceed big query scan_rows limit: current is ... but limit is ...
. 2. Terminating Queries During Execution (Circuit Breaker) Since peak memory is difficult to predict perfectly before a query runs, StarRocks uses 
big_query_mem_limit
as a runtime circuit breaker. The query starts, but if its memory footprint reaches the threshold, it is killed.
Copy code
sql
CREATE RESOURCE GROUP heavy_limit
TO (db='analytics')
WITH (
    'big_query_mem_limit' = '2147483648' -- Limit to 2GB per query
);
* Behavior: If a query hits this limit on any BE, it fails with a 
Memory Exceed Limit
error. * Global Variable: You can also set a per-session limit using 
SET query_mem_limit = <bytes>;
. 3. Preventing System Overload (Query Queues) If you want to reject or delay queries because the cluster's total memory is currently too high (rather than a specific query's plan), you use Query Queues.
Copy code
sql
-- Enable the queue
SET GLOBAL enable_query_queue_select = true;

-- Reject new queries if system memory usage > 90%
SET GLOBAL query_queue_mem_used_pct_limit = 0.9;

-- Set max queue length; if more queries come in while the system is full, they are rejected
SET GLOBAL query_queue_max_queued_queries = 50;
4. Summary Table: How to Block "Big" Queries Method | Target Metric | Rejection Timing | Best For 
big_query_scan_rows_limit
 | Estimated Scan Rows | Pre-execution | Preventing massive full-table scans. 
big_query_mem_limit
 | Real-time Memory | During execution | Killing queries that explode in memory (e.g., big joins). 
query_queue_mem_used_pct_limit
 | Cluster Memory | Pre-execution | Protecting the cluster when it's already under pressure. 
big_query_cpu_second_limit
 | Accumulated CPU | During execution | Stopping long-running complex calculations. Important: To use Resource Groups, you must ensure the Pipeline Engine is enabled (it is by default in recent versions): 
SET GLOBAL enable_pipeline_engine = true;
. References • administration/management/monitor_manage_big_queries.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • administration/management/resource_management/resource_group.md • administration/management/resource_management/query_queues.md References • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • administration/management/monitor_manage_big_queries.md
k
@Rocky I have a 5 billion row dimension table that can not be partitined that is sorted by id. how do I make filtering on a single column faster (non sort column)
r
For a 5 billion-row dimension table where you cannot use partitioning, your goal is to minimize the amount of data the engine must scan from disk. Since the table is already sorted by 
id
, filtering on any other column will currently result in a full table scan. Here are the best ways to accelerate filtering on a non-sort column, ordered by performance impact: 1. Materialized View (The "Secondary Index" Pattern) This is the most effective way to speed up filtering on a non-sort column in StarRocks. You can create a synchronous materialized view (MV) that acts as a secondary index by using a different sort order. * How it works: When you create an MV, StarRocks automatically selects the first few columns in the 
SELECT
list as the Sort Key (Prefix Index). * Implementation:
Copy code
sql
    CREATE MATERIALIZED VIEW mv_filter_column AS
    SELECT target_column, id, col2, col3 -- Put your filter column first
    FROM your_table;
* Result: When you query the base table with 
WHERE target_column = 'value'
, the StarRocks optimizer will transparently rewrite the query to use the MV. Because the MV is physically sorted by 
target_column
, it will use the Prefix Index and Zone Maps to skip 99% of the data. * Trade-off: This doubles the storage for that table, but it provides sub-second performance on 5 billion rows. 2. Bloom Filter Index If your filter column has high cardinality (many unique values) and you primarily perform equality queries (e.g., 
WHERE col = 'xyz'
or 
IN
), a Bloom Filter is very effective. * Implementation:
Copy code
sql
    ALTER TABLE your_table SET ("bloom_filter_columns" = "target_column");
* How it works: StarRocks uses the Bloom Filter to quickly determine if a data page could contain the value. If not, it skips the page entirely without reading it from disk. It is low-overhead compared to a Materialized View. 3. Inverted Index (For Strings/Text) If your version is v3.3 or higher, you can use an Inverted Index. This is particularly powerful for string filtering or when you need to match keywords within a column. * Implementation:
Copy code
sql
    ALTER TABLE your_table ADD INDEX index_name (target_column) USING GIN;
* Result: It builds a mapping of values to row IDs. It is much faster than a standard scan for high-cardinality string columns and supports 
MATCH
or equality predicates. 4. Bitmap Index If the column has moderate to high cardinality and you often combine it with other filters, a Bitmap Index can help. * Implementation:
Copy code
sql
    CREATE INDEX index_name ON your_table (target_column) USING BITMAP;
* Note: In StarRocks, Bitmap indexes are optimized for columns where a specific value filters out at least 99.9% of the data. 5. Modify the Table Sort Key (v3.3+) If you find that you almost always filter by this new column instead of 
id
, you can actually change the physical sort order of the table without recreating it (supported for Duplicate Key tables in v3.3+ and Primary Key tables in v3.0+). * Implementation:
Copy code
sql
    ALTER TABLE your_table ORDER BY (target_column, id);
* Warning: This is a heavy background operation that rewrites the data files. Summary Recommendation * For maximum speed (range & equality): Use the Materialized View (Option 1). It provides a new Prefix Index. * For point lookups (equality only): Use a Bloom Filter (Option 2). * For string/keyword searches: Use an Inverted Index (Option 3). References • best_practices/table_clustering.md • sql-reference/sql-functions/utility-functions/encode_sort_key.md
k
@Rocky how to redefine an async mv made with a select * without having to drop and create it?
r
To redefine an asynchronous materialized view (MV) that was created with 
SELECT *
, your approach depends on whether you just want to pick up new columns or if you want to change the query logic entirely. 1. To pick up new columns (same query) If you simply added columns to the base table and want the 
SELECT _
in your MV to include them, you do not need to drop it. You can force StarRocks to re-expand the 
_
and update the MV's schema using:
Copy code
sql
ALTER MATERIALIZED VIEW your_mv_name ACTIVE;
* How it works: When an MV is set to 
ACTIVE
, StarRocks re-evaluates the original SQL definition. If the original SQL was 
SELECT _
, it will re-expand that 
_
against the current schema of the base table. * When to use: Use this if the MV has become 
INACTIVE
due to a schema change or if you just want to "sync" the column list. * Note: In versions 3.1.4+ and 3.2+, StarRocks often attempts to do this automatically in the background, but the manual command guarantees an immediate update. 2. To change the query logic (different query) If you want to change the query itself (e.g., changing 
SELECT *
to 
SELECT col1, col2
), StarRocks does not support 
ALTER MATERIALIZED VIEW ... AS SELECT ...
. To achieve this without downtime or a "gap" where the MV doesn't exist, use the SWAP method: 1. Create a new temporary MV with your new desired query:
Copy code
sql
    CREATE MATERIALIZED VIEW your_mv_name_new
    REFRESH ASYNC ...
    AS SELECT col1, col2 FROM base_table;
2. Swap the new MV with the old one:
Copy code
sql
    ALTER MATERIALIZED VIEW your_mv_name SWAP WITH your_mv_name_new;
3. Drop the old MV (which is now named 
your_mv_name_new
):
Copy code
sql
    DROP MATERIALIZED VIEW your_mv_name_new;
Why use SWAP? * No Downtime: The swap is an atomic metadata operation. * Query Rewrite Preservation: Any queries relying on the MV for automatic rewrite will continue to work without interruption. Summary * *Just refreshing 
SELECT *
?* Use 
ALTER ... ACTIVE
. * Changing the SQL code? Use 
CREATE
+ 
SWAP WITH
+ 
DROP
. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky i dont see the mv expanding to include new columns not originally cauht with select * using alter mv active
r
The reason 
ALTER MATERIALIZED VIEW ... ACTIVE
is not picking up your new columns is that for Asynchronous Materialized Views, the 
SELECT *
is expanded and fixed at the moment the MV is created. Unlike a logical view (which re-evaluates the 
*
every time it is queried), an Asynchronous MV is a physical table with its own defined schema. When you create it, StarRocks looks at the base table, sees the current columns, and saves that specific list of columns into the MV's metadata and physical storage. To include new columns from the base table, you have to redefine the MV. Since StarRocks does not currently support 
ALTER MATERIALIZED VIEW ... AS SELECT ...
, you should use the Atomic Swap method to avoid downtime: The Correct Procedure (Atomic Swap) This method allows you to "redefine" the MV without dropping it first, ensuring that any queries using it for automatic rewrites don't fail. 1. Create a new temporary MV (this will pick up the new columns via its own 
SELECT *
expansion):
Copy code
sql
    CREATE MATERIALIZED VIEW my_mv_new
    REFRESH ASYNC -- matching your original refresh settings
    AS SELECT * FROM base_table;
2. Swap the metadata: This makes the new physical table (with the new columns) the "official" MV and moves the old one to the temporary name.
Copy code
sql
    ALTER MATERIALIZED VIEW my_mv SWAP WITH my_mv_new;
3. Drop the old version:
Copy code
sql
    DROP MATERIALIZED VIEW my_mv_new;
Why 
ALTER ... ACTIVE
exists The 
ACTIVE
command is not for changing the schema; it is for restoring functionality. If you perform a schema change on a base table (like dropping it or changing a column type), StarRocks sets dependent MVs to 
INACTIVE
to prevent data corruption or query errors. 
ALTER ... ACTIVE
tells StarRocks: "I have fixed the base table, please try to use this MV again with its existing column definitions." Summary * Logical Views: Use 
ALTER VIEW ... AS SELECT *
(it re-expands). * Materialized Views: Must use the SWAP pattern because they are physical tables with a schema that was locked at the time of 
CREATE
. References • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md
k
@Rocky what is starrocks defualt wg concurrency limit
r
By default, StarRocks workload groups (Resource Groups) have a concurrency limit of 0. In StarRocks, a value of 0 means there is no limit on the number of concurrent queries allowed within that group. Breakdown for Default Groups: * 
default_wg
: Used for regular queries that don't match any specific classifier. Its default 
concurrency_limit
is 0 (unlimited). * 
default_mv_wg
: Used for asynchronous materialized view refresh tasks. Its default 
concurrency_limit
is also 0 (unlimited). Key Behaviors: * Effectiveness: The 
concurrency_limit
parameter only takes effect when it is set to a value greater than 0. * System Impact: If the limit is 0 (unlimited), queries will continue to be accepted until the system runs out of physical resources (Memory/CPU), at which point they may fail with errors like 
Memory exceed limit
or experience extreme latency. * Queuing: If you set a 
concurrency_limit
greater than 0, you should also consider configuring the Query Queue (
enable_query_queue = true
). Without a queue, any query that exceeds the concurrency limit will fail immediately with an error rather than waiting for a slot. How to check or change it: You can check the current limits of your groups using:
Copy code
sql
SELECT * FROM information_schema.resource_groups;
To set a limit (e.g., to 100) for the default group:
Copy code
sql
ALTER RESOURCE GROUP default_wg WITH ('concurrency_limit' = '100');
References • administration/management/resource_management/resource_group.md • faq/resource_isolation_faq.md • administration/management/BE_configuration.md References • faq/resource_isolation_faq.md • administration/management/resource_management/resource_group.md • administration/management/BE_configuration.md
k
@Rocky does starrocks support query queue for a specific workgroup? for example i have two wgs, defualt and external. i want default to be unlimited. for external, if concurrency is 10, it waits
r
Yes, StarRocks supports query queuing for specific workload groups starting from version 3.1.4. In your scenario, you can configure the 
external
group to queue queries while leaving the 
default
group unlimited. When the group-level queue is enabled, queries that exceed a group's 
concurrency_limit
will wait in a "Pending" state instead of failing immediately. Configuration Steps 1. Enable the Queue Feature (Global) You must enable the query queue globally and specifically for workload groups:
Copy code
sql
-- Enable the general queue for SELECT queries
SET GLOBAL enable_query_queue_select = true;

-- Enable the group-level logic (v3.1.4+)
SET GLOBAL enable_group_level_query_queue = true;
2. Configure the Workload Groups Set the 
concurrency_limit
for your 
external
group. For the 
default
group, a value of 
0
means unlimited concurrency (no queueing will ever trigger for it).
Copy code
sql
-- Set external group to 10 concurrent queries; the 11th will wait
ALTER RESOURCE GROUP external WITH ('concurrency_limit' = '10');

-- Ensure default group remains unlimited (0 is the default)
ALTER RESOURCE GROUP default_wg WITH ('concurrency_limit' = '0');
Key Behaviors to Note * Queuing Logic: If a query enters the 
external
group and 10 queries are already running in that group, it will wait. Queries in the 
default_wg
will continue to run without restriction because their limit is 
0
. * Global Queue Limits: While the concurrency trigger is per-group, the queue capacity and timeout are currently global session variables. These determine how many queries total can wait across the whole cluster and for how long: * 
query_queue_max_queued_queries
(Default: 1024): Total queries allowed to wait before new ones are rejected. * 
query_queue_pending_timeout_second
(Default: 300): How long a query can stay in the queue before timing out. * Monitoring: You can see if queries are waiting by checking the 
IsPending
column in 
SHOW PROCESSLIST
or by monitoring the 
starrocks_fe_resource_group_query_queue_pending
metric. References • administration/management/resource_management/query_queues.md • sql-reference/System_variable.md • faq/resource_isolation_faq.md References • administration/management/resource_management/query_queues.md • faq/resource_isolation_faq.md • sql-reference/System_variable.md References • administration/management/resource_management/query_queues.md • sql-reference/sql-statements/cluster-management/resource_group/CREATE_RESOURCE_GROUP.md • faq/resource_isolation_faq.md • sql-reference/sql-statements/cluster-management/resource_group/ALTER_RESOURCE_GROUP.md • sql-reference/System_variable.md
k
@Rocky does starrocks support sinking data into iceberg on s3 table buckets
r
StarRocks supports sinking data into Apache Iceberg tables located in Amazon S3 Tables (which use "table buckets") starting from version 3.4.0. This integration is achieved using the Iceberg REST Catalog and the AWS Glue Iceberg REST endpoint. This allows you to manage metadata through Glue while storing the data in high-performance S3 table buckets. 1. Catalog Configuration To sink data, you first create an external catalog pointing to your S3 table bucket. Note the specific format for the 
iceberg.catalog.warehouse
property:
Copy code
sql
CREATE EXTERNAL CATALOG s3_table_catalog
PROPERTIES(
  "type"="iceberg",
  "iceberg.catalog.type" = "rest",
  "iceberg.catalog.uri"  = "<https://glue>.<region>.<http://amazonaws.com/iceberg|amazonaws.com/iceberg>",
  "iceberg.catalog.rest.sigv4-enabled" = "true",
  "iceberg.catalog.rest.signing-name" = "glue",
  "iceberg.catalog.warehouse" = "<aws_account_id>:s3tablescatalog/<table_bucket_name>",
  "aws.s3.region" = "<region>",
  "aws.s3.access_key" = "<your_access_key>",
  "aws.s3.secret_key" = "<your_secret_key>"
);
2. Sinking Data Once the catalog is created, you can sink data directly using 
INSERT INTO
or 
CREATE TABLE AS SELECT
(CTAS). Create a table in the bucket:
Copy code
sql
USE s3_table_catalog.default;

CREATE TABLE target_iceberg_table (
    id BIGINT,
    data STRING,
    event_time DATETIME
) PARTITION BY (data);
Sink data from a StarRocks table:
Copy code
sql
INSERT INTO target_iceberg_table
SELECT id, data, event_time 
FROM internal_db.source_table;
Key Requirements * Permissions: You must configure AWS Lake Formation to allow external engines (like StarRocks) to access the S3 table bucket. * Region: The Glue REST endpoint is region-specific (e.g., `https://glue.us-east-1.amazonaws.com/iceberg`). * Format: Currently, StarRocks supports sinking data into Parquet-formatted Iceberg tables. References • data_source/catalog/iceberg/iceberg_rest_s3.md • data_source/catalog/iceberg/DML.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/iceberg/iceberg_rest_s3.md • data_source/catalog/iceberg/DML.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_s3.md • quick_start/iceberg.md • data_source/catalog/iceberg/DML.md
k
@Rocky what about for merge on read workloads
r
StarRocks' support for Merge-on-Read (MoR) workloads in Apache Iceberg depends on whether you are reading or writing: 1. Reading MoR Tables (Supported) StarRocks has robust support for reading Iceberg v2 tables configured with Merge-on-Read. It can process: * Position Deletes: Supported since version 3.0/3.1. * Equality Deletes: Supported since version 3.2.5+ (Parquet) and 3.2.3+ (ORC). * Query Performance: StarRocks applies these deletes at query time (on-the-fly) using its vectorized execution engine, allowing you to query "fresh" data that has been updated by other engines like Flink or Spark. 2. Sinking/Writing MoR Data (Version Dependent) Traditionally, StarRocks' Iceberg sink was restricted to 
INSERT INTO
(appends) and 
INSERT OVERWRITE
(replaces whole partitions, a form of Copy-on-Write). Support for row-level DML (which generates MoR delete files) is a newer capability: * INSERT INTO: Works with S3 table buckets as of v3.4.0. It simply appends new data files to the table. * DELETE (Merge-on-Read): Starting with v3.4 and further optimized in v4.0/v4.1, StarRocks supports native SQL 
DELETE
for Iceberg. When you perform a 
DELETE
, StarRocks writes position delete files (MoR) rather than rewriting the entire data file. This ensures high-performance deletions that are interoperable with other engines like Spark and Trino. * UPDATE / MERGE INTO: Support for 
UPDATE
and 
MERGE INTO
on Iceberg tables is part of the v4.x roadmap. For most v3.x versions, you cannot perform row-level 
UPDATE
or 
MERGE
directly on an Iceberg catalog; these workloads are typically handled by sinking data from StarRocks to an intermediate location and using an engine like Spark, or by using StarRocks' Asynchronous Materialized Views to bridge the gap. Summary for S3 Table Buckets If you are using v3.4.0 or later: * You can sink (INSERT) data into Iceberg tables in S3 table buckets. * You can DELETE rows using a 
WHERE
clause, which will generate MoR position delete files. * You can read any MoR data (even if written by other engines) using the Glue/REST catalog integration. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/DML.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/DML.md • data_source/feature-support-data-lake-analytics.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/DML.md • data_source/feature-support-data-lake-analytics.md • table_design/table_types/primary_key_table.md References • table_design/table_types/primary_key_table.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md
k
@Rocky when will async mat views for partitioned by hash bucket be supported, we have large dimensional tables for iceberg datalake house with query patterns not on date fields
r
Starting from StarRocks 3.5, multi-column partition alignment is supported, but specifically for Iceberg's hash bucket transform, it is not yet supported in the 3.x series. However, there is a significant shift in how StarRocks handles these workloads in the latest and upcoming versions: 1. Current Status (v3.5 and below) In the 3.x versions, StarRocks asynchronous materialized views (MVs) rely on Partition-level refresh. This requires the MV's partition key to map directly to the base table's partition key. * Supported Iceberg Transforms: 
identity
, 
year
, 
month
, 
day
, and 
hour
. * Unsupported: The 
bucket
and 
truncate
transforms are currently not supported for partition-aligned refresh. * Workaround: If your dimensional table is partitioned by a 
STRING
or 
INT
column (identity), you can align the MV to that column (supported since v3.1.4/v3.2). If you specifically need the Iceberg 
bucket
transform, the MV currently treats it as an unpartitioned table, requiring a full refresh. 2. The Solution: Incremental MVs (StarRocks 4.0/4.1) To address the exact problem you described—large tables where query patterns don't follow date fields—StarRocks is moving away from "Partition-aligned refresh" toward Incremental View Maintenance (IVM). * StarRocks 4.1 introduces Incremental Materialized Views for Iceberg. * Why this matters for you: Unlike partition-based refresh (which would require a full refresh of a bucket if any row changed), IVM tracks changes at the Iceberg snapshot/row level. * Capability: It allows the MV to refresh only the delta (new data) regardless of whether the table is partitioned by date, bucketed by ID, or even unpartitioned. This makes MVs on large dimensional tables much more efficient. Summary Table for Iceberg MVs Feature | v3.2 - v3.4 | v3.5 | v4.1+ Identity/Date Partitioning | Supported | Supported | Supported Multi-Column Partitioning | No | Supported | Supported Iceberg Bucket Transform | No | No | Supported via IVM Incremental Refresh | Partition-based | Partition-based | Row/Delta-based (IVM) Recommendation * If you are on v3.x: If possible, change the Iceberg base table to use 
identity
partitioning on your dimensional key (e.g., 
user_id
as a STRING or INT) instead of the 
bucket
transform. This will allow StarRocks to use partition-level incremental refresh. * If you need Hash Buckets: Plan an upgrade to v4.1 to leverage the new Incremental MV engine, which is designed specifically for these "non-date" data lakehouse patterns. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md References • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md
k
@Rocky I need incremental refresh for a gold level table that is changing every hour
r
To achieve incremental refresh for a gold-level table partitioned by Iceberg hash buckets, you will need to use StarRocks version 4.1 or later. The Problem in Version 3.x In the 3.x series, StarRocks relies on partition-aligned refresh. This requires a 1-to-1 mapping between the base table's partition columns and the MV's partition columns. * Restriction: StarRocks 3.x does not support Iceberg's 
bucket()
or 
truncate()
transforms for partition-level refresh. * Result: If your base table uses these transforms, the MV is forced to perform a full refresh every hour, which is inefficient for large gold-level tables. The Solution in StarRocks 4.1: Incremental View Maintenance (IVM) Starting with version 4.1, StarRocks introduces a new 
refresh_mode
called 
INCREMENTAL
. This is a major architectural shift that enables row-level/snapshot-level incremental updates independent of partitioning. 1. How to Define the MV When creating your gold-level MV, you specify the 
refresh_mode
in the properties. This allows the system to track only the changed snapshots in Iceberg and apply the delta to your MV.
Copy code
sql
CREATE MATERIALIZED VIEW gold_incremental_mv
PARTITION BY (dt) -- You can still partition the MV for query performance
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "refresh_mode" = "INCREMENTAL"
)
AS 
SELECT 
    id, 
    COUNT(orders) as total_orders, 
    dt
FROM iceberg_catalog.silver_db.bucketed_table
GROUP BY id, dt;
2. Key Benefits for Your Workflow * Supports Bucket Transforms: Since the refresh logic tracks snapshot deltas rather than partition state, it bypasses the "bucket transform" limitation. * Reduced Resource Consumption: Instead of scanning the entire silver table every hour, StarRocks only processes the data that landed in the last hour. * Gold Table Freshness: You can maintain a "gold" table with low latency (hourly or even faster) without the cost of a full re-computation. Summary of Requirements * Version: StarRocks v4.1+. * Catalog: Works with Iceberg (REST, Glue, Hive catalogs). * Configuration: Ensure the base Iceberg table is v2 if you are performing row-level deletes/updates, though standard appends work for v1. Recommendation: If you are currently on v3.x and cannot upgrade immediately, the only workaround is to change your silver table's partitioning to use 
identity
(e.g., a raw string or date column) instead of 
bucket()
, which would allow version 3.5 to perform partition-level incremental refreshes. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • using_starrocks/async_mv/use_cases/data_lake_query_acceleration_with_materialized_views.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md
k
@Rocky this is not an append only table. This is a table joined from multiple star schema dimensional tables, will incremental still work
r
This is a nuanced scenario. While StarRocks 4.1 introduces the infrastructure for this, there are specific constraints regarding Joins and Non-append-only data that you need to be aware of. 1. The "Append-Only" Constraint in v4.1 The new 
INCREMENTAL
refresh mode (IVM) in StarRocks 4.1 currently has a critical limitation for Iceberg: it only supports append-only base tables. * How it works: In v4.1, the incremental engine tracks new Iceberg snapshots. If it detects a snapshot that contains an 
APPEND
operation, it can incrementally update the Materialized View (MV). * The Problem: If you perform a 
DELETE
, 
UPDATE
, or 
MERGE
on any of your dimensional or fact tables, the 
INCREMENTAL
refresh will fail. The current engine does not yet support "retractable" changes (deleting or updating existing rows in the MV based on base table changes). 2. Joins and Star Schemas The good news is that the IVM engine does support Inner Joins. You can join a large fact table with multiple dimensional tables in an incremental MV. However, the "append-only" rule applies to all tables in that join. If even one of your small dimensional tables is updated (e.g., a SCD Type 1 update), the incremental refresh of the Gold MV will be blocked. 3. The "Hash Bucket" Conflict As we discussed previously, your use of Iceberg hash buckets creates a secondary issue: * INCREMENTAL Mode: Blocked because your tables are not append-only. * Partition-Level Refresh (PCT): This is the fallback for non-append-only data, but it is blocked because StarRocks does not yet support aligning partitions based on the Iceberg 
bucket()
transform. It only supports 
identity
, 
year
, 
month
, 
day
, and 
hour
. Summary for Your Gold Table For a gold-level table that is (1) a Join, (2) Non-append-only, and (3) Hash-bucketed, here is the status: Feature | Support in v4.1 Incremental Join (IVM) | ✅ Supported (Inner Join) Updates/Deletes (IVM) | ❌ Not Supported (Requires Append-Only) Hash Bucket Alignment | ❌ Not Supported Refresh Strategy | Full Refresh Required Recommended Path Forward Since a full refresh of a large gold table every hour is likely too expensive, consider these alternatives: 1. Change Partitioning (If Possible): If you can change your base Iceberg tables from 
bucket(id)
to 
identity(id)
(if the ID is a string or int and not too high cardinality), you can use Partition-level refresh (PCT) in v3.5/v4.1. This would allow you to refresh only the "partitions" (IDs) that changed, even with updates. 2. View-based Gold Layer: Instead of a Materialized View, use a standard VIEW for the Join. StarRocks' query engine is extremely fast at Iceberg Joins. With features like Data Cache and Global Dictionary, you might find that the raw query performance on the "Silver" tables is sufficient without materializing the "Gold" layer. 3. Wait for v4.2/v4.x: Support for non-append-only IVM (handling deletes and updates) is a high-priority item on the roadmap following the initial v4.1 release. References • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • data_source/catalog/iceberg/iceberg_catalog.md References • release_notes/release-4.0.md • release_notes/release-3.2.md
k
@Rocky how to ignore mv rewrite for a single query at runtime
r
To ignore Materialized View (MV) rewrite for a single query at runtime, you can use the 
SET_VAR
query hint to temporarily disable the 
enable_materialized_view_rewrite
session variable for that specific statement. Syntax Add the following hint immediately after the 
SELECT
keyword:
Copy code
sql
SELECT /*+ SET_VAR(enable_materialized_view_rewrite = false) */ 
    column1, 
    column2 
FROM your_base_table 
WHERE ...;
Key Details * Scope: This hint only affects the query it is attached to. It does not change your global or session-level settings. * Function: It forces the StarRocks optimizer to ignore any available asynchronous materialized views and query the base tables directly. * Use Case: This is useful for troubleshooting performance issues, verifying the latest data in base tables (if the MV is stale), or comparing execution plans between the MV and base tables. Alternative (Session Level) If you want to disable it for several queries in a row without adding hints to each, you can change the variable at the session level:
Copy code
sql
SET enable_materialized_view_rewrite = false;
-- Your queries here
SET enable_materialized_view_rewrite = true;
References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • best_practices/query_tuning/query_hint.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/Materialized_view.md • sql-reference/System_variable.md • best_practices/query_tuning/query_hint.md
2 Views
Open in Slack
PreviousNext
Powered by