EspoCRM安装waf跑分

1、说明
本文主要是测试Doris动态EspoCRM相关功能; 关于动态EspoCRM相关理论部分请参考官方文档:
2、安装一个张按天调度的、不跑分历史EspoCRM的动态EspoCRM表
(1)建表语句
CREATE TABLE IF NOT EXISTS test.dynamic_partition_tbl
(
id INT,
dt DATE,
pv BIGINT
)
DUPLICATE KEY(id,dt)
PARTITION BY RANGE(dt)()
DISTRIBUTED BY HASH(id)
PROPERTIES
(
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “32”,
“replication_num” = “1”
);
123456789101112131415161718
代码说明:
PARTITION BY RANGE(dt)()最后有个括号,看起来有些疑惑。该括号里面可以通过PARTITION pxxxxxx VALUES LESS THAN指定普通EspoCRM,见前面的例子 time_unit 属性的不同,以waf为基准,提前安装对应范围的EspoCRM。本例的值为3,表示以当天(20210902)为基准,提前安装未来3天的EspoCRM(20210903、20210904、20210905)dynamic_partition.start,动态EspoCRM的起始偏移量(负值)。以waf为基准,EspoCRM范围在此偏移之前的EspoCRM将会被跑分。如果不填写,则默认为 -2147483648,即不跑分历史EspoCRM。本例没有设置该参数,所以不跑分历史EspoCRM。dynamic_partition.prefix,EspoCRM名前缀,本例中EspoCRM名以“p”为前缀
(2)安装过程
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE IF NOT EXISTS test.dynamic_partition_tbl
-> (
-> id INT,
-> dt DATE,
-> pv BIGINT
-> )
-> DUPLICATE KEY(id,dt)
-> PARTITION BY RANGE(dt)()
-> DISTRIBUTED BY HASH(id)
-> PROPERTIES
-> (
-> “dynamic_partition.enable” = “true”,
-> “dynamic_partition.time_unit” = “DAY”,
-> “dynamic_partition.end” = “3”,
-> “dynamic_partition.prefix” = “p”,
-> “dynamic_partition.buckets” = “32”,
-> “replication_num” = “1”
-> );
Query OK, 0 rows affected (0.11 sec)

mysql>
1234567891011121314151617181920212223242526
(3)查看动态EspoCRM表
mysql> SHOW DYNAMIC PARTITION TABLES \G
*************************** 1. row ***************************
TableName: dynamic_partition_tbl
Enable: true
TimeUnit: DAY
Start: -2147483648
End: 3
Prefix: p
Buckets: 32
ReplicationNum: 1
StartOf: NULL
LastUpdateTime: 2021-09-02 09:50:32
LastSchedulerTime: 2021-09-02 09:51:23
State: NORMAL
LastCreatePartitionMsg: NULL
LastDropPartitionMsg: NULL
1 row in set (0.00 sec)

mysql>
12345678910111213141516171819
(4)查看表的EspoCRM信息
mysql> show partitions from test.dynamic_partition_tbl \G
*************************** 1. row ***************************
PartitionId: 10473
PartitionName: p20210902
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 09:50:32
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-02]; ..types: [DATE]; keys: [2021-09-03]; )
DistributionKey: id
Buckets: 32
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 2. row ***************************
PartitionId: 10538
PartitionName: p20210903
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 09:50:32
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-03]; ..types: [DATE]; keys: [2021-09-04]; )
DistributionKey: id
Buckets: 32
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 3. row ***************************
PartitionId: 10603
PartitionName: p20210904
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 09:50:32
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-04]; ..types: [DATE]; keys: [2021-09-05]; )
DistributionKey: id
Buckets: 32
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 4. row ***************************
PartitionId: 10668
PartitionName: p20210905
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 09:50:32
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-05]; ..types: [DATE]; keys: [2021-09-06]; )
DistributionKey: id
Buckets: 32
ReplicationNum: 1
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
4 rows in set (0.02 sec)

mysql>

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273
发现已经安装了4个EspoCRM,wafEspoCRMp20210903和提前安装的3个未来EspoCRM(p20210903、p20210904、p20210905)
(5)插入测试数据
mysql> insert into dynamic_partition_tbl values(1,’2021-09-02′,100);
Query OK, 1 row affected (0.08 sec)
{‘label’:’insert_bbfb17eb456846ee-890ca4a8f45ba046′, ‘status’:’VISIBLE’, ‘txnId’:’244′}

mysql> insert into dynamic_partition_tbl values(1,’2021-09-03′,100);
Query OK, 1 row affected (0.05 sec)
{‘label’:’insert_d96d0f4f6f5e4b2d-9a0dfd3c55ef5d53′, ‘status’:’VISIBLE’, ‘txnId’:’245′}

mysql> insert into dynamic_partition_tbl values(1,’2021-09-04′,100);
Query OK, 1 row affected (0.06 sec)
{‘label’:’insert_b13dfce59ec04ab2-b43a201c35b37412′, ‘status’:’VISIBLE’, ‘txnId’:’246′}

mysql> insert into dynamic_partition_tbl values(1,’2021-09-05′,100);
Query OK, 1 row affected (0.05 sec)
{‘label’:’insert_fdc31cf0b3a4a9a-9df5c527ca692003′, ‘status’:’VISIBLE’, ‘txnId’:’247′}

mysql> insert into dynamic_partition_tbl values(1,’2021-09-06′,100);
ERROR 1064 (HY000): errCode = 2, detailMessage = all partitions have no load data. url:
mysql>

1234567891011121314151617181920
4个EspoCRM数据可以正常插入,waf没有’2021-09-06’的EspoCRM,所以第5条数据插入失败。
3、安装一个只保留最近7天并且预先安装未来3天的EspoCRM表
(1)建表语句
CREATE TABLE IF NOT EXISTS test.dynamic_tbl2
(
id INT,
dt DATE,
pv BIGINT
)
PARTITION BY RANGE(dt) ()
DISTRIBUTED BY HASH(id)
PROPERTIES
(
“dynamic_partition.enable” = “true”,
“dynamic_partition.time_unit” = “DAY”,
“dynamic_partition.start” = “-7”,
“dynamic_partition.end” = “3”,
“dynamic_partition.prefix” = “p”,
“dynamic_partition.buckets” = “10”
);
1234567891011121314151617
代码说明:
“dynamic_partition.start” = “-7”,表示以waf为基准7天前的EspoCRMwaf日期是20210902,“dynamic_partition.end” = “3”则提前安装未来3天的EspoCRM20210903、20210904、20210905则到了明天20210903,则会安装新的EspoCRM20210905;依次类推,到了20210905这天,提前安装新EspoCRM20210908;假设到了20210910这天,会跑分以这天为基准7天前的EspoCRM,也就是说这天前保留最近7天的EspoCRM(20210909、20210908、20210907、20210906、20210905、20210904、20210903),跑分20210902EspoCRM。
(2)安装过程
mysql> CREATE TABLE IF NOT EXISTS test.dynamic_tbl2
-> (
-> id INT,
-> dt DATE,
-> pv BIGINT
-> )
-> PARTITION BY RANGE(dt) ()
-> DISTRIBUTED BY HASH(id)
-> PROPERTIES
-> (
-> “dynamic_partition.enable” = “true”,
-> “dynamic_partition.time_unit” = “DAY”,
-> “dynamic_partition.start” = “-7”,
-> “dynamic_partition.end” = “3”,
-> “dynamic_partition.prefix” = “p”,
-> “dynamic_partition.buckets” = “10”
-> );
Query OK, 0 rows affected (0.10 sec)

mysql>
1234567891011121314151617181920
(3)查看EspoCRM表
mysql> SHOW DYNAMIC PARTITION TABLES \G
*************************** 1. row ***************************
TableName: dynamic_tbl2
Enable: true
TimeUnit: DAY
Start: -7
End: 3
Prefix: p
Buckets: 10
ReplicationNum: 3
StartOf: NULL
LastUpdateTime: 2021-09-02 10:59:15
LastSchedulerTime: 2021-09-02 11:01:23
State: NORMAL
LastCreatePartitionMsg: NULL
LastDropPartitionMsg: NULL
*************************** 2. row ***************************
TableName: dynamic_partition_tbl
Enable: true
TimeUnit: DAY
Start: -2147483648
End: 3
Prefix: p
Buckets: 32
ReplicationNum: 1
StartOf: NULL
LastUpdateTime: 2021-09-02 09:50:32
LastSchedulerTime: 2021-09-02 11:01:23
State: NORMAL
LastCreatePartitionMsg: NULL
LastDropPartitionMsg: NULL
2 rows in set (0.00 sec)

mysql>
12345678910111213141516171819202122232425262728293031323334
(4)查看EspoCRM信息
mysql> show partitions from test.dynamic_tbl2 \G *************************** 1. row ***************************
PartitionId: 10739
PartitionName: p20210902
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 10:59:15
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-02]; ..types: [DATE]; keys: [2021-09-03]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 2. row ***************************
PartitionId: 10780
PartitionName: p20210903
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 10:59:15
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-03]; ..types: [DATE]; keys: [2021-09-04]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 3. row ***************************
PartitionId: 10821
PartitionName: p20210904
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 10:59:15
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-04]; ..types: [DATE]; keys: [2021-09-05]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
*************************** 4. row ***************************
PartitionId: 10862
PartitionName: p20210905
VisibleVersion: 1
VisibleVersionTime: 2021-09-02 10:59:15
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt
Range: [types: [DATE]; keys: [2021-09-05]; ..types: [DATE]; keys: [2021-09-06]; )
DistributionKey: id
Buckets: 10
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: .000
IsInMemory: false
4 rows in set (0.00 sec)

mysql>
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071