HIVE 引擎的分区表,
在INSERT 时仅指定各分区字段的名字,
分区值则由VALUES
子句或SELECT 子句来决定。
示例:动态分区INSERT
gbase> DROP TABLE IF EXISTS h3;
Query OK, 0 rows affected
gbase> CREATE TABLE h3(a INT, b INT) PARTITIONED BY (c INT, d INT) ENGINE='HIVE';
Query OK, 0 rows affected
gbase> INSERT INTO h3 PARTITION(c, d) values(1, 1, 100, 100);
Query OK, 1 row affected
gbase> INSERT INTO h3 PARTITION(c, d) values(2, 2, 200, 200);
Query OK, 1 row affected
gbase> SELECT * FROM h3 WHERE c > 0;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 1 | 100 | 100 |
| 2 | 2 | 200 | 200 |
+------+------+------+------+
2 rows in set
gbase> DROP TABLE IF EXISTS h4;
Query OK, 0 rows affected
gbase> CREATE TABLE h4(a INT, b INT, c INT, d INT) ENGINE='HIVE';
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 758
Query OK, 0 rows affected
gbase> INSERT INTO h4 VALUES(3, 3, 200, 100);
Query OK, 1 row affected
gbase> INSERT INTO h4 VALUES(4, 4, 300, 300);
Query OK, 1 row affected
gbase> INSERT INTO h3 PARTITION(c, d) SELECT a, b, c, d FROM h4;
Query OK
gbase> SELECT * FROM h3 WHERE d > 0;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 1 | 100 | 100 |
| 3 | 3 | 200 | 100 |
| 2 | 2 | 200 | 200 |
| 4 | 4 | 300 | 300 |
+------+------+------+------+
4 rows in set
-- 动态分区也可混合静态分区使用。
gbase> TRUNCATE TABLE h3;
Query OK
-- 下面这条插入语句,分区列c 使用静态分区(值为100),
-- 分区列d 使用动态分区(值为VALUES 中的200 和300)
gbase> INSERT INTO h3 PARTITION(c=100, d) values(1, 1, 200),(2, 2, 300);
Query OK, 1 row affected
gbase> SELECT * FROM h3 WHERE c > 0;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 1 | 100 | 200 |
| 2 | 2 | 100 | 300 |
+------+------+------+------+
2 rows in set
-- 注意,不能出现父分区为动态,而子分区为静态的情况。
gbase> INSERT INTO h3 PARTITION(c, d=1000) values(1, 1, 200),(2, 2, 300);
ERROR ... Dynamic partition cannot be the parent of a static partition '1000'
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 759