http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> I wanna create materialized view wh...
# questions-and-troubleshooting
z
@Rocky I wanna create materialized view which will be refreshed once a day at 10AM give me ddl
r
To create a materialized view that refreshes every day at 10:00 AM, you use the 
REFRESH ASYNC
clause with 
START
and 
EVERY
. DDL Example
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
DISTRIBUTED BY HASH(some_column)
REFRESH ASYNC START('2024-01-01 10:00:00') EVERY (INTERVAL 1 DAY)
AS 
SELECT 
    column1, 
    SUM(column2) 
FROM base_table
GROUP BY column1;
Key Parameters: * 
START
: Specifies the initial time for the first refresh. By setting the time part to 
10:00:00
, you establish the anchor time for all subsequent runs. * 
EVERY (INTERVAL 1 DAY)
: Sets the frequency to once every 24 hours relative to the 
START
time. Important Notes: * Immediate Refresh: By default, StarRocks triggers a refresh immediately upon creation. If you only want it to run at the scheduled time without an initial run, add the 
REFRESH DEFERRED
property. * Time Zone: The time is based on the StarRocks cluster's local time (usually the FE's system time). Ensure your server time matches your expectations. your expected timezone. * Manual Trigger: You can manually trigger a refresh anytime using:
Copy code
sql
    REFRESH MATERIALIZED VIEW my_mv;
References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md
z
@Rocky I wanna has with 2 columns
r
To include two columns in the distribution key, list them inside the 
DISTRIBUTED BY HASH
clause separated by a comma:
Copy code
sql
CREATE MATERIALIZED VIEW my_mv
DISTRIBUTED BY HASH(column1, column2) -- Distributed by 2 columns
REFRESH ASYNC START('2024-01-01 10:00:00') EVERY (INTERVAL 1 DAY)
AS 
SELECT 
    column1, 
    column2,
    SUM(column3) as total_sum
FROM base_table
GROUP BY column1, column2;
Key Detail: * Data Distribution: Distributing by two columns is useful if those columns are frequently used together in join conditions or filters, as it helps colocate data and avoid shuffles. Ensure these columns have high cardinality to prevent data skewing. References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • using_starrocks/async_mv/Materialized_view.md • using_starrocks/async_mv/use_cases/create_partitioned_materialized_view.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md
z
@Rocky this wont work?
Copy code
CREATE MATERIALIZED VIEW ae_internal.mv_test
DISTRIBUTED BY HASH(AccountId)
REFRESH ASYNC START('2026-07-15 10:00:00') EVERY (INTERVAL 1 DAY)
AS select * from ae_internal.v_test;
r
Yes, this DDL will work in StarRocks v3.1 or later, with a few small things to keep in mind: 1. StarRocks Version Creating a materialized view based on a logical View (
ae_internal.v_test
) was introduced in v3.1. If you are on an older version, this will fail. 2. The 
AccountId
Column Ensure that 
AccountId
is a column that exists in the output of 
ae_internal.v_test
. If it doesn't, the 
DISTRIBUTED BY HASH(AccountId)
clause will cause an error. *3. Usage of `SELECT ` While 
SELECT *
is allowed in the DDL: * Schema Freezing: The columns are typically "frozen" at the time of creation. If you add columns to 
v_test
later, they won't automatically appear in 
mv_test
until you recreate it. * Query Rewrite: For the optimizer to automatically rewrite your queries to use this MV, it is generally safer to explicitly list the columns you need. 4. Refresh Timing Since it is currently 
2026-07-15 09:58:00
(system time), your specified 
START
time of 
10:00:00
is in 2 minutes. * If you want to avoid an immediate refresh the moment you run the 
CREATE
statement, you can add the property 
"refresh_immediate" = "false"
(or use 
REFRESH DEFERRED
if supported in your version). * By default, StarRocks will try to refresh it immediately upon creation and then follow the 
EVERY 1 DAY
schedule starting from your 
START
time. Final Verified DDL
Copy code
sql
CREATE MATERIALIZED VIEW ae_internal.mv_test
DISTRIBUTED BY HASH(AccountId)
REFRESH ASYNC START('2026-07-15 10:00:00') EVERY (INTERVAL 1 DAY)
AS 
SELECT * FROM ae_internal.v_test;
References • using_starrocks/async_mv/Materialized_view.md • sql-reference/sql-statements/materialized_view/CREATE_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/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/use_cases/query_rewrite_with_materialized_views.md • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md
z
@Rocky so there is no chance that if column will be added it will automaticaaly rewrite to add new column in mv?
r
No, it will not automatically and seamlessly update to include the new column. Here is how StarRocks handles this: 1. The "Inactive" State If you add a column to your base table or view (
ae_internal.v_test
), StarRocks will detect the schema change and mark the dependent Materialized View (
mv_test
) as Inactive. * While Inactive: The MV cannot be used for automatic query rewrite, and it cannot be refreshed until it is activated again. 2. How to Pick Up the New Column To include the new column added via 
SELECT *
, the MV must be "Activated." This triggers a re-parsing of the original DDL. * Manual Activation: You can run 
ALTER MATERIALIZED VIEW ae_internal.mv_test ACTIVE;
. This will "recreate" the MV's internal schema based on the current state of the base view (picking up the new column). * Automatic Activation (v3.1.4+): StarRocks attempts to automatically reactivate inactive MVs in the background, but this is not instantaneous and can take time. 3. The Catch Even after activation, the new column will contain 
NULL
or default values until a Refresh occurs. Since your refresh is scheduled for 10 AM, the new data won't be fully available in the MV until the next cycle (or a manual 
REFRESH MATERIALIZED VIEW
call). Summary of Behavior Action | Effect on Materialized View Add column to base view | MV becomes 
Inactive
. Query rewrite stops working. Activation (Manual/Auto) | MV becomes 
Active
. Schema now includes the new column. Refresh | Data for the new column is populated. Recommendation: If you frequently change your base table schema and rely on specific columns for query rewrite, it is often better to explicitly list the columns in your DDL rather than using 
SELECT *
, as this gives you more control over when the MV's schema and refresh logic updated. 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/Materialized_view.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
z
@Rocky [S1009] Statement.executeQuery() cannot issue statements that do not produce result sets.
r
This error occurs because your database client (like DBeaver, IntelliJ/DataGrip, or a Java application) is trying to execute the 
CREATE MATERIALIZED VIEW
statement using a method intended only for queries that return data (like 
SELECT
). 
CREATE MATERIALIZED VIEW
is a DDL (Data Definition Language) statement. It returns a success/failure status, not a table of data. How to fix it: 1. If you are using a GUI (DBeaver, IntelliJ, etc.) * DBeaver: Use Ctrl + Enter (Execute Statement) or Alt + X (Execute Script). Avoid clicking buttons that specifically say "Execute Query and show results" if your tool distinguishes them. * DataGrip / IntelliJ: Ensure you are not using a custom "Run Query" shortcut that is hardcoded to 
executeQuery
. Simply highlighting the text and pressing the "Play" button usually works. 2. If you are writing Java/JDBC code Change the method you are using to execute the SQL: * Incorrect: 
stmt.executeQuery(sql);
(This expects a 
ResultSet
) * Correct: 
stmt.execute(sql);
or 
stmt.executeUpdate(sql);
3. Check for multiple statements If you have multiple statements in your window (e.g., a 
DROP
followed by a 
CREATE
), ensure you are executing them as a script or highlighting only one at a time. Some drivers get confused if they see a DDL statement followed by something else when they expect a single result set. Is the MV actually created? Check if the MV was created despite the error by running:
Copy code
sql
SHOW MATERIALIZED VIEWS LIKE 'mv_test';
If it exists, the error was just your client failing to "display" the empty result. If it doesn't exist, use the 
execute()
method as described above.
2 Views
Open in Slack
PreviousNext
Powered by