Hive--数据仓库工具

02 Feb 2021

Hive是基于Hadoop的一个数据仓库工具，可以将 结构化的数据文件 映射为一张表 (类似于RDBMS中的表)，并提供类SQL查询功能;Hive是由Facebook开源，用于解 决海量结构化日志的数据统计。

数据仓库(Data Warehouse)是一个面向主题的、集成的、相对稳定的、反映历史变 化的数据集合，主要用于管理决策。(数据仓库之父比尔·恩门，1991年提出)。

Hive的优缺点

Hive的优点

学习成本低。Hive提供了类似SQL的查询语言，开发人员能快速上手; 处理海量数据。底层执行的是MapReduce 任务; 系统可以水平扩展。底层基于Hadoop; 功能可以扩展。Hive允许用户自定义函数; 良好的容错性。某个节点发生故障，HQL仍然可以正常完成; 统一的元数据管理。元数据包括:有哪些表、表有什么字段、字段是什么类型

Hive的缺点

HQL表达能力有限; 迭代计算无法表达; Hive的执行效率不高(基于MR的执行引擎); Hive自动生成的MapReduce作业，某些情况下不够智能; Hive的调优困难;

Hive架构

  1. 用户接口 CLI(Common Line Interface):Hive的命令行，用于接收HQL，并返 回结果; JDBC/ODBC:是指Hive的java实现，与传统数据库JDBC类似; WebUI:是指可通过浏览器访问Hive;

  2. Thrift Server, Hive可选组件，是一个软件框架服务，允许客户端使用包括Java、C++、Ruby和其他很多种语言，通过 编程的方式远程访问Hive;

  3. 元数据管理(MetaStore) Hive将元数据存储在关系数据库中(如mysql、 derby)。Hive的元数据包括:数据库名、表名及类型、字段名称及数据类型、数 据所在位置等;

  4. 驱动程序(Driver)
    解析器 (SQLParser) :使用第三方工具(antlr)将HQL字符串转换成抽象语 法树(AST);对AST进行语法分析，比如字段是否存在、SQL语义是否有 误、表是否存在;
    编译器 (Compiler) :将抽象语法树编译生成逻辑执行计划;
    优化器 (Optimizer) :对逻辑执行计划进行优化，减少不必要的列、使用分 区等;
    执行器 (Executr) :把逻辑执行计划转换成可以运行的物理计划;

Hive安装与配置

安装前提:3台虚拟机，安装了Hadoop
安装软件:Hive(2.3.7) + MySQL (5.7.26)
备注:Hive的元数据默认存储在自带的 derby 数据库中，生产中多采用MySQL
derby:java语言开发占用资源少，单进程，单用户。仅仅适用于个人的测试。

MySQL安装

Hive安装

Hive官网: http://hive.apache.org
下载网址: http://archive.apache.org/dist/hive/
文档网址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual

安装前提:3台虚拟机，安装了Hadoop
安装软件:Hive(2.3.7) + MySQL (5.7.26)

# hive安装包   
apache-hive-2.3.7-bin.tar.gz  
  
# MySQL安装包 mysql-5.7.26-1.el7.x86_64.rpm-bundle.tar  
  
# MySQL的JDBC驱动程序   
mysql-connector-java-5.1.46.jar  
  
# 整体的安装步骤:   
1、安装MySQL   
2、安装配置Hive   
3、Hive添加常用配置

Hive基础知识

参数配置方式

参数配置的三种方式:
1、用户自定义配置文件(hive-site.xml)
2、启动hive时指定参数(-hiveconf)
3、hive命令行指定参数(set)

配置信息的优先级:

set > -hiveconf > hive-site.xml > hive-default.xml

Hive命令

hive -help

数据类型与文件格式

DDL命令

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

数据库操作

Hive的数据库名、表名均不区分大小写; 名字不能使用数字开头; 不能使用关键字，尽量不使用特殊符号;

建表语法

create [external] table [IF NOT EXISTS] table_name  
[(colName colType [comment 'comment'], ...)]  
[comment table_comment]  
[partition by (colName colType [comment col_comment], ...)]  
[clustered BY (colName, colName, ...)  
[sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets]  
[row format row_format]  
[stored as file_format]  
[LOCATION hdfs_path]  
[TBLPROPERTIES (property_name=property_value, ...)]  
[AS select_statement];  
  
  
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]  
[db_name.]table_name  
  LIKE existing_table_or_view_name  
  [LOCATION hdfs_path];

内部表 & 外部表

在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部表。

分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。

而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表 的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据 时，可避免全表扫描，提高查询效率。

在实际中，通常根据时间、地区等信息进行分区。

分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。分桶的原理:

修改表alter

-- 修改表名。rename  
alter table course rename to course1;  
-- 修改列名。change column  
alter table course1 change column id uid int;  
  
-- 修改字段类型。change column  
alter table course1 change column score score string;  
alter table course1 change column score score int;  
  
-- The following columns have types incompatible with the existing columns in their respective positions  
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是 string不能转为int  
  
-- 增加字段。add columns()  
alter table course1 add columns(sex string);  
alter table course1 add columns(hobby array<String>, salary double);  
  
-- 删除字段:replace columns  
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件  
alter table course1 replace columns(id int, name string, score int);

删除表drop

-- 删除表  
drop table course1;

数据操作

数据导入

LOAD DATA [LOCAL] INPATH 'filepath'  
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1,  
partcol2=val2 ...)]

插入数据

-- 创建分区表  
create table tabC (  
    id int,  
    name string,  
    area string  
)  
partitioned by (month string)  
row format delimited fields terminated by ",";  
  
-- 插入数据  
insert into table tabC partition(month="202101") values (4, "zhangsan", "BJ"), (5, "lisi", "TJ"), (6, "wangwu", "TW");  
  
-- 插入查询的结果数据  
insert into table tabC partition(month="202102") select id, name, area from tabC;  
  
-- 多表(多分区)插入模式  
from tabC  
insert overwrite table tabC partition(month="202102")  
select id, name, area where month="202101"  
insert overwrite table tabC partition(month="202103")  
select id, name, area where month="202102";  
  
-- 创建表并插入数据(as select)  
create table if not exists tabD as select * from tabC;  
-- 新建的表没有partitions，原来的partitions字段会被转化成column

数据导出

-- 将查询结果导出到本地  
insert overwrite local directory "/root/data/tabC"  
select * from tabC;  
  
-- 将查询结果格式化输出到本地  
insert overwrite local directory "/root/data/ftabC"  
row format delimited fields terminated by "\t"  
select * from tabC;  
  
-- 将查询结果导出到HDFS  
insert overwrite directory "/user/hive/tmp/tabC"  
row format delimited fields terminated by "\t"  
select * from tabC;  
  
-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝  
dfs -get /user/hive/warehouse/mydb1.db/tabc /root/data/gtabC;  
  
-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件  
hive -e "select * from mydb1.tabC" > my.log  
  
-- export 导出数据到HDFS。使用export导出数据时，不仅有数还有表的元数据信息  
export table tabC to "/user/hive/tmp/myTabC";  
  
-- export 导出的数据，可以使用 import 命令导入到 Hive 表中  
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构 可能不一致  
create table tabE like tabC;  
import table tabE from '/user/hive/tmp/myTabC';  
  
  
-- 截断表，清空数据。(注意:仅能操作内部表)  
truncate table tabE;  
  
-- 以下语句报错，外部表不能执行 truncate 操作  
alter table tabE set tblproperties("EXTERNAL"="TRUE");

DQL命令

select语法

DQL – Data Query Language 数据查询语言

SELECT [ALL | DISTINCT] select_expr, select_expr, ...  
  FROM table_reference  
  [WHERE where_condition]  
  [GROUP BY col_list]  
  [ORDER BY col_list]  
  [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY  
col_list]]  
 [LIMIT [offset,] rows]

注意事项:

函数

系统内置函数

Hive内置函数:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-Built-inFunctions

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是:对于每个组返回多行，而聚合函数对于每个组只返回一行。

窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

HQL面试题

自定义函数

用户自定义函数分为以下三类:

DML命令

数据操纵语言DML(Data Manipulation Language)，DML主要有三种形式:插入(INSERT)、删除(DELETE)、更新(UPDATE)。

事务(transaction)是一组单元化操作，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。 事务具有的四个要素:原子性(Atomicity)、一致性(Consistency)、隔离性 (Isolation)、持久性(Durability)，这四个基本要素通常称为ACID特性。

Hive 事务

Hive从0.14版本开始支持事务 和 行级更新，但缺省是不支持的，需要一些附加的配 置。要想支持行级insert、update、delete，需要配置Hive支持事务。

Hive 事务操作示例

-- 这些参数也可以设置在hive-site.xml中  
SET hive.support.concurrency = true;  
-- Hive 0.x and 1.x only  
SET hive.enforce.bucketing = true;  
SET hive.exec.dynamic.partition.mode = nonstrict;   
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;  
  
-- 创建表用于更新。满足条件:内部表、ORC格式、分桶、设置表属性  
create table zxz_data(  
    name string,  
    nid int,  
    phone string,  
    ntime date)  
clustered by(nid) into 5 buckets  
stored as orc  
tblproperties('transactional'='true');  
  
-- 创建临时表，用于向分桶表插入数据   
create table temp1(  
    name string,  
    nid int,  
    phone string,  
    ntime date)  
row format delimited  
fields terminated by ",";  
  
-- 向临时表加载数据;向事务表中加载数据  
load data local inpath '/root/data/zxz_data.txt' overwrite into table temp1;  
insert into table zxz_data select * from temp1;  
  
-- 检查数据和文件  
select * from zxz_data;  
dfs -ls /user/hive/warehouse/mydb1.db/zxz_data;  
  
-- DML 操作  
delete from zxz_data where nid = 3;  
dfs -ls /user/hive/warehouse/mydb1.db/zxz_data;  
  
-- insert   
insert into zxz_data values ("name3", 3, "010-83596208", current_date);-- 不支持中间使用函数  
-- FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values  
  
-- 可执行  
insert into zxz_data values ("name3", 3, "010-83596208", "2020-06-01");  
insert into zxz_data select "name3", 3, "010-83596208", current_date;  
select * from zxz_data;  
dfs -ls /user/hive/warehouse/mydb1.db/zxz_data;  
  
  
insert into zxz_data values  
("name6", 6, "010-83596208", "2020-06-02"),  
("name7", 7, "010-83596208", "2020-06-03"),  
("name8", 9, "010-83596208", "2020-06-05"),  
("name9", 8, "010-83596208", "2020-06-06");  
dfs -ls /user/hive/warehouse/mydb1.db/zxz_data;  
  
update zxz_data set name=concat(name, "00") where nid>3;  
dfs -ls /user/hive/warehouse/mydb1.db/zxz_data;  
  
-- 分桶字段不能修改，下面的语句不能执行  
-- Updating values of bucketing columns is not supported   
update zxz_data set nid = nid + 1;

元数据管理与存储

Metastore

在Hive的具体使用中，首先面临的问题便是如何定义表结构信息，跟结构化的数据映 射成功。所谓的映射指的是一种对应关系。在Hive中需要描述清楚表跟文件之间的映射关系、列和字段之间的关系等等信息。这些描述映射关系的数据的称之为Hive的元数据。该数据十分重要，因为只有通过查询它才可以确定用户编写sql和最终操作文 件之间的关系。

Metadata即元数据。元数据包含用Hive创建的database、table、表的字段等元信 息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如MySQL等。

Metastore即元数据服务，是Hive用来管理库表元数据的一个服务。有了它上层的 服务不用再跟裸的文件数据打交道，而是可以基于结构化的库表信息构建计算框架。

通过metastore服务将Hive的元数据暴露出去，而不是需要通过对Hive元数据库 mysql的访问才能拿到Hive的元数据信息;metastore服务实际上就是一种thrift服 务，通过它用户可以获取到Hive元数据，并且通过thrift获取元数据的方式，屏蔽了 数据库访问需要驱动，url，用户名，密码等细节。

HiveServer2

HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。 目前基于Thrift RPC的实现是HiveServer的改进版本，并支持多客户端并发和身份验 证，启动hiveServer2服务后，就可以使用jdbc、odbc、thrift 的方式连接。

HiveServer2(HS2)是一种允许客户端对Hive执行查询的服务。HiveServer2是 HiveServer1的后续 版本。HS2支持多客户端并发和身份验证，旨在为JDBC、ODBC 等开放API客户端提供更好的支持。

HCatalog

HCatalog 提供了一个统一的元数据服务，允许不同的工具如 Pig、MapReduce 等通 过 HCatalog 直接访问存储在 HDFS 上的底层文件。HCatalog是用来访问Metastore 的Hive子项目，它的存在给了整个Hadoop生态环境一个统一的定义。
HCatalog 使用了 Hive 的元数据存储，这样就使得像 MapReduce 这样的第三方应 用可以直接从 Hive 的数据仓库中读写数据。同时，HCatalog 还支持用户在 MapReduce 程序中只读取需要的表分区和字段，而不需要读取整个表，即提供一种 逻辑上的视图来读取数据，而不仅仅是从物理文件的维度。
HCatalog 提供了一个称为 hcat 的命令行工具。这个工具和 Hive 的命令行工具类 似，两者最大的不同就是 hcat 只接受不会产生 MapReduce 任务的命令。

数据存储格式

Hive支持的存储数的格式主要有:TEXTFILE(默认格式) 、SEQUENCEFILE、RCFILE、ORCFILE、PARQUET。

Hive调优策略

Hive作为大数据领域常用的数据仓库组件，在设计和开发阶段需要注意效率。
影响Hive效率的不仅仅是数据量过大;数据倾斜、数据冗余、job或I/O过多、 MapReduce分配不合理等因素都对Hive的效率有影响。

对Hive的调优既包含对HiveQL语句本身的优化，也包含Hive配置项和MR方面的调整。

架构优化

参数优化

Hive 参数说明的官方文档:
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

SQL优化