版本2.4.1
创建语句：
CREATE TABLE SAS_PRE_ORDER_DATA (
UUID varchar(32) NOT NULL COMMENT “”,
TRANS_DATE date NOT NULL COMMENT “”,
SOURCE_DATA_ID varchar(36) NOT NULL COMMENT “”,
ORIGINAL_SOURCE_DATA_ID varchar(36) NULL COMMENT “”,
SOURCE_SYSTEM varchar(2) NULL COMMENT “”,
TRIGGER_USER_ID varchar(20) NULL COMMENT “”,
TRIGGER_TYPE varchar(1) NULL COMMENT “”,
TRIGGER_TIME datetime NULL COMMENT “”,
ORIGINAL_TRIGGER_TIME datetime NULL COMMENT “”,
TRIGGER_AMOUNT decimal64(16, 2) NULL COMMENT “”,
TRANS_AMOUNT decimal64(16, 2) NULL COMMENT “”,
TRANS_FEE_AMOUNT decimal64(9, 2) NULL COMMENT “”,
RETURN_FEE_AMOUNT decimal64(9, 2) NULL COMMENT “”,
TRANS_TYPE varchar(2) NULL COMMENT “”,
TRAN_CD varchar(20) NULL COMMENT “”,
PARTNER_ID varchar(20) NULL COMMENT “”,
COUPON_INFO varchar(300) NULL COMMENT “”,
COUPON_CHANNEL varchar(300) NULL COMMENT “”,
SZLT_FLAG varchar(1) NULL COMMENT “”,
QR_HANDLE_MODE varchar(2) NULL COMMENT “”,
TRANS_ORGANIZATION_NO varchar(32) NULL COMMENT “”,
STM_DT varchar(10) NULL COMMENT “”,
REMARK varchar(256) NULL COMMENT “”,
BOOK_STATUS varchar(2) NULL COMMENT “”,
DEAL_SYSTEM varchar(2) NULL COMMENT “”,
CREATE_TIME datetime NULL COMMENT “”,
UPDATE_TIME datetime NULL COMMENT “”
) ENGINE=OLAP
PRIMARY KEY(UUID, TRANS_DATE)
COMMENT “OLAP”
PARTITION BY RANGE(TRANS_DATE)
(PARTITION p20220814 VALUES [(‘0000-01-01’), (‘2022-08-15’)),
PARTITION p20220815 VALUES [(‘2022-08-15’), (‘2022-08-16’)),
PARTITION p20220816 VALUES [(‘2022-08-16’), (‘2022-08-17’)),
PARTITION p20220817 VALUES [(‘2022-08-17’), (‘2022-08-18’)),
PARTITION p20220818 VALUES [(‘2022-08-18’), (‘2022-08-19’)),
PARTITION p20220819 VALUES [(‘2022-08-19’), (‘2022-08-20’)),
PARTITION p20220820 VALUES [(‘2022-08-20’), (‘2022-08-21’)),
PARTITION p20220821 VALUES [(‘2022-08-21’), (‘2022-08-22’)),
PARTITION p20220822 VALUES [(‘2022-08-22’), (‘2022-08-23’)),
PARTITION p20220823 VALUES [(‘2022-08-23’), (‘2022-08-24’)),
PARTITION p20220824 VALUES [(‘2022-08-24’), (‘2022-08-25’)),
PARTITION p20220825 VALUES [(‘2022-08-25’), (‘2022-08-26’)),
PARTITION p20220826 VALUES [(‘2022-08-26’), (‘2022-08-27’)),
PARTITION p20220827 VALUES [(‘2022-08-27’), (‘2022-08-28’)),
PARTITION p20220828 VALUES [(‘2022-08-28’), (‘2022-08-29’)),
PARTITION p20220829 VALUES [(‘2022-08-29’), (‘2022-08-30’)),
PARTITION p20220830 VALUES [(‘2022-08-30’), (‘2022-08-31’)),
PARTITION p20220831 VALUES [(‘2022-08-31’), (‘2022-09-01’)),
PARTITION p20220901 VALUES [(‘2022-09-01’), (‘2022-09-02’)),
PARTITION p20220902 VALUES [(‘2022-09-02’), (‘2022-09-03’)),
PARTITION p20220903 VALUES [(‘2022-09-03’), (‘2022-09-04’)),
PARTITION p20220904 VALUES [(‘2022-09-04’), (‘2022-09-05’)),
PARTITION p20220905 VALUES [(‘2022-09-05’), (‘2022-09-06’)),
PARTITION p20220906 VALUES [(‘2022-09-06’), (‘2022-09-07’)),
PARTITION p20220907 VALUES [(‘2022-09-07’), (‘2022-09-08’)),
PARTITION p20220908 VALUES [(‘2022-09-08’), (‘2022-09-09’)),
PARTITION p20220909 VALUES [(‘2022-09-09’), (‘2022-09-10’)),
PARTITION p20220910 VALUES [(‘2022-09-10’), (‘2022-09-11’)),
PARTITION p20220911 VALUES [(‘2022-09-11’), (‘2022-09-12’)),
PARTITION p20220912 VALUES [(‘2022-09-12’), (‘2022-09-13’)),
PARTITION p20220913 VALUES [(‘2022-09-13’), (‘2022-09-14’)),
PARTITION p20220914 VALUES [(‘2022-09-14’), (‘2022-09-15’)),
PARTITION p20220915 VALUES [(‘2022-09-15’), (‘2022-09-16’)),
PARTITION p20220916 VALUES [(‘2022-09-16’), (‘2022-09-17’)),
PARTITION p20220917 VALUES [(‘2022-09-17’), (‘2022-09-18’)),
PARTITION p20220918 VALUES [(‘2022-09-18’), (‘2022-09-19’)),
PARTITION p20220919 VALUES [(‘2022-09-19’), (‘2022-09-20’)),
PARTITION p20220920 VALUES [(‘2022-09-20’), (‘2022-09-21’)),
PARTITION p20220921 VALUES [(‘2022-09-21’), (‘2022-09-22’)),
PARTITION p20220922 VALUES [(‘2022-09-22’), (‘2022-09-23’)),
PARTITION p20220923 VALUES [(‘2022-09-23’), (‘2022-09-24’)),
PARTITION p20220924 VALUES [(‘2022-09-24’), (‘2022-09-25’)),
PARTITION p20220925 VALUES [(‘2022-09-25’), (‘2022-09-26’)),
PARTITION p20220926 VALUES [(‘2022-09-26’), (‘2022-09-27’)),
PARTITION p20220927 VALUES [(‘2022-09-27’), (‘2022-09-28’)),
PARTITION p20220928 VALUES [(‘2022-09-28’), (‘2022-09-29’)),
PARTITION p20220929 VALUES [(‘2022-09-29’), (‘2022-09-30’)),
PARTITION p20220930 VALUES [(‘2022-09-30’), (‘2022-10-01’)),
PARTITION p20221001 VALUES [(‘2022-10-01’), (‘2022-10-02’)),
PARTITION p20221002 VALUES [(‘2022-10-02’), (‘2022-10-03’)),
PARTITION p20221003 VALUES [(‘2022-10-03’), (‘2022-10-04’)),
PARTITION p20221004 VALUES [(‘2022-10-04’), (‘2022-10-05’)),
PARTITION p20221005 VALUES [(‘2022-10-05’), (‘2022-10-06’)),
PARTITION p20221006 VALUES [(‘2022-10-06’), (‘2022-10-07’)),
PARTITION p20221007 VALUES [(‘2022-10-07’), (‘2022-10-08’)),
PARTITION p20221008 VALUES [(‘2022-10-08’), (‘2022-10-09’)),
PARTITION p20221009 VALUES [(‘2022-10-09’), (‘2022-10-10’)),
PARTITION p20221010 VALUES [(‘2022-10-10’), (‘2022-10-11’)),
PARTITION p20221011 VALUES [(‘2022-10-11’), (‘2022-10-12’)),
PARTITION p20221012 VALUES [(‘2022-10-12’), (‘2022-10-13’)),
PARTITION p20221013 VALUES [(‘2022-10-13’), (‘2022-10-14’)),
PARTITION p20221014 VALUES [(‘2022-10-14’), (‘2022-10-15’)),
PARTITION p20221015 VALUES [(‘2022-10-15’), (‘2022-10-16’)),
PARTITION p20221016 VALUES [(‘2022-10-16’), (‘2022-10-17’)),
PARTITION p20221017 VALUES [(‘2022-10-17’), (‘2022-10-18’)),
PARTITION p20221018 VALUES [(‘2022-10-18’), (‘2022-10-19’)),
PARTITION p20221019 VALUES [(‘2022-10-19’), (‘2022-10-20’)),
PARTITION p20221020 VALUES [(‘2022-10-20’), (‘2022-10-21’)),
PARTITION p20221021 VALUES [(‘2022-10-21’), (‘2022-10-22’)),
PARTITION p20221022 VALUES [(‘2022-10-22’), (‘2022-10-23’)),
PARTITION p20221023 VALUES [(‘2022-10-23’), (‘2022-10-24’)),
PARTITION p20221024 VALUES [(‘2022-10-24’), (‘2022-10-25’)),
PARTITION p20221025 VALUES [(‘2022-10-25’), (‘2022-10-26’)),
PARTITION p20221026 VALUES [(‘2022-10-26’), (‘2022-10-27’)),
PARTITION p20221027 VALUES [(‘2022-10-27’), (‘2022-10-28’)),
PARTITION p20221028 VALUES [(‘2022-10-28’), (‘2022-10-29’)),
PARTITION p20221029 VALUES [(‘2022-10-29’), (‘2022-10-30’)),
PARTITION p20221030 VALUES [(‘2022-10-30’), (‘2022-10-31’)),
PARTITION p20221031 VALUES [(‘2022-10-31’), (‘2022-11-01’)),
PARTITION p20221101 VALUES [(‘2022-11-01’), (‘2022-11-02’)),
PARTITION p20221102 VALUES [(‘2022-11-02’), (‘2022-11-03’)),
PARTITION p20221103 VALUES [(‘2022-11-03’), (‘2022-11-04’)),
PARTITION p20221104 VALUES [(‘2022-11-04’), (‘2022-11-05’)),
PARTITION p20221105 VALUES [(‘2022-11-05’), (‘2022-11-06’)),
PARTITION p20221106 VALUES [(‘2022-11-06’), (‘2022-11-07’)),
PARTITION p20221107 VALUES [(‘2022-11-07’), (‘2022-11-08’)),
PARTITION p20221108 VALUES [(‘2022-11-08’), (‘2022-11-09’)),
PARTITION p20221109 VALUES [(‘2022-11-09’), (‘2022-11-10’)),
PARTITION p20221110 VALUES [(‘2022-11-10’), (‘2022-11-11’)),
PARTITION p20221111 VALUES [(‘2022-11-11’), (‘2022-11-12’)),
PARTITION p20221112 VALUES [(‘2022-11-12’), (‘2022-11-13’)),
PARTITION p20221113 VALUES [(‘2022-11-13’), (‘2022-11-14’)),
PARTITION p20221114 VALUES [(‘2022-11-14’), (‘2022-11-15’)),
PARTITION p20221115 VALUES [(‘2022-11-15’), (‘2022-11-16’)),
PARTITION p20221116 VALUES [(‘2022-11-16’), (‘2022-11-17’)),
PARTITION p20221117 VALUES [(‘2022-11-17’), (‘2022-11-18’)),
PARTITION p20221118 VALUES [(‘2022-11-18’), (‘2022-11-19’)),
PARTITION p20221119 VALUES [(‘2022-11-19’), (‘2022-11-20’)),
PARTITION p20221120 VALUES [(‘2022-11-20’), (‘2022-11-21’)),
PARTITION p20221121 VALUES [(‘2022-11-21’), (‘2022-11-22’)),
PARTITION p20221122 VALUES [(‘2022-11-22’), (‘2022-11-23’)),
PARTITION p20221123 VALUES [(‘2022-11-23’), (‘2022-11-24’)),
PARTITION p20221124 VALUES [(‘2022-11-24’), (‘2022-11-25’)),
PARTITION p20221125 VALUES [(‘2022-11-25’), (‘2022-11-26’)),
PARTITION p20221126 VALUES [(‘2022-11-26’), (‘2022-11-27’)),
PARTITION p20221127 VALUES [(‘2022-11-27’), (‘2022-11-28’)),
PARTITION p20221128 VALUES [(‘2022-11-28’), (‘2022-11-29’)),
PARTITION p20221129 VALUES [(‘2022-11-29’), (‘2022-11-30’)),
PARTITION p20221130 VALUES [(‘2022-11-30’), (‘2022-12-01’)),
PARTITION p20221201 VALUES [(‘2022-12-01’), (‘2022-12-02’)),
PARTITION p20221202 VALUES [(‘2022-12-02’), (‘2022-12-03’)),
PARTITION p20221203 VALUES [(‘2022-12-03’), (‘2022-12-04’)),
PARTITION p20221204 VALUES [(‘2022-12-04’), (‘2022-12-05’)),
PARTITION p20221205 VALUES [(‘2022-12-05’), (‘2022-12-06’)),
PARTITION p20221206 VALUES [(‘2022-12-06’), (‘2022-12-07’)),
PARTITION p20221207 VALUES [(‘2022-12-07’), (‘2022-12-08’)),
PARTITION p20221208 VALUES [(‘2022-12-08’), (‘2022-12-09’)),
PARTITION p20221209 VALUES [(‘2022-12-09’), (‘2022-12-10’)),
PARTITION p20221210 VALUES [(‘2022-12-10’), (‘2022-12-11’)),
PARTITION p20221211 VALUES [(‘2022-12-11’), (‘2022-12-12’)),
PARTITION p20221212 VALUES [(‘2022-12-12’), (‘2022-12-13’)),
PARTITION p20221213 VALUES [(‘2022-12-13’), (‘2022-12-14’)),
PARTITION p20221214 VALUES [(‘2022-12-14’), (‘2022-12-15’)),
PARTITION p20221215 VALUES [(‘2022-12-15’), (‘2022-12-16’)),
PARTITION p20221216 VALUES [(‘2022-12-16’), (‘2022-12-17’)),
PARTITION p20221217 VALUES [(‘2022-12-17’), (‘2022-12-18’)),
PARTITION p20221218 VALUES [(‘2022-12-18’), (‘2022-12-19’)),
PARTITION p20221219 VALUES [(‘2022-12-19’), (‘2022-12-20’)),
PARTITION p20221220 VALUES [(‘2022-12-20’), (‘2022-12-21’)),
PARTITION p20221221 VALUES [(‘2022-12-21’), (‘2022-12-22’)),
PARTITION p20221222 VALUES [(‘2022-12-22’), (‘2022-12-23’)),
PARTITION p20221223 VALUES [(‘2022-12-23’), (‘2022-12-24’)),
PARTITION p20221224 VALUES [(‘2022-12-24’), (‘2022-12-25’)),
PARTITION p20221225 VALUES [(‘2022-12-25’), (‘2022-12-26’)),
PARTITION p20221226 VALUES [(‘2022-12-26’), (‘2022-12-27’)),
PARTITION p20221227 VALUES [(‘2022-12-27’), (‘2022-12-28’)),
PARTITION p20221228 VALUES [(‘2022-12-28’), (‘2022-12-29’)),
PARTITION p20221229 VALUES [(‘2022-12-29’), (‘2022-12-30’)),
PARTITION p20221230 VALUES [(‘2022-12-30’), (‘2022-12-31’)),
PARTITION p20221231 VALUES [(‘2022-12-31’), (‘2023-01-01’)),
PARTITION p20230101 VALUES [(‘2023-01-01’), (‘2023-01-02’)),
PARTITION p20230102 VALUES [(‘2023-01-02’), (‘2023-01-03’)),
PARTITION p20230103 VALUES [(‘2023-01-03’), (‘2023-01-04’)),
PARTITION p20230104 VALUES [(‘2023-01-04’), (‘2023-01-05’)),
PARTITION p20230105 VALUES [(‘2023-01-05’), (‘2023-01-06’)),
PARTITION p20230106 VALUES [(‘2023-01-06’), (‘2023-01-07’)),
PARTITION p20230107 VALUES [(‘2023-01-07’), (‘2023-01-08’)),
PARTITION p20230108 VALUES [(‘2023-01-08’), (‘2023-01-09’)),
PARTITION p20230109 VALUES [(‘2023-01-09’), (‘2023-01-10’)),
PARTITION p20230110 VALUES [(‘2023-01-10’), (‘2023-01-11’)),
PARTITION p20230111 VALUES [(‘2023-01-11’), (‘2023-01-12’)),
PARTITION p20230112 VALUES [(‘2023-01-12’), (‘2023-01-13’)),
PARTITION p20230113 VALUES [(‘2023-01-13’), (‘2023-01-14’)),
PARTITION p20230114 VALUES [(‘2023-01-14’), (‘2023-01-15’)),
PARTITION p20230115 VALUES [(‘2023-01-15’), (‘2023-01-16’)),
PARTITION p20230116 VALUES [(‘2023-01-16’), (‘2023-01-17’)),
PARTITION p20230117 VALUES [(‘2023-01-17’), (‘2023-01-18’)),
PARTITION p20230118 VALUES [(‘2023-01-18’), (‘2023-01-19’)),
PARTITION p20230119 VALUES [(‘2023-01-19’), (‘2023-01-20’)),
PARTITION p20230120 VALUES [(‘2023-01-20’), (‘2023-01-21’)),
PARTITION p20230121 VALUES [(‘2023-01-21’), (‘2023-01-22’)),
PARTITION p20230122 VALUES [(‘2023-01-22’), (‘2023-01-23’)),
PARTITION p20230123 VALUES [(‘2023-01-23’), (‘2023-01-24’)),
PARTITION p20230124 VALUES [(‘2023-01-24’), (‘2023-01-25’)),
PARTITION p20230125 VALUES [(‘2023-01-25’), (‘2023-01-26’)),
PARTITION p20230126 VALUES [(‘2023-01-26’), (‘2023-01-27’)),
PARTITION p20230127 VALUES [(‘2023-01-27’), (‘2023-01-28’)),
PARTITION p20230128 VALUES [(‘2023-01-28’), (‘2023-01-29’)),
PARTITION p20230129 VALUES [(‘2023-01-29’), (‘2023-01-30’)),
PARTITION p20230130 VALUES [(‘2023-01-30’), (‘2023-01-31’)),
PARTITION p20230131 VALUES [(‘2023-01-31’), (‘2023-02-01’)),
PARTITION p20230201 VALUES [(‘2023-02-01’), (‘2023-02-02’)),
PARTITION p20230202 VALUES [(‘2023-02-02’), (‘2023-02-03’)),
PARTITION p20230203 VALUES [(‘2023-02-03’), (‘2023-02-04’)),
PARTITION p20230204 VALUES [(‘2023-02-04’), (‘2023-02-05’)),
PARTITION p20230205 VALUES [(‘2023-02-05’), (‘2023-02-06’)),
PARTITION p20230206 VALUES [(‘2023-02-06’), (‘2023-02-07’)),
PARTITION p20230207 VALUES [(‘2023-02-07’), (‘2023-02-08’)),
PARTITION p20230208 VALUES [(‘2023-02-08’), (‘2023-02-09’)),
PARTITION p20230209 VALUES [(‘2023-02-09’), (‘2023-02-10’)),
PARTITION p20230210 VALUES [(‘2023-02-10’), (‘2023-02-11’)),
PARTITION p20230211 VALUES [(‘2023-02-11’), (‘2023-02-12’)))
DISTRIBUTED BY HASH(UUID) BUCKETS 4
PROPERTIES (
“replication_num” = “3”,
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.time_zone” = “Asia/Shanghai”,
“dynamic_partition.start” = “-1825”,
“dynamic_partition.end” = “2”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “4”,
“in_memory” = “false”,
“storage_format” = “DEFAULT”,
“enable_persistent_index” = “false”
);
物化视图语句：
CREATE MATERIALIZED VIEW SAS_PRE_ORDER_DATA_PK_SOURCE_DATA_ID
COMMENT “MATERIALIZED_VIEW”
PARTITION BY (TRANS_DATE)
DISTRIBUTED BY HASH(SOURCE_DATA_ID) BUCKETS 2
REFRESH ASYNC EVERY(INTERVAL 1 DAY)
PROPERTIES (
“replication_num” = “3”,
“storage_medium” = “HDD”
)
AS SELECT ODS.SAS_PRE_ORDER_DATA.SOURCE_DATA_ID AS SOURCE_DATA_ID, ODS.SAS_PRE_ORDER_DATA.TRANS_DATE AS TRANS_DATE, ODS.SAS_PRE_ORDER_DATA.UUID AS UUID FROM ODS.SAS_PRE_ORDER_DATA；
fe.audit.log slow_query如下：
2023-02-09 19:38:16,710 [slow_query] |Client=|User=root|AuthorizedUser=|ResourceGroup=default_wg|Catalog=|Db=ODS|State=OK|ErrorCode=|Time=43488|ScanBytes=0|ScanRows=0|ReturnRows=0|CpuCostNs=0|MemCostBytes=0|StmtId=1|QueryId=24059dc9-a86e-11ed-8ce1-6cfe54388271|IsQuery=false|feIp=****|Stmt=insert overwrite SAS_PRE_ORDER_DATA_PK_SOURCE_DATA_ID SELECT ODS.SAS_PRE_ORDER_DATA.SOURCE_DATA_ID AS SOURCE_DATA_ID, ODS.SAS_PRE_ORDER_DATA.TRANS_DATE AS TRANS_DATE, ODS.SAS_PRE_ORDER_DATA.UUID AS UUID FROM ODS.SAS_PRE_ORDER_DATA|Digest=|PlanCpuCost=1.552069808E9|PlanMemCost=0.0
主键分区表是实时写入，有历史数据更新， 物化视图每天更新一次
请教下，物化视图每天更新的这一次，是查原表的所有分区数据吗？还是查原表的这一天（写入和更新）数据对应的所有分区？ 还是查原表插入的数据增量更新到物化视图上？