0001 ---
0002 layout: global
0003 title: SHOW PARTITIONS
0004 displayTitle: SHOW PARTITIONS
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 The `SHOW PARTITIONS` statement is used to list partitions of a table. An optional
0025 partition spec may be specified to return the partitions matching the supplied
0026 partition spec.
0027
0028 ### Syntax
0029
0030 ```sql
0031 SHOW PARTITIONS table_identifier [ partition_spec ]
0032 ```
0033
0034 ### Parameters
0035
0036 * **table_identifier**
0037
0038 Specifies a table name, which may be optionally qualified with a database name.
0039
0040 **Syntax:** `[ database_name. ] table_name`
0041
0042 * **partition_spec**
0043
0044 An optional parameter that specifies a comma separated list of key and value pairs
0045 for partitions. When specified, the partitions that match the partition specification are returned.
0046
0047 **Syntax:** `PARTITION ( partition_col_name = partition_col_val [ , ... ] )`
0048
0049 ### Examples
0050
0051 ```sql
0052 -- create a partitioned table and insert a few rows.
0053 USE salesdb;
0054 CREATE TABLE customer(id INT, name STRING) PARTITIONED BY (state STRING, city STRING);
0055 INSERT INTO customer PARTITION (state = 'CA', city = 'Fremont') VALUES (100, 'John');
0056 INSERT INTO customer PARTITION (state = 'CA', city = 'San Jose') VALUES (200, 'Marry');
0057 INSERT INTO customer PARTITION (state = 'AZ', city = 'Peoria') VALUES (300, 'Daniel');
0058
0059 -- Lists all partitions for table `customer`
0060 SHOW PARTITIONS customer;
0061 +----------------------+
0062 | partition|
0063 +----------------------+
0064 | state=AZ/city=Peoria|
0065 | state=CA/city=Fremont|
0066 |state=CA/city=San Jose|
0067 +----------------------+
0068
0069 -- Lists all partitions for the qualified table `customer`
0070 SHOW PARTITIONS salesdb.customer;
0071 +----------------------+
0072 | partition|
0073 +----------------------+
0074 | state=AZ/city=Peoria|
0075 | state=CA/city=Fremont|
0076 |state=CA/city=San Jose|
0077 +----------------------+
0078
0079 -- Specify a full partition spec to list specific partition
0080 SHOW PARTITIONS customer PARTITION (state = 'CA', city = 'Fremont');
0081 +---------------------+
0082 | partition|
0083 +---------------------+
0084 |state=CA/city=Fremont|
0085 +---------------------+
0086
0087 -- Specify a partial partition spec to list the specific partitions
0088 SHOW PARTITIONS customer PARTITION (state = 'CA');
0089 +----------------------+
0090 | partition|
0091 +----------------------+
0092 | state=CA/city=Fremont|
0093 |state=CA/city=San Jose|
0094 +----------------------+
0095
0096 -- Specify a partial spec to list specific partition
0097 SHOW PARTITIONS customer PARTITION (city = 'San Jose');
0098 +----------------------+
0099 | partition|
0100 +----------------------+
0101 |state=CA/city=San Jose|
0102 +----------------------+
0103 ```
0104
0105 ### Related Statements
0106
0107 * [CREATE TABLE](sql-ref-syntax-ddl-create-table.html)
0108 * [INSERT STATEMENT](sql-ref-syntax-dml-insert.html)
0109 * [DESCRIBE TABLE](sql-ref-syntax-aux-describe-table.html)
0110 * [SHOW TABLE](sql-ref-syntax-aux-show-table.html)