HI everyone. First post here 🙂
StarRocks 4.0.0 using the docker image starrocks/allin1-ubuntu.
I'm trying to create a materialized view which includes some JOINS between tables from a SQL Server database registered in the external catalogue.
The creation itself works fine, but the refresh does not work, not even the first sync with the external DB.
The creation script is more or less like this one:
CREATE MATERIALIZED VIEW daily_report
DISTRIBUTED BY HASH(INSTANCEID) BUCKETS 16
REFRESH ASYNC EVERY (INTERVAL 1 DAY)
AS
SELECT
[list of columns and aliases here]
FROM external.sqlserver.table r
LEFT JOIN external.sqlserver.table2 d
ON d.ITEM_ID = r.ID
LEFT JOIN external.sqlserver.table3 p
ON p.OBJECT_ID = r.ID
When you take look at the task runs, you can see the following error:
_Refresh mv daily_report failed after 1 times, try lock failed: 0, error-msg : java.lang.IndexOutOfBoundsException: Index 0 out of bounds for length 0_
at java.base/jdk.internal.util.Preconditions.outOfBounds(Preconditions.java:64)
at java.base/jdk.internal.util.Preconditions.outOfBoundsCheckIndex(Preconditions.java:70)
at java.base/jdk.internal.util.Preconditions.checkIndex(Preconditions.java:266)
at java.base/java.util.Objects.checkIndex(Objects.java:361)
at java.base/java.util.ArrayList.get(ArrayList.java:427)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.getSelectedPartitionInfos(PartitionBasedMvRefreshProcessor.java:1452)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.getRefreshedPartitionInfos(PartitionBasedMvRefreshProcessor.java:1426)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.updateBaseTablePartitionSnapshotInfos(PartitionBasedMvRefreshProcessor.java:1331)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedView(PartitionBasedMvRefreshProcessor.java:515)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doRefreshMaterializedViewWithRetry(PartitionBasedMvRefreshProcessor.java:430)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.doMvRefresh(PartitionBasedMvRefreshProcessor.java:382)
at com.starrocks.scheduler.PartitionBasedMvRefreshProcessor.processTaskRun(PartitionBasedMvRefreshProcessor.java:215)
at com.starrocks.scheduler.TaskRun.executeTaskRun(TaskRun.java:352)
at com.starrocks.scheduler.TaskRunExecutor.lambda$executeTaskRun$0(TaskRunExecutor.java:60)
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)
It seems to be something related to the partition based refresh, but I cannot use
PARTITION BY [columnY]
because no base column from the external DB is used for partitioning.
Any idea about how I could take advantage of using materialized views using an external SQL Server DB?
I get this error every time I try to create a materialized view with different tables and JOINs, so it is not a matter of how I build the MV (I guess)