hive order by sort by distribute by cluster by

order by 文法

Hive QL中的order by 類似于SQL語言中的ORDER BY文法。

排序方式：ASC|DESC

空值排序方式：NULLS FIRST|NULLS LAST

文法：order by 字段名稱 ASC|DESC [NULLS FIRST|NULLS LAST]

“ order by”子句中有一些限制。在嚴格模式下（即hive.mapred.mode = strict），必須在order by子句後跟一個“ limit”子句。如果将hive.mapred.mode設定為nonstrict，則limit子句不是必須的。Order by 會對查詢結果集執行一個全局排序。這也就是說會有一個所有的資料都通過一個reducer進行處理的過程。對于大資料集，這個過程可能會消耗太過漫長的時間來執行。

是以，為了避免這種情況發生，一個優化方案為：hive.mapred.mode=strict。（colin總結）

請注意，列是通過名稱而不是位置編号指定的。但是，在Hive 0.11.0和更高版本中， 按以下方式配置時可以按位置指定列：

對于Hive 0.11.0到2.1.x，将hive.groupby.orderby.position.alias設定 為true（預設值為false）。

對于Hive 2.2.0和更高版本， 預設情況下， hive.orderby.position.alias為true。

預設排序順序為升序（ASC）。

在Hive 2.1.0和更高版本中，支援為“ order by”子句中的每個列指定空排序順序。ASC順序的預設空排序順序為NULLS FIRST，而DESC順序的預設空排序順序為NULLS LAST。

In Hive 3.0.0 and later, order by without limit in subqueries and views will be removed by the optimizer. To disable it, set hive.remove.orderby.in.subquery to false.

在hive3.0.0及更高版本中，子查詢和視圖中沒有limit子句的order by将會被優化器移除。可以通過設定 hive.remove.orderby.in.subquery為false來使此失效。

Sort by 文法

Sort by文法類似于文法SQL語言中的ORDER BY。

排序方式: ( ASC | DESC )

文法: SORT BY 列名稱 [ASC|DESC]

Sort by隻會在每個reducer中對資料進行排序，也就是一個局部排序過程。這可以保證每個reducer的輸出資料都是有序的（但并非全局有序列）。

排序方式将取決于列的類型。如果列是一個數值列，則排序也是數值排序。如果列是一個字元列，那麼排序也是字元排序。

在hive3.0.0及後續版本中，子查詢和視圖中沒有limit的sort by 子句将會被優化器給移除。使此失效，設定hive.remove.orderby.in.subquery為false.

Sort by 和order by 的差別

Hive支援SORT BY，可對每個reducer的資料進行排序。“ order by”和“ sort by”之間的差別在于，前者保證輸出中的總順序，而後者僅保證reducer中行的排序。如果存在多個reducer，則“排序依據”可能會給出最終結果的部分排序。

注意：關于單列的單獨SORT BY與CLUSTER BY之間的差別可能會造成混淆。差別在于，如果有多個reducer分區，則CLUSTER BY按字段劃分，而SORT BY則是随機劃分，以便在reducer上均勻地分布資料（和負載）。

基本上，每個reducer中的資料将根據使用者指定的順序進行排序。以下示例顯示

SELECT key, value FROM src SORT BY key ASC, value DESC

該查詢具有2個reducer，每個的輸出為：

0 5

0 3

3 6

9 1

0 4

0 3

1 1

2 5

Cluster By和Distribute By文法

Cluster By和Distribute By主要與Transform / Map-Reduce腳本一起使用。但是，如果需要對查詢的輸出進行分區和排序以用于後續查詢，有時在SELECT語句中很有用。

Cluster By相當于Distribute By和Sort By。

hive使用 Distribute By中的列在reducer之間配置設定列。具有相同Distribute By列的所有行将轉到相同的reducer。但是，“Distribute By不保證分布鍵上的聚類或排序屬性。

例如，我們将以下5行上的x配置設定給2個reducer：

x1

x2

x4

x3

x1

Reducer 1得到以下：

x1

x2

x1

Reducer 2得到以下：

x4

x3

請注意，具有相同鍵x1的所有行都可以保證配置設定給同一reducer（在這種情況下為reducer 1），但不能保證它們聚集在相鄰位置。

相反，如果我們使用Cluster By x，則兩個化簡器将進一步對x上的行進行排序：

Reducer 1得到以下：

x1

x1

x2

Reducer 2得到以下：

x3

x4

使用者可以指定Distribute By和Sort By來代替指定Cluster By，是以分區列和排序列可以不同。通常的情況是分區列是排序列的字首，但這不是必需的。

DISTRIBUTE BY

DISTRIBUTE BY 控制map的輸出在reducer中是如何劃分的。

MapReduce job中傳輸的所有資料都是按照鍵-值對的方式進行組織的，是以Hive在将使用者的查詢語句轉成MapReduce job時，其必須在内部使用這個功能。

假設我們希望具有相同股票交易碼的資料在一起處理。那麼我們可以使用DISTRIBUTE BY 來保證具有相同股票交易碼的記錄會分發到同一個reducer中進行處理，然後使用sort by 來按照我們的期望對資料進行排序。

例：

Select s.ymd,s.symbol,s.price_close

From stocks s

Distribute by s.symbol

Sort by s.symbol asc,s.ymd asc;

Distribute by 和 group by 在其控制着reducer是如何接受一行行資料進行處理這方面是類似的。而sort by 則控制着reducer内的資料是如何進行排序的。

CLUSTER BY

CLUSTER BY 字段1 = DISTRIBUTE BY 字段1 SORT BY 字段1

即distribute by 和sort by 中涉及到的列完全相同，而且采用的是升序排序方式（也就是預設的排序方式），那麼在這種情況下，CLUSTER BY 就等價于distribute by 字段 sort by 字段。

相當于order by ,全局排序