Hive Data Retrieval Queries: Difference between CLUSTER BY, ORDER BY, and SORT BY

by

In short, for your questions:

  1. Does CLUSTER BY guarantee a global order?
    • No.
  2. DISTRIBUTE BY puts the same keys into same reducers but what about the adjacent keys?

The longer version:

To understand Hive, first you must understand Map Reduce. The relevant property of Map Reduce for this question is that when data arrives at the reducing stage, they would have always gone through shuffling and sorting on mapper or reducer before the reducing step. Then:

  • ORDER BY x: Data guarantees global ordering by x by using one reducer for the last reducing step. This will be slow for large outputs. It is however acceptable even for processing large datasets as long as you know at the last step the data size is small. (e.g. after filtering and aggregation)
  • SORT BY x: guarantees local ordering specifically by x, at each of N reducers. Global ordering is not guaranteed.
  • DISTRIBUTE BY x: guarantees that the rows of the same distribute key x goes to the same reducer but does NOT guarantees your desired ordering. Data at reducing stage is still sorted, but NOT necessarily by x.
  • CLUSTER BY x: This is the same as doing (DISTRIBUTE BY x and SORT BY x).

Please refer to the official wiki for detailed mechanism and syntax:
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy

Edited:
As mentioned in comments and other answers, previous answer is incorrect and the choice of words may add wrong nuances. The use of of the phrase overlapping-range is misleading since shuffling is done with a hash function, which is NOT necessarily by range. For instance depending on implementation hashing integers by even or odd number is valid, which is overlapping in terms of number range. DISTRIBUTE BY in this case only guarantees all even numbers goes to the same reducer, and all odd numbers goes to another.

The other answers have minor mistakes, or unrelated to the question, or too verbose.

More Related Contents:

  1. What is the difference between partitioning and bucketing a table in Hive ?
  2. Difference between Hive internal tables and external tables?
  3. How to transpose/pivot data in hive?
  4. Sqoop import : composite primary key and textual primary key
  5. How to delete and update a record in Hive
  6. Hive unable to manually set number of reducers
  7. How to update table in Hive 0.13?
  8. how many mappers and reduces will get created for a partitoned table in hive
  9. Hive: Add partitions for existing folder structure
  10. How to load data to hive from HDFS without removing the source file?
  11. Loading Data from a .txt file to Table Stored as ORC in Hive
  12. Create HIVE Table with multi character delimiter
  13. Job queue for Hive action in oozie
  14. Difference between Pig and Hive? Why have both? [closed]
  15. What is Hive: Return Code 2 from org.apache.hadoop.hive.ql.exec.MapRedTask
  16. Can Hive recursively descend into subdirectories without partitions or editing hive-site.xml?
  17. Begenner at spark Big data programming (spark code)
  18. Failed to locate the winutils binary in the hadoop binary path
  19. How does Hadoop Namenode failover process works?
  20. How to open/stream .zip files through Spark?
  21. When do reduce tasks start in Hadoop?
  22. Hadoop java.io.IOException: Mkdirs failed to create /some/path
  23. Hive query performance for high cardinality field
  24. How to convert .txt file to Hadoop’s sequence file format
  25. java.lang.NoClassDefFoundError: org/apache/hadoop/fs/StorageStatistics
  26. Hadoop speculative task execution
  27. Is it better to have one large parquet file or lots of smaller parquet files?
  28. Easiest way to install Python dependencies on Spark executor nodes?
  29. Difference between HBase and Hadoop/HDFS
  30. hadoop: difference between 0 reducer and identity reducer?

Leave a Comment