Hive架构及HQL操作之DDL、DQL、DML命令

Hive简介

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

• Hive本质是：将 SQL 转换为 MapReduce 的任务进行运算
• 底层由HDFS来提供数据存储
• 可以将Hive理解为一个：将 SQL 转换为 MapReduce 任务的工具

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

• 数据仓库的目的：构建面向分析的、集成的数据集合；为企业提供决策支持
• 数据仓库本身不产生数据，数据来源与外部
• 存储了大量数据，对这些数据的分析和处理不可避免的用到Hive

Hive和RDBMS对比

由于 Hive 采用了类似SQL 的查询语言 HQL(Hive Query Language)，因此很容易将Hive 理解为数据库。其实从结构上来看，Hive 和传统的关系数据库除了拥有类似的查询语言，再无类似之处。

• 查询语言相似。 HQL <=> SQL 高度相似
  由于SQL被广泛的应用在数据仓库中，因此，专门针对Hive的特性设计了类SQL的查询语言HQL。熟悉SQL开发的开发者可以很方便的使用Hive进行开发。

• 数据规模。 Hive存储海量数据；RDBMS只能处理有限的数据集；由于Hive建立在集群上并可以利用MapReduce进行并行计算，因此可以支持很大规模的数据；而RDBMS可以支持的数据规模较小。

• 执行引擎。 Hive的引擎是MR/Tez/Spark/Flink；RDBMS使用自己的执行引擎Hive中大多数查询的执行是通过 Hadoop 提供的 MapReduce 来实现的。而RDBMS通常有自己的执行引擎。

• 数据存储。 Hive保存在HDFS上；RDBMS保存在本地文件系统 或 裸设备Hive 的数据都是存储在 HDFS 中的。而RDBMS是将数据保存在本地文件系统或裸设备中。

• 执行速度。 Hive相对慢（MR/数据量）；RDBMS相对快；Hive存储的数据量大，在查询数据的时候，通常没有索引，需要扫描整个表；加之Hive使用MapReduce作为执行引擎，这些因素都会导致较高的延迟。而RDBMS对数据的访问通常是基于索引的，执行延迟较低。当然这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出并行的优势。

• 可扩展性。 Hive支持水平扩展；通常RDBMS支持垂直扩展，对水平扩展不友好Hive建立在Hadoop之上，其可扩展性与Hadoop的可扩展性是一致的（Hadoop集群规模可以轻松超过1000个节点）。而RDBMS由于 ACID 语义的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle 在理论上的扩展能力也只有100台左右。

• 数据更新。 Hive对数据更新不友好；RDBMS支持频繁、快速数据更新Hive是针对数据仓库应用设计的，数据仓库的内容是读多写少的。因此，Hive中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而RDBMS中的数据需要频繁、快速的进行更新。

Hive的优缺点

优点

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

缺点

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) ：把逻辑执行计划转换成可以运行的物理计划；

HQL操作之 – DDL命令

官方链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
DDL（data definition language）： 主要的命令有CREATE、ALTER、DROP等。
DDL主要是用在定义、修改数据库对象的结构 或 数据类型。

数据库操作

创建数据库语法

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
        [COMMENT database_comment]
        [LOCATION hdfs_path]
        [MANAGEDLOCATION hdfs_path]
        [WITH DBPROPERTIES (property_name=property_value, ...)];

比如：

-- 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db
hive (default)> create database mydb;


-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】
hive (default)> create database if not exists mydb;

-- 创建数据库。添加备注，指定数据库在存放位置
hive (default)> create database if not exists mydb2
                    comment 'this is mydb2'
                    location '/user/hive/mydb2.db';

查看数据库

-- 查看所有数据库
show database;

-- 查看数据库信息
desc database mydb2;
desc database extended mydb2;
describe database extended mydb2;

删除数据库

-- 删除一个空数据库
drop database databasename;

-- 如果数据库不为空，使用 cascade 强制删除
drop database databasename cascade;

建表语法

1. create [external] table [IF NOT EXISTS] table_name
2. [(colName colType [comment 'comment'], ...)]
3. [comment table_comment]
4. [partition by (colName colType [comment col_comment], ...)]
5. [clustered BY (colName, colName, ...)
6. [sorted by (col_name [ASC|DESC], ...)] into num_buckets
    buckets]
7. [row format row_format]
8. [stored as file_format]
9. [LOCATION hdfs_path]
10. [TBLPROPERTIES (property_name=property_value, ...)]
11. [AS select_statement];

12. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS]
      [db_name.]table_name
13. LIKE existing_table_or_view_name
14. [LOCATION hdfs_path];

1. CREATE TABLE。按给定名称创建表，如果表已经存在则抛出异常。可使用if not exists 规避。

2. EXTERNAL关键字。创建外部表，否则创建的是内部表(管理表)。
   删除内部表时，数据和表的定义同时被删除；
   删除外部表时，仅仅删除了表的定义，数据保留；
   在生产环境中，多使用外部表；

3. comment。表的注释

4. partition by。对表中数据进行分区，指定表的分区字段

5. clustered by。创建分桶表，指定分桶字段

6. sorted by。对桶中的一个或多个列排序，较少使用

7. 存储子句。

   ROW FORMAT DELIMITED
   [FIELDS TERMINATED BY char]
   [COLLECTION ITEMS TERMINATED BY char]
   [MAP KEYS TERMINATED BY char]
   [LINES TERMINATED BY char] | SERDE serde_name
   [WITH SERDEPROPERTIES (property_name=property_value,
   property_name=property_value, ...)]
   

   该字段用来指定表中各字段间，以及字段内部的分隔符。根据字段的类型可以分为COLLECTION、MAP、LINES等，为其指定内部的分隔字符。
   建表时可指定 SerDe 。如果没有指定 ROW FORMAT 或者 ROW FORMATDELIMITED，将会使用默认的 SerDe。建表时还需要为表指定列，在指定列的同时也会指定自定义的 SerDe。Hive通过 SerDe 确定表的具体的列的数据。SerDe是 Serialize/Deserilize 的简称， hive使用Serde进行行对象的序列与反序列化。

8. stored as SEQUENCEFILE|TEXTFILE|RCFILE。如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED ASSEQUENCEFILE（二进制序列文件）。

9. LOCATION。表在HDFS上的存放位置

10. TBLPROPERTIES。定义表的属性

11. AS。后面可以接查询语句，表示根据后面的查询结果创建表

14.LIKE。like 表名，允许用户复制现有的表结构，但是不复制数据

内部表 & 外部表

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

• 默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
• 在删除内部表时，表的定义(元数据) 和 数据 同时被删除
• 在删除外部表时，仅删除表的定义，数据被保留
• 在生产环境中，多使用外部表

创建内部表

-- 创建内部表
create table t1(
    id int,
    name string,
    hobby array<string>,
    addr map<string, string>
)
row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

创建外部表

-- 创建外部表
create external table t2(
    id int,
    name string,
    hobby array<string>,
    addr map<string, string>
)

row format delimited
fields terminated by ";"
collection items terminated by ","
map keys terminated by ":";

内部表与外部表的转换

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;

-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

小结

• 建表时：
  如果不指定external关键字，创建的是内部表；
  指定external关键字，创建的是外部表；

• 删表时
  删除外部表时，仅删除表的定义，表的数据不受影响
  删除内部表时，表的数据和定义同时被删除

• 外部表的使用场景
  想保留数据时使用。生产多用外部表

分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。
而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。
在实际中，通常根据时间、地区等信息进行分区。

分区表创建与数据加载

-- 创建表
create table if not exists t3(
    id int,
    name string,
    hobby array<string>,
    addr map<String,string>
)

partitioned by (dt string)
row format delimited
fields terminated by ';'
collection items terminated by ','
map keys terminated by ':';

-- 加载数据。
load data local inpath "/home/hadoop/data/t1.dat" into table t3
partition(dt="2020-06-01");

**备注：分区字段不是表中已经存在的数据，可以将分区字段看成伪列

查看分区

show partitions t3;

新增分区并设置数据

-- 增加一个分区，不加载数据
alter table t3 add partition(dt='2020-06-03');

-- 增加多个分区，不加载数据
alter table t3
add partition(dt='2020-06-05') partition(dt='2020-06-06');

修改分区的hdfs路径

alter table t3 partition(dt='2020-06-01') set location '/user/hive/warehouse/t3/dt=2020-06-03';

删除分区

-- 可以删除一个或多个分区，用逗号隔开
alter table t3 drop partition(dt='2020-06-03'),
partition(dt='2020-06-04');

分桶表

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

• MR中：key.hashCode % reductTask
• Hive中：分桶字段.hashCode % 分桶个数

创建分桶表

create table course(
    id int,
    name string,
    score int
)
clustered by (id) into 3 buckets
row format delimited fields terminated by "\t";


-- 分桶表加载数据需要通过 insert ... select ... 利用一个简单表作为过渡给分桶表加载数据
insert into table course select * from course_common;

备注：

• 分桶规则：分桶字段.hashCode % 分桶数
• 分桶表加载数据时，使用 insert… select … 方式进行
• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

HQL DDL命令小结

• 主要对象：数据库、表
• 表的分类：
  • 内部表。删除表时，同时删除元数据和表数据
  • 外部表。删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表
  • 分区表。按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能
  • 分桶表。按照分桶字段，将表中数据分开。 分桶字段.hashCode % 分桶数据
• 主要命令：create、alter 、drop

HQL操作之–DQL命令

DQL – Data Query Language 数据查询语言

select语法：

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]

SQL语句书写注意事项：

• SQL语句对大小写不敏感
• SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)
• 关键字不能缩写，也不能分行
• 各子句一般要分行
• 使用缩进格式，提高SQL语句的可读性(重要)

where子句

WHERE子句紧随FROM子句，使用WHERE子句，过滤不满足条件的数据；

where 子句中不能使用列的别名；

select * from emp
where sal > 2000;

where子句中会涉及到较多的比较运算 和 逻辑运算；

比较运算符

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

group by子句

GROUP BY语句通常与聚组函数一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作。

• where子句针对表中的数据发挥作用；having针对查询结果（聚组以后的结果）发挥作用
• where子句不能有分组函数；having子句可以有分组函数
• having只用于group by分组统计之后

排序子句

全局排序(order by)

• order by 子句出现在select语句的结尾；
• order by子句对最终的结果进行排序；
• 默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示降序；
• ORDER BY执行全局排序，只有一个reduce；
• 排序字段要出现在select子句中。以下语句无法执行（因为select子句中缺少deptno）

每个MR内部排序(sort by)

• 对于大规模数据而言order by效率低；
• 在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by；
• sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的结果；

分区排序(distribute by)

• distribute by 将特定的行发送到特定的reducer中，便于后继的聚合 与 排序操作；
• distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；
• distribute by 要写在sort by之前；

Cluster By

当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法；
cluster by 只能是剩下，不能指定排序规则；

-- 语法上是等价的
select * from emp distribute by deptno sort by deptno;
select * from emp cluster by deptno;

排序小结：

• order by。执行全局排序，效率低。生产环境中慎用
• sort by。使数据局部有序(在reduce内部有序)
• distribute by。按照指定的条件将数据分组，常与sort by联用，使数据局部有序
• cluster by。当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法

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提供行级别的ACID语义

• BEGIN、COMMIT、ROLLBACK 暂时不支持，所有操作自动提交

• 目前只支持 ORC 的文件格式

• 默认事务是关闭的，需要设置开启

• 要是使用事务特性，表必须是分桶的

• 只能使用内部表

• 如果一个表用于ACID写入（INSERT、UPDATE、DELETE），必须在表中设置表属性 : “transactional=true”

• 必须使用事务管理器org.apache.hadoop.hive.ql.lockmgr.DbTxnManager

• 目前支持快照级别的隔离。就是当一次数据查询时，会提供一个数据一致性的快照

• LOAD DATA语句目前在事务表中暂时不支持

HDFS是不支持文件的修改；并且当有数据追加到文件，HDFS不对读数据的用户提供一致性的。为了在HDFS上支持数据的更新：

• 表和分区的数据都被存在基本文件中（base files）
• 新的记录和更新，删除都存在增量文件中（delta files）
• 一个事务操作创建一系列的增量文件
• 在读取的时候，将基础文件和修改，删除合并，最后返回给查询

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 ",";

-- 数据
name1,1,010-83596208,2020-01-01
name2,2,027-63277201,2020-01-02
name3,3,010-83596208,2020-01-03
name4,4,010-83596208,2020-01-04
name5,5,010-83596208,2020-01-05

-- 向临时表加载数据；向事务表中加载数据
load data local inpath '/home/hadoop/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/mydb.db/zxz_data ;

-- 分桶字段不能修改，下面的语句不能执行
-- Updating values of bucketing columns is not supported
update zxz_data set nid = nid + 1;