背景：创建异步物化视图后刷新失败，查看errormsg为：
com.starrocks.common.UserException: Bad page: checksum mismatch (actual=1411277864 vs expect=2922535458), file=/data/emr/starrocks/be/storage/data/524/2452185/1119514271/020000000017c064b042fcc077703d196a7cda02d4f10192_0.dat
################################################################################
base table 1 ddl
CREATE TABLE `interactions` (
`address` varchar(100) NOT NULL COMMENT "",
`hash` varchar(100) NOT NULL COMMENT "",
`index` int(11) NOT NULL COMMENT "",
`internal_index` varchar(50) NOT NULL COMMENT "",
`block_timestamp` datetime NOT NULL COMMENT "",
`flow_type` varchar(10) NOT NULL COMMENT "",
`block_number` int(11) NOT NULL COMMENT "",
`contract_method_id` varchar(100) NULL COMMENT "",
`interact_address` varchar(100) NULL COMMENT "",
`amount_raw` varchar(100) NULL COMMENT "",
`token_symbol` varchar(100) NULL COMMENT ""
) ENGINE=OLAP
UNIQUE KEY(`address`, `hash`, `index`, `internal_index`, `block_timestamp`, `flow_type`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`address`) BUCKETS 3000
base table 2 ddl
CREATE TABLE `asset_flow` (
`address` varchar(100) NOT NULL COMMENT "",
`hash` varchar(100) NOT NULL COMMENT "",
`index` int(11) NOT NULL COMMENT "",
`internal_index` varchar(50) NOT NULL COMMENT "",
`block_timestamp` datetime NOT NULL COMMENT "",
`asset_address` varchar(100) NOT NULL COMMENT "",
`flow_type` varchar(10) NOT NULL COMMENT "",
`block_number` int(11) NOT NULL COMMENT "",
`interact_address` varchar(100) NULL COMMENT "",
`nft_token_id` varchar(100) NULL COMMENT "",
`amount_raw` varchar(100) NULL COMMENT "",
INDEX asset_address (`asset_address`) USING BITMAP COMMENT ''
) ENGINE=OLAP
UNIQUE KEY(`address`, `hash`, `index`, `internal_index`, `block_timestamp`, `asset_address`, `flow_type`)
COMMENT "OLAP"
DISTRIBUTED BY HASH(`address`) BUCKETS 3000
物化视图查询如下：
with source_asset_flow as (
select
a.*,
b.protocol_slug
from asset_flow a
inner join contract_info b
on a.address = b.address
where a.timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 30 day)
)
, source_intercations as (
select
a.*
from interactions a
inner join contract_info b
on a.address = b.address
where a.timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 30 day)
)
, one_day_volume as (
select
protocol_slug,
sum(amount_raw) as value
from source_asset_flow
where timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 1 day)
)
, one_month_volume as (
select
protocol_slug,
sum(amount_raw) as value
from source_asset_flow
where timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 30 day)
)
, one_day_interaction as (
select
protocol_slug,
count(distinct hash) as num_of_tx,
count(distinct intercat_address) as num_of_users
from source_intercations
where timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 1 day)
)
, one_month_interaction as (
select
protocol_slug,
count(distinct hash) as num_of_tx,
count(distinct intercat_address) as num_of_users
from source_intercations
where timestamp >= date_sub(CURRENT_TIMESTAMP(), INTERVAL 30 day)
)
select
a.*,
b.value,
c.value,
d.num_of_tx,
d.num_of_users,
e.num_of_tx,
e.num_of_users
from contract_info a
left join one_day_volume b
on a.protocol_slug = b.protocol_slug
left join one_month_volume c
on a.protocol_slug = c.protocol_slug
left join one_day_interaction d
on a.protocol_slug = d.protocol_slug
left join one_month_interaction e
on a.protocol_slug = e.protocol_slug