6——Hive数仓项目完整流程（在线教育）（Hive理论总结1）

在线教育(6)——Hive理论

1. Hive 分区

1.1 分区理论

1.由于Hive通常用来存储大型数据集，在检索过程中，可以根据特定的分区来检索，减少扫描成本。在一定程度上提高查询效率。
2.分区是一种根据“分区列”（partition column）的值对表进行粗略划分的机制。Hive中每个分区对应着表很多的子目录，将所有的数据按照分区列放入到不同的子目录中去。

1.在建立分区时，数据会依照单个或多个列进行分区，通常按照时间、地域或者是商业维度进行分区。比如电影表，分区的依据可以是电影的种类和评级，另外，按照拍摄时间划分可能会得到均匀的结果。
2.为了达到性能表现的一致性，对不同列的划分应该让数据尽可能均匀分布。最好的情况下，分区的划分条件总是能够对应where语句的部分查询条件，这样才能充分利用分区带来的性能优势。

1.2 分类

• 静态分区

静态分区：插入时需要手动指定分区。
在创建静态分区时，
		直接在 PARTITIONED BY 后面跟上分区键、类型即可。（分区键不能和任何列重名）

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type> [, <col_name> <data_type> ...])
    -- 指定分区键和数据类型
    PARTITIONED BY  (<partition_key> <data_type>, ...) 
    [ROW FORMAT <row_format>] 
    [STORED AS TEXTFILE|ORC|CSVFILE]
    [LOCATION '<file_path>']    
[TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

--分区字段主要是时间，按年分区
CREATE TABLE device_open (
deviceid varchar(50),
...
)
PARTITIONED BY (year varchar(50))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
-- 追加写
insert overwrite table device_open partition(year=’2020’)
select
	...,
	original_device_open.month as month,
	original_device_open.day as day,
	original_device_open.hour as hour
FROM original_device_open

• 动态分区

动态分区：插入时需要手动指定分区。
在创建动态分区时，
		直接在 PARTITIONED BY 后面跟上分区键、类型即可。（分区键不能和任何列重名），创建方式与静态分区表完全一样。
		动态分区只需要给出分区键名称。

--分区字段主要是时间，分为年，月，日，时
CREATE TABLE device_open (
deviceid varchar(50),
...
)
PARTITIONED BY (year varchar(50), month varchar(50), day varchar(50), hour varchar(50))
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 开启动态分区支持，并开启非严格模式
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

insert overwrite table device_open partition(year,month,day,hour)
select
	...,
	original_device_open.year as year,
	original_device_open.month as month,
	original_device_open.day as day,
	original_device_open.hour as hour
FROM original_device_open

set hive.exec.dynamic.partition=true; 是开启动态分区
set hive.exec.dynamic.partition.mode=nonstrict; 这个属性默认值是strict，就是要求分区字段必须有一个是静态的分区值。全部动态分区插入，需要设置为nonstrict非严格模式。
partition(year,month,day,hour) 就是要动态插入的分区。对于大批量数据的插入分区，动态分区相当方便

• 静态分区、动态分析混用

一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区键的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。
静态分区键要用 <spk>=<value> 指定分区值；动态分区只需要给出分区键名称 <dpk>。
spk 即静态分区static partition key， dpk 即动态分区dynamic partition key。

insert overwrite table device_open partition(year='2017',month='05',day,hour)
select
	...,
	original_device_open.day as day,
	original_device_open.hour as hour
FROM original_device_open 
where original_device_open.year='2017' and original_device_open.month='05'
partition(year='2017', month='05', day, hour)

year和month是静态分区字段，day和hour是动态分区字段，这里指将2017年5月份的数据插入分区表，对应底层的物理操作就是将2017年5月份的数据load到hdfs上对应2017年5月份下的所有day和hour目录中去。
注意混用的情况下，静态分区的上层必须也是静态分区，如果partition(year, month, day=’05’, hour=’08’)，则会报错：FAILED: SemanticException [Error 10094]: Line 1:50 Dynamic partition cannot be the parent of a static partition ''day''。

注意，如果个人电脑性能不好，出现因为动态分区而导致的内存溢出问题，可以设置hive.optimize.sort.dynamic.partition进行避免：

设置为true后，当启用动态分区时，reducer仅随时保持一个记录写入程序，从而降低对 reducer产生的内存压力。但同时也会使查询性能变慢。

动态分区其他相关属性设置：

2. Hive 数据格式

2.1 列式存储和行式存储

• 列式存储

列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

• 行式存储

行存储的特点： 查询满足条件的一整行（所有列）数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

2.2 TEXTFILE

默认格式，行式存储。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。并且反序列化过程中，必须逐个字符判断是不是分隔符和行结束符，性能较差。

2.3 ORCFILE

使用ORC文件格式可以提高hive读、写和处理数据的能力。ORCFile是RCFile的升级版。
在ORC格式的hive表中，数据按行分块，每块按列存储。结合了行存储和列存储的优点。记录首先会被横向的切分为多个stripes，然后在每一个stripe内数据以列为单位进行存储，所有列的内容都保存在同一个文件中。
每个stripe的默认大小为256MB，相对于RCFile每个4MB的stripe而言，更大的stripe使ORC可以支持索引，数据读取更加高效。

3. Hive 存储压缩格式

3.1 zlib压缩

优点：压缩率比较高；hadoop本身支持，在应用中处理gzip格式的文件就和直接处理文本一样。
缺点：压缩性能一般。

3.2 snappy压缩

优点：高速压缩速度和合理的压缩率。
缺点：压缩率比zlib要低；hadoop本身不支持，需要安装（CDH版本已自动支持，可忽略）。

3.3 系统推荐

因为ORCFILE的压缩快、存取快，而且拥有特有的查询优化机制，所以系统采用ORCFILE存储格式（RCFILE升级版），压缩算法采用orc支持的ZLIB和SNAPPY。
在ODS数据源层，因为数据量较大，可以采用orcfile+ZLIB的方式，以节省磁盘空间；
而在计算的过程中（DWD、DWM、DWS、APP），为了不影响执行的速度，可以浪费一点磁盘空间，采用orcfile+SNAPPY的方式，提升hive的执行速度。
存储空间足够的情况下，推荐采用SNAPPY压缩。

4. Hive 分桶表

4.1 分桶表作用

理论:分桶是将数据集分解成更容易管理的若干部分的一个技术，是比分区更为细粒度的数据范围划分。
作用:在分桶之后，可以获得更高的查询效率，并且可以对数据进行采样
查询处理效率:
	在分区数量过于庞大以至于可能导致文件系统崩溃时，或数据集找不到合理的分区字段时，我们就需要使用分桶来解决问题了。
分区中的数据可以被进一步拆分成桶，不同于分区对列直接进行拆分，桶往往使用列的哈希值对数据打散，并分发到各个不同的桶中从而完成数据的分桶过程。
	注意:hive使用对分桶所用的值进行hash，并用hash结果除以桶的个数做取余运算的方式来分桶，保证了每个桶中都有数据，但每个桶中的数据条数不一定相等。
	如果另外一个表也按照同样的规则分成了一个个小文件。两个表join的时候，就不必要扫描整个表，只需要匹配相同分桶的数据即可，从而提升效率。
在数据量足够大的情况下，分桶比分区有更高的查询效率。
数据采样:
	在真实的大数据分析过程中，由于数据量较大，开发和自测的过程比较慢，严重影响系统的开发进度。此时就可以使用分桶来进行数据采样。采样使用的是一个具有代表性的查询结果而不是全部结果，通过对采样数据的分析，来达到快速开发和自测的目的，节省大量的研发成本。

4.2 分桶与分区的异同

1. 分桶对数据的处理比分区更加细粒度化：分区针对的是数据的存储路径；分桶针对的是数据文件；
2. 分桶是按照列的哈希函数进行分割的，相对比较平均；而分区是按照列的值来进行分割的，容易造成数据倾斜；
3. 分桶和分区两者不干扰，可以把分区表进一步分桶。

4.3 分桶语法

• 创建分桶

create table test_buck(id int, name string)
clustered by(id) sorted by (id asc) into 6 buckets
row format delimited fields terminated by '\t';
-- CLUSTERED BY来指定划分桶所用列；
-- SORTED BY对桶中的一个或多个列进行排序；
-- into 6 buckets指定划分桶的个数。
-- 分桶规则：HIVE对key的hash值除bucket个数取余数，保证数据均匀随机分布在所有bucket里。

• 查看分桶表信息

• 插入数据

--启用桶表
set hive.enforce.bucketing=true;
insert into table test_buck select id, name from temp_buck;

hive.enforce.bucketing：启用桶表，数据分桶是否被强制执行，默认false，如果开启，则写入table数据时会启动分桶。

4.4 文本数据处理

注意：对于分桶表，不能使用load data的方式进行数据插入操作，因为load data导入的数据不会有分桶结构。

• 如何避免针对桶表使用load data插入数据的误操作呢？

--限制对桶表进行load操作
set hive.strict.checks.bucketing = true;

​ 也可以在CM的hive配置项中修改此配置，当针对桶表执行load data操作时会报错。

• 那么对于文本数据如何处理呢？

1. 先创建临时表，通过load data将txt文本导入临时表。

--创建临时表
create table temp_buck(id int, name string)
row format delimited fields terminated by '\t';
--导入数据
load data local inpath '/tools/test_buck.txt' into table temp_buck;

2. 使用insert select语句间接的把数据从临时表导入到分桶表。

--启用桶表
set hive.enforce.bucketing=true;
--限制对桶表进行load操作
set hive.strict.checks.bucketing = true;
--insert select
insert into table test_buck select id, name from temp_buck;

–分桶成功

4.5 数据采样

对表分桶一般有两个目的，提高数据查询效率、抽样调查。通过前面的讲解，我们已经可以对分桶表进行正常的创建并导入数据了。

一般在实际生产中，对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果，比如在开发自测的时候。这个时候Hive就可以通过对表进行抽样来满足这个需求。

  select * from  table tablesample(bucket x out of y on column)  

hive根据y的大小，决定抽样的比例。y必须是table总bucket数的倍数或者因子。

例如，table总共分了10份bucket，当y=2时，抽取(10/2=)5个bucket的数据，当y=10时，抽取(10/10=)1个bucket的数据。

x表示从哪个bucket开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。

例如，table总bucket数为6，tablesample(bucket 1 out of 2)，表示总共抽取（6/2=）3个bucket的数据，从第1个bucket开始，抽取第1(x)个和第3(x+y)个和第5(x+y)个bucket的数据。

注意：x的值必须小于等于y的值。否则会抛出异常：FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck。

select * from test_buck  tablesample(bucket 1 out of 10 on id); 

注意：sqoop不支持分桶表，如果需要从sqoop导入数据到分桶表，可以通过中间临时表进行过度。ODS也可以不做分桶，从DWD明细层开始分桶。

5. Hive 拉链表

• 由于数据每天存在更新，所以在Hive中构建拉链表。
• 业务模拟如下，原始数据:

• 经过一天时间，数据发生变化，即增量数据: 9月27日

得到存储新增和更新数据的临时表的SQL

select * from 表 where create_time = '2021-09-27' or update_time ='2021-09-27'

• 接下来，需要将Hive中原始数据与临时表合并

  

• 在经过一天时间，又有新增数据以及更新的数据

• 创建一张临时表存储新增及更新的数据，并与原始数据合并

• 整体过程可分为三步：

  • 更改原始拉链表的已过期数据

  select  from 原始表 A left join 新增数据表 B on A.sid = B.sid
  

  left join 结果:

  

  select  
  A.sid,A.sname,A.address,A.create_time,A.update_time,A.start_time,
  if(A.end_time='9999-12-31' and B.sid is not null,B.end_time,A.end_time) as end_time
  from 原始表 A left join 新增数据表 B on A.sid = B.sid
  

  SQL结果:

  

  • 将更改的数据 与 临时表数据 union all 合并操作

    select  
    A.sid,A.sname,A.address,A.create_time,A.update_time,A.start_time,
    if(A.end_time='9999-12-31' and B.sid is not null,B.end_time,A.end_time) as end_time
    from 原始表 A left join 新增数据表 B on A.sid = B.sid
    union all
    select * from 新增数据表
    

    

  • 将临时表数据结果覆盖到原始拉链表

    • 为了确保不会在覆盖过程中的任务失败，导致数据丢失，所以上述操作在临时表中进行，最终覆盖到原始拉链表

    insert overwrite 原始拉链表 select * from 最终临时表
    

6. Hive 索引

为什么索引查询效率高？

​ hive索引是在 分区 分桶优化基础上, 又提供一种新的优化手段, 如果分区 和分桶受限, 可以尝试使用索引的方式来优化处理

hive提供了三种索引:

• 原始索引
• row group index(行组索引)
• bloom filter index(布隆过滤索引)

6.1 hive的原始索引

​ 结论: 此索引已经不再使用, 在hive3.0以上, 彻底不支持

	hive的原始索引, 可以针对表中某一列或者某几列构建索引, 构建之后, 当查询的时候使用到索引字段, 可以帮助提升一定效率
	弊端: 
		hive原始索引, 不会自动更新, 每一次对表中数据进行处理后, 都需要进行重建索引, 而这个效率是比较差, 而且整体效率的提升,相对比较有限

6.2 row group index(行组索引)

条件:
	1) 要求表必须是ORC存储格式
	2) 需要对那个字段进行索引, 必须对这个字段进行排序, 否则索引意义不大
	3) 在创建表的时候, 必须开启行组索引: ’orc.create.index’=’true’
	4) 主要应用在 < > = 场景中, 主要针对数值类型数据

-- 建表操作: 
CREATE TABLE lxw1234_orc2(
    ....
) stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
--     开启行组索引
    'orc.create.index'='true'
)

-- 插入数据:
    SELECT CAST(siteid AS INT) AS id,
    pcid
    FROM lxw1234_text
--     插入的数据保持排序
    DISTRIBUTE BY id sort BY id;

6.3 bloom filter index(布隆过滤索引)

条件:
	1) 必须是ORC类型的表
	2) 需要按照哪一个字段进行等值连接, 需要对这个字段构建布隆索引
	3) 仅适合做等值连接, 不局限于数据类型
	4) 在建表的时候, 必须指定为那些字段开启: ”orc.bloom.filter.columns”=”pcid,字段2..”
	

使用索引:
SET hive.optimize.index.filter=true --开启 hive的自动使用索引
SELECT COUNT(1) FROM lxw1234_orc1 WHERE id >= 0 AND id <= 1000   (行组索引)
AND pcid IN ('0005E26F0DCCDB56F9041C','A'); (布隆过滤索引)

-- 如何使用:
CREATE TABLE lxw1234_orc2 (
   ....
) stored AS ORC
TBLPROPERTIES
(
    'orc.compress'='SNAPPY',
    'orc.create.index'='true',
--     pcid字段开启BloomFilter索引
    "orc.bloom.filter.columns"="pcid"
)

-- 插入数据: 
SELECT CAST(siteid AS INT) AS id,
pcid
FROM lxw1234_text
DISTRIBUTE BY id sort BY id;

注意 如果要使用hive的索引, 必须开启hive自动使用索引

SET hive.optimize.index.filter=true --开启 hive的自动使用索引  

当然也可以将这个配置, 直接配置到cm的hive的配置窗口中, 进行统一配置

在生产中这些索引如何选择呢?

1) 对于行组索引, 建议是常开即可
	在插入数据, 如果不明确对那个字段使用行组索引, 可以任意的插入, 在条件合适情况下, 也在使用行组索引
	
2) 布隆过滤索引:  主要将那些用于 等值连接的字段开启索引即可
	一般主要指的是 join关联字段 以及清洗字段