Hive初识

jiezi

5 年前

Hive 是一个 SQL 解析引擎，将 SQL 语句转译成 MRJob，然后再在 Hadoop 平台上运行，达到快速开发的目的。
Hive 中的表，是纯逻辑表，它本身不存储数据，只存储表的定义即表的元数据信息。本质就是 Hadoop 的目录 / 文件，因此达到元数据和数据存储相分离的目的。
Hive 由于本身不存储数据，因此完全依赖 HDFS 和 MR。
Hive 不支持数据的修改和删除。
Hive 中的数据格式由用户指定，需要指定三个参数：
- 列分隔符：空格 (” “)、逗号 (“,”)、制表符 (“t”)
- 行分隔符：换行符 (“n”)
- 读取文件数据的方法

对于存储在 HDFS 上的文件或 Hbase 上的表进行查询时，使用单纯的 MR 需要写大量 MapReduce 代码
对于一些统计任务，只能由懂 MapReduce 的人才能完成
任务繁重，效率低

语言	HQL	SQL
数据存储	HDFS Hbase	Local FS
数据格式	用户自定义	由系统决定
数据更新	不支持（会覆盖）	支持
索引	有（0.8 版后新增）	有
执行	MR	Executor
执行延迟	高	低
可扩展性	高（UDF、UDAF、UDTF）	低
数据规模	大（数据大于 TB）	小
数据检查	读时模式	写时模式

// 建内部表
create table inner_test (name String) 
row format delimited fields terminated by '\n';
// 导入数据
load data local inpath 'local/path' into table inner_test;

// 建外部表
create external table external_test (name String) 
row format delimited fields terminated by '\n'
-- stored as textfile
location 'local/path';// 必须是文件

内部表被 drop 后，Hive、hdfs 中的元数据和文件数据都会同时被删除。即：内部表是由 Hive 自己管理的
外部表被 drop 后，Hive 中的元数据信息会被删除，但是，指定的外部文件的文件数据还在。即：外部表不是 Hive 管理的。

Hive 中，分区表的一个 partition 对应于表下的一个目录，所有 partition 数据都存储在对应的目录中
- 如：表 tablea 中有两个分区 p1 和 p2，则：
```
  对应的 p1='aaa',p2='111' 的目录为：xxx/tablea/p1=aaa/p2=111;
  对应的 p1='aaa',p2='222' 的目录为：xxx/tablea/p1=aaa/p2=222;
```
partition 可以辅助查询，作为查询条件，加快查询速度
SQL 语句：

// 建分区表，按 dt 分区
create table partition_test (name String) 
partitioned by (dt String) 
row format delimited fields terminated by '\n';
// 插入数据
insert overwrite table partition_test 
partition (dt='xxx')
select * from partition_from
limit 1000;
// 查看分区表分区信息
show partitions partition_test;
// 一般情况下，分区表数据由定时任务插入

Hive 中，table 可以分成 partition，而 table 和 partition 可以进一步分桶 bucket，使用 ‘clustered by’，在桶内的数据可以使用 ‘sort by’ 排序。（order by 可以吗？？？todo）

分桶 SQL：

// 建分桶表
create table bucket_test (num int)
clustered by (num) into 32 buckets;

分桶主要作用：
- 进行数据采样
- 提升某些查询操作的效率，例如: mapside join
设置参数：set hive.enforce.bucketing = true; 可以自动控制上一轮 reduce 的数量从而适配 bucket 个数。同时，也可以自主设置 mapred.reduce.tasks 去适配 bucket 个数。

语法：tablesample(bucket x out of y on num)
假设：共 num 个桶，则：从第 x 个桶开始，取 num/y 个桶的数据

SQL

select * from bucket_test
tablesample (bucket 1 out of 32 on 32) ;

我们假设分桶表 bucket_test 里的数据是 ‘1,2,3,4,5,6,7…32’，那么，执行上面 sql 后会返回什么呢？
运用采样公式，得知：共有 32 个桶，会从第一个桶开始取数据，共取 32/32=1 个数据。而桶的下标是从 0 开始的，则得出采样结果：32（第一个桶（下标为 0）内的数据是 32）。
我们换一下采样 sql，改为：tablesample(bucket 1 out of 16 on 32); 结果会是什么？
同样，利用采样公式得知：共有 32 个桶，会从第一个桶开始取数据，共取 32/16=2 个数据。一个桶内只有一个数据，那么，另一个数据取哪个桶里的呢？
我们可以将桶分为两部分，每部分 16 个，所以，取每部分的第一个桶，对应到 32 个桶上，就是第一个和第十七个，即：32、16。

没有分桶的表，如何采样 90% 的数据？

select * from tablename where num % 10 > 0;
// 如果是字符串的话，需要先 hash 转成数字。

设置参数：
- set hive.exec.dynamic.partition = true; // 使用动态分区
- set hive.exec.dynamic.partition.mode = nonstrict; // 使用无限制模式

split：切分。如：”aaa_bbb”.split(“_”) 按照下划线切分。
explode：将列数据按行输出。
regexp_extract：正则匹配。如：regexp_extract(‘string’, ‘[[\w]]+’, 0)
ceil：向上取整
collect_list：聚合。如：collect_list(column) -> [‘1′,’2′,’3’,….]
concat_ws：拼接。如：concat_ws(‘_’, column1, column2) as t
row_number：行号，连续值。（1,2,3,4,5,….）
rank：排序相同时，会重复，总数不变。（1,1,3,4,5,5,5,8,…）
dense_rank：排序相同时，会重复，总数减少。（1,1,2,2,2,3,4,5,5,6…）

sort by：在同一个 reduce 中进行排序
order by：全局排序，一般只有一个 reduce

partition by xx order by xx：partition by 和 order by 组合
distribute by xx sort by xx：distribute by 和 sort by 组合

partition by：需要搭配 over 使用，原来数据有多少条 user_id，结果还有多少条
```
select user_id, count(order_id) over(partition by user_id)
from orders;
```
group by：由于聚合，最终一个 user_id 只有一条数据
```
select user_id, count(order_id) 
from orders
group by user_id;
```

order by 占用一个 mapreduce
打印表头：set hive.cli.print.header=true;
如果设置参数不生效，要知道，设置的参数也有优先级
常用的分区字段有：①日期 ②客户端类型（pc、m、app）

Hive初识

Hive

Hive 是什么

为什么用 Hive

Hive SQL 和传统的 SQL 对比

Hive 体系架构

Hive 建表

内部表

外部表

内部表和外部表的区别

分区表

分桶

分桶采样

动态插入分区表

Hive 常用函数

sort by 和 order by

partition by 和 distribute by

partition by 和 group by 的使用场景

tip 点