http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> addFIles() function, how does it wo...
# questions-and-troubleshooting
m
@Rocky addFIles() function, how does it work? when we write parquet files to s3
r
In StarRocks, the 
add_files
procedure is a management tool for Iceberg tables. It allows you to register existing Parquet or ORC files (stored in S3 or other storage) into an Iceberg table's metadata without rewriting the actual data. How it works When you write Parquet files to S3 (e.g., via an external ETL process or a data lake migration), they are just "orphan" files that an Iceberg table doesn't know about yet. The 
add_files
procedure scans the specified S3 path, reads the file metadata, and updates the Iceberg table's snapshots to include these files. Syntax You run this as an 
ALTER TABLE ... EXECUTE
command:
Copy code
sql
ALTER TABLE [catalog.][database.]table_name
EXECUTE add_files(
    location = '<s3://your-bucket/path/to/parquet/>',
    file_format = 'parquet',
    recursive = true
);
Key Parameters * 
location
: The S3 directory or specific file path where your Parquet files are stored. * 
file_format
: Must be 
'parquet'
or 
'orc'
. * 
recursive
: (Optional) If set to 
true
(default), StarRocks will scan all subdirectories for files. * 
source_table
: (Alternative to 
location
) You can also sync files directly from another source table (like a Hive table) instead of providing a raw S3 path. Important Notes 1. Schema Matching: The schema of the Parquet files in S3 must match the schema of the target Iceberg table. 2. Catalog Requirement: This procedure is specifically for the Iceberg Catalog. 3. No Data Movement: This is a metadata-only operation. It is extremely fast because it doesn't move or copy the Parquet files; it only tells Iceberg that "these files now belong to this table." For simply querying or loading files from S3 without using Iceberg, you would typically use the *`FILES()` table function* instead. References • data_source/catalog/iceberg/procedures.md • sql-reference/sql-functions/table-functions/files.md
m
@Rocky is it possible to use the following, not using the pipe, just as is? SELECT * FROM FILES( "path" = "<s3://1stdev/em-test/.parquet>"*, "format" = "parquet", "aws.s3.use_aws_sdk_default_behavior" = "true", "aws.s3.region" = "us-east-1" );
r
Yes, you can use that exact syntax. The 
FILES()
 table function is designed specifically for ad-hoc queries, allowing you to select data directly from S3 without creating an external table or a Load Pipe. However, there are two minor syntax adjustments needed for your query to run successfully: 1. Remove the 
< >
brackets: These are usually placeholders in documentation. The path should be a plain string. 2. Verify the authentication property: While 
aws.s3.use_aws_sdk_default_behavior
is a valid property, most users intending to use the EC2/Node IAM role typically use 
"aws.s3.use_instance_profile" = "true"
. Corrected Query
Copy code
sql
SELECT * FROM FILES(
    "path" = "<s3://1stdev/em-test/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
Key Details * Ad-hoc Queries: This approach is perfect for "not using the pipe" (i.e., when you don't need continuous loading). It is the standard way to "peek" at data or perform one-time transformations. * Wildcards: Your use of 
*.parquet
is correct; it will scan all Parquet files in that folder. * Schema Inference: Since Parquet files contain their own metadata, StarRocks automatically detects the column names and types. You don't need to define them. * Permissions: Ensure the StarRocks FE and BE nodes have the necessary S3 permissions if you are relying on the "default behavior" (IAM roles/environment variables). Pro Tip: 
DESC FILES()
 If you just want to see the schema (columns and types) of the files without scanning the data, you can run:
Copy code
sql
DESC FILES(
    "path" = "<s3://1stdev/em-test/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
); ``` ``` References • sql-reference/sql-functions/table-functions/files.md • loading/s3.md References • administration/management/FE_configuration.md • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/s3.md • loading/BrokerLoad.md
e
@Rocky I dont see 
< >
brackets
r
My apologies for any confusion! In the snippet you pasted in your previous message, the path was written like this: 
"path" = "<s3://1stdev/em-test/*.parquet>"
The 
<
 at the beginning and the 
>
 at the end of the URL are what I was referring to. In technical documentation, those symbols are often used to mean "insert your value here," but they shouldn't be included in the actual SQL query. If your code already looks like this (without those symbols), then you are all set:
Copy code
sql
-- Correct version
SELECT * FROM FILES(
    "path" = "<s3://1stdev/em-test/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
Just to confirm: * No 
< >
: The path starts with 
s3://
and ends with 
.parquet
. * Result: This will immediately scan the files in S3 and return the rows in your SQL editor, just like a normal table. If you run this and get an error, feel free to share the error message! References • sql-reference/sql-functions/table-functions/files.md • loading/s3.md • data_source/catalog/iceberg/procedures.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/BrokerLoad.md • administration/management/FE_configuration.md the error message and I can help troubleshoot!
e
@Rocky is it possible instead of :
Copy code
"path" = "<s3://1stdev/em-test/*.parquet>",
to give a list of paths (files to read in s3 ) ?
r
Yes, you can specify multiple files or paths in the 
FILES()
function using wildcards, which provide a very flexible way to target specific lists of files without using a general 
*
wildcard. While 
FILES()
does not currently support a comma-separated list of full URIs (like 
"<s3://path1>, <s3://path2>"
), you can achieve the same result using the 
{}
(curly braces) glob pattern. 1. Using 
{}
for specific files If you have a specific list of files in the same bucket/folder, you can list them inside curly braces:
Copy code
sql
SELECT * FROM FILES(
    "path" = "<s3://1stdev/em-test/{file1.parquet,file2.parquet,file3.parquet}>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
2. Using 
{}
for multiple sub-directories You can also use this to pick specific folders:
Copy code
sql
SELECT * FROM FILES(
    "path" = "<s3://1stdev/em-test/{folder_a,folder_b}/*.parquet>",
    "format" = "parquet",
    -- ... other params
);
3. Other supported wildcards In addition to 
{}
and 
*
, the 
FILES()
function supports: * *`*`*: Matches zero or more characters. * 
?
: Matches exactly one character. * 
[abc]
: Matches any one character inside the brackets (e.g., 
file[1-3].parquet
). * 
^
: Used inside brackets to negate a match (e.g., 
file[^0].parquet
to exclude file0). Alternative: 
UNION ALL
 If your files are in completely different buckets or have different authentication requirements, the standard way to query them together is using a `UNION ALL`:
Copy code
sql
SELECT * FROM FILES("path" = "<s3://bucket-a/data1.parquet>", ...)
UNION ALL
SELECT * FROM FILES("path" = "<s3://bucket-b/data2.parquet>", ...)
Summary: Use the 
{}
wildcard inside your path string to provide a list of files or directories. References • sql-reference/sql-functions/table-functions/files.md • sql-reference/sql-statements/loading_unloading/BROKER_LOAD.md • loading/s3.md • loading/BrokerLoad.md • administration/management/FE_configuration.md • data_source/catalog/iceberg/procedures.md specific files or folders. References • sql-reference/sql-functions/table-functions/files.md
e
@Rocky when using:
Copy code
SELECT * FROM FILES(
    "path" = "<s3://bucketName/em-test/{file1.parquet,file2.parquet,file3.parquet}>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
or 
[abc]
: Matches any one character inside the brackets (e.g., 
file[1-3].parquet
). how does it work behind the scenes -> is it one multi-part call to s3 ? is it a call to s3 per parquet ? is it in parallel ? if the file is huge does it happen in the same call ?
r
When you use a glob pattern (like 
{file1, file2}
) in the 
FILES()
function, StarRocks handles it through a distributed, multi-stage process. Here is exactly how it works behind the scenes: 1. The "List and Plan" Phase (FE) First, the StarRocks Frontend (FE) acts as the coordinator. * Is it one call? No. S3 does not have a native "Get these 3 specific objects" API. * The FE first sends a ListObjects request to S3 to find all files matching your prefix. _ It then filters that list based on your glob pattern (
{file1, file2}
or 
_.parquet
). * Once it has the list of files, it calculates how to divide the work (the "Scan Ranges"). 2. Call per file vs. Call per part * Call per file? Generally, yes. For small files, each BE (Backend) node will issue a GET request for the file it is assigned. * What if the file is huge? If you are reading Parquet or ORC, these formats are "splittable." If a single file is very large (e.g., 1GB), StarRocks will split that one file into multiple Scan Ranges (e.g., 128MB chunks). * Multi-part/Range GETs: For these huge files, different BE nodes will issue Range GET requests (using the HTTP 
Range
header) to read only their assigned portion of that single file. 3. Parallelism * Is it in parallel? Yes, highly. * Because StarRocks is an MPP (Massively Parallel Processing) engine, it distributes the list of files/ranges across all available BE nodes in your cluster. * Multiple files are read simultaneously by different nodes, and even a single huge file can be read in parallel by multiple nodes if it is split. 4. Comparison to "Pipes" While 
FILES()
is parallel and efficient for ad-hoc queries: * FILES() is an "all-or-nothing" operation. If one file in your list of 100 is corrupted, the whole query fails. * Pipes (the 
INSERT
pipe you mentioned) are designed for background, continuous loading. They handle files one-by-one and can "skip" or "retry" individual failed files without stopping the whole process. Summary Table Feature | Behavior in 
FILES()
 S3 API Calls | Multiple (one or more per file/chunk). Parallelism | Parallel across all BE nodes and CPU cores. Large Files | Split into chunks and read via 
Range
requests in parallel. Wildcards | Resolved by FE via a 
List
call, then filtered. References • sql-reference/sql-functions/table-functions/files.md • loading/s3.md • sql-reference/sql-statements/loading_unloading/pipe/CREATE_PIPE.md • loading/BrokerLoad.md References • loading/s3.md • sql-reference/sql-functions/table-functions/files.md
e
@Rocky if my external service triggers starrocks:
Copy code
ALTER TABLE [catalog.][database.]table_name
EXECUTE add_files(
    location = '<s3://your-bucket/path/to/parquet/>',
    file_format = 'parquet',
    recursive = true
);
but it does lets say 100 one after the other (without waiting for the response -async) how does starrocks handle this behind the scenes ?
r
If your external service triggers 100 
add_files
calls asynchronously (without waiting for the result), here is how StarRocks handles that process behind the scenes: 1. Request Handling at the Frontend (FE) * Concurrency: Each of the 100 requests will arrive at the StarRocks Frontend (FE) and be assigned to a worker thread from the FE's connection pool. * Parallel Execution: The FE will start processing these requests in parallel. For each request, it performs a "List" operation on S3 to find the files in the specified location and reads their footers (metadata) to prepare the Iceberg update. 2. The Iceberg Commit Mechanism (Optimistic Concurrency) This is where the "100 calls" becomes tricky. StarRocks follows the standard Apache Iceberg Optimistic Concurrency Control (OCC) model: * Conflict Detection: Each 
add_files
call attempts to create a new snapshot. Before it can finish, it must "commit" the change to the Metastore (Glue, HMS, or Nessie). * Race Conditions: If 100 calls try to commit at the exact same time, only one can successfully move the table's "current snapshot" pointer forward. The other 99 will detect that the "current version" has changed since they started. * Automatic Retries: StarRocks (via the Iceberg library) will automatically try to "rebase" and retry the commit. However, if 100 operations are hammering the same table, you may eventually see 
CommitFailedException
or timeout errors if the retries exceed the limit. 3. Metadata Performance (The "Small File" Problem for Metadata) Even if all 100 calls succeed, doing it this way is highly inefficient: * Snapshot Bloat: You will create 100 separate snapshots in your Iceberg history. * Manifest Bloat: Each call generates new manifest files. Over time, having thousands of tiny manifests will significantly slow down your 
SELECT
queries, as the FE has to read all those manifest files just to plan a query. * S3 Pressure: You are triggering 100 
ListObjects
and hundreds of metadata read/write requests to S3 simultaneously. 4. Better Alternatives Instead of firing 100 separate 
add_files
calls, you should use one of these two strategies: * Option A: Use Wildcards (Recommended) If the files follow a pattern, send one command that covers all 100 locations.
Copy code
sql
    -- Add files from multiple folders in one transaction
    ALTER TABLE my_iceberg_table EXECUTE add_files(
        location = '<s3://my-bucket/path/to/data/{folder_1,folder_2,folder_3}/>',
        file_format = 'parquet'
    );
* Option B: External Batching If your service is receiving events, have it "buffer" the events for 10–30 seconds and then send a single 
add_files
call for all new locations discovered during that window. Summary While StarRocks can handle these requests via standard concurrency and Iceberg retries, 100 rapid-fire metadata updates will eventually cause commit conflicts and degrade the long-term performance of your table. Batching multiple paths into a single call is the best practice. References • data_source/catalog/iceberg/procedures.md • data_source/catalog/iceberg/DML.md • loading/s3.md • sql-reference/sql-functions/table-functions/files.md • data_source/catalog/iceberg/iceberg_catalog.md References • data_source/catalog/iceberg/procedures.md • sql-reference/sql-functions/table-functions/files.md
e
@Rocky when doing add_files does the s3 location effect our iceberg partition or does it have nothing to do with that
r
Yes, the S3 location definitely affects how StarRocks assigns partitions to the added files. In Apache Iceberg, partitioning is "hidden," meaning the table metadata tracks which file belongs to which partition. When you use 
add_files
, StarRocks does not read every row of your Parquet files (that would be too slow); instead, it performs Partition Inference based on your S3 path. 1. How Path Inference Works StarRocks looks for Hive-style partitioning in the S3 URL you provide. * Example Path: 
<s3://my-bucket/data/year=2024/month=05/file1.parquet>
* Behavior: If your Iceberg table is partitioned by 
year
and 
month
, StarRocks will parse the folder names (
year=2024
and 
month=05
) and automatically register 
file1.parquet
into that specific partition in the Iceberg metadata. 2. What happens if the path is "flat"? If you point to a location that doesn't have 
key=value
folders: * Example: 
<s3://my-bucket/all_files/file1.parquet>
* Unpartitioned Table: If the Iceberg table is not partitioned, it works fine. * Partitioned Table: If your table is partitioned but the S3 path contains no partition information, the 
add_files
command may fail or result in errors because Iceberg requires every data file to be associated with a valid partition. 3. Key Rules for 
add_files
and Partitioning * Identity Partitions Only: Path inference typically works only for Identity partitions (where the partition value is exactly the same as the column value, like 
region=US
). It usually does not support complex transforms (like 
bucket(4, id)
or 
truncate(10, name)
) via the path. * Recursive Flag: When you set 
recursive = true
, StarRocks will crawl all subdirectories. It uses the folder structure of every file it finds to determine its partition. * Consistency is Critical: You must ensure the data inside the file actually matches the folder it is in. If you put data for 
year=2023
into a folder named 
year=2024
and run 
add_files
, Iceberg will record it as 2024. This will cause incorrect query results because the engine will prune (skip) that file when you query for 2023. Summary The S3 location is the primary way StarRocks determines the partition for the files you are adding. If your table is partitioned, ensure your S3 files are organized in 
column=value
folders so StarRocks can map them correctly. References • data_source/catalog/iceberg/procedures.md • data_source/catalog/iceberg/iceberg_catalog.md • sql-reference/sql-functions/table-functions/files.md • loading/s3.md References • loading/s3.md • sql-reference/sql-functions/table-functions/files.md • data_source/catalog/iceberg/procedures.md
e
@Rocky when doing
Copy code
sql
-- Correct version
SELECT * FROM FILES(
    "path" = "<s3://test/em-test/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
is it possible to say write the data to starrocks table but also to a iceberg table ?
r
Yes, it is possible to write the data to both, but there isn't a single command that writes to both simultaneously. Instead, you have two primary strategies depending on whether you want the Iceberg table to "own" the files or just "reference" them. Option 1: The "Metadata Link" Method (Highly Efficient) If you want the data to live in your own S3 path and have both StarRocks and Iceberg see it without duplicating the physical files in S3: 1. Load into StarRocks:
Copy code
sql
    INSERT INTO starrocks_internal_table
    SELECT * FROM FILES(
        "path" = "<s3://test/em-test/*.parquet>",
        "format" = "parquet",
        ...
    );
2. Register to Iceberg (Metadata only):
Copy code
sql
    ALTER TABLE iceberg_catalog.db.iceberg_table
    EXECUTE add_files(
        location = '<s3://test/em-test/>',
        file_format = 'parquet'
    );
* Result: Data is copied into StarRocks' internal storage (for maximum query speed), and the Iceberg table's metadata is updated to point to your original S3 files. No new Parquet files are created. ────────── Option 2: The "Double Sink" Method (Data Portability) If you want the Iceberg table to have its own managed copy of the data (e.g., in its own folder structure), you can run two separate 
INSERT
commands. StarRocks supports using 
FILES()
as a source for Iceberg since version 3.1. 1. Insert into StarRocks:
Copy code
sql
    INSERT INTO sr_table SELECT * FROM FILES(...);
2. Insert into Iceberg:
Copy code
sql
    INSERT INTO iceberg_catalog.db.iceberg_table SELECT * FROM FILES(...);
* Result: StarRocks will read the files from your path and write new Parquet files into the Iceberg table's managed S3 location. This ensures the Iceberg table is fully self-contained. ────────── Can I do this in one transaction? StarRocks supports SQL Transactions (using 
BEGIN
/ 
COMMIT
), which allow you to group multiple 
INSERT
statements. However, there is a key limitation: * Database Restriction: Currently, all target tables in a single transaction must reside in the same database. * Since your internal table is in a StarRocks database and the Iceberg table is in an External Catalog, you cannot currently wrap them in a single atomic transaction. You must execute them sequentially. Summary Table Goal | Method | Efficiency Minimize S3 Storage | 
INSERT
(SR) + 
add_files
(Iceberg) | Best (Files stay in one place) Max Portability | 
INSERT
(SR) + 
INSERT
(Iceberg) | Medium (Data is duplicated) Atomicity | Not currently supported across catalogs | N/A References • data_source/catalog/iceberg/procedures.md • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md • data_source/catalog/iceberg/DML.md References • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md • data_source/catalog/iceberg/procedures.md
e
@Rocky the pipe Files() when it reads from s3 what formats does it support ?
r
The 
FILES()
table function supports the following file formats when reading from S3 (or other remote storage): 1. Supported Formats * Parquet: Supported since version 3.1. * ORC: Supported since version 3.3 (some features available since 3.1). * CSV: Supported since version 3.3. When using CSV, you often need to specify additional parameters like 
column_separator
and 
row_delimiter
. * Avro: Supported since version 3.4.4 (for loading only, meaning 
INSERT INTO ... SELECT * FROM FILES(...)
). 2. Important Note on JSON * JSON File Format (
.json
): Currently not supported as a standalone file format by the 
FILES()
function. If your data is in JSON files, you must use Broker Load or Stream Load instead. * JSON Data Type: While the 
.json
file format is not supported, the 
JSON
data type is supported within Parquet, ORC, or CSV files (from v3.2+). For example, a Parquet file containing a column of type JSON can be read by 
FILES()
. 3. Complex Data Types From version 3.2 onwards, 
FILES()
can infer and read complex data types from Parquet and ORC, including: * 
ARRAY
* 
JSON
* 
MAP
* 
STRUCT
Example Usage for Different Formats
Copy code
sql
-- Parquet (Default)
SELECT * FROM FILES(
    "path" = "<s3://bucket/data.parquet>",
    "format" = "parquet",
    ...
);

-- CSV (Requires formatting options)
SELECT * FROM FILES(
    "path" = "<s3://bucket/data.csv>",
    "format" = "csv",
    "column_separator" = ",",
    ...
);
References • sql-reference/sql-functions/table-functions/files.md • loading/loading_introduction/feature-support-loading-and-unloading.md • loading/s3.md • using_starrocks/Flat_json.md References • using_starrocks/Flat_json.md • loading/loading_introduction/feature-support-loading-and-unloading.md • sql-reference/data-types/semi_structured/JSON.md • sql-reference/sql-functions/table-functions/files.md
e
@Rocky if we want to read from s3 using pipe Files() into a general table and then based on that table create a view that has another structure that will allow better performance for ui queries ?
r
This is a very common and recommended pattern in StarRocks for building high-performance dashboards. To achieve the best UI performance, you should use a Materialized View (MV) rather than a standard Logical View. Here is the recommended workflow: 1. Load data into a "Bronze" (Raw) Table First, use 
FILES()
to pull the raw data from S3 into a standard StarRocks internal table. This ensures the data is stored locally in StarRocks' optimized format (indexing, columnar storage).
Copy code
sql
-- Create table and load in one step
CREATE TABLE raw_s3_data AS 
SELECT * FROM FILES(
    "path" = "<s3://test/em-test/*.parquet>",
    "format" = "parquet",
    "aws.s3.use_aws_sdk_default_behavior" = "true",
    "aws.s3.region" = "us-east-1"
);
2. Create a Materialized View for the UI While a standard 
VIEW
just stores a query, a Materialized View actually stores the pre-computed result set. For UI queries (which usually involve aggregations or joins), an MV provides sub-second responses. You can use an Asynchronous Materialized View to transform the structure, perform joins, or pre-aggregate data:
Copy code
sql
CREATE MATERIALIZED VIEW ui_optimized_view
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Adjust refresh frequency as needed
AS
SELECT 
    event_date,
    user_id,
    count(event_id) as total_events,
    sum(revenue) as daily_revenue
FROM raw_s3_data
GROUP BY event_date, user_id;
Why this is better for UI Performance: * Pre-Aggregation: If your UI shows "Total Sales by Day," the MV stores only the daily totals. The UI doesn't have to scan millions of raw rows every time a user refreshes the page. * Query Rewrite: StarRocks has an intelligent optimizer. If a user queries the 
raw_s3_data
table with a query that matches the MV's logic, StarRocks can automatically route the query to the MV to speed it up, even if the user didn't specify the MV name. * Indexing: You can define a different sort key (
ORDER BY
) on the MV that is optimized specifically for the filters used in your UI (e.g., sorting by 
tenant_id
or 
timestamp
). Summary of Differences Feature | Logical View (
CREATE VIEW
) | Materialized View (
CREATE MV
) Storage | No data stored (Metadata only) | Stores data on disk Performance | Same as querying base table | Sub-second (Pre-computed) UI Suitability | Good for simplifying SQL | Best for Dashboards/High Concurrency Maintenance | None | Requires refresh (manual or scheduled) References • sql-reference/sql-statements/table_bucket_part_index/CREATE_MATERIALIZED_VIEW.md • using_starrocks/Materialized_view.md • sql-reference/sql-functions/table-functions/files.md • loading/InsertInto.md References • loading/InsertInto.md • sql-reference/sql-functions/table-functions/files.md • using_starrocks/Materialized_view.md • sql-reference/sql-statements/table_bucket_part_index/CREATE_MATERIALIZED_VIEW.md view) References • sql-reference/sql-functions/table-functions/files.md
e
@Rocky
Copy code
CREATE MATERIALIZED VIEW ui_optimized_view
REFRESH ASYNC EVERY (INTERVAL 1 HOUR) -- Adjust refresh frequency as needed
we want to see the data in the ui in near real time - that means within a minute - can starrocks support 1 minute interval in a good manner ? In addition would SYNC MATERIALIZED VIEW be better ? Also behind the scenes can the MATERIALIZED VIEW handle the same volume of data as a normal table (example it will hold data of the last 7 days that can be 100GB) ?
r
Yes, StarRocks can support a 1-minute refresh interval and handle 100GB of data in a Materialized View (MV) very effectively. However, there are specific architectural choices you should make to ensure "near real-time" performance without overloading the system. 1. Is a 1-minute interval supported? Yes. The default minimum refresh interval for Asynchronous MVs is 60 seconds (controlled by the 
materialized_view_min_refresh_interval
configuration). * Is it "good" for the system? It depends on how you refresh. If you refresh the entire 100GB every minute, it will be very expensive. To make this work smoothly at a 1-minute frequency, you must use Partitioning. * Partitioned Refresh: If your base table and MV are partitioned by time (e.g., by day or hour), StarRocks will only refresh the partitions where data has actually changed. If your S3 load only adds data to the "today" partition, the 1-minute refresh will only process that small increment of data, making it very fast and efficient. 2. Is 
SYNC MATERIALIZED VIEW
(Rollup) better? In StarRocks, a "Synchronous Materialized View" (often called a Rollup) is updated immediately when data is loaded into the base table. However, it has significant limitations: * Single Table Only: It cannot perform joins. * Limited Functions: It only supports simple aggregations like 
SUM
, 
MIN
, 
MAX
, 
COUNT
, 
BITMAP_UNION
, and 
HLL_UNION
. * Default Catalog Only: It only works on internal StarRocks tables. Recommendation: * Use Sync MV (Rollup) if your UI only needs simple aggregations on a single table. It provides the freshest data with zero refresh management. * Use Async MV (even with a 1-minute refresh) if you need complex logic, joins, or advanced SQL functions. 3. Can an MV handle 100GB / 7 days of data? Absolutely. In StarRocks, an Asynchronous MV is physically stored as a Local Table. * Scalability: 100GB is considered a relatively small-to-medium dataset for StarRocks. MVs can easily handle many terabytes. * Storage: Since it is a physical table, it benefits from all of StarRocks' performance features: * Columnar Storage: Only the columns needed by the UI are read. * Indexing: You can define a 
ORDER BY
(sort key) on the MV specifically for your UI filters (e.g., 
tenant_id
or 
timestamp
). * Z-Order/C-Index: Advanced indexing for high-dimensional filtering. Pro-Tip for Near Real-Time UI: If you need 1-minute freshness for 100GB of data, use this configuration for your Async MV:
Copy code
sql
CREATE MATERIALIZED VIEW ui_optimized_view
PARTITION BY (event_date) -- Crucial for performance
DISTRIBUTED BY HASH(user_id)
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
PROPERTIES (
    "partition_refresh_number" = "1" -- Refresh 1 partition at a time to reduce load
)
AS
SELECT ... FROM raw_s3_data;
Summary * 1-Minute Refresh: Fully supported; use partitioning to keep it lightweight. * Sync MV: Better for "zero-latency" simple math on one table. * 100GB Volume: Well within StarRocks' "comfort zone" for MVs. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/Materialized_view-single_table.md • administration/management/FE_configuration.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md References • faq/Sql_faq.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
e
@Rocky when you say "However, it has significant limitations: * Single Table Only: It cannot perform joins." my end goal is for ever row in the RAW table we create a row in the MV that has some more columns for the raw data and the original raw data (this allows better queries performance) so when you say MV It cannot perform joins -> do you mean we can make a query that says query the MV but left join to a states table ?
r
To clarify, the "No Joins" limitation applies only to Synchronous Materialized Views (Rollups) when you are defining them. For your specific goal—creating a row-for-row enriched version of your raw data—the Asynchronous Materialized View is exactly what you need. Here is how it works for your use case: 1. Can you join the MV to a "states" table? Yes, absolutely. Once an Asynchronous MV is created, StarRocks treats it like a regular table. You can write a query like this for your UI:
Copy code
sql
SELECT 
    mv.original_col, 
    mv.enriched_col, 
    s.state_name 
FROM ui_optimized_mv AS mv
LEFT JOIN states_table AS s ON mv.state_id = s.id
WHERE mv.event_time > now() - interval 1 hour;
2. Async MV vs. Sync MV for your "Raw + Extra Columns" goal Because you want a 1-to-1 mapping (one row in Raw = one row in MV) plus extra columns/transformations, here is why you must use Async MV: * Synchronous MV (Rollup): It is designed only for aggregation (e.g., 
SUM
, 
COUNT
). It cannot simply "copy" all raw rows and add columns via logic or joins. It also cannot perform joins inside its own definition. * Asynchronous MV: It is designed for exactly what you described. You can define it to join your RAW table with other metadata tables to create a "Wide Table" (Denormalized Table) that is refreshed every minute. 3. Recommended Pattern for "Near Real-Time" Performance To handle 100GB of data with a 1-minute refresh and keep it "row-for-row," use a Partitioned Async MV. If your raw data is partitioned by 
event_date
, define the MV like this:
Copy code
sql
CREATE MATERIALIZED VIEW ui_optimized_mv
PARTITION BY (event_date) -- This makes the 1-minute refresh very fast
REFRESH ASYNC EVERY (INTERVAL 1 MINUTE)
AS 
SELECT 
    r.*,                               -- All original raw data
    (r.price * r.tax_rate) as total,   -- New calculated column
    d.category_name                    -- Column joined from a dimension table
FROM raw_s3_data AS r
LEFT JOIN dimensions AS d ON r.category_id = d.id;
Why this works for you: 1. Row-for-row: It keeps all the original detail but pre-calculates the joins and math. 2. Performance: Your UI query doesn't have to do the heavy lifting; it just reads the "Wide Table" which is already stored on StarRocks' fast local disks. 3. Efficiency: Because it's partitioned, every minute StarRocks only looks at the "increment" (the latest data arriving in S3), so it doesn't re-process the whole 100GB every time. Summary: Forget Synchronous MVs for this use case. Use an *Asynchronous MV*; it supports the joins you need (both inside the definition and in your UI queries) and can handle the data volume and refresh frequency you're looking for. References • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/data_modeling_with_materialized_views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/Materialized_view-single_table.md References • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md
2 Views
Open in Slack
PreviousNext
Powered by