@Rocky How Materialized views on ICEBERG by StarRocks shared Data architecture works? Do they store data on starrocks native form? I have a Data in ICEBERG do I need to convert it to StarRocks native form to get benefits of StarRocks or I can just make views and I can get away with it. We also can't make index on ICEBERG table so can we add index in MVs? what's good AMV or something else?

In StarRocks' shared-data architecture, Materialized Views (MVs) on Iceberg—specifically *Asynchronous Materialized Views (AMVs)*—act as a bridge between your data lake and StarRocks' high-performance engine. 1. How it Works in Shared-Data Architecture In StarRocks shared-data mode, your data is stored in remote object storage (like S3 or Azure Blob). When you create an AMV on an Iceberg table: * Physical Storage: The AMV is a physical table. StarRocks reads the data from Iceberg, processes it (joins, aggregations, etc.), and writes the result into StarRocks' own internal storage format on the same remote storage. * Metadata Tracking: StarRocks (from v3.1.4+) can track Iceberg snapshots. It detects when Iceberg data changes and can trigger a refresh of the MV (either manually or on a schedule). * Query Rewrite: You don't have to query the MV directly. If you query the base Iceberg table, StarRocks' optimizer can automatically "rewrite" the query to use the MV instead if it’s more efficient. 2. Do they store data in StarRocks native form? Yes. This is the core benefit. While your source data remains in Iceberg format, the data within the Materialized View is stored in StarRocks' native columnar format. * Why this matters: Because the MV is stored natively, it benefits from StarRocks' storage optimizations like prefix indexes, zone maps, and highly efficient vectorized execution. 3. Do you need to convert Iceberg data? You do not need to manually convert/load your entire dataset. You can "get away with" making views, but there's a performance trade-off: * Standard Views: These are just logical shortcuts. StarRocks still has to pull the data from Iceberg every time you query. This is fast, but limited by network I/O and Iceberg's metadata overhead. * Asynchronous MVs (AMVs): Use these for your "hot" data or heavy aggregations. It effectively "caches" the data in StarRocks' native format. You get the performance of a native table with the convenience of an automated view. 4. Adding Indexes to MVs One of the biggest advantages of AMVs is that you can add indexes that Iceberg doesn't support natively: * Sort Keys: When creating the MV, you can define an

ORDER BY

expression. This acts as a primary index for data skipping. * Bitmap Indexes: You can create bitmap indexes on columns in the MV to accelerate filtering on low-cardinality data. * Bloom Filter Indexes: You can enable Bloom Filters on the MV to speed up equality predicates (e.g.,

WHERE id = 123

). Example of creating an MV with an index on Iceberg data:

sql
CREATE MATERIALIZED VIEW my_iceberg_mv
DISTRIBUTED BY HASH(user_id)
ORDER BY (event_time) -- Defines the sort key/index
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS 
SELECT user_id, event_time, action 
FROM iceberg_catalog.db.logs;

-- Add a bitmap index to the MV
CREATE INDEX idx_action ON my_iceberg_mv(action) USING BITMAP;

Summary: AMV vs. Others * AMV (Asynchronous Materialized View): Best for Data Lake acceleration. Supports multi-table joins, complex SQL, and indexes. It stores data natively. * Synchronous MV: Mostly used for simple rollups (aggregations) on a single internal table. It is less flexible for Iceberg. * Data Cache: StarRocks also has a "Data Cache" feature that automatically caches Iceberg data blocks on local BE disks. This is "zero-effort" acceleration, but MVs are even faster for complex queries because they store pre-computed results, not just raw blocks. Recommendation: Use AMVs if you need sub-second response times on Iceberg data or want to add indexes for specific query acceleration. References • using_starrocks/async_mv/Materialized_view.md • table_design/table_design.md

@Rocky My ICEBERG is on s3 and Starrocks has own S3 to store native data. So if I create AMV on ICEBERG table will it take space on which s3 1. One with ICEBERG or 2. one which is designated for onject storage for StarRocks also can we create index on view? Like ICEBERG doesn't have index but I want index on table

In the StarRocks shared-data architecture, here is how the storage and indexing work for Asynchronous Materialized Views (AMVs) built on Iceberg: 1. Where is the data stored? If your Iceberg data is in Bucket A and StarRocks native storage is in Bucket B: * The AMV data is stored in Bucket B. * When you create an AMV, StarRocks reads the data from the Iceberg table (Bucket A), processes it, and then writes the result into its own native storage (Bucket B) in StarRocks' internal format. * It does not write anything back to your Iceberg S3 bucket. 2. Do you need to convert to StarRocks native form? You don't need to manually convert your entire data lake. By using AMVs, you get the best of both worlds: * Iceberg (Bucket A): Remains your "Source of Truth" and storage for long-term data. * AMV (Bucket B): Acts as a high-performance "cache" or "pre-computed layer." Because the AMV is in StarRocks' native form, it uses StarRocks' highly optimized storage engine (vectorized I/O, better compression) which is much faster than querying Iceberg directly. 3. Adding Indexes to "Views" You cannot add indexes to a standard logical

VIEW

(because a view has no data of its own), but you can add them to an Asynchronous Materialized View (AMV) because it is a physical table. Since Iceberg doesn't support traditional database indexes, AMVs are the perfect way to add them: * Sort Keys (Primary Index): When creating the AMV, use

ORDER BY

. This stores the data physically sorted, allowing StarRocks to "skip" massive amounts of data during range filters. * Bitmap Indexes: Excellent for speeding up filters on columns with low-to-medium variety (e.g.,

city

,

category

,

status

). * Bloom Filter Indexes: Excellent for high-cardinality columns (e.g.,

user_id

,

order_uuid

) to quickly check if a value exists in a data block. Example:

sql
-- Create AMV with a Sort Key (Index)
CREATE MATERIALIZED VIEW iceberg_accelerated_mv
DISTRIBUTED BY HASH(user_id)
ORDER BY (event_date, user_id) -- This is your "index" for data skipping
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
PROPERTIES (
    "bloom_filter_columns" = "user_id" -- Adds a Bloom Filter index
)
AS 
SELECT user_id, event_date, region, revenue 
FROM iceberg_catalog.db.raw_data;

-- Add a Bitmap index after creation
CREATE INDEX idx_region ON iceberg_accelerated_mv(region) USING BITMAP;

Recommendation: AMV is the right choice For your scenario, AMVs are the superior choice. * Standard Views: No performance gain; no indexes. * Data Cache: Speeds up raw Iceberg reads, but doesn't allow for pre-aggregation or custom indexing. * AMVs: Provide pre-aggregation, native StarRocks performance, and the ability to add Sort Keys, Bitmap, and Bloom Filter indexes to data that originally came from Iceberg. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • deployment/shared_data/s3.md • using_starrocks/async_mv/Materialized_view.md • table_design/indexes/Bitmap_index.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md

@Rocky 1.) So ICEBERG tables are in ICEBERG_catalogue and this view will stay in same catlogue or in defauilt cataloue? 2.) How AMV works? It sync periodically and also syncs when someone queries on it? I have constant data updation happening in ICEBERG so I don't want StarRocks to be busy with this work constantly

1. Where does the Materialized View (MV) stay? The Asynchronous Materialized View (AMV) will stay in the

default_catalog

, not in your

iceberg_catalog

. * How it works: Your base data remains in the

iceberg_catalog

(remote S3 Bucket A). However, because an AMV is a physical StarRocks table that requires local metadata and internal storage (S3 Bucket B), it must reside within StarRocks' internal storage system. * Accessing it: You typically create a database in the

default_catalog

(e.g.,

create database mv_db

) and create the AMV there. You can then query it directly or let the optimizer pick it when you query the Iceberg catalog. ────────── 2. How the Refresh (Sync) Works StarRocks offers flexible refresh strategies so you can control how "busy" the cluster gets: Does it sync when someone queries it? No. Querying the view does not trigger a data sync. * If you query the base Iceberg table: StarRocks checks if the MV is "fresh." If the Iceberg data has changed since the last MV refresh, StarRocks will ignore the MV and query the raw Iceberg data directly to ensure accuracy (unless you explicitly allow "stale" data using

mv_rewrite_staleness_second

). * If you query the MV directly: You will get the data from the last successful refresh, regardless of how much the Iceberg source has changed. Handling "Constant Updates" in Iceberg If your Iceberg tables are updated constantly, you should avoid the default

REFRESH ASYNC

strategy, as it may trigger too many refresh tasks. Instead, use one of these two options to keep StarRocks from being overloaded: 1. Scheduled Refresh (Recommended for your case): Set a fixed interval (e.g., every 30 minutes or 1 hour). StarRocks will only look for changes and sync at those specific times.

sql
    CREATE MATERIALIZED VIEW my_mv
    REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Syncs only once an hour
    AS SELECT ... FROM iceberg_catalog.db.table;

2. Manual Refresh: StarRocks does nothing automatically. You trigger the sync via your own pipeline (like Airflow, dbt, or a cron job) after your Iceberg ETL is finished.

sql
    -- Triggered manually by your pipeline
    REFRESH MATERIALIZED VIEW my_mv;

Partition-Level Refresh (The Efficiency Key) Starting from StarRocks v3.1.4, StarRocks can detect which specific partitions in Iceberg have changed. If you partition your AMV the same way as your Iceberg table, StarRocks will only refresh the changed partitions instead of re-reading the entire dataset. This drastically reduces the workload on StarRocks during a sync. Example of a partitioned AMV:

sql
CREATE MATERIALIZED VIEW ice_mv
PARTITION BY event_date -- Matches Iceberg partitioning
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS SELECT * FROM iceberg_catalog.db.large_table;

Summary Recommendation To prevent StarRocks from being "constantly busy" while still getting the benefit of indexes: 1. Create the AMV in the

default_catalog

. 2. Use

REFRESH ASYNC EVERY (INTERVAL ...)

to control the frequency. 3. Use

PARTITION BY

to ensure StarRocks only updates new/changed data, rather than the whole table. whole table. whole table. References • using_starrocks/async_mv/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 • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md References

@Rocky if instad i create ICEBERG views then are that views are visible through StarRokcs I don't see it automatically

There are two main reasons why you might not see your Iceberg views in StarRocks automatically: Version Compatibility and Dialect/Engine Support. 1. Version Requirements Support for Iceberg views is a relatively new feature in StarRocks. If you are on an older version, the catalog will simply ignore the "view" objects in your Iceberg metastore. * Querying Iceberg Views: Supported from v3.3.2+ (on REST catalogs) and v3.4.1+ (on Hive/Glue catalogs). * Creating Iceberg Views via StarRocks: Supported from v3.5+. 2. The "Engine" Problem (Dialect) Iceberg views are unique because they can store multiple SQL "dialects" (e.g., a Spark version, a Trino version, and a StarRocks version) in the same metadata. * Default Behavior: If a view was created by Spark or Trino, it might not have a "StarRocks dialect" definition inside it. StarRocks currently prioritizes views that it created itself or views that contain a compatible dialect. * New in v3.5: You can now "attach" a StarRocks definition to an existing Iceberg view. If the view exists in the catalog but isn't queryable, you can run a

CREATE OR REPLACE VIEW

command in StarRocks using the same name. StarRocks will add its own dialect to that same Iceberg view object without breaking it for other engines. 3. How to verify and "see" them If you are on a supported version but still don't see them: 1. Grant Permissions: Ensure your user has

USAGE

and

SELECT

permissions on the catalog.

sql
    GRANT USAGE ON CATALOG iceberg_catalog TO ROLE public;

2. Check Object Type: In StarRocks, run

SHOW TABLES FROM <catalog>.<db>;

. StarRocks typically lists both tables and views in this output. If it's missing, it’s likely a version or dialect incompatibility. 3. Manual Creation: If the view isn't showing up, you can simply "re-create" it from within StarRocks pointing to the same base Iceberg tables. This creates a StarRocks-native view object that will then be visible and queryable. Summary If you want the best performance and indexing (as discussed in the previous messages), you should still use Asynchronous Materialized Views (AMVs) in your

default_catalog

. Standard Iceberg views (logical views) are only for "getting away with it" for simple queries and do not support indexes. If you need the performance of indexes and native storage, AMVs are the way to go, even if you already have logical views in Iceberg. References • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/DDL.md • data_source/catalog/iceberg/iceberg_catalog.md References • data_source/catalog/iceberg/DDL.md • data_source/feature-support-data-lake-analytics.md • sql-reference/sql-statements/View/CREATE_VIEW.md • data_source/catalog/iceberg/iceberg_catalog.md

@Rocky I'm on 4.1.4 and I'm, getting below error for root user ERROR 1064 (HY000): This catalog doesn't support creating/alter views

This error occurs because StarRocks only supports creating/altering logical views within an Iceberg catalog if that catalog is using a REST backend (e.g., Polaris, Gravitino, or Tabular). If your Iceberg catalog is using AWS Glue or Hive Metastore (HMS) as the metastore, StarRocks does not yet support writing view definitions back to those systems. This is why you see the message:

This catalog doesn't support creating/alter views

. How to resolve this: 1. Create the view in the

default_catalog

(Recommended) Instead of creating the view inside your

iceberg_catalog

, create it in the StarRocks internal catalog. This view will still query your Iceberg data, but its definition will be stored in StarRocks.

sql
-- Switch to a database in your local StarRocks catalog
USE default_catalog.your_db;

-- Create the view pointing to the Iceberg table
CREATE VIEW my_iceberg_view AS 
SELECT * FROM iceberg_catalog.iceberg_db.my_table;

2. Use an Asynchronous Materialized View (AMV) As discussed earlier, if your goal is to add indexes (Sort Keys, Bitmap, etc.), a standard logical view won't work anyway. You must use an AMV. Like logical views, AMVs must also reside in the

default_catalog

.

sql
CREATE MATERIALIZED VIEW default_catalog.your_db.accelerated_iceberg_mv
DISTRIBUTED BY HASH(id)
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS 
SELECT * FROM iceberg_catalog.iceberg_db.my_table;

Why don't you see Iceberg views automatically? If you created views using Spark or Trino in your Iceberg catalog: * Version Compatibility: StarRocks only started supporting the discovery/querying of existing Iceberg views in recent versions (v3.3.2+ for REST, v3.4.1+ for Glue/HMS). * Dialect Issues: Iceberg views store specific SQL dialects. If a view was created by Spark, it may not have a "StarRocks" dialect version. In StarRocks v3.5+, you can "attach" a StarRocks definition to an existing Iceberg view using

CREATE OR REPLACE VIEW

if the catalog supports it (currently primarily REST catalogs). Summary for v4.1.4 * Iceberg REST Catalog: Supports

CREATE VIEW

(writes to the remote catalog). * Iceberg Glue/HMS Catalog: Does not support

CREATE VIEW

(returns the error you saw). * Workaround: Always create your views or AMVs in the

default_catalog

. References • data_source/catalog/iceberg/DDL.md • sql-reference/sql-statements/View/CREATE_VIEW.md • data_source/catalog/iceberg/iceberg_catalog.md • using_starrocks/async_mv/Materialized_view.md

@Rocky but I can't even see view which created by athena on same catalogue

The reason you cannot see a view created by Athena in your Iceberg catalog is that Athena "Views" and "Iceberg Views" are not the same thing. 1. The Technical Difference * Athena Logical Views: When you create a view in Athena, it saves a SQL definition (in Presto/Trino dialect) inside the AWS Glue Data Catalog as a

VIRTUAL_VIEW

. Other engines (like StarRocks) cannot easily "see" or parse this because it’s a specific Glue/Athena object, not an Iceberg object. * Iceberg Views: These follow the official Apache Iceberg View Specification. They are stored as metadata files on S3, similar to how Iceberg tables work. StarRocks (v3.3+) only looks for these native Iceberg View objects. 2. Why StarRocks doesn't see them automatically StarRocks' Iceberg Catalog is designed to discover Iceberg-formatted objects. * If the view was created by Athena's standard

CREATE VIEW

command, it is not an Iceberg-formatted view. It is an "Athena view." * StarRocks currently does not support importing standard Athena logical views via the Iceberg catalog. 3. How to fix this in StarRocks v4.x Since you are on a modern version (v4.x), you have two paths: Option A: Re-create the view in StarRocks (Logical Only) If you just want the view to be visible and queryable in StarRocks, create it in your

default_catalog

. It will point to the Iceberg tables but the definition will live in StarRocks.

sql
CREATE VIEW default_catalog.your_db.my_view AS 
SELECT * FROM iceberg_catalog.iceberg_db.table;

Option B: Create a Native Iceberg View (Experimental) If you are using a REST Catalog (like Polaris or Gravitino), StarRocks v3.5+ can create views that follow the Iceberg Spec. However, if you are using Glue, StarRocks (and Athena) support for the native Iceberg View Spec is still evolving. * Note: Athena only recently added support for native Iceberg views. If you didn't specifically create an "Iceberg View" in Athena (which requires specific settings), StarRocks won't see it. Summary Recommendation Do not rely on Athena to create views for StarRocks. Because StarRocks is a high-performance engine, the best practice is: 1. For simple logic: Create the view in the StarRocks

default_catalog

. 2. For performance (Indexes): Create an Asynchronous Materialized View (AMV) in the

default_catalog

. This will give you the "Data Warehouse" speed you likely want from StarRocks while keeping your data in Iceberg. References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • sql-reference/sql-statements/View/CREATE_VIEW.md References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/feature-support-data-lake-analytics.md • data_source/catalog/iceberg/DDL.md • quick_start/iceberg.md