CREATE TABLEGROUP

Purpose

This statement is used to create a table group.

A table group is mainly used to create a group of tables with the same distribution characteristics. When tables with the same distribution characteristics are joined, the join can be performed locally, avoiding data requests across nodes.

To create a table group, the current user must have the global CREATE (*.*) privilege.

Syntax

CREATE TABLEGROUP [ IF NOT EXISTS ] tablegroup_name [tablegroup_option_list];

tablegroup_option_list:
    tablegroup_option [, tablegroup_option ...]

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

Parameters

Examples

• Create a table group named myTableGroup1 and add the non-partitioned tables myt1 and myt2 to it.

  obclient> CREATE TABLEGROUP myTableGroup1;
  obclient> CREATE TABLE myt1 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  obclient> CREATE TABLE myt2 (c1 INT, c2 INT ) TABLEGROUP = myTableGroup1;
  

• Create a table group named order_tg_none with the SHARDING attribute set to NONE, and add the non-partitioned table user_table, the primary partitioned table order_table, and the secondary partitioned table order_item_table to it.

  obclient> CREATE TABLEGROUP order_tg_none SHARDING = 'NONE';
  obclient> CREATE TABLE user_table (col INT) TABLEGROUP = order_tg_none;
  obclient> CREATE TABLE order_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = order_tg_none
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  obclient> CREATE TABLE order_item_table(col1 INT,col2 varchar(50)) TABLEGROUP = order_tg_none
      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'))
       );
  

• Create a table group named product_tg_partition with the SHARDING attribute set to PARTITION, and add the partitioned tables product_table and category_table to it, ensuring their partitioning methods are consistent.

  obclient> CREATE TABLEGROUP product_tg_partition SHARDING = 'PARTITION';
  obclient> CREATE TABLE product_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
  obclient> CREATE TABLE category_table (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
       );
  

• Create a table group named mixed_tg_partition with the SHARDING attribute set to PARTITION, and add the non-partitioned table user_table_np and the partitioned table order_table_p to it. However, they cannot be added to the same table group.

  obclient> CREATE TABLEGROUP mixed_tg_partition SHARDING = 'PARTITION';
  obclient> CREATE TABLE user_table_np (col INT) TABLEGROUP = mixed_tg_partition;
  obclient> CREATE TABLE order_table_p (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_partition
      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
  

• Create a table group named product_tg_partition2 with the SHARDING attribute set to PARTITION, and add the partitioned tables product_table2 and category_table2 to it. However, their partition values are inconsistent, so they cannot be added to the same table group.

  obclient> CREATE TABLEGROUP product_tg_partition2 SHARDING = 'PARTITION';
  obclient> CREATE TABLE product_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2
      PARTITION BY LIST(col1)
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
         );
  obclient> CREATE TABLE category_table2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = product_tg_partition2
      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
  

• Create a table group named order_tg_adaptive with the SHARDING attribute set to ADAPTIVE, and add the partitioned tables order_detail_table and order_item_detail_table to it, ensuring their partitioning methods are consistent.

  obclient> CREATE TABLEGROUP order_tg_adaptive SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE order_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive
      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'))
        );
  obclient> CREATE TABLE order_item_detail_table(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive
      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'))
         );
  

• Create a table group named mixed_tg_adaptive with the SHARDING attribute set to ADAPTIVE, and add the non-partitioned table user_table_np2 and the partitioned table order_detail_table2 to it. However, they cannot be added to the same table group.

  obclient> CREATE TABLEGROUP mixed_tg_adaptive SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE user_table_np2 (col int) TABLEGROUP = mixed_tg_adaptive;
  obclient> CREATE TABLE order_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = mixed_tg_adaptive
      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
  

• Create a table group named order_tg_adaptive2 with the SHARDING attribute set to ADAPTIVE, and add the partitioned tables order_detail_table3 and order_item_detail_table2 to it. However, their primary partitions have the same partitioning type, but their secondary partitions have different partitioning types (different number of secondary partitions), so they cannot be added to the same table group.

  obclient> CREATE TABLEGROUP order_tg_adaptive2 SHARDING = 'ADAPTIVE';
  obclient> CREATE TABLE order_detail_table3(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2
      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'))
        );
  obclient> CREATE TABLE order_item_detail_table2(col1 INT,col2 VARCHAR(50)) TABLEGROUP = order_tg_adaptive2
      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
  

References

• ALTER TABLEGROUP

• DROP TABLEGROUP

• Manage tables in a table group

• View information about a table group