CLUSTER BY 句CLUSTER BY clause

入力式に基づいてデータを再分割し、各パーティション内のデータを並べ替えます。Repartitions the data based on the input expressions and then sorts the data within each partition. これは、 分布 の後に による並べ替えを実行するのと同じ意味です。This is semantically equivalent to performing a DISTRIBUTE BY followed by a SORT BY. この句では、結果の行が各パーティション内で並べ替えられるだけで、出力の合計順序が保証されるわけではありません。This clause only ensures that the resultant rows are sorted within each partition and does not guarantee a total order of output.

構文Syntax

CLUSTER BY { expression [ , ... ] }

パラメーターParameters

  • 式 (expression)expression

    値を生成する1つ以上の値、演算子、および SQL 関数の組み合わせを指定します。Specifies combination of one or more values, operators and SQL functions that results in a value.

Examples

CREATE TABLE person (name STRING, age INT);
INSERT INTO person VALUES
    ('Zen Hui', 25),
    ('Anil B', 18),
    ('Shone S', 16),
    ('Mike A', 25),
    ('John A', 18),
    ('Jack N', 16);

-- Reduce the number of shuffle partitions to 2 to illustrate the behavior of `CLUSTER BY`.
-- It's easier to see the clustering and sorting behavior with less number of partitions.
SET spark.sql.shuffle.partitions = 2;

-- Select the rows with no ordering. Please note that without any sort directive, the results
-- of the query is not deterministic. It's included here to show the difference in behavior
-- of a query when `CLUSTER BY` is not used vs when it's used. The query below produces rows
-- where age column is not sorted.
SELECT age, name FROM person;
+---+-------+
|age|   name|
+---+-------+
| 16|Shone S|
| 25|Zen Hui|
| 16| Jack N|
| 25| Mike A|
| 18| John A|
| 18| Anil B|
+---+-------+

-- Produces rows clustered by age. Persons with same age are clustered together.
-- In the query below, persons with age 18 and 25 are in first partition and the
-- persons with age 16 are in the second partition. The rows are sorted based
-- on age within each partition.
SELECT age, name FROM person CLUSTER BY age;
+---+-------+
|age|   name|
+---+-------+
| 18| John A|
| 18| Anil B|
| 25|Zen Hui|
| 25| Mike A|
| 16|Shone S|
| 16| Jack N|
+---+-------+