SHOW PARTITIONS

Description

The SHOW PARTITIONS statement is used to list partitions of a table. An optional partition spec may be specified to return the partitions matching the supplied partition spec.

Syntax

  1. SHOW PARTITIONS table_identifier [ partition_spec ]

Parameters

  • table_identifier

    Specifies a table name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • partition_spec

    An optional parameter that specifies a comma separated list of key and value pairs for partitions. When specified, the partitions that match the partition specification are returned.

    Syntax: PARTITION ( partition_col_name = partition_col_val [ , ... ] )

Examples

  1. -- create a partitioned table and insert a few rows.
  2. USE salesdb;
  3. CREATE TABLE customer(id INT, name STRING) PARTITIONED BY (state STRING, city STRING);
  4. INSERT INTO customer PARTITION (state = 'CA', city = 'Fremont') VALUES (100, 'John');
  5. INSERT INTO customer PARTITION (state = 'CA', city = 'San Jose') VALUES (200, 'Marry');
  6. INSERT INTO customer PARTITION (state = 'AZ', city = 'Peoria') VALUES (300, 'Daniel');
  8. -- Lists all partitions for table `customer`
  9. SHOW PARTITIONS customer;
  10. +----------------------+
  11. |             partition|
  12. +----------------------+
  13. |  state=AZ/city=Peoria|
  14. | state=CA/city=Fremont|
  15. |state=CA/city=San Jose|
  16. +----------------------+
  18. -- Lists all partitions for the qualified table `customer`
  19. SHOW PARTITIONS salesdb.customer;
  20. +----------------------+
  21. |             partition|
  22. +----------------------+
  23. |  state=AZ/city=Peoria|
  24. | state=CA/city=Fremont|
  25. |state=CA/city=San Jose|
  26. +----------------------+
  28. -- Specify a full partition spec to list specific partition
  29. SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
  30. +---------------------+
  31. |            partition|
  32. +---------------------+
  33. |state=CA/city=Fremont|
  34. +---------------------+
  36. -- Specify a partial partition spec to list the specific partitions
  37. SHOW PARTITIONS customer PARTITION (state = 'CA');
  38. +----------------------+
  39. |             partition|
  40. +----------------------+
  41. | state=CA/city=Fremont|
  42. |state=CA/city=San Jose|
  43. +----------------------+
  45. -- Specify a partial spec to list specific partition
  46. SHOW PARTITIONS customer PARTITION (city =  'San Jose');
  47. +----------------------+
  48. |             partition|
  49. +----------------------+
  50. |state=CA/city=San Jose|
  51. +----------------------+