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';//必须是文件

Hive中默认的记录和字段分隔符

分隔符：\n。行分隔符。
分隔符：^A。列分隔符。八进制编码\0001。
分隔符：^B。ARRAY 和 STRUCT 分隔符。八进制编码\0002。
分隔符：^C。MAP 中键和值间的分隔符。八进制编码\0003。

row format delimited
行分隔符：lines terminated by '\n'
列分隔符：fields terminated by '\0001'
集合分隔符：collection items terminated by '\0002'
MAP分隔符：map keys terminated by '\0003'
行分隔符目前只支持'\n'

内部表和外部表的区别

• 内部表被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 组合
• distribute by 控制map的输出在reducer是如何划分的，将相同的记录分发到同一个reducer中。
• sort by控制数据在reducer中排序。

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;

动态分区

insert overwrite table tb1
partition(a,b)
select ...,c,d
from tb2;

根据select语句最后两列 c、d 确定 a、b 的值。即根据位置而不是根据命名来匹配的。

动静态分区混合。
insert overwrite table tb1
partition(a='1',b)
select ...,c,d
from tb2
where e='xx';
静态分区键必须在动态分区键之前。
动态分区默认没开启。开启后，默认以“严格”模式执行，要求至少有一列分区字段是静态的。

连接

指定大表： /+streamtable(t) /

指定小表： /+mapjoin(t) /
Hive 0.7版本之前使用。之后也有效。
Hive 0.7版本之后，设置属性 hive.auto.convert.JOIN=true;
hive.mapjoin.smalltable.filesize=25000000; 字节

左半开连接： left semi join 。 select和where不能用右边表的字段。没有右半开连接。左半开连接比inner join更高效：左表中的一条记录一旦在右表中找到匹配的记录，立即停止扫描。

浮点数比较

规避浮点数比较的方法：

1. 表定义浮点数为Double，不用Float。
2. 显式指定比较的数字的类型。 where field > cast(0.2 as float);

命令语句

location：修改数据库默认位置。

• create database db1 location 'a/b/c';

comment：数据库描述信息。

• create database db1 comment 'db1 comment';

extended：和数据库相关的键值对属性信息。

• create database db1 with dbproperties('creator'='author', 'date'='2020-05-01');
• describe database extended db1;

restrict：删除数据库时，如果库中有表，则会删除失败。和默认情况一样。

• drop database if exists db1 restrict;

cascade：删除数据库时加上此关键字会自行删除数据库中的表。

• drop database if exists db1 cascade;

tblproperties：表属性信息。

• create table db1.tb1(...) comment 'xxx' tblproperties('creator'='me', 'created_at'='2020-05-01', ...) location '/path/to/table/xxx';

describe：只查看某一个列的信息。

• describe db1.tb1.field1; // 表名后加字段名

show partitions：展示表中的分区。

• show partitions db1 partition(field1='a');

serde：自定义表存储格式。

• create table tb1
• partitioned by (ds string)
• row format serde 'com.linkedin.haivvreo.AvroSerDe'
• with serdeproperties ('schema.url'='http://schema_provider/db1.avsc') // 设置配置信息
• stored as // 可指定输入输出格式，若指定，就要都指定
• inputformat 'com.linkedin.haivvreo.AvroContainerInputFormat' // 输入格式
• outputformat 'com.linkedin.haivvreo.AvroContainerOutputFormat'; // 输出格式

rename to：表重命名。

• alter table tb1 rename to tb2;

add partition：增加分区。

• hive 0.8版本后，一个查询可以同时增加多个分区。
• hive 0.7版本也允许指定多个分区，但是只有第一个生效。可以对每个分区都使用 alter statement语句。
• alter table tb1
• add if not exists
• partition(xxx) location 'xxx'
• partition(xxx) location 'xxx';

set location：修改分区。

• alter table tb1 partition(xxx)
• set location 'xxxx';

drop partitions：删除分区。

• alter table tb1 drop if exists partition(xxx);

change column：修改列信息。

• alter table tb1
• change column oldfield newfield int
• comment 'xxx'
• after field; // first 放到第一列

replace columns：删除或替换列。

• alter table tb1
• replace columns(a string comment 'xxx', b long comment 'xxx') ;
• 该语句实际上，语句需要和原来的表对比，语句中没有的列会被删除，有的列会修改。
• replace语句只能用在使用了2种内置SerDe模块的表：DynamicSerDe和MetadataTypedColumnsetSerDe。

set tblpropertties：修改表属性。

• 可增加、修改、无法删除属性。
• alter table tb1
• set tblproperties('author'='xxx');

set fileformat：修改存储格式。

• alter table tb1
• partition(xxx) // 如果是分区表，就加上
• set fileformat sequencefile;

set serde with serdeproperties：设置serde属性。

• alter table tb1
• set serde 'com.example.JSONSerDe'
• with serdeproperties('prop1'='xx','prop2'='xx');

set serdeproperties：新增serde属性。

• alter table tb1
• set serdeproperties('prop3'='xxx');

touch：钩子。

• TODO 还不太懂
• alter table tb1 touch partition(xxx);

archive：将分区内文件压缩达成一个har压缩文件。

• unarchive是反向操作。他们只能用于分区表独立的分区。
• alter table tb1
• archive partition(xxx);

enable：Hive保护机制。

• 防止被删除：enable no_drop;
• 防止被查询：enable offline;
• enable 和 disable 反向。
• 只可用于分区表。
• alter table tb1
• partition(xxx) enable no_drop;

其他tip点

• order by 占用一个 mapreduce
• 打印表头： set hive.cli.print.header=true;
• 如果设置参数不生效，要知道，设置的参数也有优先级
• 常用的分区字段有：①日期 ②客户端类型（pc、m、app）
• 提高聚合性能：hive.map.aggr=true;
• count(distinct col):当col是分区列时，结果为0，这个是bug；
• 当使用表生成函数时，要使用别名；
• 不能再where语句使用别名；
• 抽样百分比：tablesample(0.1 percent)。hive.sample.seednumber=0。
• 分桶表优化： hive.enforce.bucketing=true; 抽样只扫描设计到的表的哈斯分区下的数据。
• 表的索引数据存在于另一张表中。
• 限制调整：hive.limit.optimize.enable=true; 还有两个参数可以控制：hive.limit.row.max.size=100000; hive.limit.optimize.limit.file=10;
• 并行执行：hive.exec.parallel=true;
• JVM重用：mapred.job.reuse.jvm.num.tasks=10;
• bitmap索引：在指定的列排重后值较小时使用；加快group by查询计算速度；
• 设置动态分区为严格模式：hive.exec.dynamic.partition.mode=strict;
• 限制查询可以创建的最大动态分区个数：hive.exec.max.dynamic.pritition=300000;
• datanode一次可以打开文件个数：dfs.datanode.max.xcievers=8192。