SHOW PARTITIONS

    • table_identifier

      Syntax: [ database_name. ] table_name

    • 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.

    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=San Jose|
    17. -- Lists all partitions for the qualified table `customer`
    18. SHOW PARTITIONS salesdb.customer;
    19. +----------------------+
    20. |             partition|
    21. +----------------------+
    22. |  state=AZ/city=Peoria|
    23. | state=CA/city=Fremont|
    24. |state=CA/city=San Jose|
    25. +----------------------+
    27. -- Specify a full partition spec to list specific partition
    28. SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
    29. +---------------------+
    30. |            partition|
    31. +---------------------+
    32. |state=CA/city=Fremont|
    34. -- Specify a partial partition spec to list the specific partitions
    35. SHOW PARTITIONS customer PARTITION (state = 'CA');
    36. +----------------------+
    37. |             partition|
    38. +----------------------+
    39. | state=CA/city=Fremont|
    40. |state=CA/city=San Jose|
    41. +----------------------+
    43. -- Specify a partial spec to list specific partition
    44. SHOW PARTITIONS customer PARTITION (city =  'San Jose');
    45. +----------------------+
    46. |             partition|
    47. +----------------------+
    48. |state=CA/city=San Jose|
    49. +----------------------+