关于StarRocks --- REPLACE_IF_NOT_NULL场景

关于REPLACE_IF_NOT_NULL

这个聚合类型的含义是当且仅当新导入数据是非NULL值时会发生替换行为， 如果新导入的数据是NULL，那么StarRocks仍然会保留原值。 注意：如果用在建表时REPLACE_IF_NOT_NULL列指定了NOT NULL，那么StarRocks仍然会将其转化NULL，不会向用户报错。

建表示例：

create table t1_not_null (
inc_date datetime,
uid int,
v1 string  REPLACE_IF_NOT_NULL ,
v2 string  REPLACE_IF_NOT_NULL ,
v3 int  REPLACE_IF_NOT_NULL )
AGGREGATE KEY(`inc_date`, `uid`)
DISTRIBUTED BY HASH(`uid`) BUCKETS 3
;

测试数据，插入1条

insert into t1_not_null values
('2021-06-22', 101, '101-v1', '101-v2', 1);

结果查询

mysql> select * from t1_not_null;
+---------------------+------+--------+--------+------+
| inc_date            | uid  | v1     | v2     | v3   |
+---------------------+------+--------+--------+------+
| 2021-06-22 00:00:00 |  101 | 101-v1 | 101-v2 |    1 |
+---------------------+------+--------+--------+------+
1 row in set (0.00 sec)

插入第2条

insert into t1_not_null values ('2021-06-22', 101, null, '101-v2-test-replace', 2);

结果查询
v1不变，v2,v3被更新

mysql> select * from t1_not_null;
+---------------------+------+--------+---------------------+------+
| inc_date            | uid  | v1     | v2                  | v3   |
+---------------------+------+--------+---------------------+------+
| 2021-06-22 00:00:00 |  101 | 101-v1 | 101-v2-test-replace |    2 |
+---------------------+------+--------+---------------------+------+
1 row in set (0.01 sec)

插入第3条

• 新的key

insert into t1_not_null values
('2021-06-22', 102, '102-v1', '102-v2', null);

mysql> select * from t1_not_null;
+---------------------+------+--------+---------------------+------+
| inc_date            | uid  | v1     | v2                  | v3   |
+---------------------+------+--------+---------------------+------+
| 2021-06-22 00:00:00 |  101 | 101-v1 | 101-v2-test-replace |    2 |
| 2021-06-22 00:00:00 |  102 | 102-v1 | 102-v2              | NULL |
+---------------------+------+--------+---------------------+------+
2 rows in set (0.01 sec)