1.分区排序（Distribute By）
Distribute By： 类似 MR 中 partition， 进行分区， 结合 sort by 使用。
注意， Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试， 一定要分配多 reduce 进行处理， 否则无法看到 distribute by的效果。
案例实操：
（1） 先按照部门编号分区， 再按照员工编号降序排序。

hive (default)> set mapreduce.job.reduces=3;
 hive (default)> insert overwrite local directory
 '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

 

2.Cluster By
当 distribute by 和 sorts by 字段相同时， 可以使用 cluster by 方式。
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是倒序排序， 不能指定排序规则为 ASC 或者 DESC。
1） 以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;
注意： 按照部门编号分区， 不一定就是固定死的数值， 可以是 20 号和 30 号部门分到一个分区里面去。

 

3.分桶抽样查询
对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。 Hive 可以通过对表进行抽样来满足这个需求。查询表 stu_buck 中的数据。
hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);
注： tablesample 是抽样语句， 语法： TABLESAMPLE(BUCKET x OUT OF y) 。

y 必须是 table 总 bucket 数的倍数或者因子。 hive 根据 y 的大小， 决定抽样的比例。 例
如， table 总共分了 4 份， 当 y=2 时， 抽取(4/2=)2 个 bucket 的数据， 当 y=8 时， 抽取(4/8=)1/2个 bucket 的数据。
x 表示从哪个 bucket 开始抽取， 如果需要取多个分区， 以后的分区号为当前分区号加上y。

例如， table 总 bucket 数为 4， tablesample(bucket 1 out of 2)， 表示总共抽取（4/2=） 2 个bucket 的数据， 抽取第 1(x)个和第 4(x+y)个 bucket 的数据。

注意： x 的值必须小于等于 y 的值， 否则
FAILED: SemanticException [Error 10061]: Numerator should not be bigger than
denominator in sample clause for table stu_buck

4.行转列

CONCAT(string A/col, string B/col…)： 返回输入字符串连接后的结果， 支持任意个输入字符串;

CONCAT_WS(separator, str1, s4tr2,...)： 它是一个特殊形式的 CONCAT()。 第一个参数剩余参数间的分隔符。 分隔符可以是与剩余参数一样的字符串。 如果分隔符是 NULL，返回值也将为 NULL。 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。 分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)： 函数只接受基本数据类型， 它的主要作用是将某字段的值进行去重汇总， 产生 array 类型字段。
 

5.列转行

EXPLODE(col)： 将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

用法： LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释： 用于和 split, explode 等 UDTF 一起使用， 它能够将一列数据拆成多行数据， 在此基础上可以对拆分后的数据进行聚合

 

6.窗口函数

OVER()： 指定分析函数工作的数据窗口大小， 这个数据窗口大小可能会随着行的变而变化
CURRENT ROW： 当前行
n PRECEDING： 往前 n 行数据
n FOLLOWING： 往后 n 行数据
UNBOUNDED： 起点， UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING 表示到后面的终点
LAG(col,n)： 往前第 n 行数据
LEAD(col,n)： 往后第 n 行数据
NTILE(n)： 把有序分区中的行分发到指定数据的组中， 各个组有编号， 编号从 1 开始，
对于每一行， NTILE 返回此行所属的组的编号。 注意： n 必须为 int 类型。

 

7.Rank
RANK() 排序相同时会重复， 总数不会变
DENSE_RANK()排序相同时会重复， 总数会减少
ROW_NUMBER() 会根据顺序计算