测试异步物化视图,doris版本:doris-2.1.4-rc02
测试结果:测试用外部表创建异步物化视图异常,当外部表用大写时,提示表不存在;
用小写时,视图创建成功,但数据不存在
详细测试步骤如下:
第一步查询外部表----正常输出
mysql> select a.id
-> from oracle_232.INMON.F_PERSONAL_INFO a
-> join oracle_232.INMON.T_SEX b on a.sex=b.id limit 10;
+----------+
| id |
+----------+
| 38068938 |
| 38030325 |
| 38071508 |
| 246893 |
| 7758 |
| 7759 |
| 7760 |
| 7761 |
| 8336 |
| 8337 |
+----------+
10 rows in set (0.03 sec)
**第二步:使用第一步的sql创建异步物化视图 **
mysql> CREATE MATERIALIZED VIEW awh_test_materialized
-> BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 8 hour
-> DISTRIBUTED BY HASH(id) BUCKETS 10
-> PROPERTIES (
-> "replication_num" = "1"
-> )
-> as
-> select a.id
-> from oracle_232.INMON.F_PERSONAL_INFO a
-> join oracle_232.INMON.T_SEX b on a.sex=b.id limit 10;
-------------提示报错
ERROR 1105 (HY000): errCode = 2, detailMessage = Table [F_PERSONAL_INFO] does not exist in database [INMON].
第三步:将表名换成小写,查询正常
mysql> select a.id
-> from oracle_232.INMON.f_personal_info a
-> join oracle_232.INMON.t_sex b on a.sex=b.id limit 10
-> ;
+----------+
| id |
+----------+
| 38068938 |
| 38030325 |
| 38071508 |
| 246893 |
| 7758 |
| 7759 |
| 7760 |
| 7761 |
| 8336 |
| 8337 |
+----------+
10 rows in set (0.04 sec)
第四步:用小写表创建异步物化视图成功,但视图中数据不存在
mysql> CREATE MATERIALIZED VIEW awh_test_materialized
-> BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 8 hour
-> DISTRIBUTED BY HASH(id) BUCKETS 10
-> PROPERTIES (
-> "replication_num" = "1"
-> )
-> as
-> select a.id
-> from oracle_232.INMON.f_personal_info a
-> join oracle_232.INMON.t_sex b on a.sex=b.id limit 10;
Query OK, 0 rows affected (0.03 sec)
--查询物化视图状态
mysql> select * from mv_infos("database"="awhtest") where Name="awh_test_materialized";
+--------+-----------------------+-------------------+--------+--------------------+--------------+-------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------------------+--------------+----------------------------------------------+--------------------+
| Id | Name | JobName | State | SchemaChangeDetail | RefreshState | RefreshInfo | QuerySql | EnvInfo | MvProperties | MvPartitionInfo | SyncWithBaseTables |
+--------+-----------------------+-------------------+--------+--------------------+--------------+-------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------------------+--------------+----------------------------------------------+--------------------+
| 161240 | awh_test_materialized | inner_mtmv_161240 | NORMAL | | SUCCESS | BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 8 HOUR | select a.id
from oracle_232.INMON.f_personal_info a
join oracle_232.INMON.t_sex b on a.sex=b.id limit 10 | EnvInfo{ctlId='0', dbId='17353'} | {} | MTMVPartitionInfo{partitionType=SELF_MANAGE} | 1 |
+--------+-----------------------+-------------------+--------+--------------------+--------------+-------------------------------------------------------+----------------------------------------------------------------------------------------------------------+----------------------------------+--------------+----------------------------------------------+--------------------+
1 row in set (0.02 sec)
---数据不存在
mysql> select * from awh_test_materialized;
Empty set (0.00 sec)