0001 ---
0002 layout: global
0003 title: DISTRIBUTE BY Clause
0004 displayTitle: DISTRIBUTE BY Clause
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 `DISTRIBUTE BY` clause is used to repartition the data based
0025 on the input expressions. Unlike the [CLUSTER BY](sql-ref-syntax-qry-select-clusterby.html)
0026 clause, this does not sort the data within each partition.
0027
0028 ### Syntax
0029
0030 ```sql
0031 DISTRIBUTE BY { expression [ , ... ] }
0032 ```
0033
0034 ### Parameters
0035
0036 * **expression**
0037
0038 Specifies combination of one or more values, operators and SQL functions that results in a value.
0039
0040 ### Examples
0041
0042 ```sql
0043 CREATE TABLE person (name STRING, age INT);
0044 INSERT INTO person VALUES
0045 ('Zen Hui', 25),
0046 ('Anil B', 18),
0047 ('Shone S', 16),
0048 ('Mike A', 25),
0049 ('John A', 18),
0050 ('Jack N', 16);
0051
0052 -- Reduce the number of shuffle partitions to 2 to illustrate the behavior of `DISTRIBUTE BY`.
0053 -- It's easier to see the clustering and sorting behavior with less number of partitions.
0054 SET spark.sql.shuffle.partitions = 2;
0055
0056 -- Select the rows with no ordering. Please note that without any sort directive, the result
0057 -- of the query is not deterministic. It's included here to just contrast it with the
0058 -- behavior of `DISTRIBUTE BY`. The query below produces rows where age columns are not
0059 -- clustered together.
0060 SELECT age, name FROM person;
0061 +---+-------+
0062 |age| name|
0063 +---+-------+
0064 | 16|Shone S|
0065 | 25|Zen Hui|
0066 | 16| Jack N|
0067 | 25| Mike A|
0068 | 18| John A|
0069 | 18| Anil B|
0070 +---+-------+
0071
0072 -- Produces rows clustered by age. Persons with same age are clustered together.
0073 -- Unlike `CLUSTER BY` clause, the rows are not sorted within a partition.
0074 SELECT age, name FROM person DISTRIBUTE BY age;
0075 +---+-------+
0076 |age| name|
0077 +---+-------+
0078 | 25|Zen Hui|
0079 | 25| Mike A|
0080 | 18| John A|
0081 | 18| Anil B|
0082 | 16|Shone S|
0083 | 16| Jack N|
0084 +---+-------+
0085 ```
0086
0087 ### Related Statements
0088
0089 * [SELECT Main](sql-ref-syntax-qry-select.html)
0090 * [WHERE Clause](sql-ref-syntax-qry-select-where.html)
0091 * [GROUP BY Clause](sql-ref-syntax-qry-select-groupby.html)
0092 * [HAVING Clause](sql-ref-syntax-qry-select-having.html)
0093 * [ORDER BY Clause](sql-ref-syntax-qry-select-orderby.html)
0094 * [SORT BY Clause](sql-ref-syntax-qry-select-sortby.html)
0095 * [CLUSTER BY Clause](sql-ref-syntax-qry-select-clusterby.html)
0096 * [LIMIT Clause](sql-ref-syntax-qry-select-limit.html)
