http://starrocks.io logo
Join Slack
Powered by
<@U0A71G31CDV> give me create materialized view co...
# questions-and-troubleshooting
z
@Rocky give me create materialized view command which run in every hour
r
To create an asynchronous materialized view that refreshes every hour, use the 
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
clause. Syntax Example
Copy code
sql
CREATE MATERIALIZED VIEW my_hourly_mv
REFRESH ASYNC EVERY (INTERVAL 1 HOUR)
AS 
SELECT 
    column1, 
    SUM(column2) 
FROM base_table
GROUP BY column1;
Key Parameters: * 
REFRESH ASYNC
: Specifies that the refresh should happen asynchronously (not tied to base table commits). * 
EVERY (INTERVAL 1 HOUR)
: Sets the scheduled interval. You can also use 
MINUTE
, 
DAY
, or 
SECOND
. * 
START
(Optional): You can specify when the schedule should begin. If omitted, it starts immediately upon creation. * Example: 
REFRESH ASYNC START('2024-01-01 00:00:00') EVERY (INTERVAL 1 HOUR)
Additional Notes: * Manual Refresh: Even with a schedule, you can trigger an immediate refresh manually using: 
REFRESH MATERIALIZED VIEW my_hourly_mv;
* Base Table Requirements: For asynchronous MVs, the base tables can be StarRocks native tables or external tables (like Hive, Iceberg, etc.). References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md
z
@Rocky if it starts WITH command?
r
In StarRocks, you do not use the word 
WITH
after 
START
. The syntax simply uses 
START
followed by a timestamp in parentheses. Correct Syntax
Copy code
sql
CREATE MATERIALIZED VIEW my_hourly_mv
REFRESH ASYNC START ('2024-01-01 00:00:00') EVERY (INTERVAL 1 HOUR)
AS 
SELECT ... ;
Comparison * ✅ Correct: 
START ('2024-01-01 00:00:00')
* ❌ Incorrect: 
START WITH ('2024-01-01 00:00:00')
Key Details for the 
REFRESH
Clause: 1. 
START
(Optional): Specifies when the first refresh task should begin. If you omit this, the schedule starts immediately after the materialized view is created. 2. 
EVERY
(Required for scheduling): Specifies the interval. You must use 
INTERVAL
followed by the value and unit (e.g., 
INTERVAL 1 HOUR
, 
INTERVAL 1 DAY
). 3. `ASYNC`: This keyword is required before 
START
or 
EVERY
to indicate a scheduled background refresh. If you already created the materialized view and want to add or change the schedule, you can use the 
ALTER
command:
Copy code
sql
ALTER MATERIALIZED VIEW my_hourly_mv 
REFRESH ASYNC START ('2024-01-01 00:00:00') EVERY (INTERVAL 1 HOUR);
References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/ALTER_MATERIALIZED_VIEW.md References • sql-reference/sql-statements/materialized_view/CREATE_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md
z
@Rocky how to check all mat view refresh logs
r
To check the refresh logs for asynchronous materialized views, you can query the system views in the 
information_schema
database. 1. View Refresh History The 
task_runs
table contains the execution history of all MV refresh tasks.
Copy code
sql
SELECT * 
FROM information_schema.task_runs 
ORDER BY CREATE_TIME DESC;
Key columns to monitor: * 
STATE
: Shows if the refresh was 
SUCCESS
, 
FAILED
, 
RUNNING
, or 
PENDING
. * 
CREATE_TIME
/ 
FINISH_TIME
: The start and end times of the refresh. * 
ERROR_MESSAGE
: Provides the reason if a refresh task failed. * 
PROGRESS
: Shows the completion percentage for currently running tasks. ────────── 2. Filter Logs for a Specific Materialized View StarRocks assigns a internal 
TASK_NAME
(usually in the format 
mv-<id>
) to each materialized view. You can find the specific logs for your MV by joining the 
tasks
table:
Copy code
sql
SELECT 
    t.TASK_NAME,
    tr.STATE,
    tr.CREATE_TIME,
    tr.FINISH_TIME,
    tr.ERROR_MESSAGE
FROM information_schema.tasks t
JOIN information_schema.task_runs tr ON t.TASK_NAME = tr.TASK_NAME
WHERE t.TASK_NAME LIKE 'mv-%' -- Filter for MVs
ORDER BY tr.CREATE_TIME DESC;
3. Quick Status Check If you just want to see the current status and the last refresh time of all materialized views without full logs, you can use:
Copy code
sql
SHOW MATERIALIZED VIEWS;
Check the 
LastRefreshTime
and 
RefreshState
columns in the output. References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • sql-reference/information_schema/task_runs.md • using_starrocks/async_mv/Materialized_view.md References • using_starrocks/async_mv/troubleshooting_asynchronous_materialized_views.md • using_starrocks/async_mv/materialized_view_task_run_details.md • using_starrocks/async_mv/feature-support-asynchronous-materialized-views.md • using_starrocks/async_mv/Materialized_view.md
z
@Rocky why it has STATE - SKIPPED?
r
A 
STATE
of 
SKIPPED
 in the refresh logs (such as in 
information_schema.task_runs
) means that StarRocks detected no data changes in the base tables since the last successful refresh. To save system resources, StarRocks does not perform a refresh if it determines the materialized view is already up-to-date. This is a normal behavior for asynchronous materialized views. Why it happens: * No New Data: The base table(s) haven't received any new inserts, updates, or deletes. * Up-to-Date Partitions: If your MV is partitioned, StarRocks checks specific partitions. If the data in the underlying base table partitions hasn't changed, the refresh for those partitions is skipped. How to Force a Refresh: If you want to trigger a refresh regardless of whether the data has changed (for example, to verify a fix or force a calculation), you can use the 
FORCE
keyword:
Copy code
sql
-- Manually force a refresh of the entire MV
REFRESH MATERIALIZED VIEW my_hourly_mv FORCE;
How to verify: You can check which partitions were checked and why they might have been skipped by looking at the 
EXTRA_MESSAGE
column in the 
task_runs
table:
Copy code
sql
SELECT 
    TASK_NAME, 
    STATE, 
    EXTRA_MESSAGE 
FROM information_schema.task_runs 
WHERE STATE = 'SKIPPED'
ORDER BY CREATE_TIME DESC;
The 
EXTRA_MESSAGE
often contains JSON details about the refresh decision. References • sql-reference/information_schema/task_runs.md • sql-reference/sql-statements/materialized_view/SHOW_MATERIALIZED_VIEW.md • sql-reference/sql-statements/materialized_view/REFRESH_MATERIALIZED_VIEW.md References • sql-reference/information_schema/task_runs.md • sql-reference/information_schema/tasks.md • sql-reference/sql-statements/loading_unloading/ETL/SUBMIT_TASK.md • sql-reference/sql-functions/meta-functions/inspect_task_runs.md • sql-reference/information_schema/fe_tablet_schedules.md • using_starrocks/async_mv/materialized_view_task_run_details.md
2 Views
Open in Slack
PreviousNext
Powered by