【已解决】Doris 2.1.0 版本物化视图使用Jdbc sqlserver Catelog外部表，视图没有数据

Viewed 132
mtmv 2.1

期望

  1. 视图创建成功，定时更新数据。

测试环境

sqlserver: Microsoft SQL Server 2014
doris: 2.1.0

视图脚本

CREATE MATERIALIZED VIEW dwd_advertiser_uvdd_mv BUILD IMMEDIATE REFRESH COMPLETE ON SCHEDULE EVERY 10 MINUTE
 DISTRIBUTED BY RANDOM BUCKETS 2
 PROPERTIES (
 "replication_num" = "1"
 )
 as  select  ssdsd.Month AS 'month'
		,oap.Name AS 'plate_name'
		,bu.BUName AS 'bu_name'
		,d.DepartmentName 
		,ssdsd.DriverId 
		,ssdsd.DriverName 
		,ssdsd.VehicleNo 
		,kpio.Name AS 'first_level_indicator'
		,kpit.Name AS 'second_level_indicator'
		,ssdsd.StatisticalDate 
		,ssdsd.Description 
		,ssdsd.CreatedUserId 
		,ssdsd.CreatedUserName 
		,ssdsd.CreatedDateTime  
from jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_Plate ssp 
inner join jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_PlateDepartment sspd  
on sspd.PlateCode=ssp.Code
inner join jdbc_sqlserver_catalog.dbo.MDT_Department d  ON d.IsActive=1 
and d.DepartmentID=sspd.DepartmentId
inner join jdbc_sqlserver_catalog.dbo.MDT_BUDepartment bud  
ON bud.BUDepartmentCode = d.DepartmentCode
inner join jdbc_sqlserver_catalog.dbo.MDT_BU bu  
ON bu.BUID=bud.BUID
inner join jdbc_sqlserver_catalog.dbo.OA_Plate oap  
on oap.IsActive=1 
and d.PlateId=oap.PlateID
left join jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_DriverScoreDetail ssdsd  
on ssdsd.IsActive=1 
and ssdsd.DepartmentId=sspd.DepartmentId 
inner join jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_KPIStandard kpis  
on kpis.Id=ssdsd.KPIStandardId
inner join jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_KPI kpit  
on kpit.Id=kpis.KPIId
inner join jdbc_sqlserver_catalog.dbo.ASM_SafetySupervision_KPI kpio  
on kpio.Id=kpit.ParentId

执行task日志

-- task执行完成，刷新状态没有变化。
image.png

1.查询视图没有数据

-- 强制刷新物化视图全部数据 或者 指定分区 才会刷新数据
REFRESH MATERIALIZED VIEW dwd_advertiser_uvdd_mv complete;
image.png

2.查询视图有数据

heye3
1 Answers

这个外部表的异步物化视图我来验证下的。
对了，咱们有测试过内表吗？我这边之前测试内表是可以的。

阿渊@SelectDB5460