数据仓库Hive
Hive Join
Join 语法
Hive支持以下连接表的语法：
join_table:
table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
table_reference:
table_factor
| join_table
table_factor:
tbl_name [alias]
| table_subquery alias
| ( table_references )
join_condition:
ON expression
有关join语法的上下文，请参考Select Syntax
Version 0.13.0+: Implicit join notation(隐式连接表示法)
从Hive 0.13.0开始支持隐式join表示法(参见HIVE-5558)。这允许FROM子句join逗号分隔的表列表，省略join关键字。例如：
SELECT * FROM table1 t1, table2 t2, table3 t3 WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';
Version 0.13.0+: Unqualified column references(未限定列引用)
从Hive 0.13.0开始，关联条件支持非限定的列引用(参见HIVE-6393)。Hive会根据关联的输入解析这些数据。如果一个未限定的列引用涉及多个表，Hive会将其标记为有歧义的引用。例如：
CREATE TABLE a (k1 string, v1 string); CREATE TABLE b (k2 string, v2 string);
SELECT k1, v1, k2, v2 FROM a JOIN b ON k1 = k2;
Version 2.2.0+: Complex expressions in ON clause(ON子句中的复杂表达式)
从Hive 2.2.0开始支持ON子句中的复杂表达式(参见HIVE-15211, HIVE-15251)。在此之前，Hive不支持非相等条件的join
具体来说，join条件的语法限制如下
join_condition: ON equality_expression ( AND equality_expression )* equality_expression: expression = expression
Examples
编写join查询时需要考虑以下几点
- 允许使用复杂的join表达式
SELECT a.* FROM a JOIN b ON (a.id = b.id);
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department);
SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id);
- 同一个查询中可以join两个以上的表
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
- 如果每个表在join子句中使用相同的列，Hive将多个表的join转换为单个map/reduce作业
-- 转换为单个map/reduce作业，因为join中只涉及b的key1列
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);
-- 转换为两个map/reduce作业，因为来自b的key1列用于第一个join条件，而来自b的key2列用于第二个join条件。第一个map/reduce作业将a与b join起来，然后在第二个map/reduce作业中将结果与c join起来
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
- 在join的每个map/reduce阶段，序列中的最后一个表通过reducer进行流处理，而其他表则被缓冲。因此，通过组织表，使最大的表出现在序列的最后，有助于减少reducer缓冲join键的特定值的行所需的内存
-- 所有的三张表在一个map/reduce作业中被join起来，表a和表b中的key的特定值被缓存在reducer的内存中。然后，对于从c中检索的每一行，使用缓冲的行计算join
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);
-- 在计算join时涉及两个map/reduce作业。第一个MR作业将a与b join起来，并缓存a的值，同时将b的值流式传输到reducer中。第二个MR作业缓存第一个join的结果，同时将c的值流式传输到reducer中
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2);
- 在join的每个map/reduce阶段，可以通过提示指定要流式传输的表
-- 所有的三张表在一个map/reduce作业中被join起来，表b和表c中的key的特定值被缓存在reducer的内存中。然后，对于从a中检索的每一行，使用缓冲的行计算join。如果忽略STREAMTABLE的提示，Hive会在join操作中流处理最右边的表
SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);
- 存在LEFT, RIGHT, 和 FULL OUTER JOIN 是为了对没有匹配的ON子句提供更多的控制
-- 这个查询将返回a中的每一行。当 b.key 等于 a.key 时，这个输出行将是`a.val,b. val`；当没有相应的b.key时，输出行将是`a.val,NULL`。b中没有对应的a.key的行将被抛弃。语法"FROM a LEFT OUTER JOIN b"必须写在一行上，以便理解它是如何工作的——在这个查询中，a在b的左边，因此从a开始的所有行都被保留；`RIGHT OUTER JOIN`会保留来自b的所有行，`FULL OUTER JOIN`会保留来自a的所有行和来自b的所有行。`OUTER JOIN `的语义应该符合标准的SQL规范
SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key);
- join发生在WHERE子句之前。因此，如果希望限制join的输出，则需求（条件）应该在WHERE子句中，否则应该在join子句中。这个问题的一个大困惑点是分区表
注意这里sql的测试可以直接拿到mysql中运行，结果是一样的，对于OUTER JOIN hive与mysql表现类似
-- 准备数据
create table my_a(`key` int,`val` varchar(16),ds varchar(16));
insert into my_a values
(1, 'aa', '2009-07-07'),
(2, 'bb', '2009-07-07'),
(3, 'cc', '2009-07-07'),
(4, 'dd', '2009-07-08');
create table my_b(`key` int,`val` varchar(16),ds varchar(16));
insert into my_b values
(1, 'AA', '2009-07-07'),
(2, 'BB', '2009-08-07'),
(3, 'CC', '2009-08-07'),
(4, 'DD', '2009-07-07');
-- 这个查询将a join b，生成一个a.val和b.val的列表。然而，WHERE子句也可以引用join输出中的a和b的其他列，然后对它们进行过滤。然而，当join中的某一行找到了对应于a的key，而没有找到对应于b的key时，b的所有列都将为NULL，包括ds列。也就是说，你会过滤掉所有无效的b.key的join输出，这样你就超过了LEFT OUTER要求。换句话说，如果在WHERE子句中引用b的任何列，则join的LEFT OUTER部分是无关的
SELECT a.val,a.ds,b.val FROM my_a a LEFT OUTER JOIN my_b b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07';
aa 2009-07-07 AA
-- 相反，当OUTER JOIN时，使用以下语法。结果是，join的输出是经过预过滤的，对于具有有效的a.key但没有匹配的b.key的行，不会遇到后过滤问题。同样的逻辑适用于RIGHT JOIN和FULL JOIN
SELECT a.val,a.ds,b.val FROM my_a a LEFT OUTER JOIN my_b b
ON (a.key=b.key AND b.ds='2009-07-07' AND a.ds='2009-07-07');
aa 2009-07-07 AA
bb 2009-07-07 NULL
cc 2009-07-07 NULL
dd 2009-07-08 NULL
-- 上面是hive官网的描述很难理解，这里简单说明一下。对于OUTER JOIN其ON中指定的条件只是用来连接表的（生成一个中间的临时表），不是对表进行过滤的，要对表进行过滤必须在WHERE子句中进行。而且JOIN发生在WHERE子句之前，左连接join时始终保留左表的数据而不管ON中左表的条件是否满足（表现为ON中左表的条件有时生效、有时不生效），ON中的条件只用于连接，当条件满足时右表有数据，当条件不满足时右表没有数据。对于右连接和全连接是类似的，ON仅用于连接，然后满足连接的语义即可
-- 下面再举个例子
-- 准备数据，在mysql中执行时，建表语句加上'DEFAULT CHARSET=utf8'
create table my_classes(id int,name varchar(16)) ;
insert into my_classes values(1, '一班'),(2, '二班'),(3, '三班'),(4, '四班');
create table my_students(id int,class_id int,name varchar(16),gender varchar(1));
insert into my_students values
(1,1,'小明','M'),
(2,1,'小红','F'),
(3,1,'小军','M'),
(4,1,'小米','F'),
(5,2,'小白','F'),
(6,2,'小兵','M'),
(7,2,'小林','M'),
(8,3,'小新','F'),
(9,3,'小王','M'),
(10,3,'小丽','F');
-- 找出每个班级的名称及其对应的女同学数量，没有女同学的班级也需要展示，即不限制join的输出，则条件放到on中
SELECT c.name, count(s.name) as num FROM my_classes c left join my_students s
on s.class_id = c.id and s.gender = 'F' group by c.name;
一班 2
三班 2
二班 1
四班 0
SELECT c.name, count(s.name) as num FROM my_classes c left join my_students s
on s.class_id = c.id where s.gender = 'F' group by c.name;
一班 2
三班 2
二班 1
-- 找出一班的同学总数，只展示一班，即限制join的输出，则条件放到where中
SELECT c.name, count(s.name) as num FROM my_classes c left join my_students s
on s.class_id = c.id where c.name = '一班' group by c.name;
一班 4
SELECT c.name, count(s.name) as num FROM my_classes c left join my_students s
on s.class_id = c.id and c.name = '一班' group by c.name;
一班 4
三班 0
二班 0
四班 0
- join是不可交换的！join是左结合的（从左往右执行），无论它们是LEFT JOIN还是RIGHT JOIN
-- 首先将a和b join起来，丢弃a或b中在另一张表中没有相应key的所有数据（内连接语义）。将第一个join的结果表再join c，如果一个key同时存在于a和c中而不存在于b中，那么结果就不直观了。在"a JOIN b"步骤中，整行数据(包括a.val1, a.val2, 和 a.key)会被抛弃，因为它不在b中。结果中没有a.key，因此当它与c进行左外连接时，c.val无法获取，因为没有c.key与a.key匹配（因为a中的那一行已经被抛弃了）。类似地，如果这是一个'RIGHT OUTER JOIN'（而不是LEFT），我们会得到一个更奇怪的结果：'NULL, NULL, NULL, c.val'，因为即使我们指定了'a.key=c.key'作为join的键，我们也抛弃了与第一个JOIN不匹配的所有行（join从左往右执行的效果）
-- 为了达到更直观的效果，我们应该改为执行'FROM c LEFT OUTER JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key = b.key)'
SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key);
- LEFT SEMI JOIN 以一种有效的方式实现了不相关的IN/EXISTS子查询语义。从Hive 0.13开始，使用子查询支持'IN/NOT IN/EXISTS/NOT EXISTS'操作符，因此大多数join不再需要手动执行。使用'LEFT SEMI JOIN'的限制是右侧表只能在join条件（ON子句）中引用，而不能在WHERE或SELECT等子句中引用
SELECT a.key, a.value FROM a
WHERE a.key in (SELECT b.key FROM b);
SELECT a.key, a.value FROM a
WHERE EXISTS (SELECT b.key FROM b WHERE a.key=b.key);
-- 上面的sql，可以重写为下面的格式
SELECT a.key, a.value
FROM a LEFT SEMI JOIN b ON (a.key = b.key);
- 如果要join的表都很小，只有一个表例外，那么join操作可以只执行map作业
-- 这个查询不需要reducer，对于A的每个mapper, B都被完全读取到内存。限制是不能执行'FULL/RIGHT OUTER JOIN b'
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a JOIN b ON a.key = b.key;
- 如果要join的表在join的列上被分桶，并且一个表中的桶数量是另一个表中桶数量的倍数，那么这些桶可以彼此join
-- 如果表A有4个桶，表B有4个桶，则只能在mapper上执行以下join。不是为A的每个mapper完全获取B，而是只获取所需的桶。对于下面的查询，为A处理桶1的mapper将只获取b的桶1
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a JOIN b ON a.key = b.key;
-- 这不是默认行为，由以下参数控制
set hive.optimize.bucketmapjoin = true;
- 如果要join的表在join的列上进行了排序和分桶，并且它们具有相同数量的桶，则可以执行排序-合并join。对应的桶在mapper上相互join
-- 如果A和B都有4个桶，则只能在mapper上完成。对应于A的桶的mapper将遍历对应于b的桶
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM A a JOIN B b ON a.key = b.key;
-- 这不是默认行为，需要设置以下参数
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
MapJoin限制
- 如果要join的表都很小，只有一个表例外，那么join操作可以只执行map作业
-- 这个查询不需要reducer。对于A的每个mapper, B都被完全读取到内存
SELECT /*+ MAPJOIN(b) */ a.key, a.value
FROM a JOIN b ON a.key = b.key;
-
不支持以下内容
- Union Followed by a MapJoin
- Lateral View Followed by a MapJoin
- Reduce Sink (Group By/Join/Sort By/Cluster By/Distribute By) Followed by MapJoin
- MapJoin Followed by Union
- MapJoin Followed by Join
- MapJoin Followed by MapJoin
-
配置变量
hive.auto.convert.join(如果设置为true)会在运行时自动将join转换为mapjoin，并且应该使用它来代替mapjoin提示。mapjoin提示只能用于下面的查询- 如果所有输入都是分桶或排序的，那么join应该转换为分桶化的map-side(映射端) join 或分桶化的sort-merge(排序-合并) join
-
考虑在不同的键上进行多个mapjoin的可能性
select /*+MAPJOIN(smallTableTwo)*/ idOne, idTwo, value FROM ( select /*+MAPJOIN(smallTableOne)*/ idOne, idTwo, value FROM bigTable JOIN smallTableOne on (bigTable.idOne = smallTableOne.idOne) ) firstjoin JOIN smallTableTwo ON (firstjoin.idTwo = smallTableTwo.idTwo);不支持上述查询。如果没有mapjoin提示，上述查询将作为两个仅map的作业执行。如果用户事先知道输入足够小，可以放入内存，则可以使用以下可配置参数来确保查询在单个map-reduce作业中执行
- 'hive.auto.convert.join.noconditionaltask': Hive是否根据输入文件大小对普通join转换为mapjoin进行优化。如果开启此参数（默认开启），并且n路join的n-1个表/分区的大小总和小于指定的大小，则该join将直接转换为mapjoin(没有条件任务)
- 'hive.auto.convert.join.noconditionaltask.size': 如果'hive.auto.convert.join. noconditionaltask'为off，则此参数不生效。但是，如果它处于开启状态，并且n-1个表/分区的n路join的大小总和小于此大小，则该join将直接转换为mapjoin(没有条件任务)。默认值是10MB
Hive explain
Hive的执行计划描述了一个hiveSQL语句的具体执行步骤，通过执行计划解读可以了解hiveSQL语句被解析器转换为相应程序语言的执行逻辑。通过执行逻辑可以知晓HiveSQL运行流程，进而对流程进行优化，实现更优的数据查询处理。（编译器将一个Hive SQL转换为操作符，操作符是Hive的最小的处理单元，每个操作符代表HDFS的一个操作或者一个MapReduce作业）
同样，通过执行计划，还可以了解到哪些不一样的SQL逻辑其实是等价的，哪些看似一样的逻辑其实是执行代价完全不一样
Hive执行计划是一个预估的执行计划，只有在SQL实际执行后才会获取到真正的执行计划，而一些关系型数据库中，会提供真实的SQL执行计划。如SQLserver和Oracle等
hive执行计划语法EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query
- EXPLAIN: 查看执行计划的基本信息
- EXTENDED: 加上 extended 可以输出有关计划的额外扩展信息。这些通常是物理信息，例如文件名等
- CBO: 可以选择使用Calcite优化器不同成本模型生成计划。CBO 从 hive 4.0.0 版本开始支持
- AST: 输出查询的抽象语法树。AST 在hive 2.1.0 版本删除了，存在bug，转储AST可能会导致OOM错误，将在4.0.0版本修复
- DEPENDENCY: dependency在EXPLAIN语句中使用会产生有关计划中输入的依赖信息。包含表和分区信息等
- AUTHORIZATION: 显示SQL操作相关权限的信息
- LOCKS: 这对于了解系统将获得哪些锁以运行指定的查询很有用。LOCKS 从 hive 3.2.0 开始支持
- VECTORIZATION: 查看SQL的矢量化描述信息
- ANALYZE: 用实际的行数注释计划。从 Hive 2.2.0 开始支持
准备数据
for((i=1;i<=200000;i++))
do
remain=$((i%100))
nick="${remain}zhang${remain}"
age=$((i%50+10))
echo -e "$i\t$nick\t$age" >> user_info.txt
done
执行计划实践
-- 创建表并加载数据
create table user_info(id int,nick string,age int) row format delimited fields terminated by '\t';
load data local inpath "/home/bigdata/user_info.txt" into table user_info;
-- 统计年龄小于30岁各个年龄里，昵称里带"zhang1"的人数
explain select age,count(0) as num from user_info where age < 30 and nick like '%zhang1%' group by age;
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: user_info
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: ((age < 30) and (nick like '%zhang1%')) (type: boolean)
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: age (type: int)
outputColumnNames: age
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: count()
keys: age (type: int)
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: int)
sort order: +
Map-reduce partition columns: _col0 (type: int)
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
value expressions: _col1 (type: bigint)
Execution mode: vectorized
Reduce Operator Tree:
Group By Operator
aggregations: count(VALUE._col0)
keys: KEY._col0 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 38488952 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
上面的执行计划分为两大部分：STAGE DEPENDENCIES各个stage之间的依赖性，STAGE PLANS各个stage的执行计划
STAGE DEPENDENCIES包含两个stage，Stage-1是根stage，说明这是开始的stage，Stage-0依赖Stage-1，Stage-1执行完成后执行Stage-0
下面重点分析STAGE PLANS
Stage-1
里面有一个Map Reduce，Map Operator Tree是map端的执行计划树，Reduce Operator Tree是reduce端的执行计划树
Map Operator Tree信息解读，TableScan对关键字alias声明的表进行扫描操作
-
alias 表名称
-
Statistics 表统计信息，包括表中数据条数，数据大小等
-
Filter Operator 过滤操作，表示在之前的表扫描结果集上进行数据过滤
predicate 过滤数据时使用的谓词（过滤条件），如sql语句中的and age < 30，hive中也支持谓词下推
Statistics 过滤后数据条数和大小
Select Operator 对列进行投影，即筛选列，选取操作
-
expressions 筛选的列名称及列类型
outputColumnNames 输出的列名称
Statistics 筛选列后表统计信息，包括表中数据条数，数据大小等
Group By Operator 分组聚合操作
-
aggregations 显示聚合函数信息，这里使用
count()keys 表示分组的列，如果没有分组，则没有此字段
mode 聚合模式，值有hash随机聚合；mergepartial合并部分聚合结果；final最终聚合
outputColumnNames 聚合之后输出列名，_col0对应的是age列， _col1对应的是count()列
Statistics 表统计信息，包含分组聚合之后的数据条数，数据大小
Reduce Output Operator 输出到reduce操作的结果集信息
-
key expressions MR计算引擎，在map和reduce阶段的输出都是key-value形式，这里描述的是map端输出的键使用的是哪个数据列，_col0对应的是age列
sort order 值为空不排序；值为 + 正序排序，值为 - 倒序排序；值为 ± 排序的列为两列，第一列为正序，第二列为倒序，以此类推多值排序
Map-reduce partition columns 表示Map阶段输出到Reduce阶段的分区列，可以用distribute by指定分区的列。这里默认为_col0对应的是age列
Statistics 输出结果集的统计信息
value expressions 对应key expressions，这里是value值字段。_col1对应的是count()列
-
-
-
Reduce Operator Tree信息解读，出现和map阶段关键词一样的，其含义是一致的，下面说明一下map阶段未出现的关键词
-
File Output Operator 文件输出操作
compressed 表示输出结果是否进行压缩，true压缩，false不压缩
-
table 表示当前操作表的信息
input format 输入文件类型
output format 输出文件类型
serde 读取表数据的序列化和反序列化方式
Stage-0
Fetch Operator 客户端获取数据操作
-
limit 值为-1标识不限制条数，其他值为限制的条数
Processor Tree 处理器树
- ListSink 数据展示
hive执行计划，还有很多内容，这里不再展开了（MR现在用的已经不多了）。可以参考这个博客 blog.csdn.net/weixin_3983…
压缩和存储
压缩配置
MR支持的压缩编码
| 压缩格式 | 工具 | 算法 | 压缩率 | 速度 | 文件扩展名 | 是否可切分 |
|---|---|---|---|---|---|---|
| Deflate | 无 | Deflate | 很高 | 比较快 | .deflate | 否 |
| Gzip | gzip | Deflate | 很高 | 比较快 | .gz | 否 |
| Bzip2 | bzip2 | Bzip2 | 最高 | 慢 | .bz2 | 是 |
| LZO | lzop | LZO | 比较高 | 很快 | .lzo | 是（需要建额外索引） |
| LZ4 | 无 | LZ4 | 比较高 | 很快 | .lz4 | 否 |
| Snappy | 无 | Snappy | 比较高 | 很快 | .snappy | 否 |
每一种压缩算法都有自己的优缺点，压缩率和速度永远无法达到最优，应该根据不同的场景选择不同的算法。对于冷数据应该选择压缩率最高的算法（例如Bzip2），较热的数据应该选择压缩速度很快的算法（例如LZO和Snappy），对于非常热的数据不应该压缩。最常用的是Snappy，该算法压缩/解压缩速度很快且压缩率也比较高
压缩一般用的不多，因为太麻烦了，现在磁盘都不贵，使用压缩收益并不高
为了支持多种压缩/解压缩算法，Hadoop引入了编码/解码器，如下表所示（除了LzopCodec需要单独安装，其它的都是hadoop自带的）
| 压缩格式 | 对应的编码/解码器 |
|---|---|
| Deflate | org.apache.hadoop.io.compress.DeflateCodec |
| Gzip | org.apache.hadoop.io.compress.GzipCodec |
| Bzip2 | org.apache.hadoop.io.compress.BZip2Codec |
| LZO | com.hadoop.compression.lzo.LzopCodec |
| LZ4 | org.apache.hadoop.io.compress.Lz4Codec |
| Snappy | org.apache.hadoop.io.compress.SnappyCodec |
压缩配置参数
要在Hadoop中启用压缩，可以配置如下参数，配置文件在$HADOOP_HOME/etc/hadoop/这个目录
core-site.xml中相关配置如下
<!-- 可用于压缩/解压缩的压缩编解码器类的逗号分隔列表。除了使用此属性指定的任何类(优先)之外，还可以使用Java ServiceLoader发现类路径上的编解码器类。这里配置的编解码器类，就是从 hadoop-common-3.2.4.jar 里面 META-INF/services/org.apache.hadoop.io.compress.CompressionCodec 这个文件中复制过来的，可以不用在这里配置的（这里只是为了演示），使用自定义的编解码器类可以在这里进行配置 -->
<property>
<name>io.compression.codecs</name>
<value>org.apache.hadoop.io.compress.BZip2Codec,org.apache.hadoop.io.compress.DefaultCodec,org.apache.hadoop.io.compress.DeflateCodec,org.apache.hadoop.io.compress.GzipCodec,org.apache.hadoop.io.compress.Lz4Codec,org.apache.hadoop.io.compress.SnappyCodec,org.apache.hadoop.io.compress.ZStandardCodec</value>
</property>
Hadoop使用文件扩展名判断是否支持某种编解码器，io.compression.codecs这个配置的编解码器用于向hdfs中写入和读取时的编解码
mapred-site.xml中相关配置如下
<!-- map输出，压缩配置 -->
<!-- 是否启用map输出压缩（即map输出在通过网络发送之前是否压缩），默认是false -->
<property>
<name>mapreduce.map.output.compress</name>
<value>false</value>
</property>
<!-- map输出使用的编解码器（用于压缩数据），这里是默认值 -->
<property>
<name>mapreduce.map.output.compress.codec</name>
<value>org.apache.hadoop.io.compress.DefaultCodec</value>
</property>
<!-- reduce输出，压缩配置 -->
<!-- 是否启用reduce输出压缩，默认是false -->
<property>
<name>mapreduce.output.fileoutputformat.compress</name>
<value>false</value>
</property>
<!-- 如果要将作业输出压缩为SequenceFiles，那么应该如何压缩呢？应该是NONE, RECORD 或 BLOCK之一，默认是RECORD -->
<property>
<name>mapreduce.output.fileoutputformat.compress.type</name>
<value>RECORD</value>
</property>
<!-- reduce输出使用的编解码器（用于压缩数据），这里是默认值 -->
<property>
<name>mapreduce.output.fileoutputformat.compress.codec</name>
<value>org.apache.hadoop.io.compress.DefaultCodec</value>
</property>
开启map输出压缩
开启map输出压缩可以减少job中map和reduce间数据传输量
-- 开启hive中间传输数据压缩功能，默认为false
set hive.exec.compress.intermediate=true;
-- 开启mapreduce中map输出压缩功能，默认为false
set mapreduce.map.output.compress=true;
-- 设置mapreduce中map输出数据的压缩方式，默认为org.apache.hadoop.io.compress.DefaultCodec
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- 执行查询，但是并没有看到压缩的日志？？？
select count(*) from student;
开启reduce输出压缩
当Hive将输出写入到表中时，输出内容同样可以进行压缩。hive.exec.compress.output属性控制着这个功能，可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能
-- 开启hive最终输出数据压缩功能，默认为false
set hive.exec.compress.output=true;
-- 开启mapreduce最终输出数据压缩，默认为false
set mapreduce.output.fileoutputformat.compress=true;
-- 设置mapreduce最终数据输出压缩方式，默认为org.apache.hadoop.io.compress.DefaultCodec
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- 设置mapreduce最终数据输出压缩为块压缩，默认为RECORD
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
-- 测试一下输出结果是否是压缩文件
create table student_compress as select * from student;
-- student_compress表在hdfs中的数据文件是以`.snappy`为后缀，说明reduce输出压缩配置生效了
dfs -ls /user/hive/warehouse/student_compress;
-rw-r--r-- 3 bigdata supergroup 69 2024-04-03 11:15 /user/hive/warehouse/student_compress/000000_0.snappy
文件存储格式
Hive支持以下几种文件格式
- Text File
- SequenceFile
- RCFile
- Avro Files
- ORC Files
- Parquet
- Custom INPUTFORMAT and OUTPUTFORMAT（自定义输入格式化和输出格式化）
列式存储和行式存储
上图左边为逻辑表，右边第一个为行式存储，第二个为列式存储
行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快
列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段存储的数据的数据类型一定是相同的，列式存储可以针对性地设计更好的压缩算法
Text File、SequenceFile和Avro Files是基于行式存储的
ORC Files和Parquet是基于列式存储的
Text File格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作
ORC Files格式
ORC File 格式
ORC (Optimized Row Columnar)是hive 0.11版里引入的新的存储格式，ORC 文件格式提供了一种高效的Hive数据存储方式。它的设计是为了克服其他Hive文件格式的限制。使用ORC文件可以提高Hive读写和处理数据时的性能
与RCFile格式相比，ORC文件格式具有许多优点，例如
- 单个文件作为每个任务的输出，这减少了NameNode的负载
- Hive类型支持包括datetime, decimal和复杂类型(struct, list, map, union)
- 存储在文件中的轻量级索引
- 可以跳过未通过谓词过滤的行组
- 可以跳到指定的行
- 基于数据类型的块模式压缩
- 整数列的游程编码(run-length encoding)
- 字符串列的字典编码
- 使用不同的RecordReader并发读取同一个文件
- 能够在不扫描标记的情况下拆分文件
- 限制读或写所需的内存数量
- 使用Protocol Buffer存储元数据，允许添加和删除字段
文件结构
ORC文件包含称为stripe(条纹)的行数据组，以及file footer(文件页脚)中的辅助信息。在文件的末尾，postscript保存压缩参数和压缩页脚的大小。默认的stripe大小是250MB，大的stripe支持从HDFS进行大规模、高效的读取（提升了顺序读的吞吐量）。文件页脚包含文件中的stripe列表、每个stripe的行数以及每列的数据类型。它还包含列级聚合count,min,max和sum
下图展示了ORC文件结构
Stripe结构
如图所示，ORC文件中的每个stripe都包含索引数据(Index Data)、行数据(Row Data)和stripe页脚(Stripe Footer)。stripe页脚包含一个流位置目录，行数据用于表扫描。
索引数据包括每列的最小值和最大值以及每列中的行位置（位字段或布隆过滤器也可以包括在内）。行索引项提供偏移量，支持在解压缩块中查找正确的压缩块和字节。注意，ORC索引仅用于选择stripe和行组，而不是用于回答查询
拥有相对频繁的行索引项可以在stripe内进行行跳转，从而实现快速读取，尽管stripe大小很大。默认情况下，每10000行可以跳过一次
由于能够基于过滤器谓词跳过大量行，因此可以根据表的辅助键对表进行排序，从而大大减少执行时间。例如，如果主分区是事务日期，则可以按州、邮政编码和姓氏对表进行排序。然后，查找一个州的记录将跳过所有其他州的记录
ORC规范中给出了该格式的完整规范
上面是官网中的文档，这里简单说明一下
- Index Data：一个轻量级的索引，默认是每隔1W行做一个索引。这里做的索引只是记录某行的各字段在Row Data中的offset
- Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储
- Stripe Footer：存的是各个Stream的类型，长度等信息
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读
HiveQL语法
文件格式在表(或分区)级别指定。你可以用以下HiveQL语句指定ORC文件格式：
CREATE TABLE ... STORED AS ORCALTER TABLE ... [PARTITION partition_spec] SET FILEFORMAT ORCSET hive.default.fileformat=Orc
参数都放在TBLPROPERTIES中(参考Create Table)
| Key | Default | Notes |
|---|---|---|
| orc.compress | ZLIB | high level compression (one of NONE, ZLIB, SNAPPY) |
| orc.compress.size | 262144 | 每个压缩块中的字节数 |
| orc.stripe.size | 67108864 | 每个stripe中的字节数 |
| orc.row.index.stride | 10000 | 索引项之间的行数（必须>=1000） |
| orc.create.index | true | 是否创建行索引 |
| orc.bloom.filter.columns | "" | 应该为其创建布隆过滤器的列名称列表，用逗号分隔 |
| orc.bloom.filter.fpp | 0.05 | 布隆过滤器的假阳性概率(必须>0.0且<1.0) |
-- 例如，创建一个没有压缩的ORC存储表
create table Addresses (
name string,
street string,
city string,
state string,
zip int
) stored as orc tblproperties ("orc.compress"="NONE");
从Hive 0.14.0开始，ALTER TABLE table_name [PARTITION partition_spec] CONCATENATE可以用于将小的ORC文件合并成一个更大的文件。合并发生在stripe级别，这避免了数据的解压缩和解码
序列化和压缩
ORC文件中列数据的序列化取决于数据类型是整数还是字符串
整数列序列化
整数列在两个流中序列化
- 当前比特流：值是非空的吗？
- 数据流：整数流
整数数据的序列化方式利用了数字的共同分布：
- 整数使用可变宽度编码进行编码，小整数的字节数较少
- 重复的值是游程编码的
- 在-128到127之间相差一个常数的值是游程编码的
可变宽度编码基于Google protocol buffer，使用高位来表示该字节是否不是最后一个字节，使用低7位来编码数据。要对负数进行编码，使用zigzag编码，其中0, -1, 1, -2, 和 2分别映射为0, 1, 2, 3, 4, 和 5
每组数字都是这样编码的：
- 如果第一个字节(b0)是负的
- -b0后面是可变长度的整数
- 如果第一个字节(b0)是正数
- 它表示b0 + 3个重复整数
- 第二个字节(-128到+127)在每次重复之间添加
- 1个可变长度整数
在游程编码中，第一个字节指定游程长度，以及值是字面量还是重复值。副本可以步进-128到+128。游程编码使用protobuf风格的变长整数
字符串列序列化
字符串列的序列化使用字典来形成唯一的列值。对字典进行排序以加快谓词过滤并提高压缩比
字符串列在四个流中序列化
- 当前比特流：值是非空的吗？
- 字典数据：字符串的字节
- 字典长度：每个条目的长度
- 行数据：行值
字典长度和行值都是游程编码的整数流
压缩
流使用编解码器进行压缩，该编解码器被指定为该表中所有流的表属性。为了优化内存使用，压缩是在生成每个块时增量进行的。可以跳过被压缩的块，而不需要先解压扫描。流中的位置由块开始位置和块中的偏移量表示
编解码器可以是Snappy, Zlib, 或 none
ORC File转储实用程序
ORC文件转储实用程序分析ORC文件。要调用它，请使用以下命令
# Hive version 0.11 through 0.14:
hive --orcfiledump <location-of-orc-file>
# Hive version 1.1.0 and later:
hive --orcfiledump [-d] [--rowindex <col_ids>] <location-of-orc-file>
# Hive version 1.2.0 and later:
hive --orcfiledump [-d] [-t] [--rowindex <col_ids>] <location-of-orc-file>
# Hive version 1.3.0 and later:
hive --orcfiledump [-j] [-p] [-d] [-t] [--rowindex <col_ids>] [--recover] [--skip-dump]
[--backup-path <new-path>] <location-of-orc-file-or-directory>
- 在命令中指定-d将导致它转储ORC文件数据而不是元数据
- 使用以逗号分隔的列id列表指定--rowindex，会打印出指定列的行索引，其中0是包含所有列的顶级结构体，1是第一个列id
- 在命令中指定-t将打印写入器的时区id
- 在命令中指定-j将以JSON格式打印ORC文件元数据。要美观地打印JSON元数据，请在命令中添加-p
- 在命令中指定--recover可以恢复由Hive streaming生成的损坏的ORC文件
- 指定--skip-dump和--recover将在不转储元数据的情况下执行恢复
- 使用new-path指定--backup-path将允许恢复工具将损坏的文件移动到指定的备份路径(默认:/tmp)
<location-of-orc-file>是ORC文件的URI<location-of-orc-file-or-directory>是ORC文件或目录的URI。从Hive 1.3.0开始，这个URI可以是一个包含ORC文件的目录
Parquet格式
Parquet(parquet.io/)是面向分析型业务的列…
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式如下图所示
上图展示了一个Parquet文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的Magic Code，用于校验它是否是一个Parquet文件，Footer length记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据中，在Parquet中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前Parquet中还不支持索引页
主流文件存储格式对比实验
准备数据
for((i=1;i<=200000;i++))
do
track_time=$(date -d "+ $i second" +"%Y-%m-%d %H:%M:%S")
ip="192.168.91."$(($i % 100))
echo -e "$track_time\twww.test.$i\tsession$i\treferer$i\t$ip\tendUserId$i\tcityId$i" >> log_text01.txt
done
for((i=1;i<=100000;i++))
do
track_time=$(date -d "+ $i second" +"%Y-%m-%d %H:%M:%S")
ip="192.168.91."$(($i % 100))
echo -e "$track_time\twww.test.$i\tsession$i\treferer$i\t$ip\tendUserId$i\tcityId$i" >> log_text02.txt
echo -e "$track_time\twww.test.$i\tsession$i\treferer$i\t$ip\tendUserId$i\tcityId$i" >> log_text02.txt
done
Text File
-- 创建表，存储数据格式为textfile
create table log_text01 (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as textfile;
create table log_text02 like log_text01;
-- 向表中加载数据
load data local inpath '/home/bigdata/log_text01.txt' into table log_text01;
load data local inpath '/home/bigdata/log_text02.txt' into table log_text02;
-- 查看表中数据大小
dfs -du -h /user/hive/warehouse/log_text*;
19.9 M 59.6 M /user/hive/warehouse/log_text01/log_text01.txt
19.3 M 58.0 M /user/hive/warehouse/log_text02/log_text02.txt
ORC File
-- 创建表，存储数据格式为ORC
create table log_orc01(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc;
create table log_orc02 like log_orc01;
-- 向表中加载数据
insert into table log_orc01 select * from log_text01;
insert into table log_orc02 select * from log_text02;
-- 查看表中数据大小
dfs -du -h /user/hive/warehouse/log_orc*;
2.8 M 8.4 M /user/hive/warehouse/log_orc01/000000_0
2.2 M 6.7 M /user/hive/warehouse/log_orc02/000000_0
Parquet
-- 创建表，存储数据格式为parquet
create table log_parquet01(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet;
create table log_parquet02 like log_parquet01;
-- 向表中加载数据
insert into table log_parquet01 select * from log_text01;
insert into table log_parquet02 select * from log_text02;
-- 查看表中数据大小
dfs -du -h /user/hive/warehouse/log_parquet*;
20.8 M 62.4 M /user/hive/warehouse/log_parquet01/000000_0
18.0 M 53.9 M /user/hive/warehouse/log_parquet02/000000_0
存储文件的压缩比总结：ORC File > Parquet > Text File
当数据中每个字段都没有重复数据时Parquet存储的文件会比原始文件Text File存储稍大一些，这种极端情况在现实中一般不会遇到，数据中字段重复数据比例越高，则Parquet的压缩包越大
存储和压缩结合
创建一个非压缩的的ORC存储方式
-- 建表语句
create table log_orc_none01(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ("orc.compress"="NONE");
create table log_orc_none02 like log_orc_none01;
-- 插入数据
insert into table log_orc_none01 select * from log_text01;
insert into table log_orc_none02 select * from log_text02;
-- 查看插入后数据
dfs -du -h /user/hive/warehouse/log_orc_none*;
16.3 M 48.8 M /user/hive/warehouse/log_orc_none01/000000_0
10.9 M 32.6 M /user/hive/warehouse/log_orc_none02/000000_0
创建一个SNAPPY压缩的ORC存储方式
-- 建表语句
create table log_orc_snappy01(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc tblproperties ("orc.compress"="SNAPPY");
create table log_orc_snappy02 like log_orc_snappy01;
-- 插入数据
insert into table log_orc_snappy01 select * from log_text01;
insert into table log_orc_snappy02 select * from log_text02;
-- 查看插入后数据
dfs -du -h /user/hive/warehouse/log_orc_snappy*;
5.3 M 15.9 M /user/hive/warehouse/log_orc_snappy01/000000_0
5.0 M 14.9 M /user/hive/warehouse/log_orc_snappy02/000000_0
-- 上一节中，创建orc存储文件，默认采用ZLIB压缩，比snappy压缩的小
dfs -du -h /user/hive/warehouse/log_orc0*;
2.8 M 8.4 M /user/hive/warehouse/log_orc01/000000_0
2.2 M 6.7 M /user/hive/warehouse/log_orc02/000000_0
Hive HA高可用
在前面使用Hive时，我们只需要在Hive服务端启动Hive Metastore 即可(hive --service metastore)，然后在Hive的客户端连接Hive。实际上我们在实际开发中经常通过HiveServer2的方式连接Hive，在使用HiveServer2时，我们需要在Hive的服务端上启动HiveServer2(hiveserver2 &)，然后在Hive的客户端使用beeline登录操作Hive(beeline -u jdbc:hive2://node01:10000/default -n bigdata)
如果Hive服务端hiveserver2服务挂掉之后，我们就不能使用beeline方式连接Hive。这时我们需要对hiveserver2进行高可用部署，这就是Hive HA 高可用，Hive从0.14开始，使用Zookeeper实现了HiveServer2的HA功能（ZooKeeper Service Discovery），Client端可以通过指定一个nameSpace来连接HiveServer2，而不是指定某一个host和port
Hive高可用部署我们这里选择node01和node02两个节点为hiveserver2服务节点，也就是hive服务端，node03节点是Hive客户端，具体搭建Hive HA步骤如下
- 在node01节点上修改
$HIVE_HOME/conf/hive-site.xml这个配置文件，添加或修改如下配置
<!-- 修改hdfs中存储路径、修改在mysql中的元数据库，这样就可以不用删除老数据了，也可以随时恢复老数据 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive_ha/warehouse</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://node02:3306/hive_ha?createDatabaseIfNotExist=true&useSSL=false</value>
</property>
<!-- 下面配制是新添加的 -->
<!-- hive HA 相关配置 -->
<property>
<name>hive.server2.support.dynamic.service.discovery</name>
<value>true</value>
</property>
<!-- 在zk中的根目录 -->
<property>
<name>hive.server2.zookeeper.namespace</name>
<value>hiveserver2_zk</value>
</property>
<property>
<name>hive.zookeeper.quorum</name>
<value>node02:2181,node03:2181,node04:2181</value>
</property>
<property>
<name>hive.zookeeper.client.port</name>
<value>2181</value>
</property>
<property>
<name>hive.server2.thrift.bind.host</name>
<value>node01</value>
</property>
<property>
<name>hive.server2.thrift.port</name>
<value>10000</value>
</property>
- 将node01节点上的Hive安装包发送到node02节点
# node01节点root用户
cd /usr/local
scp -r ./hive-3.1.3/ node02:`pwd`
- 在node02节点修改Hive目录的属主并配置Hive环境变量
# node02节点root用户
chown -R bigdata /usr/local/hive-3.1.3/
echo 'HIVE_HOME=/usr/local/hive-3.1.3' >> /etc/profile
echo 'export PATH=$PATH:$HIVE_HOME/bin:$HIVE_HOME/sbin' >> /etc/profile
source /etc/profile
- 在node02节点上修改hive-site.xml中如下配置
<property>
<name>hive.server2.thrift.bind.host</name>
<value>node02</value>
</property>
- 初始化Hive
在node01或node02任意一个节点初始化hive
# node02节点bigdata用户
schematool -dbType mysql -initSchema
- 在node01和node02上启动hiveserver2
# node01节点bigdata用户
# 先杀死metastore和hiveserver2
kill -9 `jps | grep RunJar | awk '{print $1}'`
hiveserver2 &
# node02节点bigdata用户
hiveserver2 &
- 在hive客户端node03节点上通过beeline连接hive
准备数据(my_person.txt)
1 zs 18
2 ls 19
# node03节点bigdata用户
# 上传数据到hdfs
hdfs dfs -put my_person.txt /test
# 通过beeline连接hive
beeline -u "jdbc:hive2://node02,node03,node04/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2_zk" -n bigdata
连接之后，可以创建表，并加载数据
-- 创建表my_person
create table my_person(id int,name string,age int) row format delimited fields terminated by '\t';
-- 加载数据，必须加载HDFS路径，也可以加载本地文件，但是对应的hive服务端节点需要有对应文件
load data inpath '/test/my_person.txt' into table my_person;
-- 查询
select * from my_person;
+---------------+-----------------+----------------+
| my_person.id | my_person.name | my_person.age |
+---------------+-----------------+----------------+
| 1 | zs | 18 |
| 2 | ls | 19 |
+---------------+-----------------+----------------+
- 测试hiveserver2 HA
可以将连接的node01或node02节点上hiveserver2 服务kill掉，然后继续通过beeline进行查询，可以正常查询
注意：测试hiveserver2 HA时，当一个hiveserver2 挂掉之后，如果正好是客户端连接的服务端，我们需要重新登录beeline 通过jdbc方式连接Hive。这里并没有做存储元数据的mysql的高可用，HAProxy可以做mysql的高可用
