跳到主要内容
版本:Latest-3.3

表达式分区(推荐)

自 v3.0 起,StarRocks 支持表达式分区(原称自动创建分区),更加灵活易用,适用于大多数场景,比如按照连续日期范围或者枚举值来查询和管理数据。

您仅需要在建表时设置分区表达式(时间函数表达式或列表达式)。在数据导入时,StarRocks 会根据数据和分区表达式的定义规则自动创建分区,您无需在建表时预先手动/批量创建大量分区,或者配置动态分区属性。

时间函数表达式分区

如果您经常按照连续日期范围来查询和管理数据,则只需要在时间函数分区表达式中,指定一个日期类型(DATE 或者 DATETIME )的分区列,以及指定分区粒度(年、月、日或小时)。StarRocks 会根据导入的数据和分区表达式,自动创建分区并且设置分区的起止时间。

不过在一些特殊场景下,比如历史数据按月划分分区、最近数据按天划分分区,则需要采用 Range 分区创建分区。

语法

PARTITION BY expression
...
[ PROPERTIES( 'partition_live_number' = 'xxx' ) ]

expression ::=
{ date_trunc ( <time_unit> , <partition_column> ) |
time_slice ( <partition_column> , INTERVAL <N> <time_unit> [ , boundary ] ) }

参数解释

参数是否必填说明
expression目前仅支持 date_trunctime_slice 函数。并且如果您使用 time_slice 函数,则可以不传入参数 boundary,因为在该场景中该参数默认且仅支持为 floor,不支持为 ceil
time_unit分区粒度,目前仅支持为 hourdaymonthyear,暂时不支持为 week。如果分区粒度为 hour,则仅支持分区列为 DATETIME 类型,不支持为 DATE 类型。
partition_column分区列。
  • 仅支持为日期类型(DATE 或 DATETIME),不支持为其它类型。如果使用 date_trunc 函数,则分区列支持为 DATE 或 DATETIME 类型。如果使用 time_slice 函数,则分区列仅支持为 DATETIME 类型。分区列的值支持为 NULL
  • 如果分区列是 DATE 类型,则范围支持为 [0000-01-01 ~ 9999-12-31]。如果分区列是 DATETIME 类型,则范围支持为 [0000-01-01 01:01:01 ~ 9999-12-31 23:59:59]。
  • 目前仅支持指定一个分区列,不支持指定多个分区列。
partition_live_number保留最近多少数量的分区。最近是指分区按时间的先后顺序进行排序,以当前时间为基准,然后从后往前数指定个数的分区进行保留,其余(更早的)分区会被删除。后台会定时调度任务来管理分区数量,调度间隔可以通过 FE 动态参数 dynamic_partition_check_interval_seconds 配置,默认为 600 秒,即 10 分钟。假设当前为 2023 年 4 月 4 日,partition_live_number 设置为 2,分区包含 p20230401p20230402p20230403p20230404,则分区 p20230403p20230404 会保留,其他分区会删除。如果导入了脏数据,比如未来时间 4 月 5 日和 6 日的数据,导致分区包含 p20230401p20230402p20230403p20230404p20230405p20230406,则分区 p20230403p20230404p20230405p20230406 会保留,其他分区会删除。

使用说明

  • 在导入的过程中 StarRocks 根据导入数据已经自动创建了一些分区,但是由于某些原因导入作业最终失败,则在当前版本中,已经自动创建的分区并不会由于导入失败而自动删除。
  • StarRocks 自动创建分区数量上限默认为 4096,由 FE 配置参数 max_automatic_partition_number 决定。该参数可以防止您由于误操作而创建大量分区。
  • 分区命名规则与动态分区的命名规则一致。

示例

示例一:假设您经常按天查询数据,则建表时可以使用分区表达式 date_trunc() ,并且设置分区列为 event_day ,分区粒度为 day,实现导入数据时自动按照数据所属日期划分分区。将同一天的数据存储在一个分区中,利用分区裁剪可以显著提高查询效率。

CREATE TABLE site_access1 (
event_day DATETIME NOT NULL,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY date_trunc('day', event_day)
DISTRIBUTED BY HASH(event_day, site_id);

导入如下两行数据,则 StarRocks 会根据导入数据的日期范围自动创建两个分区 p20230226p20230227,范围分别为 [2023-02-26 00:00:00,2023-02-27 00:00:00)、[2023-02-27 00:00:00,2023-02-28 00:00:00)。如果后续导入数据的日期属于这两个范围,则都会自动划分至对应分区。

-- 导入两行数据
INSERT INTO site_access1
VALUES ("2023-02-26 20:12:04",002,"New York","Sam Smith",1),
("2023-02-27 21:06:54",001,"Los Angeles","Taylor Swift",1);

-- 查询分区
mysql > SHOW PARTITIONS FROM site_access1;
+-------------+---------------+----------------+---------------------+--------------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | VisibleVersionHash | State | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | LastConsistencyCheckTime | DataSize | IsInMemory | RowCount |
+-------------+---------------+----------------+---------------------+--------------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
| 17138 | p20230226 | 2 | 2023-07-19 17:53:59 | 0 | NORMAL | event_day | [types: [DATETIME]; keys: [2023-02-26 00:00:00]; ..types: [DATETIME]; keys: [2023-02-27 00:00:00]; ) | event_day, site_id | 6 | 3 | HDD | 9999-12-31 23:59:59 | NULL | 0B | false | 0 |
| 17113 | p20230227 | 2 | 2023-07-19 17:53:59 | 0 | NORMAL | event_day | [types: [DATETIME]; keys: [2023-02-27 00:00:00]; ..types: [DATETIME]; keys: [2023-02-28 00:00:00]; ) | event_day, site_id | 6 | 3 | HDD | 9999-12-31 23:59:59 | NULL | 0B | false | 0 |
+-------------+---------------+----------------+---------------------+--------------------+--------+--------------+------------------------------------------------------------------------------------------------------+--------------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
2 rows in set (0.00 sec)

示例二:如果您希望引入分区生命周期管理,即仅保留最近一段时间的分区,删除历史分区,则可以使用 partition_live_number 设置只保留最近多少数量的分区。

CREATE TABLE site_access2 (
event_day DATETIME NOT NULL,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY date_trunc('month', event_day)
DISTRIBUTED BY HASH(event_day, site_id)
PROPERTIES(
"partition_live_number" = "3" -- 只保留最近 3 个分区
);

示例三:假设您经常按周查询数据,则建表时可以使用分区表达式 time_slice(),设置分区列为 event_day,分区粒度为七天。将一周的数据存储在一个分区中,利用分区裁剪可以显著提高查询效率。

CREATE TABLE site_access3 (
event_day DATETIME NOT NULL,
site_id INT DEFAULT '10',
city_code VARCHAR(100),
user_name VARCHAR(32) DEFAULT '',
pv BIGINT DEFAULT '0'
)
DUPLICATE KEY(event_day, site_id, city_code, user_name)
PARTITION BY time_slice(event_day, INTERVAL 7 day)
DISTRIBUTED BY HASH(event_day, site_id);

列表达式分区(自 v3.1)

如果您经常按照枚举值来查询和管理数据,则您只需要指定表示类型的列为分区列,StarRocks 会根据导入的数据的分区列值,来自动划分并创建分区。

不过在一些特殊场景下,比如表中包含表示城市的列,您经常按照国家和城市来查询和管理数据,希望将同属于一个国家的多个城市的数据存储在一个分区中,则需要使用 List 分区

语法

PARTITION BY expression
...

expression ::=
( <partition_columns> )

partition_columns ::=
<column>, [ <column> [,...] ]

参数解释

参数是否必填参数
partition_columns分区列。
  • 支持为字符串(不支持 BINARY)、日期、整数和布尔值。不支持分区列的值为 NULL
  • 导入后自动创建的一个分区中只能包含各分区列的一个值,如果需要包含各分区列的多值,请使用 List 分区

使用说明

  • 在导入的过程中 StarRocks 根据导入数据已经自动创建了一些分区,但是由于某些原因导入作业最终失败,则在当前版本中,已经自动创建的分区并不会由于导入失败而自动删除。
  • StarRocks 自动创建分区数量上限默认为 4096,由 FE 配置参数 max_automatic_partition_number 决定。该参数可以防止您由于误操作而创建大量分区。
  • 分区命名规则:如果存在多个分区列,则不同分区列的值以下划线(_)连接。例如:存在有两个分区列 dtcity,均为字符串类型,导入一条数据 2022-04-01, beijing,则自动创建的分区名称为 p20220401_beijing

示例

示例一:假设经常按日期范围和特定城市查询机房收费明细,则建表时可以使用分区表达式指定分区列为日期 dt 和城市 city。这样属于相同日期和城市的数据分组到同一个分区中,利用分区裁剪可以显著提高查询效率。

CREATE TABLE t_recharge_detail1 (
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 (dt,city)
DISTRIBUTED BY HASH(`id`);

导入一条数据。

INSERT INTO t_recharge_detail1 
VALUES (1, 1, 1, 'Houston', '2022-04-01');

查看具体分区。返回结果显示,StarRocks 根据导入数据的分区列值自动创建一个分区 p20220401_Houston ,如果后续导入数据的分区列 dtcity 的值是 2022-04-01Houston,则都会被划分至该分区。

说明

分区中只能包含各分区列的一个值,如果需要一个分区中包含各分区列的多值,请使用 List 分区

MySQL > SHOW PARTITIONS from t_recharge_detail1\G
*************************** 1. row ***************************
PartitionId: 16890
PartitionName: p20220401_Houston
VisibleVersion: 2
VisibleVersionTime: 2023-07-19 17:24:53
VisibleVersionHash: 0
State: NORMAL
PartitionKey: dt, city
List: (('2022-04-01', 'Houston'))
DistributionKey: id
Buckets: 6
ReplicationNum: 3
StorageMedium: HDD
CooldownTime: 9999-12-31 23:59:59
LastConsistencyCheckTime: NULL
DataSize: 2.5KB
IsInMemory: false
RowCount: 1
1 row in set (0.00 sec)

管理分区

导入数据至分区

导入数据时,StarRocks 会根据数据和分区表达式定义的分区规则,自动创建分区。

值得注意的是,如果您建表时使用表达式分区,并且需要使用 INSERT OVERWRITE 覆盖写入指定分区的数据,不论该分区是否已经创建,目前您都需要在 PARTITION() 提供明确的分区范围。而不是同 Range 分区或 List 分区一样,只需要在PARTITION (partition_name) 中提供分区名称。

如果建表时您使用时间函数表达式分区,则此时覆盖写入指定分区,您需要提供该分区的起始范围(分区粒度与建表时配置分区粒度一致)。如果该分区不存在,则导入数据时会自动创建该分区。

INSERT OVERWRITE site_access1 PARTITION(event_day='2022-06-08 20:12:04')
SELECT * FROM site_access2 PARTITION(p20220608);

如果建表时您使用列表达式分区,则覆盖写入指定分区时,您需要提供该分区包含的分区列值。如果该分区不存在,则导入数据时会自动创建该分区。

INSERT OVERWRITE t_recharge_detail1 PARTITION(dt='2022-04-02',city='texas')
SELECT * FROM t_recharge_detail2 PARTITION(p20220402_texas);

查看分区

查看自动创建的分区的具体信息时,您需要使用 SHOW PARTITIONS FROM <table_name> 语句。而SHOW CREATE TABLE <table_name> 语句返回的结果中仅包含建表时配置的表达式分区语法。

MySQL > SHOW PARTITIONS FROM t_recharge_detail1;
+-------------+-------------------+----------------+---------------------+--------------------+--------+--------------+-----------------------------+-----------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime | VisibleVersionHash | State | PartitionKey | List | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime | LastConsistencyCheckTime | DataSize | IsInMemory | RowCount |
+-------------+-------------------+----------------+---------------------+--------------------+--------+--------------+-----------------------------+-----------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
| 16890 | p20220401_Houston | 2 | 2023-07-19 17:24:53 | 0 | NORMAL | dt, city | (('2022-04-01', 'Houston')) | id | 6 | 3 | HDD | 9999-12-31 23:59:59 | NULL | 2.5KB | false | 1 |
| 17056 | p20220402_texas | 2 | 2023-07-19 17:27:42 | 0 | NORMAL | dt, city | (('2022-04-02', 'texas')) | id | 6 | 3 | HDD | 9999-12-31 23:59:59 | NULL | 2.5KB | false | 1 |
+-------------+-------------------+----------------+---------------------+--------------------+--------+--------------+-----------------------------+-----------------+---------+----------------+---------------+---------------------+--------------------------+----------+------------+----------+
2 rows in set (0.00 sec)

使用限制

  • 自 v3.1.0 起,StarRocks 存算分离模式支持时间函数表达式分区。并且自 v3.1.1 起 StarRocks 存算分离模式支持列表达式分区
  • 使用 CTAS 建表时暂时不支持表达式分区。
  • 暂时不支持使用 Spark Load 导入数据至表达式分区的表。
  • 使用 ALTER TABLE <table_name> DROP PARTITION <partition_name> 删除列表达式分区时,分区直接被删除并且不能被恢复。
  • 列表达式分区暂时不支持备份与恢复
  • 如果使用表达式分区,则仅支持回滚到 2.5.4 及以后的版本。