GBase 8a 的Merge into 功能使用介绍

最新推荐文章于 2023-12-27 19:31:26 发布

kaixin.1

最新推荐文章于 2023-12-27 19:31:26 发布

阅读量1.7k

点赞数

文章标签：数据仓库

本文链接：https://blog.csdn.net/zzzPQ/article/details/124801563

版权

GBase 8a支持Merge 合并功能，混合了update和insert，本文介绍其使用方法。该功能指针对Hash分布表，否则会报错ERROR 1149 (42000): (GBA-02SC-1001) Can’t DO Merge when not able to build hash (redistribution) JOIN relationship between merge target and source.

语法

Merge into TABLE1 using TABLE2 on ( match condition)
WHEN MATCHED THEN UPDATE_CLUSE
WHEN NOT MATCHED THEN INSERT_CLUSE

说明

match_condition 表关联条件
UPDATE_CLUSE 更新操作的set 部分
INSERT_CLUSE 插入操作的字段列表和values值列表部分

改功能，只能用于Hash分布表，且关联条件包含Hash分布列。随机分布表做merge会报错

ERROR 1149 (42000): (GBA-02SC-1001) Can't DO Merge when not able to build hash (redistribution) JOIN relationship between merge target and source.

样例

t1 是主表，t2是变动表，要将t2的数据merge到主表T1

环境

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t2;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1212 | 2011-12-12 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
3 rows in set (Elapsed: 00:00:00.01)

Merge

匹配上的id=1被更新了，没匹配上的2和3被insert了。

gbase> merge into t1 using t2 on t1.id=t2.id when matched then update set t1.name=t2.name,t1.birth=t2.birth when not matched then insert(id,name,birth)values(t2.id,t2.name,t2.birth);
Query OK, 3 rows affected (Elapsed: 00:00:00.13)
Rows matched: 3  Changed: 3  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    9 | 9999 | 2011-09-09 |
|    1 | 1212 | 2011-12-12 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.01)

只包含Matched，用于更新

gbase> truncate table t1;
Query OK, 4 rows affected (Elapsed: 00:00:00.21)

gbase> insert into t1 values(1,'1111','2011-11-11'),(9,'9999','2011-09-09');
Query OK, 2 rows affected (Elapsed: 00:00:00.14)
Records: 2  Duplicates: 0  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> merge into t1 using t2 on t1.id=t2.id when matched then update set t1.name=t2.name,t1.birth=t2.birth;                      Query OK, 1 row affected (Elapsed: 00:00:00.17)
Rows matched: 1  Changed: 1  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    9 | 9999 | 2011-09-09 |
|    1 | 1212 | 2011-12-12 |
+------+------+------------+
2 rows in set (Elapsed: 00:00:00.01)

只包含NOT Matched用于插入新数据

gbase> truncate table t1;
Query OK, 2 rows affected (Elapsed: 00:00:00.16)

gbase> insert into t1 values(1,'1111','2011-11-11'),(9,'9999','2011-09-09');
Query OK, 2 rows affected (Elapsed: 00:00:00.12)
Records: 2  Duplicates: 0  Warnings: 0

gbase> merge into t1 using t2 on t1.id=t2.id when not matched then insert(id,name,birth)values(t2.id,t2.name,t2.birth);           Query OK, 2 rows affected (Elapsed: 00:00:00.17)
Rows matched: 2  Changed: 2  Warnings: 0

gbase> select * from t1;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | 1111 | 2011-11-11 |
|    9 | 9999 | 2011-09-09 |
|    2 | 2222 | 2022-12-12 |
|    3 | 3333 | 2033-12-12 |
+------+------+------------+
4 rows in set (Elapsed: 00:00:00.00)