1. 概述
总结一些自己不常用但必须知道的hive功能
2. 导出数据
- INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] SELECT ... FROM ...
- export table 表名 to 'hdfs目录';
- import table [partition(字段='值')] from 'hdfs目录';
3. 排序
| 操作 | 描述 | 举例 |
|---|---|---|
| order by | 全局排序，只有一个reduce | |
| sort by | 分区内排序，对排序字段进行随机分区以使数据均匀，reduce多个 | |
| distribute by | 搭配sort by使用，分区内排序，对记录按照distribute by的字段进行hash分区，reduce多个 | select * from test dristribute by row_key sort by entname desc; |
| cluster by | 当distribute by与sort by字段相同时，可以使用cluster by来代替，cluster by不支持desc、asc，默认就是asc | select * from test cluster by row_key; |
4. 分桶
一个分区代表一个目录，分区针对的是目录，而分桶针对的是数据文件，同一个数据文件中的分区字段的hash值%桶数相同
set hive.enforce.bucketing = true;
create table bucketed_t (id string, name string) clustered by (id) into 256 buckets; -- 以字段id分桶
insert overwrite table bucketed_t select id, name from other_table;
多用来采样，语法是tablesample (bucket x out of y [on column]),其中x表示从第几个bucket开始，y表示间隔多少个bucket在取样及采样的比例：
比如总bucket数为32，x为2，y为16，返回的数据量为32/16个桶，返回第2、18个桶的数据
比如总bucket数为32，x为2，y为64，返回的数据量为32/64个桶，返回第2个桶的1/2数据
还可以对bucket数成倍数关系，分桶字段类型相同的表执行mapjoin
5. skewed table
语法：
create table t (key string, value string) skewed by key on (1,5,6) [stored as directories]，添加stored as directories则开启list bucketing功能，将key为1，5，6的数据会分别放到不同的子目录中
这个表是用来优化join时少量关联key数据倾斜的，假设有2张表A、B，如果A表的key在值1、5、6时数据倾斜，B的各个key的数据量不大(即单独key的数据可以放入内存)。为了避免在key为1、5、6时数据倾斜，常规解决方式：
- 将A表的数据分为4部分，第一部分为key为1的数据，第二部分为key为5的数据，第三部分为key为6的数据，其余的数据为第四部分
- B表同样处理
- 将第四部分分发到reducer中进行join，将其余的三部分分为3个map task，B的数据加载到内存，与A的对应部分数据进行map join
这样能解决数据倾斜的问题，但是需要多次数据读取，为了只读取一次，引入了skewed table，skewed table让hive知道哪些key需要mapjoin，list bucketing功能则已经把数据进行划分，只需要读取一遍数据
注意的是，这个功能只针对少量key数据倾斜，因为每个key对应的目录，会作为元数据添加到metastore
6. 窗口
聚合函数是多对1，而窗口还是多对多，行数不变，且也能获取聚合值。注意窗口是针对一条数据而言的
6.1 开窗
开窗的关键字:OVER
6.2 窗口规格(Window Specification)
4部分组成：
- Partition Specification：over (partition by)
- Order Specification：over (sort by)
- Window Frame:
3.1 (ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
3.2 (ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
3.3 (ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
语法OVER(window frame)，以当前行为原点，n PRECEDING表示前n行，n FOLLOWING表示后n行，UNBOUNDED表示无穷大，UNBOUNDED PRECEDING表示窗口起点，UNBOUNDED FOLLOWING表示窗口终点，。RANGE表示有Order Specification，否则为ROWS
- Window的别名
举例：
over()：相当于over(ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),窗口大小为整个数据集，一个窗口，对窗口内的每一条数据，能看到整个窗口。over (partition by 字段1)：相当于over(partition by 字段1 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)，以字段1分组后的数据集，一个组一个窗口，对窗口内的每一条数据，能看到整个窗口。over (order by 字段1)：相当于over(order by 字段1 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，窗口大小为整个数据集，一个窗口，对窗口内的每一条数据，能看到第一行到自己。
6.3 窗口函数(windowing functions)
笔者习惯称为错位函数，这些函数需要与窗口一起使用
LEAD：LEAD(列名, n, [为null时的替换值])，在窗口内，以当前行为原点，后第n行指定列的值，如果第n行没有则用null代替，指定了第3个参数，使用第三个参数替代。注意只是一个值
LAG：LAG(列名, n, [为null时的替换值])，在窗口内，以当前行为原点，前第n行指定列的值，如果第n行没有则用null代替，指定了第3个参数，使用第三个参数替代。注意只是一个值
FIRST_VALUE：FIRST_VALUE(列名, [布尔值，默认false])，在窗口内，能看到的第一行指定列的值，false表示不忽略null值
LAST_VALUE：LAST_VALUE(列名, [布尔值，默认false])，在窗口内，能看到的最后一行指定列的值，false表示不忽略null值
6.4 聚合函数
SUM、AVG、COUNT、MAX、MIN、COUNT(DISTINCT)
6.5 序号函数(Analytics functions)
- RANK：遇到重复的字段值，序号不变，但是序号不连续，从1开始
- ROW_NUMBER：不管重复，序号从1开始
- DENSE_RANK：遇到重复的字段值，序号不变，但是序号连续，从1开始
- CUME_DIST：小于等于当前值的行数/分组内总行数
- PERCENT_RANK：RANK的拓展，(当前行的RANK值-1)/(分组内总行数-1)
- NTILE：均匀进行分组
7. 自定义函数
UDF：一条进一条出
UDTF：全称user-defined table-generating functions，一条进多条输出
UDAF: 多条进一条输出
8. 动态分区
9. ANALYZE
ANALYZE TABLE [db_name.]tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)]
COMPUTE STATISTICS
[FOR COLUMNS] -- (Note: Hive 0.10.0 and later.)
[CACHE METADATA] -- (Note: Hive 2.1.0 and later.)
[NOSCAN];
用来统计表的信息。由于spark-sql生成的表没有表的统计信息，当需要在spark-sql中broadastHashJoin或者在hive中mapJoin时，读取不到rawDataSize信息而不能启用，这时候可以使用该命令来获取表的统计信息
笔者遇到上述情况就使用
ANALYZE TABLE [db_name.]tablenameANALYZE TABLE [db_name.]tablename COMPUTE STATISTICS NOSCAN;
