Hive のsort by, order by, distribute by

sort by , order by, distribute byのOutput

アウトプットについてあまり載っているがサイトなかったので
実際にクエリを投げて確認してみます。

投入データ

df = spark.sql('select * from food.food_list')
df.show()

+---+-------------+-------------+
| id|         food|     location|
+---+-------------+-------------+
| 14|        sushi|japanese food|
|  2|     sandwich| western food|
| 16|    spaghetti| western food|
|  4|        pizza| western food|
|  5|     nikujaga|japanese food|
|  6|         soba|japanese food|
|  7|         udon|japanese food|
|  8|      unajyuu|japanese food|
| 15|      Hamburg| western food|
| 10|      tenpura|japanese food|
| 11|      karaage|japanese food|
| 13|      tonkatu|japanese food|
|  1|     sukiyaki|japanese food|
|  3|fried chicken| western food|
| 12|        omlet| western food|
|  9|       gratin| western food|
+---+-------------+-------------+

型確認

+--------+---------+-------+
|col_name|data_type|comment|
+--------+---------+-------+
|      id|      int|   null|
|    food|   string|   null|
|location|   string|   null|
+--------+---------+-------+

SORT BY

sort by output (id)

select * from food_list sort by id;

1       sukiyaki        japanese food
2       sandwich        western food
3       fried chicken   western food
4       pizza   western food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
9       gratin  western food
10      tenpura japanese food
11      karaage japanese food
12      omlet   western food
13      tonkatu japanese food
14      sushi   japanese food
15      Hamburg western food
16      spaghetti       western food

sort by output (food)

hive> select * from food_list sort by food;

15      Hamburg western food
3       fried chicken   western food
9       gratin  western food
11      karaage japanese food
5       nikujaga        japanese food
12      omlet   western food
4       pizza   western food
2       sandwich        western food
6       soba    japanese food
16      spaghetti       western food
1       sukiyaki        japanese food
14      sushi   japanese food
10      tenpura japanese food
13      tonkatu japanese food
7       udon    japanese food
8       unajyuu japanese food

sort by output (location)

hive> select * from food.food_list sort by location;

10      tenpura japanese food
11      karaage japanese food
13      tonkatu japanese food
1       sukiyaki        japanese food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
14      sushi   japanese food
15      Hamburg western food
2       sandwich        western food
16      spaghetti       western food
4       pizza   western food
3       fried chicken   western food
12      omlet   western food
9       gratin  western food

ORDER BY

order by output (id)

hive> select * from food.food_list order by id;

1       sukiyaki        japanese food
2       sandwich        western food
3       fried chicken   western food
4       pizza   western food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
9       gratin  western food
10      tenpura japanese food
11      karaage japanese food
12      omlet   western food
13      tonkatu japanese food
14      sushi   japanese food
15      Hamburg western food
16      spaghetti       western food

order by output (food)

hive> select * from food.food_list order by food;

15      Hamburg western food
3       fried chicken   western food
9       gratin  western food
11      karaage japanese food
5       nikujaga        japanese food
12      omlet   western food
4       pizza   western food
2       sandwich        western food
6       soba    japanese food
16      spaghetti       western food
1       sukiyaki        japanese food
14      sushi   japanese food
10      tenpura japanese food
13      tonkatu japanese food
7       udon    japanese food
8       unajyuu japanese food

order by output (location)

hive> select * from food.food_list order by location;

10      tenpura japanese food
11      karaage japanese food
13      tonkatu japanese food
1       sukiyaki        japanese food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
14      sushi   japanese food
15      Hamburg western food
2       sandwich        western food
16      spaghetti       western food
4       pizza   western food
3       fried chicken   western food
12      omlet   western food
9       gratin  western food

DISTRUBUTE BY

distribute by output (id,location,food)

distribute by はソートしないのでid,location,foodどれを指定してもアウトプットは同じ

hive> select * from food.food_list distribute by id;

14      sushi   japanese food
2       sandwich        western food
16      spaghetti       western food
4       pizza   western food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
15      Hamburg western food
10      tenpura japanese food
11      karaage japanese food
13      tonkatu japanese food
1       sukiyaki        japanese food
3       fried chicken   western food
12      omlet   western food
9       gratin  western food

distribute by ＆ sort by

hive> select * from food.food_list
    > distribute by location
    > sort by id;

1       sukiyaki        japanese food
2       sandwich        western food
3       fried chicken   western food
4       pizza   western food
5       nikujaga        japanese food
6       soba    japanese food
7       udon    japanese food
8       unajyuu japanese food
9       gratin  western food
10      tenpura japanese food
11      karaage japanese food
12      omlet   western food
13      tonkatu japanese food
14      sushi   japanese food
15      Hamburg western food
16      spaghetti       western food
Time taken: 4.971 seconds, Fetched: 16 row(s)