【详述】手动创建oracle、sqlserver jdbc catalog，根据catalog 创建对应的物化视图，刷新时报错。
【是否存算分离】否
【StarRocks版本】3.3.7
【集群规模】1fe 3be
【背景】
CREATE EXTERNAL CATALOG create_oracle_user1
comment “创建oracle数据源”
PROPERTIES (“driver_class” = “oracle.jdbc.OracleDriver”,
“checksum” = “0b2a8e010df63e6feb396287d2ea7dbd”,
“driver_url” = “file:///opt/soft/StarRocks-3.3.7-centos-amd64/ojdbc8-19.3.0.0.jar”,
“type” = “jdbc”,
“user” = “user1”,
“jdbc_uri” = “jdbc:oracle:thin:@192.168.5.55:1521:xe”
)
CREATE EXTERNAL CATALOG create_sqlserver_ds4
comment “创建sqlserver数据源”
PROPERTIES (“driver_class” = “com.microsoft.sqlserver.jdbc.SQLServerDriver”,
“checksum” = “4f0d8f2012feb14541d9a5b32b01bf7a”,
“driver_url” = “file:///opt/soft/StarRocks-3.3.7-centos-amd64/mssql-jdbc-10.2.3.jre8.jar”,
“type” = “jdbc”,
“user” = “SA”,
“jdbc_uri” = “jdbc:sqlserver://192.168.5.55:14330;databaseName=ds4;trustServerCertificate=true”
)
CREATE MATERIALIZED VIEW sqlserver_view (FlightID, AirlineID, FlightNumber, Departure, Destination, DepartureTime, ArrivalTime)
DISTRIBUTED BY RANDOM
REFRESH MANUAL
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “3”,
“storage_medium” = “HDD”
)
AS SELECT T0.FlightID, T0.AirlineID, T0.FlightNumber, T0.Departure, T0.Destination, T0.DepartureTime, T0.ArrivalTime
FROM create_sqlserver_ds4.dbo.V_Flight AS T0 LEFT OUTER JOIN create_sqlserver_ds4.dbo.V_Passenger AS T1 ON T0.FlightID = T1.PassengerID;
CREATE MATERIALIZED VIEW oracle_view (AIRLINEID, AIRLINENAME, CONTACTNUMBER, HEADQUARTERS, FLIGHTID, FLIGHTAIRLINEID, FLIGHTNUMBER, DEPARTURE, DESTINATION, DEPARTURETIME, ARRIVALTIME)
DISTRIBUTED BY RANDOM
REFRESH MANUAL
PROPERTIES (
“replicated_storage” = “true”,
“replication_num” = “3”,
“storage_medium” = “HDD”
)
AS SELECT T0.AIRLINEID, T0.AIRLINENAME, T0.CONTACTNUMBER, T0.HEADQUARTERS, T1.FLIGHTID, T1.AIRLINEID AS FLIGHTAIRLINEID, T1.FLIGHTNUMBER, T1.DEPARTURE, T1.DESTINATION, T1.DEPARTURETIME, T1.ARRIVALTIME
FROM create_oracle_user1.USER1.V_AIRLINE AS T0 LEFT OUTER JOIN create_oracle_user1.USER1.V_FLIGHT AS T1 ON T0.AIRLINEID = T1.AIRLINEID;
