CLUSTER BY Clause

    1. CREATE TABLE person (name STRING, age INT);
    2. INSERT INTO person VALUES
    3.     ('Zen Hui', 25),
    4.     ('Anil B', 18),
    5.     ('Shone S', 16),
    6.     ('Mike A', 25),
    7.     ('John A', 18),
    8.     ('Jack N', 16);
    10. -- Reduce the number of shuffle partitions to 2 to illustrate the behavior of `CLUSTER BY`.
    11. -- It's easier to see the clustering and sorting behavior with less number of partitions.
    12. SET spark.sql.shuffle.partitions = 2;
    14. -- Select the rows with no ordering. Please note that without any sort directive, the results
    15. -- of a query when `CLUSTER BY` is not used vs when it's used. The query below produces rows
    16. -- where age column is not sorted.
    17. SELECT age, name FROM person;
    18. +---+-------+
    19. |age|   name|
    20. +---+-------+
    21. | 16|Shone S|
    22. | 25|Zen Hui|
    23. | 16| Jack N|
    24. | 25| Mike A|
    25. | 18| John A|
    26. | 18| Anil B|
    27. +---+-------+
    30. -- In the query below, persons with age 18 and 25 are in first partition and the
    31. -- persons with age 16 are in the second partition. The rows are sorted based
    32. -- on age within each partition.
    33. SELECT age, name FROM person CLUSTER BY age;
    34. +---+-------+
    35. +---+-------+
    36. | 18| John A|
    37. | 18| Anil B|
    38. | 25|Zen Hui|
    39. | 25| Mike A|
    40. | 16|Shone S|
    41. | 16| Jack N|
    42. +---+-------+