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
SHOW PARTITIONS table_identifier [ partition_spec ] [ AS JSON ]
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 [ , ... ] ) -
AS JSON
An optional parameter to return the partition list as a single-row JSON document instead of the default tabular format. Only supported for V1 (session catalog / Hive metastore) tables.
Syntax:
[ AS JSON ]Output schema: A single column named
json_metadataof typeSTRING NOT NULL.Schema:
Below is the full JSON schema. In actual output, the JSON is not pretty-printed (see Examples).
{ "partitions": [{"col": "val", ...}, ...] }Field Type Description partitionsarray of objects Each element is a JSON object mapping partition column names to their string values. The array is empty when no partitions match the optional partition_spec. Elements appear in the order returned by the underlying catalog (lexicographic for in-memory and Hive catalogs); no order is guaranteed across catalogs.
Examples
-- create a partitioned table and insert a few rows.
USE salesdb;
CREATE TABLE customer(id INT, name STRING) PARTITIONED BY (state STRING, city STRING);
INSERT INTO customer PARTITION (state = 'CA', city = 'Fremont') VALUES (100, 'John');
INSERT INTO customer PARTITION (state = 'CA', city = 'San Jose') VALUES (200, 'Marry');
INSERT INTO customer PARTITION (state = 'AZ', city = 'Peoria') VALUES (300, 'Daniel');
-- Lists all partitions for table `customer`
SHOW PARTITIONS customer;
+----------------------+
| partition|
+----------------------+
| state=AZ/city=Peoria|
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+
-- Lists all partitions for the qualified table `customer`
SHOW PARTITIONS salesdb.customer;
+----------------------+
| partition|
+----------------------+
| state=AZ/city=Peoria|
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+
-- Specify a full partition spec to list specific partition
SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
+---------------------+
| partition|
+---------------------+
|state=CA/city=Fremont|
+---------------------+
-- Specify a partial partition spec to list the specific partitions
SHOW PARTITIONS customer PARTITION (state = 'CA');
+----------------------+
| partition|
+----------------------+
| state=CA/city=Fremont|
|state=CA/city=San Jose|
+----------------------+
-- Specify a partial spec to list specific partition
SHOW PARTITIONS customer PARTITION (city = 'San Jose');
+----------------------+
| partition|
+----------------------+
|state=CA/city=San Jose|
+----------------------+
-- List all partitions as a JSON
SHOW PARTITIONS customer AS JSON;
+--------------------------------------------------------------------------------------------------------------+
|json_metadata |
+--------------------------------------------------------------------------------------------------------------+
|{"partitions":[{"state":"AZ","city":"Peoria"},{"state":"CA","city":"Fremont"},{"state":"CA","city":"San Jose"}]}|
+--------------------------------------------------------------------------------------------------------------+
-- Filter with a partial partition spec and return results as JSON
SHOW PARTITIONS customer PARTITION (state = 'CA') AS JSON;
+-----------------------------------------------------------------------------------+
|json_metadata |
+-----------------------------------------------------------------------------------+
|{"partitions":[{"state":"CA","city":"Fremont"},{"state":"CA","city":"San Jose"}]} |
+-----------------------------------------------------------------------------------+
-- Filter with a full partition spec as JSON
SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont') AS JSON;
+--------------------------------------------------+
|json_metadata |
+--------------------------------------------------+
|{"partitions":[{"state":"CA","city":"Fremont"}]} |
+--------------------------------------------------+
-- When no partitions match the spec, AS JSON returns an empty array
SHOW PARTITIONS customer PARTITION (state = 'TX') AS JSON;
+------------------+
|json_metadata |
+------------------+
|{"partitions":[]} |
+------------------+