数仓工具hive概述

---

前言

数据仓库是为了解决大数据分析而产生的数据存储平台，简称数仓：DW（DataWarehouse），面向分析的集成化数据平台，分析结果给公司提供决策依据。

一、数据仓库

1.基本性质

1、不生产数据，即面向分析、非面向事务
2、不消费数据，即数据冗余存储，分析结果对外服务
3、不支持事务，需要先存在数据源，才能存在数仓，如：RDBMS。
4、不是大型数据库，是数据分析平台。

2.特性

1、面向主题性，以分析为目的，以业务主题为导向。
2、集成性，数据源种类多样，格式多样，字段表达方式多样，经ETL集成在数仓中。
3、不可更新性，面向分析，数据越多越好，冗余储存，数据之间规律不可更改。
4、时变性，批处理、流处理，需要持续维护，T+1、T+N模式

3.数仓分层

分层的意义：
基础：问题复杂，数据多
将数据清洗，留下高质量的数据
将数据拆解、汇总，提取有意义的维度、指标
面向分析，简化表关系，关联主题
明确过程，保留中间结果，更好的排错

一般分层结构：
1、ODS（操作型数据层、数据引入层、数据接入层）：将数据源的数据汇总储存至分布式存储系统中，保持原数据，分区优化，压缩存储等
2、DW（数据仓库）：ETL将数据反复转化，储存大量中间结果，可根据具体业务继续分层
3、DA（面向应用层）：最终消费DW数据的各种应用，即分析结果的实际应用体现，如可视化、暴漏接口

阿里数仓三层分层：
https://help.aliyun.com/document_detail/114447.html

分层的好处：
1、清晰数据结构：分层之后的数据结构更清晰，便于提取分析
2、数据血缘追踪：因为有分层，可根据分层找到源数据位置（冗余存储）
3、减少重复开发：分层保留了中间结果，出现问题不必从头再来
4、把复杂问题简单化：每一层只干一件事，细化问题
5、屏蔽原始数据的异常：异常数据在分层中会被剔除

二、HIVE

1.来源、特性

来源：
Hive是Facebook开源出来，后来贡献给力Apache
宗旨是：提高分析数据的能力降低分析数据的开发成本

特性：
基于hadoop的分布式文件储存
基于hadoop的资源调度
基于hadoop的分布式计算（MapReduce）
最大的功能：hive元数据（文件和表之间的关系）管理，将结构化数据映射成表结构，提供类sql查询功能，方便分析

2.hive两代客户端

第一代：hive
第二代：beeline
区别：
第一代不支持jdbc协议只启一个服务metastore
第二代支持jdbc协议启metastore、hiveserver2（! connect jdbc:hive2://localhost:10000）

3.hive表映射语法

row format delimited fields terminated by "\t";

支持的数据类型：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

4.hive序列化与反序列化

序列化机制：SerDe（Serializer and Deserializer）

读文件：
HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
1、使用InputFileFormat（默认实现TextInputFormat）读取hdfs上文件，按行读
2、返回<key, value>，其中数据存储在value中
3、使用Deserializer反序列化动作读取value 解析成为对象（Row object），默认为LazysimpleSerDe

写文件：
Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
1、将对象序列化成<key,value>
2、利用outputfileformat组件上传至hdfs

5.hive基础语法

基础语法格式：
row format delimited
[fields terminated by char] #指定字段分隔符
[collection items terminated by char] #指定集合元素之间的分隔符
[map keys terminated by char] #指定map类型kv之间的分隔符
[lines terminated by char] #指定换行符

默认分割符：\001 ACSII SOH

内部表、外部表（external）：
最大区别：外部表删除时不会删除HDFS上的数据。

分区表、分桶表：
分区表：
1、开启非严格模式、动态分区

set hive.exec.dynamic.partition.mode=nonstrict;

2、分区字段不能与表中字段相同
3、分区结果根据表字段distinct个数建对应数量的小文件
4、优化表，分区条件过滤更高效
5、PARTITION BY(date string)

分桶表:
1、开启分桶

set hive.enforce.bucketing=true;

2、分桶字段为表中字段
3、分桶数自己指定，数字类型%N=分桶结果，非数字类型.hashcode%N=分桶结果
4、优化表，减少join笛卡尔集数
5、CLUSTERED BY xxx INTO N BUCKETS

常用语法：
insert into xxx select * from xxx;
insert overwrite xxx select * from xxx;
desc formatted table;
show create table xxx;
desc database extended databasename;
show partitions;
show functions;

加载服务器文件至hive数据库
LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO
TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)];

从hive数据库导数据至hdfs：
insert overwrite directory ‘/tmp’ row format delimited fields terminated by ‘,’ stored as orc select * from table limit 2;

从hive数据库导出至服务器：
insert overwrite local directory ‘/root/’ select * from table limit 2;

hive客户端执行程序：
批处理：
./hive -e ‘select * from table’
./hive -f test.sql

位与操作: & A和B按位进行与操作的结果。 与表示两个都为1则结果为1
select 4 & 8 from dual; --4转换二进制：0100 8转换二进制：1000
select 6 & 4 from dual; --4转换二进制：0100 6转换二进制：0110

位或操作: | A和B按位进行或操作的结果 或表示有一个为1则结果为1
select 4 | 8 from dual;
select 6 | 4 from dual;

位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;

6.hive函数

UDF 、UDAF、UDTF：
UDF：普通函数，一进一出，spilt
UDAF：聚合函数，多进一出，sum
UDTF：表生成函数，一进多出，explode

正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace(‘100-200’, ‘(\d+)’, ‘num’); --正则分组

正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容
select regexp_extract(‘100-200’, ‘(\d+)-(\d+)’, 2);

URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数

json解析函数：get_json_object(json_txt, path)
,$表示json对象

--获取当前日期: current_date
select current_date();
--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1620723323);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);

条件函数

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);

--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");

--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

强制转换：

--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);

脱敏函数、加密函数：

--mask
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定义替换的字母

--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);

--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");

--取哈希值函数:hash
select hash("allen");

--current_user()、logged_in_user()、current_database()、version()

--SHA-1加密: sha1(string/binary)
select sha1("allen");

--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);

--crc32加密:
select crc32("allen");

--MD5加密: md5(string/binary)
select md5("allen");

7.hive高阶函数

1、表生成函数explode

功能：将array或者map集合中的数据“炸开”，生成多行数据
基本用法：

select explode(`array`(11,22,33,44,55));
select explode(`map`("id",10086,"name","allen","age",18));	

explode属于UDTF表生成函数，其结果为多行，不能直接在select字句中出现，需要借助侧视图join
侧视图的原理：
将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表

--lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;

因为explode只能接收array和map类型，可用collect_list()和collect_tuple()收集字段中的数据，利用concat_ws()将结果拼接。

2、行列转换

--多行转多列
select
    col1 as col1,
    max(case col2 when 'c' then col3 else 0 end) as c,
    max(case col2 when 'd' then col3 else 0 end) as d,
    max(case col2 when 'e' then col3 else 0 end) as e
from row2col1
group by col1;

--多列转单行
select 
	col1,
	col2,
	concat_ws("-",collect_list(cast(col3 as string))) 
from row2col2
group by col1,col2;

--多列转多行union去重
select 'b','a','c'
union
select 'a','b','c'
union
select 'a','b','c';

--单列转多行
select
    col1,
    col2,
    lv.col3 as col3
from col2row2
lateral view explode(split(col3, ',')) lv as col3;

3、join解析函数

--get_json_object用于解析单个对象
	--获取设备名称
    get_json_object(json,"$.device") as device,
    --获取设备类型
    get_json_object(json,"$.deviceType") as deviceType,
    --获取设备信号强度
    get_json_object(json,"$.signal") as signal,
    --获取时间
    get_json_object(json,"$.time") as stime

--json_tuple用于解析全部对象
json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
--可配合侧视图lateral view使用
--使用JsonSerDe类解析，在加载json数据到表中的时候完成解析动作

4、窗口函数

用法与mysql大致相同，需要注意的是：
有PARTITION BY 没有PARTITION BY的区别；有ORDER BY没有ORDER BY的区别：
1、有没有partition by 影响的是全局聚合 还是分组之后 每个组内聚合
2、没有order by，默认是rows between，首行到最后行，这里的"行"是物理上的行；
3、有order by，默认是range between，首行到当前行，这里的"行"是逻辑上的行，由字段的值的区间range来划分范围。
可用自定义窗口。

--语法
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)

(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

三、HIVE通用调优

1、orc存储和snappy压缩

hive默认存储为textfile按行读取，不利于分析，且文件大小未改变。可利用orc存储，按列读取，利于分析，减少文件空间。
stored as ‘orc’ tblproperties(“orc.compress”=“SNAPPY”)
snappy为综合使用较好的压缩算法。hive默认使用ZLIB
本质上，还是mapreduce的压缩，所以该慢的还慢，一些参数：

--设置Hive的中间压缩 也就是map的输出压缩
1）开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
2）开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;
3）设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;

--设置Hive的最终输出压缩，也就是Reduce输出压缩
1）开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;
2）开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
3）设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
4）设置 mapreduce 最终数据输出压缩为块压缩  还可以指定RECORD
set mapreduce.output.fileoutputformat.compress.type=BLOCK;  

2、fetch抓取与MR本地模式

1、fetch不走mr，直接读取hdfs文件
2、mr本地，不走分布式，不让yarn分配调度任务
set hive.exec.mode.local.auto = true;
开启后尝试本地，不行就走yarn

关键点：计算引擎的问题，追求速度，不用mr，用tez，spark

3、join优化

1、mapjoin，小表join大表，小表join小表，实现方式就是小表全部数据进内存缓存中，和大表切片join。
2、reducejoin，适用于大表join大表
3、join之前先过滤，大表转小表；利用分区裁剪
4、bucketjoin，适用大表join大表
分桶字段和join相同Bucktet Map Join，分桶的个数相等或者成倍数。
SMB分桶之后排序，分桶字段 = Join字段 = 排序字段,分桶的个数相等或者成倍数
核心是在maptask阶段完成join

3、数据倾斜优化

出现原因：
group by导致数据倾斜
join导致数据倾斜

优化：
group by：
1、开启Map端聚合，先做一部分
2、实现随机分区select * from table distribute by rand();
3、自动负载均衡
两阶段，一阶段，每个reducer随机拉取做部分聚合
第二个MapReduce将上一步聚合的结果再按照业务（group by key）进行处理，保证相同的分布到一起，最终聚合得到结果

join数据倾斜：
1、提前过滤，将大数据变成小数据，实现Map Join
2、使用Bucket Join
3、使用Skew Join
将Map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，就会将产生数据倾斜的数据单独使用Map Join来实现
其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题
最终将Map Join的结果和Reduce Join的结果进行Union合并

4、其他优化

1、MR程序task个数调整
maptask个数：
调整maptask和reducetask任务数。maptask通过调整逻辑切片大小、文件数量。小文件合并、block size。

reducetask个数：
（1）每个 Reduce 处理的数据量默认是 256MB
hive.exec.reducers.bytes.per.reducer=256000000
（2）每个任务最大的 reduce 数，默认为 1009
hive.exec.reducsers.max=1009
（3）mapreduce.job.reduces
该值默认为-1，由 hive 自己根据任务情况进行判断。不适用order by全局排序

2、执行计划
explain + sql语句，查看hive如何执行，适当调优

3、并行机制、推测执行机制
并行机制：
如果hivesql的底层某些stage阶段可以并行执行，就可以提高执行效率。
前提是stage之间没有依赖 并行的弊端是瞬时服务器压力变大。

推测执行机制：
一个job底层可能有多个task执行，如果某些拖后腿的task执行慢，可能会导致最终job失败。
所谓的推测执行机制就是通过算法找出拖后腿的task,为其启动备份的task。
两个task同时处理一份数据，谁先处理完，谁的结果作为最终结果。

总结

MR优点稳定，但也导致了繁琐的shuffle过程。hive提高了分析效率，可以直接用类sql完成数据的分析，但没有自己的计算引擎、资源调度，过分的依赖导致运行速率不可直视。
时光如水，人生逆旅矣。