1. Hive简介

1.1 Hive的起源

背景问题：

• MapReduce开发难度大，学习成本高，项目周期长
• hdfs文件没有字段名、没有数据类型，不方便进行数据的有效管理

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

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

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

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

  1.2 Hive与RDBMS对比

  | | Hive | RDBMS | | :—-: | :—-: | :—-: | | 查询语言相似 | HQL | SQL | | 数据规模 | 存储海量数据 | 有限数据集 | | 执行引擎 | MR/Tez/Spark/Flink | 各个数据库管理工具有自己的引擎 | | 数据存储 | HDFS | 本地文件系统或裸设备 | | 执行速度 | 相对慢 | 相对快 | | 可扩展性 | 支持水平扩展，轻松超过1000个节点 | 支持垂直扩展，水平扩展目前最好的Oracle也就100个左右 | | 数据更新 | 对数据更新不友好 | 支持频繁，快速数据更新 |

1.3 Hive的优缺点

优点

• 学习成本低，类似SQL的查询语言，可以快速上手
• 处理海量数据
• 系统可以水平扩展
• 支持自定义函数
• 良好的容错性
• 统一的元数据管理

缺点

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

  1.4 Hive架构

  

1. 用户接口CLI(Common Line Interface)：
   1. Hive的命令行，用于接收HQL，并返回结果；
   2. JDBC/ODBC，如Hive的Java实现
   3. webUI，通过浏览器访问Hive
2. Thrift Server

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

1. 元数据管理(MetaStore)

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

1. 驱动程序(Driver)

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

      2. Hive的安装与配置

      2.1 Hive安装

      Hive官网：http://hive.apache.org
      下载网址：http://archive.apache.org/dist/hive/
      文档网址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual
      安装文档参考hive安装.pdf

      2.2 参数配置

      Hive的日志文件

      Hive的log默认存放在 /tmp/root 目录下（root为当前用户名）
      这个位置可以修改

      vi $HIVE_HOME/conf/hive-log4j2.properties
      # 添加以下内容：
      property.hive.log.dir = /opt/lagou/servers/hive-2.3.7/logs

      参数配置的三种方式

2. 配置文件方式

默认配置文件：hive-default.xml
用户自定义配置文件：hive-site.xml
配置优先级：hive-site.xml > hive-default.xml
配置文件的设定对本机启动的所有Hive进程有效；
配置文件的设定对本机所有启动的Hive进程有效；

1. 启动时指定参数值

启动Hive时，可以在命令行添加 -hiveconf param=value 来设定参数，这些设定仅对本次启动有效。

# 启动时指定参数
hive -hiveconf hive.exec.mode.local.auto=true
# 在命令行检查参数是否生效
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=true

1. 命令行修改参数

可在 Hive 命令行中使用SET关键字设定参数，同样仅对本次启动有效

hive> set hive.exec.mode.local.auto=false;
hive> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=false

优先级
set > -hiveconf > hive-site.xml > hive-default.xml

2.3 Hive命令

不进入hive交互窗口，执行sql语句

hive -e "select * from users"

执行脚本中的sql语句

# 执行文件中的sql语句
hive -f hqlfile.sql
# 将结果写入文件
hive -f hqlfile.sql >> result.log

推出hive命令行

exit or quit

在命令行中执行shell命令/dfs命令

hive> ! ls;
hive> ! clear;
hive> dfs -ls /;

3. 数据类型与文件格式

Hive支持关系型数据库的绝大多数基本数据类型，同时也支持4种集合数据类型。

3.1 基本数据类型与转换

数据类型的隐式转换
Hive的数据类型是可以进行隐式转换的，类似于Java的自动类型转换，小范围转大范围，原则就是精度不失。

• 任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT， INT 可以转换成 BIGINT；
• 所有整数类型、 FLOAT 和 STRING 类型（仅当STRING都是数字时）可以隐式转换为 DOUBLE；
• TINYINT、 SMALLINT、 INT 都可以转换为 FLOAT；
• BOOLEAN 类型不可以转换为任何其它的类型；

  hive> select '1.0'+2;
  OK
  3.0
  hive> select '1111' > 10;
  hive> select 1 > 0.8;
  

  数据类型的显示转换
  使用cast函数进行强制类型转换，如果失败返回null

  hive> select cast('1111a' as int);
  OK
  NULL
  hive> select cast('1111' as int);
  OK
  1111
  

  3.2 集合数据类型

  Hive支持集合数据类型，包括array、map、struct、union

hive> select array(1,2,3);
OK
[1,2,3]
-- 使用 [] 访问数组元素
hive> select arr[0] from (select array(1,2,3) arr) tmp;
hive> select map('a', 1, 'b', 2, 'c', 3);
OK
{"a":1,"b":2,"c":3}

-- 使用 [] 访问map元素
hive> select mymap["a"] from (select map('a', 1, 'b', 2, 'c', 3)
as mymap) tmp;
-- 使用 [] 访问map元素。 key 不存在返回 NULL
hive> select mymap["x"] from (select map('a', 1, 'b', 2, 'c', 3)
as mymap) tmp;
NULL

hive> select struct('username1', 7, 1288.68);
OK
{"col1":"username1","col2":7,"col3":1288.68}
-- 给 struct 中的字段命名
hive> select named_struct("name", "username1", "id", 7, "salary",
12880.68);
OK
{"name":"username1","id":7,"salary":12880.68}
-- 使用 列名.字段名 访问具体信息
hive> select userinfo.id
> from (select named_struct("name", "username1", "id", 7,
"salary", 12880.68) userinfo) tmp;

-- union 数据类型
hive> select create_union(0, "zhansan", 19, 8000.88) uinfo;

3.3 文本文件数据编码

Hive表中的数据在存储在文件系统上，Hive定义了默认的存储格式，也支持用户自定义文件存储格式。 Hive默认使用几个很少出现在字段值中的控制字符，来表示替换默认分隔符的字符。

Hive 中没有定义专门的数据格式，数据格式可以由用户指定，用户定义数据格式需要指定三个属性：列分隔符（通常为空格、”\t”、”\x001”（就是上面的\001））、行分隔符（”\n”）以及读取文件数据的方法。
在加载数据的过程中，Hive 不会对数据本身进行任何修改，而只是将数据内容复制或者移动到相应的HDFS目录中。
将 Hive 数据导出到本地时，系统默认的分隔符是^A、^B、^C 这些特殊字符，使用
cat 或者 vim 是看不到的；
在 vi 中输入特殊字符：
(Ctrl + v) + (Ctrl + a) => ^A
(Ctrl + v) + (Ctrl + b) => ^B
(Ctrl + v) + (Ctrl + c) => ^C
^A / ^B / ^C 都是特殊的控制字符，使用 more 、 cat 命令是看不见的；可以使用
cat -A file.dat

3.4 读时模式

在传统数据库中，数据在写入数据库时，对照表的定义进行检查，若不符合则拒绝写入，这种模式称为”写时模式”。
写时模式 -> 写数据检查 -> RDBMS；
Hive中数据写入过程采用”读时模式” (schema on read)，写入数据时不进行数据格式的校验，读取数据时如果不合法则显示NULL。这种模式的优点是写入数据快，读取时数据显示null

4. HQL之DDL

参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
DDL(data definition language)：主要的命令有create、alter、drop等
主要用在定义、修改数据库对象的结构或者数据类型

4.1 数据库操作

• Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库；
• Hive的数据库名、表名均不区分大小写；
• 名字不能使用数字开头；
• 不能使用关键字，尽量不使用特殊符号；

创建数据库语法

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment] -- 数据库的描述信息
[LOCATION hdfs_path]  --  外部表在hdfs上的存储位置，默认存储在hive.metastore.warehouse.dir 指定的目录下
[MANAGEDLOCATION hdfs_path] -- 内部表在hdfs上的存储位置 从hive 4.0.0开始
[WITH DBPROPERTIES (property_name=property_value, ...)]; -- ['author'='glh','date'='2020-10-15']之类

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

-- 避免数据库已经存在时报错，使用 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;

使用数据库

use mydb;

删除数据库

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

4.2 建表语法

create [external] table [IF NOT EXISTS] table_name  // external 关键字，外部表，否则创建内部表
[(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];

4.3 内部表与外部表

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

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

• 在生产环境中，多使用外部表

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

  4.4 分区表

  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");
  load data local inpath "/home/hadoop/data/t1.dat" into table t3 partition(dt="2020-06-02");
  -- 分区字段不是表中已经存在的数据，可以将分区字段看成伪列
  

  查看分区

  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 dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
  /user/hive/warehouse/mydb.db/t3/dt=2020-06-07
  hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01
  /user/hive/warehouse/mydb.db/t3/dt=2020-06-08
  -- 增加多个分区。加载数据
  alter table t3 add
  partition(dt='2020-06-07') location
  '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07'
  partition(dt='2020-06-08') location
  '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08';
  -- 查询数据
  select * from t3;
  

  修改分区的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');
  

  4.5 分桶表

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

• MR中：key.hashCode % reductTask

• Hive中：分桶字段.hashCode % 分桶个数 ```sql — 创建分桶表 create table course( id int, name string, score int ) clustered by (id) into 3 buckets row format delimited fields terminated by “\t”;

— 创建普通表 create table course_common( id int, name string, score int ) row format delimited fields terminated by “\t”; — 普通表加载数据 load data local inpath ‘/home/hadoop/data/course.dat’ into table course_common; — 通过 insert … select … 给桶表加载数据 insert into table course select * from course_common; — 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

<a name="ULNaI"></a>
## 4.6 修改表与删除表
```sql
-- 修改表名。rename
alter table course_common rename to course_common1;
-- 修改列名。change column 
alter table course_common1 change column id cid int;
-- 修改字段类型。change column
alter table course_common1 change column cid cid string;
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是string不能转为int
-- 增加字段。add columns
alter table course_common1 add columns (common string);
-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table course_common1 replace columns(id string, cname string, score int);
-- 删除表
drop table course_common1;

5. HQL之数据导入与导出

5.1 数据导入

装载数据(Load)

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

• LOCAL：
  • LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
  • LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
• INPATH：加载数据的路径
• OVERWRITE：覆盖表中已有数据；否则表示追加数据
• PARTITION：将数据加载到指定的分区

插入数据(insert)

-- 创建分区表
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='202001')
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan','TJ');
-- 插入查询的结果数据
insert into table tabC partition(month='202002')
select id, name, area from tabC where month='202001';
-- 多表（多分区）插入模式
from tabC
insert overwrite table tabC partition(month='202003')
select id, name, area where month='202002'
insert overwrite table tabC partition(month='202004')
select id, name, area where month='202002';

创建表并插入数据(as select)

-- 根据查询结果创建表
create table if not exists tabD
as select * from tabC;

使用import导入数据

import table student2 partition(month='201709')
from '/user/hive/warehouse/export/student';

5.2 数据导出

-- 将查询结果导出到本地
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;
-- 将查询结果格式化输出到本地
insert overwrite local directory '/home/hadoop/data/tabC2'
row format delimited fields terminated by ' '
select * from tabC;
-- 将查询结果导出到HDFS
insert overwrite directory '/user/hadoop/data/tabC3'
row format delimited fields terminated by ' '
select * from tabC;
-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001
/home/hadoop/data/tabC4
-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
hive -e "select * from tabC" > a.log
-- export 导出数据到HDFS。使用export导出数据时，不仅有数据还有表的元数据信息
export table tabC to '/user/hadoop/data/tabC4';
-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。create ... as select ... 结构
-- 可能不一致
-- 截断表，清空数据。(注意：仅能操作内部表)
truncate table tabE;

Hive的数据导入与导出还可以使用其他工具：Sqoop、DataX等；

6. HQL之DQL

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语句的可读性(重要)

  6.1 基本查询

  -- 省略from子句的查询
  select 8*888 ;
  select current_date ;
  -- 使用列别名
  select 8*888 product;
  select current_date as currdate;
  -- 全表查询
  select * from emp;
  -- 选择特定列查询
  select ename, sal, comm from emp;
  -- 使用函数
  select count(*) from emp;
  -- count(colname) 按字段进行count，不统计NULL
  select sum(sal) from emp;
  select max(sal) from emp;
  select min(sal) from emp;
  select avg(sal) from emp;
  -- 使用limit子句限制返回的行数
  select * from emp limit 3;
  

  6.2 where子句

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

  select * from emp where sal > 2000;
  select * from t1 where partition_date='2020-1-1'; // 查询某个分区的数据
  

  where子句中会涉及到较多的比较运算和逻辑运算
  官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

备注：通常情况下NULL参与运算，返回值为NULL；NULL<=>NULL的结果为true

-- 使用 like
select ename, sal from emp where ename like '%L%';
-- 使用 rlike。正则表达式，名字以A或S开头
select ename, sal from emp where ename rlike '^(A|S).*'

6.3 group by 子句

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

-- 计算emp表每个部门的平均工资
select deptno, avg(sal)
from emp
group by deptno;
-- 计算emp每个部门中每个岗位的最高薪水
select deptno, job, max(sal)
from emp
group by deptno, job;

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

-- 求每个部门的平均薪水大于2000的部门
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) > 2000;

6.4 表连接

Hive支持通常的SQL JOIN语句。默认情况下，仅支持等值连接，不支持非等值连接。
JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写，使用表名前缀可以提高SQL的解析效率。 连接查询操作分为两大类：

• 内连接 [inner] join
• 外连接 (outer join)
  • 左外连接 left [outer] join左外连接
  • 右外连接。 right [outer] join，右表的数据全部显示
  • 全外连接。 full [outer] join，两张表的数据都显示

多表连接
连接n张表，至少需要n-1个连接条件，例如：连接四张表，至少需要三个连接条件
多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生：

select *
from techer t left join course c on t.t_id = c.t_id
                            left join score s on s.c_id = c.c_id
                            left join student stu on s.s_id = stu.s_id;

Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个MapReduce任务。
上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作；然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作； 然后再继续直到全部操作；
笛卡尔积
满足以下条件将会产生笛卡尔集：

• 没有连接条件
• 连接条件无效
• 所有表中的所有行互相连接

如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据；缺省条件下 hive不支持笛卡尔积运算；

set hive.strict.checks.cartesian.product=false;

6.5 排序子句

全局排序 order by

• order by 子句出现在select语句的结尾；
• order by子句对最终的结果进行排序；
• 默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示降序；

• ORDER BY执行全局排序，只有一个reduce；

  -- 普通排序
  select * from emp order by deptno;
  -- 按别名排序
  select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
  from emp
  order by salcomm desc;
  -- 多列排序
  select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno
  from emp
  order by deptno, salcomm desc;
  -- 排序字段要出现在select子句中。以下语句无法执行（因为select子句中缺少deptno）：
  select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm
  from emp
  order by deptno, salcomm desc;
  

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

  -- 设置reduce个数
  set mapreduce.job.reduces=2;
  -- 按照工资降序查看员工信息
  select * from emp sort by sal desc;
  -- 将查询结果导入到文件中（按照工资降序）。生成两个输出文件，每个文件内部数据按工资降序排列
  insert overwrite local directory '/home/hadoop/output/sortsal'
  select * from emp sort by sal desc;
  

  分区排序 distribute by

• distribute by 将特定的行发送到特定的reducer中，便于后继的聚合 与 排序操作；

• distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；

• distribute by 要写在sort by之前；

  -- 启动2个reducer task；先按 deptno 分区，在分区内按 sal+comm 排序
  set mapreduce.job.reduces=2;
  -- 将结果输出到文件，观察输出结果
  insert overwrite local directory '/home/hadoop/output/distBy'
  select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
  from emp
  distribute by deptno
  sort by salcomm desc;
  -- 上例中，数据被分到了统一区，看不出分区的结果
  -- 将数据分到3个区中，每个分区都有数据
  set mapreduce.job.reduces=3;
  insert overwrite local directory '/home/hadoop/output/distBy1'
  select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm
  from emp
  distribute by deptno
  sort by salcomm desc;
  

  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简化语法

  7. 函数

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

  7.1 系统内置函数

  查看系统函数

  -- 查看系统自带函数
  show functions;
  -- 显示自带函数的用法
  desc function upper;
  desc function extended upper;
  

  日期函数

  -- 当前前日期
  select current_date;
  select unix_timestamp();
  -- 建议使用current_timestamp，有没有括号都可以
  select current_timestamp();
  -- 时间戳转日期
  select from_unixtime(1505456567);
  select from_unixtime(1505456567, 'yyyyMMdd');
  select from_unixtime(1505456567, 'yyyy-MM-dd HH:mm:ss');
  -- 日期转时间戳
  select unix_timestamp('2019-09-15 14:23:00');
  -- 计算时间差
  select datediff('2020-04-18','2019-11-21');
  select datediff('2019-11-21', '2020-04-18');
  -- 查询当月第几天
  select dayofmonth(current_date);
  -- 计算月末:
  select last_day(current_date);
  -- 当月第1天:
  select date_sub(current_date, dayofmonth(current_date)-1)
  -- 下个月第1天:
  select add_months(date_sub(current_date,
  dayofmonth(current_date)-1), 1)
  -- 字符串转时间（字符串必须为：yyyy-MM-dd格式）
  select to_date('2020-01-01');
  select to_date('2020-01-01 12:12:12');
  -- 日期、时间戳、字符串类型格式化输出标准时间格式
  select date_format(current_timestamp(), 'yyyy-MM-dd HH:mm:ss');
  select date_format(current_date(), 'yyyyMMdd');
  select date_format('2020-06-01', 'yyyy-MM-dd HH:mm:ss');
  -- 计算emp表中，每个人的工龄
  select *, round(datediff(current_date, hiredate)/365,1)
  workingyears from emp;
  

  字符串函数

  -- 转小写。lower
  select lower("HELLO WORLD");
  -- 转大写。upper
  select lower(ename), ename from emp;
  -- 求字符串长度。length
  select length(ename), ename from emp;
  -- 字符串拼接。 concat / ||
  select empno || " " ||ename idname from emp;
  select concat(empno, " " ,ename) idname from emp;
  -- 指定分隔符。concat_ws(separator, [string | array(string)]+)
  SELECT concat_ws('.', 'www', array('lagou', 'com'));
  select concat_ws(" ", ename, job) from emp;
  -- 求子串。substr
  SELECT substr('www.lagou.com', 5);
  SELECT substr('www.lagou.com', -5);
  SELECT substr('www.lagou.com', 5, 5);
  -- 字符串切分。split，注意 '.' 要转义
  select split("www.lagou.com", "\\.");
  

  数学函数

  -- 四舍五入。round
  select round(314.15926);
  select round(314.15926, 2);
  select round(314.15926, -2);
  -- 向上取整。ceil
  select ceil(3.1415926);
  -- 向下取整。floor
  select floor(3.1415926);
  -- 其他数学函数包括：绝对值、平方、开方、对数运算、三角运算等
  

  条件函数

  ```sql — if (boolean testCondition, T valueTrue, T valueFalseOrNull) select sal, if (sal<1500, 1, if (sal < 3000, 2, 3)) from emp; — CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END — 将emp表的员工工资等级分类：0-1500、1500-3000、3000以上 select sal, if (sal<=1500, 1, if (sal <= 3000, 2, 3)) from emp; — CASE WHEN a THEN b [WHEN c THEN d] [ELSE e] END — 复杂条件用 case when 更直观 select sal, case when sal<=1500 then 1

                             when sal<=3000 then 2
                             else 3 end sallevel
  

  from emp;

— 以下语句等价 select ename, deptno, case deptno when 10 then ‘accounting’ when 20 then ‘research’ when 30 then ‘sales’ else ‘unknown’ end deptname from emp;

select ename, deptno, case when deptno=10 then ‘accounting’ when deptno=20 then ‘research’ when deptno=30 then ‘sales’ else ‘unknown’ end deptname from emp;

— COALESCE(T v1, T v2, …)。返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL select sal, coalesce(comm, 0) from emp; — isnull(a) isnotnull(a) select from emp where isnull(comm); select from emp where isnotnull(comm); — nvl(T value, T default_value) 如果为null返回default_value select empno, ename, job, mgr, hiredate, deptno, sal + nvl(comm,0) sumsal from emp; — nullif(x, y) 相等为空，否则为a SELECT nullif(“b”, “b”), nullif(“b”, “a”)

<a name="1BPvM"></a>
### UDTF函数
UDTF : User Defined Table-Generating Functions。用户定义表生成函数，一行输入，多行输出。
```sql
-- explode，炸裂函数
-- 就是将一行中复杂的 array 或者 map 结构拆分成多行
select explode(array('A','B','C')) as col;
select explode(map('a', 8, 'b', 88, 'c', 888));

--  SELECT pageid, explode(adid_list) AS myCol... is not supported // 需要lateral view
-- SELECT explode(explode(adid_list)) AS myCol... is not supported // 不允许嵌套

-- lateral view的基本使用
with t1 as (
SELECT 'OK' cola,
            split('www.lagou.com', '\\.') colb
)
select cola, colc
from t1
lateral view explode(colb) t2 as colc;

小结

• 将一行数据转换成多行数据，可以用于array和map类型的数据；

• lateral view 与 explode 联用，解决 UDTF 不能添加额外列的问题

  7.2 窗口函数

  窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数，很多场景都需要用到。窗口函数用于计算基于组的某种聚合值，它和聚合 函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
  窗口函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

  over关键字

  使用窗口函数之前一般要要通过over()进行开窗

  -- 查询emp表工资总和
  select sum(sal) from emp;
  -- 不使用窗口函数，有语法错误
  select ename, sal, sum(sal) salsum from emp;
  -- 使用窗口函数，查询员工姓名、薪水、薪水总和
  select ename, sal, sum(sal) over() salsum,
  concat(round(sal / sum(sal) over()*100, 1) || '%') ratiosal
  from emp;
  

  注意：窗口函数是针对每一行数据的；如果over中没有参数，默认的是全部结果集；

  partition by子句

  在over窗口中进行分区，对某一列进行分区统计，窗口的大小就是分区的大小

  -- 查询员工姓名、薪水、部门薪水总和
  select ename, sal, sum(sal) over(partition by deptno) salsum from emp;
  

  order by 子句

  order by 子句对输入的数据进行排序

  -- 增加了order by子句；sum：从分组的第一行到当前行求和
  select ename, sal, deptno, sum(sal) over(partition by deptno order by sal)
  

  window 子句

  rows between ... and ...
  

  如果要对窗口的结果做更细粒度的划分，使用window子句，有如下的几个选项：

• unbounded preceding。组内第一行数据

• n preceding。组内当前行的前n行数据
• current row。当前行数据
• n following。组内当前行的后n行数据
• unbounded following。组内最后一行数据

-- rows between ... and ... 子句
-- 等价。组内，第一行到当前行的和
select ename, sal, deptno,
             sum(sal) over(partition by deptno order by ename) 
from emp;

select ename, sal, deptno,
             sum(sal) over(partition by deptno order by ename
                              rows between unbounded preceding and current row)
from emp;

-- 组内，第一行到最后一行的和
select ename, sal, deptno,
             sum(sal) over(partition by deptno order by ename
                                        rows between unbounded preceding and unbounded following)
from emp;

-- 组内，前一行 + 当前行 +后一行
select ename, sal, deptno,
             sum(sal) over(partition by deptno order by ename
                                        rows between 1 preceding and 1 following)
from emp;

排名函数

都是从1开始，生成数据项在分组中的排名。

• row_number()
  • 排名顺序增加不会重复；如1、2、3、4、… …
• RANK()
  • 排名相等会在名次中留下空位；如1、2、2、4、5、… …
• DENSE_RANK()
  • 排名相等会在名次中不会留下空位 ；如1、2、2、3、4、… … ```sql — 按照班级，使用3种方式对成绩进行排名 select cname, sname, score,

        row_number() over (partition by cname order by score desc) rank1,
        rank() over (partition by cname order by score desc) rank2,
        dense_rank() over (partition by cname order by score desc) rank3
    

    from t2;

— 求每个班级前3名的学员—前3名的定义是什么—假设使用dense_rank select cname, sname, score, rank from (select cname, sname, score, dense_rank() over (partition by cname order by score desc) rank from t2) tmp where rank <= 3;

<a name="Z3pzS"></a>
### 序列函数

- lag  返回当前数据行的上一行数据 
- lead 返回当前数据行的下一行数据 
- first_value 取分组内排序后，截止到当前行，第一个值
- last_value 分组内排序后，截止到当前行，最后一个值 
- ntile 将分组的数据按照顺序切分成n片，返回当前切片值
```sql
-- 测试数据 userpv.dat。cid ctime pv
cookie1,2019-04-10,1
cookie1,2019-04-11,5
cookie1,2019-04-12,7
cookie1,2019-04-13,3
cookie1,2019-04-14,2
cookie1,2019-04-15,4
cookie1,2019-04-16,4
cookie2,2019-04-10,2
cookie2,2019-04-11,3
cookie2,2019-04-12,5
cookie2,2019-04-13,6
cookie2,2019-04-14,3
cookie2,2019-04-15,9
cookie2,2019-04-16,7
-- 建表语句
create table userpv(
    cid string,
    ctime date,
    pv int
)
row format delimited fields terminated by ",";
-- 加载数据
Load data local inpath '/home/hadoop/data/userpv.dat' into table userpv;
-- lag。返回当前数据行的上一行数据
-- lead。功能上与lag类似
select cid, ctime, pv,
             lag(pv) over(partition by cid order by ctime) lagpv,
             lead(pv) over(partition by cid order by ctime) leadpv
    from userpv;
-- first_value / last_value
select cid, ctime, pv,
             first_value(pv) over (partition by cid order by ctime 
                             rows between unbounded preceding and unbounded following) as firstpv,
             last_value(pv) over (partition by cid order by ctime 
                            rows between unbounded preceding and unbounded following) as lastpv
    from userpv;
-- ntile。按照cid进行分组，每组数据分成2份
select cid, ctime, pv,
             ntile(2) over(partition by cid order by ctime) ntile
    from userpv;

sql面试题

连续值的求解，连续登录，几连冠之类

1. 使用row_number在组内给数据编号（rownum）
2. 某个值-rownum = gid ## rownum连续增长，某个值如果是连续的，则gid为常量，
3. 根据gid ，作为分组条件，求得最终结果。 ```sql — 求连续7天登录的用户 — 数据。uid dt status(1 正常登录，0 异常) 1 2019-07-11 1 1 2019-07-12 1 1 2019-07-13 1 1 2019-07-14 1 1 2019-07-15 1 1 2019-07-16 1 1 2019-07-17 1 1 2019-07-18 1 2 2019-07-11 1 2 2019-07-12 1 2 2019-07-13 0 2 2019-07-14 1 2 2019-07-15 1 2 2019-07-16 0 2 2019-07-17 1 2 2019-07-18 0 3 2019-07-11 1 3 2019-07-12 1 3 2019-07-13 1 3 2019-07-14 0 3 2019-07-15 1 3 2019-07-16 1 3 2019-07-17 1 3 2019-07-18 1 — 建表语句 create table ulogin( uid int, dt date, status int ) row format delimited fields terminated by ‘ ‘; — 加载数据 load data local inpath ‘/home/hadoop/data/ulogin.dat’ into table ulogin;

select uid, dt, date_sub(dt, row_number() over (partition by uid order by dt)) gid from ulogin where status=1;

select uid, count(*) countlogin from (select uid, dt, date_sub(dt, row_number() over (partition by uid order by dt)) gid from ulogin where status=1) t1 group by uid, gid having countlogin >= 7;

TopN求解

1. 得到基表
1. 上排名函数，分数一样并列，所以用dense_rank
1. where rank<=n
<a name="6XLoc"></a>
## 7.3 自定义函数
当 Hive 提供的内置函数无法满足实际的业务处理需要时，可以考虑使用用户自定义函数进行扩展。用户自定义函数分为以下三类：

- UDF（User Defined Function）。用户自定义函数，一进一出 
- UDAF（User Defined Aggregation Function）。用户自定义聚集函数，多进一出；类似于：count/max/min
- UDTF（User Defined Table-Generating Functions）。用户自定义表生成函数，一进多出；类似于：explode

**UDF开发**

- 继承org.apache.hadoop.hive.ql.exec.UDF 
- 需要实现evaluate函数；evaluate函数支持重载
- UDF必须要有返回类型，可以返回null，但是返回类型不能为void

**UDF开发步骤**

- 创建maven java 工程，添加依赖
```xml
<!-- pom.xml 文件 -->
<dependencies>
  <dependency>
    <groupId>org.apache.hive</groupId>
    <artifactId>hive-exec</artifactId>
    <version>2.3.7</version>
  </dependency>
</dependencies>

• 开发java类继承UDF，实现evaluate方法

  package cn.lagou.hive.udf;
  import org.apache.hadoop.hive.ql.exec.UDF;
  public class nvl extends UDF {
    public Text evaluate(final Text t, final Text x) {
        if (t == null || t.toString().trim().length()==0) {
            return x;
        }
        return t;
    }
  }
  

• 将项目打包上传服务器

• 创建临时函数

  • 添加开发的jar包(在hive命令行中)

    add jar /home/hadoop/hiveudf.jar;
    

  • 设置函数与自定义函数关联

    # 创建临时函数，关闭hive后失效
    create temporary function mynvl as "cn.lagou.hive.udf.nvl";
    

  • 使用自定义函数

• 创建永久函数

  • 将jar上传到hdfs

    hdfs dfs -put hiveudf.jar /user/hadoop/jar/
    

  • 在Hive命令行中创建永久函数

    create function mynvl1 as 'cn.lagou.hive.udf.nvl' using jar 'hdfs:/user/hadoop/jar/hiveudf.jar';
    

• 删除永久函数

  drop function mynvl1
  

  8. HQL之DML

  数据操纵语言DML(Data Manipulation Language)，DML主要有三种形式：插入 (INSERT)、删除(DELETE)、更新(UPDATE)。
  事务(transaction)是一组单元化操作，这些操作要么都执行，要么都不执行，是一个 不可分割的工作单元。 事务具有的四个要素：

• 原子性（Atomicity）

• 一致性（Consistency）
• 隔离性 （Isolation）
• 持久性（Durability）

• 这四个基本要素通常称为ACID特性。

  8.1 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）
• 一个事务操作创建一系列的增量文件
• 在读取的时候，将基础文件和修改，删除合并，最后返回给查询

  8.2 事务操作示例

  ```sql — 这些参数也可以设置在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 ;

— DML 操作 delete from zxz_data where nid = 3; dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; insert into zxz_data values (“name3”, 3, “010-83596208”,current_date); — 不支持 insert into zxz_data values (“name3”, 3, “010-83596208”, “2020-06-01”); — 执行 insert into zxz_data select “name3”, 3, “010-83596208”,current_date; dfs -ls /user/hive/warehouse/mydb.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/mydb.db/zxz_data ;

update zxz_data set name=concat(name, “00”) where nid>3; dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; — 分桶字段不能修改，下面的语句不能执行 — Updating values of bucketing columns is not supported update zxz_data set nid = nid + 1;

<a name="RITHO"></a>
# 9. 元数据管理与存储
<a name="f4cy4"></a>
## 9.1 Metastore
在Hive的具体使用中，首先面临的问题便是如何定义表结构信息，跟结构化的数据映射成功。所谓的映射指的是一种对应关系。在Hive中需要描述清楚表跟文件之间的映射关系、列和字段之间的关系等等信息。这些描述映射关系的数据的称之为Hive的元数据。该数据十分重要，因为只有通过查询它才可以确定用户编写sql和最终操作文 件之间的关系。<br />**Metadata**即元数据。元数据包含用Hive创建的database、table、表的字段等元信息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如MySQL等。<br />**Metastore**即元数据服务，是Hive用来管理库表元数据的一个服务。有了它上层的 服务不用再跟裸的文件数据打交道，而是可以基于结构化的库表信息构建计算框架。<br />通过metastore服务将Hive的元数据暴露出去，而不是需要通过对Hive元数据库mysql的访问才能拿到Hive的元数据信息；metastore服务实际上就是一种thrift服 务，通过它用户可以获取到Hive元数据，并且通过thrift获取元数据的方式，屏蔽了数据库访问需要驱动，url，用户名，密码等细节。
<a name="OpHPI"></a>
### metastroe三种配方式

1. **内嵌模式**

内嵌模式使用的是内嵌的Derby数据库来存储元数据，也不需要额外起Metastore服务。数据库和Metastore服务都嵌入在主Hive Server进程中。这个是默认的，配置简单，但是一次只能一个客户端连接，适用于用来实验，不适用于生产环境。<br />优点：配置简单，解压hive安装包 bin/hive 启动即可使用； <br />缺点：不同路径启动hive，每一个hive拥有一套自己的元数据，无法共享。

2. **本地模式**

本地模式采用外部数据库来存储元数据，目前支持的数据库有：MySQL、 Postgres、Oracle、MS SQL Server。教学中实际采用的是MySQL。<br />本地模式不需要单独起metastore服务，用的是跟Hive在同一个进程里的metastore 服务。也就是说当启动一个hive 服务时，其内部会启动一个metastore服务。Hive根据 hive.metastore.uris 参数值来判断，如果为空，则为本地模式。<br />优点：配置较简单，本地模式下hive的配置中指定mysql的相关信息即可。<br />缺点：每启动一次hive服务，都内置启动了一个metastore；在hive-site.xml中暴露了数据库的连接信息；

3. **远程模式**

远程模式下，需要单独起metastore服务，然后每个客户端都在配置文件里配置连接到该metastore服务。远程模式的metastore服务和hive运行在不同的进程里。**在生产环境中，建议用远程模式来配置Hive Metastore。**<br />在这种模式下，其他依赖hive的软件都可以通过Metastore访问Hive。此时需要配置 hive.metastore.uris 参数来指定 metastore 服务运行的机器ip和端口，并且需要单 独手动启动metastore服务。metastore服务可以配置多个节点上，避免单节点故障 导致整个集群的hive client不可用。同时hive client配置多个metastore地址，会自动选择可用节点。<br />**远程模式配置**<br />**规划**

| 节点 | metastore | client |
| :---: | :---: | :---: |
| Linux121 | √ |  |
| Linux122 |  | √ |
| Linux123 | √ |  |

**步骤**

1. 将 linux123 的 hive 安装文件拷贝到 linux121、linux122
1. 在linux121、linux123上分别启动 metastore 服务
```bash
# 启动 metastore 服务
nohup hive --service metastore &
# 查询9083端口(metastore服务占用的端口)
lsof -i:9083

1. 修改Linux122上的hive-site.xml，删除配置文件中：mysql的配置，连接数据库的用户名、口令等信息；增加连接metastore的配置

   <!-- hive metastore 服务地址 -->
   <property>
    <name>hive.metastore.uris</name>
    <value>thrift://linux121:9083,thrift://linux123:9083</value>
   </property>
   

2. 启动hive。此时client端无需实例化hive的metastore，启动速度会加快。

   # 分别在linux121、linux123上执行以下命令，查看连接情况
   lsof -i:9083
   

3. 高可用测试

关闭已连接的metastore服务，发现hive连到另一个节点的服务上，仍然能够正常使用。

9.2 hiveserver2

HiveServer2是一个服务端接口，使远程客户端可以执行对Hive的查询并返回结果。 目前基于Thrift RPC的实现是HiveServer的改进版本，并支持多客户端并发和身份验证，启动hiveServer2服务后，就可以使用jdbc、odbc、thrift的方式连接。
Thrift是一种接口描述语言和二进制通讯协议，它被用来定义和创建跨语言的服务。 它被当作一个远程过程调用（RPC）框架来使用，是由Facebook为“大规模跨语言服 务开发”而开发的。

HiveServer2（HS2）是一种允许客户端对Hive执行查询的服务。HiveServer2是 HiveServer1的后续版本。HS2支持多客户端并发和身份验证，旨在为JDBC、ODBC 等开放API客户端提供更好的支持。 HS2包括基于Thrift的Hive服务（TCP或HTTP）和用于Web UI 的Jetty Web服务器。
HiveServer2作用：

• 为Hive提供了一种允许客户端远程访问的服务
• 基于thrift协议，支持跨平台，跨编程语言对Hive访问
• 允许远程访问Hive

HiveServer2配置
配置规划

配置步骤

1. 修改集群上的 core-site.xml，增加以下内容：

   <!-- HiveServer2 连不上10000；hadoop为安装用户 -->
   <!-- root用户可以代理所有主机上的所有用户 -->
   <property>
    <name>hadoop.proxyuser.root.hosts</name>
    <value>*</value>
   </property>
   <property>
    <name>hadoop.proxyuser.root.groups</name>
    <value>*</value>
   </property>
   <property>
    <name>hadoop.proxyuser.hadoop.hosts</name>
    <value>*</value>
   </property>
   <property>
    <name>hadoop.proxyuser.hadoop.groups</name>
    <value>*</value>
   </property>
   

2. 修改集群上的 hdfs-site.xml，增加以下内容：

   <!-- HiveServer2 连不上10000；启用 webhdfs 服务 -->
   <property>
    <name>dfs.webhdfs.enabled</name>
    <value>true</value>
   </property>
   

3. 启动linux123上的hiveserver2服务

   # 启动 hiveserver2 服务
   nohup hiveserver2 &
   # 检查 hiveserver2 端口
   lsof -i:10000
   # 从2.0开始，HiveServer2提供了WebUI
   # 还可以使用浏览器检查hiveserver2的启动情况。http://linux123:10002/
   

4. 启动linux122节点上的beeline

Beeline是从 Hive 0.11版本引入的，是 Hive 新的命令行客户端工具。
Hive客户端工具后续将使用Beeline 替代 Hive 命令行工具 ，并且后续版本也会废弃 掉 Hive 客户端工具

!connect jdbc:hive2://linux123:10000
use mydb;
show tables;
select * from emp;
create table tabtest1 (c1 int, c2 string);
!connect jdbc:mysql://linux123:3306
!help
!quit

9.3 HCatalog

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

# 进入 hcat 所在目录。$HIVE_HOME/hcatalog/bin
cd $HIVE_HOME/hcatalog/bin
# 执行命令，创建表
./hcat -e "create table default.test1(id string, name string, age
int)"
# 长命令可写入文件，使用 -f 选项执行
./hcat -f createtable.txt
# 查看元数据
./hcat -e "use mydb; show tables"
# 查看表结构
./hcat -e "desc mydb.emp"
# 删除表
./hcat -e "drop table default.test1"

9.4 数据的存储格式

Hive支持的存储数的格式主要有：TEXTFILE（默认格式） 、SEQUENCEFILE、 RCFILE、ORCFILE、PARQUET。

• textfile为默认格式，建表时没有指定文件格式，则使用TEXTFILE，导入数据时会直接把数据文件拷贝到hdfs上不进行处理；

• sequencefile，rcfile，orcfile格式的表不能直接从本地文件导入数据，数据要先导入到textfile格式的表中， 然后再从表中用insert导入sequencefile、rcfile、 orcfile表中。

  行存储与列存储

  行式存储下一张表的数据都是放在一起的，但列式存储下数据被分开保存了。
  行式存储：
  优点：数据被保存在一起，insert和update更加容易
  缺点：选择（selection）时即使只涉及某几列，所有数据也都会被读取
  列式存储：
  优点：查询时只有涉及到的列会被读取，效率高
  缺点：选中的列要重新组装，insert/update比较麻烦
  TEXTFILE、SEQUENCEFILE 的存储格式是基于行存储的；
  ORC和PARQUET 是基于列式存储的。
  

• TextFile

Hive默认的数据存储格式，数据不做压缩，磁盘开销大，数据解析开销大。 可结合 Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不 会对数据进行切分，从而无法对数据进行并行操作。

• SEQUENCEFILE

SequenceFile是Hadoop API提供的一种二进制文件格式，其具有使用方便、可分割、可压缩的特点。 SequenceFile支持三种压缩选择：none，record，block。 Record压缩率低，一般建议使用BLOCK压缩。

• RCFile

RCFile全称Record Columnar File，列式记录文件，是一种类似于SequenceFile的键值对数据文件。RCFile结合列存储和行存储的优缺点，是基于行列混合存储的 RCFile。 RCFile遵循的“先水平划分，再垂直划分”的设计理念。先将数据按行水平划分为行组，这样一行的数据就可以保证存储在同一个集群节点；然后在对行进行垂直划分。

• ORCFile

ORC File，它的全名是Optimized Row Columnar (ORC) file，其实就是对RCFile做 了一些优化，在hive 0.11中引入的存储格式。这种文件格式可以提供一种高效的方法来存储Hive数据。它的设计目标是来克服Hive其他格式的缺陷。运用ORC File可以提高Hive的读、写以及处理数据的性能。ORC文件结构由三部分组成：

• 文件脚注(file footer)：包含了文件中 stripe 的列表，每个stripe行数，以及每个列的数据类型。还包括每个列的最大、最小值、行计数、求和等信息
• postscript：压缩参数和压缩大小相关信息
• 条带(stripe)：ORC文件存储数据的地方。在默认情况下，一个stripe的大小为 250MB
  • Index Data：一个轻量级的index，默认是每隔1W行做一个索引。包括该条 带的一些统计信息，以及数据在stripe中的位置索引信息
  • Rows Data：存放实际的数据。先取部分行，然后对这些行按列进行存储。 对每个列进行了编码，分成多个stream来存储
  • Stripe Footer：存放stripe的元数据信息

ORC在每个文件中提供了3个级别的索引：文件级、条带级、行组级。借助ORC提供 的索引信息能加快数据查找和读取效率，规避大部分不满足条件的查询条件的文件和 数据块。使用ORC可以避免磁盘和网络IO的浪费，提升程序效率，提升整个集群的工作负载。

• Parquet

Apache Parquet是Hadoop生态圈中一种新型列式存储格式，它可以兼容Hadoop生 态圈中大多数计算框架(Mapreduce、Spark等)，被多种查询引擎支持（Hive、 Impala、Drill等），与语言和平台无关的。 Parquet文件是以二进制方式存储的，不能直接读取的，文件中包括实际数据和元数 据，Parquet格式文件是自解析的。
小结
在生产环境中，Hive表的数据格式使用最多的有三种：TextFile、ORCFile、 Parquet。

• TextFile文件更多的是作为跳板来使用(即方便将数据转为其他格式)
• 有update、delete和事务性操作的需求，通常选择ORCFile
• 没有事务性要求，希望支持Impala、Spark，建议选择Parquet