PARTITIONS

PARTITIONS

PARTITIONS 表提供了关于分区表的信息。

1. USE INFORMATION_SCHEMA;
2. DESC PARTITIONS;

结果如下：

1. +-------------------------------+----------+------+------+---------+---------------+
2. | Column                        | Type     | Key  | Null | Default | Semantic Type |
3. +-------------------------------+----------+------+------+---------+---------------+
4. | table_catalog                 | String   |      | NO   |         | FIELD         |
5. | table_schema                  | String   |      | NO   |         | FIELD         |
6. | table_name                    | String   |      | NO   |         | FIELD         |
7. | partition_name                | String   |      | NO   |         | FIELD         |
8. | subpartition_name             | String   |      | YES  |         | FIELD         |
9. | partition_ordinal_position    | Int64    |      | YES  |         | FIELD         |
10. | subpartition_ordinal_position | Int64    |      | YES  |         | FIELD         |
11. | partition_method              | String   |      | YES  |         | FIELD         |
12. | subpartition_method           | String   |      | YES  |         | FIELD         |
13. | partition_expression          | String   |      | YES  |         | FIELD         |
14. | subpartition_expression       | String   |      | YES  |         | FIELD         |
15. | partition_description         | String   |      | YES  |         | FIELD         |
16. | table_rows                    | Int64    |      | YES  |         | FIELD         |
17. | avg_row_length                | Int64    |      | YES  |         | FIELD         |
18. | data_length                   | Int64    |      | YES  |         | FIELD         |
19. | max_data_length               | Int64    |      | YES  |         | FIELD         |
20. | index_length                  | Int64    |      | YES  |         | FIELD         |
21. | data_free                     | Int64    |      | YES  |         | FIELD         |
22. | create_time                   | DateTime |      | YES  |         | FIELD         |
23. | update_time                   | DateTime |      | YES  |         | FIELD         |
24. | check_time                    | DateTime |      | YES  |         | FIELD         |
25. | checksum                      | Int64    |      | YES  |         | FIELD         |
26. | partition_comment             | String   |      | YES  |         | FIELD         |
27. | nodegroup                     | String   |      | YES  |         | FIELD         |
28. | tablespace_name               | String   |      | YES  |         | FIELD         |
29. | greptime_partition_id         | UInt64   |      | YES  |         | FIELD         |
30. +-------------------------------+----------+------+------+---------+---------------+
31. 26 rows in set (0.01 sec)

主要列包括：

table_catalog：表所属目录的名称。该值始终为 def。
table_schema：表所属的 schema（数据库）的名称。
table_name：包含分区（region）的表的名称。
partition_name：分区（region）的名称。
partition_ordinal_position：所有分区按照定义的顺序进行索引，1 是分配给第一个分区的编号。
partition_method：该值始终为 RANGE，GreptimeDB 仅支持范围分区。
partition_expression：该分区的表达式。
create_time：分区创建的时间。
greptime_partition_id：GreptimeDB 扩展字段，也就是 Region Id。

创建一张分区表并查询：

1. CREATE TABLE public.test_p (
2. a INT PRIMARY KEY,
3. b STRING,
4. ts TIMESTAMP TIME INDEX,
5. )
6. PARTITION ON COLUMNS (a) (
7. a < 10,
8. a >= 10 AND a < 20,
9. a >= 20
10. );

12. --- 查询表的分区信息---
13. SELECT * FROM PARTITIONS WHERE table_schema='public' AND table_name='test_p'\G

示例输出如下：

1. *************************** 1. row ***************************
2. table_catalog: greptime
3. table_schema: public
4. table_name: test_p
5. partition_name: p0
6. subpartition_name: NULL
7. partition_ordinal_position: 1
8. subpartition_ordinal_position: NULL
9. partition_method: RANGE
10. subpartition_method: NULL
11. partition_expression: (a) VALUES LESS THAN (PartitionExpr { lhs: Column("a"), op: Lt, rhs: Value(Int32(10)) })
12. subpartition_expression: NULL
13. partition_description: NULL
14. table_rows: NULL
15. avg_row_length: NULL
16. data_length: NULL
17. max_data_length: NULL
18. index_length: NULL
19. data_free: NULL
20. create_time: 2024-04-01 10:49:49.468000
21. update_time: NULL
22. check_time: NULL
23. checksum: NULL
24. partition_comment: NULL
25. nodegroup: NULL
26. tablespace_name: NULL
27. greptime_partition_id: 4453881085952
28. *************************** 2. row ***************************
29. table_catalog: greptime
30. table_schema: public
31. table_name: test_p
32. partition_name: p1
33. subpartition_name: NULL
34. partition_ordinal_position: 2
35. subpartition_ordinal_position: NULL
36. partition_method: RANGE
37. subpartition_method: NULL
38. partition_expression: (a) VALUES LESS THAN (PartitionExpr { lhs: Column("a"), op: GtEq, rhs: Value(Int32(20)) })
39. subpartition_expression: NULL
40. partition_description: NULL
41. table_rows: NULL
42. avg_row_length: NULL
43. data_length: NULL
44. max_data_length: NULL
45. index_length: NULL
46. data_free: NULL
47. create_time: 2024-04-01 10:49:49.468000
48. update_time: NULL
49. check_time: NULL
50. checksum: NULL
51. partition_comment: NULL
52. nodegroup: NULL
53. tablespace_name: NULL
54. greptime_partition_id: 4453881085954
55. *************************** 3. row ***************************
56. table_catalog: greptime
57. table_schema: public
58. table_name: test_p
59. partition_name: p2
60. subpartition_name: NULL
61. partition_ordinal_position: 3
62. subpartition_ordinal_position: NULL
63. partition_method: RANGE
64. subpartition_method: NULL
65. partition_expression: (a) VALUES LESS THAN (PartitionExpr { lhs: Expr(PartitionExpr { lhs: Column("a"), op: Gt, rhs: Value(Int32(10)) }), op: And, rhs: Expr(PartitionExpr { lhs: Column("a"), op: Lt, rhs: Value(Int32(20)) }) })
66. subpartition_expression: NULL
67. partition_description: NULL
68. table_rows: NULL
69. avg_row_length: NULL
70. data_length: NULL
71. max_data_length: NULL
72. index_length: NULL
73. data_free: NULL
74. create_time: 2024-04-01 10:49:49.468000
75. update_time: NULL
76. check_time: NULL
77. checksum: NULL
78. partition_comment: NULL
79. nodegroup: NULL
80. tablespace_name: NULL
81. greptime_partition_id: 4453881085953