表达式分区（推荐）
自 v3.0 起，StarRocks 支持表达式分区（之前称为自动分区），该分区方式更加灵活和用户友好。此分区方法适用于大多数场景，例如基于连续时间范围或 ENUM 值查询和管理数据。
您只需在创建表时指定一个简单的分区表达式。在数据加载期间，StarRocks 将根据数据和分区表达式中定义的规则自动创建分区。您不再需要在创建表时手动创建大量分区，也不需要配置动态分区属性。
从 v3.4 开始，表达式分区得到了进一步优化，统一了所有分区策略并支持更复杂的解决方案。在大多数情况下建议使用表达式分区，并且在未来的版本中将取代其他分区策略。
从 v3.5 开始，StarRocks 支持基于时间函数合并表达式分区，以优化存储效率和查询性能。有关详细信息，请参见 合并表达式分区。
基于简单时间函数表达式的分区
如果您经常基于连续时间范围查询和管理数据，则只需指定日期类型（DATE 或 DATETIME）列作为分区列，并在时间函数表达式中指定年、月、日或小时作为分区粒度。StarRocks 将根据加载的数据和分区表达式自动创建分区并设置分区的开始日期和结束日期或日期时间。
但是，在一些特殊场景中，例如将历史数据按月分区，将最近的数据按日分区，您必须使用 范围分区 来创建分区。
语法
PARTITION BY expression
...
[ PROPERTIES( { 'partition_live_number' = 'xxx' | 'partition_retention_condition' = 'expr' } ) ]
expression ::=
{ date_trunc ( <time_unit> , <partition_column> ) |
time_slice ( <partition_column> , INTERVAL <N> <time_unit> [ , boundary ] ) }
参数
expression
必需：是
描述： 使用 date_trunc 或 time_slice 函数的简单时间函数表达式。如果您使用函数 time_slice，则无需传递 boundary 参数。这是因为在此场景中，此参数的默认和有效值为 floor，并且该值不能为 ceil。
time_unit
必需：是
描述：分区粒度，可以是 hour、day、month 或 year。不支持 week 分区粒度。如果分区粒度为 hour，则分区列必须为 DATETIME 数据类型，不能为 DATE 数据类型。
partition_column
必需：是
描述：分区列的名称。
- 分区列只能是 DATE 或 DATETIME 数据类型。分区列允许
NULL值。 - 如果使用
date_trunc函数，则分区列可以是 DATE 或 DATETIME 数据类型。如果使用time_slice函数，则分区列必须是 DATETIME 数据类型。 - 如果分区列是 DATE 数据类型，则支持的范围是 [0000-01-01 ~ 9999-12-31]。如果分区列是 DATETIME 数据类型，则支持的范围是 [0000-01-01 01:01:01 ~ 9999-12-31 23:59:59]。
- 目前，您只能指定一个分区列；不支持多个分区列。
partition_live_number
必需：否
描述：要保留的最近分区的数量。分区按时间顺序排序，以当前日期为基准；删除早于当前日期减去 partition_live_number 的分区。StarRocks 调度任务来管理分区的数量，调度间隔可以通过 FE 动态参数 dynamic_partition_check_interval_seconds 进行配置，默认为 600 秒（10 分钟）。假设当前日期为 2023 年 4 月 4 日，partition_live_number 设置为 2，分区包括 p20230401、p20230402、p20230403、p20230404。则保留分区 p20230403 和 p20230404，并删除其他分区。如果加载脏数据，例如来自未来日期 4 月 5 日和 4 月 6 日的数据，分区包括 p20230401、p20230402、p20230403、p20230404 和 p20230405 以及 p20230406。然后保留分区 p20230403、p20230404、p20230405 和 p20230406，并删除其他分区。
partition_retention_condition
从 v3.5.0 开始，StarRocks 原生表支持通用分区表达式 TTL。
partition_retention_condition：声明要动态保留的分区的表达式。不符合表达式条件的分区将被定期删除。例如：'partition_retention_condition' = 'dt >= CURRENT_DATE() - INTERVAL 3 MONTH'。
- 表达式只能包含分区列和常量。不支持非分区列。
- 通用分区表达式对 List 分区和 Range 分区的应用方式不同
- 对于具有 List 分区的表，StarRocks 支持删除通过通用分区表达式过滤的分区。
- 对于具有 Range 分区的表，StarRocks 只能使用 FE 的分区裁剪功能来过滤和删除分区。与分区裁剪不支持的谓词对应的分区无法被过滤和删除。
使用说明
- 在数据加载期间，StarRocks 会根据加载的数据自动创建一些分区，但是如果加载作业由于某种原因失败，则 StarRocks 自动创建的分区无法自动删除。
- StarRocks 将一次加载自动创建的最大分区数默认设置为 4096，可以通过 FE 参数
auto_partition_max_creation_number_per_load进行配置。此参数可以防止您意外创建过多分区。 - 分区的命名规则与动态分区的命名规则一致。
示例
示例 1：假设您经常按天查询数据。您可以使用分区表达式 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 将自动创建两个分区，p20230226 和 p20230227，范围分别为 [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 two data rows
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);
-- view partitions
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)
示例 2：如果您想实现分区生命周期管理，即仅保留一定数量的最近分区并删除历史分区，则可以使用 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" -- only retains the most recent three partitions
);
示例 3：假设您经常按周查询数据。您可以使用分区表达式 time_slice()，并在创建表时将分区列设置为 event_day，将分区粒度设置为七天。一周的数据存储在一个分区中，并且可以使用分区裁剪来显着提高查询效率。
CREATE TABLE site_access(
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 将根据加载的数据的分区列值自动创建分区。
但是，在某些特殊场景中，例如当表包含列 city 时，您经常根据国家和城市查询和管理数据。您必须使用 List 分区 将同一国家/地区的多个城市的数据存储在一个分区中。
语法
PARTITION BY expression
...
expression ::=
partition_columns
partition_columns ::=
<column>, [ <column> [,...] ]
参数
partition_columns
必需：是
描述：分区列的名称。
- 分区列值可以是字符串（不支持 BINARY）、日期或日期时间、整数和布尔值。分区列允许
NULL值。 - 每个分区只能包含分区列中具有相同值的数据。要在分区中包含分区列中具有不同值的数据，请参见 List 分区。
从 v3.4 开始，您可以省略用于包装分区列的括号。例如，您可以将 PARTITION BY (dt,city) 替换为 PARTITION BY dt,city。
使用说明
- 在数据加载期间，StarRocks 会根据加载的数据自动创建一些分区，但是如果加载作业由于某种原因失败，则 StarRocks 自动创建的分区无法自动删除。
- StarRocks 将一次加载自动创建的最大分区数默认设置为 4096，可以通过 FE 参数
auto_partition_max_creation_number_per_load进行配置。此参数可以防止您意外创建过多分区。 - 分区的命名规则：如果指定了多个分区列，则不同的分区列的值以连字符
_在分区名称中连接起来，格式为p<分区列 1 中的值>_<分区列 2 中的值>_...。例如，如果指定了两个列dt和province作为分区列，这两个列都是字符串类型，并且加载了值2022-04-01和beijing的数据行，则自动创建的相应分区命名为p20220401_beijing。
示例
示例 1：假设您经常根据时间范围和特定城市查询数据中心计费的详细信息。在创建表时，您可以使用分区表达式将第一个分区列指定为 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_Houston1。在后续加载期间，分区列 dt 和 city 中具有值 2022-04-01 和 Houston 的数据将存储在此分区中。
每个分区只能包含分区列的指定值的数据。要在分区中为分区列指定多个值，请参见 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)
基于复杂时间函数表达式的分区（自 v3.4 起）
从 v3.4.0 开始，表达式分区支持返回 DATE 或 DATETIME 类型的任何表达式，以适应更复杂的分区场景。有关支持的时间函数，请参见 附录 - 支持的时间函数。
例如，您可以定义一个 Unix 时间戳列，并在分区表达式中直接使用 from_unixtime() 函数处理该列来定义分区键，而不是定义一个带有该函数的生成的 DATE 或 DATETIME 列。有关用法的更多信息，请参见 示例。
从 v3.4.4 开始，基于大多数 DATETIME 相关函数的分区支持分区裁剪。
示例
示例 1：假设您为每个数据行分配一个 Unix 时间戳，并经常按天查询数据。您可以在表达式中使用时间戳列和 from_unixtime() 函数来定义时间戳作为分区列，并在创建表时将分区粒度设置为一天。每天的数据存储在一个分区中，并且可以使用分区裁剪来提高查询效率。
CREATE TABLE orders (
ts BIGINT NOT NULL,
id BIGINT NOT NULL,
city STRING NOT NULL
)
PARTITION BY from_unixtime(ts,'%Y%m%d');
示例 2：假设您为每个数据行分配一个 INT 类型的时间戳，并按月存储数据。您可以在表达式中使用时间戳列和 cast() 和 str_to_date() 函数将时间戳转换为 DATE 类型，将其设置为分区列，并在创建表时使用 date_trunc() 将分区粒度设置为一个月。每个月的数据存储在一个分区中，并且可以使用分区裁剪来提高查询效率。
CREATE TABLE orders_new (
ts INT NOT NULL,
id BIGINT NOT NULL,
city STRING NOT NULL
)
PARTITION BY date_trunc('month', str_to_date(CAST(ts as STRING),'%Y%m%d'));
使用说明
分区裁剪适用于基于复杂时间函数表达式的分区的情况
- 如果分区子句是
PARTITION BY from_unixtime(ts)，则可以使用格式为ts>1727224687的过滤器将查询裁剪到相应的分区。 - 如果分区子句是
PARTITION BY str2date(CAST(ts AS string),'%Y%m')，则可以使用格式为ts = "20240506"的过滤器进行裁剪。 - 以上情况也适用于 基于混合表达式的分区。
基于混合表达式的分区（自 v3.4 起）
从 v3.4.0 开始，表达式分区支持多个分区列，其中一个分区列是时间函数表达式。
示例
示例 1：假设您为每个数据行分配一个 Unix 时间戳，并经常按天和特定城市查询数据。您可以在创建表时使用时间戳列（带有 from_unixtime() 函数）和城市列作为分区列。每个城市中每天的数据存储在一个分区中，并且可以使用分区裁剪来提高查询效率。
CREATE TABLE orders (
ts BIGINT NOT NULL,
id BIGINT NOT NULL,
city STRING NOT NULL
)
PARTITION BY from_unixtime(ts,'%Y%m%d'), city;
管理分区
将数据加载到分区中
在数据加载期间，StarRocks 将根据加载的数据和分区表达式定义的分区规则自动创建分区。
请注意，如果在创建表时使用了表达式分区，并且需要使用 INSERT OVERWRITE 覆盖特定分区中的数据，则无论该分区是否已创建，当前都需要在 PARTITION() 中显式提供分区范围。这与 范围分区 或 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)
合并表达式分区
在数据管理中，基于不同时间粒度进行分区对于优化查询和存储至关重要。为了提高存储效率和查询性能，StarRocks 支持将多个较细时间粒度的表达式分区合并为一个较粗时间粒度的分区，例如，将按天分区合并为按月分区。通过合并满足指定条件（时间范围）的分区，StarRocks 允许您在不同的时间粒度上对数据进行分区。
语法
ALTER TABLE [<db_name>.]<table_name>
PARTITION BY <time_expr>
BETWEEN <start_time> AND <end_time>
参数
PARTITION BY <time_expr>
必需：是
描述：指定分区策略的新的时间粒度，例如，PARTITION BY date_trunc('month', dt)。
WHERE <time_range_column> BETWEEN <start_time> AND <end_time>
必需：是
描述：指定要合并的分区的时间范围。此范围内的分区将根据 PARTITION BY 子句中定义的规则进行合并。
示例
合并表 site_access1 中的分区，并将分区时间粒度从天更改为月。要合并的分区的时间范围是从 2024-01-01 到 2024-03-31。
ALTER TABLE site_access1 PARTITION BY date_trunc('month', event_day)
BETWEEN '2024-01-01' AND '2024-03-31';
合并后
- 天级别分区
2024-01-01到2024-01-31合并为2024-01的月级别分区。 - 天级别分区
2024-02-01到2024-02-29合并为2024-02的月级别分区。 - 天级别分区
2024-03-01到2024-03-31合并为2024-03的月级别分区。
使用说明
- 仅支持基于时间函数的表达式分区进行合并。
- 不支持合并具有多个分区列的分区。
- 不支持合并和 Schema Change/DML 操作的并行执行。
限制
- 自 v3.1.0 起，StarRocks 的共享数据模式支持 时间函数表达式。自 v3.1.1 起，StarRocks 的共享数据模式进一步支持 列表达式。
- 当前，不支持使用 CTAS 创建配置了表达式分区的表。
- 当前，不支持使用 Spark Load 将数据加载到使用表达式分区的表中。
- 当使用
ALTER TABLE <table_name> DROP PARTITION <partition_name>语句删除使用列表达式创建的分区时，分区中的数据将被直接删除且无法恢复。 - 从 v3.4.0、v3.3.8、v3.2.13 和 v3.1.16 开始，StarRocks 支持 备份和还原 使用表达式分区策略创建的表。
附录
支持的时间函数
表达式分区支持以下函数
时间函数:
- timediff
- datediff
- to_days
- years_add/sub
- quarters_add/sub
- months_add/sub
- weeks_add/sub
- date_add/sub
- days_add/sub
- hours_add/sub
- minutes_add/sub
- seconds_add/sub
- milliseconds_add/sub
- date_trunc
- date_format(YmdHiSf/YmdHisf)
- str2date(YmdHiSf/YmdHisf)
- str_to_date(YmdHiSf/YmdHisf)
- to_iso8601
- to_date
- unix_timestamp
- from_unixtime(YmdHiSf/YmdHisf)
- time_slice
其他函数:
- add
- subtract
- cast
- 支持多个时间函数的组合使用。
- 系统默认时区用于上面列出的所有时间函数。
- 时间函数的值格式
YmdHiSf必须以最粗的时间粒度%Y开头。不允许以较细的时间粒度开头的格式，例如%m-%d。
示例
PARTITION BY from_unixtime(cast(str as INT) + 3600, '%Y-%m-%d')