PARTITIONS

The PARTITIONS table provides information about partitioned tables.

  1. USE INFORMATION_SCHEMA;
  2. DESC PARTITIONS;

The output is as follows:

  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)

Main columns:

  • table_catalog: The name of the catalog to which the table belongs. This value is always def.
  • table_schema: The name of the schema (database) to which the table belongs.
  • table_name: The name of the table containing the partition(region).
  • partition_name: The name of the partition(region).
  • partition_ordinal_position: All partitions are indexed in the same order as they are defined, with 1 being the number assigned to the first partition.
  • partition_method: This value is always RANGE, GreptimeDB only supports range partitioning.
  • partition_expression: The expression of this partition.
  • create_time: The time that the partition was created.
  • greptime_partition_id: GreptimeDB extended field, it’s the Region Id.

For example, create a partitioned table:

  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. --- Query the partitions of the table --
  13. SELECT * FROM PARTITIONS WHERE table_schema='public' AND table_name='test_p'\G

Outputs:

  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