大网数据广告活动
数据库部署与 SQL 参考

Apache Doris 中文参考:SHOW PARTITIONS

· 52CE 我爱测

需要注意的是: 1. 目前 子句等操作符。对字符型的 , 只支持 、 、 操作符。对其余的只支持 、 、 、 、 、 操作符。 2. 子句使用上面的操作符时,列名需要在左侧。 3. 子句可以包含 。

描述

该语句用于展示分区信息。支持 Internal catalog 和 Hive Catalog。

对于 Hive Catalog:

支持返回所有分区,包括多级分区

语法

SHOW [ TEMPORARY ] PARTITIONS
    FROM [ <db_name>. ] <table_name>
    [ <where_clause> ]
    [ ORDER BY <order_by_key> ]
    [ LIMIT <limit_rows> ];

必选参数

1. <table_name>

需要指定查看分区信息的表名称。

可选参数

1. TEMPORARY

是否需要列出临时分区

2. <db_name>

需要指定查看分区信息的数据库名称。

3. <where_clause>

过滤条件,支持 PartitionId,PartitionName,State,Buckets,ReplicationNum,LastConsistencyCheckTime 等列的过滤。

需要注意的是:

  1. 目前 where子句等操作符。对字符型的 PartitionName, State 只支持=、!=、like 操作符。对其余的只支持 =、!=、>、<、>=、<= 操作符。
  2. where子句使用上面的操作符时,列名需要在左侧。
  3. where子句可以包含AND。

4. <order_by_key>

排序条件,支持 PartitionId,PartitionName,State,Buckets,ReplicationNum,LastConsistencyCheckTime 等列的排序。

5. <limit_rows>

返回的最大行数。

返回值

列名 类型 说明
PartitionId bigint 分区 ID
PartitionName varchar 分区名称
VisibleVersion int 该分区下,最大的 tablet 的 VisibleVersion
VisibleVersionTime datetime 该分区下,最近一次的 VisibleVersionde 时间
State varchar 分区状态
PartitionKey datetime 分区键
Range datetime 该分区的分区区间
DistributionKey varchar 该分区的分布键
Buckets int 该分区的分桶数
ReplicationNum int 该分区的副本书
StorageMedium varchar 该分区的存储介质
CooldownTime datetime 该分区的降冷时间,如果没有冷热分离,该字段的值为 [9999-12-31 23:59:59] ,即一直为热数据。
RemoteStoragePolicy varchar 该分区的远端存储策略
LastConsistencyCheckTime datetime 该分区的上一次的分区一致性检查的时间
DataSize int 该分区下的数据大小
IsInMemory boolean 是否为内存分区,默认为 false
ReplicaAllocation varchar 该分区的副本分布策略
IsMutable boolean 该分区是否为可变分区,默认为 true
SyncWithBaseTables boolean 该分区是否和基表的数据同步保持一致
UnsyncTables varchar 该分区是否是非同步表的分区

权限控制

需要具备要查看的表的 SHOW 权限。

示例

  1. 展示指定 db 下指定表的所有非临时分区信息
SHOW PARTITIONS FROM t_agg;
+-------------+---------------+----------------+---------------------+--------+--------------+-------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime  | State  | PartitionKey | Range | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize  | IsInMemory | ReplicaAllocation       | IsMutable | SyncWithBaseTables | UnsyncTables |
+-------------+---------------+----------------+---------------------+--------+--------------+-------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
| 170307      | t_agg         | 4              | 2024-11-05 16:13:40 | NORMAL |              |       | k1              | 1       | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 806.000 B | false      | tag.location.default: 1 | true      | true               | NULL         |
+-------------+---------------+----------------+---------------------+--------+--------------+-------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
  1. 展示指定 db 下指定表的所有临时分区信息
SHOW TEMPORARY PARTITIONS FROM t_temp;
+-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime  | State  | PartitionKey | Range                                                                                                    | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation       | IsMutable | SyncWithBaseTables | UnsyncTables |
+-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+
| 828863      | tp2020        | 1              | 2025-01-22 16:19:50 | NORMAL | create_time  | [types: [DATETIMEV2]; keys: [2020-01-01 00:00:00]; ..types: [DATETIMEV2]; keys: [2021-01-01 00:00:00]; ) | reference_no    | 1       | 1              | SSD           | 9999-12-31 23:59:59 |                     | NULL                     | 0.000    | false      | tag.location.default: 1 | true      | true               | NULL         |
+-------------+---------------+----------------+---------------------+--------+--------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+
  1. 展示指定 db 下指定表的指定非临时分区的信息,并对结果进行过滤
SHOW PARTITIONS FROM t_agg WHERE PartitionName = "p2024";
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime  | State  | PartitionKey    | Range                                                                                                    | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize  | IsInMemory | ReplicaAllocation       | IsMutable | SyncWithBaseTables | UnsyncTables |
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
| 169851      | p2024         | 2              | 2024-11-05 14:14:29 | NORMAL | idp_create_time | [types: [DATETIMEV2]; keys: [2024-01-01 00:00:00]; ..types: [DATETIMEV2]; keys: [2025-01-01 00:00:00]; ) | idp_es_id       | 3       | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 27.396 KB | false      | tag.location.default: 1 | true      | true               | NULL         |
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+-----------+------------+-------------------------+-----------+--------------------+--------------+
  1. 展示指定 db 下指定表的最新非临时分区的信息
SHOW PARTITIONS FROM t_agg ORDER BY PartitionId DESC LIMIT 1;
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+
| PartitionId | PartitionName | VisibleVersion | VisibleVersionTime  | State  | PartitionKey    | Range                                                                                                    | DistributionKey | Buckets | ReplicationNum | StorageMedium | CooldownTime        | RemoteStoragePolicy | LastConsistencyCheckTime | DataSize | IsInMemory | ReplicaAllocation       | IsMutable | SyncWithBaseTables | UnsyncTables |
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+
| 169866      | p2025         | 1              | 2024-11-05 14:13:56 | NORMAL | idp_create_time | [types: [DATETIMEV2]; keys: [2025-01-01 00:00:00]; ..types: [DATETIMEV2]; keys: [2026-01-01 00:00:00]; ) | idp_es_id       | 3       | 1              | HDD           | 9999-12-31 23:59:59 |                     | NULL                     | 0.000    | false      | tag.location.default: 1 | true      | true               | NULL         |
+-------------+---------------+----------------+---------------------+--------+-----------------+----------------------------------------------------------------------------------------------------------+-----------------+---------+----------------+---------------+---------------------+---------------------+--------------------------+----------+------------+-------------------------+-----------+--------------------+--------------+

本文来自 Apache Doris 文档贡献者,按 Apache 2.0 整理版式与相对链接,保留正文与代码。图示请参阅原文。作者与贡献记录。

资料来源与适用范围

资料来源
Apache Doris 文档贡献者
适用范围
master 文档快照(2026-11-16)
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-16
授权说明
Apache 2.0

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。