Hive初识

Hive

Hive 是什么

• Hive 是一个 SQL 解析引擎，将 SQL 语句转译成 MRJob，然后再在 Hadoop 平台上运行，达到快速开发的目的。
• Hive 中的表，是纯逻辑表，它本身不存储数据，只存储表的定义即表的元数据信息。本质就是 Hadoop 的目录 / 文件，因此达到元数据和数据存储相分离的目的。
• Hive 由于本身不存储数据，因此完全依赖 HDFS 和 MR。
• Hive 不支持数据的修改和删除。

• Hive 中的数据格式由用户指定，需要指定三个参数：

  • 列分隔符：空格 (” “)、逗号 (“,”)、制表符 (“t”)
  • 行分隔符：换行符 (“n”)
  • 读取文件数据的方法

为什么用 Hive

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

Hive SQL 和 传统的 SQL 对比

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

Hive 体系架构

Hive 建表

内部表

// 建内部表
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; // 使用无限制模式

Hive 常用函数

• 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 和 order by

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

partition by 和 distribute by

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

partition by 和 group 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;

tip 点

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