Doris分区

一时失言乱红尘 2022-09-09 13:50 220阅读 0赞

### 1、创建分区表 ###

**（1）默认分区**

当不使用 Partition 建表时，系统会自动生成一个和表名同名的，全值范围的 Partition。该 Partition 对用户不可见，并且不可删改。

CREATE TABLE table1 (
         siteid INT DEFAULT '10',
         citycode SMALLINT,
         username VARCHAR(32) DEFAULT '',     
         pv BIGINT SUM DEFAULT '0' 
    ) 
    AGGREGATE KEY(siteid, citycode, username) 
    DISTRIBUTED BY HASH(siteid) BUCKETS 10 
    PROPERTIES("replication_num" = "1")

（2）指定多分区  
下面建表语句指定了三个分区，Partition 支持通过 VALUES LESS THAN (…) 仅指定上界，系统会将前一个分区的上界作为该分区的下界。

CREATE TABLE table2 (
         event_day DATE,     
         siteid INT DEFAULT '10',     
         citycode SMALLINT,     
         username VARCHAR(32) DEFAULT '',     
         pv BIGINT SUM DEFAULT '0' 
    ) 
    AGGREGATE KEY(event_day, siteid, citycode, username) 
    PARTITION BY RANGE(event_day) (     
    	PARTITION p201706 VALUES LESS THAN ('2017-07-01'),     
    	PARTITION p201707 VALUES LESS THAN ('2017-08-01'),     
    	PARTITION p201708 VALUES LESS THAN ('2017-09-01') 
    ) 
    DISTRIBUTED BY HASH(siteid) BUCKETS 10 
    PROPERTIES("replication_num" = "2")

### 3、查看分区信息 ###

MySQL [test]> show partitions from test.table1 \G
    *************************** 1. row ***************************
                 PartitionId: 10006
               PartitionName: table1
              VisibleVersion: 2
          VisibleVersionTime: 2021-08-30 13:46:04
          VisibleVersionHash: 6956565961900736218
                       State: NORMAL
                PartitionKey: 
                       Range: 
             DistributionKey: siteid
                     Buckets: 10
              ReplicationNum: 2
               StorageMedium: HDD
                CooldownTime: 9999-12-31 23:59:59
    LastConsistencyCheckTime: 2021-08-30 23:00:54
                    DataSize: 6.736 KB
                  IsInMemory: false
    1 row in set (0.01 sec)
    
    MySQL [test]> show partitions from test.table2 \G
    *************************** 1. row ***************************
                 PartitionId: 10152
               PartitionName: p201706
              VisibleVersion: 1
          VisibleVersionTime: 2021-08-30 15:00:38
          VisibleVersionHash: 0
                       State: NORMAL
                PartitionKey: event_day
                       Range: [types: [DATE]; keys: [0000-01-01]; ..types: [DATE]; keys: [2017-07-01]; )
             DistributionKey: siteid
                     Buckets: 10
              ReplicationNum: 2
               StorageMedium: HDD
                CooldownTime: 9999-12-31 23:59:59
    LastConsistencyCheckTime: NULL
                    DataSize: .000 
                  IsInMemory: false
    *************************** 2. row ***************************
                 PartitionId: 10153
               PartitionName: p201707
              VisibleVersion: 2
          VisibleVersionTime: 2021-08-30 15:07:45
          VisibleVersionHash: 8754137926813451799
                       State: NORMAL
                PartitionKey: event_day
                       Range: [types: [DATE]; keys: [2017-07-01]; ..types: [DATE]; keys: [2017-08-01]; )
             DistributionKey: siteid
                     Buckets: 10
              ReplicationNum: 2
               StorageMedium: HDD
                CooldownTime: 9999-12-31 23:59:59
    LastConsistencyCheckTime: 2021-08-30 23:00:54
                    DataSize: 8.633 KB
                  IsInMemory: false
    *************************** 3. row ***************************
                 PartitionId: 10154
               PartitionName: p201708
              VisibleVersion: 1
          VisibleVersionTime: 2021-08-30 15:00:38
          VisibleVersionHash: 0
                       State: NORMAL
                PartitionKey: event_day
                       Range: [types: [DATE]; keys: [2017-08-01]; ..types: [DATE]; keys: [2017-09-01]; )
             DistributionKey: siteid
                     Buckets: 10
              ReplicationNum: 2
               StorageMedium: HDD
                CooldownTime: 9999-12-31 23:59:59
    LastConsistencyCheckTime: NULL
                    DataSize: .000 
                  IsInMemory: false
    3 rows in set (0.00 sec)
    
    MySQL [test]>