Hive---高级技术

一、Mapjoin

set hive.auto.convert.join=true;
运行时自动调用Mapjoin，效率会提高：原因是减少reduce操作，reduce操作耗内存

二、数据传输

1、load
文件->表
load data local inpath “本地路径” into table 表名
load data inpath ‘HDFS路径’ into table 表名
表->文件
insert overwrite local directory “本地路径” select * from 表名
表->表
insert overwrite table 新表名 select * from 表名

2、export和import
export(导出)和import(导入)
1.export:
export table 表名 to ‘HDFS路径’
export table 表名 partition(字段) to ‘HDFS路径’
2.import:
【注意：表名必须是新的】
import table 表名 from ‘HDFS路径’
import table 表名 partition(字段) from ‘HDFS路径’

三、排序

order by:全局排序，使用一个reducer做全局排序
sort by:分区内排序，对每个reducer中的数据进行排序，当设置reducer数量为1时候，就是order by
distribute by:类似MR中partition，进行分区,在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中，在sort by 之前
cluster by:=distributed by+sort by。当distribute和sort相同字段时可以直接cluster字段,没有desc和asc,被cluster by的字段必须在select语句中，配合order by使用更好
partition by:全局分区，按照字段值进行分区，相同字段名在一起 为一个区

使用情景：
distribute by+ sort by :各个reducer内部进行分区排序，相比下面更快
partition by + order by:全局分区排序

四、聚合函数

1. group by [having 条件]:分组
2. grouping sets((a,b),a,b,())
   
3. CUBE：对分组列进行所有可能组合的聚合
4. ROLLUP：计算维度层次级别上的聚合
   

五、窗口函数

扫描多个输入行来计算每个输出值，为每行数据生成一行结果
可以通过窗口函数来实现复杂的计算和聚合

• 排序
• 聚合
• 分析

1、排序
rank() over():不同分组排序不连续
dense_rank() over():不同分组排序连续
percent_rank() over():百分比
row_number() over():行号
NTILE(2)：分片

2、聚合
max() over()
min() over()
count() over()
sum() over()
avg() over()

3、分析
lead()
功能：用于取窗口内往下第n行值，以当前行为参照。
这个函数最多传入3个参数，第一个参数指定想要得到值的列，第二个参数（可选参数，默认为1）表示往下第几行，第三个参数（可选参数，默认为NULL）用于指定默认值。

lag()
功能：用于取窗口内往上第n行值，以当前行为参照。
这个函数最多传入3个参数，第一个参数指定想要得到值的列，第二个参数（可选参数，默认为1）表示往上第几行，第三个参数（可选参数，默认为NULL）用于指定默认值。

first_value()
功能：返回分组内排序后，截止到当前行的第一个值。
这个函数最多需两个参数，第一个参数指定想要得到值的列，第二个参数是个布尔值（可选参数，默认为false），如果设置true，跳过null值。

last_value()
功能：返回分组内排序后，截止到当前行的最后一个值。
这个函数最多需两个参数，第一个参数指定想要得到值的列，第二个参数是个布尔值（可选参数，默认为false），如果设置true，跳过null值。

窗口定义

RANK、NTILE、DENSE_RANK、CUME_DIST、PERCENT_RANK、LEAD、LAG和ROW_NUMBER函数不支持与窗口子句一起使用

• 行窗口定义
• 范围窗口定义

1、行窗口定义

SELECT
name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING) win2,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) win3,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) win4,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) win5,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) win6,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) win7,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) win8,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) win9,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) win10,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) win11,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS 2 PRECEDING) win12
FROM employee_contract  ORDER BY dept, name;

2、范围窗口定义

范围窗口是取分组内的值在指定范围区间内的行
该范围值/区间必须是数字或日期类型
目前只支持一个ORDER BY列

SUM(close) RANGE BETWEEN 500 PRECEDING AND 1000 FOLLOWING
-- 假设当前close值为3000，语句将包含分区内范围从2500到4000的行
--实例
SELECT name, dept_num AS dept, salary AS sal,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) win1,
salary - 1000 as sal_r_start,salary as sal_r_end,
MAX(salary) OVER (PARTITION BY dept_num ORDER BY name RANGE BETWEEN 1000 PRECEDING AND CURRENT ROW) win13
FROM employee_contract ORDER BY dept, name;