CREATE TABLEGROUP

描述

该语句用来创建表组。

表组主要用于创建一组具有相同分布特征的表，具有相同分布特征的表在进行连接操作时，可以在本地进行，避免跨节点的数据请求。

创建表组需要当前用户拥有全局 CREATE（*.*）权限。

语法

CREATE TABLEGROUP tablegroup_name [tablegroup_option];

tablegroup_option:
   SHARDING = 'NONE' | 'PARTITION' | 'ADAPTIVE'

参数解释

示例

• 创建名为 myTableGroup1 的表组，并将非分区表 myt1 和 myt2 加入到表组中。

  obclient> CREATE TABLEGROUP myTableGroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE myt1 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE myt2 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  Query OK, 0 rows affected
  

• 创建名为 tblgroup1 的表组，其 SHARDING 属性为 NONE，并将非分区表 tbl1 、一级分区表 tbl2、二级分区表 tbl3 加入到表组中。

  obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT) TABLEGROUP = tblgroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl3(col1 INT,col2 varchar(50)) TABLEGROUP = tblgroup1
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
     (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
         SUBPARTITION mp1 VALUES in ('02'),
         SUBPARTITION mp2 VALUES in ('03')),
      PARTITION p1 VALUES in (02)
        (SUBPARTITION mp3 VALUES in ('01'),
         SUBPARTITION mp4 VALUES in ('02'),
         SUBPARTITION mp5 VALUES in ('03'))
       );
  Query OK, 0 rows affected
  

• 创建名为 tblgroup2 的表组，其 SHARDING 属性为 PARTITION，并将分区表 tbl1 和分区表 tbl2 加入到表组中，两者的分区方式一致。

  obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup2
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
       );
  Query OK, 0 rows affected
  

• 创建名为 tblgroup3 的表组，其 SHARDING 属性为 PARTITION，非分区表 tbl1 和分区表 tbl2 不能同时加入到该表组中。

  obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col INT) TABLEGROUP = tblgroup3;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup3
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• 创建名为 tblgroup4 的表组，其 SHARDING 属性为 PARTITION，分区表 tbl1 和分区表 tbl2 的分区值不一致，不能同时加入到表组中。

  obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'PARTITION';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup4
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 7),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  ERROR 4179 (HY000): list_part partition value not equal, add table to tablegroup not allowed
  

• 创建名为 tblgroup5 的表组，其 SHARDING 属性为 ADAPTIVE，并将分区表 tbl1 和分区表 tbl2 加入到表组中，两者的分区方式一致。

  obclient> CREATE TABLEGROUP tblgroup5 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup5
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02'),
            SUBPARTITION mp2 VALUES in ('03')),
         PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'),
            SUBPARTITION mp5 VALUES in ('03'))
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup5
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02'),
            SUBPARTITION mp2 VALUES in ('03')),
         PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'),
            SUBPARTITION mp5 VALUES in ('03'))
         );
  Query OK, 0 rows affected
  

• 创建名为 tblgroup6 的表组，其 SHARDING 属性为 ADAPTIVE，非分区表 tbl1 和分区表 tbl2 不能同时加入到表组中。

  obclient> CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col int) TABLEGROUP = tblgroup6;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup6
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02'),
            SUBPARTITION mp2 VALUES in ('03')
         ),
        PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'),
            SUBPARTITION mp5 VALUES in ('03'))
         );
  ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
  

• 创建名为 tblgroup7 的表组，其 SHARDING 属性为 ADAPTIVE，由于分区表 tbl1 和分区表 tbl2 的一级分区的分区类型相同，二级分区分区类型不同（二级分区个数不同），所以不能同时加入到表组中。

  obclient> CREATE TABLEGROUP tblgroup7 SHARDING = 'ADAPTIVE';
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup7
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
          (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02'),
            SUBPARTITION mp2 VALUES in ('03')
            ),
         PARTITION p1 VALUES in (02)
            (SUBPARTITION mp3 VALUES in ('01'),
             SUBPARTITION mp4 VALUES in ('02'),
             SUBPARTITION mp5 VALUES in ('03'))
        );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = tblgroup7
      PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES in (01)
           (SUBPARTITION mp0 VALUES in ('01'),
            SUBPARTITION mp1 VALUES in ('02')),
         PARTITION p1 VALUES in (02)
           (SUBPARTITION mp3 VALUES in ('01'),
            SUBPARTITION mp4 VALUES in ('02'))
         );
  ERROR 4179 (HY000): subpartition num not matched, add table to tablegroup not allowed
  

相关文档

• ALTER TABLEGROUP

• DROP TABLEGROUP

• 管理表组内的表

• 查看表组信息