腾讯云数据仓库 TCHouse-X 数据分区与分桶

建表时，您可以通过设置合理的分区（Partition）和分桶（Bucket），实现数据均匀分布和查询性能提升。数据均匀分布是指数据按照一定规则划分为子集，并且均衡地分布在不同节点上。查询时能够有效裁剪数据扫描量，最大限度地利用集群的并发性能，从而提升查询性能。
StarRocks 支持通过设置分区 + 分桶的方式来实现数据分布。
第一层为分区：在一张表中，可以进行分区，支持的分区方式有表达式分区、Range 分区和 List 分区，或者不分区（即全表只有一个分区）。
第二层为分桶：在一个分区中，必须进行分桶。支持的分桶方式有哈希分桶和随机分桶。
分区（Partition）
分区用于将数据划分成不同的区间。分区的主要作用是将一张表按照分区键拆分成不同的管理单元，针对每一个管理单元选择相应的存储策略，比如分桶数、冷热策略、存储介质、副本数等。
表达式分区
表达式分区，原称自动创建分区，更加灵活易用，适用于大多数场景，比如按照连续日期范围或者枚举值来查询和管理数据。
您仅需要在建表时使用分区表达式（时间函数表达式或列表达式），即可实现导入数据时自动创建分区，不需要预先创建出分区或者配置动态分区属性。
Range 分区
Range 分区适用于简单且具有连续的数据，如时间序列数据活连续的数值数据。您需要显示定义数据分区列，以及分区和分区列值范围的映射关系。数据导入时 StarRocks 会根据数据分区列值所属范围，将数据分配到相应的分区中。以下展示示例，具体信息可参考文档 Range 分区。
手动创建示例
-- 分区列为日期类型
CREATE TABLE site_access(
    event_day DATE,
    site_id INT,
    city_code VARCHAR(100),
    user_name VARCHAR(32),
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day)(
    PARTITION p1 VALUES LESS THAN ("2020-01-31"),
    PARTITION p2 VALUES LESS THAN ("2020-02-29"),
    PARTITION p3 VALUES LESS THAN ("2020-03-31")
)
DISTRIBUTED BY HASH(site_id);
﻿
-- 分区列为整数类型
CREATE TABLE site_access(
    datekey INT,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    PARTITION p1 VALUES LESS THAN ("20200131"),
    PARTITION p2 VALUES LESS THAN ("20200229"),
    PARTITION p3 VALUES LESS THAN ("20200331")
)
DISTRIBUTED BY HASH(site_id);
﻿
--三个特定时间函数作为分区列
-- 精确到秒的 10 位时间戳，例如：1703832553
CREATE TABLE site_access(
    event_time bigint,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT SUM DEFAULT '0'
  )
AGGREGATE KEY(event_time, site_id, city_code, user_name)
PARTITION BY RANGE(from_unixtime(event_time)) (
    PARTITION p1 VALUES LESS THAN ("2021-01-01"),
    PARTITION p2 VALUES LESS THAN ("2021-01-02"),
    PARTITION p3 VALUES LESS THAN ("2021-01-03")
)
DISTRIBUTED BY HASH(site_id)
;
-- 分区列值为字符串
CREATE TABLE site_access (
     event_time  varchar(100),
     site_id INT,
     city_code SMALLINT,
     user_name VARCHAR(32),
     pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_time, site_id, city_code, user_name)
PARTITION BY RANGE(str2date(event_time, '%Y-%m-%d'))(
    PARTITION p1 VALUES LESS THAN ("2021-01-01"),
    PARTITION p2 VALUES LESS THAN ("2021-01-02"),
    PARTITION p3 VALUES LESS THAN ("2021-01-03")
)
DISTRIBUTED BY HASH(site_id);
批量创建示例
-- 批量分区的开始日期为 2021-01-01 和结束日期为 2021-01-04，增量值为一天
CREATE TABLE site_access (
    datekey DATE,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("2021-01-01") END ("2021-01-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(site_id);
﻿
-- 针对不同的日期分区区间创建
CREATE TABLE site_access (
    datekey DATE,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("2019-01-01") END ("2021-01-01") EVERY (INTERVAL 1 YEAR),
    START ("2021-01-01") END ("2021-05-01") EVERY (INTERVAL 1 MONTH),
    START ("2021-05-01") END ("2021-05-04") EVERY (INTERVAL 1 DAY)
)
DISTRIBUTED BY HASH(site_id);
﻿
-- 批量分区的开始值为 1 和结束值为 5，分区增量值为 1
CREATE TABLE site_access (
    datekey INT,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("1") END ("5") EVERY (1)
)
DISTRIBUTED BY HASH(site_id);
﻿
-- 针对不同的数值分区区间创建
CREATE TABLE site_access (
    datekey INT,
    site_id INT,
    city_code SMALLINT,
    user_name VARCHAR(32),
    pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(datekey, site_id, city_code, user_name)
PARTITION BY RANGE (datekey) (
    START ("1") END ("10") EVERY (1),
    START ("10") END ("100") EVERY (10)
)
DISTRIBUTED BY HASH(site_id);
List 分区
List 分区适用于按照枚举值来查询和管理数据。尤其适用于一个分区中需要包含各分区列的多个值。StarRocks 会按照您显式定义的枚举值列表与分区的映射关系将数据分配到相应的分区中。以下展示示例，具体信息可参考文档 List 分区。
-- 假设经常按照州或城市查询机房收费明细，则建表时可以指定分区列为城市 city ，并且指定每个分区所包含城市同属一个州，这样可以加速查询特定州或城市的数据，并且方便按照特定州或城市进行数据管理。
CREATE TABLE t_recharge_detail2 (
    id bigint,
    user_id bigint,
    recharge_money decimal(32,2), 
    city varchar(20) not null,
    dt varchar(20) not null
)
DUPLICATE KEY(id)
PARTITION BY LIST (city) (
   PARTITION pCalifornia VALUES IN ("Los Angeles","San Francisco","San Diego"), -- 这些城市同属一个州
   PARTITION pTexas VALUES IN ("Houston","Dallas","Austin")
)
DISTRIBUTED BY HASH(`id`);
管理分区
对于 Range 分区和 List 分区，您可以手动新增分区，用于存储新的数据，而表达式分区可以实现导入新数据时自动创建分区，您无需手动新增分区。 新增分区的默认分桶数量和原分区相同。您也可以根据新分区的数据规模调整分桶数量。
ALTER TABLE site_access
ADD PARTITION p4 VALUES LESS THAN ("2020-04-30")
DISTRIBUTED BY HASH(site_id);
执行如下语句，删除 site_access 表中分区 p1 及数据。分区中的数据不会立即删除，会在 Trash 中保留一段时间（默认为一天）。如果误删分区，可以通过 RECOVER 命令恢复分区及数据。
ALTER TABLE site_access
DROP PARTITION p1;
执行如下语句，恢复 site_access 表中分区 p1 及数据。
RECOVER PARTITION p1 FROM site_access;
执行如下语句，查看 site_access 表中分区情况。
SHOW PARTITIONS FROM site_access;
分桶（Bucket）
一个分区按分桶方式被分成了多个桶 bucket，每个桶的数据称之为一个 tablet。
分桶方式：StarRocks 支持随机分桶和哈希分桶。
随机分桶，建表和新增分区时无需设置分桶键。在同一分区内，数据随机分布到不同的分桶中。
哈希分桶，建表和新增分区时需要指定分桶键。在同一分区内，数据按照分桶键划分分桶后，所有分桶键的值相同的行会唯一分配到对应的一个分桶。
分桶数量：默认由 StarRocks 自动设置分桶数量。同时也支持您手动设置分桶数量。
随机分桶
对每个分区的数据，StarRocks 将数据随机地分布在所有分桶中，适用于数据量不大，对查询性能要求不高的场景。如果您不设置分桶方式，则默认由 StarRocks 使用随机分桶，并且自动设置分桶数量。
注意：
随机分桶有以下使用限制：
仅支持明细表。
不支持指定 Colocation Group。
不支持 Spark Load。
如下建表示例中，没有使用 DISTRIBUTED BY xxx 语句，即表示默认由 StarRocks 使用随机分桶，并且由 StarRocks 自动设置分桶数量。
CREATE TABLE site_access1(
    event_day DATE,
    site_id INT DEFAULT '10', 
    pv BIGINT DEFAULT '0' ,
    city_code VARCHAR(100),
    user_name VARCHAR(32) DEFAULT ''
)
DUPLICATE KEY(event_day,site_id,pv);
﻿
-- 也可以设置分桶数量
CREATE TABLE site_access2(
    event_day DATE,
    site_id INT DEFAULT '10', 
    pv BIGINT DEFAULT '0' ,
    city_code VARCHAR(100),
    user_name VARCHAR(32) DEFAULT ''
)
DUPLICATE KEY(event_day,site_id,pv)
DISTRIBUTED BY RANDOM BUCKETS 8; -- 手动设置分桶数量为 8
哈希分桶
对每个分区的数据，StarRocks 会根据分桶键和分桶数量进行哈希分桶。在哈希分桶中，使用特定的列值作为输入，通过哈希函数计算出一个哈希值，然后将数据根据该哈希值分配到相应的桶中。
注意：
建表时，如果使用哈希分桶，则必须指定分桶键。
组成分桶键的列仅支持整型、DECIMAL、DATE/DATETIME、CHAR/VARCHAR/STRING 数据类型。
建表后支持通过 ALTER TABLE 修改分桶键。
如下示例中，site_access 表采用 site_id 作为分桶键，其原因在于 site_id 为高基数列。此外，针对 site_access 表的查询请求，基本上都以站点作为查询过滤条件，采用 site_id 作为分桶键，还可以在查询时裁剪掉大量无关分桶。如果 site_id 分布十分不均，需调整分桶字段，将数据划分更加均匀，避免性能问题。
CREATE TABLE site_access(
    event_day DATE,
    site_id INT DEFAULT '10',
    city_code VARCHAR(100),
    user_name VARCHAR(32) DEFAULT '',
    pv BIGINT SUM DEFAULT '0'
)
AGGREGATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY RANGE(event_day) (
    PARTITION p1 VALUES LESS THAN ("2020-01-31"),
    PARTITION p2 VALUES LESS THAN ("2020-02-29"),
    PARTITION p3 VALUES LESS THAN ("2020-03-31")
)
DISTRIBUTED BY HASH(site_id);
数据分区与分桶

本页目录：

分区（Partition）

表达式分区

Range 分区

手动创建示例

批量创建示例

List 分区

管理分区

分桶（Bucket）

随机分桶

哈希分桶
